/* 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>());
}
