Access your SQL Server data from ASP.NET

This article describes how to access a Microsoft SQL Server database programmatically from within C#, using ASP.NET 2.

Introduction

Some of the time it is enough to use the Visual Studio or Visual Web Developer visual method of dragging and dropping components and objects to build an application. But usually you need to add a little bit of brains to the thing, and start creating your own procedures with actual SQL queries in them.

An example Query

This first example shows a query performed on an SQL database from C# inside ASP.NET. I have Visual Web Developer 2005 Express Edition and SQL Server 2005 Express Edition. The SQL database is running on the same PC as the ASP.NET program. Don't forget that you need to put:

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

at the top of the .aspx.cs page in order to access the SQL functions. Here is the example code:

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

The connection string

It took me a while to figure out the connection string. In the end I got it this way:

In Visual Web Developer, go to the Database Explorer panel.
Add a connection there, and use the wizard to make a connection to your SQL Server database. Test it to see that it works.
Now, right-click on the database connection in the Database Explorer, and click properties. You will see the Connection String listed as a property, and you can copy/paste it from there.

The line string order = reader[2].ToString(); gets the THIRD result from the query, i.e. if you select * from the database, the first item in each row is returned as item 0, and the second as item 1.

The bit about ExecuteReader is used if you are doing a query that returns results. If you are just doing an update or delete command, you can use the ExecuteNonQuery command instead. Just simply do a cmd.ExecuteNonQuery(); and forget all that "reader" stuff.

SqlConnection is clever in ASP.NET 2

The SqlConnection part is apparently quite clever in ASP.NET 2, and does some sort of connection pooling for you, that you don't need to worry about, but just sit back and let it speed up things for you. All you need to know is that if you have a few SQL commands to do in one procedure, open the connection at the start, do each of the commands in turn, then close the connection at the end, and everything will be ok.

If you make the mistake of not closing the reader, or the connection, then sooner or later you will get bizarre errors as the system runs out of available connections.

Reading integers or booleans, but not nulls, from the database

If you want to read some integers from the database, and want to make sure that you don't get tricked by any bogus NULLs in there, do this:

tString = "select max(rankorder) from evaluation_type_part where type_id=" + type_id; cmd = new SqlCommand(tString, con); reader = cmd.ExecuteReader(); if (reader.Read()) { if (!reader.IsDBNull(0)) { rankorder = reader.GetInt32(0); } } reader.Close();

You can use various types of reader.GetIntxx or Getdate etc commands to get data in certain types from the database.

For example, reader.GetBoolean(1) gets a bool value from the second column in the record.