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); }