public static Table AddPrimaryKey(this Table table, string name, DataType dataType, bool isIdentityColumn = false, bool isClusteredIndex = true)
{
var column = new Column(table, name, dataType);
column.Nullable = false;
if (isIdentityColumn)
{
column.Identity = true;
column.IdentitySeed = 1;
column.IdentityIncrement = 1;
}
else if (dataType.Equals(DataType.UniqueIdentifier))
{
column.AddDefaultConstraint();
column.DefaultConstraint.Text = "NEWID()";
}
table.Columns.Add(column);
// Create the primary key index.
var index = new Index(table, "PK_" + table.Name);
index.IsClustered = isClusteredIndex;
index.IsUnique = true;
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
index.IndexedColumns.Add(new IndexedColumn(index, column.Name));
table.Indexes.Add(index);
return(table);
}
/// <summary>
/// Creates column in the table
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column description <see cref="DataColumnDbo"/></param>
/// <returns>SMO column, <see cref="Column"/></returns>
private static Column CreateColumn(Table aTable, DataColumnDbo column)
{
SqlDataType sqlType = (SqlDataType)Enum.Parse(typeof(SqlDataType), column.SqlDataType, true);
int length;
if (column.MaximumLength.HasValue && column.MaximumLength.Value > 0)
{
length = column.MaximumLength.Value;
}
else
{
length = column.NumericPrecision ?? 0;
}
var newColumn = new Column(aTable, column.Name,
GetSmoType(sqlType, length, column.NumericPrecision ?? 0, column.NumericScale ?? 0))
{
Identity = column.IsIdentity,
Nullable = column.IsNullable,
};
if (!String.IsNullOrEmpty(column.Default))
{
newColumn.AddDefaultConstraint();
newColumn.DefaultConstraint.Text = column.Default;
}
if (newColumn.Identity)
{
newColumn.IdentityIncrement = 1;
newColumn.IdentitySeed = 1;
}
aTable.Columns.Add(newColumn);
return(newColumn);
}
private void AddDateTimeOffsetField(string columnName, Table tb, bool nullable = false)
{
Column col = new Column(tb, columnName, DataType.DateTimeOffset(7))
{
Nullable = nullable
};
col.AddDefaultConstraint().Text = "sysdatetimeoffset()";
tb.Columns.Add(col);
}
/// <summary>
/// Create indexes for WKB table
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
public static void AddRowguidDefValue(SqlConnection conn, string tableName, string schemaName = "dbo")
{
Server srv = new Server(new ServerConnection(new SqlConnection((conn.ConnectionString))));
Database db = srv.Databases[conn.Database];
Table tbl = db.Tables[tableName, schemaName];
Column column = tbl.Columns[WkbfieldsRowguid];
column.AddDefaultConstraint();
column.DefaultConstraint.Text = "NEWID()";
column.Alter();
}
public void CreateStgTable()
{
// Create the new table in the appropriate filegroup for the SWITCH
foreach (Column c in partitionTable.Columns)
{
// Populate the table with each column and associated properties from the partition table
Column stgCol = new Column(stgTable, c.Name, c.DataType);
stgCol.Collation = c.Collation;
stgCol.Nullable = c.Nullable;
stgCol.Computed = c.Computed;
stgCol.ComputedText = c.ComputedText;
stgCol.Default = c.Default;
// Apply default constraint value, if present, as a default value
if (c.DefaultConstraint != null)
{
stgCol.AddDefaultConstraint(stgTable.Name + "_" + c.DefaultConstraint.Name);
stgCol.DefaultConstraint.Text = c.DefaultConstraint.Text;
}
stgCol.IsPersisted = c.IsPersisted;
stgCol.DefaultSchema = c.DefaultSchema;
stgCol.RowGuidCol = c.RowGuidCol;
if (srv.VersionMajor >= 10)
{
stgCol.IsFileStream = c.IsFileStream;
stgCol.IsSparse = c.IsSparse;
stgCol.IsColumnSet = c.IsColumnSet;
}
stgTable.Columns.Add(stgCol);
}
// Match other new table attributes to the partition table; required for SWITCH compatibility
stgTable.AnsiNullsStatus = partitionTable.AnsiNullsStatus;
stgTable.QuotedIdentifierStatus = partitionTable.QuotedIdentifierStatus;
// Calculate the filegroup associated with the partition nunber to switch; create temp table in that filegroup
stgTable.FileGroup = db.PartitionSchemes[partitionTable.PartitionScheme].FileGroups[partitionNumber - 1];
stgTable.TextFileGroup = db.PartitionSchemes[partitionTable.PartitionScheme].FileGroups[partitionNumber - 1];
if (srv.VersionMajor >= 10)
{
// Define compression property to match by creating a Physical Partition object
PhysicalPartition stgPartition = new PhysicalPartition(stgTable, 1, partitionTable.PhysicalPartitions[partitionNumber - 1].DataCompression);
stgTable.PhysicalPartitions.Add(stgPartition);
}
scriptChunks.Add(stgTable.Script());
if (executeCommands)
{
stgTable.Create();
}
}
internal static void CreateShell(Server server, string databaseName, string schemaAndTableName, Boolean keepIdentity = false)
{
string schemaName = SqlTestTable.GetSchemaName(schemaAndTableName);
string tableName = SqlTestTable.GetTableName(schemaAndTableName);
Database database = server.Databases[databaseName];
if (database.Tables[$"{tableName}_Faked", schemaName] != null)
{
Console.WriteLine($"Table: {tableName} has already been faked, dropping and restoring...");
FakeTable.Drop(server, databaseName, schemaAndTableName);
}
Table tableToFake = database.Tables[tableName, schemaName];
if (tableToFake == null)
{
throw new Exception($"Error creating fake table: Table not found: {schemaAndTableName}");
}
Table fakeTable = new Table(database, tableName, schemaName);
foreach (Column column in tableToFake.Columns)
{
Column copyofCol = new Column(fakeTable, column.Name, column.DataType);
if (keepIdentity)
{
copyofCol.Identity = column.Identity;
}
if (column.DefaultConstraint != null)
{
copyofCol.AddDefaultConstraint($"{column.DefaultConstraint.Name}_fake");
copyofCol.DefaultConstraint.Text = column.DefaultConstraint.Text;
}
fakeTable.Columns.Add(copyofCol);
}
try
{
tableToFake.Rename($"{tableName}_Faked");
fakeTable.Create();
}
catch (Exception e)
{
throw new Exception($"Failed to create fake table '{schemaAndTableName}': {e.Message}");
}
}
/// <summary>
/// Create physical table in DB
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <param name="tableColumns"></param>
public static void CreateSqlServerTable(SqlConnection conn, string tableName, List <DataColumn> tableColumns, string tableSchema = "dbo")
{
Server srv = new Server(new ServerConnection(conn));
Database db = srv.Databases[conn.Database];
Table tbl = new Table(db, tableName, tableSchema);
foreach (DataColumn column in tableColumns)
{
Column newColumn = new Column(tbl, column.ColumnName);
newColumn.Nullable = column.AllowDBNull;
newColumn.DataType = SystemType2SmoType(column.DataType);
newColumn.Identity = column.AutoIncrement;
newColumn.IdentitySeed = column.AutoIncrementSeed;
newColumn.IdentityIncrement = column.AutoIncrementStep;
if (column.DefaultValue != null && !string.IsNullOrEmpty(column.DefaultValue.ToString()))
{
newColumn.AddDefaultConstraint().Text = column.DefaultValue.ToString();
}
tbl.Columns.Add(newColumn);
}
tbl.Create();
}
private void AddTableButton_Click(object sender, System.EventArgs e)
{
Database db;
Table tbl;
Column col;
Index idx;
Default dflt;
Cursor csr = null;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Show the current tables for the selected database
db = (Database)DatabasesComboBox.SelectedItem;
if (db.Tables.Contains(TableNameTextBox.Text) == false)
{
// Create an empty string default
dflt = db.Defaults["dfltEmptyString"];
if (dflt == null)
{
dflt = new Default(db, "dfltEmptyString");
dflt.TextHeader = "CREATE DEFAULT [dbo].[dfltEmptyString] AS ";
dflt.TextBody = @"'';";
dflt.Create();
}
// Create a new table object
tbl = new Table(db,
TableNameTextBox.Text, db.DefaultSchema);
// Add the first column
col = new Column(tbl, @"Column1", DataType.Int);
tbl.Columns.Add(col);
col.Nullable = false;
col.Identity = true;
col.IdentitySeed = 1;
col.IdentityIncrement = 1;
// Add the primary key index
idx = new Index(tbl, @"PK_" + TableNameTextBox.Text);
tbl.Indexes.Add(idx);
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
idx.IsClustered = true;
idx.IsUnique = true;
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
// Add the second column
col = new Column(tbl, @"Column2", DataType.NVarChar(1024));
tbl.Columns.Add(col);
col.DataType.MaximumLength = 1024;
col.AddDefaultConstraint(null); // Use SQL Server default naming
col.DefaultConstraint.Text = Properties.Resources.DefaultConstraintText;
col.Nullable = false;
// Add the third column
col = new Column(tbl, @"Column3", DataType.DateTime);
tbl.Columns.Add(col);
col.Nullable = false;
// Create the table
tbl.Create();
// Refresh list and select the one we just created
ShowTables();
// Clear selected items
TablesComboBox.SelectedIndex = -1;
// Find the table just created
TablesComboBox.SelectedIndex = TablesComboBox.FindStringExact(tbl.ToString());
}
else
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.TableExists, TableNameTextBox.Text);
emb.Show(this);
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
/// <summary>
/// Create column for split table
/// </summary>
/// <param name="productionTable">Split production table</param>
/// <param name="surveyId">Survey ID</param>
/// <param name="databaseEngine">Pointer of database class</param>
/// <returns></returns>
public override MySmoObjectBase CreateSplitItem(
Table productionTable,
int surveyId,
Database databaseEngine)
{
var modelColumn = (Column)SourceSmoObject;
var newColumn = new Column(
productionTable,
modelColumn.Name,
modelColumn.DataType)
{
Collation = modelColumn.Collation,
Nullable = modelColumn.Nullable,
Identity = modelColumn.Identity,
IdentitySeed = modelColumn.IdentitySeed,
IdentityIncrement = modelColumn.IdentityIncrement
};
if (!productionTable.Columns.Contains(modelColumn.Name) && modelColumn.DefaultConstraint == null)
{
try
{
// For new table will generate PropertyNotSetException
if (!productionTable.IsSystemObject)
{
throw new Exception(string.Format(
"Added column {0} for table {1} does not have default value",
modelColumn.Name,
productionTable.Name));
}
}
catch (PropertyNotSetException)
{
}
}
if (modelColumn.DefaultConstraint != null)
{
newColumn.AddDefaultConstraint(CreateUniqueDefaultConstraintName(modelColumn.DefaultConstraint.Name)).Text = modelColumn.DefaultConstraint.Text;
}
if (!productionTable.Columns.Contains(modelColumn.Name))
{
productionTable.Columns.Add(newColumn);
}
// if productionTable is new table - return null MySmoObjectBase.
// Column will create with table creating
try
{
#pragma warning disable 168
bool temp = productionTable.IsSystemObject;
#pragma warning restore 168
return(new MySmoObjectBase(newColumn, newColumn.Name, productionTable.Name));
}
catch (PropertyNotSetException)
{
return(new MySmoObjectBase(null, string.Empty, string.Empty));
}
}
public void CreateUDSTable(SmoContext ctx)
{
Table tb = new Table(ctx.DbInstace, _tableName, _dbSchema);
Column col = new Column(tb, UDSPK, DataType.UniqueIdentifier)
{
Nullable = false
};
tb.Columns.Add(col);
//colonne dei metadati
if (_uds.Model.Metadata != null && _uds.Model.Metadata.Length > 0)
{
foreach (Section section in _uds.Model.Metadata.Where(f => f.Items != null))
{
foreach (FieldBaseType field in section.Items)
{
AddField(field, tb);
}
}
}
col = new Column(tb, UDSRepositoryFK, DataType.UniqueIdentifier)
{
Nullable = false
};
tb.Columns.Add(col);
//colonne di default
AddDateTimeOffsetField(UDSRegistrationDateField, tb);
col = new Column(tb, UDSRegistrationUserField, DataType.NVarChar(256))
{
Nullable = false
};
tb.Columns.Add(col);
AddDateTimeOffsetField(UDSLastChangedDateField, tb, true);
col = new Column(tb, UDSLastChangedUserField, DataType.NVarChar(256))
{
Nullable = true
};
tb.Columns.Add(col);
col = new Column(tb, UDSYearField, DataType.SmallInt)
{
Nullable = false
};
tb.Columns.Add(col);
col = new Column(tb, UDSNumberField, DataType.Int)
{
Nullable = false
};
tb.Columns.Add(col);
col = new Column(tb, UDSSubjectField, DataType.NVarChar(4000))
{
Nullable = true
};
tb.Columns.Add(col);
col = new Column(tb, UDSIdCategoryFK, DataType.SmallInt)
{
Nullable = false
};
tb.Columns.Add(col);
col = new Column(tb, UDSStatusField, DataType.SmallInt)
{
Nullable = false
};
col.AddDefaultConstraint().Text = "1"; // Active
tb.Columns.Add(col);
col = new Column(tb, UDSCancelMotivationField, DataType.NVarChar(1024))
{
Nullable = true
};
tb.Columns.Add(col);
col = new Column(tb, UDSTimestampField, DataType.Timestamp)
{
Nullable = false
};
tb.Columns.Add(col);
//Create the table on the instance of SQL Server.
tb.ValidateSchema();
tb.Create();
tb.AddPrimaryKey(UDSPK);
tb.AddForeignKey(UDSIdCategoryFK, DSWCategoryTableName, "dbo", DSWIdCategoryPK);
tb.AddForeignKey(UDSRepositoryFK, UDSRepositoriesTableName, _dbSchema, UDSRepositoryFK);
tb.AddClusterIndex(UDSRegistrationDateField);
tb.AddIndex(new List <string>()
{
UDSYearField, UDSNumberField
});
}
static void Main(string[] args)
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
ServerConnection cnn;
string databaseName;
using (var sqlConnection = new SqlConnection(connectionString))
{
cnn = new ServerConnection(sqlConnection);
// Read the database name from app.config
databaseName = sqlConnection.Database;
}
cnn.Connect();
Console.WriteLine("Connected");
//Create the server object
var server = new Server(cnn);
Console.WriteLine("Create the server object");
//Create the database object
var db = server.Databases[databaseName];
var schemaName = ConfigurationManager.AppSettings["C_TEST_SCHEMA"];
var tableName = ConfigurationManager.AppSettings["C_TEST_TABLE"];
//
//Create the schema if not exists
//
if (db.Schemas.Contains(schemaName) == false)
{
var hr = new Schema(db, schemaName);
db.Schemas.Add(hr);
db.Schemas[schemaName].Create();
}
Console.WriteLine("Create the schema object - if not exists");
//
//Drop the table if exists
//
if (db.Tables.Contains(tableName, schemaName))
{
db.Tables[tableName, schemaName].Drop();
}
Console.WriteLine("Droping the table if exists");
Console.WriteLine($"Create the table object {schemaName}.{tableName}");
//
// Create a new table object
//
var tbl = new Table(db, tableName, schemaName)
{
IsMemoryOptimized = false
};
//
//tbl.IsMemoryOptimized = true;
//tbl.Durability = DurabilityType.SchemaAndData;
// Add the identity column
var col = new Column(tbl, @"ID", DataType.Int)
{
Nullable = false,
Identity = true,
IdentitySeed = 1,
IdentityIncrement = 1
};
tbl.Columns.Add(col);
var idx = new Index(tbl, $@"PK_{tableName}")
{
IsClustered = true,
IsUnique = true,
IndexKeyType = IndexKeyType.DriPrimaryKey
};
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
tbl.Indexes?.Add(idx);
// Add the varchar column
col = new Column(tbl, @"Name", DataType.VarChar(128))
{
DataType = { MaximumLength = 128 }
};
col.AddDefaultConstraint(null);
col.DefaultConstraint.Text = "''";
col.Nullable = false;
tbl.Columns.Add(col);
// Add the datetime column
col = new Column(tbl, @"Date", DataType.DateTime);
tbl.Columns.Add(col);
col.Nullable = false;
Console.WriteLine($"Adding the table columns");
// Create the table
tbl.Create();
Console.WriteLine($"Create the table on SQL Server {schemaName}.{tableName}");
var sb = new StringBuilder();
//Scripter scrp = new Scripter(server);
//scrp.Options.ScriptDrops = false;
//scrp.Options.WithDependencies = true;
//scrp.Options.Indexes = true; // To include indexes
//scrp.Options.DriAllConstraints = true; // to include referential constraints in the script
//StringCollection sc = scrp.Script(new Urn[] { tbl.Urn });
//foreach (string st in sc)
//{
// sb.Append(st);
// sb.Append(CNewline);
//}
Console.WriteLine($"Make T-SQL script to create table {schemaName}.{tableName}");
var coll = tbl.Script(MakeOptions());
foreach (var str in coll)
{
sb.Append(str);
sb.AppendLine();
}
var fileName = $"{tableName}{DateTime.Now:yyyy_mm_dd_HH_mm_ss}.txt";
if (File.Exists(fileName))
{
File.Delete(fileName);
}
File.WriteAllText(fileName, sb.ToString());
// start notepad and disply the configuration
Process.Start(fileName);
if (cnn.IsOpen)
{
cnn.Disconnect();
}
cnn = null;
db = null;
server = null;
Console.WriteLine($"Press any key to exit...");
Console.ReadLine();
}
public void CreateStgTable()
{
// Create the new table in the appropriate filegroup for the SWITCH
foreach (Column c in partitionTable.Columns)
{
// Populate the table with each column and associated properties from the partition table
Column stgCol = new Column(stgTable, c.Name, c.DataType);
stgCol.Collation = c.Collation;
stgCol.Nullable = c.Nullable;
stgCol.Computed = c.Computed;
stgCol.ComputedText = c.ComputedText;
stgCol.Default = c.Default;
// Apply default constraint value, if present, as a default value
if (c.DefaultConstraint != null)
{
stgCol.AddDefaultConstraint(stgTable.Name + "_" + c.DefaultConstraint.Name);
stgCol.DefaultConstraint.Text = c.DefaultConstraint.Text;
}
stgCol.IsPersisted = c.IsPersisted;
stgCol.DefaultSchema = c.DefaultSchema;
stgCol.RowGuidCol = c.RowGuidCol;
if (srv.VersionMajor >= 10)
{
stgCol.IsFileStream = c.IsFileStream;
stgCol.IsSparse = c.IsSparse;
stgCol.IsColumnSet = c.IsColumnSet;
}
stgTable.Columns.Add(stgCol);
}
// Match other new table attributes to the partition table; required for SWITCH compatibility
stgTable.AnsiNullsStatus = partitionTable.AnsiNullsStatus;
stgTable.QuotedIdentifierStatus = partitionTable.QuotedIdentifierStatus;
// Calculate the filegroup associated with the partition nunber to switch; create temp table in that filegroup
stgTable.FileGroup = db.PartitionSchemes[partitionTable.PartitionScheme].FileGroups[partitionNumber - 1];
stgTable.TextFileGroup = db.PartitionSchemes[partitionTable.PartitionScheme].FileGroups[partitionNumber - 1];
if (srv.VersionMajor >= 10)
{
// Define compression property to match by creating a Physical Partition object
PhysicalPartition stgPartition = new PhysicalPartition(stgTable, 1, partitionTable.PhysicalPartitions[partitionNumber - 1].DataCompression);
stgTable.PhysicalPartitions.Add(stgPartition);
}
scriptChunks.Add(stgTable.Script());
if (executeCommands) stgTable.Create();
}
private const string CServerVersion = "13.0.4001.0"; // https://support.microsoft.com/en-us/help/3182545
static void Main(string[] args)
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
ServerConnection cnn;
string databaseName;
using (var sqlConnection = new SqlConnection(connectionString))
{
cnn = new ServerConnection(sqlConnection);
// Read the database name from app.config
databaseName = sqlConnection.Database;
}
cnn.Connect();
var server = new Server(cnn);
if (server.Version <= new Version(CServerVersion))
{
ConsoleEx.WriteLine("Only supported for SQL 2016+");
Console.ReadLine();
}
try
{
var db = server.Databases[databaseName];
//Person.EmailAddress
var tbl = db.Tables["EmailAddress", "Person"];
// --== Let's define the first column 'ValidFrom' ==---
// the equivalent T-SQL would be 'ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL'
var col = new Column(tbl, "ValidForm")
{
DataType = DataType.DateTime2(7),
//IsHidden = true,
//GeneratedAlwaysType = GeneratedAlwaysType.AsRowStart,
Nullable = false,
};
col.AddDefaultConstraint("DfValidFrom");
col.DefaultConstraint.Text = "'1991-01-01 00:00:00.0000000'";
tbl.Columns.Add(col);
// --== Let's define the column 'ValidTo' ==---
var col2 = new Column(tbl, "ValidTo")
{
DataType = DataType.DateTime2(7),
//IsHidden = true,
//GeneratedAlwaysType = GeneratedAlwaysType.AsRowEnd,
Nullable = false,
};
col2.AddDefaultConstraint("DfValidTo");
col2.DefaultConstraint.Text = "'9999-12-31 23:59:59.9999999'";
tbl.Columns.Add(col2);
// --== Let's define the period ==---
tbl.Alter();
tbl.AddPeriodForSystemTime(col.Name, col2.Name, true);
tbl.Alter();
tbl.IsSystemVersioned = true;
tbl.HistoryTableSchema = tbl.Schema;
tbl.HistoryTableName = $"{tbl.Name}_History";
tbl.DataConsistencyCheck = true;
tbl.Alter();
ConsoleEx.WriteLine("Let's Examine the Table ", ConsoleColor.Red);
ConsoleEx.WriteLine($"Is System Versioned Enabled {tbl.IsSystemVersioned}", ConsoleColor.Blue);
ConsoleEx.WriteLine($"History Table Name {tbl.HistoryTableSchema}.{tbl.HistoryTableName}", ConsoleColor.Blue);
ConsoleEx.WriteLine($"Data Consistency Check {tbl.DataConsistencyCheck}", ConsoleColor.Blue);
ConsoleEx.WriteLine("Let's make some updates - change the email address for the first record and see the result", ConsoleColor.Red);
var ds = db.ExecuteWithResults("UPDATE [Person].[EmailAddress] SET [EmailAddress] = '*****@*****.**' " +
"WHERE BusinessEntityID=1 AND EmailAddressID=1; " +
"DECLARE @Now AS DATE = CAST(GETDATE() AS DATE);" +
"SELECT EmailAddress FROM [Person].[EmailAddress] FOR SYSTEM_TIME AS OF @Now WHERE BusinessEntityID=1 AND EmailAddressID=1");
ConsoleEx.WriteLine($"The Email Address Is {ds.Tables[0].Rows[0][0]}");
Console.ReadLine();
}
catch (Exception ex)
{
ConsoleEx.WriteLine(ex.ToString(), ConsoleColor.Red);
Console.ReadLine();
}
}
/// <summary>
/// Создает таблицы в БД для сохранения/загрузки объектов переданного типа
/// </summary>
/// <returns></returns>
public void CreateTableFor(Type type)
{
//Определение атрибута сохраняемой таблицы
TableAttribute dbTable = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
if (!Database.Tables.Contains(dbTable.TableName, dbTable.TableScheme))
{
if (!Database.Schemas.Contains(dbTable.TableScheme))
{
//Если схемы нет в БД, то производится ее создание
Schema newSchema = new Schema(Database, dbTable.TableScheme);
newSchema.Owner = "dbo";
//Create the schema on the instance of SQL Server.
newSchema.Create();
//Define an ObjectPermissionSet that contains the Update and Select object permissions.
ObjectPermissionSet obperset = new ObjectPermissionSet();
obperset.Add(ObjectPermission.Select);
obperset.Add(ObjectPermission.Update);
obperset.Add(ObjectPermission.Insert);
obperset.Add(ObjectPermission.Delete);
//Grant the set of permissions on the schema to the guest account.
newSchema.Grant(obperset, "sa");
}
Table newTable = new Table(Database, dbTable.TableName, dbTable.TableScheme);
//определение своиств типа
List <PropertyInfo> preProrerty = new List <PropertyInfo>(type.GetProperties());
//определение своиств, имеющих атрибут "сохраняемое"
List <PropertyInfo> properties =
preProrerty.Where(p => p.GetCustomAttributes(typeof(TableColumnAttribute), false).Length != 0).ToList();
foreach (PropertyInfo t in properties)
{
TableColumnAttribute tca =
(TableColumnAttribute)t.GetCustomAttributes(typeof(TableColumnAttribute), false).FirstOrDefault();
Column newColumn = new Column(newTable, tca.ColumnName);
DataType storedType = GetDataType(t, tca);
if (storedType != null)
{
newColumn.DataType = storedType;
}
else
{
throw new Exception("для типа " + t.PropertyType.Name + " не удается определить хранимый тип в БД");
}
newColumn.Nullable = true;
if (tca.ColumnName == dbTable.PrimaryKey)
{
// Определение своиств ключа
newColumn.Nullable = false;
newColumn.Identity = true;
newColumn.IdentitySeed = 1;
newColumn.IdentityIncrement = 1;
}
newTable.Columns.Add(newColumn);
}
// Create a PK Index for the table
Index index = new Index(newTable, "PK_" + dbTable.TableName)
{
IndexKeyType = IndexKeyType.DriPrimaryKey
};
// The PK index will consist of 1 column, "ID"
index.IndexedColumns.Add(new IndexedColumn(index, dbTable.PrimaryKey));
// Add the new index to the table.
newTable.Indexes.Add(index);
// Physically create the table in the database
newTable.Create();
//Database.Tables.Add(newTable);
if (newTable.Columns.Contains("IsDeleted"))
{
// Определение своиств ключа
Column col = newTable.Columns["IsDeleted"];
string defName = dbTable.TableName + "_" + col.Name;
DefaultConstraint dc = col.AddDefaultConstraint(defName);
dc.Text = "((0))";
dc.Create();
col.Nullable = false;
col.Alter();
//Default def = new Default(Database, defName, dbTable.TableScheme)
//{
// TextHeader = "CREATE DEFAULT " + dbTable.TableScheme + ".[" + defName + "] AS",
// TextBody = "((0))"
//};
////Create the default on the instance of SQL Server.
//def.Create();
////Bind the default to a column in a table in AdventureWorks2012
//def.BindToColumn(dbTable.TableName, col.Name, dbTable.TableScheme);
}
}
else
{
//Получение таблицы
Table table = Database.Tables[dbTable.TableName, dbTable.TableScheme];
//определение своиств типа
List <PropertyInfo> preProrerty = new List <PropertyInfo>(type.GetProperties());
//определение своиств, имеющих атрибут "сохраняемое"
List <PropertyInfo> properties =
preProrerty.Where(p => p.GetCustomAttributes(typeof(TableColumnAttribute), false).Length != 0).ToList();
//Проверка таблицы на наличие соответствующх колонок, их имен и типа хранимого значения
foreach (PropertyInfo p in properties)
{
TableColumnAttribute tca =
(TableColumnAttribute)p.GetCustomAttributes(typeof(TableColumnAttribute), false).FirstOrDefault();
//Для начала определяется, можно ли сохранить тип в БД
DataType storedType = GetDataType(p, tca);
if (storedType == null)
{
throw new Exception("для типа " + p.PropertyType.Name + " не удается определить хранимый тип в БД");
}
//Проверка наличия колонки с заданным именем в таблице
if (!table.Columns.Contains(tca.ColumnName))
{
//Если колонки с заданным именем нет в таблице
//то производится ее создание
Column newColumn = new Column(table, tca.ColumnName);
newColumn.DataType = storedType;
newColumn.Nullable = true;
if (tca.ColumnName == dbTable.PrimaryKey)
{
// Определение своиств ключа
newColumn.Nullable = false;
newColumn.Identity = true;
newColumn.IdentitySeed = 1;
newColumn.IdentityIncrement = 1;
newColumn.Create();
//table.Columns.Add(newColumn);
// Create a PK Index for the table
Index index = new Index(table, "PK_" + dbTable.TableName)
{
IndexKeyType = IndexKeyType.DriPrimaryKey
};
// The PK index will consist of 1 column, "ID"
index.IndexedColumns.Add(new IndexedColumn(index, dbTable.PrimaryKey));
// Add the new index to the table.
table.Indexes.Add(index);
continue;
}
newColumn.Create();
continue;
}
//Проверка типа хранимого значения колонки
Column col = table.Columns[tca.ColumnName];
if (col.DataType.Name != storedType.Name)
{
//Если тип колонки в таблице не соответствует типу для хранения
//то производится смена типа
col.DataType = storedType;
col.Alter();
continue;
}
if (col.DataType.MaximumLength != storedType.MaximumLength && storedType.MaximumLength != 0)
{
//Если размер типа данных колонки в таблице не соответствует размеру для хранения
//то производится изменение размера
col.DataType.MaximumLength = storedType.MaximumLength;
col.Alter();
continue;
}
}
}
}
/// <summary>
/// Creates column in the table
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column description <see cref="DataColumnDbo"/></param>
/// <returns>SMO column, <see cref="Column"/></returns>
private static Column CreateColumn(Table aTable, DataColumnDbo column)
{
SqlDataType sqlType = (SqlDataType)Enum.Parse(typeof(SqlDataType), column.SqlDataType, true);
int length;
if (column.MaximumLength.HasValue && column.MaximumLength.Value > 0)
length = column.MaximumLength.Value;
else
length = column.NumericPrecision ?? 0;
var newColumn = new Column(aTable, column.Name,
GetSmoType(sqlType, length, column.NumericPrecision ?? 0, column.NumericScale ?? 0))
{
Identity = column.IsIdentity,
Nullable = column.IsNullable,
};
if (!String.IsNullOrEmpty(column.Default))
{
newColumn.AddDefaultConstraint();
newColumn.DefaultConstraint.Text = column.Default;
}
if (newColumn.Identity)
{
newColumn.IdentityIncrement = 1;
newColumn.IdentitySeed = 1;
}
aTable.Columns.Add(newColumn);
return newColumn;
}
public static void CopyColumn(Column source, Table sourceTable, Table copiedtable, Server server, int?colPos = null)
{
var column = new Column(copiedtable, source.Name, source.DataType);
column.Collation = source.Collation;
column.Nullable = source.Nullable;
column.Computed = source.Computed;
column.ComputedText = source.ComputedText;
column.Default = source.Default;
if (source.DefaultConstraint != null)
{
var tabname = copiedtable.Name;
var constrname = source.DefaultConstraint.Name;
column.AddDefaultConstraint(tabname + "_" + constrname);
column.DefaultConstraint.Text = source.DefaultConstraint.Text;
}
column.IsPersisted = source.IsPersisted;
column.DefaultSchema = source.DefaultSchema;
column.RowGuidCol = source.RowGuidCol;
if (server.VersionMajor >= 10)
{
column.IsFileStream = source.IsFileStream;
column.IsSparse = source.IsSparse;
column.IsColumnSet = source.IsColumnSet;
}
if (colPos != null)
{
var columnNameBeforeCopiedTable = "";
for (var i = 0; i < sourceTable.Columns.Count; i++)
{
var sc = sourceTable.Columns[i];
if (sc == source)
{
if (i > 0)
{
for (var j = i - 1; j >= 0; i--)
{
var tmpColumnName = sourceTable.Columns[j].Name;
var fromDest = copiedtable.Columns[tmpColumnName];
if (fromDest != null)
{
columnNameBeforeCopiedTable = fromDest.Name;
break;
}
}
if (columnNameBeforeCopiedTable == "")
{
colPos = null;
}
break;
}
else
{
colPos = 0;
}
}
}
if (columnNameBeforeCopiedTable == "")
{
copiedtable.Columns.Add(column, colPos.Value);
}
else
{
copiedtable.Columns.Add(column, columnNameBeforeCopiedTable);
}
}
else
{
copiedtable.Columns.Add(column);
}
}
private void AddTableButton_Click(object sender, System.EventArgs e)
{
Database db;
Table tbl;
Column col;
Index idx;
Default dflt;
Cursor csr = null;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Show the current tables for the selected database
db = (Database)DatabasesComboBox.SelectedItem;
if (db.Tables.Contains(TableNameTextBox.Text) == false)
{
// Create an empty string default
dflt = db.Defaults["dfltEmptyString"];
if (dflt == null)
{
dflt = new Default(db, "dfltEmptyString");
dflt.TextHeader = "CREATE DEFAULT [dbo].[dfltEmptyString] AS ";
dflt.TextBody = @"'';";
dflt.Create();
}
// Create a new table object
tbl = new Table(db,
TableNameTextBox.Text, db.DefaultSchema);
// Add the first column
col = new Column(tbl, @"Column1", DataType.Int);
tbl.Columns.Add(col);
col.Nullable = false;
col.Identity = true;
col.IdentitySeed = 1;
col.IdentityIncrement = 1;
// Add the primary key index
idx = new Index(tbl, @"PK_" + TableNameTextBox.Text);
tbl.Indexes.Add(idx);
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
idx.IsClustered = true;
idx.IsUnique = true;
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
// Add the second column
col = new Column(tbl, @"Column2", DataType.NVarChar(1024));
tbl.Columns.Add(col);
col.DataType.MaximumLength = 1024;
col.AddDefaultConstraint(null); // Use SQL Server default naming
col.DefaultConstraint.Text = Properties.Resources.DefaultConstraintText;
col.Nullable = false;
// Add the third column
col = new Column(tbl, @"Column3", DataType.DateTime);
tbl.Columns.Add(col);
col.Nullable = false;
// Create the table
tbl.Create();
// Refresh list and select the one we just created
ShowTables();
// Clear selected items
TablesComboBox.SelectedIndex = -1;
// Find the table just created
TablesComboBox.SelectedIndex = TablesComboBox.FindStringExact(tbl.ToString());
}
else
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.TableExists, TableNameTextBox.Text);
emb.Show(this);
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
private const string CServerVersion = "13.0.4001.0"; // https://support.microsoft.com/en-us/help/3182545
static void Main(string[] args)
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
ServerConnection cnn;
string databaseName;
using (var sqlConnection = new SqlConnection(connectionString))
{
cnn = new ServerConnection(sqlConnection);
// Read the database name from app.config
databaseName = sqlConnection.Database;
}
cnn.Connect();
Console.WriteLine("Connected");
//Create the server object
var server = new Server(cnn);
Console.WriteLine("Create the server object");
//Create the database object
try
{
var db = server.Databases[databaseName];
//
// Only for SQL Server version 2016 SP1
// Add MEMORY OPTIMIZED FILE GROUP AND FILE
if (server.Version >= new Version(CServerVersion))
{
ConsoleEx.WriteLine("Add support for memory optimized tables", ConsoleColor.Cyan);
// First check if there is already memory optimized file group
var isMemoryOptimizedFileGropuExists = false;
foreach (FileGroup f in db.FileGroups)
{
if (f.FileGroupType != FileGroupType.MemoryOptimizedDataFileGroup)
{
continue;
}
isMemoryOptimizedFileGropuExists = true;
break;
}
if (isMemoryOptimizedFileGropuExists == false)
{
// If memory optimized file group does not exists - create
if (db.FileGroups.Contains(CFileGroup) == false)
{
// CFileGroup is constant defined above as
// private const string CFileGroup = "mofg";
var mo = new FileGroup(db, CFileGroup, FileGroupType.MemoryOptimizedDataFileGroup);
db.FileGroups.Add(mo);
db.FileGroups[CFileGroup].Create();
}
// If the file for memory optimized file group does not exists - create
if (db.FileGroups[CFileGroup].Files.Contains(CFileName) == false)
{
// C_MO_PATH is the constant defined in app.config ;
// CFileName is the constant defined as private const string CFileName = "mofile";
// CFileGroup is the constant defined as private const string CFileGroup = "mofg";
var path = ConfigurationManager.AppSettings["C_MO_PATH"] + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss");
// Create the file ( the container )
var df = new DataFile(db.FileGroups[CFileGroup], CFileName, path);
// Add the container to the memory optimized file group
db.FileGroups[CFileGroup].Files.Add(df);
// Actually create. Now it exists in the database
try
{
db.FileGroups[CFileGroup].Files[CFileName].Create();
}
catch (Exception ex)
{
ConsoleEx.WriteLine(ex.Message, ConsoleColor.Red);
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
return;
}
}
}
}
//
// end database operation - adding memory optimized file group
//
//
//Create the schema if not exists
//
var schemaName = ConfigurationManager.AppSettings["C_MO_TEST_SCHEMA"];
if (db.Schemas.Contains(schemaName) == false)
{
var hr = new Schema(db, schemaName);
db.Schemas.Add(hr);
db.Schemas[schemaName].Create();
}
ConsoleEx.WriteLine("Create the schema object - if not exists", ConsoleColor.Cyan);
//
//Drop the table if exists
//
var tableName = ConfigurationManager.AppSettings["C_MO_TEST_TABLE"];
if (db.Tables.Contains(tableName, schemaName))
{
db.Tables[tableName, schemaName].Drop();
}
ConsoleEx.WriteLine("Droping the table if exists", ConsoleColor.Red);
ConsoleEx.WriteLine($"Create the table object {schemaName}.{tableName}", ConsoleColor.Cyan);
//
// Create a new table object
//
var tbl = new Table(db, tableName, schemaName)
{
//
IsMemoryOptimized = true,
Durability = DurabilityType.SchemaAndData
};
// Add the identity column
var col = new Column(tbl, @"ID", DataType.Int)
{
Nullable = false,
Identity = true,
IdentitySeed = 1,
IdentityIncrement = 1
};
tbl.Columns.Add(col);
// Add the primary key index
var idx = new Index(tbl, $@"PK_{tableName}")
{
IndexType = IndexType.NonClusteredHashIndex,
BucketCount = 128,
IndexKeyType = IndexKeyType.DriPrimaryKey
};
//idx.IndexType = IndexType.NonClusteredIndex;
tbl.Indexes.Add(idx);
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
// Add the varchar column
col = new Column(tbl, @"Name", DataType.VarChar(128))
{
DataType = { MaximumLength = 128 }
};
col.AddDefaultConstraint(null);
col.DefaultConstraint.Text = "''";
col.Nullable = false;
tbl.Columns.Add(col);
//Add range index
idx = new Index(tbl, @"NAME_" + tableName)
{
IndexType = IndexType.NonClusteredIndex,
IndexKeyType = IndexKeyType.None
};
tbl.Indexes.Add(idx);
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
// Add the datetime column
col = new Column(tbl, @"Date", DataType.DateTime);
tbl.Columns.Add(col);
col.Nullable = false;
ConsoleEx.WriteLine("Adding the table columns", ConsoleColor.DarkGray);
// Create the table
tbl.Create();
ConsoleEx.WriteLine($"Create the table on SQL Server {schemaName}.{tableName}", ConsoleColor.Cyan);
var sb = new StringBuilder();
ConsoleEx.WriteLine($"Make T-SQL script to create table {schemaName}.{tableName}", ConsoleColor.Yellow);
var coll = tbl.Script(CreateTable.MakeOptions());
foreach (var str in coll)
{
sb.AppendLine(str);
}
string fileName = $"{tableName}{DateTime.Now:yyyy_mm_dd_HH_mm_ss}.txt";
if (File.Exists(fileName))
{
File.Delete(fileName);
}
File.WriteAllText(fileName, sb.ToString());
// start notepad and disply the configuration
Process.Start(fileName);
if (cnn.IsOpen)
{
cnn.Disconnect();
}
cnn = null;
db = null;
server = null;
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message)));
}
}
