Arquitetura de Soluções

por Waldemir Cambiucci

Tech-Ed Brasil 2010 – Sessão sobre SQL Azure

Olá pessoal, tudo certo?

Hoje tivemos o segundo dia do Tech-Ed Brasil 2010 e na parte da manhã, fiz uma apresentação sobre o SQL Azure, o banco de dados relacional na plataforma Azure.

Para lembrar, temos 3 tipos de arquitetura de aplicações consumindo dados do SQL Azure, como vemos na figura a seguir:

image

Arquitetura Code Near – quando a aplicação e a base de dados estão sobre o Windows Azure. Assim, a aplicação é uma Web Role ou Worker Role, consumindo dados do SQL Azure.

Arquitetura Code Far – quando a aplicação está hospedada em ambiente local (on-premise) e consome dados hospedados no SQL Azure. Esse tipo de cenário é interessante para aplicações legadas que estão usando dados na nuvem, por exemplo.

Arquitetura Híbrida – quando a solução combina aplicação e dados entre o ambiente local e o ambiente na nuvem.

Cada cenário possui seus prós e contras, que devem ser avaliados de acordo com as necessidades da aplicação.

Entre os vários assuntos tratados, apresentei uma demo com diversas classes de conexão com o SQL Azure, usando: ODBC, ADO.NET, OLEDB, LINQ TO SQL, PHP e JAVA.

Como combinado, segue abaixo o link para a demo de conexão.

Conectando via ODBC…

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Data.Common;
   6: using System.Data.Odbc;
   7:  
   8: namespace ConnectDemoApp
   9: {
  10:     public class OdbcConnectionDemo : SQLAzureConnectionDemo
  11:     {
  12:         public OdbcConnectionDemo(string userName, string password, string dataSource, string databaseName) :
  13:             base(userName, password, dataSource, databaseName)
  14:         {
  15:         }
  16:  
  17:         protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName)
  18:         {
  19:             return new OdbcConnection(CreateOdbcConnectionString(userName, password, dataSource, databaseName));
  20:         }
  21:  
  22:         private string CreateOdbcConnectionString(string userName, string password, string dataSource, string databaseName)
  23:         {
  24:             string serverName = GetServerName(dataSource);
  25:  
  26:             OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder
  27:             {
  28:                 Driver = "SQL Server Native Client 10.0",
  29:             };
  30:             connectionStringBuilder["Server"] = "tcp:" + dataSource;
  31:             connectionStringBuilder["Database"] = databaseName;
  32:             connectionStringBuilder["Uid"] = userName + "@" + serverName;
  33:             connectionStringBuilder["Pwd"] = password;
  34:             return connectionStringBuilder.ConnectionString;
  35:         }
  36:  
  37:         protected override DbCommand CreateCommand(DbConnection connection)
  38:         {
  39:             return new OdbcCommand() { Connection = connection as OdbcConnection };
  40:         }
  41:     }
  42: }

Conectando via ADO.NET…

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Data.Common;
   6: using System.Data.SqlClient;
   7:  
   8: namespace ConnectDemoApp
   9: {
  10:     public class AdoConnectionDemo : SQLAzureConnectionDemo
  11:     {
  12:         public AdoConnectionDemo(string userName, string password, string dataSource, string databaseName)
  13:             : base(userName, password, dataSource, databaseName)
  14:         {
  15:         }
  16:  
  17:         protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName)
  18:         {
  19:             return new SqlConnection(CreateAdoConnectionString(userName, password, dataSource, databaseName));
  20:         }
  21:  
  22:         private string CreateAdoConnectionString(string userName, string password, string dataSource, string databaseName)
  23:         {
  24:             // create a new instance of the SQLConnectionStringBuilder
  25:             SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
  26:             {
  27:                 DataSource = dataSource,
  28:                 InitialCatalog = databaseName,
  29:                 Encrypt = true,
  30:                 TrustServerCertificate = false,
  31:                 UserID = userName,
  32:                 Password = password,
  33:             };
  34:  
  35:             return connectionStringBuilder.ToString();
  36:         }
  37:  
  38:         protected override DbCommand CreateCommand(DbConnection connection)
  39:         {
  40:             return new SqlCommand() { Connection = connection as SqlConnection };
  41:         }
  42:     }
  43: }

