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.
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.
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 );
myTable.Create();
Obiekt kolumny
W celu utworzenia kolumny należy:
myColumn = new Column( myTable, dc.ColumnName );
myColumn.DataType = GetDataType( DataType.ToString(),dc.MaxLength );
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:
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.
Zapraszam również do odwiedzenia mojego bloga: http://maciej-progtech.blogspot.com/.