SQL Data Services (SDS) Part III

In my previous article SQL Data Services (SDS) Part II, we discussed how to create database and tables with the help of a query in SQL Server Management Studio (SSMS).

While writing the application we have the choice of using ADO.NET provider or SQL Server 2008 ODBC driver. In this example, we are using ADO.NET in the code snippet.

Following code is for a console application which creates a table named T1 with 3 columns in it.

// Provide the following information to connect to server
private static string userName = "<administrator name>";
private static string password = "<password>";
private static string dataSource = "<data source name";
// data source will be server name we gave for SSMS query in last article
private static string sampleDatabaseName = "<name of database we created>";

static void Main(string[] args)on we hav
{

    // Create a connection string for the existing database
    SqlConnectionStringBuilder connStringBuilder;
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dataSource;
    connStringBuilder.InitialCatalog = sampleDatabaseName;
    connStringBuilder.Encrypt = true;
    connStringBuilder.TrustServerCertificate = true;
    connStringBuilder.UserID = userName;
    connStringBuilder.Password = password;

    // Connect to the existing database, create table and insert records
    using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (SqlCommand command = conn.CreateCommand())
        {
            conn.Open();

            // Create a table
            command.CommandText = "CREATE TABLE T1([id] int primary key," +
                "FirstName varchar(20), LastName varchar(20))";
            command.ExecuteNonQuery();

            // Insert sample records
            command.CommandText = "INSERT INTO T1 ([id],FirstName, LastName)" +
            "values (1, 'Name 1','Last1'), (2, 'Name 2','Last2')," +
            " (3, 'Name 3','Last3')";
            int rowsAdded = command.ExecuteNonQuery();

            // Query table and view data in while loop
            command.CommandText = "SELECT * FROM T1";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Id: {0}, First Name: {1}, Last Name: {2}",
                                    reader["Id"].ToString(),
                                    reader["FirstName"].ToString(),
                                    reader["LastName"].ToString());
                }
            // Update a record
            command.CommandText =
                "UPDATE T1 SET [FirstName]='change name 2' WHERE [Id]=2";
            command.ExecuteNonQuery();

            // Delete a record
            command.CommandText = "DELETE FROM T1 WHERE [Id]=1";
            command.ExecuteNonQuery();

            }
        }
    }
    Console.WriteLine("Press enter to continue");
    Console.ReadLine();
 }

After you run this application, you can verify the creation of table, insertion, modification and deletion of records by connecting to SQL Azure with the help of SQL Server Management Studio as discussed in last article.