public static void GenerateSelectCountMethod(CodeStringBuilder code, String methodName, String script)
{
code.CodeBlockBegin("public int {0}()", methodName);
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"{0}\"))", script);
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
code.CodeBlockBegin("using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())");
code.CodeBlockBegin("if (reader.Read())");
code.Append("return reader.GetInt32(0);");
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.Append("throw new InvalidOperationException(\"Select count failed.\");");
code.CodeBlockEnd();
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // try
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // method
}
private static void AddUsing(CodeStringBuilder output)
{
output.AppendLine("using System;");
output.AppendLine("using System.Collections.Generic;");
output.AppendLine("using System.Threading;");
output.AppendLine("using System.Threading.Tasks;");
output.AppendLine();
}
public static void GenerateDeleteMethod(CodeStringBuilder code, String methodName, String script, List <ColumnInfo> key)
{
StringBuilder parameters = new StringBuilder();
for (int a = 0; a < key.Count; ++a)
{
if (a > 0)
{
parameters.Append(", ");
}
parameters.AppendFormat("{0} {1}", key[a].FullTypeName, key[a].LocalVariableName);
}
code.CodeBlockBegin("public void {0}({1})", methodName, parameters.ToString());
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"{0}\"))", script);
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
foreach (ColumnInfo columnInfo in key)
{
code.AppendLineFormat("// Parameter settings: {0}", columnInfo.ParameterName);
GenerateSqlParameter(
code,
columnInfo.LocalVariableName,
columnInfo.LocalParameterVariableName,
columnInfo.ParameterName,
columnInfo.DbType,
columnInfo.IsNullable,
System.Data.ParameterDirection.Input,
columnInfo.MaxLength);
code.AppendLine();
}
code.Append("command.ExecuteNonQuery();");
code.CodeBlockEnd(); // try
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
code.CodeBlockEnd(); // using command
code.CodeBlockEnd(); // method
}
private static IDisposable[] DeclareClass(ClassDeclarationSyntax node, CodeStringBuilder output)
{
var owners = ImmutableArray <string> .Empty;
foreach (var ancestor in node.AncestorMembers())
{
if (ancestor.Kind == SyntaxKind.NamespaceDeclaration)
{
owners = owners.Insert(0, "namespace " + ancestor.Name);
}
else
{
owners = owners.Insert(0, "partial class " + ancestor.Name);
}
}
owners = owners.Add("partial class " + node.Identifier.WithoutTrivia());
var result = new IDisposable[owners.Length];
for (var i = 0; i < owners.Length; i++)
{
output
.AppendLine(owners[i])
.AppendLine("{");
result[i] = output.Indent();
}
return(result);
}
public static void GenerateSelectPagedMethod(CodeStringBuilder code, String methodName, String entityTypeName, String mappingMethodName, String script, List <ColumnInfo> key)
{
if (key == null || key.Count == 0)
{
return;
}
String firstIndexParameterName = "@__FirstIndex", firstIndexLocalVariableName = "pFirstIndex", firstIndex = "firstIndex";
String lastIndexParameterName = "@__LastIndex", lastIndexLocalVariableName = "pLastIndex", lastIndex = "lastIndex";
code.CodeBlockBegin("public List<{1}> {0}(int {2}, int {3})", methodName, entityTypeName, firstIndex, lastIndex);
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"{0}\"))", script);
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
GenerateSqlParameter(code, firstIndex, firstIndexLocalVariableName, firstIndexParameterName, System.Data.SqlDbType.Int, false, System.Data.ParameterDirection.Input, -1);
code.AppendLine();
GenerateSqlParameter(code, lastIndex, lastIndexLocalVariableName, lastIndexParameterName, System.Data.SqlDbType.Int, false, System.Data.ParameterDirection.Input, -1);
code.AppendLine();
code.CodeBlockBegin("using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())");
code.AppendLineFormat("List<{0}> result = new List<{0}>();", entityTypeName);
code.CodeBlockBegin("while (reader.Read())");
code.AppendFormat("result.Add({0}(reader));", mappingMethodName);
code.CodeBlockEnd();
code.Append("return result;");
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // try
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // method
}
public static void GenerateModel(CodeStringBuilder code, TableInfo tableInfo)
{
code.CodeBlockBegin("public partial class {0}", tableInfo.ClassName);
foreach (var c in tableInfo.Columns)
{
code.AppendLine(ToAutomaticProperty(c.PropertyName, c.FullTypeName));
}
code.CodeBlockEnd();
}
/// <summary>
/// generate C binder
/// </summary>
/// <param name="ispc_headerFilename"></param>
/// <param name="outputC_filename"></param>
void GenerateCBinder(CodeCompilationUnit cu, string ispc_headerFilename, string outputC_filename)
{
//from cu=> we generate interface c
CodeStringBuilder sb = new CodeStringBuilder();
sb.AppendLine("//AUTOGEN," + DateTime.Now.ToString("s"));
sb.AppendLine("//Tools: ispc and BridgeBuilder");
sb.AppendLine("//Src: " + ispc_headerFilename);
sb.AppendLine("#include <stdio.h>");
sb.AppendLine("#include <stdlib.h>");
sb.AppendLine("//Include the header file that the ispc compiler generates");
sb.AppendLine($"#include \"{ Path.GetFileName(ispc_headerFilename) }\"");
sb.AppendLine(@"#ifdef _WIN32
#define MY_DLL_EXPORT __declspec(dllexport)
#else
#define MY_DLL_EXPORT
#endif
");
sb.AppendLine("using namespace ispc;");
//c_inf.AppendLine("using namespace ispc;");
sb.AppendLine("extern \"C\"{");
foreach (CodeMemberDeclaration mb in cu.GlobalTypeDecl.GetMemberIter())
{
if (mb is CodeTypeDeclaration namespaceDecl &&
namespaceDecl.Kind == TypeKind.Namespace)
{
//ispc output
//iterate member inside the current namespace
foreach (CodeMemberDeclaration nsMember in namespaceDecl.GetMemberIter())
{
if (nsMember is CodeMethodDeclaration met)
{
sb.AppendLine("MY_DLL_EXPORT "); //WINDOWS
sb.Append(met.ToString(IspcBridgeFunctionNamePrefix));
sb.AppendLine("{");
string ret = met.ReturnType.ToString();
if (ret != "void")
{
sb.Append("return ");
}
sb.Append("ispc::" + met.Name + "(");
int par_i = 0;
foreach (CodeMethodParameter par in met.Parameters)
{
if (par_i > 0)
{
sb.Append(",");
}
sb.Append(par.ParameterName);
par_i++;
}
sb.AppendLine(");");
sb.AppendLine("}");
}
else if (nsMember is CodeTypeDeclaration other && other.Kind == TypeKind.Struct)
{
//skip C struct regen
}
public static void GenerateStoredProcedureMethod(CodeStringBuilder code, StoredProcedureInfo storedProcedureInfo)
{
String parameters = "";
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
if (parameters.Length > 0)
{
parameters += ", ";
}
if (parameterInfo.IsOutput)
{
parameters += "out ";
}
parameters += parameterInfo.FullTypeName;
parameters += " ";
parameters += parameterInfo.LocalVariableName;
}
code.CodeBlockBegin("public void Execute{0}({1})", storedProcedureInfo.Name, parameters);
code.AppendLine("BeginTransaction();");
code.AppendLine();
code.CodeBlockBegin("try");
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"[{0}].[{1}]\"))", storedProcedureInfo.Schema, storedProcedureInfo.Name);
code.AppendLine("command.CommandType = System.Data.CommandType.StoredProcedure;");
code.AppendLine();
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
GenerateSqlParameter(code,
parameterInfo.LocalVariableName,
parameterInfo.LocalParameterVariableName,
parameterInfo.ParameterName,
parameterInfo.DbType,
parameterInfo.HasDefault,
parameterInfo.IsOutput ? System.Data.ParameterDirection.Output : System.Data.ParameterDirection.Input,
-1);
code.AppendLine();
}
code.AppendLine("command.ExecuteNonQuery();");
code.AppendLine();
foreach (StoredProcedureParameterInfo parameterInfo in storedProcedureInfo.Parameters)
{
if (parameterInfo.IsOutput == false)
{
continue;
}
code.AppendFormat("{0} = ({1}){2}.Value;", parameterInfo.LocalVariableName, parameterInfo.FullTypeName, parameterInfo.LocalParameterVariableName);
code.AppendLine();
}
// end of try
code.CodeBlockEnd();
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
// end of using
code.CodeBlockEnd();
// end of try
code.AppendLine();
code.Append("CommitTransaction();");
code.CodeBlockEnd();
code.CodeBlockBegin("catch");
code.AppendLine("RollbackTransaction();");
code.AppendLine("throw;");
code.CodeBlockEnd();
code.CodeBlockEnd();
}
public static void GenerateInsertMethod(CodeStringBuilder code, String methodName, String entityTypeName, String script, List <ColumnInfo> insertableColumns, List <ColumnInfo> outputColumns)
{
code.CodeBlockBegin("public void {0}({1} entity)", methodName, entityTypeName);
code.CodeBlockBegin("if (entity == null)");
code.Append("throw new ArgumentNullException(\"entity\");");
code.CodeBlockEnd();
code.AppendLine();
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"{0}\"))", script);
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
foreach (ColumnInfo columnInfo in insertableColumns)
{
code.AppendLineFormat("// Parameter settings: {0}", columnInfo.ParameterName);
GenerateSqlParameter(
code,
String.Format("entity.{0}", columnInfo.PropertyName),
columnInfo.LocalParameterVariableName,
columnInfo.ParameterName,
columnInfo.DbType,
columnInfo.IsNullable,
System.Data.ParameterDirection.Input,
columnInfo.MaxLength);
code.AppendLine();
}
if (outputColumns != null && outputColumns.Count > 0)
{
code.CodeBlockBegin("using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())");
code.CodeBlockBegin("if (reader.Read())");
int ordinal = 0;
foreach (ColumnInfo columnInfo in outputColumns)
{
GenerateColumnMapperFromSqlDataReader(code, columnInfo, ordinal++);
}
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.Append("throw new InvalidOperationException(\"Insert failed.\");");
code.CodeBlockEnd();
code.CodeBlockEnd();
}
else
{
code.CodeBlockBegin("if (command.ExecuteNonQuery() <= 0)");
code.Append("throw new InvalidOperationException(\"Insert failed.\");");
code.CodeBlockEnd();
}
code.CodeBlockEnd();
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
code.CodeBlockEnd();
code.CodeBlockEnd();
}
public static void GenerateSelectMethod(CodeStringBuilder code, String methodName, String entityTypeName, String script, List <ColumnInfo> key, bool singleResult, String mappingMethod)
{
StringBuilder parameters = new StringBuilder();
if (key != null)
{
for (int a = 0; a < key.Count; ++a)
{
if (a > 0)
{
parameters.Append(", ");
}
parameters.AppendFormat("{0} {1}", key[a].FullTypeName, key[a].LocalVariableName);
}
}
if (singleResult)
{
code.CodeBlockBegin("public {0} {1}({2})", entityTypeName, methodName, parameters.ToString());
}
else
{
code.CodeBlockBegin("public List<{0}> {1}({2})", entityTypeName, methodName, parameters.ToString());
}
code.CodeBlockBegin("using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(\"{0}\"))", script);
code.CodeBlockBegin("try");
code.AppendLine("PopConnection(command);");
code.AppendLine();
if (key != null)
{
foreach (ColumnInfo columnInfo in key)
{
code.AppendLineFormat("// Parameter settings: {0}", columnInfo.ParameterName);
GenerateSqlParameter(code,
columnInfo.LocalVariableName,
columnInfo.LocalParameterVariableName,
columnInfo.ParameterName,
columnInfo.DbType,
columnInfo.IsNullable,
System.Data.ParameterDirection.Input,
columnInfo.MaxLength);
code.AppendLine();
}
}
code.CodeBlockBegin("using (System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())");
if (singleResult)
{
code.CodeBlockBegin("if (reader.Read())");
code.AppendFormat("return {0}(reader);", mappingMethod);
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.Append("return null;");
code.CodeBlockEnd();
}
else
{
code.AppendLineFormat("List<{0}> result = new List<{0}>();", entityTypeName);
code.CodeBlockBegin("while (reader.Read())");
code.AppendFormat("result.Add({0}(reader));", mappingMethod);
code.CodeBlockEnd();
code.Append("return result;");
}
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // try
code.CodeBlockBegin("finally");
code.Append("PushConnection(command);");
code.CodeBlockEnd();
code.CodeBlockEnd(); // using
code.CodeBlockEnd(); // method
}
public static void GenerateDatabaseAccessCode(DatabaseSchemaInfo schemaInfo, String outputFilename)
{
CodeStringBuilder code = new CodeStringBuilder();
// usings
code.AppendLine("using System;");
code.AppendLine("using System.Collections.Generic;");
code.AppendLine();
// namespace
code.CodeBlockBegin("namespace {0}", schemaInfo.Namespace);
// model
code.AppendLine("#region Models");
code.AppendLine();
foreach (var t in schemaInfo.Tables)
{
CSharpTextGenerator.GenerateModel(code, t);
code.AppendLine();
}
code.AppendLine("#endregion");
code.AppendLine();
// class - DataAccess wrapper
code.CodeBlockBegin("public partial class {0} : IDisposable", schemaInfo.ClassName);
// variable
code.AppendLine();
code.AppendLine("private System.Data.SqlClient.SqlConnection connection;");
code.AppendLine("private System.Data.SqlClient.SqlTransaction transaction;");
code.AppendLine("private int transactionCounter;");
code.AppendLine("private String connectionString;");
code.AppendLine("private bool externalResource;");
code.AppendLine();
// constructor 1
code.AppendFormat("public {0}(String connectionString)", schemaInfo.ClassName);
code.CodeBlockBegin();
code.AppendLine("this.externalResource = false;");
code.AppendLine("this.connectionString = connectionString;");
code.CodeBlockEnd();
code.AppendLine();
// constructor 2
code.AppendFormat("public {0}(System.Data.SqlClient.SqlConnection connection, System.Data.SqlClient.SqlTransaction transaction)", schemaInfo.ClassName);
code.CodeBlockBegin();
code.AppendLine("this.externalResource = true;");
code.AppendLine("this.connection = connection;");
code.AppendLine("this.transaction = transaction;");
code.CodeBlockEnd();
code.AppendLine();
foreach (var t in schemaInfo.Tables)
{
String entityTypeName = t.ClassName;
String mappingMethodName = "Read" + entityTypeName;
code.AppendLineFormat("#region Upsert, Insert, Update, Delete, Select, Mapping - {0}", t.ToString());
code.AppendLine();
CSharpTextGenerator.GenerateMapping(code, mappingMethodName, entityTypeName, t.Columns);
code.AppendLine();
SimpleDataAccessGenerator.GenerateUpsertMethod(code, t);
code.AppendLine();
SimpleDataAccessGenerator.GenerateInsertMethod(code, t);
code.AppendLine();
SimpleDataAccessGenerator.GenerateUpdateMethod(code, t);
code.AppendLine();
SimpleDataAccessGenerator.GenerateSelectMethod(code, t, null, false, mappingMethodName);
code.AppendLine();
SimpleDataAccessGenerator.GenerateSelectCountMethod(code, t);
code.AppendLine();
SimpleDataAccessGenerator.GenerateSelectPagedMethod(code, t, mappingMethodName);
code.AppendLine();
Dictionary <String, Filter> filterColumnLists = new Dictionary <string, Filter>();
foreach (var i in t.Indexes)
{
String key = String.Join(":", i.Columns.OrderBy(ci => ci.Name).Select(ci => ci.Name));
if (!filterColumnLists.ContainsKey(key))
{
filterColumnLists.Add(key, new Filter
{
IsUnique = i.IsUnique,
Columns = i.Columns
});
}
}
foreach (var fk in t.ForeignKeys)
{
String key = String.Join(":", fk.Columns.OrderBy(ci => ci.Name).Select(ci => ci.Name));
if (!filterColumnLists.ContainsKey(key))
{
filterColumnLists.Add(key, new Filter
{
IsUnique = false,
Columns = fk.Columns
});
}
}
foreach (KeyValuePair <String, Filter> keyValue in filterColumnLists)
{
SimpleDataAccessGenerator.GenerateSelectMethod(code, t, keyValue.Value.Columns, keyValue.Value.IsUnique, mappingMethodName);
code.AppendLine();
SimpleDataAccessGenerator.GenerateDeleteMethod(code, t, keyValue.Value.Columns);
code.AppendLine();
}
code.AppendLine("#endregion");
code.AppendLine();
}
foreach (StoredProcedureInfo storedProcedureInfo in schemaInfo.StoredProcedures)
{
code.AppendLine("#region Stored Procedures");
code.AppendLine();
CSharpTextGenerator.GenerateStoredProcedureMethod(code, storedProcedureInfo);
code.AppendLine("#endregion");
code.AppendLine();
}
code.Append(@"
private void PopConnection(System.Data.SqlClient.SqlCommand command)
{
if (this.connection != null)
{
command.Connection = this.connection;
command.Transaction = this.transaction;
}
else
{
command.Connection = new System.Data.SqlClient.SqlConnection(this.connectionString);
command.Connection.Open();
}
}
private void PushConnection(System.Data.SqlClient.SqlCommand command)
{
System.Data.SqlClient.SqlConnection connection = command.Connection;
System.Data.SqlClient.SqlTransaction transaction = command.Transaction;
command.Connection = null;
command.Transaction = null;
if (connection != null && this.connection != connection)
{
connection.Close();
}
}
public void BeginTransaction(System.Data.IsolationLevel isolationLevel = System.Data.IsolationLevel.Unspecified)
{
if (this.connection == null)
{
this.connection = new System.Data.SqlClient.SqlConnection(this.connectionString);
this.connection.Open();
}
if (this.transaction == null)
{
this.transaction = this.connection.BeginTransaction(isolationLevel);
}
else
{
if (isolationLevel != System.Data.IsolationLevel.Unspecified && this.transaction.IsolationLevel != isolationLevel)
{
throw new InvalidOperationException(""Transaction isolation level mismatch."");
}
}
++this.transactionCounter;
}
public void CommitTransaction()
{
if (this.transaction == null || this.transactionCounter <= 0)
{
throw new InvalidOperationException(""currentTransaction"");
}
--this.transactionCounter;
if (this.transactionCounter == 0)
{
this.transaction.Commit();
this.transaction = null;
}
}
public void RollbackTransaction()
{
if (this.transaction == null || this.transactionCounter <= 0)
{
throw new InvalidOperationException(""currentTransaction"");
}
this.transactionCounter = 0;
this.transaction.Rollback();
this.transaction = null;
}
public void Dispose()
{
if (this.externalResource)
{
return;
}
try
{
if (this.transaction != null)
{
this.transaction.Rollback();
this.transaction = null;
this.transactionCounter = 0;
}
}
finally
{
if (this.connection != null)
{
this.connection.Close();
this.connection = null;
}
}
}
");
code.CodeBlockEnd();
code.CodeBlockEnd();
File.WriteAllText(outputFilename, code.ToString());
}
