public void buildDataAccess(string table_name, string name_space)
{
Report Report = new Report();
DataSet ds;
DataSet ds2;
DataSet ds3;
DataRow row;
string output = "";
string table_id;
string column_name;
string file;
string proc_name;
string proc_param_name;
string proc_param_type;
Report.OpenConnections();
ds = Report.retrieve_db_table_column_summary(table_name);
Report.CloseConnections();
table_id = (string)ds.Tables[0].Rows[0]["COLUMN_NAME"];
output = output + @"using System;" + nl;
output = output + @"using System.Collections.Generic;" + nl;
output = output + @"using System.Data;" + nl;
output = output + @"using System.Data.SqlClient;" + nl;
output = output + @"using System.Linq;" + nl;
output = output + @"using Dapper;" + nl;
output = output + @"using " + name_space + ".Models;" + nl;
output = output + @"using static " + name_space + ".Tools;" + nl;
output = output + @"" + nl;
output = output + @"namespace " + name_space + ".Data_Access" + nl;
output = output + @"{" + nl;
output = output + @" public class " + CommonLib.GetDisplayName(table_name) + "DataAccess" + nl;
output = output + @" {" + nl;
output = output + @" public " + CommonLib.GetDisplayName(table_name) + " Save(" + CommonLib.GetDisplayName(table_name) + " " + table_name + ")" + nl;
output = output + @" {" + nl;
output = output + @" using (IDbConnection cnn = new SqlConnection(GetConnectionString()))" + nl;
output = output + @" {" + nl;
output = output + @" string sql = $""EXEC " + table_name.ToUpper() + @"_SP_SAVE "" +" + nl;
//insert params
//foreach (DataRow row in ds.Tables[0].Rows)
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
row = ds.Tables[0].Rows[i];
column_name = (string)row["COLUMN_NAME"].ToString();
if (i != ds.Tables[0].Rows.Count - 1)
{
if ((short)row["DATA_TYPE"] == 2)
{
//numeric
output = output + @" $""{" + table_name + "." + column_name + @"}, "" +" + nl;
}
else if ((short)row["DATA_TYPE"] == 12)
{
//varchar
output = output + @" $""'{" + table_name + "." + column_name + @"}', "" +" + nl;
}
else if ((short)row["DATA_TYPE"] == -1)
{
//varchar(max)
output = output + @" $""'{" + table_name + "." + column_name + @"}', "" +" + nl;
}
else if ((short)row["DATA_TYPE"] == 11)
{
//datetime
output = output + @" $""'{" + table_name + "." + column_name + @"}', "" +" + nl;
}
else if ((short)row["DATA_TYPE"] == -6)
{
//tinyint
output = output + @" $""{" + table_name + "." + column_name + @"}, "" +" + nl;
}
}
else
{
//last row
if ((short)row["DATA_TYPE"] == 2)
{
//numeric
output = output + @" $""{" + table_name + "." + column_name + @"} "";" + nl;
}
else if ((short)row["DATA_TYPE"] == 12)
{
//varchar
output = output + @" $""'{" + table_name + "." + column_name + @"}' "";" + nl;
}
else if ((short)row["DATA_TYPE"] == -1)
{
//varchar(max)
output = output + @" $""'{" + table_name + "." + column_name + @"}' "";" + nl;
}
else if ((short)row["DATA_TYPE"] == 11)
{
//datetime
output = output + @" $""'{" + table_name + "." + column_name + @"}' "";" + nl;
}
else if ((short)row["DATA_TYPE"] == -6)
{
//tinyint
output = output + @" $""{" + table_name + "." + column_name + @"} "";" + nl;
}
}
}
output = output + @"" + nl;
output = output + @" return cnn.Query<" + CommonLib.GetDisplayName(table_name) + ">(sql).FirstOrDefault(); ;" + nl;
output = output + @" }" + nl;
output = output + @" }" + nl;
output = output + @" public List<" + CommonLib.GetDisplayName(table_name) + "> Retrieve()" + nl;
output = output + @" {" + nl;
output = output + @" using (IDbConnection cnn = new SqlConnection(GetConnectionString()))" + nl;
output = output + @" {" + nl;
output = output + @" string sql = $""EXEC " + table_name.ToUpper() + @"_SP_RETRIEVE 0 ""; " + nl;
output = output + @"" + nl;
output = output + @" return cnn.Query<" + CommonLib.GetDisplayName(table_name) + ">(sql).ToList();" + nl;
output = output + @" }" + nl;
output = output + @" }" + nl;
output = output + @" public " + CommonLib.GetDisplayName(table_name) + " Retrieve(string " + table_id + ")" + nl;
output = output + @" {" + nl;
output = output + @" using (IDbConnection cnn = new SqlConnection(GetConnectionString()))" + nl;
output = output + @" {" + nl;
output = output + @" string sql = $""EXEC " + table_name.ToUpper() + @"_SP_RETRIEVE "" +" + nl;
output = output + @" $""{" + table_id + @"} "";" + nl;
output = output + @"" + nl;
output = output + @" return cnn.Query<" + CommonLib.GetDisplayName(table_name) + ">(sql).FirstOrDefault();" + nl;
output = output + @" }" + nl;
output = output + @" }" + nl;
output = output + @" public void Delete(string " + table_id + ")" + nl;
output = output + @" {" + nl;
output = output + @" using (IDbConnection cnn = new SqlConnection(GetConnectionString()))" + nl;
output = output + @" {" + nl;
output = output + @" string sql = $""EXEC " + table_name.ToUpper() + @"_SP_DELETE "" +" + nl;
output = output + @" $""{" + table_id + @"} "";" + nl;
output = output + @"" + nl;
output = output + @" cnn.Execute(sql);" + nl;
output = output + @" }" + nl;
output = output + @" }" + nl;
//get the rest of the stored procedures
Report.OpenConnections();
ds2 = Report.retrieve_stored_procedures_for_table(table_name);
Report.CloseConnections();
foreach (DataRow proc in ds2.Tables[0].Rows)
{
proc_name = proc["name"].ToString();
//get the params for procedure
Report.OpenConnections();
ds3 = Report.retrieve_params_for_sp(proc_name);
Report.CloseConnections();
output = output + @" public " + CommonLib.GetDisplayName(table_name) + " " + CommonLib.GetDisplayName(proc_name) + "(";
for (int i = 0; i < ds3.Tables[0].Rows.Count; i++)
{
proc_param_name = ds.Tables[0].Rows[i]["name"].ToString().Replace("@", "").ToLower();
proc_param_type = ds.Tables[0].Rows[i]["type"].ToString();
if (i != ds.Tables[0].Rows.Count - 1)
{
output = output + "string " + proc_param_name + ", ";
}
else
{
output = output + "string " + proc_param_name + "";
}
}
output = output + ")" + nl;
output = output + @" {" + nl;
output = output + @" using (IDbConnection cnn = new SqlConnection(GetConnectionString()))" + nl;
output = output + @" {" + nl;
output = output + @" string sql = $""exec " + proc_name + @" "" +" + nl;
//insert params
for (int i = 0; i < ds3.Tables[0].Rows.Count; i++)
{
proc_param_name = ds.Tables[0].Rows[i]["name"].ToString().Replace("@", "").ToLower();
proc_param_type = ds.Tables[0].Rows[i]["type"].ToString();
if (i != ds.Tables[0].Rows.Count - 1)
{
if (proc_param_type == "numeric" || proc_param_type == "tinyint")
{
output = output + @" $""{" + proc_param_name + @"}, "" +" + nl;
}
else
{
output = output + @" $""'{" + proc_param_name + @"}', "" +" + nl;
}
}
else
{
if (proc_param_type == "numeric" || proc_param_type == "tinyint")
{
output = output + @" $""{" + proc_param_name + @"} "";" + nl;
}
else
{
output = output + @" $""'{" + proc_param_name + @"}' "";" + nl;
}
}
}
output = output + @"" + nl;
output = output + @" return cnn.Query<" + CommonLib.GetDisplayName(table_name) + ">(sql).FirstOrDefault(); ;" + nl;
output = output + @" }" + nl;
output = output + @" }" + nl;
}
output = output + @" }" + nl;
output = output + @"}" + nl;
file = @"C:\Scaffolding\Data Access\" + CommonLib.GetDisplayName(table_name) + "DataAccess.cs";
if (System.IO.File.Exists(file))
{
System.IO.File.Delete(file);
}
System.IO.File.WriteAllText(file, output);
}
public DataSet retrieve_stored_procedures_for_table(string table_name)
{
string strSQL;
SqlCommand cmdSQL = new SqlCommand();
DataSet ds;
SqlDataAdapter adAdapter;
//set up the data adapter/data set
adAdapter = new SqlDataAdapter();
ds = new DataSet();
//set up the query
strSQL = "";
strSQL = strSQL + "SELECT [name] ";
strSQL = strSQL + "FROM sysobjects ";
strSQL = strSQL + "WHERE type = 'P' ";
strSQL = strSQL + "AND category = 0 ";
strSQL = strSQL + "AND [name] like '" + CommonLib.sqlclean(table_name) + "_SP_%' ";
strSQL = strSQL + "AND [name] not in ('" + CommonLib.sqlclean(table_name.ToUpper()) + "_SP_SAVE', '" + CommonLib.sqlclean(table_name.ToUpper()) + "_SP_RETRIEVE', '" + CommonLib.sqlclean(table_name.ToUpper()) + "_SP_DELETE') ";
//Set up the command object
cmdSQL.CommandText = strSQL;
cmdSQL.Connection = connConnectionClass;
//Fill The dataset
adAdapter.SelectCommand = cmdSQL;
adAdapter.Fill(ds, "table");
return(ds);
}
public void buildModel(string table_name, string name_space)
{
Report Report = new Report();
DataSet ds;
string output = "";
string column_name;
string column_data_type = "";
string file;
string table_id;
Report.OpenConnections();
ds = Report.retrieve_db_table_column_summary(table_name);
Report.CloseConnections();
table_id = (string)ds.Tables[0].Rows[0]["COLUMN_NAME"];
output = output + @"using System;" + nl;
output = output + @"using System.ComponentModel.DataAnnotations;" + nl;
output = output + @"using System.ComponentModel.DataAnnotations.Schema;" + nl;
output = output + @"" + nl;
output = output + @"namespace " + name_space + ".Models" + nl;
output = output + @"{" + nl;
output = output + @" public class " + CommonLib.GetDisplayName(table_name) + "" + nl;
output = output + @" {" + nl;
//properties
foreach (DataRow row in ds.Tables[0].Rows)
{
column_name = (string)row["COLUMN_NAME"].ToString();
if ((short)row["DATA_TYPE"] == 2)
{
//numeric
column_data_type = "decimal";
}
else if ((short)row["DATA_TYPE"] == 12)
{
//varchar
column_data_type = "string";
}
else if ((short)row["DATA_TYPE"] == -1)
{
//varchar(max)
column_data_type = "string";
}
else if ((short)row["DATA_TYPE"] == 11)
{
//datetime
column_data_type = "DateTime";
}
else if ((short)row["DATA_TYPE"] == -6)
{
//tinyint
column_data_type = "byte";
}
if (column_name == table_id)
{
output = output + @" [Key]" + nl;
output = output + @" [Display(Name = """ + CommonLib.GetDisplayNameWithSpaces(column_name).Trim() + @""")]" + nl;
output = output + @" public " + column_data_type + " " + column_name + " { get; set; }" + nl;
output = output + nl;
}
else if (column_name != "")
{
output = output + @" [Display(Name = """ + CommonLib.GetDisplayNameWithSpaces(column_name).Trim() + @""")]" + nl;
output = output + @" public " + column_data_type + " " + column_name + " { get; set; }" + nl;
output = output + nl;
}
}
output = output + @" }" + nl;
output = output + @"}" + nl;
file = @"C:\Scaffolding\Models\" + CommonLib.GetDisplayName(table_name) + ".cs";
if (System.IO.File.Exists(file))
{
System.IO.File.Delete(file);
}
System.IO.File.WriteAllText(file, output);
}
