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
}
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
}
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
}
private Assembly CompileMessage(string assemblyName, string ns, int propertiesCount)
{
var output = new CodeStringBuilder();
output.Append("namespace ").Append(ns).AppendLine(";");
new CSharpMessageBuilder(propertiesCount).GenerateMemberDeclaration(output);
var syntaxTree = SyntaxFactory.ParseSyntaxTree(
output.AsStringBuilder().ToString(),
CSharpParseOptions.Default.WithLanguageVersion(LanguageVersion.CSharp10));
var compilation = CSharpCompilation.Create(
assemblyName,
syntaxTrees: new[] { syntaxTree },
references: new[]
public void CodeStringBuilderWorks()
{
string source = @"
public class MyAltClass
{
public void MyAltMethod()
{
}
}
public class MyOtherClass
{
public void MyAltMethod(int myAltN)
{
int x = myAltN + 1;
int y = x + 1;
}
}
";
//string source = @"if (x == 2) {y=3; z= 2;}";
string expected = @"
public class MyAltClass
{
public void MyAltMethod()
{
}
}
public class MyOtherClass
{
public void MyAltMethod(int myAltN)
{
int x = myAltN + 1;
int y = x + 1;
}
}
";
var builder = new CodeStringBuilder();
builder.Append(source);
var result = builder.ToString();
if (result != expected)
{
IdentifyStringDifferencePoint(expected, result);
}
result.Should().Be(expected);
}
private static void AddComment(CodeStringBuilder output)
{
var comment = @"// ------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool.
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
// ------------------------------------------------------------------------------
"
.Replace("\r\n", "\n")
.Replace("\n", Environment.NewLine);
output.Append(comment);
}
private static void GenerateColumnMapperFromSqlParameter(CodeStringBuilder code, ColumnInfo columnInfo, bool throwIfNullAtNotNullable)
{
if (columnInfo.IsNullable)
{
code.CodeBlockBegin("if ({0}.Value == System.DBNull.Value)", columnInfo.LocalParameterVariableName);
code.AppendLineFormat("entity.{0} = null;", columnInfo.PropertyName);
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.AppendLineFormat("entity.{0} = ({1}){2}.Value;", columnInfo.PropertyName, columnInfo.FullTypeName, columnInfo.LocalParameterVariableName);
code.CodeBlockEnd();
}
else
{
if (throwIfNullAtNotNullable)
{
code.CodeBlockBegin("if ({0}.Value == System.DBNull.Value)", columnInfo.LocalParameterVariableName);
code.Append("throw new InvalidOperationException(\"Invalid output value: " + columnInfo.LocalParameterVariableName + "\");");
code.CodeBlockEnd();
}
code.AppendLineFormat("entity.{0} = ({1}){2}.Value;", columnInfo.PropertyName, columnInfo.FullTypeName, columnInfo.LocalParameterVariableName);
}
}
/// <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());
}