Database Search Solution
(New Version) Search Control
Free ASP.NET Controls
ASP.NET Media Player Control
Flash Video Player Control
ASP.NET Telecommute Jobs
Free IP Location Lookup
Test .Net Regular Expressions
CSS/Table/DIV Page Layouts
Article Sites Master List
.NET Windows Forms
General .NET Framework
Accepting Credit Cards
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.
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:
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:
string with T-SQL statement, pay attention: no semicolon at the end of //the
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
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:
the DataSet instance
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.
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:
main â€œforeachâ€ loop seeks all rows in the table
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.
comments powered by Disqus