/* TODO: GetSchemaTable was failing on SQL Server Express, not sure why. * It's not that it needed data rows in the table. * Does it need data actually returned? */ private void PopulateFields2008(ProcedureDef procedureDef) { /* MSSQL prior to 2012 might need this, we can verify if there's a client to test against at some point */ /* * call the procedure to see the result set * */ using (SqlTransaction trans = this.SqlConnection.BeginTransaction()) { using (SqlCommand command = new SqlCommand() { CommandText = procedureDef.ProcedureName, CommandType = CommandType.StoredProcedure, Connection = this.SqlConnection, Transaction = trans }) { foreach (ParameterDef parameterDef in procedureDef.ParameterDefMap.Values) { ParameterDirection direction; if (parameterDef.IsOutParameter) { direction = ParameterDirection.Output; } else { direction = ParameterDirection.Input; } var parameter = new SqlParameter { ParameterName = parameterDef.ParameterName, Direction = direction, Size = parameterDef.ParameterSize, SqlDbType = GetSqlDbType(parameterDef.ParameterDataTypeCode) }; parameter.Value = DBNull.Value; command.Parameters.Add(parameter); } DataTable tableSchema; /* we really want all 3 behaviors, and within a transaction, but the two * behaviors CommandBehavior.SchemaOnly and CommandBehavior.KeyInfo fail * to give us a schema table to read. So we have to get a result within * a transaction. There's nothing super wrong with that, but it feels * dirty. Here are the results of combinations we've tried (all with SQL * Server Express 2014): * No behaviors, no transaction : succeeded * CommandBehavior.SchemaOnly alone, no transaction : failed * CommandBehavior.SchemaOnly| CommandBehavior.KeyInfo, no trans : failed * CommandBehavior.SchemaOnly| CommandBehavior.SingleResult, no trans : failed * CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo | CommandBehavior.SingleResult, no trans : failed * CommandBehavior.KeyInfo, no trans : failed * CommandBehavior.KeyInfo | CommandBehavior.SingleResult, no trans : failed * CommandBehavior.SingleResult, no trans : succeeded * No behaviors, with trans : succeeded * CommandBehavior.SingleResult, with trans : succeeded */ using (var reader = command.ExecuteReader(CommandBehavior.SingleResult)) { reader.Read(); tableSchema = reader.GetSchemaTable(); } trans.Rollback(); if (tableSchema == null) { throw new ApplicationException("Sampling of procedure " + procedureDef.ProcedureName + " yielded no schema."); } foreach (DataRow row in tableSchema.Rows) { /* IsIdentity */ /* * int columnSize = (int)row["ColumnSize"]; * string sqlTypeCode = * bool isReadOnly = (bool)row["IsReadOnly"]; * bool isPrimaryKey = (bool)row["IsKey"]; * bool isAutoIncrement = (bool)row["IsAutoIncrement"]; */ string fieldName = row["ColumnName"].ToString(); /* TODO: on views, these BaseTableName and BaseColumnName are for the view, not the source table like we get for MSSQL2012 */ string baseTableName = row["BaseTableName"].ToString(); string baseColumnName = row["BaseColumnName"].ToString(); bool isNullable = (bool)row["AllowDBNull"]; string dataTypeCode = TypeConvertor.ConvertCLRToVernacular(row["DataType"].ToString()); FieldDef fieldDef = new FieldDef { FieldName = fieldName, ProcedureDef = procedureDef, DataTypeCode = dataTypeCode, IsNullable = isNullable, BaseTableName = baseTableName, BaseColumnName = baseColumnName }; procedureDef.FieldDefMap[fieldDef.FieldName] = fieldDef; } } } }
public void AddUsingCommand(StringBuilder buildText, ProcedureDef procedureDef) { buildText.AppendLine("\t\t\t\tusing (var command = DataAccess.GetCommand(conn, trans, \"" + procedureDef.ProcedureName + "\")) {"); }
private void PopulateFields2012(ProcedureDef procedureDef) { /* pass procedureName, then a string of parameters (not necessary), * browserInfo should be 0 to return only the outputted columns and not join columns * and finally browserInfo must be 1 to get source table info on second call */ var validNameList = new List <string>(); using (SqlCommand command = new SqlCommand() { CommandText = $@"SELECT FieldName=name FROM sys.dm_exec_describe_first_result_set('{procedureDef.ProcedureName}', NULL, 0)", CommandType = CommandType.Text, Connection = this.SqlConnection }) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { IDataReader row = reader; string fieldName = row["FieldName"].ToString(); validNameList.Add(fieldName); } } } using (SqlCommand command = new SqlCommand() { CommandText = $@"SELECT FieldName=name, DataTypeCode=system_type_name, IsNullable = is_nullable, BaseTableName = source_table, BaseColumnName = source_column FROM sys.dm_exec_describe_first_result_set('{procedureDef.ProcedureName}', NULL, 1)", CommandType = CommandType.Text, Connection = this.SqlConnection }) { var regex = new Regex("^(\\w+)\\(\\d+(,\\d+)?\\)$"); using (var reader = command.ExecuteReader()) { while (reader.Read()) { IDataReader row = reader; string fieldName = row["FieldName"].ToString(); if (fieldName == "") { throw new Exception("MSSQL inspection for procedure " + procedureDef.ProcedureName + " returned a field with no name. Check the stored procedure for invalid characters."); } if (validNameList.Contains(fieldName) == false) { // Shared.Info("Skipping superfluous field " + fieldName); } else { string sqlDataTypeCode = row["DataTypeCode"].ToString(); /* MSSQL2012 will send a char length too, e.g. varchar(10), decimal(18,0) */ var match = regex.Match(sqlDataTypeCode); if (match.Success) { sqlDataTypeCode = match.Groups[1].Value; } string dataTypeCode; try { dataTypeCode = TypeConvertor.ConvertSQLToCSharp(sqlDataTypeCode); } catch { Shared.Info("Call to ConvertSQLToCSharp for procedure " + procedureDef.ProcedureName + ", field named \"" + fieldName + "\", of SQL data type \"" + sqlDataTypeCode + "\". This can happen if you have a stored procedure that is referencing a column that no longer exists for a table. Check that the procedure will run on its own."); throw; } bool isNullable = (bool)row["IsNullable"]; /* max_length, precision, scale */ /* TODO: using result set procedure does not populate source * table or column, which is as close to these fields * as I could find */ string baseTableName = row["BaseTableName"].ToString(); string baseColumnName = row["BaseColumnName"].ToString(); // Shared.Info($"DEBUG:procedureName={procedureDef.ProcedureName}, fieldName={fieldName}, baseTableName={baseTableName}, baseColumnName={baseColumnName}"); FieldDef fieldDef = new FieldDef { FieldName = fieldName, ProcedureDef = procedureDef, DataTypeCode = dataTypeCode, IsNullable = isNullable, BaseTableName = baseTableName, BaseColumnName = baseColumnName }; procedureDef.FieldDefMap[fieldDef.FieldName] = fieldDef; } } } } }
public List <ProcedureDef> MakeProcedureDefList(string databaseName, string moduleName, Dictionary <string, TableDef> tableDefMap) { /* var tableMap = {}; * var functionMap = {}; * var tableArray = []; * * */ SqlConnection conn = this.SqlConnection; var globalProcedureDefMap = new Dictionary <string, ProcedureDef>(); using (SqlCommand command = new SqlCommand() { CommandText = $@"SELECT sobj.name as Name, (SELECT count(is_selected) FROM sys.sql_dependencies AS sis WHERE sobj.object_id = sis.object_id AND is_selected = 1) AS PerformsSelectCount, (SELECT count(is_updated) FROM sys.sql_dependencies AS siu WHERE sobj.object_id = siu.object_id AND is_updated = 1) AS PerformsUpdateCount FROM sys.objects AS sobj WHERE type = 'P' AND name like '{moduleName}\_%\_%' ESCAPE '\' ORDER BY name;", CommandType = CommandType.Text, Connection = conn }) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { IDataRecord procRow = reader; string procedureName = (string)procRow["Name"]; Shared.Info("Reading procedure " + procedureName); /* we also have the ability to check referenced tables if need be */ int secondUnderscoreIndex = procedureName.IndexOf("_", moduleName.Length + 2, StringComparison.InvariantCulture); string functionalName = procedureName.Substring(moduleName.Length + 1, secondUnderscoreIndex - moduleName.Length - 1); Shared.Info($"moduleName:{moduleName}, functionalName:{functionalName}, secondUnderscoreIndex:{secondUnderscoreIndex}"); /* that is the functional name, so we want to find by that */ var tableDef = tableDefMap.Values.SingleOrDefault(x => NameMapping.GetFunctionalName(x) == functionalName); if (tableDef == null) { throw new ApplicationException("Table with functional name " + functionalName + " referenced in stored procedure " + procedureName + " was not found in table definitions."); } ProcedureDef procedureDef = new ProcedureDef { ProcedureName = procedureName, TableDef = tableDef, ParameterDefMap = new Dictionary <string, ParameterDef>(), FieldDefMap = new Dictionary <string, FieldDef>() }; Shared.Info($"procedure {procedureName}, select:{(int)procRow["PerformsSelectCount"]}, update:{(int)procRow["PerformsUpdateCount"]} "); /* DELETE and UPDATE queries will have a value in PerformsSelectCount, so we screen by PerformsUpdateCount first */ if ((int)procRow["PerformsUpdateCount"] > 0) { procedureDef.ReadOnly = false; procedureDef.OutputsRows = false; } else { procedureDef.ReadOnly = true; procedureDef.OutputsRows = true; if ((int)procRow["PerformsSelectCount"] == 0) { throw new ApplicationException($"Procedure {procedureName} had 0 values for select and for update dependency counts"); } } tableDef.ProcedureDefMap[procedureName] = procedureDef; globalProcedureDefMap[procedureName] = procedureDef; } } } PopulateParameters(databaseName, moduleName, globalProcedureDefMap); foreach (ProcedureDef procedureDef in globalProcedureDefMap.Values) { if (procedureDef.OutputsRows) { // Shared.Info("Collecting output fields from procedure " + procedureDef.ProcedureName); // Shared.Info(procedureDef.ToJSONString()); /* MSSQL 2012 and beyond have result set function, which we'll use in this version */ // PopulateFields2008(procedureDef); PopulateFields2012(procedureDef); } } return(globalProcedureDefMap.Values.ToList <ProcedureDef>()); }
private void PopulateParameters(string databaseName, string moduleName, Dictionary <string, ProcedureDef> globalProcedureDefMap) { using (SqlCommand command = new SqlCommand() { CommandText = $"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_CATALOG='{databaseName}' AND SPECIFIC_NAME like '{moduleName}\\_%\\_%' ESCAPE '\\' ORDER BY ORDINAL_POSITION ASC", CommandType = CommandType.Text, Connection = this.SqlConnection }) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { var parameterRow = reader; string procedureName = (string)parameterRow["SPECIFIC_NAME"]; ProcedureDef procedureDef = globalProcedureDefMap[procedureName]; string parameterName = (string)parameterRow["PARAMETER_NAME"]; int?charLength; if (parameterRow["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value) { charLength = null; } else { charLength = (int)parameterRow["CHARACTER_MAXIMUM_LENGTH"]; } int?scale; if (parameterRow["NUMERIC_SCALE"] == DBNull.Value) { scale = null; } else { scale = (int)parameterRow["NUMERIC_SCALE"]; } byte?precision; if (parameterRow["NUMERIC_PRECISION"] == DBNull.Value) { precision = null; } else { precision = (byte)parameterRow["NUMERIC_PRECISION"]; } /* TODO: if you have an interface, and a referring class uses the interface * and not the implementing object, but you put the object name + Key in the * database table, TIDAL is making procedures/functions using the interface * name + Key, and this doesn't map back to the table to get the columndef */ /* try to find the best guess as to the field it may be */ ColumnDef columnDef; procedureDef.TableDef.ColumnDefMap.TryGetValue(parameterName.Substring(1), out columnDef); // if (columnDef == null) Console.WriteLine("Could not get ColumnDef for " + parameterName.Substring(1) + " of " + procedureDef.ProcedureName); ParameterDef parameterDef = new ParameterDef { ProcedureDef = procedureDef, ParameterName = parameterName, ParameterMode = (string)parameterRow["PARAMETER_MODE"], ParameterDataTypeCode = (string)parameterRow["DATA_TYPE"], ColumnDef = columnDef, CharLength = charLength, Precision = (ulong?)precision, Scale = scale, OrdinalPosition = (int)parameterRow["ORDINAL_POSITION"], IsOutParameter = parameterRow["PARAMETER_MODE"].ToString() == "INOUT" }; procedureDef.ParameterDefMap[parameterName] = parameterDef; /* isNullable, isOut, length */ } } } }
private void PopulateParameters(string databaseName, string moduleName, Dictionary <string, ProcedureDef> globalProcedureDefMap) { using (MySqlCommand command = new MySqlCommand() { CommandText = "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_CATALOG='def' AND SPECIFIC_SCHEMA='" + databaseName + "' AND SPECIFIC_NAME like '" + moduleName + "_%_%' ORDER BY ORDINAL_POSITION ASC", CommandType = CommandType.Text, Connection = this.mySqlConnection }) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { var parameterRow = reader; string procedureName = (string)parameterRow["SPECIFIC_NAME"]; ProcedureDef procedureDef = globalProcedureDefMap[procedureName]; string parameterName = (string)parameterRow["PARAMETER_NAME"]; int?charLength; if (parameterRow["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value) { charLength = null; } else { charLength = (int)parameterRow["CHARACTER_MAXIMUM_LENGTH"]; } int?scale; if (parameterRow["NUMERIC_SCALE"] == DBNull.Value) { scale = null; } else { scale = (int)parameterRow["NUMERIC_SCALE"]; } ulong?precision; if (parameterRow["NUMERIC_PRECISION"] == DBNull.Value) { precision = null; } else { precision = (ulong)parameterRow["NUMERIC_PRECISION"]; } /* try to find the best guess as to the field it may be */ ColumnDef columnDef; procedureDef.TableDef.ColumnDefMap.TryGetValue(parameterName.Substring(1), out columnDef); ParameterDef parameterDef = new ParameterDef { ProcedureDef = procedureDef, ParameterName = parameterName, ParameterMode = (string)parameterRow["PARAMETER_MODE"], ParameterDataTypeCode = (string)parameterRow["DATA_TYPE"], ColumnDef = columnDef, CharLength = charLength, Precision = precision, Scale = scale, OrdinalPosition = (int)parameterRow["ORDINAL_POSITION"], IsOutParameter = parameterRow["PARAMETER_MODE"].ToString() == "OUT" }; procedureDef.ParameterDefMap[parameterName] = parameterDef; /* isNullable, isOut, length */ } } } }
public List <ProcedureDef> MakeProcedureDefList(string databaseName, string moduleName, Dictionary <string, TableDef> tableDefMap) { /* var tableMap = {}; * var functionMap = {}; * var tableArray = []; * * */ MySqlConnection conn = this.mySqlConnection; var globalProcedureDefMap = new Dictionary <string, ProcedureDef>(); using (MySqlCommand command = new MySqlCommand() { CommandText = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='" + databaseName + "' AND ROUTINE_NAME LIKE '" + moduleName + "_%_%' AND ROUTINE_TYPE='PROCEDURE';", CommandType = CommandType.Text, Connection = conn }) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { IDataRecord procRow = reader; string procedureName = (string)procRow["ROUTINE_NAME"]; if ((string)procRow["SQL_DATA_ACCESS"] == "CONTAINS SQL") { /* TODO: I could envision a get server time function that doesn't read or write data but should be part of a DataAccess class. */ Shared.Info("Skipping procedure " + procedureName + " marked CONTAINS SQL, as this implies it does not read or write data."); } else { int lastUnderscoreIndex = procedureName.IndexOf("_", moduleName.Length + 2); string tableName = procedureName.Substring(moduleName.Length + 1, lastUnderscoreIndex - moduleName.Length - 1); string catalogName = (string)procRow["ROUTINE_CATALOG"]; if (catalogName != "def") { throw new ApplicationException("Unexpected catalog name found: " + catalogName); } if (tableDefMap.ContainsKey(tableName) == false) { throw new ApplicationException("Table " + tableName + " referenced in stored procedure " + procedureName + " was not found in table definitions."); } TableDef tableDef = tableDefMap[tableName]; ProcedureDef procedureDef = new ProcedureDef { ProcedureName = procedureName, TableDef = tableDef, ParameterDefMap = new Dictionary <string, ParameterDef>(), FieldDefMap = new Dictionary <string, FieldDef>() /* ReadOnly, OutputsRows */ }; /* TODO: there may be some issues here if we require list and read functions to a data access level of reads sql data, i think * it may change the transaction scope if you mix with modifies sql data (not sure) */ switch ((string)procRow["SQL_DATA_ACCESS"]) { case "READS SQL DATA": procedureDef.ReadOnly = true; procedureDef.OutputsRows = true; break; case "MODIFIES SQL DATA": procedureDef.ReadOnly = false; procedureDef.OutputsRows = false; break; default: throw new ApplicationException("Unrecognized SQL Data Access setting for procedure " + procedureName + ": " + procRow["SQL_DATA_ACCESS"]); } tableDef.ProcedureDefMap[procedureName] = procedureDef; globalProcedureDefMap[procedureName] = procedureDef; } } } } PopulateParameters(databaseName, moduleName, globalProcedureDefMap); /* * call each procedure to see the result set * */ foreach (ProcedureDef procedureDef in globalProcedureDefMap.Values) { if (procedureDef.OutputsRows) { MySqlTransaction trans = this.mySqlConnection.BeginTransaction(); using (MySqlCommand command = new MySqlCommand() { CommandText = procedureDef.ProcedureName, CommandType = CommandType.StoredProcedure, Connection = this.mySqlConnection }) { foreach (ParameterDef parameterDef in procedureDef.ParameterDefMap.Values) { ParameterDirection direction; if (parameterDef.IsOutParameter) { direction = ParameterDirection.Output; } else { direction = ParameterDirection.Input; } var parameter = new MySqlParameter { ParameterName = parameterDef.ParameterName, Direction = direction, Size = parameterDef.ParameterSize, MySqlDbType = GetMySqlDbType(parameterDef.ParameterDataTypeCode) }; /* for (Parameter p : proc.parameters) { * p.sqlType.setTestValue(cs, p.procParamName); * } */ parameter.Value = DBNull.Value; command.Parameters.Add(parameter); } /* alternatively for MSSQL at least: * * SELECT COLUMN_NAME * FROM * INFORMATION_SCHEMA.COLUMNS * WHERE * TABLE_NAME = 'vwGetData' * ORDER BY * ORDINAL_POSITION ASC; * */ // cs.setMaxRows(0); DataTable tableSchema; Dictionary <string, string> fieldTypeLookup = new Dictionary <string, string>(); using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) { reader.Read(); tableSchema = reader.GetSchemaTable(); for (int i = 0; i < reader.FieldCount; i++) { fieldTypeLookup[reader.GetName(i)] = reader.GetDataTypeName(i); } } trans.Rollback(); foreach (DataRow row in tableSchema.Rows) { /* IsIdentity */ /* * int columnSize = (int)row["ColumnSize"]; * string sqlTypeCode = * bool isReadOnly = (bool)row["IsReadOnly"]; * bool isPrimaryKey = (bool)row["IsKey"]; * bool isAutoIncrement = (bool)row["IsAutoIncrement"]; */ string fieldName = row["ColumnName"].ToString(); string baseTableName = row["BaseTableName"].ToString(); string baseColumnName = row["BaseColumnName"].ToString(); bool isNullable = (bool)row["AllowDBNull"]; string dataTypeCode = TypeConvertor.ConvertSQLToCSharp(fieldTypeLookup[fieldName].ToLowerInvariant()); /* TODO: This check wouldn't really be necessary if we could rely on GetSchemaTable's DataType field */ if (tableDefMap.ContainsKey(baseTableName)) { if (tableDefMap[baseTableName].ColumnDefMap.ContainsKey(baseColumnName)) { string newDataTypeCode = tableDefMap[baseTableName].ColumnDefMap[baseColumnName].ColumnType; if (newDataTypeCode != dataTypeCode) { Shared.Warning(" GetTableSchema reported an incorrect data type of " + dataTypeCode + " from stored procedure " + procedureDef.ProcedureName + ", field " + fieldName + ", instead of the source table's column data type of " + newDataTypeCode + "."); dataTypeCode = newDataTypeCode; } } } FieldDef fieldDef = new FieldDef { FieldName = fieldName, ProcedureDef = procedureDef, DataTypeCode = dataTypeCode, IsNullable = isNullable, BaseTableName = baseTableName, BaseColumnName = baseColumnName }; procedureDef.FieldDefMap[fieldDef.FieldName] = fieldDef; } } } } return(globalProcedureDefMap.Values.ToList <ProcedureDef>()); }