Dhananjay Kumar

A common question comes in our mind that, how to work with ADO.Net and SQL Azure? In this post, I am trying to simplify this.

Assume we do have a database called School at local SQL Server and as well as at SQL Azure. Essentially we need to change only connection string in config file to connect with either local database or cloud database respectively. It is very simple, isn’t it?

We have a GridView and we are going to bind data either from local database or SQL Azure Database. We are going to bind records of Person table from School database. First step, I have created an entity class correspondent to Person table. Class is as below,

Person.cs

namespace ADONetExample
{
 public class Person
 {
 public string firstName { get; set; }
 public string lastName { get; set; }
 public string personId { get; set; }

 }
}

Next step we need to add connectionstring for local database. Open Web.Config file and add connection string as below,

This is connection string for local database. I have given the name here “LocalConnectionString”.

And a very usual code we will write to fetch the data and bind to the Gridview.

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
namespace ADONetExample
{
 public partial class _Default : System.Web.UI.Page
 {
 List<Person> lstPersons;
 protected void Page_Load(object sender, EventArgs e)
 {
 lstPersons = new List<Person>();
 using (SqlConnection connection = new SqlConnection(GetConnectionString()))
 {
 using (SqlCommand command = connection.CreateCommand())
 {
 connection.Open();
 command.CommandText = "select * from person";
 using (SqlDataReader reader = command.ExecuteReader())
 {
 while(reader.Read())
 {
 lstPersons.Add(new Person(){firstName= reader["FirstName"] as string,
 lastName = reader["LastName"] as string,
 personId = reader["PersonID"].ToString() });

 }
 }

}

}

GridView1.DataSource = lstPersons.ToList();
 GridView1.DataBind();
 }
 private string GetConnectionString()
 {
 return System.Configuration.ConfigurationManager.ConnectionStrings["CloudconnectionString"].ConnectionString;
 }
 }
}

In above code, one function GetConnectionString() needs special attention because we need to change connection string name here to get data from cloud whereas others are a very usual ADO.Net code and you will get much explanation about them at many places. So I am not explaining that.

We are simply providing connection string name to be fetched from config file.

Next step and very important step is to create connection string to connect with database in SQL Azure.

Connection String for SQL Azure

Above is the connection string for database in SQL Azure. Point to keep in mind is about TCP connection. We connect with SQL Azure database server using TCP connection. The second important point is USER ID. Make sure you are providing a space in between User and Id. On the other hand user name need to be provided as username@servername .Make sure you are not appending .database.windows.net with server name while providing User Id.

We can add connection string in Web.Config file for SQL Azure as below,

As last step we need to modify GetConnectionString() and change it as below ,

In this article , I discussed how could be use SQL Azure and ADO.Net together.