private IEnumerable <KTableType> GetTableTypes(string solutionName)
{
_sl.SelectWorksheet("TableTypes");
var tableTypes = new List <KTableType>();
var colSolutionName = GetColumnIndex("SolutionName");
var colSchema = GetColumnIndex("Schema");
var colTableTypeName = GetColumnIndex("TableTypeName");
var colColumnName = GetColumnIndex("ColumnName");
var colColumnTypeRaw = GetColumnIndex("ColumnTypeRaw");
var colColumnLength = GetColumnIndex("ColumnLength");
var currentRow = 2;
while (!string.IsNullOrEmpty(_sl.GetCellValueAsString(currentRow, colSolutionName)))
{
if (_sl.GetCellValueAsString(currentRow, colSolutionName) != solutionName)
{
currentRow++;
continue;
}
var tableType = new KTableType
{
Schema = _sl.GetCellValueAsString(currentRow, colSchema),
TableTypeName = _sl.GetCellValueAsString(currentRow, colTableTypeName)
};
tableType.GeneratedTableType = new CTableType(Utility.DataStoreTypes.Unknown)
{
TableName = tableType.TableTypeName,
Schema = new CSchema {
SchemaName = tableType.Schema
}
};
while (tableType.TableTypeName == _sl.GetCellValueAsString(currentRow, colTableTypeName))
{
var column = new CColumn(tableType.GeneratedTableType)
{
ColumnName = _sl.GetCellValueAsString(currentRow, colColumnName),
ColumnTypeRaw = _sl.GetCellValueAsString(currentRow, colColumnTypeRaw),
ColumnLength = _sl.GetCellValueAsInt32(currentRow, colColumnLength)
};
tableType.GeneratedTableType.Column.Add(column);
currentRow++;
}
//tableType.GeneratedTableType.Parameter.AddRange(GetStoredProcedureParameters(storedProcedure.StoredProcedureName));
tableTypes.Add(tableType);
// currentRow++;
}
return(tableTypes);
}
private List <KTableType> CreateTableTypesInDb(KDataStoreProject kDataStoreProject, string connectionString, string sqlText)
{
var tableTypes = new List <KTableType>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var 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 createTypeTableStatement = statement as CreateTypeTableStatement;
if (createTypeTableStatement == null)
{
continue;
}
var viewSchemaName = createTypeTableStatement.Name.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
scriptGen.GenerateScript(statement, out var scriptOut);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
var tableType = new KTableType
{
Schema =
createTypeTableStatement.Name.SchemaIdentifier.Value,
TableTypeName =
createTypeTableStatement.Name.BaseIdentifier.Value,
TableTypeText = scriptOut
};
var oldTableType = kDataStoreProject.OldTableType.FirstOrDefault(tt => tt.TableTypeName == tableType.TableTypeName); //todo: compare schema
if (oldTableType != null)
{
tableType.DerivedFrom = oldTableType.DerivedFrom;
}
tableTypes.Add(tableType);
}
}
sqlConnection.Close();
}
return(tableTypes);
}
protected virtual CClass BuildTableTypeList(KTableType kTableType)
{
throw new NotImplementedException();
}
protected override CClass BuildTableTypeList(KTableType kTableType)
{
return(null);
}
