public static void GenerateMapping(CodeStringBuilder code, String methodName, String entityTypeName, List <ColumnInfo> columns)
{
code.CodeBlockBegin("public static {0} {1}(System.Data.SqlClient.SqlDataReader reader)", entityTypeName, methodName);
code.AppendLineFormat("{0} entity = new {0}();", entityTypeName);
foreach (var columnInfo in columns)
{
GenerateColumnMapperFromSqlDataReader(code, columnInfo);
}
code.CodeBlockEnd("return entity;");
}
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 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);
}
}
private static void GenerateColumnMapperFromSqlDataReader(CodeStringBuilder code, ColumnInfo columnInfo, int?ordinalOverride = null)
{
int ordinal = columnInfo.Ordinal;
if (ordinalOverride.HasValue)
{
ordinal = ordinalOverride.Value;
}
if (columnInfo.IsNullable && columnInfo.Type.IsValueType)
{
code.CodeBlockBegin("if (reader.IsDBNull({0}))", ordinal);
code.AppendLineFormat("entity.{0} = null;", columnInfo.PropertyName, columnInfo.MappingMethodName, ordinal);
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.AppendLineFormat("entity.{0} = reader.{1}({2});", columnInfo.PropertyName, columnInfo.MappingMethodName, ordinal);
code.CodeBlockEnd();
}
else
{
code.AppendLineFormat("entity.{0} = reader.{1}({2});", columnInfo.PropertyName, columnInfo.MappingMethodName, ordinal);
}
}
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 static void GenerateSqlParameter(CodeStringBuilder code, String valueExpression, String localParameterName, String sqlParameterName, System.Data.SqlDbType sqlDbType, bool is_nullable, System.Data.ParameterDirection parameterDirection, long max_length)
{
if (IsVariableLength(sqlDbType))
{
code.AppendLineFormat("System.Data.SqlClient.SqlParameter {0} = command.Parameters.Add(\"{1}\", System.Data.SqlDbType.{2}, {3});", localParameterName, sqlParameterName, sqlDbType.ToString(), max_length);
}
else
{
code.AppendLineFormat("System.Data.SqlClient.SqlParameter {0} = command.Parameters.Add(\"{1}\", System.Data.SqlDbType.{2});", localParameterName, sqlParameterName, sqlDbType.ToString(), max_length);
}
if (parameterDirection == System.Data.ParameterDirection.Output)
{
code.AppendLineFormat("{0}.Direction = System.Data.ParameterDirection.Output;", localParameterName);
}
else
{
if (parameterDirection == System.Data.ParameterDirection.InputOutput)
{
code.AppendLineFormat("{0}.Direction = System.Data.ParameterDirection.InputOutput;", localParameterName);
}
if (is_nullable)
{
code.CodeBlockBegin("if ({0} == null)", valueExpression);
code.AppendLineFormat("{0}.Value = System.DBNull.Value;", localParameterName);
code.CodeBlockEnd();
code.CodeBlockBegin("else");
code.AppendLineFormat("{0}.Value = {1};", localParameterName, valueExpression);
code.CodeBlockEnd();
}
else
{
code.AppendLineFormat("{0}.Value = {1};", localParameterName, valueExpression);
}
}
}
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());
}
