Introduction to ADO.NET

By Christos S. on June 15, 2013 • ( 3 )

If you aren’t new to .NET development you will be aware that there are several different ways to access and manipulate database data. New developers always use the build in Visual Studio features such as SqlDataSource or EntityDataSource. These are very helpful features coming from .NET Framework allowing you to access your database with just a few clicks and let’s be honest, they do work fine. Other developers prefer to use LINQ in the way we have seen in previous posts. All these techniques for sure let you access your data, writing less code and making less mistakes, since they use strongly data types. What you might aren’t aware of, is that all these features have a drawback: most of their actions/commands are converted sometime in native ADO.NET code before they are actually executed. In other words, what you gain in simplifying code writing, you loose in performance since all commands have to be translated in lower lever code before executed. You might haven’t seen this drawback in simple transactions with just a few data in your database but giving a try your code in a great amount of data trust me, you ‘ll get the filling. This post, as it’s title says is a brief introduction to ADO.NET Framework. If you need to boost your application’s performance while accessing great amount of data, ADO.NET is what you need to use. I personally believe that using ADO.NET is so important that I created a separate category for this feature in my blog. There will be a lot of other posts relating to ADO.NET so stay in touch if you want to get the most of that.

We will begin by learning the following features for using ADO.NET:

Storing Connection Strings

Building Connection Strings

Connection Strings in ASP.NET

In this tutorial I use the AdventureWorks database so if you haven’t installed it in your SQL Server go and as soon as you download it from here, attach it your database server. Open Visual Studio and create a blank solution named AdoNetIntroduction. We ‘ll use this solution where we ‘ll add different projects to see how each of the above features work.

Storing Connection Strings

Right click your solution and create a C# console application project named StoringConnectionStrings. Add a reference to the System.Configuration assembly to your project. You can create and store your connection string in the App.config file of your project. You need to create a connectionString element under the configuration element and define three basic properties:

name: The connectionString’s name
providerName: Your provider
connectionString: A pointer to your database

Following is my App.config file configuration pointing to the AdventureWorks database in my Sql Server. Make sure you change your Data Source property respectively, in case you have a named instance and not the default (e.g Data Source = localhost/namedInstance)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="AdventureWorks"
         providerName="System.Data.SqlClient"
         connectionString="Data Source=localhost; Integrated security=SSPI; Initial Catalog=AdventureWorks"/>
  </connectionStrings>
</configuration>

We used Integrated security=SSPI to connect on our Sql Server which means that the identity of executing code will be used for authentication, rather than an explicit user ID and password. You should use SSPI when applicable. Now let’s use this information in the App.config file to access the database. Open the Program.cs file and paste the following code.

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace StoringConnectionStrings
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("---Accessing my connections strings in the App.config---");
            foreach (ConnectionStringSettings conString in ConfigurationManager.ConnectionStrings)
            {
                Console.WriteLine(conString.Name);
                Console.WriteLine(conString.ProviderName);
                Console.WriteLine(conString.ConnectionString);
            }

            // Retrieve a connection string and open/close it
            Console.WriteLine("-> Get my connection string AdventureWorks");
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            Console.WriteLine("-> Opening the connection.");
            connection.Open();
            Console.WriteLine("Connection string state = {0}", connection.State);
            connection.Close();
            Console.WriteLine("-> Closing connection string.");
            Console.WriteLine("Connection string state = {0}", connection.State);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();

        }
    }
}

Build and run your solution.

You can see two connection strings in your console so you may wondering why that happened. Well the first one (red square) comes from system’s configuration settings for .NET, so give it a rest. The second one is our Adventureworks connection string. We used the System.Configuration namespace to retrieve our connection string, through the ConfigurationManager.ConnectionStrings. You create a connection to your database, using an SqlConnection instance and passing it your connectionString as parameter. You open and close the connection using the SqlConnection.open() and SqlConnection.close() medthods respectively.

Building Connection Strings

We saw how we can store and access a connection string defined in the App.config file but what if we didn’t want to store the connection string there but instead, create it at runtime? Moreover, what if we wanted to change database at runtime using the same connectionString? To see these features create another console application project named BuildingConnectionString. Open and paste the following code in the Program.cs file.

using System.Data.SqlClient;

namespace BuildingConnectionString
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=localhost;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                Console.WriteLine("My connectionString = {0}\n",
                    connection.ConnectionString);

                // Open the connection
                connection.Open();
                Console.WriteLine("=> Connection opened.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}\n", connection.Database);

                // Change database at runtime.
                connection.ChangeDatabase("ReportServerTempDB");
                Console.WriteLine("=> Database changed to ReportServerTempDB.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}\n", connection.Database);

                // Close the connection
                connection.Close();
                Console.WriteLine("=> Connection closed.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}", connection.Database);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

Make sure you set this project as the start up, build and run it.

We defined this time our connectionString simply with a string (lines 9-10) which we passed it as a parameter to an SqlConnection instance. When you create an SqlConnection instance it’s good to use a using statement so all resources the connection uses will be disposed as soon as they aren’t needed anymore (line 12). You can use the same connection string to change database at runtime as we did in the line 25 through the SqlConnection.changeDatabase(string newDatabaseName) method.

Connection Strings in ASP.NET

Last thing I wanna show you is how to configure your ASP.NET application in order to access a database through ADO.NET using Integrated Security. Create an ASP.NET empty Web application project named ConnectionStringsInAspNet. We will add the connection string’s setting in the Web.config file as follow.

<?xml version="1.0"?>

<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <authentication mode="Windows"/>
    <identity impersonate="true" userName="developer-pc\developer"
        password="*******"/>
  </system.web>
  <connectionStrings>
    <add name="AdventureWorks" providerName="System.Data.SqlClient"
        connectionString="Data Source=localhost; Integrated security=SSPI;Initial Catalog=AdventureWorks;"/>
  </connectionStrings>
</configuration>

Beside the connectionStrings element we have added in the same way we did before, we need to tell the ASP.NET Framework that we want Integrated Security. We do that by adding an authentication element with mode=”Windows”. But that’s not enough. We have to add an identity element too, defining exactly the user will access the Sql Server. You need to give both username and password. Add a Web Form to your new project and name it “Default.aspx”. Change to the code behind file (Default.aspx.cs) and paste the following code. We are going to connect to the AdventureWorks database and retrieve some data.

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ConnectionStringsInAspNet
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string sqlText = "SELECT TOP 10 * FROM Person.Person";
            string connectString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlText, connectString);
            da.Fill(dt);

            foreach (DataRow row in dt.Rows)
                Response.Write(row["BusinessEntityID"] + " - " + row["LastName"] + ", " + row["FirstName"] + "<br/>");
        }
    }
}

Set your ASP.NET project as the start up, make sure you have changed your username and password in the Web.config file respectively and start your application.

I will not explain in details what DataTable or DataRow objects are and how they work, since this is subject of a later post in these series. The only thing I want to point is how you actually connect to your Sql Server database. This is done with a SqlDataAdapter instance, where we passed two parameters: our pure SQL select statement and our connection string.

That’s it, this was a brief introduction to ADO.NET learning some basic things we need to know, before continuing to query specific data from our database. Lot’s of interesting posts about ADO.NET are coming next so keep in touch. I hope you enjoyed the post, you can download the solution we created from here.