Exercise 4: Connecting via Client Libraries

หน้าแรก windows azure Exercise 4: Connecting via Client Libraries

Task 1 Connect to SQL Azure via Microsoft Technologies

In this task, you will create a console application, which will use four different Microsoft technologies (ADO.NET, ODBC, OleDB and the Entity Framework) to connect to your SQL Azure database, and perform some simple T-SQL operations. If you have not completed the previous exercise, please do now.

You will see that fundamentally the way in which you interact with your SQL Azure database from within your applications is the same as a traditional SQL database. The main difference between using these four technologies lies in the connection strings used to connect. After a connection is established, you can then use the appropriate inheritor of the DbCommand to issue your commands to SQL Azure.

Navigate to the following destination %TrainingKitInstallationFolder%LabsIntroToSqlAzureEx04-ConnectingViaClientLibraries begin Select either the C# folder or the VB folder and open the solutionConnectDemoApp. A visual studio solution with the following structure should open.
Figure 1 Configuration File
Now Create a new class called SQLAzureConnectionDemo . This will be the class which contains the main query execution logic. In its constructor we it will build connection strings for each connection type. Its methods will be primarily concerned with executing commands.
Figure 2 Adding a new class
Make this class public by adding the public access modifier.

Add the following using statements to the top of your SQLAzureConnectionDemo class

