public static string CreateTable(string connectionString, string tableName, string attachDbFileName, string modellingDbName, string defaultSchema)
{
try
{
Server server = MDSModelling.ConnectToServer(connectionString);
if (server != null)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
if (!string.IsNullOrWhiteSpace(modellingDbName))
{
Database database = server.Databases[modellingDbName];
if (database == null)
{
database = new Database();
database.Name = modellingDbName;
server.AttachDatabase(builder.InitialCatalog, new StringCollection()
{
builder.AttachDBFilename
}, AttachOptions.None);
}
if (!database.Schemas.Contains(defaultSchema))
{
database.Schemas.Add(new Schema(database, defaultSchema));
database.Alter();
}
if (!database.Tables.Contains(tableName, defaultSchema))
{
Table table = new Table(database, tableName, defaultSchema);
table.Columns.Add(new Column((SqlSmoObject)table, "ID")
{
DataType = DataType.Int,
Nullable = false,
Identity = true,
IdentitySeed = 1L,
IdentityIncrement = 1L
});
Index index = new Index((SqlSmoObject)table, "PK_" + tableName);
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
index.IndexedColumns.Add(new IndexedColumn(index, "ID"));
table.Indexes.Add(index);
table.Create();
}
}
else
{
throw new Exception("database Name (= initial catalog) is empty");
}
}
else
{
throw new Exception("cannot find server");
}
}
catch (Exception ex)
{
return(ex.Message + (ex.InnerException == null || string.IsNullOrEmpty(ex.InnerException.Message) ? string.Empty : " ; " + ex.InnerException.Message));
}
return(string.Empty);
}
public static void DropExistingDb(string connectionString, string dbName)
{
try
{
Server server = MDSModelling.ConnectToServer(connectionString);
if (server != null)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
if (string.IsNullOrWhiteSpace(dbName))
{
throw new Exception("dbName (initial catalog) is empty");
}
else
{
Database database = server.Databases[dbName];
if (database == null)
{
throw new Exception(string.Format("database with name {0} not found", dbName));
}
database.Drop();
}
}
else
{
throw new Exception(string.Format("cannot connect to SQL server with connection string : {0}", connectionString));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void AddColumn(string connectionString, string dbName, string tableName, MetadataAttribute att)
{
try
{
Server server = MDSModelling.ConnectToServer(connectionString);
if (server == null)
{
throw new Exception("cannot find server");
}
Database database = server.Databases[dbName];
if (database == null)
{
throw new Exception("cannot find database : " + dbName);
}
if (database.Tables[tableName].Columns.Contains(att.Identifier.Name))
{
return;
}
database.Tables[tableName].Columns.Add(new Column((SqlSmoObject)database.Tables[tableName], att.Identifier.Name)
{
DataType = DataType.VarChar(50),
Nullable = false
});
database.Tables[tableName].Alter();
}
catch (Exception ex)
{
throw ex;
}
}
public static string CreateRelationShips(string dataSource, string initialCatalog, string DataBase, string parentTableName, string childTableName)
{
try
{
Database database = MDSModelling.ConnectToServer(dataSource, initialCatalog).Databases[DataBase];
Table table1 = database.Tables[childTableName];
Table table2 = database.Tables[parentTableName];
if (table1 != null && table2 != null)
{
ForeignKey foreignKey = new ForeignKey(table1, "FK_" + childTableName + "_" + parentTableName);
ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(foreignKey, "ID", "ID");
foreignKey.Columns.Add(foreignKeyColumn);
foreignKey.ReferencedTable = parentTableName;
if (!table1.ForeignKeys.Contains(foreignKey.Name))
{
foreignKey.Create();
}
}
}
catch (Exception ex)
{
return(ex.Message + (ex.InnerException == null || string.IsNullOrEmpty(ex.InnerException.Message) ? string.Empty : " ; " + ex.InnerException.Message));
}
return(string.Empty);
}
public static void DropExistingDb(string dataSource, string initialCatalog, string DataBase)
{
try
{
Database database = MDSModelling.ConnectToServer(dataSource, initialCatalog).Databases[DataBase];
if (database == null)
{
return;
}
database.Drop();
}
catch (Exception ex)
{
throw ex;
}
}
public static string CreateTable(string dataSource, string initialCatalog, string dataBaseName, string tableName, string attachDbFileName, string defaultSchema)
{
try
{
Server server = MDSModelling.ConnectToServer(dataSource, initialCatalog);
Database database = server.Databases[dataBaseName];
if (database == null)
{
database = new Database();
database.Name = dataBaseName;
server.AttachDatabase(dataBaseName, new StringCollection()
{
attachDbFileName
});
}
if (!database.Schemas.Contains(defaultSchema))
{
database.Schemas.Add(new Schema(database, defaultSchema));
database.Alter();
}
if (!database.Tables.Contains(tableName, defaultSchema))
{
Table table = new Table(database, tableName, defaultSchema);
table.Columns.Add(new Column((SqlSmoObject)table, "ID")
{
DataType = DataType.Int,
Nullable = false,
Identity = true,
IdentitySeed = 1L,
IdentityIncrement = 1L
});
Index index = new Index((SqlSmoObject)table, "PK_" + tableName);
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
index.IndexedColumns.Add(new IndexedColumn(index, "ID"));
table.Indexes.Add(index);
table.Create();
}
}
catch (Exception ex)
{
return(ex.Message + (ex.InnerException == null || string.IsNullOrEmpty(ex.InnerException.Message) ? string.Empty : " ; " + ex.InnerException.Message));
}
return(string.Empty);
}
