public string GetFlywayFileName(CStoredProcedure storedProcedure)
{
if (storedProcedure.DatabaseType == DataStoreTypes.SqlServer)
{
return
($"{FlywayVersionNumber}__Create_Procedure_{storedProcedure.Schema.SchemaName}_{storedProcedure.StoredProcedureName}.sql");
}
else
{
return
($"{FlywayVersionNumber}__Create_Function_{storedProcedure.Schema.SchemaName}_{storedProcedure.StoredProcedureName}.sql");
}
}
public string Convert(CStoredProcedure storedProcedure)
{
var converter = new CStoredProcedureToCreateProcedureStatementConverter();
var createStoredProcedure = converter.Convert(storedProcedure);
//todo: this should be done when creating the CStoredProcedure.StoredProcBody
//var snakeCaseVisitor = new SqlServerSnakeCaseVisitor();
//createStoredProcedure.Accept(snakeCaseVisitor);
string scriptOut;
var scriptGen = new Sql120ScriptGenerator();
scriptGen.GenerateScript(createStoredProcedure, out scriptOut);
return(scriptOut);
}
public CStoredProcedure Convert(CTable table)
{
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
{
DataOperationIs = COperationIs.Delete | COperationIs.CRUD
};
storedProcedure.Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
};
storedProcedure.StoredProcedureName = $"{table.TableName}Delete";
//storedProcedure.ResultSetName = table.TableName;
storedProcedure.Parameter = new List <CStoredProcedureParameter>();
var pkColumns = table.GetPrimaryKeyColumns();
foreach (var pkColumn in pkColumns)
{
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{pkColumn.ColumnName}",
ParameterType = pkColumn.ColumnType,
ParameterTypeRaw = pkColumn.ColumnTypeRaw,
ParameterLength = pkColumn.ColumnLength,
SourceColumn = pkColumn
};
storedProcedure.Parameter.Add(parameter);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM [{table.Schema.SchemaName}].[{table.TableName}] ");
stringBuilder.AppendLine("WHERE");
var first = true;
foreach (var pkColumn in pkColumns)
{
if (!first)
{
stringBuilder.Append(" AND ");
}
stringBuilder.AppendLine($"\t\t[{pkColumn.ColumnName}] = @{pkColumn.ColumnName}");
first = false;
}
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
return(storedProcedure);
}
public CClass BuildResultEntityClass(CStoredProcedure storedProcedure, string resultSetName, KDataLayerProject dataLayerKProject)
{
if (string.IsNullOrEmpty(resultSetName))
{
return(null);
}
var converter = new CStoredProcedureToCClassConverter();
var @class = converter.ConvertByResultSet(storedProcedure);
//overrite the default namespace logic
@class.Namespace = new CNamespace
{
NamespaceName =
$"{dataLayerKProject.CompanyName}.{dataLayerKProject.ProjectName}{dataLayerKProject.NamespaceSuffix}.Data.Entities"
};
return(@class);
}
public CreateProcedureStatement Convert(CStoredProcedure storedProcedure)
{
//build body
string[] parts = { storedProcedure.Schema.SchemaName, storedProcedure.StoredProcedureName };
var createStoredProcedure = new CreateProcedureStatement();
///set schema and table name
///
var schemaObjectName = new SchemaObjectName();
schemaObjectName.Identifiers.Add(new Identifier {
Value = storedProcedure.Schema.SchemaName
});
schemaObjectName.Identifiers.Add(new Identifier {
Value = storedProcedure.StoredProcedureName
});
createStoredProcedure.ProcedureReference = new ProcedureReference();
createStoredProcedure.ProcedureReference.Name = schemaObjectName;
//add parameters
foreach (var param in storedProcedure.Parameter)
{
if (param.ParameterTypeIsUserDefined)
{
var dataType = new UserDataTypeReference();
dataType.Name = new SchemaObjectName();
dataType.Name.Identifiers.Add(new Identifier {
Value = param.ParameterTypeRaw
});
if (param.ParameterLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = param.ParameterLength.ToString()
});
}
createStoredProcedure.Parameters.Add(new ProcedureParameter
{
VariableName = new Identifier {
Value = $"@{param.ParameterName}"
},
DataType = dataType,
Value = param.DefaultToNull ? new NullLiteral() : null,
Modifier = param.ParameterTypeRaw.ToLower() == "sysname" ? ParameterModifier.None: ParameterModifier.ReadOnly //todo
});
}
else
{
var dataType = new SqlDataTypeReference();
var parameterName = param.ParameterName;
if (param.IsCollection)
{
parameterName += "_Collection";// temp solution for comma separate collection parameters
}
if (param.ParameterTypeRaw == "enum")
{
dataType.SqlDataTypeOption = SqlDataTypeOption.Int; //todo: review this
}
else
{
dataType.SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(param.ParameterTypeRaw);
}
if (param.ParameterLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = param.ParameterLength.ToString()
});
}
createStoredProcedure.Parameters.Add(new ProcedureParameter
{
VariableName = new Identifier {
Value = $"@{parameterName}"
},
DataType = dataType,
Value = param.DefaultToNull ? new NullLiteral() : null
});
}
}
var parser = new TSql120Parser(false);
createStoredProcedure.StatementList = new StatementList();
if (!string.IsNullOrEmpty(storedProcedure.StoredProcedureBody))
{
IList <ParseError> errors;
var script2 =
parser.Parse(new StringReader(storedProcedure.StoredProcedureBody), out errors) as TSqlScript;
if (errors.Count > 0)
{
RaiseSqlErrorsException(errors, storedProcedure.StoredProcedureBody);
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
createStoredProcedure.StatementList.Statements.Add(statement);
}
}
}
return(createStoredProcedure);
}
private string InferStoredProcedures(KProtoFile kProtoFile)
{
var codeWriter = new CodeWriter();
var convert = new CStoredProcedureToSSqlServerStoredProcedureConverter();
foreach (var service in kProtoFile.GeneratedProtoFile.ProtoService)
{
foreach (var rpc in service.Rpc)
{
var request = rpc.GetInnerMessageOrRequest();
//if (!request.ProtoField.Any(f=>f.FieldType != GrpcType.__message))
// continue;
var storedProc = new CStoredProcedure(DataStoreTypes.Unknown)
{
DerivedFrom = request, // rpc,
Schema = InferSchema(service),
StoredProcedureName = rpc.RpcName,
ResultSetName = rpc.Response.MessageName + "Dto"//"ResultSet"
};
foreach (var pp in request.ProtoField)
{
var parameter = pp;
if (parameter.FieldType == GrpcType.__message)
{
//if the inner message is just a single scalar field, lets use that
var m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm => pm.MessageName == parameter.MessageType);
if (m.ProtoField.Count == 1)
{
parameter = m.ProtoField.First();
/*
* if (parameter.FieldType == GrpcType.__message)
* {
* m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
* pm.MessageName == parameter.MessageType);
* if (m.ProtoField.Count == 1)
* {
* parameter = m.ProtoField.First();
*
* }
* else if (m.ProtoField.Count > 1)
* {
* Debugger.Break();
*
* }
* }*/
}
else if (m.ProtoField.Count > 1)
{
/*
* //todo: we need to pass table valued parameters to query
* Debugger.Break();
* parameter = m.ProtoField.First();
*
* if (parameter.FieldType == GrpcType.__message)
* {
* m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
* pm.MessageName == parameter.MessageType);
* if (m.ProtoField.Count == 1)
* {
* parameter = m.ProtoField.First();
*
* }
* else if (m.ProtoField.Count > 1)
* {
* //todo: we need to pass table valued parameters to query
* Debugger.Break();
* parameter = m.ProtoField.First();
*
* }
* }
*/
}
}
var p = InferStoredProcedureParameter(parameter);
if (!storedProc.Parameter.Exists(p2 => p2.ParameterName == p.ParameterName))
{
storedProc.Parameter.Add(p);
}
}
var tables = new List <CTable>();
var request2 = rpc.GetInnerMessageOrRequest();
InferTable(tables, rpc, request2, null);
var table = tables.First();
if (rpc.OperationIs.HasFlag(COperationIs.Get) ||
rpc.OperationIs.HasFlag(COperationIs.Find) ||
rpc.OperationIs.HasFlag(COperationIs.List) ||
rpc.OperationIs.HasFlag(COperationIs.Read) ||
rpc.OperationIs.HasFlag(COperationIs.Dequeue) ||
rpc.OperationIs.HasFlag(COperationIs.Check)
)
{
storedProc.StoredProcedureBody = $@"SELECT {table.ColumnAsColumnList()} FROM {table.Schema.SchemaName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}.{table.TableName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}";
}
else if (rpc.OperationIs.HasFlag(COperationIs.Set) ||
rpc.OperationIs.HasFlag(COperationIs.Update) ||
rpc.OperationIs.HasFlag(COperationIs.Create))
{
storedProc.StoredProcedureBody = $@"PRINT 'todo: UPDATE {table.Schema.SchemaName}.{table.TableName} SET ....'";
}
codeWriter.WriteLine(convert.Convert(storedProc));
codeWriter.WriteLine();
codeWriter.WriteLine("GO");
codeWriter.WriteLine();
this.StoredProcedure.Add(new KStoredProcedure
{
StoredProcedureName = storedProc.StoredProcedureName,
Schema = storedProc.Schema.SchemaName,
ReturnsMultipleRows = storedProc.ReturnsMultipleRows,
ResultSetName = storedProc.ResultSetName,
ParameterSetName = storedProc.ParameterSetName,
DerivedFrom = storedProc.DerivedFrom,
GeneratedStoredProcedure = storedProc
});
}
}
return(codeWriter.ToString());
}
public CStoredProcedure Convert(CTable table)
{
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);
storedProcedure.Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
};
storedProcedure.StoredProcedureName = $"{table.TableName}Update";
storedProcedure.ResultSetName = table.TableName;
storedProcedure.Parameter = new List <CStoredProcedureParameter>();
foreach (var column in table.Column)
{
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{column.ColumnName}",
ParameterType = column.ColumnType,
ParameterTypeRaw = column.ColumnTypeRaw,
ParameterLength = column.ColumnLength,
SourceColumn = column
};
storedProcedure.Parameter.Add(parameter);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"UPDATE [{table.Schema.SchemaName}].[{table.TableName}] ");
stringBuilder.AppendLine("SET");
var pkColumns = table.GetPrimaryKeyColumns();
var first = true;
foreach (var column in table.Column)
{
if (column.IsIdentity)
{
continue;
}
if (pkColumns.Contains(column))
{
continue;
}
if (!first)
{
stringBuilder.Append("\t\t,");
}
stringBuilder.AppendLine($"{column.ColumnName} = @{column.ColumnName}");
first = false;
}
stringBuilder.AppendLine("WHERE");
first = true;
foreach (var pkColumn in pkColumns)
{
if (!first)
{
stringBuilder.Append(" AND ");
}
stringBuilder.AppendLine($"\t\t{pkColumn.ColumnName} = @{pkColumn.ColumnName}");
}
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
return(storedProcedure);
}
public CStoredProcedure Convert(CTable table)
{
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);
storedProcedure.Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
};
storedProcedure.StoredProcedureName = $"{table.TableName}Add";
storedProcedure.ResultSetName = table.TableName;
storedProcedure.Parameter = new List <CStoredProcedureParameter>();
foreach (var column in table.Column)
{
if (column.IsIdentity)
{
continue;
}
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{column.ColumnName}",
ParameterType = column.ColumnType,
ParameterTypeRaw = column.ColumnTypeRaw,
ParameterLength = column.ColumnLength,
SourceColumn = column
};
storedProcedure.Parameter.Add(parameter);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"INSERT INTO [{table.Schema.SchemaName}].[{table.TableName}] ");
stringBuilder.Append("(");
var first = true;
foreach (var column in table.Column)
{
if (column.IsIdentity)
{
continue;
}
if (!first)
{
stringBuilder.Append("\t\t,");
}
stringBuilder.AppendLine(column.ColumnName);
first = false;
}
stringBuilder.AppendLine(")");
stringBuilder.AppendLine("VALUES");
stringBuilder.Append("(");
first = true;
foreach (var parameter in storedProcedure.Parameter)
{
if (!first)
{
stringBuilder.Append("\t\t,");
}
first = false;
stringBuilder.AppendLine(parameter.ParameterName);
}
stringBuilder.AppendLine(")");
stringBuilder.AppendLine();
var identityCol = table.Column.FirstOrDefault(c => c.IsIdentity);
if (identityCol != null)
{
stringBuilder.AppendLine(
$"SELECT CAST(SCOPE_IDENTITY() as {SqlMapper.DbTypeToSqlDbType(identityCol.ColumnType)})");
}
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
return(storedProcedure);
}
public string Convert(CStoredProcedure storedProcedure)
{
var converter = new CStoredProcedureToCreateProcedureStatementConverter();
var createStoredProcedure = converter.Convert(storedProcedure);
var snakeCaseVisitor = new SnakeCaseVisitor();
createStoredProcedure.Accept(snakeCaseVisitor);
var codeWriter = new CodeWriter();
codeWriter.WriteLine($@"CREATE OR REPLACE FUNCTION {storedProcedure.Schema.SchemaName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{storedProcedure.StoredProcedureName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} ");
codeWriter.WriteLine($@"(");
bool first = true;
foreach (var parameter in storedProcedure.Parameter)
{
if (!first)
{
codeWriter.Write(", ");
}
first = false;
var parameterType = string.Empty;
if (parameter.ParameterTypeIsUserDefined)
{
if (parameter.ParameterTypeRaw == "sysname")
{
//fixup. Todo: implement a better way
parameterType = SqlMapper.NpgsqlDbTypeToPostgres(NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.ParameterLength = 128;
}
else
{
//todo: need schema included
parameterType = $"{parameter.ParameterTypeRawSchema.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{parameter.ParameterTypeRaw.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} []";
}
}
else
{
parameterType = SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(parameter.ParameterType));
}
//todo: remove p_ prefix. Too much UI change, better to manually fix the stored procs
var parameterName = "p_" + parameter.ParameterName;
codeWriter.Write($@"{parameterName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} {parameterType} ");
if (parameter.DoesNeedLength())
{
codeWriter.Write($"({parameter.ParameterLength})");
}
}
codeWriter.WriteLine();
codeWriter.WriteLine($@")");
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.Write($@"RETURNS TABLE (");
{
var first2 = true;
foreach (var resultCol in storedProcedure.ResultSet)
{
if (!first2)
{
codeWriter.WriteLine(",");
}
first2 = false;
codeWriter.Write($"{resultCol.ColumnName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}");
codeWriter.Write($" {SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType))}");
if (resultCol.DoesNeedLength())
{
codeWriter.Write($"({resultCol.ColumnLength})");
}
if (!resultCol.IsNullable)
{
codeWriter.Write(" NOT NULL");
}
}
}
codeWriter.Write($@") ");
}
else
{
codeWriter.Write("RETURNS void ");
}
codeWriter.WriteLine($@"AS $func$");
codeWriter.WriteLine($@"BEGIN");
codeWriter.Indent();
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.WriteLine($@"RETURN QUERY");
}
codeWriter.WriteLine("--TODO: Manually convert the sql below to Postgresql");
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.Write("SELECT ");
var first3 = true;
var sampleDataService = new SamplePostgresDataService();
foreach (var resultCol in storedProcedure.ResultSet)
{
if (!first3)
{
codeWriter.Write(",");
}
first3 = false;
var npgsqlType = SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType);
var postgresType = SqlMapper.NpgsqlDbTypeToPostgres(npgsqlType);
codeWriter.Write($"CAST ({sampleDataService.GetSampleData(npgsqlType, resultCol.ColumnLength)} AS {postgresType} ) AS {resultCol.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, storedProcedure.ConvertToSnakeCase)}");
}
codeWriter.WriteLine(";");
}
codeWriter.WriteLine("/*");
//codeWriter.WriteLine(storedProcedure.StoredProcedureBody);
var scriptGen = new Sql120ScriptGenerator();
foreach (var statement2 in createStoredProcedure.StatementList.Statements)
{
string scriptOut;
scriptGen.GenerateScript(statement2, out scriptOut);
codeWriter.WriteLine(scriptOut);
}
codeWriter.WriteLine("*/");
codeWriter.Unindent();
codeWriter.WriteLine($@"END");
codeWriter.WriteLine($@"$func$ LANGUAGE plpgsql;");
return(codeWriter.ToString());
}
public CStoredProcedure Convert(CTable table)
{
//http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
{
Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
},
StoredProcedureName = $"{table.TableName}AddUpdate",
ParameterSetName = table.TableName,
ResultSetName = $"{table.TableName}AddUpdateResult",
ReturnsMultipleRows = true,
DataOperationIs = COperationIs.Add | COperationIs.Update | COperationIs.CRUD
};
table.InsertStoredProcedure = storedProcedure;
storedProcedure.Parameter.AddRange(GetParameters(table));
var primaryKeyCol = table.Column.FirstOrDefault(c => c.IsPrimaryKey);
var insertUpdateColumnList = GetInsertUpdateColumnList(table);
var insertUpdateColumnMappings = GetInsertUpdateColumnMappings(table);
var insertUpdateColumnValues = GetInsertUpateColumnValues(table);
var redefines = GetRedefines(table);
var pkCompares = GetPrimaryKeyCompares(table);
var rowVersionColumn = table.GetRowVersionColumn();
var insertCondition = rowVersionColumn != null
? $" AND NEW_T.[{primaryKeyCol.ColumnName}] = 0 AND NEW_T.[{rowVersionColumn.ColumnName}] IS NULL "
: string.Empty;
var updateCondition = rowVersionColumn != null
? $" AND (NEW_T.[{rowVersionColumn.ColumnName}] IS NULL OR T.[{rowVersionColumn.ColumnName}] = NEW_T.[{rowVersionColumn.ColumnName}]) "
: string.Empty;
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine(
"SET XACT_ABORT ON;"); //http://michaeljswart.com/2015/10/dont-abandon-your-transactions/
stringBuilder.AppendLine();
//create table variable to hold output
stringBuilder.Append($@"DECLARE @Output TABLE (Action VARCHAR(20),
[{primaryKeyCol.ColumnName}] {
SqlMapper.DbTypeToSqlDataTypeOption(primaryKeyCol.ColumnType)
}");
if (primaryKeyCol.ColumnLength > 0)
{
stringBuilder.Append($"({primaryKeyCol.ColumnLength})");
}
var rowVersionCol = string.Empty;
var insertedRowVersionCol = string.Empty;
if (rowVersionColumn != null)
{
rowVersionCol = $", {rowVersionColumn.ColumnName}";
insertedRowVersionCol =
$", inserted.[{rowVersionColumn.ColumnName}] as '[{rowVersionColumn.ColumnName}]'";
stringBuilder.Append($", [{rowVersionColumn.ColumnName}] binary(8)");
}
stringBuilder.AppendLine($");");
stringBuilder.AppendLine();
stringBuilder.AppendLine(
$@"MERGE [{table.Schema.SchemaName}].[{table.TableName}] WITH (HOLDLOCK) AS T
USING (SELECT {redefines} ) as NEW_T
ON {pkCompares}
WHEN MATCHED {updateCondition} THEN
UPDATE
SET
{insertUpdateColumnMappings}
WHEN NOT MATCHED {insertCondition} THEN
INSERT
(
{insertUpdateColumnList}
)
VALUES
(
{insertUpdateColumnValues}
)
OUTPUT
$action, inserted.[{primaryKeyCol.ColumnName}] as '[{primaryKeyCol.ColumnName}]' {
insertedRowVersionCol
} INTO @Output;
--TODO: Use OUTPUT to insert original values into Audit tables
SELECT Action, [{primaryKeyCol.ColumnName}] {rowVersionCol} FROM @Output;");
/*
* var identityCol = table.Column.FirstOrDefault(c => c.IsIdentity);
* if (identityCol != null)
* {
* stringBuilder.AppendLine();
* stringBuilder.AppendLine($"SELECT CAST(SCOPE_IDENTITY() as {SqlMapper.DbTypeToSqlDbType( identityCol.ColumnType)})");
*
* }
*/
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = "Action",
ColumnTypeRaw = "varchar",
ColumnSqlDbType = SqlDbType.VarChar,
ColumnType = DbType.AnsiString,
ColumnLength = 20
});
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = primaryKeyCol.ColumnName,
ColumnTypeRaw = primaryKeyCol.ColumnTypeRaw,
ColumnSqlDbType = primaryKeyCol.ColumnSqlDbType,
ColumnType = primaryKeyCol.ColumnType,
ColumnLength = primaryKeyCol.ColumnLength
});
if (rowVersionColumn != null)
{
storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
{
ColumnName = rowVersionColumn.ColumnName,
ColumnTypeRaw = rowVersionColumn.ColumnTypeRaw,
ColumnType = rowVersionColumn.ColumnType,
ColumnSqlDbType = rowVersionColumn.ColumnSqlDbType,
ColumnLength = rowVersionColumn.ColumnLength
});
}
return(storedProcedure);
}
private CProtoRpc BuildRpcFromStoredProc(CProtoFile protoFile, CProtoService protoService, CMethod method,
CStoredProcedure storedProcedure)
{
var rpc = new CProtoRpc(protoService)
{
RpcName = storedProcedure.StoredProcedureName,
RpcDescription = storedProcedure.StoredProcedureDescription,
//DomainModelName = storedProcedure.ResultSetName,
DerivedFrom = storedProcedure
};
rpc.Request = new CProtoMessage(rpc)
{
IsRequest = true,
MessageName = $"{storedProcedure.StoredProcedureName}Request"
};
rpc.Response = new CProtoMessage(rpc)
{
IsResponse = true,
MessageName = $"{storedProcedure.StoredProcedureName}Response"
};
var requestMessage = rpc.Request;
if (!string.IsNullOrEmpty(storedProcedure.ParameterSetName))
{
rpc.Request.ProtoField.Add(new CProtoMessageField(storedProcedure)
{
IsScalar = false,
MessageType = storedProcedure.ParameterSetName,
FieldName = storedProcedure.ParameterSetName
});
requestMessage = new CProtoMessage(rpc)
{
IsRequest = true,
MessageName = storedProcedure.ParameterSetName
};
if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == requestMessage.MessageName))
{
protoFile.ProtoMessage.Add(requestMessage);
}
}
foreach (var parameter in storedProcedure.Parameter)
{
var field = new CProtoMessageField(parameter)
{
FieldName = parameter.ParameterName //.SourceColumn.ColumnName,
};
if (!parameter.ParameterTypeIsUserDefined)
{
var sqlType = SqlMapper.ParseValueAsSqlDbType(parameter.ParameterTypeRaw);
field.FieldType = SqlMapper.SqlDbTypeToGrpcType(sqlType);
}
else
{
//todo: property handle user defined sql types (tables)
//lookup table type
//for now, use the data type of the first column, assumes single column table
var tableType = FindTableType(parameter.ParameterTypeRaw);
var converter = new CTableTypeToCClassConverter();
var @class = converter.Convert(tableType.GeneratedTableType);
field.FieldType = GrpcType.__string;
field.IsScalar = false;
field.Repeated = true;
field.MessageType = $@"{@class.ClassName}";
if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == field.MessageType))
{
//create a message
var tableTypeDerivedMessage = new CProtoMessage(rpc)
{
MessageName = field.MessageType
};
foreach (var property in @class.Property)
{
var field2 = new CProtoMessageField(property)
{
FieldName = property.PropertyName,
FieldType = SqlMapper.ClrTypeToGrpcType(SqlMapper.ClrTypeAliasToClrType(property.Type))
};
tableTypeDerivedMessage.ProtoField.Add(field2);
}
protoFile.ProtoMessage.Add(tableTypeDerivedMessage);
}
}
requestMessage.ProtoField.Add(field);
}
var responseMessage = rpc.Response;
if (!string.IsNullOrEmpty(storedProcedure.ResultSetName))
{
rpc.Response.ProtoField.Add(new CProtoMessageField(null)
{
IsScalar = false,
Repeated = true,
MessageType = storedProcedure.ResultSetName,
FieldName = storedProcedure.ResultSetName
});
responseMessage = new CProtoMessage(rpc)
{
IsResponse = true,
MessageName = storedProcedure.ResultSetName
};
if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == responseMessage.MessageName))
{
protoFile.ProtoMessage.Add(responseMessage);
}
}
foreach (var resultColumn in storedProcedure.ResultSet)
{
var field = new CProtoMessageField(resultColumn)
{
FieldName = resultColumn.ColumnName,
FieldType = SqlMapper.SqlDbTypeToGrpcType(resultColumn.ColumnSqlDbType)
};
responseMessage.ProtoField.Add(field);
}
return(rpc);
}
public CStoredProcedure Read(string connectionString, string schema, string storedProcedureName, string storedProcedureText)
{
this.ConnectionString = connectionString;
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);
storedProcedure.Schema = new CSchema {
SchemaName = schema
};
storedProcedure.StoredProcedureName = storedProcedureName;
storedProcedure.StoredProcedureBody = GetStoredProcBody(storedProcedureText);
storedProcedure.Parameter.AddRange(GetParameters(ConnectionString, storedProcedureText, storedProcedure));
using (var sqlConnection = new SqlConnection(ConnectionString))
{
var sqlViewQuery =
$@"DECLARE @sql NVARCHAR(MAX) = N'EXEC [{schema}].[{storedProcedureName}];';
SELECT dm.name as 'column_name', t.name as 'data_type',dm.is_nullable,dm.max_length as 'character_maximum_length',is_hidden
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) dm
join sys.types t on dm.system_type_id = t.system_type_id
and dm.system_type_id = t.user_type_id
WHERE is_hidden=0;";
sqlConnection.Open();
var sqlCommand = new SqlCommand(sqlViewQuery, sqlConnection);
var dataReader = sqlCommand.ExecuteReader();
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn {
ColumnName = "column_name"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "data_type"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "is_nullable"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "character_maximum_length", DataType = typeof(int)
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "storedprocedure_name"
});
dataTable.Columns.Add(new DataColumn {
ColumnName = "storedprocedure_schema"
});
while (dataReader.Read())
{
var dataRow = dataTable.NewRow();
dataRow["column_name"] = dataReader["column_name"];
dataRow["data_type"] = dataReader["data_type"];
dataRow["is_nullable"] = dataReader["is_nullable"];
dataRow["character_maximum_length"] = dataReader["character_maximum_length"];
dataRow["storedprocedure_name"] = storedProcedureName;
dataRow["storedprocedure_schema"] = schema;
dataTable.Rows.Add(dataRow);
}
foreach (var row2 in dataTable.Rows)
{
var row = row2 as DataRow;
var column = new CColumn(storedProcedure)
{
ColumnName = row[0] != DBNull.Value ? (string)row[0] : null,
ColumnTypeRaw = (string)row[1],
ColumnSqlDbType = SqlMapper.ParseValueAsSqlDbType((string)row[1]),
ColumnType = SqlMapper.GetDbType((string)row[1]),
ColumnLength = row[3] != DBNull.Value ? (int)row[3] : -1
};
storedProcedure.ResultSet.Add(column);
}
if (storedProcedure.HasResultSet)
{
storedProcedure.ResultSetName = storedProcedure.StoredProcedureName + "Dto"; // "ResultSet";
}
return(storedProcedure);
}
}
private List <CStoredProcedureParameter> GetParameters(string connectionString, string sqlText,
CStoredProcedure storedProcedure)
{
var storedProcedureParameters = new List <CStoredProcedureParameter>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var statementList = new StatementList();
IList <ParseError> errors;
var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createProcedureStatement = statement as CreateProcedureStatement;
if (createProcedureStatement == null)
{
continue;
}
foreach (var param in createProcedureStatement.Parameters)
{
//(new System.Collections.Generic.Mscorlib_CollectionDebugView<Microsoft.SqlServer.TransactSql.ScriptDom.Literal>
// (((Microsoft.SqlServer.TransactSql.ScriptDom.ParameterizedDataTypeReference)param.DataType).Parameters).Items[0]).Value;
var length = 0;
if ((param.DataType as ParameterizedDataTypeReference).Parameters.Count > 0)
{
var lengthString = (param.DataType as ParameterizedDataTypeReference).Parameters[0].Value;
if ((param.DataType as ParameterizedDataTypeReference).Parameters[0] is Microsoft.SqlServer.TransactSql.ScriptDom.MaxLiteral)
{
length = -1;
}
else
{
length = int.Parse(lengthString);
}
}
var storedProcedureParameter = new CStoredProcedureParameter
{
ParameterName = param.VariableName.Value.Replace("@", "").Replace("_Collection", ""),
ParameterTypeIsUserDefined = param.DataType is UserDataTypeReference,
ParameterTypeRaw = param.DataType.Name.BaseIdentifier.Value,
ParameterTypeRawSchema = param.DataType.Name?.SchemaIdentifier?.Value,
SourceColumn =
new CColumn(storedProcedure)
{
ColumnName = param.VariableName.Value.Replace("@", "")
},
IsCollection = param.VariableName.Value.EndsWith("_Collection")
};
if (length > 0)
{
storedProcedureParameter.ParameterLength = length;
storedProcedureParameter.SourceColumn.ColumnLength = length;
}
if (!storedProcedureParameter.ParameterTypeIsUserDefined)
{
storedProcedureParameter.ParameterType =
SqlMapper.SqlDbTypeToDbType(
SqlMapper.ParseValueAsSqlDbType(param.DataType.Name.BaseIdentifier.Value));
}
storedProcedureParameters.Add(storedProcedureParameter);
}
}
}
sqlConnection.Close();
}
return(storedProcedureParameters);
}
public CClass ConvertByResultSet(CStoredProcedure storedProcedure)
{
if (string.IsNullOrEmpty(storedProcedure.ResultSetName))
{
throw new Exception("cannot create CClass without a class name");
}
var @class = new CClass(storedProcedure.ResultSetName)
{
DerivedFrom = storedProcedure,
Namespace = new CNamespace {
NamespaceName = storedProcedure.Schema.SchemaName
}
};
if (storedProcedure.ResultSet.Exists(r => r.ColumnType == DbType.DateTime2))
{
@class.NamespaceRef.Add(new CNamespaceRef()
{
ReferenceTo = new CNamespace()
{
NamespaceName = "System"
}
});
}
if (!string.IsNullOrEmpty(storedProcedure.ResultSetName))
{
foreach (var column in storedProcedure.ResultSet)
{
var prop = new CProperty
{
PropertyName = column.ColumnName,
Type = column.ColumnType.ToClrTypeName(),
MaxLength = column.ColumnLength
};
@class.Property.Add(prop);
}
}
return(@class);
/*
* var view = new SView
* {
* Schema = new SSchema { SchemaName = storedProcedure.Schema.SchemaName},
* ViewName = storedProcedure.StoredProcedureName
* };
* view.Column = new List<SColumn>();
*
* foreach (var col in storedProcedure.ResultSet)
* {
* var column = new SColumn(view)
* {
* ColumnName = col.ColumnName,
* ColumnTypeRaw = col.ColumnTypeRaw,
* ColumnSqlDbType = col.ColumnSqlDbType,
* ColumnType = col.ColumnType,
* ColumnLength = col.ColumnLength
* };
* view.Column.Add(column);
* }
* return view;
*/
}
