Using WebMatrix.Data in ASP.NET Web Forms

John Avis by | July 17, 2013 | ASP.NET Web Forms ASP.NET Web Pages

ASP.NET Web Pages has a simple to use Namespace that help you open, query and send commands to a database, and to work with rows that are returned by SQL queries.
ASP.NET Web Pages (you know the difference between ASP.NET Web Forms and Web Pages, right?) has a simple to use Namespace that help you open, query and send commands to a database, and to work with rows that are returned by SQL queries.

Although intended for use in ASP.NET Web Pages and WebMatrix, it can also be added to ASP.NET Web Forms websites and applications and used as a simpler alternative to System.Data.SqlClient, or as an alternative to Linq to SQL, Entity Framework, or the many ORMs and Micro ORMs available.

Why use WebMatrix.Data?
  • If you want to craft your own SQL queries or access stored procedures, rather than use Linq.
  • If you usually use System.Data.SqlClient but would like something simpler to use.

Why wouldn't you use WebMatrix.Data?
  • You need or prefer strongly typed objects, and Intellisense.
  • You're afraid people will laugh at you when they see you used it.

Here's some examples of how simple WebMatrix.Data can be to use.

Open a database

You can open a database by specifying a SDF database file, a connection string from web.config, or a connection string.
var db = Database.Open(filename);

Or
var db = Database.Open(connectionStringName);

Or
var db = Database.OpenConnectionString(connectionString);

Execute a command and return a count of affected rows
int rows = db.Execute("INSERT INTO Data (Name, DateStamp, Status) VALUES ('Smith', DateTime.Now, null)");

Get the identity columns from the most recently inserted row
int id = db.GetLastInsertId();

(Note that under the hood this method uses "SELECT @@Identity". See this Stack Overflow quesion for some alternative methods.)

Query the database and return a collection of rows
foreach (var result in db.Query("SELECT * FROM PRODUCT"))
{
int id = result.Id;
string name = result.Name;
var price = result.Price;
bool? enabled = product.Enabled;
}

Query the database and return a single row
var product = db.QuerySingle("SELECT * FROM Product WHERE Id = 1");

if (product != null)
{
int id = product.Id;
string name = product.Name;
var price = product.Price;
bool? enabled = product.Enabled;
}

Query the database and return a scalar value
int count = db.QueryValue("SELECT COUNT(*) FROM Product");

As you can see in the above examples you can explicitly specify the column type value, or use var.

Passing parameters

Execute, Query, QuerySingle and QueryValue all allow you to add parameters very simply.

For example:
db.Query("SELECT * FROM PRODUCT WHERE Price > @0 AND Price < @1", 20, 100))

Importantly, passing parameters this way protects against SQL injection attacks.

Closing the database

As usual, it's good practice to close your connection when you're finished with it. You can do this with a using block or explicity.
using (var db = Database.Open(filename))
{
//code here
}

Or
db.Close();
db.Dispose();

How to add a reference to WebMatrix.Data

In Visual Studio 2012 you will find WebMatrix.Data under Assemblies then Extensions.

Adding a reference to WebMatrix.Data

For detailed documentation see WebMatrix.Data Namespace.

Related Posts

Bootstrap ASP.NET Web Forms

ASP.NET Web Forms and Bootstrap 3 Modals

by John Avis | July 6, 2017

There are a few methods for hiding and showing Bootstrap 3.x modals in an ASP.NET Web Forms application. This technique is pure ASP.NET (requires no client script) and allows you to show and hide modals by changing the Visible property of a containing PlaceHolder or Panel.


ASP.NET Web Forms

Disable common UpdateProgress controls for an UpdatePanel with an associated UpdateProgress

by John Avis | April 12, 2017

If you have an ASP.NET Web Forms page with multiple UpdatePanels and multiple UpdateProgress controls, sometimes you may want one or more UpdateProgress controls associated with a specific UpdatePanel, and one or more UpdateProgress controls that have no association with an UpdatePanel so are shown when any UpdatePanel is updated.


Bootstrap ASP.NET Web Forms

Bootstrap checkbox-inline and radio-inline with ASP.NET CheckBoxList and RadioButtonList controls

by John Avis | June 20, 2016

Although you can easily create inline checkboxes and radio buttons using ASP.NET CheckBoxes and RadioButtons using Bootstrap's checkbox-inline and radio-inline classes, it's not so easy with ASP.NET CheckBoxList and RadioButtonList controls.

Comments

Rob Churcch

by Rob Churcch | February 22, 2015

Regarding int id = db.GetLastInsertId();

The function returns a decimal data type so you will need to cast...

int id = (int) db.GetLastInsertId();

Reply

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.