the following includes the following fixs:
1) C# code connection missing ;
2) sql - edit stored procedure now ignores the key fields in the set part of the sql
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace SUNCodeGenerator.Classes
{
class GeneratorClass
{
private string mTableName = "";
private string connStr = "";
public GeneratorClass(string connectionString, string TableName)
{
mTableName = TableName;
connStr = connectionString;
}
private string CreateProperty(string type)
{
string ret = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (type == "C#")
{
ret += "\t\tprivate string m_" + rdr[0].ToString() + ";" +
"\n" +
"\t\tpublic string " + rdr[0].ToString() + "\n" +
"\t\t{\n" +
"\t\t\tget { return m_" + rdr[0].ToString() + "; }\n" +
"\t\t\tset { m_" + rdr[0].ToString() + " = value; }\n" +
"\t\t}\n";
}
else if (type == "VB")
{
ret += "\t\tPrivate m_" + rdr[0].ToString() + " As String" +
"\n" +
"\t\tProperty " + rdr[0].ToString() + " As String\n" +
"\t\t\tGet\n" +
"\t\t\t\tReturn m_" + rdr[0].ToString() + "\n" +
"\t\t\tEnd Get\n" +
"\t\t\tSet(ByVal value As String)\n" +
"\t\t\t\tm_" + rdr[0].ToString() + " = value\n" +
"\t\t\tEnd Set\n" +
"\t\tEnd Property\n\n";
}
}
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
return ret;
}
private string CreateInsert(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
string tValue = "";
string m_value = "string ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " , string ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
}
m_value = m_value.Substring(0, m_value.Length - 10);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tpublic void Insert(" + m_value + ")\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Insert_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
tValue +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
string tValue = "";
string m_value = "ByVal ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " As String , ByVal ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
}
m_value = m_value.Substring(0, m_value.Length - 9);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tPublic Sub Insert(" + m_value + ")\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Insert_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
tValue +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
private string CreateUpdate(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
string tValue = "";
string m_value = "string ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " , string ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(@\"" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
}
m_value = m_value.Substring(0, m_value.Length - 10);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tpublic void Update(" + m_value + ")\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Update_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
tValue +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
string tValue = "";
string m_value = "ByVal ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " As String , ByVal ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
}
m_value = m_value.Substring(0, m_value.Length - 9);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tPublic Sub Update(" + m_value + ")\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Update_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
tValue +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
private string CreateDelete(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
ret += "\t\tpublic void Delete(string ID)\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Delete_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
"\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID);\n" +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
ret += "\t\tPublic Sub Delete(ByVal ID As String)\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Delete_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
"\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID)\n" +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
public string ReturnSQLDelete()
{
string ret = "";
ret = "CREATE PROCEDURE [delete_" + mTableName + "]\n" +
"\t(@ID [int])\n" +
"AS DELETE [" + mTableName + "]\n" +
"WHERE\n" +
"\t( [ID] = @ID)\n";
return ret;
}
public string ReturnSQLSelect()
{
string ret = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr[0].ToString().Length > 4)
{
if (rdr[0].ToString().Substring(rdr[0].ToString().Length - 4, 4) == "Code")
m_value += "\t\t(Select Title From " + rdr[0].ToString().Substring(0, rdr[0].ToString().Length - 5) + " Where Code = " + rdr[0].ToString() + ")\tAS\t'" + rdr[0].ToString() + "',\n";
else
m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
}
else
m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE View [Select_" + mTableName + "]\n" +
"AS\n" +
"\nSelect " + m_value + "\n\n" +
"From\t" + mTableName + "";
return ret;
}
public string ReturnSQLUpdate()
{
string ret = "";
string tValue = "";
string tValue1 = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(@"
SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG AND
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA AND
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AND
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
Where INFORMATION_SCHEMA.COLUMNS.Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr[3].ToString()=="")
m_value += "\t\t" + rdr[0].ToString() + "\t\t\t=\t@" + rdr[0].ToString() + ",\n";
if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
else
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
tValue1 += "\t@" + rdr[0].ToString() + ",\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
tValue = tValue.Substring(0, tValue.Length - 2);
tValue1 = tValue1.Substring(0, tValue1.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE PROCEDURE [update_" + mTableName + "]\n" +
"(\n" + tValue + "\n)\n" +
"AS UPDATE " + mTableName + "\n" +
"SET\n" + m_value + "\n" +
"WHERE\n" +
"(\n\tID\t=\t@ID\n)";
return ret;
}
public string ReturnSQLInsert()
{
string ret = "";
string tValue = "";
string tValue1 = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += "\t" + rdr[0].ToString() + ",\n";
if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
else
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
tValue1 += "\t@" + rdr[0].ToString() + ",\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
tValue = tValue.Substring(0, tValue.Length - 2);
tValue1 = tValue1.Substring(0, tValue1.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE PROCEDURE [insert_" + mTableName + "]\n" +
"\t(\n" + tValue + "\n\t)\n" +
"AS INSERT INTO " + mTableName + "\n" +
"\t(\n" + m_value + "\n\t)\n" +
"VALUES\n" +
"\t(\n" + tValue1 + "\n\t)\n";
return ret;
}
public string CreateSelect(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
ret = "\t\tpublic DataSet SelectRecords()\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlDataAdapter cmd = new SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn);\n" +
"\t\t\tDataSet dts = new DataSet();\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.Fill(dts);\n" +
"\t\t\t\treturn dts;\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{ }\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t\treturn dts;\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
ret += "\t\tPublic Function SelectRecords() As DataSet\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn)\n" +
"\t\t\tDim dts As New DataSet()\n" +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.Fill(dts)\n" +
"\t\t\t\tReturn dts\n" +
"\t\t\tCatch\n" +
"\t\t\t\tReturn Null\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Function\n";
}
return ret;
}
public string ReturnClass(string type)
{
string ret = "";
if (type == "C#")
{
ret = "using System;\n" +
"using System.Collections.Generic;\n" +
"using System.Text;\n" +
"using System.Data;\n" +
"using System.Data.SqlClient;\n" +
"using System.Collections;\n" +
"using System.Configuration;\n" +
"using System.Web;\n" +
"using System.Web.Security;\n" +
"using System.Web.UI;\n" +
"using System.Web.UI.WebControls;\n" +
"using System.Web.UI.WebControls.WebParts;\n" +
"using System.Web.UI.HtmlControls;\n" +
"\n" +
"namespace DAL\n" +
"{\n" +
"\tclass " + mTableName + "\n" +
"\t{\n" +
"\n" +
"\t\tprivate string ConnectionString;\n" +
"\t\tpublic " + mTableName + "(string ConnStr)\n" +
"\t\t{\n" +
"\t\t\tConnectionString = ConnStr;\n" +
"\t\t}\n" +
"\n" +
CreateProperty("C#") +
"\n" +
CreateUpdate("C#") +
"\n" +
CreateInsert("C#") +
"\n" +
CreateDelete("C#") +
"\n" +
CreateSelect("C#") +
"\t}\n" +
"}\n";
}
else if (type == "VB")
{
ret = "Imports System\n" +
"Imports System.Collections.Generic\n" +
"Imports System.Text\n" +
"Imports System.Data\n" +
"Imports System.Data.SqlClient\n" +
"Imports System.Collections\n" +
"Imports System.Configuration\n" +
"Imports System.Web\n" +
"Imports System.Web.Security\n" +
"Imports System.Web.UI\n" +
"Imports System.Web.UI.WebControls\n" +
"Imports System.Web.UI.WebControls.WebParts\n" +
"Imports System.Web.UI.HtmlControls\n" +
"\n" +
"Namespace SUNCodeGenerator\n" +
"\tPublic Class " + mTableName + "\n" +
"\n" +
"\t\tPrivate ConnectionString As String\n" +
"\n" +
CreateProperty("VB") +
"\n" +
CreateUpdate("VB") +
"\n" +
CreateInsert("VB") +
"\n" +
CreateDelete("VB") +
"\n" +
CreateSelect("VB") +
"\tEnd Class\n" +
"End Namespace\n";
}
return ret;
}
}
}
|