private List <CStoredProcedureParameter> GetParameters(CTable table)
{
var parameters = new List <CStoredProcedureParameter>();
foreach (var column in table.Column)
{
if (column.IsCreatedDate)
{
continue;
}
if (column.IsModifiedDate)
{
continue;
}
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{column.ColumnName}",
ParameterType = column.ColumnType,
ParameterTypeRaw = column.ColumnTypeRaw,
ParameterLength = column.ColumnLength,
DefaultToNull = column.IsRowVersion,
SourceColumn = column
};
parameters.Add(parameter);
}
return(parameters);
}
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);
}
private static CStoredProcedureParameter InferStoredProcedureParameter(CProtoMessageField parameter)
{
DbType parameterType;
string parameterTypeRaw;
bool isUserDefined = false;
if (parameter.IsTimestampMessage)
{
parameterType = DbType.DateTime2;
parameterTypeRaw = DbType.DateTime2.ToString();
}
else if (parameter.FieldType == GrpcType.__message)
{
isUserDefined = true;
parameterType = SqlMapper.SqlDbTypeToDbType(SqlMapper.GrpcTypeToSqlDbType(parameter.FieldType));
parameterTypeRaw = $"tt{parameter.MessageType}"; //todo: look it up
}
else
{
parameterType = SqlMapper.SqlDbTypeToDbType(SqlMapper.GrpcTypeToSqlDbType(parameter.FieldType));
parameterTypeRaw = SqlMapper.GrpcTypeToSqlRaw(parameter.FieldType);
}
var p = new CStoredProcedureParameter
{
ParameterName = parameter.FieldName,
ParameterType = parameterType,
ParameterTypeRaw = parameterTypeRaw,
ParameterTypeIsUserDefined = isUserDefined,
IsCollection = parameter.Repeated
};
if (p.ParameterType == System.Data.DbType.AnsiString)
{
p.ParameterLength = 255;
}
return(p);
}
private IEnumerable <CStoredProcedureParameter> GetStoredProcedureParameters(string solutionName,
string storedProcedureName)
{
_sl.SelectWorksheet("StoredProcedureParameters");
var parameters = new List <CStoredProcedureParameter>();
var colSolutionName = GetColumnIndex("SolutionName");
var colStoredProcedureName = GetColumnIndex("StoredProcedureName");
var colParameterName = GetColumnIndex("ParameterName");
var colParameterType = GetColumnIndex("ParameterType");
var colParameterTypeIsUserDefined = GetColumnIndex("ParameterTypeIsUserDefined");
var currentRow = 2;
while (!string.IsNullOrEmpty(_sl.GetCellValueAsString(currentRow, colStoredProcedureName)))
{
if (_sl.GetCellValueAsString(currentRow, colSolutionName) != solutionName)
{
currentRow++;
continue;
}
if (_sl.GetCellValueAsString(currentRow, colStoredProcedureName) != storedProcedureName)
{
currentRow++;
continue;
}
var storedProcedureParameter = new CStoredProcedureParameter
{
ParameterName = _sl.GetCellValueAsString(currentRow, colParameterName),
ParameterTypeRaw = _sl.GetCellValueAsString(currentRow, colParameterType),
ParameterTypeIsUserDefined = _sl.GetCellValueAsBoolean(currentRow, colParameterTypeIsUserDefined)
};
parameters.Add(storedProcedureParameter);
currentRow++;
}
return(parameters);
}
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);
}
private List <CStoredProcedureParameter> GetParameters(string connectionString, string sqlText,
CFunction function)
{
var functionParameters = 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(function)
{
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));
}
functionParameters.Add(storedProcedureParameter);
}
}
}
sqlConnection.Close();
}
return(functionParameters);
}
