Using ASP.NET With SQL Server

Intro

If you want to develop web sites with dynamic contents (eCommerce, bulletin boards, etc.), one of the options is to MS SQL Server to store, modify, and get your data. Data access to SQL Servers is provided in ASP.NET by ADO.NET. There are five steps in this area below.

How To Describe Connection to SQL Server

We will use the System.Data.SqlClient and the System.Data namespaces of ADO.NET. The System.Data contains basic enumerations and classes, which we will use below. The System.Data.SqlClient provides data access to SQL servers such as MS SQL Server 2000 and higher. Add the next snippet to the beginning of your code page in order to get easy access to their classes:

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

To begin "communications" with our server we should define the SqlConnection class, initialize a new instance and set its connection string parameters. There is an example of a connection string below:

string Connection = "server=ALDAN; uid=sa; pwd=sa; database=GAZCAD; Connect Timeout=10000";

Let's understand what each parameter means:

Pay attention: letters' case of the keywords has no matter.

We are ready to create an instance of the SQLConnection class:

SqlConnection DataConnection = new SqlConnection(Connection);

The connection is described; we will use it at next steps.

Execute "non-SELECT" statements

T-SQL "non-SELECT" statements begin with such keywords: INSERT, DELETE and UPDATE. For example, there is a table, called "myTable", in our database:

Let's insert a row into it. We will use the SQLCommand class. Initialize a new instance of it with a string of a T-SQL statement and our SQLConnection instance. Open the connection, execute the statement with the ExecuteNonQuery method, which is used for "non-Select" statements and procedures, and close the connection. Here is the code snippet for that:

// the string with T-SQL statement, pay attention: no semicolon at the end of //the statement
string Command = "INSERT INTO myTable VALUES (1,100)";
// create the SQLCommand instance
SQLCommand DataCommand = new SqlCommand(Command, DataConnection);
// open the connection with our database
DataCommand.Connection.Open();
// execute the statement and return the number of affected rows
int i = DataCommand.ExecuteNonQuery();
//close the connection
DataCommand.Connection.Close();

The "I" variable contains the number of affected rows. You will find out how to execute stored procedures at the next step.

Execute stored procedures

For example, we have a stored procedure, called "myProc", which does something, and it has a list of parameters:

You can execute it very easy, using the SQLCommand class. There are several differences between executing "non-Select" statements and stored procedures. The command string contains the procedure's name now. The CommandType Property has to be set as StoredProcedure (use the CommandType enumeration), because the default is Text (T-SQL statement). To create the parameter list we use the SQLParameter class. To set a type of parameters, we use the SQLDbType enumeration. To set a direction of a parameter we use the ParameterDirection enumeration. There is the snippet with comments below:

// create the SQLCommand instance with the name of the procedure and the //SQLConnection instance
SqlCommand execproc = new SqlCommand("myProc", DataConnection);
//Set the CommandType property to StoredProcedure. It is necessary in this //case. The default is Text (T-SQL statement).
execproc.CommandType = CommandType.StoredProcedure;
//open our connection
execproc.Connection.Open();
//Add the parameter to parameters of the procedure with the required type
SqlParameter Param = execproc.Parameters.Add("@Id", SqlDbType.Int);
//Set the parameter`s value
Param.Value = 100;
//Add the next parameter, set its size to 10.
Param = execproc.Parameters.Add("@Value", SqlDbType.NChar, 10);
//Set the parameter`s value
Param.Value = "your_chars";
//Add the next parameter
Param = execproc.Parameters.Add("@Ret", SqlDbType.Int);
//Set the parameter`s value
Param.Value = null;
//Set the Direction property to Output. It is necessary in this case. The //default is Input
Param.Direction = ParameterDirection.Output;
// execute the procedure
execproc.ExecuteNonQuery();
// Get a value of “@Ret” (it is Output). Don`t forget cast the Value property //to a required type, cause the Value property has the Object type.
int ret = (int)execproc.Parameters["@Ret"].Value;
//Close our connection
execproc.Connection.Close();

You will find out how to execute "SELECT" statements at the next step.

Execute "SELECT" statements

When we execute "SELECT" statements we get data tables from SQL Server. To provide this process, we should use the DataSet class. It represents data tables which we will get from a server. The filling of the DataSet is provided by the SQLDataAdapter class with using its Fill method. A constructor of this class takes same arguments as the SQLCommand does. There is the snippet with comments below:

//Create the DataSet instance
DataSet ds = new DataSet();
//Assign the “select” statement string                          
string SelectCommand = "SELECT * FROM myTable WHERE Value = 100";
//Create the SQLDataAdapter instance
SqlDataAdapter DataCommand = new SqlDataAdapter(SelectCommand, DataConnection);
//Get data from a server and fill the DataSet 
DataCommand.Fill(ds);

Pay attention: it is not necessary to open connection "manually". The Fill method provides it automatically.

The "ds" contains findings now. You will find out how to process them at the next step.

Process findings

We have got the "ds" instance of the Dataset with findings at the previous step. So, we should process them to display at our web site. The Dataset contains the Tables property. It is the collection of tables. Findings are written to a zero-indexed table. We use two loops to seek all data. The first loop seeks all rows in a table (DataRow instances); the embedded loop seeks all columns in a row (DataColumn instances). You can get each element as DataRow[Datacolumn]. There is the snippet with comments below:

// the main “foreach” loop seeks all rows in the table
foreach (DataRow row in ds.Tables[0])
    // the embedded "foreach" loop seeks all columns in a row
    foreach (DataColumn col in DataRow)
    {
        // do everything you want with row[col]
    }

Conclusion

There are a lot of subjects when developing ASP.NET websites with using SQL Servers. Some of them are introducted in this tutorial. I hope it was useful for you. Good Luck!

This tutorial is written by LStudio.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |