How to Connect to a Database in ASP.NET

string.Format C#We use ASP.Net to create dynamic and responsive web applications that take advantage of the power of IIS and C#. With ASP.Net we can do just about anything with our websites just by adding the right tags and their associated C# code. However, even the most flash-heavy web applications need a way to store information about is visitors and customers. Fortunately, we have access to the entire ADO.Net framework to connect to a database in ASP.Net.

Learn the basic of ASP.Net programming at Udemy

ASP.Net Connection Strings

Unlike other internet programming languages and frameworks, ASP.Net uses a generic database system to connect and retrieve information from a database. While you can package this system into database specific extensions, nothing will happen until you pass a connection string to it.

Connection strings are just ordinary strings that contain all the information ASP.Net needs to create the database connection and then configure it for use. This information includes the address of the database server, your username and password, and any default settings your database needs to run correctly. There is a connection string for just about any database. There are too many to name here, so your best bet is to do an internet search for the string that corresponds to your particular database solution. If all else fails, you can create a data source name through your web servers administration settings if none of the available strings online will work for you.

Now that you have your connection string, you have to add it to your code. While you could hard code it directly into your C#, this is not recommended. With ASP.Net, you want to put your connections string in your web site’s web.config file. This way you can change databases without having to edit your code at all.

The web.config file lists the site’s settings in XML-formatted code. While the file has sections for everything, you want to locate the connectionStrings element inside the configuration element. The piece of code you want looks like this:

<configuration>
.
.
.
<connectionStrings>

<add name=”MyDatabaseConnectionString” connectionString=”Server=MyDataServer;Integrated Security=SSPI;Database=MyDatabase;" providerName=”System.Data.SqlClient” />

</connectionStrings>
.
.
.
</configuration>

If you don’t see an “add” element in side connectionStrings, or the defaults don’t match your database, you will need to add a new “add” element of your own. This is as simple as including your connection string as the connectionString. The name attribute is required and it lets you give the string a name you can use later. ProviderName is optional and it sets the ASP.Net namespace associated with your database.

Become a C# master at Udemy

Creating a Database Connection

Now that you have your database connection string set and ready to go, it is now time to use it in your applications. With ASP.Net, you have two ways to create a connection to your database. The first method is to use data source codes. The other method requires the use of Connection classes in your C# code.

Data Source Controls

Introduced in ASP.Net 4, data source controls let you embed your database connection directly into your web form. The framework comes with source controls for all the most common database types as well as generic controls for ole db and odbc connections if you need them. You might be able to find a few good third party solutions as well.

Regardless of the type of database you use, every data source controls has the same required attributes. The only difference is the name of the control. The following is a sample control for an MS SQL database.

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="Select * from Customers"
ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" />

For any source control, only the ID, runat, and ConnectionString attributes are required. ConnectionString is where you include your database connection string, while ID and runat are your standard .Net control attributes. The code I used above lets you draw this information from your web.config file otherwise you will have to hard code the information here.

SelectCommand sets the SQL statement the control will execute when called. SelectCommand itself is for SQL select inquiries and is the most common type of these command statements. There is a command attribute for every SQL command. You just have to include the statements you need with the attribute that bears the name of the SQL command with “Command” after it.

For more advanced inquiries, ASP.Net also provides a Linq data source control which looks like this:

<asp:LinqDataSource  ContextTypeName="MyDatabaseDataContext"   TableName="Products"  ID="LinqDataSource1"    runat="server"> </asp:LinqDataSource>

To use these controls in your application, you just set the DataSourceID attributes of your data controls to the appropriate source control.

Learn how to write SQL statements at Udemy

Connecting to the Database in Code

Before source controls, everyone had to use C# to connect to their databases. There are still many situations where this method is still preferred.

The entire ADO.net framework exists under the System.Data namespace, so you will need to include it everywhere you need to use this “in code” method. You do this with either the using C# statement in your code file or the Import ASP.Net directive in your web form file.

using System.Data.SqlClient;

<%@ Import Namespace=”System.Data.SqlClient” %>

As with the source controls, there is a namespace for each common database plus generic options of ole db and odbc connections.

Once you have the right namespace set, you can start connecting to your database through a connection object. Each database namespace has its own connection object. It usually has the name of the database type followed by the word Connection. You set the connection string through the connection objects ConnectionString property or as an argument of its constructor. You can use the ConnectionStrings collection of the built in ConfigurationManager to get your connection string from web.config.

string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();

SqlConnection mySQLconnection = new SqlConnection(connStr);

There you have it. Your ASP.Net application is now connected to your database. You will have to called your connection object’s Open() method to actually use it, but that’s all you need to connect to a database through ASP.Net controls. From this point on, you just need to use .Net’s data objects to access your database data as you need it.  To close the connection, you just have to issue the connection’s Close() command, and then its Dispose() command to clean up your server’s resources.