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())

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

// 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}",
// Update a record
command.CommandText =
"UPDATE T1 SET [FirstName]='change name 2' WHERE [Id]=2";

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

Console.WriteLine("Press enter to continue");
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.

No comments: