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.

Autor:Maciej Zbrzezny
Źródło:http://maciej-progtech.blogspot.com/

Ze wspomnianym problem spotkałem się już jakiś czas temu (przeczytajcie na devPytaniach: http://devpytania.pl/questions/2693/jak-z-dataseta-wytworzyc-baze-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.