Using ASP.NET With SQL Server

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.

 

Creating SQL Connection in .Net and Connection String description

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=Niranjan-PC; uid=sa; pwd=sa; database=NIR; Connect Timeout=10000";

Let's understand what each parameter means:

Keyword	Description
server	The address of a SQL Server. If the server is on a same computer, where your website runs, define it as "local" or place dot (.). If the server is remote, define it as an IP address, a domain name or a netbios name (as in the example string) of the server.
Uid	The login name, which is defined at your SQL Server to get access. Our login name is "sa".
Pwd	The password, which is defined at your SQL Server to get access. Our password is "sa".
Database	The database's name, which you connect to. Our database name is "NIR".
Connect timeout	The time in milliseconds. When this time is over and the connection is not established, the timeout exception is thrown. This keyword is not necessary. In our case it equals 10,000 ms. Use so large timeouts when you request a lot of data from the server.

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:

myTable
Field	Description
Id	INT, Primary Key
Value	INT

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 created stored procedures from front end

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

 

myProc
Parameter	Description
@Id	Input, INT
@Value	Input, CHAR(10)
@Ret	Output, INT

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 asStoredProcedure (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 &quotforeach" loop seeks all columns in a row
    foreach (DataColumn col in DataRow)
    {
        // do everything you want with row[col]
    }

it is all about using SQL Connection in .Net