public TableMappings(string connectionString, string databaseName, string tableName) { _databaseName = databaseName; _tableName = tableName; _connectionString = connectionString; // read all fields in the primary key ReadPrimaryKeyList(); // read all fields string query = "SELECT * FROM " + databaseName + ".INFORMATION_SCHEMA.COLUMNS WHERE table_name='" + tableName + "' ORDER BY ORDINAL_POSITION"; using (var db = new ADODatabaseContext(connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { FieldMapping fieldMapping = new FieldMapping { FieldName = reader["COLUMN_NAME"].ToString(), DataType = reader["DATA_TYPE"].ToString(), Nullable = (reader["IS_NULLABLE"].ToString() == "YES"), Precision = reader["NUMERIC_PRECISION"].ToString(), PrecisionRadix = reader["NUMERIC_PRECISION_RADIX"].ToString(), StringLength = reader["CHARACTER_MAXIMUM_LENGTH"].ToString() }; Fields.Add(fieldMapping); } } }
private string LookupStoredProcedureCode() { var result = ""; using (var db = new ADODatabaseContext(_connectionString.Replace("master", _databaseName))) { var reader = db.ReadQuery($@" SELECT pr.name, m.definition, pr.type_desc, pr.create_date, pr.modify_date, schema_name(pr.schema_id) FROM sys.procedures pr INNER JOIN sys.sql_modules m ON pr.object_id = m.object_id WHERE is_ms_shipped = 0 AND schema_name(pr.schema_id) = '{_schemaName}' AND pr.name = '{_storedProcedureName}' "); while (reader.Read()) { result = reader["definition"].ToString(); } } return(result); }
public void CreateStoredProcedureMappings() { Directory.CreateDirectory(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "StoredProcedures")); var noStoredProceduresCreated = true; var query = $"SELECT ROUTINE_NAME, ROUTINE_SCHEMA FROM [{DatabaseName}].information_schema.routines WHERE routine_type = 'PROCEDURE'"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return; } // generate any new stored procedure mappings CreateStoredProcedure(reader["ROUTINE_NAME"].ToString(), reader["ROUTINE_SCHEMA"].ToString()); noStoredProceduresCreated = false; } } if (noStoredProceduresCreated) { UpdateProjectFileList("StoredProcedures", ""); } }
public void CreateViewMappings() { Directory.CreateDirectory(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "Views")); var noViewsCreated = true; var query = $"SELECT TABLE_NAME FROM [{DatabaseName}].information_schema.views"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return; } // generate any new view mappings CreateView(reader["TABLE_NAME"].ToString()); noViewsCreated = false; } } if (noViewsCreated) { UpdateProjectFileList("Views", ""); } }
private void CreateWrapperMappings() { Directory.CreateDirectory(RootDirectory + DatabaseName + "\\Wrappers"); var wrapperMappings = new WrapperMappings(); var query = "SELECT * FROM " + DatabaseName + ".INFORMATION_SCHEMA.tables"; using (var sessionWrapperFile = new StreamWriter(RootDirectory + DatabaseName + "\\Wrappers\\SessionWrapper.cs")) { using (var statelessSessionWrapperFile = new StreamWriter(RootDirectory + DatabaseName + "\\Wrappers\\StatelessSessionWrapper.cs")) { sessionWrapperFile.Write(wrapperMappings.EmitHeader("SessionWrapper")); statelessSessionWrapperFile.Write(wrapperMappings.EmitHeader("StatelessSessionWrapper")); using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { var tableName = reader["TABLE_NAME"].ToString(); sessionWrapperFile.Write(wrapperMappings.EmitCode(DatabaseName, tableName)); statelessSessionWrapperFile.Write(wrapperMappings.EmitCode(DatabaseName, tableName)); } } sessionWrapperFile.Write(wrapperMappings.EmitFooter()); statelessSessionWrapperFile.Write(wrapperMappings.EmitFooter()); } } UpdateProjectFileList("Wrappers", "SessionWrapper"); UpdateProjectFileList("Wrappers", "StatelessSessionWrapper"); }
private void ReadPrimaryKeyList() { string query = "SELECT * FROM " + _databaseName + ".INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name='" + _tableName + "' AND CONSTRAINT_NAME LIKE 'PK_%' ORDER BY ORDINAL_POSITION"; using (var db = new ADODatabaseContext(_connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { KeyList.Add(reader["COLUMN_NAME"].ToString()); } } }
private string LookupViewCode() { var result = ""; using (var db = new ADODatabaseContext(_connectionString.Replace("master", _databaseName))) { var myReader = db.ReadQuery("SELECT OBJECT_DEFINITION(OBJECT_ID('" + _viewName + "')) AS code"); while (myReader.Read()) { result = myReader["code"].ToString(); } } return(result); }
private int CountTables() { var query = $"SELECT COUNT(*) AS Total FROM [{DatabaseName}].INFORMATION_SCHEMA.tables"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { return((int)reader["Total"]); } } return(0); }
private int CountStoredProcedures() { var query = $"SELECT COUNT(*) AS Total FROM [{DatabaseName}].information_schema.routines WHERE routine_type = 'PROCEDURE'"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { return((int)reader["Total"]); } } return(0); }
private int CountViews() { var query = $"SELECT count(*) AS Total FROM [{DatabaseName}].information_schema.views"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { return((int)reader["Total"]); } } return(0); }
private List <string> ReadPrimaryKeyList(string tableName) { var keyList = new List <string>(); var query = "SELECT * FROM [" + _databaseName + "].INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name='" + tableName + "' AND CONSTRAINT_NAME LIKE 'PK_%' ORDER BY ORDINAL_POSITION"; using (var db = new ADODatabaseContext(_connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { keyList.Add(reader["COLUMN_NAME"].ToString()); } } return(keyList); }
public void CreateConstraintMappings() { Directory.CreateDirectory(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "Constraints")); var constraintMappings = new ConstraintMappings(ConnectionString, DatabaseName); var @out = new StringBuilder(); var firstTime = true; using (var db = new ADODatabaseContext(ConnectionString.Replace("master", DatabaseName))) { var reader = db.ReadQuery(constraintMappings.ConstraintMappingQueryString); while (reader.Read()) { var pkTableName = reader["PKTABLE_NAME"].ToString(); var pkColumnName = reader["PKCOLUMN_NAME"].ToString(); var fkTableName = reader["FKTABLE_NAME"].ToString(); var fkColumnName = reader["FKCOLUMN_NAME"].ToString(); var schemaName = reader["PKTABLE_OWNER"].ToString(); if (!firstTime) { @out.AppendLine(","); } firstTime = false; @out.Append("\t\t\tnew ConstraintDefinition { DatabaseName=\"" + DatabaseName + "\", PkTable = \"" + pkTableName + "\", PkField = \"" + pkColumnName + "\", FkTable = \"" + fkTableName + "\", FkField = \"" + fkColumnName + "\", SchemaName = \"" + schemaName + "\" }"); TotalCompleted++; } if (!firstTime) { @out.AppendLine(""); } } var result = constraintMappings.EmitCode(@out.ToString()); using (var file = new StreamWriter(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "Constraints", DatabaseName.FixSpecialCharacters() + "Constraints.cs"))) { file.Write(result); } //UpdateProjectFileList("Constraints", DatabaseName + "Constraints"); }
public string EmitCode() { var @out = new StringBuilder(); @out.AppendLine("using System;"); @out.AppendLine("using HelperLibrary;"); @out.AppendLine("using System.Collections.Generic;"); @out.AppendLine(""); @out.AppendLine("namespace NHibernateDataLayer." + _databaseName + ".TableGenerator"); @out.AppendLine("{"); @out.AppendLine("\t// DO NOT MODIFY! This code is auto-generated."); @out.AppendLine("\tpublic partial class " + _databaseName + "Tables"); @out.AppendLine("\t{"); @out.AppendLine("\t\tpublic static string DatabaseName {"); @out.AppendLine("\t\t\tget "); @out.AppendLine("\t\t\t{"); @out.AppendLine("\t\t\t\treturn \"" + _databaseName + "\";"); @out.AppendLine("\t\t\t}"); @out.AppendLine("\t\t}"); @out.AppendLine(""); @out.AppendLine("\t\tpublic static List<TableDefinition> TableList = new List<TableDefinition> {"); string query = "SELECT * FROM " + _databaseName + ".INFORMATION_SCHEMA.tables"; using (var db = new ADODatabaseContext(_connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { @out.Append("\t\t\tnew TableDefinition {"); @out.Append(EmitTableGenerateCode(reader["TABLE_NAME"].ToString())); @out.AppendLine("},"); } } @out.AppendLine("\t\t};"); @out.AppendLine("\t}"); @out.AppendLine("}"); return(@out.ToString()); }
private string GetIdentitySeedAndIncrementValues(string tableName, string fieldName) { using (var db = new ADODatabaseContext(_connectionString)) { var queryString = "SELECT * " + "FROM [" + _databaseName + "].sys.identity_columns AS a INNER JOIN [" + _databaseName + "].sys.objects AS b ON a.object_id=b.object_id " + "WHERE LOWER(b.name)='" + tableName.ToLower().Trim() + "' AND LOWER(a.name)='" + fieldName.ToLower().Trim() + "' AND type='U'"; using (var columnReader = db.ReadQuery(queryString)) { while (columnReader.Read()) { return($" IDENTITY({columnReader["SEED_VALUE"]},{columnReader["INCREMENT_VALUE"]})"); } } } return(""); }
private bool IsThisAnIdentityColumn(string fieldName) { using (var db = new ADODatabaseContext(_connectionString)) { string queryString = "SELECT * " + "FROM " + _databaseName + ".sys.identity_columns AS a INNER JOIN " + _databaseName + ".sys.objects AS b ON a.object_id=b.object_id " + "WHERE LOWER(b.name)='" + _tableName.ToLower().Trim() + "' AND LOWER(a.name)='" + fieldName.ToLower().Trim() + "' AND type='U'"; using (var columnReader = db.ReadQuery(queryString)) { if (columnReader.HasRows) { return(true); } } } return(false); }
// fill the check list box of databases from the server selected private void PopulateDatabaseList() { // clear the list first lstDatabases.Items.Clear(); string query = "SELECT name, database_id, create_date FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') ORDER BY name"; using (var db = new ADODatabaseContext(GetConnectionString())) { var reader = db.ReadQuery(query); while (reader.Read()) { lstDatabases.Items.Add(reader["name"].ToString()); } } // disable the generate button btnGenerate.Enabled = false; lblResult.Visible = false; }
private void CreateFunctionMappings() { Directory.CreateDirectory(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "Functions")); var query = $"SELECT ROUTINE_NAME,routine_schema FROM [{DatabaseName}].information_schema.routines WHERE routine_type = 'FUNCTION'"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return; } // generate any new stored procedure mappings CreateFunction(reader["ROUTINE_NAME"].ToString(), reader["ROUTINE_SCHEMA"].ToString()); } } }
private int CountConstraints() { var query = @" SELECT count(*) AS Total FROM sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id" ; using (var db = new ADODatabaseContext(ConnectionString.Replace("master", DatabaseName))) { var reader = db.ReadQuery(query); while (reader.Read()) { return((int)reader["Total"]); } } return(0); }
public void CreateStoredProcedureMappings() { Directory.CreateDirectory(RootDirectory + DatabaseName + "\\StoredProcedures"); var noStoredProceduresCreated = true; var query = "SELECT ROUTINE_NAME FROM " + DatabaseName + ".information_schema.routines WHERE routine_type = 'PROCEDURE'"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { // generate any new stored procedure mappings CreateStoredProcedure(reader["ROUTINE_NAME"].ToString()); } } if (noStoredProceduresCreated) { UpdateProjectFileList("StoredProcedures", ""); } }
private string LookupFunctionCode() { var result = ""; using (var db = new ADODatabaseContext(_connectionString.Replace("master", _databaseName))) { var reader = db.ReadQuery($@" select * from sys.sql_modules where object_name(object_id) = '{_functionName}' "); while (reader.Read()) { result = reader["definition"].ToString(); } } return(result); }
public void CreateViewMappings() { Directory.CreateDirectory(RootDirectory + DatabaseName + "\\Views"); var noViewsCreated = true; var query = "SELECT TABLE_NAME FROM " + DatabaseName + ".information_schema.views"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { // generate any new view mappings CreateView(reader["TABLE_NAME"].ToString()); noViewsCreated = false; } } if (noViewsCreated) { UpdateProjectFileList("Views", ""); } }
public void CreateTableMappings() { Directory.CreateDirectory(RootDirectory + DatabaseName + "\\Tables"); bool noTablesCreated = true; string query = "SELECT * FROM " + DatabaseName + ".INFORMATION_SCHEMA.tables"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { noTablesCreated = false; // generate any new table mappings CreateTable(reader["TABLE_NAME"].ToString()); } } if (!noTablesCreated) { UpdateProjectFileList("Tables", ""); } }
private void CreateTableGeneratorMappings() { Directory.CreateDirectory(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "TableGeneratorCode")); var tableDefinitionString = new StringBuilder(); var tableGeneratorMappings = new TableGeneratorMappings(ConnectionString, DatabaseName, _token); var query = $"SELECT * FROM [{DatabaseName}].INFORMATION_SCHEMA.tables ORDER BY TABLE_SCHEMA,TABLE_NAME"; using (var db = new ADODatabaseContext(ConnectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return; } tableDefinitionString.Append("\t\t\tnew TableDefinition {"); tableDefinitionString.Append(tableGeneratorMappings.EmitTableGenerateCode(reader["TABLE_NAME"].ToString(), reader["TABLE_SCHEMA"].ToString())); tableDefinitionString.AppendLine("},"); TotalCompleted++; } } var result = tableGeneratorMappings.EmitCode(tableDefinitionString.ToString()); using (var file = new StreamWriter(Path.Combine(RootDirectory, DatabaseName.FixSpecialCharacters(), "TableGeneratorCode", DatabaseName.FixSpecialCharacters() + "TableGeneratorCode.cs"))) { file.Write(result); } UpdateProjectFileList("TableGeneratorCode", DatabaseName + "TableGeneratorCode"); }
public string EmitCode() { var result = new StringBuilder(); var firstTime = true; result.AppendLine("using System.Collections.Generic;"); result.AppendLine("using HelperLibrary;"); result.AppendLine(""); result.AppendLine("namespace ApplicationUnderTest." + _databaseName + ".Constraints"); result.AppendLine("{"); result.AppendLine("\tpublic class " + _databaseName + "Constraints"); result.AppendLine("\t{"); result.AppendLine("\t\tpublic static List<ConstraintDefinition> ConstraintList = new List<ConstraintDefinition> {"); using (var db = new ADODatabaseContext(_connectionString)) { var query = @" SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)), PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME), PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)), FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME), FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME), -- Force the column to be non-nullable (see SQL BU 325751) --KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)), UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END), FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)), PK_NAME = CONVERT(SYSNAME,I.NAME), DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE FROM SYS.ALL_OBJECTS O1, SYS.ALL_OBJECTS O2, SYS.ALL_COLUMNS C1, SYS.ALL_COLUMNS C2, SYS.FOREIGN_KEYS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS K ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID) INNER JOIN SYS.INDEXES I ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID AND F.KEY_INDEX_ID = I.INDEX_ID) WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND O2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND C2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID AND C2.COLUMN_ID = K.PARENT_COLUMN_ID" ; var reader = db.ReadQuery(query); while (reader.Read()) { var pkTableName = reader["PKTABLE_NAME"].ToString(); var pkColumnName = reader["PKCOLUMN_NAME"].ToString(); var fkTableName = reader["FKTABLE_NAME"].ToString(); var fkColumnName = reader["FKCOLUMN_NAME"].ToString(); var schemaName = reader["PKTABLE_OWNER"].ToString(); if (!firstTime) { result.AppendLine(","); } firstTime = false; result.Append("\t\t\tnew ConstraintDefinition { DatabaseName=\"" + _databaseName + "\", PkTable = \"" + pkTableName + "\", PkField = \"" + pkColumnName + "\", FkTable = \"" + fkTableName + "\", FkField = \"" + fkColumnName + "\", SchemaName = \"" + schemaName + "\" }"); } if (!firstTime) { result.AppendLine(""); } } result.AppendLine("\t\t};"); result.AppendLine("\t}"); result.AppendLine("}"); return(result.ToString()); }
public string EmitTableGenerateCode(string tableName, string schema) { var @out = new StringBuilder(); var firstTime = true; @out.Append("Name=\"" + tableName + "\", "); @out.Append($"CreateScript=\"CREATE TABLE [{schema}].[{tableName}]("); var query = $"SELECT * FROM [{_databaseName}].INFORMATION_SCHEMA.COLUMNS WHERE table_name='{tableName}' ORDER BY ORDINAL_POSITION"; using (var db = new ADODatabaseContext(_connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return(""); } if (firstTime) { firstTime = false; } else { @out.Append(","); } @out.Append($"[{reader["COLUMN_NAME"]}]"); @out.Append($"[{reader["DATA_TYPE"]}]"); switch (reader["DATA_TYPE"].ToString().ToLower()) { case "char": case "nchar": case "varchar": case "nvarchar": case "varbinary": if (reader["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1") { @out.Append("(MAX)"); } else { @out.Append($"({reader["CHARACTER_MAXIMUM_LENGTH"]})"); } break; case "numeric": case "money": @out.Append($"({reader["NUMERIC_PRECISION"]}"); if (reader["NUMERIC_SCALE"].ToString() != "") { @out.Append($",{reader["NUMERIC_SCALE"]}"); } @out.Append(")"); break; } // output identity field information @out.Append(GetIdentitySeedAndIncrementValues(tableName, reader["COLUMN_NAME"].ToString())); if (reader["IS_NULLABLE"].ToString() != "YES") { @out.Append(" NOT NULL"); } } } // get the primary key firstTime = true; query = $"SELECT * FROM [{_databaseName}].INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name='{tableName}' AND CONSTRAINT_NAME LIKE 'PK_%' ORDER BY ORDINAL_POSITION"; using (var db = new ADODatabaseContext(_connectionString)) { var reader = db.ReadQuery(query); while (reader.Read()) { if (_token.IsCancellationRequested) { return(""); } if (firstTime) { @out.Append($", CONSTRAINT [{reader["CONSTRAINT_NAME"]}] PRIMARY KEY CLUSTERED ("); firstTime = false; } else { @out.Append(","); } @out.Append($"[{reader["COLUMN_NAME"]}]"); @out.Append(" ASC"); } } if (!firstTime) { @out.Append(")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"); @out.Append(") ON [PRIMARY]\""); } else { @out.Append(")\""); } return(@out.ToString()); }