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