Accessing SQL Azure Using ADO.NET

by
In my first post on SQL Azure (SQL Azure: A First Contact) I talked about how you can access SQL Azure using SQL Server 2008 Management Studio (SSMS).

In this post, the journey continues with “walking” on the cloud using ADO .NET 🙂

Here’s the recipe!

Ingredients

  • A valid SQL Azure CTP account
  • Visual Studio 2008 SP1
  • .NET Framework 3.5 SP1
  • C# (or respective VB.NET) namespace: System.Data.SqlClient

Objects used from System.Data.SqlClient namespace:

  • SqlConnection
  • SqlCommand
  • SqlDataReader
  • SqlDataAdapter

Process

  • Step 1: Add namespace for using SqlClient
  • Step 2: Set up connection to database
  • Step 3: Open database connection
  • Step 4: Set the T-SQL command(s) – Use the “SqlCommand” object
  • Step 5: Open the SqlDataReader – Execute the T-SQL command(s)
  • Step 6: Display the results (if any)
  • Step 7: Close the SqlDataReader
  • Step 8: Close the database connection

Source Code (for obvious reasons I removed the connection string from the source code file)

  • You can find the sample source code for this post here.
  • *Note: This sample code is for demo purposes and does not represent production code.

Considerations

  • My example is based on the example in my previous post on SQL Azure (Database Name: sqlazure, table name: CLOUD_MSGS)
  • For this post I used a new Visual C# “Console Application” project named ADO .NET – SQL Azure like in the following screenshot:

Screenshot of the output


Have fun!

Drop me a comment if you need any help implementing the ADO.NET connection to SQL Azure!

Views: 1,260

2 thoughts on “Accessing SQL Azure Using ADO.NET”

  1. I am not able to connect to SQL Azure.

    Here is what my code is:

    using (SqlConnection conn = new SqlConnection("Server=ufh1cinawq.database.windows.net;Database=MASTER;User ID=vksingh24;Password=xxx;Trusted_Connection=False"))
    {
    using (SqlCommand command = conn.CreateCommand())
    {
    conn.Open();

    // Create a table
    command.CommandText = "CREATE TABLE Vikash(Col1 int primary key, Col2 varchar(20))";
    command.ExecuteNonQuery();

    // Insert sample records
    command.CommandText = "INSERT INTO Vikash(col1, col2) values (1, 'Vikash Kumar Singh'), (2, 'Kumar Saurabh'), (3, 'RockStar')";
    int rowsAdded = command.ExecuteNonQuery();

    // Query the table and print the results
    command.CommandText = "SELECT * FROM Vikash";

    using (SqlDataReader reader = command.ExecuteReader())
    {
    // Loop over the results
    while (reader.Read())
    {
    MessageBox.Show(reader["Col1"].ToString().Trim());
    MessageBox.Show(reader["Col2"].ToString().Trim());
    }
    reader.Close();
    }

    conn.Close();
    }
    }

    I also check the firewall setting. Please help me to connect to SQL Azure.

  2. Hi,

    You need to properly configure the Firewall settings from within the SQL Azure – Server Administration web page.

    To this end you can add a new Firewall rule, give it a name you like, and in the IP range include your public IP (the "Add Firewall Rule" dialog displays your public IP).

    Then wait for a few minutes and try again.

    I have also noticed that in the connection string you are using the "master" database and then you are trying to create a new table in it. Note that you cannot create a table in the master database as it is a system database. You can create a new database instead and work with it.

    Hope this helps!

    Cheers,
    Artemakis

Comments are closed.