/// <summary>
/// Creates a string that represents the "select by" functionality of the data access class.
/// </summary>
/// <param name="table">The Table instance that this method will be created for.</param>
/// <param name="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectAllByMethods(Table table, string storedProcedurePrefix, TextWriter streamWriter)
{
string className = AppUtility.FormatClassName(table.Name);
string dtoVariableName = AppUtility.FormatCamel(className);
// Create a stored procedure for each foreign key
foreach (List <Column> compositeKeyList in table.ForeignKeys.Values)
{
// Create the stored procedure name
StringBuilder stringBuilder = new StringBuilder(255);
stringBuilder.Append("SelectAllBy");
for (var i = 0; i < compositeKeyList.Count; i++)
{
var column = compositeKeyList[i];
if (i > 0)
{
stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));
}
else
{
stringBuilder.Append(AppUtility.FormatPascal(column.Name));
}
}
string methodName = stringBuilder.ToString();
string procedureName = storedProcedurePrefix + table.Name + methodName;
// Create the select function based on keys
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table by a foreign key.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic List<" + className + "> " + methodName + "(");
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
streamWriter.Write(AppUtility.CreateMethodParameter(column));
if (i < (compositeKeyList.Count - 1))
{
streamWriter.Write(",");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
streamWriter.WriteLine("\t\t\t\t {");
streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
for (var i = 0; i < compositeKeyList.Count; i++)
{
var column = compositeKeyList[i];
streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + AppUtility.FormatCamel(column.Name) + ");");
}
streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", vParams, commandType: CommandType.StoredProcedure).ToList();");
streamWriter.WriteLine("\t\t\t\t }");
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates a string that represents the "select" functionality of the data access class.
/// </summary>
/// <param name="table">The Table instance that this method will be created for.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectMethod(Table table, TextWriter streamWriter)
{
if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||
table.Columns.Count == table.ForeignKeys.Count)
{
return;
}
var className = AppUtility.FormatClassName(table.Name);
var variableName = "a" + table.PrimaryKeys[0].Name;
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Selects the Single object of " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic " + className + " Get" + className + "(" + AppUtility.GetCsType(table.PrimaryKeys[0]) + " " + variableName + ")");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
streamWriter.WriteLine("\t\t\t\t {");
streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + table.PrimaryKeys[0].Name + "\"," + variableName + ");");
streamWriter.WriteLine("\t\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "Select\", vParams, commandType: CommandType.StoredProcedure);");
streamWriter.WriteLine("\t\t\t\t }");
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
/// <summary>
/// Creates a string that represents the update functionality of the data access class.
/// </summary>
/// <param name="table">The Table instance that this method will be created for.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateUpdateMethod(Table table, TextWriter streamWriter)
{
if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||
table.Columns.Count == table.ForeignKeys.Count)
{
return;
}
var className = AppUtility.FormatClassName(table.Name);
var variableName = "a" + className;
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Updates record to the " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false");
streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic bool Update(" + className + " " + variableName + ")");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t var blResult = false;");
streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
streamWriter.WriteLine("\t\t\t\t {");
streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
foreach (var column in table.Columns)
{
streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");");
}
streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Update\", vParams, commandType: CommandType.StoredProcedure);");
streamWriter.WriteLine("\t\t\t\t if (iResult == -1) blResult = true;");
streamWriter.WriteLine("\t\t\t\t }");
streamWriter.WriteLine("\t\t\treturn blResult;");
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
/// <summary>
/// Creates a project file that references each generated C# code file for data access.
/// </summary>
/// <param name="path">The path where the project file should be created.</param>
/// <param name="projectName">The name of the project.</param>
/// <param name="tableList">The list of tables code files were created for.</param>
/// <param name="daoSuffix">The suffix to append to the name of each data access class.</param>
internal static void CreateProjectFile(string path, string projectName, List <Table> tableList, string daoSuffix)
{
string projectXml = AppUtility.GetResource("PocoSqlGenerator.Resources.Project.xml");
var document = new XmlDocument();
document.LoadXml(projectXml);
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(document.NameTable);
namespaceManager.AddNamespace(String.Empty, "http://schemas.microsoft.com/developer/msbuild/2003");
namespaceManager.AddNamespace("msbuild", "http://schemas.microsoft.com/developer/msbuild/2003");
var selectSingleNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:ProjectGuid", namespaceManager);
if (selectSingleNode !=
null)
{
selectSingleNode.InnerText = "{" + Guid.NewGuid().ToString() + "}";
}
var singleNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:RootNamespace", namespaceManager);
if (singleNode !=
null)
{
singleNode.InnerText = projectName;
}
var xmlNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:AssemblyName", namespaceManager);
if (xmlNode !=
null)
{
xmlNode.InnerText = projectName;
}
XmlNode itemGroupNode = document.SelectSingleNode("/msbuild:Project/msbuild:ItemGroup[msbuild:Compile]", namespaceManager);
foreach (Table table in tableList)
{
string className = AppUtility.FormatClassName(table.Name);
XmlNode dtoCompileNode = document.CreateElement("Compile", "http://schemas.microsoft.com/developer/msbuild/2003");
XmlAttribute dtoAttribute = document.CreateAttribute("Include");
dtoAttribute.Value = className + ".cs";
if (dtoCompileNode.Attributes != null)
{
dtoCompileNode.Attributes.Append(dtoAttribute);
}
if (itemGroupNode != null)
{
itemGroupNode.AppendChild(dtoCompileNode);
}
}
document.Save(Path.Combine(path, projectName + ".csproj"));
}
/// <summary>
/// Creates a C# Model /POCO class for all of the table's of Database.
/// </summary>
/// <param name="databaseName">The name of the database.</param>
/// <param name="table">Instance of the Table class that represents the table this class will be created for.</param>
/// <param name="targetNamespace">The namespace that the generated C# classes should contained in.</param>
/// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
/// <param name="path">Path where the class should be created.</param>
internal static void CreateModelClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)
{
var className = AppUtility.FormatClassName(table.Name);
using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
{
#region Create the header for the class
streamWriter.WriteLine("using System;");
streamWriter.WriteLine();
streamWriter.WriteLine("namespace " + targetNamespace);
streamWriter.WriteLine("{");
streamWriter.WriteLine("\tpublic class " + className);
streamWriter.WriteLine("\t{");
#endregion
#region Append the public properties
streamWriter.WriteLine("\t\t#region Properties");
for (var i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
var parameter = AppUtility.CreateMethodParameter(column);
var type = parameter.Split(' ')[0];
var name = parameter.Split(' ')[1];
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Gets or sets the " + AppUtility.FormatPascal(name) + " value.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic " + type + " " + AppUtility.FormatPascal(name));
streamWriter.WriteLine("\t\t{ get; set; }");
if (i < (table.Columns.Count - 1))
{
streamWriter.WriteLine();
}
}
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
#endregion
// Close out the class and namespace
streamWriter.WriteLine("\t}");
streamWriter.WriteLine("}");
}
}
/// <summary>
/// Creates a string that represents the select functionality of the data access class.
/// </summary>
/// <param name="table">The Table instance that this method will be created for.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectAllMethod(Table table, TextWriter streamWriter)
{
if (table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count)
{
return;
}
var className = AppUtility.FormatClassName(table.Name);
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\t public IEnumerable<" + className + "> SelectAll()");
streamWriter.WriteLine("\t\t{");
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
streamWriter.WriteLine("\t\t\t{");
streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", commandType: CommandType.StoredProcedure).ToList();");
streamWriter.WriteLine("\t\t\t}");
streamWriter.WriteLine("\t\t}");
}
internal static void CreateRepoClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)
{
var className = AppUtility.FormatClassName(table.Name);
using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
{
#region Add References & Declare Class
streamWriter.WriteLine("using System.Collections.Generic;");
streamWriter.WriteLine("using System.Data;");
streamWriter.WriteLine("using System.Linq;");
streamWriter.WriteLine("using Dapper;");
streamWriter.WriteLine();
streamWriter.WriteLine("namespace " + targetNamespace);
streamWriter.WriteLine("{");
streamWriter.WriteLine("\t public class " + className + "Repo : BaseRepository");
streamWriter.WriteLine("\t\t {");
#endregion
#region Append the access methods
streamWriter.WriteLine("\t\t#region Methods");
streamWriter.WriteLine();
CreateInsertMethod(table, streamWriter);
CreateUpdateMethod(table, streamWriter);
CreateSelectMethod(table, streamWriter);
CreateSelectAllMethod(table, streamWriter);
CreateSelectAllByMethods(table, storedProcedurePrefix, streamWriter);
#endregion
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
// Close out the class and namespace
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine("}");
}
}