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()); }