public void BusinessLogic(Table table)
{
try
{
string nameDB = StringExtension.ToPascalCase(Database.Database);
string nameTable = StringExtension.ToPascalCase(table.Name);
string pathBusinessLogic = Path.Combine(this.PathRoot, "BusinessLogic");
string fileBusinessLogic = $"{nameTable}.cs";
if (Directory.Exists(pathBusinessLogic) == false)
{
Directory.CreateDirectory(pathBusinessLogic);
}
string pathFilePathBusinessLogic = Path.Combine(pathBusinessLogic, fileBusinessLogic);
if (File.Exists(pathFilePathBusinessLogic) == true)
{
File.Delete(pathFilePathBusinessLogic);
}
using (StreamWriter objReader = new StreamWriter(pathFilePathBusinessLogic))
{
List <Column> lstColumns = Database.GetColumns(table.Id);
Column objColumnPK = lstColumns.FirstOrDefault(x => x.IsPrimaryKey == true);
objReader.WriteLine($"using BE = {nameDB}.BusinessEntity;");
objReader.WriteLine("using System.Collections.Generic;");
objReader.WriteLine("using System.Data.SqlClient;");
objReader.WriteLine("using System.Data;");
objReader.WriteLine("using System;");
objReader.WriteLine("");
objReader.WriteLine($"namespace {nameDB}.BusinessLogic");
objReader.WriteLine("{");
objReader.WriteLine($"\tpublic class {nameTable}");
objReader.WriteLine("\t{");
objReader.WriteLine("\t\tprivate string connectionString = \"\";");
objReader.WriteLine("");
#region Constructor
objReader.WriteLine($"\t\tpublic {nameTable}(string connectionString)");
objReader.WriteLine("\t\t{");
objReader.WriteLine("\t\t\ttry {");
objReader.WriteLine("\t\t\t\tthis.connectionString = connectionString;");
objReader.WriteLine("\t\t\t}");
objReader.WriteLine("\t\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\t\tthrow ex;");
objReader.WriteLine("\t\t\t}");
objReader.WriteLine("\t\t}");
objReader.WriteLine("");
#endregion
#region Insert
objReader.WriteLine($"\t\tpublic int Insertar(ref BE.{nameTable} be{nameTable})");
objReader.WriteLine("\t\t{");
objReader.WriteLine("\t\t\tint rowsAffected = 0;");
objReader.WriteLine("\t\t\t\ttry {");
objReader.WriteLine($"\t\t\t\tstring sp = \"Sp{nameTable}Insertar\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\t\tcnn.Open();");
objReader.WriteLine("");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string parameter = $"new SqlParameter(\"@{nameColumn}\", be{nameTable}.{nameColumn})";
objReader.WriteLine($"\t\t\t\tcmd.Parameters.Add({parameter});");
if (objColumn.IsPrimaryKey == true)
{
objReader.WriteLine($"\t\t\t\tcmd.Parameters[\"@{nameColumn}\"].Direction = ParameterDirection.Output;");
}
}
objReader.WriteLine("");
objReader.WriteLine("\t\t\t\trowsAffected = cmd.ExecuteNonQuery();");
objReader.WriteLine("");
if (objColumnPK != null)
{
string nameColumn = StringExtension.ToPascalCase(objColumnPK.Name);
string typeColumn = this.TranslateType(objColumnPK.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = cmd.Parameters[\"@{nameColumn}\"].Value.ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(cmd.Parameters[\"@{nameColumn}\"].Value.ToString());");
}
}
objReader.WriteLine("");
objReader.WriteLine("\t\t\t\treturn rowsAffected;");
objReader.WriteLine("\t\t\t}");
objReader.WriteLine("\t\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\t\tthrow ex;");
objReader.WriteLine("\t\t\t}");
objReader.WriteLine("\t\t}");
objReader.WriteLine("");
#endregion
#region Update
objReader.WriteLine($"\tpublic int Actualizar(BE.{nameTable} be{nameTable})");
objReader.WriteLine("\t{");
objReader.WriteLine("\tint rowsAffected = 0;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Actualizar\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string parameter = $"new SqlParameter(\"@{nameColumn}\", be{nameTable}.{nameColumn})";
objReader.WriteLine($"\t\t\tcmd.Parameters.Add({parameter});");
}
objReader.WriteLine("");
objReader.WriteLine("\t\t\trowsAffected = cmd.ExecuteNonQuery();");
objReader.WriteLine("");
objReader.WriteLine("\t\t\treturn rowsAffected;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
#endregion
#region Delete
objReader.WriteLine($"\tpublic int Eliminar(BE.{nameTable} be{nameTable})");
objReader.WriteLine("\t{");
objReader.WriteLine("\tint rowsAffected = 0;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Eliminar\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
if (objColumnPK != null)
{
string nameColumn = StringExtension.ToPascalCase(objColumnPK.Name);
string parameter = $"new SqlParameter(\"@{nameColumn}\", be{nameTable}.{nameColumn})";
objReader.WriteLine($"\t\t\tcmd.Parameters.Add({parameter});");
}
objReader.WriteLine("");
objReader.WriteLine("\t\t\trowsAffected = cmd.ExecuteNonQuery();");
objReader.WriteLine("");
objReader.WriteLine("\t\t\treturn rowsAffected;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
#endregion
#region List
objReader.WriteLine($"\tpublic List<BE.{nameTable}> Listar()");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tvar lst{nameTable} = new List<BE.{nameTable}>();");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Listar\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\twhile (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tvar be{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine($"\t\t\t\tlst{nameTable}.Add(be{nameTable});");
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn lst{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
#endregion
#region Get
if (objColumnPK != null)
{
string nameColumnPK = StringExtension.ToPascalCase(objColumnPK.Name);
string typeColumnPK = this.TranslateType(objColumnPK.Type.Name);
objReader.WriteLine($"\tpublic BE.{nameTable} Obtener({typeColumnPK} {nameColumnPK})");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tBE.{nameTable} be{nameTable} = null;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Obtener\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine($"\t\t\tcmd.Parameters.Add(new SqlParameter(\"@{nameColumnPK}\", {nameColumnPK}));");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\tif (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tbe{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn be{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
}
#endregion
#region First
objReader.WriteLine($"\tpublic BE.{nameTable} Primero()");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tBE.{nameTable} be{nameTable} = null;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Primero\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\tif (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tbe{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn be{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
#endregion
#region Last
objReader.WriteLine($"\tpublic BE.{nameTable} Ultimo()");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tBE.{nameTable} be{nameTable} = null;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Ultimo\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\tif (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tbe{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn be{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
#endregion
#region Previous
if (objColumnPK != null)
{
string nameColumnPK = StringExtension.ToPascalCase(objColumnPK.Name);
string typeColumnPK = this.TranslateType(objColumnPK.Type.Name);
objReader.WriteLine($"\tpublic BE.{nameTable} Anterior({typeColumnPK} {nameColumnPK})");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tBE.{nameTable} be{nameTable} = null;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Anterior\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine($"\t\t\tcmd.Parameters.Add(new SqlParameter(\"@{nameColumnPK}\", {nameColumnPK}));");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\tif (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tbe{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn be{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
}
#endregion
#region Next
if (objColumnPK != null)
{
string nameColumnPK = StringExtension.ToPascalCase(objColumnPK.Name);
string typeColumnPK = this.TranslateType(objColumnPK.Type.Name);
objReader.WriteLine($"\tpublic BE.{nameTable} Siguiente({typeColumnPK} {nameColumnPK})");
objReader.WriteLine("\t{");
objReader.WriteLine($"\tBE.{nameTable} be{nameTable} = null;");
objReader.WriteLine("\t\ttry {");
objReader.WriteLine($"\t\t\tstring sp = \"Sp{nameTable}Siguiente\";");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tvar cnn = new SqlConnection(this.connectionString);");
objReader.WriteLine("\t\t\tvar cmd = new SqlCommand(sp, cnn);");
objReader.WriteLine("\t\t\tcmd.CommandType = CommandType.StoredProcedure;");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tcnn.Open();");
objReader.WriteLine("");
objReader.WriteLine($"\t\t\tcmd.Parameters.Add(new SqlParameter(\"@{nameColumnPK}\", {nameColumnPK}));");
objReader.WriteLine("");
objReader.WriteLine("\t\t\tSqlDataReader reader = cmd.ExecuteReader();");
objReader.WriteLine("\t\t\tif (reader.Read())");
objReader.WriteLine("\t\t\t{");
objReader.WriteLine($"\t\t\t\tbe{nameTable} = new BE.{nameTable}();");
foreach (var objColumn in lstColumns)
{
string nameColumn = StringExtension.ToPascalCase(objColumn.Name);
string typeColumn = this.TranslateType(objColumn.Type.Name);
if (typeColumn.ToUpper().Equals("STRING"))
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = reader[\"{nameColumn}\"].ToString();");
}
else
{
objReader.WriteLine($"\t\t\t\tbe{nameTable}.{nameColumn} = {typeColumn}.Parse(reader[\"{nameColumn}\"].ToString());");
}
}
objReader.WriteLine("\t\t\t}");
objReader.WriteLine($"\t\t\treturn be{nameTable};");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t\tcatch (Exception ex) {");
objReader.WriteLine("\t\t\tthrow ex;");
objReader.WriteLine("\t\t}");
objReader.WriteLine("\t}");
objReader.WriteLine("");
}
#endregion
objReader.WriteLine("\t}");
objReader.WriteLine("}");
objReader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void StoredProcedure(Poco.Table table, string PathRoot)
{
try
{
string nameTable = StringExtension.ToPascalCase(table.Name);
string pathStoredProcedure = Path.Combine(PathRoot, "StoredProcedure");
string fileStoredProcedure = $"{nameTable}.sql";
if (Directory.Exists(pathStoredProcedure) == false)
{
Directory.CreateDirectory(pathStoredProcedure);
}
string pathFileStoredProcedure = Path.Combine(pathStoredProcedure, fileStoredProcedure);
if (File.Exists(pathFileStoredProcedure) == true)
{
File.Delete(pathFileStoredProcedure);
}
using (StreamWriter objReader = new StreamWriter(pathFileStoredProcedure))
{
#region Cabecera
objReader.WriteLine("set ANSI_NULLS ON");
objReader.WriteLine("set QUOTED_IDENTIFIER ON");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
List <Poco.Column> lstColumns = this.GetColumns(table.Id);
List <Poco.Column> lstColumnsWithoutPk = lstColumns.Where(x => x.IsPrimaryKey == false).ToList();
Poco.Column objPrimaryKey = lstColumns.FirstOrDefault(x => x.IsPrimaryKey == true);
#region Insert
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Insertar");
string head = $"\tINSERT INTO {nameTable} (";
string foot = "\tVALUES (";
string ret = "";
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name;
string typeColumn = lstColumns[i].Type.Name.ToUpper();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
if (lstColumns[i].IsPrimaryKey)
{
objReader.WriteLine($"@{nameColumn} AS {typeColumn} OUTPUT{comma}");
ret = $"\tSET @{nameColumn} = @@IDENTITY";
}
else
{
objReader.WriteLine($"@{nameColumn} AS {typeColumn}{comma}");
string separator = (i == lstColumns.Count - 1) ? ")" : ",";
head += $"{nameColumn}{separator}";
foot += $"@{nameColumn}{separator}";
}
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine(head);
objReader.WriteLine(foot);
objReader.WriteLine(ret);
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Update
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Actualizar");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name;
string typeColumn = lstColumns[i].Type.Name.ToUpper();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
objReader.WriteLine($"@{nameColumn} AS {typeColumn}{comma}");
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine($"\tUPDATE {nameTable}");
for (int i = 0; i < lstColumnsWithoutPk.Count; i++)
{
string nameColumn = lstColumnsWithoutPk[i].Name.Trim();
string comma = (i == lstColumnsWithoutPk.Count - 1) ? "" : ",";
if (i == 0)
{
objReader.WriteLine($"\tSET {nameColumn} = @{nameColumn}{comma}");
}
else
{
objReader.WriteLine($"\t{nameColumn} = @{nameColumn}{comma}");
}
}
if (objPrimaryKey != null)
{
string nameColumn = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tWHERE {nameColumn} = @{nameColumn}");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Delete
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Eliminar");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine($"\tDELETE FROM {nameTable}");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tWHERE {nameColumnPK} = @{nameColumnPK}");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region List
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Listar");
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
if (i == 0)
{
objReader.WriteLine($"\tSELECT {nameColumn}{comma}");
}
else
{
objReader.WriteLine($"\t{nameColumn}{comma}");
}
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Get
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Obtener");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
if (i == 0)
{
objReader.WriteLine($"\tSELECT TOP 1 {nameColumn}{comma}");
}
else
{
objReader.WriteLine($"\t{nameColumn}{comma}");
}
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tWHERE {nameColumnPK} = @{nameColumnPK}");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region First
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Primero");
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine("\tSELECT TOP 1 ");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
objReader.WriteLine($"\t{nameColumn}{comma}");
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tORDER BY {nameColumnPK} ASC");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Last
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Ultimo");
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine("\tSELECT TOP 1 ");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
objReader.WriteLine($"\t{nameColumn}{comma}");
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tORDER BY {nameColumnPK} DESC");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Previous
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Anterior");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine("\tSELECT TOP 1 ");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
objReader.WriteLine($"\t{nameColumn}{comma}");
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tWHERE {nameColumnPK} < @{nameColumnPK}");
objReader.WriteLine($"\tORDER BY {nameColumnPK} DESC");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
#region Next
objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Siguiente");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
}
objReader.WriteLine("AS");
objReader.WriteLine("BEGIN");
objReader.WriteLine("\tSELECT TOP 1 ");
for (int i = 0; i < lstColumns.Count; i++)
{
string nameColumn = lstColumns[i].Name.Trim();
string comma = (i == lstColumns.Count - 1) ? "" : ",";
objReader.WriteLine($"\t{nameColumn}{comma}");
}
objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
if (objPrimaryKey != null)
{
string nameColumnPK = objPrimaryKey.Name.Trim();
objReader.WriteLine($"\tWHERE {nameColumnPK} > @{nameColumnPK}");
objReader.WriteLine($"\tORDER BY {nameColumnPK} ASC");
}
objReader.WriteLine("END");
objReader.WriteLine("GO");
objReader.WriteLine("");
#endregion
objReader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