(Code Snippet Intro to SQL Azure - Ex04 Usings C#)

using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

(Code Snippet Intro to SQL Azure - Ex04 Usings VB)

Visual Basic

Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Data.Common
Imports System.Text

Add the following code to the top of your SQLAzureConnectionDemo class

(Code Snippet Intro to SQL Azure - Ex04 SQLAzureConnectionDemo ctor C#)

public string ADO_ConnectionString { get; private set; }
public string ODBC_ConnectionString { get; private set; }
public string OleDb_ConnectionString { get; private set; }

public SQLAzureConnectionDemo(string userName, string password, string dataSource, string databaseName)
{
      ADO_ConnectionString = CreateADO_ConnectionString(userName, password, dataSource, databaseName);
      ODBC_ConnectionString = CreateODBC_ConnectionString(userName, password, dataSource, databaseName);
      OleDb_ConnectionString = CreateOleDB_ConnectionString(userName, password, dataSource, databaseName);
}

(Code Snippet Intro to SQL Azure - Ex04 SqlAzureConnectionDemo ctor VB)

Visual Basic

    Private _adoConnectionString As String
    Private _odbcConnectionString As String
    Private _oleDbConnectionString As String

    Public Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String)
        Me.ADOConnectionString = Me.CreateADOConnectionString(userName, password, dataSource, databaseName)
        Me.ODBCConnectionString = Me.CreateOBDCConnectionString(userName, password, dataSource, databaseName)
        Me.OleDbConnectionString = Me.CreateOleDBConnectionString(userName, password, dataSource, databaseName)
    End Sub

    Public Property ADOConnectionString() As String
        Get
            Return Me._adoConnectionString
        End Get
        Private Set(ByVal value As String)
            Me._adoConnectionString = value
        End Set
    End Property

    Property ODBCConnectionString() As String
        Get
            Return Me._odbcConnectionString
        End Get
        Private Set(ByVal value As String)
            Me._odbcConnectionString = value
        End Set
    End Property

    Property OleDbConnectionString() As String
        Get
            Return Me._oleDbConnectionString
        End Get
        Private Set(ByVal value As String)
            Me._oleDbConnectionString = value
        End Set
    End Property

Bellow this add following method definition. This method is responsible for building up the ODBC Drivers connection string. It is using SQL Server Native Client 10.0 as its driver. You can specify any ODBC driver you wish here.

(Code Snippet Intro to SQL Azure - Ex04 CreateODBC method C#)

private string CreateODBC_ConnectionString(string userName, string password, string dataSource, string databaseName)
{

    string serverName = GetServerName(dataSource);

    OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder
    {
        Driver = "SQL Server Native Client 10.0",

    };
    connectionStringBuilder["Server"] = "tcp:" + dataSource;
    connectionStringBuilder["Database"] = databaseName;
    connectionStringBuilder["Uid"] = userName + "@" + serverName;
    connectionStringBuilder["Pwd"] = password;
    return connectionStringBuilder.ConnectionString;
}

(Code Snippet Intro to SQL Azure - Ex04 CreateODBC method VB)

Visual Basic

    Private Function CreateOBDCConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String
        Dim serverName As String = Me.GetServerName(dataSource)

        Dim connectionStringBuilder As New OdbcConnectionStringBuilder
        connectionStringBuilder.Driver = "SQL Server Native Client 10.0"
        connectionStringBuilder.Item("Server") = ("tcp:" & dataSource)
        connectionStringBuilder.Item("Database") = databaseName
        connectionStringBuilder.Item("Uid") = (userName & "@" & serverName)
        connectionStringBuilder.Item("Pwd") = password

        Return connectionStringBuilder.ConnectionString
    End Function

Add the following method definition to your SQLAzureConnectionDemo. This method is responsible for building an OleDB connection string.

(Code Snippet Intro to SQL Azure - Ex04 CreateOleDB method C#)

private string CreateOleDB_ConnectionString(string userName, string password, string dataSource, string databaseName)
{
    string serverName = GetServerName(dataSource);

    OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder
    {
        Provider = "SQLOLEDB",
        DataSource = dataSource,

    };
    connectionStringBuilder["Initial Catalog"] = databaseName;
    connectionStringBuilder["UId"] = userName + "@" + serverName;
    connectionStringBuilder["Pwd"] = password;

    return connectionStringBuilder.ConnectionString;
}

(Code Snippet Intro to SQL Azure - Ex04 CreateOleDB method C#)

Visual Basic

    Private Function CreateOleDBConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String
        Dim serverName As String = Me.GetServerName(dataSource)
        Dim connectionStringBuilder As New OleDbConnectionStringBuilder

        connectionStringBuilder.Provider = "SQLOLEDB"
        connectionStringBuilder.DataSource = dataSource
        connectionStringBuilder.Item("Initial Catalog") = databaseName
        connectionStringBuilder.Item("UId") = (userName & "@" & serverName)
        connectionStringBuilder.Item("Pwd") = password

        Return connectionStringBuilder.ConnectionString
    End Function

Add this method to your SQLAzureConnectionDemo class.

(Code Snippet Intro to SQL Azure - Ex04 CreateADO method C#)

private string CreateADO_ConnectionString(string userName, string password, string dataSource, string databaseName)
{
    // create a new instance of the SQLConnectionStringBuilder
    SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
    {
        DataSource = dataSource,
        InitialCatalog = databaseName,
        Encrypt = true,
        TrustServerCertificate = false,
        UserID = userName,
        Password = password,

    };
    return connectionStringBuilder.ToString();
}

(Code Snippet Intro to SQL Azure - Ex04 CreateADO method VB)

Visual Basic

Private Function CreateADOConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String

        Dim connectionStringBuilder As New SqlConnectionStringBuilder
        connectionStringBuilder.DataSource = dataSource
        connectionStringBuilder.InitialCatalog = databaseName
        connectionStringBuilder.Encrypt = True
        connectionStringBuilder.TrustServerCertificate = False
        connectionStringBuilder.UserID = userName
        connectionStringBuilder.Password = password

        Return connectionStringBuilder.ToString
    End Function

Finally add this helper method.
(Code Snippet Intro to SQL Azure - Ex04 GetServerName method C#)
C#
```
private string GetServerName(string dataSource)
{
     return dataSource.Split('.')[0];
}
```
(Code Snippet Intro to SQL Azure - Ex04 GetServerName method VB)
Visual Basic
```
Private Function GetServerName(ByVal dataSource As String) As String
    Return dataSource.Split(New Char() {"."c})(0)
End Function
```
Now that you have taken care of how to build a connection string for ADO.NET, ODBC and OleDB, you are ready to add the main query executing logic to this class.

For each technology we will create a new DemoTable add some test data to this table, then read this data back from the cloud, and finally drop the table again. Add the following method to your SQLAzureConnectionDemoclass.

(Code Snippet Intro to SQL Azure - Ex04 CreateDemoTableStatement method C#)

public void ExecuteCreateDemoTableStatement(DbCommand command)
        {
            Console.WriteLine("Creating DemoTable..");
            command.CommandText = "CREATE TABLE DemoTable(DemoId int primary key, DemoName varchar(20))";
            command.ExecuteNonQuery();
        }

(Code Snippet Intro to SQL Azure - Ex04 CreateDemoTableStatement VB)

Visual Basic

Public Sub ExecuteCreateDemoTableStatement(ByVal command As DbCommand)
        Console.WriteLine("Creating DemoTable..")
        command.CommandText = "CREATE TABLE DemoTable(DemoId int primary key, DemoName varchar(20))"
        command.ExecuteNonQuery()
    End Sub

Add the following code to your SQLAzureConnectionDemo class. This will insert test data to your DemoTable.

(Code Snippet Intro to SQL Azure - Ex04 InsertTestData method C#)

public void ExecuteInsertTestDataStatement(DbCommand command)
{
    Console.WriteLine("Adding some test data..");

    for (int data = 0; data < 5; data++)
    {
        string commandText = string.Format("INSERT INTO DemoTable (DemoId, DemoName) values ({0}, 'Demo {0}')", data);
        Console.WriteLine(commandText);
        command.CommandText = commandText;
        command.ExecuteNonQuery();
    }

    Console.WriteLine("Done..");
    Console.WriteLine("Press any key to read back your data from the cloud..");
    Console.ReadKey();
}

(Code Snippet Intro to SQL Azure - Ex04 InsertTestData method VB)

Visual Basic

    Public Sub ExecuteInsertTestDataStatement(ByVal command As DbCommand)
        Console.WriteLine("Adding some test data..")
        Dim data As Integer
        For data = 0 To 5 - 1
            Dim commandText As String = String.Format("INSERT INTO DemoTable (DemoId, DemoName) values ({0}, 'Demo {0}')", data)
            Console.WriteLine(commandText)
            command.CommandText = commandText
            command.ExecuteNonQuery()
        Next data
        Console.WriteLine("Done..")
        Console.WriteLine("Press any key to read back your data from the cloud..")
        Console.ReadKey()
    End Sub

Add the following code to read your newly inserted data

(Code Snippet Intro to SQL Azure - Ex04 ReadInsertedData method C#)

public void ExecuteReadInsertedTestData(DbCommand command)
{
    string selectText = "SELECT * FROM DemoTable";
    Console.WriteLine("Reading Data..");
    Console.WriteLine(selectText);
    command.CommandText = selectText;
    ReadData(command.ExecuteReader());
}
private void ReadData(IDataReader reader)
{
    // loop over the results and write them out to the console
    while (reader.Read())
    {
        StringBuilder row = new StringBuilder();
        for (int col = 0; col < reader.FieldCount; col++)
        {
            row.Append(reader.GetName(col) + ":" + reader.GetValue(col) + "  |  ");
       }
        Console.WriteLine(row.ToString());
    }
    reader.Close();
}

(Code Snippet Intro to SQL Azure - Ex04 ReadInsertedData method VB)

Visual Basic

    Public Sub ExecuteReadInsertedTestData(ByVal command As DbCommand)
        Dim selectText As String = "SELECT * FROM DemoTable"
        Console.WriteLine("Reading Data..")
        Console.WriteLine(selectText)
        command.CommandText = selectText
        Me.ReadData(command.ExecuteReader)
    End Sub

    ''' <summary>
    ''' Reads data and out puts the results to the console.
    ''' </summary>
    ''' <param name="reader"></param>
    ''' <remarks></remarks>
    Private Sub ReadData(ByVal reader As IDataReader)
        Do While reader.Read
            Dim row As New StringBuilder
            Dim col As Integer
            For col = 0 To reader.FieldCount - 1
                row.Append(String.Concat(New Object() {reader.GetName(col), ":", reader.GetValue(col), "  |  "}))
            Next col
            Console.WriteLine(row.ToString)
        Loop
        reader.Close()
    End Sub

Add the following method to drop your DemoTable

(Code Snippet Intro to SQL Azure - Ex04 DropDemoTable method C#)

public void ExecuteDropDemoTable(DbCommand command)
{
    Console.WriteLine("Removing DemoTable..");

    command.CommandText = "DROP TABLE DemoTable";
    command.ExecuteNonQuery();
}

(Code Snippet Intro to SQL Azure - Ex04 DropDemoTable method VB)

Visual Basic

ขึ้นไปด้านบน

Exercise 4: Connecting via Client Libraries

Task 1  Connect to SQL Azure via Microsoft Technologies

Task 1 Connect to SQL Azure via Microsoft Technologies