รับทำเว็บไซต์ครบวงจร
เลือกภาษา Thai English     facebook
รายการหลัก



บทความ


Page Ranking Tool

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.

  1. 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

  2. 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

  3. Make this class public by adding the public access modifier.
  4. Add the following using statements to the top of your SQLAzureConnectionDemo class

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

    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
    
  5. Add the following code to the top of your SQLAzureConnectionDemo class

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

    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
    
  6. 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#)

    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
    
  7. 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#)

    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
    
  8. Add this method to your SQLAzureConnectionDemo class.

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

    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
    
  9. 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.

  10. 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#)

    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
    
  11. 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#)

    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
    
  12. Add the following code to read your newly inserted data

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

    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
    
  13. Add the following method to drop your DemoTable

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

    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
    
    Short URL click!
    << กลับคืน : เข้าชม 3,352 ครั้ง : ขึ้นไปด้านบน

รับทำเว็บไซต์ รับสร้างเว็บไซต์ รับออกแบบเว็บ รับเขียนเว็บ รับสอนทำเว็บ รับเช่า hosting รับเช่าพื้นที่เว็บไซต์ จดชื่อเว็บ รับโปรโมทเว็บไซต์ รับดูแลเว็บ SiteMap
สปริงเกอร์http://www.xn--22c2c4blb9n.xn--o3cw4h/