public static string ConvertNullableSQLToCSharp(string sqlTypeCode, bool isNullable)
{
string typeCode = TypeConvertor.ConvertSQLToCSharp(sqlTypeCode);
if (isNullable && typeCode != "string")
{
typeCode += "?";
}
return(typeCode);
}
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>());
}
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 static void MakeModels(List <TableDef> tableDefList, string namespaceText, string directory, List <TableMapping> tableMappingList, bool cleanOracle)
{
if (directory.EndsWith(Path.DirectorySeparatorChar.ToString(), false, CultureInfo.InvariantCulture) == false)
{
directory += Path.DirectorySeparatorChar;
}
foreach (var tableDef in tableDefList.OrderBy(x => x.TableName))
{
/* TODO: For now we skip views, but should we? */
if (tableDef.TableType == "TABLE")
{
Shared.Info("Making model from table " + tableDef.TableName);
var modelName = NameMapping.MakeCleanTableName(tableMappingList, tableDef.TableName, cleanOracle);
Shared.Info($"\t\tusing model name {modelName}");
StringBuilder buildText = new StringBuilder();
buildText.AppendLine("namespace " + namespaceText + " {\n");
buildText.AppendLine("\tpublic partial class " + modelName + " {\n");
buildText.AppendLine();
bool needsSystem = false;
foreach (var columnDef in tableDef.ColumnDefMap.Values)
{
string columnTypeCode;
string columnName = columnDef.ColumnName;
string convertedColumnName = NameMapping.MakeCleanColumnName(tableMappingList, tableDef.TableName, modelName, columnName, cleanOracle);
if (columnDef.ReferencedTableDef != null)
{
if (convertedColumnName.EndsWith("Key", StringComparison.InvariantCultureIgnoreCase))
{
convertedColumnName = convertedColumnName.Substring(0, convertedColumnName.Length - 3);
}
var rawReferencedTableName = columnDef.ReferencedTableDef.TableName;
columnTypeCode = NameMapping.MakeCleanTableName(tableMappingList, rawReferencedTableName, cleanOracle);
}
else
{
columnTypeCode = TypeConvertor.ConvertSQLToCSharp(columnDef.ColumnType);
if (columnTypeCode == "DateTime")
{
needsSystem = true;
}
if (columnTypeCode.Contains("[]"))
{
needsSystem = true;
}
else
{
if (columnDef.ForceToBit == true)
{
columnTypeCode = "bool";
}
if (columnDef.IsNullable && columnTypeCode != "string")
{
columnTypeCode += "?";
}
}
}
buildText.AppendLine("\t\tpublic " + columnTypeCode + " " + convertedColumnName + " { get; set; }");
}
buildText.AppendLine("\t}");
buildText.Append("}");
if (needsSystem)
{
buildText.Insert(0, "using System;" + Environment.NewLine);
}
File.WriteAllText(directory + modelName + ".cs", buildText.ToString());
}
}
/* TODO: consider whether we want to make collections when our tables are referenced */
}
