Accessing SQL Server 2005 data from ASP.NET 2

This article describes how to attach a Microsoft SQL Server database programmatically to your ASP.NET 2 web page, using C#.

Introduction

When you first start with ASP.NET2 it is fun to create your applications by just dragging and dropping, and using the wizards. But sooner or later you will want to access your SQL Server data directly, without using the data connection wizards of ASP.NET. This article describes how to do exactly this.

An Introductory Query

This first simple example shows how to access some SQL Server data and display it on your ASP.NET page. The example uses C# and SQL Server 2005 Express, but it should be good for the full SQL Sever too. I have my database on the same computer as the ASP.net server, but you are not limited to this.

First, you need to make sure that you include these lines at the top of your code-behind page (.aspx.cs)

using System.Data;
using System.Data.SqlClient;

These commands allow you access to the SQL functions. Here is the example query:

string connectString = @"Data Source=MYPC\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True";
SqlConnection con = new SqlConnection(connectString);
con.Open();
string sql= "select * from product where type_id=" + thistype + " and part_id="+thispart;
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
  string order = reader[0].ToString();
  Response.Write("Order is " + order+ "<br>");
}
reader.Close();
con.Close();

This simple example makes a SQL query on the product table, and goes through all the resulting records writing out item 0 from them. So if "select * from..." returns an ID, a description, a type_id and a part_id, then item 0 is the ID, item 1 is the description, etc.

You may be wondering about the "ExecuteReader" method. This is used because this query returns a set of results that we get by stepping through a data reader one row at a time. If the query was an insert or an update that didn't return results, then we could use cmd.ExecuteNonQuery instead. If the SQL statement returns just a single result, such as "select count(*) from..." then you can use cmd.ExecuteScalar to get just that single result.

You can put this code anywhere you like. If you are just testing, put it here:

protected void Page_Load(object sender, EventArgs e)
{
   // Put the code here!
}

Where the connection string comes from

You can find out the connection string for any database like this:

1. In Visual Web Developer, go to the Database Explorer panel.
2. Click "Add a connection" and use the wizard to make a connection to a SQL Server database. Use the test button to check that it works.
3. Now right-click on the database connection in the Database Explorer and click properties. You will see the Connection String listed, and you can copy it from there into your code. So, the wizard did the work and we got the benefit.

SqlConnection connection pooling

The SqlConnection part is smart in ASP.NET 2 because it does connection pooling behind the scenes. If you have more than one SQL command to perform in one procedure then open the connection at the start, do each of the commands in turn, and then close the connection at the end. The automatic pooling will keep track of things for you and speed things up because you don't need a new connection for each query.

If you make the mistake of not closing a reader or a connection, then sooner the system will runs out of available connections and you will get funny errors. The error doesn't say something like "oops, you've run out of connections". You just get unexplained errors with connecting to the database.

How to reading integers and booleans from a SQL Server database

The above example reads strings from the database, and if the data field contains NULL, it is not a problem because strings can hold null values. But if you want to read integers from your database and want to make sure that you don't get an error if you encounter a NULL, but instead return a 0, then do this:

tString = "select max(myfield) from mytable where myotherfield=" + myvalue;
myresult=0;
cmd = new SqlCommand(tString, con);
reader = cmd.ExecuteReader();
if (reader.Read())
{
   if (!reader.IsDBNull(0))
   {
    myresult = reader.GetInt32(0);
   }
}
reader.Close();

You can use various types of reader.GetIntxx or reader.Getdate commands to get data in certain types from the database. Another example would be reader.GetBoolean(1) to get a bool value from the second column in the record.

Parameterized Queries

The above examples are very badly written because the SQL statements are built up piece by piece, from various strings. This is inefficient because string concatenation is slow, and also because ASP.NET has a better way to do it that is easier to read.

protected void Page_Load(object sender, EventArgs e)
{
   int mycat = 1;
   string connectString = @"Data Source=MYPC\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True";
   SqlConnection con = new SqlConnection(connectString);
   con.Open();
   string sql = "select name from category where category_id=@cat";
   SqlCommand cmd = new SqlCommand(sql, con);
   cmd.Parameters.AddWithValue("cat", mycat);
   SqlDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      string name = reader[0].ToString();
      Response.Write("name is " + name + "<br>");
   }
   reader.Close();
   con.Close();
}

As you can see, things are a little cleaner here because we can write the SQL query more neatly, just using @variablename where we want to put a parameter. Then we use cmd.Parameters.AddWithValue to add a new parameter to the ones that the command knows about (initally it knows nothing) and then match this parameter up to the one in the query by giving its name, and also providing a value for it.

Conclusion

So that's all for now. This page has shown you how to create parameterised queries that access SQL Server 2005 data from an asp.net page.