Jak DataSet’a przenieść do MsSQL'a? (SQLServer Management Objects i SqlBulkCopy) [PL]

W tym artykule przedstawione zostanie jak można przenieść informacje z DataSet'a (układ i zawartość) do bazy danych opartej o Microsoft SQL Server (również w wersji Express). W tym celu zostaną wykorzystane SQLServer Management Objects (SMO) i SqlBulkCopy.

Ze wspomnianym problem spotkałem się już jakiś czas temu (przeczytajcie na devPytaniach: „Jak z DataSet’a wytworzyć bazę danych?”), ale dopiero teraz znalazłem chwilę czasu, aby rozwiązanie opisać.

Podstawy wykorzystania SQLServer Management Objects (SMO)

Referencje do bibliotek

Przed przystąpieniem do wykorzystania SMO w naszej aplikacji należy dodać do projektu odpowiednie referencje (poniżej przedstawiono lokalizacje domyślne):

• %programfiles%\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
• %programfiles%\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
• %programfiles%\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
• %programfiles%\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll

Później wskazujemy, z jakich dodatkowych przestrzeni adresowych będziemy korzystać:

• using Microsoft.SqlServer;
• using Microsoft.SqlServer.Server;
• using Microsoft.SqlServer.Management.Smo;
• using Microsoft.SqlServer.Management.Common;

Obiekt serwera

Aby podłączyć się do serwera SQL należy:

SqlConnection Connection = new SqlConnection( ConnectionString );
//SMO Server object setup with SQLConnection.
Server MySQLServer = new Server( new ServerConnection( Connection ) );

Obiekt bazy danych

Aby utworzyć bazę danych należy:

Database MyDataBase = new Database( MySQLServer, DataBaseName );
MyDataBase.Create();

Obiekt tabeli

Aby utworzyć tabelę w bazie danych należy:

Table myTable = new Table( MyDataBase, DataSetTable.TableName );
// ...
// Creation of Columns, Keys, etc...
//Create the Destination Table
myTable.Create();

Obiekt kolumny

W celu utworzenia kolumny należy:

  myColumn = new Column( myTable, dc.ColumnName );
  myColumn.DataType = GetDataType( dc.DataType.ToString(),dc.MaxLength );
  //GetDataType - created SQL Server DataType based on originall type and max length
  if ( dc.AutoIncrement )
  {
    myColumn.Identity = true;
    myColumn.IdentityIncrement = dc.AutoIncrementStep;
    myColumn.IdentitySeed = dc.AutoIncrementSeed;
  }
  myTable.Columns.Add( myColumn );

Obiekt indeksu (klucze dla tabeli)

Aby ustawić dla tabeli klucz główny należy:

Index index = new Index( myTable, "PrimaryID" + myTable.Name );
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
foreach ( DataColumn pk in DataSetTable.PrimaryKey )
{
  index.IndexedColumns.Add( new IndexedColumn( index, pk.ColumnName ) );
}
myTable.Indexes.Add( index );

Podstawy SqlBulkCopy

SqlBulkCopy pozwala na efektywne ładowanie danych do bazy danych z pliku XML lub DataSet'a.

Najpierw należy dodać referencję do Assembly System.Data.dll. Późniejsze wykorzystanie jest już bardzo proste:

// Bulk Copy loader
System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy( MyConnectionString );
//Destination table:
bulkCopy.DestinationTableName = String.Format( "\"{0}\"", dataSetTable.TableName );
try
{
  //writing to server
  bulkCopy.WriteToServer( dataSetTable );
}
catch ( Exception ex )
{
  MessageBox.Show( dataSetTable.TableName + ":" + ex.Message );
}
finally
{
  //closing bulk copy connection
  bulkCopy.Close();
}

Gotowy kod rozwiązujący tytułowy problem (DataSet -> MsSQL)

