This tutorial explains about Accessing Data with C# with an Overview of ADO.NET, Connecting to Data , Executing Commands , Working with Data and Choosing an ADO.NET Provider along with the Project files used in this.
ASP.NET: Accessing Data with C#
Introduction:
When working with classic asp we had ADO, object model for communication with the database. Microsoft.NET has introduced ADO.NET components that lets the developer communicate with the database more efficiently and easily. In this article we will see how we can make use of the ADO.NET classes to perform different operations on the database.
ADO. NET Classes:
ADO .NET classes are put in the System.data namespace. You can access the classes using the following code:
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.Oracle;
Different classes are used for different purpose.
System.Data.SqlClient: This class is used to communicate with the Sql Server database. The database can be version 7.0 or version 2000.
System.Data.SqlClient: This class is used to perform operations on the MySQL databases.
System.Data.OleDb: This class is used to perform operations on the Access Database.
System.Data.Oracle: This class is used to perform operations on the Oracle database.
In this article we will focus on the Sql Server 2000 database and hence we will be using System.Data.SqlClient namespace to perform different operations on the Sql Server 2000 Database.
Making the database connection:
Let’s see how we can make a database connection. There are several ways of making a database connection. You can simple drag and drop the database connection on the asp.net web form and the connection will be made. Let’s see how we can do that:
Open you Visual Studio.NET and start a new asp.net web application. In the toolbox you will see a tab called data. Click on the tab and it will dropdown showing various ADO objects. Drag and Drop the SqlConnection object on the screen. As soon as you drop the connection object you will see it at the bottom of the screen.
Right click on the connection object and select properties. In the properties you can see the property named "ConnectionString". When you click on it will take you to a wizard where you can select your database. In this article I will be using Northwind database which can be found in every Sql Server 2000 database.
Once you select the database, test your connection by clicking on the Test connection button. If the connection is correct a message box will pop saying that the connection has been tested and connection is right.
Problems using this approach of making the connection String:
As you have just seen that we just dragged and dropped the connection string on the screen and the new connection to the database was made in seconds. This approach should never be used since if in the future you change your connection string you will have to change every where in the application.
Using Web.config to store the connection String:
As you can see above that you can make your connection string with just one line. Take a look at the "key" represents the keyword that we will use to refer to it in our application.
"value" contains the connection string.
"database" contains the name of the database which in this case is Northwind.
I have to point out that saving the connection string like this is not secure. Usually you store the connection string after encrypting it. I will not perform encryption in this article and keep the article simple enough.
Accessing database from webpage:
Now you have added the connection string in the web.config file. Let’s see some code that we can use to access the database. We want to load some data from the database when the page is loaded for the first time.
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
string connectionString = (string) ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories",myConnection);
DataSet ds = new DataSet();
ad.Fill(ds,"Categories");
myDataGrid.DataSource = ds;
myDataGrid.DataBind();
}
}
First, we check that if it’s not a postback we load data. For this example to work you need to add the namespace:
using System.Configuration;
Let’s see what is going on here:
1) string connectionString receives the string from the web.config file which is referred by the "ConnectionString" key we set in the web.config file.
2) We make the object of the SqlConnection class which deals with SQL Server databases.
3) We made the data adapter object. Data adapter is a kind of connection to the database. In the data adapter object we specify that what we want from the database. In this case we want all the rows of the Categories table.
NOTE:
Always use stored procedures for accessing the data. Accessing or running Ad-hoc queries are dangerous as they are open for SQL Injections.
4) Next we made an instance of the DataSet class. DataSet will contain the result of the SqlDataAdapter even if the database connection is not made.
5) Later, we filled the dataset with the data using dataadapter.
6) And finally we assigns the datagrid to the dataset and binds it on the screen.
Pretty simple right?
Lets see if we can improve the code above:
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
string connectionString = (string) ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataReader reader = null;
SqlCommand myCommand = new SqlCommand("GetData",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
try
{
myConnection.Open();
reader = myCommand.ExecuteReader();
myDataGrid.DataSource = reader;
myDataGrid.DataBind();
}
catch(Exception ex)
{
// Catches and logs the exception
}
finally
{
reader.Close();
myConnection.Close();
}
}
}
In the code above we are using the SqlCommand object and stored procedure. You might have noted SqlDataReader, if you want to merely iterate through the database rows and select them its best to use SqlDataReader since its much faster than DataSet.
Finally we catch exceptions if any of them are generated and closes the connection.
I hope you liked the article happy coding !
