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.