Conectando via LINQTOSQL…

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5:  
   6: namespace ConnectDemoApp
   7: {
   8:     public class LinqToSqlConnectionDemo
   9:     {
  10:         /// <summary>
  11:         /// AdventureWorksSqlAzureDataContext takes care of handling your transactions for you 
  12:         /// leaving you free you use Linq to extraxt information stored up in the cloud.
  13:         /// </summary>
  14:         public void ConnectToSQLAzureDemo()
  15:         {
  16:             HolTestDBEntities context = new HolTestDBEntities();
  17:  
  18:             // get all company names
  19:             IQueryable<string> emails = from customer in context.Customer
  20:                                               select customer.EmailAddress;
  21:  
  22:             // display these all on the console
  23:             foreach (string email in emails)
  24:             {
  25:                 Console.WriteLine(email);
  26:             }
  27:         }
  28:     }
  29: }

Conectando via OLEDB…

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Data.Common;
   6: using System.Data.OleDb;
   7:  
   8: namespace ConnectDemoApp
   9: {
  10:     public class OleDbConnectionDemo : SQLAzureConnectionDemo
  11:     {
  12:         public OleDbConnectionDemo(string userName, string password, string dataSource, string databaseName)
  13:             : base(userName, password, dataSource, databaseName)
  14:         {
  15:         }
  16:  
  17:         protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName)
  18:         {
  19:             return new OleDbConnection(CreateOleDBConnectionString(userName, password, dataSource, databaseName));
  20:         }
  21:  
  22:         private string CreateOleDBConnectionString(string userName, string password, string dataSource, string databaseName)
  23:         {
  24:             string serverName = GetServerName(dataSource);
  25:  
  26:             OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder
  27:             {
  28:                 Provider = "SQLOLEDB",
  29:                 DataSource = dataSource,
  30:             };
  31:             connectionStringBuilder["Initial Catalog"] = databaseName;
  32:             connectionStringBuilder["UId"] = userName + "@" + serverName;
  33:             connectionStringBuilder["Pwd"] = password;
  34:  
  35:             return connectionStringBuilder.ConnectionString;
  36:         }
  37:  
  38:         protected override DbCommand CreateCommand(DbConnection connection)
  39:         {
  40:             return new OleDbCommand() { Connection = connection as OleDbConnection };
  41:         }
  42:     }
  43: }

Conectando via JAVA…

   1: // Build a connection string
   2: String connectionUrl= "jdbc:sqlserver://server.database.windows.net;" +
   3:        "database=mydatabase;encrypt=true;user=user@server;password=*****";
   4:  
   5: // Next, make the sure the SQL Server Driver is loaded.
   6: Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   7:             
   8: // Then attempt to get a connection.  This will null or throw if we can't get a connection.
   9:  
  10: Connection sqlConn = DriverManager.getConnection(connectionUrl);
  11: if (sqlConn == null)
  12: {
  13:     System.out.println("Unable to obtain connection.  exiting");
  14:     System.exit(1);
  15: }
  16:  
  17: // Got a connection, do what you will
  18: // Free the connection 
  19: sqlConn.close();

Conectando via PHP…

   1: <?php
   2:   $host = "server.database.windows.net";
   3:   $dbname = "database";
   4:   $dbuser = "user@server";
   5:   $dbpwd = "password";
   6:   $driver = "{SQL Server Native Client 10.0}";
   7:  
   8:   // Build connection string  $dsn="Driver=$driver;Server=$host;Database=$dbname;Encrypt=true;TrustServerCertificate=true";
   9:   if (!($conn = @odbc_connect($dsn, $dbuser, $dbpwd))) {
  10:       die("Connection error: " . odbc_errormsg());
  11:   }
  12:  
  13:   // Got a connection, do what you will
  14:   // Free the connection
  15:   @odbc_close($conn);
  16: ?>

Bastante coisa!

Por enquanto é só! Até o próximo post 🙂

Waldemir.