A common question comes to mind; how to work with ADO.Net and SQL Azure? In this article, I am trying to simplify this.
Assume we have a database called School at local SQL Server and as well as at SQL Azure. Essentially we only need to change the connection string in the 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 a local database or a SQL Azure Database. We are going to bind records of a Person table from a School database. The first step is to create an entity class corresponding to the Person table. The 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; }
}
}
The next step we need to do is to add a connectionstring for the local database. Open the Web.Config file and add a connection string as below:
This is the connection string for the local database. I have given it the name "LocalConnectionString".
And we will write some very usual code 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["CloudconnectionString1"].ConnectionString;
}
}
}
In the above code, one function is GetConnectionString() that needs an explanation because we need to change the connection string name here to get data from cloud whereas others are a very usual ADO.Net code and you can get plenty of explanation about them in many other places. So I am not explaining that.
We are simply providing connection string name to be fetched from config file.
In the next step which is a very important step is to create a connection string to connect with the database in SQL Azure.
Connection String for SQL Azure
Above is the connection string for a database in SQL Azure. The point to keep in mind is about TCP connections. We connect with the SQL Azure database server using a 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 needs 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 a connection string in Web.Config file for SQL Azure as below:
As the last step we need to modify GetConnectionString() and change it as below:
In this article, I discussed how we can use SQL Azure and ADO.Net together.