Na sam koniec zapraszam do zapoznania się z gotowym kodem rozwiązujący tytułowy problem, czyli przeniesienie informacji z DataSet'a (układ i zawartość tabel) do bazy danych:

  class DataSet2SMO
  {
    public static void CreateDataBase( string ConnectionString, string DataBaseName, DataSet SourceDataSet, bool LoadData )
    {
      SqlConnection Connection = new SqlConnection( ConnectionString );
      //SMO Server object setup with SQLConnection.
      Server MySQLServer = new Server( new ServerConnection( Connection ) );
      //Create a new SMO Database giving server object and database name
      Database MyDataBase = new Database( MySQLServer, DataBaseName );
      MyDataBase.Create();
      foreach ( DataTable dt in SourceDataSet.Tables )
      {
        CreateDataTable( MySQLServer.Databases[ DataBaseName ], dt );
        if ( LoadData )
        {
          //loading Data (if required)
          string NewConnectionString = String.Format( "{0};Initial Catalog={1};", ConnectionString, DataBaseName );
          // Bulk Copy loader
          System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy( NewConnectionString );
          //Destination table:
          bulkCopy.DestinationTableName = String.Format( "\"{0}\"", dt.TableName );
          try
          {
            //writing to server
            bulkCopy.WriteToServer( dt );
          }
          catch ( Exception ex )
          {
            MessageBox.Show( dt.TableName + ":" + ex.Message );
          }
          finally
          {
            //closing bulk copy connection
            bulkCopy.Close();
          }
        }
      }
    }
    private static void CreateDataTable( Database MyDataBase, DataTable DataSetTable )
    {
      Table myTable = new Table( MyDataBase, DataSetTable.TableName );
      //SMO Column object referring to destination table.
      Column myColumn;
      //Add the column names and types from the datatable into the new table
      //Using the columns name and type property
      foreach ( DataColumn dc in DataSetTable.Columns )
      {
        //Create columns from datatable column schema
        myColumn = new Column( myTable, dc.ColumnName );
        myColumn.DataType = GetDataType( dc.DataType.ToString(), dc.MaxLength );
        //GetDataType - created SQL Server DataType based on originall type and max length
        if ( dc.AutoIncrement )
        {
          myColumn.Identity = true;
          myColumn.IdentityIncrement = dc.AutoIncrementStep;
          myColumn.IdentitySeed = dc.AutoIncrementSeed;
        }
        myTable.Columns.Add( myColumn );
      }
      //Create a primary key index
      Index index = new Index( myTable, "PrimaryID" + myTable.Name );
      index.IndexKeyType = IndexKeyType.DriPrimaryKey;
      foreach ( DataColumn pk in DataSetTable.PrimaryKey )
      {
        index.IndexedColumns.Add( new IndexedColumn( index, pk.ColumnName ) );
      }
      myTable.Indexes.Add( index );
      //Create the Destination Table
      myTable.Create();
    }
    private static DataType GetDataType( string dataType, int MaxLength )
    {
      DataType DTTemp = null;

      switch ( dataType )
      {
        case "System.Decimal":
          DTTemp = DataType.Decimal( 2, 18 );
          break;
        case ( "System.String" ):
          DTTemp = DataType.VarChar( MaxLength );
          break;
        case "System.Int16":
        case "System.Int32":
        case "System.UInt16":
        case "System.UInt32":
          DTTemp = DataType.Int;
          break;
        case "System.Double":
        case "System.Single":
          DTTemp = DataType.Real;
          break;
        case "System.Int64":
          DTTemp = DataType.BigInt;
          break;
        case "System.DateTime":
          DTTemp = DataType.DateTime;
          break;
      }
      return DTTemp;
    }
  }

I przykład wykorzystania:

MySchema myDataSet = new MySchema();
myDataSet.ReadXml( xmlfilename );
string connectionstring = @"Data Source=localhost\SQLEXPRESS;Integrated Security=True";
DataSet2SMO.CreateDataBase( connectionstring, "MyNewDatabase", myDataSet, true );

Źródła i dalsze informacje na ten temat

Zapraszam również do zapoznania się ze źródłami, na podstawie których powstało niniejsze opracowanie:

• http://devpytania.pl/questions/2693/jak-z-dataseta-wytworzyc-baze-danych
• http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3/
• http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx
• http://www.codeproject.com/KB/database/DataTableToDataBase.aspx

Czy to już wszystko?

Na pewno to dopiero podstawowe informacje o SMO i SqlBulkCopy. Nie jest to też przedstawienie pełnego rozwiązania. Rozwinąć można również temat mapowania typów danych. Nie uwzględniłem powiązań między tabelami i kluczy obcych. Przyznam, że nie potrzebowałem akurat takiej funkcjonalności, ale może ktoś inny zaproponuje co tu można jeszcze zrobić. W każdym razie wydaje mi się, że zaprezentowane rozwiązanie wystarczy w wielu przypadkach.