SQL Server Management Objects (SMO) zbiór bibliotek, a w nich klas dla platformy .NET, pozwalające na łatwe i proste zarządzanie bazą danych Microsoft SQL Server z poziomu kodu programu .NET (np. w językach takich jak C#, VB i innych). Zostały one wprowadzone po raz pierwszy z bazą danych Microsoft SQL Server 2005 (włącznie z wersją Express). Dostępne są również biblioteki SMO dla bazy danych MS SQL 2008.
Dzięki wykorzystaniu SMO, można opracowywać aplikacji o funkcjonalności podobnej do Microsoft SQL Server Management Studio.
Wpisy związane z SQLServer Management Objects (SMO), pojawiały się już kilka razy na tym blogu:
Wpisy te, oprócz samego SMO dotyczyły również zagadnienia, które łatwo można zawrzeć w pytaniu: „Jak DataSet’a przenieść do MsSQL'a?”. Teraz w ramach pewnego podsumowania chciałbym przedstawić kod źródłowy klasy (bazujący na fragmentach prezentowanych wcześniej), realizujący wytworzenie bazy danych na bazie DataSet'a. Ponieważ wydaje mi się, że ważniejsze fragmenty były już wcześniej opisywane we wspomnianych wpisach, dlatego tym razem zamieszczę go już bez dalszych komentarzy:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
public class DataSet2SMO
{
/// <summary>
/// Creates the database based on DataSet.
/// </summary>
/// <param name="ConnectionString">The connection string to SQL Server
/// (this connection string should not include the database).</param>
/// <param name="DataBaseName">Name of the database.</param>
/// <param name="SourceDataSet">The source DataSet (including data).</param>
/// <param name="LoadData">if set to <c>true</c> source data is loaded to new data base;
/// if set to <c>false</c> only structure is created but no data is loaded.</param>
/// <param name="RecreateTablesIfExists">if set to <c>true</c> the table is recreated if exists; .</param>
public static void CreateDataBase(
string ConnectionString,
string DataBaseName,
DataSet SourceDataSet,
bool LoadData,
bool RecreateTablesIfExists )
{
using ( 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;
if ( MySQLServer.Databases.Contains( DataBaseName ) )
{
MyDataBase = MySQLServer.Databases[ DataBaseName ];
}
else
{
MyDataBase = new Database( MySQLServer, DataBaseName );
MyDataBase.Create();
}
Dictionary<string, Table> TableDictionary = new Dictionary<string, Table>();
foreach ( DataTable dt in SourceDataSet.Tables )
{
TableDictionary.Add( dt.TableName, CreateDataTable( MyDataBase, dt, RecreateTablesIfExists, null ) );
if ( LoadData )
{
//loading Data (if required)
string NewConnectionString = String.Format( "{0};Initial Catalog={1};", ConnectionString, DataBaseName );
// Bulk Copy loader
SqlBulkCopyOptions option = SqlBulkCopyOptions.Default ^ SqlBulkCopyOptions.KeepIdentity;
using ( SqlBulkCopy bulkCopy =
new SqlBulkCopy( NewConnectionString, option ) )
{
//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 );
}
}
}
}
////////////////
//foreign keys and constraints:
foreach ( var kvp_table in TableDictionary )
{
CreateForeignKeysBaseOnDataTableRelations( SourceDataSet,
kvp_table.Value.Name, kvp_table.Value );
}
foreach ( var kvp_table in TableDictionary )
{
CreateConstraintsBaseOnDataTableConstraints( SourceDataSet,
kvp_table.Value.Name, kvp_table.Value );
}
}
}
/// <summary>
/// Creates the foreign keys base on data table relations.
/// </summary>
/// <param name="SourceDataSet">The source DataSet that cotains:
/// tables, relations and data.</param>
/// <param name="TableName">Name of the table in DataSet.</param>
/// <param name="TableInSqlServer">The table object (SMO) in SQL server.</param>
private static void CreateForeignKeysBaseOnDataTableRelations
( DataSet SourceDataSet, string TableName, Table TableInSqlServer )
{
DataTable DataSetTable = SourceDataSet.Tables[ TableName ];
foreach ( DataRelation datarel in DataSetTable.ParentRelations )
{
ForeignKey FK = new ForeignKey( TableInSqlServer, datarel.RelationName );
for ( int i = 0; i < datarel.ChildColumns.Length; i++ )
{
ForeignKeyColumn fkc =
new ForeignKeyColumn( FK, datarel.ChildColumns[ i ].ColumnName, datarel.ParentColumns[ i ].ColumnName );
FK.Columns.Add( fkc );
}
FK.ReferencedTable = datarel.ParentTable.TableName;
FK.Create();
}
}
/// <summary>
/// Creates the constraints base on data table constraints.
/// </summary>
/// <param name="SourceDataSet">The source data set.</param>
/// <param name="TableName">Name of the table.</param>
/// <param name="TableInSqlServer">The table in SQL server.</param>
private static void CreateConstraintsBaseOnDataTableConstraints
( DataSet SourceDataSet, string TableName, Table TableInSqlServer )
{
DataTable DataSetTable = SourceDataSet.Tables[ TableName ];
foreach ( Constraint con in DataSetTable.Constraints )
{
if ( con is UniqueConstraint )
{
UniqueConstraint ucon = (UniqueConstraint)con;
if ( !ucon.IsPrimaryKey ) //there is no need to duplicate the indes
{
Index newIndex = new Index( TableInSqlServer, String.Format( "IX_{0}_{1}", TableName, con.ConstraintName ) );
newIndex.IndexKeyType = IndexKeyType.DriUniqueKey;
foreach ( DataColumn dc in ucon.Columns )
{
newIndex.IndexedColumns.Add( new IndexedColumn( newIndex, dc.ColumnName, false ) );
}
newIndex.Create();
}
}
}
}
private static Table CreateDataTable( Database MyDataBase, DataTable DataSetTable, bool RecreateTableIfExists, string SchemaName )
{
if ( RecreateTableIfExists )
{
DropTable( MyDataBase, DataSetTable.TableName, SchemaName );
}
Table myTable = new Table( MyDataBase, DataSetTable.TableName );
if ( !string.IsNullOrEmpty( SchemaName ) )
myTable.Schema = SchemaName;
//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();
return myTable;
}
/// <summary>
/// It removes all foreign keys that reference the table to be dropped
/// and then drops the table.
/// </summary>
/// <param name="MyDataBase">The database object (SMO)</param>
/// <param name="TableName">Name of the table to be dropped.</param>
/// <param name="SchemaName">Name of the schema that includes the table to be dropped.</param>
private static void DropTable( Database MyDataBase, string TableName, string SchemaName )
{
if ( MyDataBase.Tables.Contains( TableName ) )
{
foreach ( Table t in MyDataBase.Tables )
{
ForeignKey[] fkArray = new ForeignKey[ t.ForeignKeys.Count ];
t.ForeignKeys.CopyTo( fkArray, 0 );
foreach ( ForeignKey fk in fkArray )
if ( fk.ReferencedTable == TableName &&
( fk.ReferencedTableSchema == SchemaName || string.IsNullOrEmpty( SchemaName ) ) )
fk.Drop();
}
MyDataBase.Tables[ TableName ].Drop();
}
}
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;
}
}
Maciej_Zbrzezny on delicious.com
Posty
Brak komentarzy:
Prześlij komentarz