Q99. How to connect to a SQL Database with ADO.NET?

we will connect to SQL Database using ADO.NET and perform some simple query operations on the Customer table of SOM database. Note that the way an application interacts with SQL Database is similar to how an application interacts with the traditional SQL server database. However, there are a few differences. The main difference is the connection and connection strings used to connect to the database. After we change the connection string to connect to the SQL Database, ADO.Net client library classes and methods will start working.

In case, we are configuring an on-premise application to connect to SQL Database in the cloud, we need to change the connectionstring in the connectionstring section of the App.config or Web.config file.

In case, the application is a Cloud service and we are trying to connect to SQL Database from a Web Role or a Worker Role, we will store the connection string in the service configuration files and not in the web.config or App.config files.

We can use the same designer tools in Visual Studio to develop .Net SQL Database applications. Remember, you can use the Server Explorer to view the database objects of SQL Database but we cannot edit them. To edit the objects, we have to use the management portal for SQL Database which we have covered in the previous topics.

Using the Entity Data Model Designer in Visual Studio we can create EDM against SQL Database also, just as would in case of SQL server.

Using ADO.NET to connect to Windows Azure SQL Database is similar to connecting to an instance of SQL Server in your datacenter. Now, let us discuss the changes we need to consider, for connecting to SQL Database.

1.SQL Database does not support Windows Authentication. So, we can use only the SQL Server authentication in the connection string.
2.SQL Database does not support OLE DB.
3.SQL Database does not support distributed transactions.
4.User ID parameter in the connection string should follow this notation - <login>@<server>. In the sample connection string code which is given next, login name is 'somadmin' and server name is appended to the login name.


“Server=tcp:ld8xofkmo6.database.windows.net,1433;
Database=SOMDb;User ID=somadmin@ld8xofkmo6;Password=som123ABC; 
Trusted_Connection=False; Encrypt=True;Connection Timeout=30”;    


5. If you do not specify a database in the connection string, you will be connected to the master database.

The following example is a console application that connects to Windows Azure SQL Database. This application reads the customer name for every row in the Customer table.

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

namespace cloudconnectionstringwebapp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection())
                {
                    conn.ConnectionString = “Server=tcp:ld8xofkmo6.database.windows.net,1433;Database=SOMDb;User ID=somadmin@ld8xofkmo6;Password=som123ABC;Trusted_Connection=False;Encrypt=True;Connection Timeout=30”;
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = “SELECT * from Customer”;
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(
                        CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                TextBox1.Text =(String) dr[“Name”];
                            }
                            dr.Close();
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                // ....
            }
        }
    }
}



1. Set the TrustServerCertificate connection property to False (TrustServerCertificate = False) and Set the Encrypt connection property to True (Encrypt = True) to secure your connection with the SQL Database.

2. It is recommended to set the connection timeout to 30 seconds, when opening a connection to a SQL Database.

The only thing that needs to be changed in the application is the connection string to connect to the database in SQL Database server. We need not do any other changes in the application code. The standard connection string looks like this:

Server=tcp:VirtualServerName.database.windows.net;  
Database=;
User ID=@;
Password=;
Trusted_Connection=False;
Encrypt=True;
ConnectionTimeout=;


In the above code, Trusted_Connection=False; indicates that the connection cannot be a trusted connection.

Severname is the fully qualified domain name of your SQL Database server, as shown for the example: servername.database.windows.net. The server name is assigned by Azure when we create a SQL Database server in the Cloud. Database is the name of the database we want to work with. Substitute your values in the connection string accordingly;

Server=tcp:ld8xofkmo6.database.windows.net,1433;
Database=SOMDb;
UserID=somadmin@XXXXXXX;
Password=som123ABC;
Trusted_Connection=False;
Encrypt=True;
Connection Timeout=30”;


The below code is in the configuration file and sets the connection string.


  



We can retrieve the connection string from the configuration file using the ConfigurationManager class as shown.

SqlConnectionStringBuilder csBuilder;
csBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
After you have built your connection string, you can use the SQLConnection class to connect the SQL Database server:
SqlConnection conn = new SqlConnection(csBuilder.ToString());
conn.Open();