public static void GenerateUpdateMethod(CodeStringBuilder code, TableInfo tableInfo)
{
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
String methodName = String.Format("Update{0}", entityTypeName);
List <ColumnInfo> editableColumns = tableInfo.GetEditableColumns();
List <ColumnInfo> computedColumns = tableInfo.GetComputedColumns();
List <ColumnInfo> key = tableInfo.GetKey();
if (editableColumns.Count == 0)
{
return;
}
if (key.SequenceEqual(editableColumns))
{
return;
}
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateUpdateStatement(script, target, key, editableColumns, computedColumns);
// generate code for script
CSharpTextGenerator.GenerateUpdateMethod(code, methodName, entityTypeName, script.ToString(), key, editableColumns, computedColumns);
}
public static void GenerateInsertMethod(CodeStringBuilder code, TableInfo tableInfo)
{
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
String methodName = String.Format("Insert{0}", entityTypeName);
List <ColumnInfo> editableColumns = tableInfo.GetEditableColumns();
List <ColumnInfo> computedColumns = tableInfo.GetComputedColumns();
List <ColumnInfo> identityColumns = tableInfo.GetIdentityColumns();
List <ColumnInfo> key = tableInfo.GetKey();
List <ColumnInfo> readBackColumns = new List <ColumnInfo>();
readBackColumns.AddRange(identityColumns);
readBackColumns.AddRange(computedColumns);
if (editableColumns.Count == 0)
{
return;
}
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateInsertStatement(script, target, editableColumns, readBackColumns);
// generate code for script
CSharpTextGenerator.GenerateInsertMethod(code, methodName, entityTypeName, script.ToString(), editableColumns, readBackColumns);
}
public static void GenerateSelectPagedMethod(CodeStringBuilder code, TableInfo tableInfo, String mappingMethodName)
{
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
List <ColumnInfo> key = tableInfo.GetKey();
StringBuilder methodName = new StringBuilder();
methodName.Append("Select");
methodName.Append(entityTypeName);
methodName.Append("Paged");
if (key == null || key.Count == 0)
{
return;
}
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateSelectStatementPaged(script, target, tableInfo.Columns, "@__FirstIndex", "@__LastIndex", key, null);
// generate code for script
CSharpTextGenerator.GenerateSelectPagedMethod(code, methodName.ToString(), entityTypeName, mappingMethodName, script.ToString(), key);
}
public static void GenerateSelectMethod(CodeStringBuilder code, TableInfo tableInfo, List <ColumnInfo> key, bool singleResult, String mappingMethodName)
{
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
StringBuilder methodName = new StringBuilder();
methodName.Append("Select");
methodName.Append(entityTypeName);
if (key != null && key.Count > 0)
{
methodName.Append("By");
foreach (var k in key)
{
methodName.Append(k.PropertyName);
}
}
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateSelectStatement(script, target, tableInfo.Columns, key);
// generate code for script
CSharpTextGenerator.GenerateSelectMethod(code, methodName.ToString(), entityTypeName, script.ToString(), key, singleResult, mappingMethodName);
}
public static void GenerateDeleteMethod(CodeStringBuilder code, TableInfo tableInfo, List <ColumnInfo> key)
{
if (key == null || key.Count == 0)
{
return;
}
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
StringBuilder methodName = new StringBuilder();
methodName.Append("Delete");
methodName.Append(entityTypeName);
methodName.Append("By");
foreach (var k in key)
{
methodName.Append(k.PropertyName);
}
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateDeleteStatement(script, target, key);
// generate code for script
CSharpTextGenerator.GenerateDeleteMethod(code, methodName.ToString(), script.ToString(), key);
}
public static void GenerateSelectCountMethod(CodeStringBuilder code, TableInfo tableInfo)
{
String entityTypeName = tableInfo.ClassName;
String target = String.Format("[{0}].[{1}]", tableInfo.Schema, tableInfo.Name);
StringBuilder methodName = new StringBuilder();
methodName.Append("Select");
methodName.Append(entityTypeName);
methodName.Append("Count");
// generate sql script
StringBuilder script = new StringBuilder();
SQLTextGenerator.GenerateSelectStatementCount(script, target, null);
// generate code for script
CSharpTextGenerator.GenerateSelectCountMethod(code, methodName.ToString(), script.ToString());
}
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());
}