Accessing SQL Azure Using ADO.NET

Accessing SQL Azure Using ADO.NET

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

2 thoughts on “Accessing SQL Azure Using ADO.NET

  1. Bablu Singh

    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. Artemakis Artemiou [MVP]

    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