Introduction
One of the most tedious parts of developing database-driven application is coding the classes for your database objects. Especially in a situation when you are dealing with a database containing a large number of tables, or tables with many columns. Today the application development process has vastly expanded so it is better to use sub applications to generate code dynamically / automatically. I developed this application to reduce burden on software developers and provide them some relief. As a software developer I can understand very well that programming is a tough job so they need some rest.Key Features
Application is designed to create- Business Logic Layer Class.
- Data Access Layer Class.
- SQL Database stored Procedure script.
Key Benifits
- Reduce development time
- Reduce development cost
- Follow standard coding techniques and application architecture.
- Fully commented code so any one can easily modify it according to needs.
How to use this Application
In order to use this application you must know computer name/ IP address and login information for connecting SQL server database.
After successful login you can go to another tab "Create Class and Stored Procedure" .Select particular database and check tables to create stored procedure and classes. Click on “Create” button to create stored procedures and classes.
All classes and stored procedure scripts reside in bin folder.
Example
As a very simple example, suppose we have the table name Employee.
Create Business Logic Layer Class
Business Logic Layer class contains Namespaces, Default Constructor, Private Fields, Public Properties with getters and setters and all methods (Select, Insert, Delete and Update) required for connecting Database Access Layer. For every database table it creates class with the name same as table name and concatenate it with “controller”. In this example application will create class with the name “clsEmployeeController”. It also creates properties with the name same as table attributes.
Create Data Acess Layer Class
Database Access layer class contains Namespaces, Default Constructor, Private Fields, Public Properties with getters and setters and all methods (Select, Insert, Delete and Update) required to direct deal with database with stored procedures. For every database table it creates class with the name same as table name and creates properties with the name same as table attributes. In this example application will create class with the name “clsEmployee”.
Create Stored Proced Script
It also creates script of four stored procedures (SELECT, INSERT, DELETE, UPDATE) in notepad file with the name same as table name. You can simply copy and run it on Sql Query analyzer.Points of Interest
I did not use any library in this application. It is a small application with BIG benefits.code
ClassGenerator.cs
/*
* Created by: Syeda Anila Nusrat
* Date: 28/05/2010
* Time: 11:54 PM
*/
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using Microsoft.ApplicationBlocks.Data;
namespace AutomaticClassGenerator
{
public partial class ClassGenerator : Form
{
#region Constructor
public ClassGenerator()
{
InitializeComponent();
}
#endregion
#region Private Variables
private static string SQL_CONN_STRING = string.Empty;
private static SqlConnection connection;
private static string strTable = string.Empty;
#endregion
#region Common Methods
//...........Create connection string .................
private string CreateConnectionStringWithoutDatabase()
{
try
{
string strDataSource = this.txtServerName.Text;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (this.rboUsernamePasswordSecurity.Checked)
{
//........................... user name password security mode............................
string strUserName = this.txtUserName.Text;
string strPassword = this.txtPassword.Text;
sb.AppendFormat("Data Source=" + strDataSource + ";Persist Security Info=True;User ID=" + strUserName + ";Password=" + strPassword);
}
else if (this.rboWindowsSecurity.Checked)
{
//......................... windows authentication security mode..........................
sb.AppendFormat("Data Source=" + strDataSource + ";Integrated Security=SSPI;Persist Security Info=False");
}
SQL_CONN_STRING = sb.ToString();
}
catch (Exception ex)
{
throw ex;
}
return SQL_CONN_STRING;
}
private string CreateConnectionStringWithDatabase()
{
try
{
string strDataSource = this.txtServerName.Text;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (this.rboUsernamePasswordSecurity.Checked)
{
//........................... user name password security mode............................
string strUserName = this.txtUserName.Text;
string strPassword = this.txtPassword.Text;
sb.AppendFormat("Data Source=" + strDataSource + ";Persist Security Info=True;User ID=" + strUserName + ";Password=" + strPassword + ";Initial Catalog=" + this.cboDatabaseNames.SelectedValue);
}
else if (this.rboWindowsSecurity.Checked)
{
//......................... windows authentication security mode..........................
sb.AppendFormat("Data Source=" + strDataSource + ";Integrated Security=SSPI;Persist Security Info=False" + ";Initial Catalog=" + this.cboDatabaseNames.SelectedValue);
}
SQL_CONN_STRING = sb.ToString();
}
catch (Exception ex)
{
throw ex;
}
return SQL_CONN_STRING;
}
//........... Connection with database .................
private bool ConnectToDatabase(string SQL_CONN_STRING)
{
bool isConnected = false;
try
{
connection = new SqlConnection(SQL_CONN_STRING);
connection.Open();
if (connection != null)
{
isConnected = true;
}
}
catch
{
isConnected = false;
MessageBox.Show("Login failed");
}
return isConnected;
}
//........... Reset form controls ......................
private void Reset(bool status)
{
try
{
if (status)
{
//.................... True .................................
this.gboConnectionStatus.Enabled = !status;
this.btnDisconnect.Enabled = !status;
this.tabAutomaticClassGenerator.TabPages.Add(this.tbConnectionString);
this.tabAutomaticClassGenerator.TabPages.Remove(this.tbClassGenerator);
}
else
{
//.................... False .................................
this.gboConnectionStatus.Enabled = !status;
this.btnDisconnect.Enabled = !status;
this.tabAutomaticClassGenerator.TabPages.Remove(this.tbConnectionString);
this.tabAutomaticClassGenerator.TabPages.Add(this.tbClassGenerator);
}
//this.gboConnectionStatus.Enabled = !status;
//this.gboConnectionString.Enabled = status;
//this.btnDisconnect.Enabled = !status;
//this.gboCreateClass.Enabled = !status;
}
catch(Exception ex)
{
throw ex;
}
}
private void ClearAll()
{
try
{
this.txtServerName.Text = "";
this.rboWindowsSecurity.Checked = true;
this.rboUsernamePasswordSecurity.Checked = false;
this.txtUserName.Text = "";
this.txtPassword.Text = "";
this.lblConnectionStatus.Text = "Not Connected";
//this.cboDatabaseNames.DataSource = null;
//this.cboDatabaseNames.Items.Clear();
//this.chkListBoxDataBaseTables.DataSource = null;
//this.chkListBoxDataBaseTables.Items.Clear();
}
catch (Exception ex)
{
throw ex;
}
}
//............ Get Database , Tables and Attributes Names ................
private void GetAllDatabaseNames()
{
try
{
//Get all database names from selected computer
string strSQLquery = "select name as DatabaseName from master.dbo.sysdatabases";
DataSet ds = SqlHelper.ExecuteDataset(connection, CommandType.Text,strSQLquery);
if (ds.Tables.Count >= 1)
{
if (ds.Tables[0].Rows.Count >= 1)
{
this.cboDatabaseNames.DataSource = ds.Tables[0];
}
}
this.cboDatabaseNames.SelectedIndex = 0;
//Get all table names from selected database
if (ConnectToDatabase(CreateConnectionStringWithDatabase()))
{
GetAllTableNames();
}
}
catch(Exception ex)
{
throw ex;
}
}
private void GetAllTableNames()
{
try
{
string strSQLquery = "select table_name from Information_Schema.Tables where Table_Type='Base Table' order by table_name";
DataSet ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, strSQLquery);
if (ds.Tables.Count >= 1)
{
//Fill check list box with database tables
if(ds.Tables[0].Rows.Count>=1)
{
chkListBoxDataBaseTables.DataSource = ds.Tables[0];
chkListBoxDataBaseTables.DisplayMember = "table_name";
chkListBoxDataBaseTables.ValueMember = "table_name";
}
}
}
catch(Exception ex)
{
throw ex;
}
}
private SqlDataReader GetTableAttributes(string strTableName)
{
SqlDataReader dr = null;
try
{
//Get table attributes
string strSQLquery = "SELECT table_name,column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + strTableName + " ' ";
dr = SqlHelper.ExecuteReader(connection, CommandType.Text, strSQLquery);
if (dr.HasRows)
{
return dr;
}
}
catch (Exception ex)
{
throw ex;
}
return dr;
}
//Get system datatype from sql datatype
private string GetSystemType(string tstrSqlType)
{
string _Type = string.Empty;
try
{
#region Sql to dot net Conversion
switch (tstrSqlType)
{
case "bigint":
{
_Type = "long";
} break;
case "smallint":
{
_Type = "short";
} break;
case "tinyint":
{
_Type = "byte";
} break;
case "int":
{
_Type = "int";
} break;
case "bit":
{
_Type = "bool";
} break;
case "decimal":
case "numeric":
{
_Type = "decimal";
} break;
case "money":
case "smallmoney":
{
_Type = "decimal";
} break;
case "float":
case "real":
{
_Type = "float";
} break;
case "datetime":
case "smalldatetime":
{
_Type = "System.DateTime";
} break;
case "char":
{
_Type = "char";
} break;
case "sql_variant":
{
_Type = "object";
} break;
case "varchar":
case "text":
case "nchar":
case "nvarchar":
case "ntext":
{
_Type = "string";
} break;
case "binary":
case "varbinary":
{
_Type = "byte[]";
} break;
case "image":
{
_Type = "System.Drawing.Image";
} break;
case "timestamp":
case "uniqueidentifier":
{
_Type = "string";
} break;
default:
{
_Type = "unknown";
} break;
}
#endregion
}
catch (Exception ex)
{
_Type = null;
throw ex;
}
return _Type;
}
//Sql databse type conversion
private string SqlDatabseType(string tstrSqlType)
{
string _Type = string.Empty;
try
{
#region Sql Conversion
switch (tstrSqlType)
{
case "bigint":
{
_Type = "BigInt";
}
break;
case "smallint":
{
_Type = "SmallInt";
}
break;
case "tinyint":
{
_Type = "TinyInt";
}
break;
case "int":
{
_Type = "Int";
}
break;
case "bit":
{
_Type = "Bit";
}
break;
case "decimal":
case "numeric":
{
_Type = "Decimal";
}
break;
case "money":
{
_Type = "Money";
}
break;
case "smallmoney":
{
_Type = "SmallMoney";
}
break;
case "float":
case "real":
{
_Type = "Float";
}
break;
case "datetime":
{
_Type = "System.DateTime.DateTime";
}
break;
case "smalldatetime":
{
_Type = "System.DateTime.SmallDateTime";
}
break;
case "char":
{
_Type = "Char";
}
break;
case "sql_variant":
{
_Type = "object";
}
break;
case "nvarchar":
{
_Type = "NVarChar";
}
break;
case "varchar":
{
_Type = "VarChar";
}
break;
case "text":
{
_Type = "Text";
}
break;
case "nchar":
{
_Type = "NChar";
}
break;
case "binary":
{
_Type = "Binary";
}
break;
case "varbinary":
{
_Type = "byte[]";
}
break;
case "image":
{
_Type = "System.Drawing.Image";
}
break;
default:
{
_Type = "unknown";
}
break;
}
#endregion
}
catch (Exception ex)
{
_Type = null;
throw ex;
}
return _Type;
}
private void LogError(Exception ex)
{
string sLogFormat = DateTime.Now.ToShortDateString().ToString() + " " + DateTime.Now.ToLongTimeString().ToString() + " ==> ";
string sPathName = @"ErrorLog\";
string sYear = DateTime.Now.Year.ToString();
string sMonth = DateTime.Now.Month.ToString();
string sDay = DateTime.Now.Day.ToString();
string sErrorTime = sYear + sMonth + sDay;
if (!Directory.Exists(sPathName))
{
Directory.CreateDirectory(sPathName);
}
StreamWriter sw = new StreamWriter(sPathName + "ErrorLog" + sErrorTime + ".txt", true);
sw.WriteLine(sLogFormat + ex.Message);
sw.Flush();
sw.Close();
MessageBox.Show(ex.Message);
}
#endregion
#region Common Events
private void btnCreateClassAndSP_Click(object sender, EventArgs e)
{
try
{
bool isCreated = false;
if ((this.chkDataAccessLayerClasses.Checked) || (this.chkBusinessLogicLayerClasses.Checked) || (this.chkSelectSP.Checked) || (this.chkDeleteSP.Checked) || (this.chkInsertSP.Checked) || (this.chkUpdateSP.Checked))
{
if (this.chkDataAccessLayerClasses.Checked)
{
#region Create DataAccessLayer Class
foreach (int indexChecked in chkListBoxDataBaseTables.CheckedIndices)
{
strTable = chkListBoxDataBaseTables.GetItemText(chkListBoxDataBaseTables.Items[indexChecked]);
GenerateClass(GetTableAttributes(strTable), "DatabaseLayer", "cls" + strTable);
}
#endregion
isCreated = true;
}
if (this.chkBusinessLogicLayerClasses.Checked)
{
#region Create BusinessLogicLayer Class
foreach (int indexChecked in chkListBoxDataBaseTables.CheckedIndices)
{
strTable = chkListBoxDataBaseTables.GetItemText(chkListBoxDataBaseTables.Items[indexChecked]);
GenerateClass(GetTableAttributes(strTable), "BusinessLayer", "cls" + strTable + "Controller");
//GenerateClass(GetTableAttributes(strTable));
}
#endregion
isCreated = true;
}
if ((this.chkSelectSP.Checked) || (this.chkDeleteSP.Checked) || (this.chkInsertSP.Checked) || (this.chkUpdateSP.Checked))
{
#region Create Stored Procedure
foreach (int indexChecked in chkListBoxDataBaseTables.CheckedIndices)
{
strTable = chkListBoxDataBaseTables.GetItemText(chkListBoxDataBaseTables.Items[indexChecked]);
GenerateStoredProcedures(GetTableAttributes(strTable));
}
#endregion
isCreated = true;
}
if (isCreated)
{
MessageBox.Show("Classes/StoredProcedures have been created successfully");
}
}
else
{
MessageBox.Show("Please Check and then press Create");
}
}
catch (Exception ex)
{
LogError(ex);
}
}
private void btnConnectDisconnect_Click(object sender, EventArgs e)
{
try
{
if ((this.rboWindowsSecurity.Checked) && (this.txtServerName.Text != ""))
{
this.lblConnectionStatus.Text = "Connected to " + this.txtServerName.Text; ;
#region Windows Security
if (ConnectToDatabase(CreateConnectionStringWithoutDatabase()))
{
Reset(false);
GetAllDatabaseNames();
}
#endregion
}
else if ((this.rboUsernamePasswordSecurity.Checked) && (this.txtServerName.Text != ""))
{
this.lblConnectionStatus.Text = "Connected to " + this.txtServerName.Text; ;
#region User Name Password security
if ((this.txtUserName.Text != "") && (this.txtPassword.Text != ""))
{
if (ConnectToDatabase(CreateConnectionStringWithoutDatabase()))
{
Reset(false);
GetAllDatabaseNames();
}
}
else
{
MessageBox.Show("Please enter user name and password");
}
#endregion
}
else
{
MessageBox.Show("Please enter server name");
}
}
catch (Exception ex)
{
LogError(ex);
}
}
private void btnDisconnect_Click(object sender, EventArgs e)
{
try
{
ClearAll();
Reset(true);
if (connection != null)
{
connection.Close();
}
}
catch (Exception ex)
{
LogError(ex);
}
}
private void chkCreateStoredProcedure_CheckedChanged(object sender, EventArgs e)
{
}
#endregion
#region Region Generate Class
//.............................................. User Define Methods ..................................................
//Generate Classes automatically for selected tables ............................
private void GenerateClass(SqlDataReader dr, string NamespaceName, string ClassName)
{
try
{
if (dr != null)
{
StreamWriter sw = null;
System.Text.StringBuilder sb = null;
//Stream myStream = null;
ArrayList AttributeNameArrayList = new ArrayList();
ArrayList AttributeTypeArrayList_DotNet = new ArrayList();
ArrayList AttributeTypeArrayList_Sql = new ArrayList();
ArrayList AttributeTypeArrayList_Sql2 = new ArrayList();
string strAttributeName = string.Empty;
string strAttributeType_DotNet = string.Empty;
string strAttributeType_Sql = string.Empty;
string lstrTableName = strTable; //table name
#region Create Empty cs file
sb = new System.Text.StringBuilder(ClassName);
// sb = new System.Text.StringBuilder(lstrTableName);
sb.Append(".cs");
FileInfo lobjFileInfo = new FileInfo(sb.ToString());
sw = lobjFileInfo.CreateText();
#endregion
#region Get Table Name, Attributes Name and Attribute Types
while (dr.Read())
{
AttributeNameArrayList.Add(dr.GetString(1)); //Attribute Name
AttributeTypeArrayList_Sql.Add(dr.GetString(2)); //Attribute Type in Sql
AttributeTypeArrayList_DotNet.Add(GetSystemType(dr.GetString(2))); //Attribute Type in dotnet
AttributeTypeArrayList_Sql2.Add(SqlDatabseType(dr.GetString(2))); //Attribute Type in Sql
}
#endregion
#region Write Namespaces
this.WriteNamespaces(sw, lstrTableName, NamespaceName, ClassName);
#endregion
#region Write Class Default Constructor
this.WriteDefaultConstructor(sw, ClassName);
#endregion
#region Write Private Variables
sb = new System.Text.StringBuilder("\r\n\t");
sb.Append("#region Private Variables");
//sb.Append("\r\n\tprivate int result;");
for (int j = 0; j < AttributeNameArrayList.Count; j++)
{
strAttributeName = AttributeNameArrayList[j].ToString();
strAttributeType_DotNet = AttributeTypeArrayList_DotNet[j].ToString();
this.WritePrivateVariables(sb, strAttributeType_DotNet, strAttributeName);
}
sb.Append("\r\n\t cls" + strTable + " objcls" + strTable + ";");
sb.Append("\r\n\t#endregion");
sw.WriteLine(sb.ToString());
#endregion
#region Write Public Properties
sb = new System.Text.StringBuilder("\r\n\t");
sb.Append("#region Public Properties");
for (int j = 0; j < AttributeNameArrayList.Count; j++)
{
strAttributeName = AttributeNameArrayList[j].ToString();
strAttributeType_DotNet = AttributeTypeArrayList_DotNet[j].ToString();
this.WritePublicProperties(sb, strAttributeType_DotNet, strAttributeName);
}
sb.Append("\r\n\t#endregion");
sw.WriteLine(sb.ToString());
#endregion
if (NamespaceName == "BusinessLayer")
{
#region Write Public Methods for BLL
sb = new System.Text.StringBuilder("\r\n\t");
sb.Append("#region Public Methods");
//...................................... Select Method ..........................................
WriteSelectMethod_forBLL(sb, AttributeNameArrayList);
//...................................... Insert Method ..........................................
WriteInsertMethod_forBLL(sb, AttributeNameArrayList);
//...................................... Update Method ..........................................
WriteUpdateMethod_forBLL(sb, AttributeNameArrayList);
//...................................... Delete Method ..........................................
WriteDeleteMethod_forBLL(sb, AttributeNameArrayList);
sb.Append("\r\n\t#endregion");
sw.WriteLine(sb.ToString());
#endregion
}
else
if (NamespaceName == "DatabaseLayer")
{
#region Write Public Methods for DAL
sb = new System.Text.StringBuilder("\r\n\t");
sb.Append("#region Public Methods");
//...................................... Select Method ..........................................
strAttributeName = AttributeNameArrayList[0].ToString();
strAttributeType_Sql = AttributeTypeArrayList_Sql[0].ToString();
WriteSelectMethod_forDAL(sb, AttributeNameArrayList, AttributeTypeArrayList_Sql2);
//...................................... Insert Method ..........................................
WriteInsertMethod_forDAL(sb, AttributeNameArrayList);
//...................................... Update Method ..........................................
WriteUpdateMethod_forDAL(sb, AttributeNameArrayList);
//...................................... Delete Method ..........................................
strAttributeName = AttributeNameArrayList[0].ToString();
strAttributeType_Sql = AttributeTypeArrayList_Sql[0].ToString();
WriteDeleteMethod_forDAL(sb, strAttributeType_Sql, strAttributeName);
sb.Append("\r\n\t#endregion");
sw.WriteLine(sb.ToString());
#endregion
}
#region Close file
if (sw != null)
{
sw.WriteLine("\r\n\t}\r\n}");
dr.Close();
sw.Close();
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
}
//Namespace
private void WriteNamespaces(StreamWriter sw, string tstrClassName, string NamespaceName, string ClassName)
{
try
{
System.Text.StringBuilder sb = new System.Text.StringBuilder("using System;");
sb.Append("\r\nusing System.Collections.Generic;");
sb.Append("\r\nusing System.Text;");
sb.Append("\r\nusing System.Data;");
sb.Append("\r\nusing System.Data.SqlClient;");
sb.Append("\r\nusing Microsoft.ApplicationBlocks.Data;");
sb.Append("\r\n\r\nnamespace " + NamespaceName);
sb.Append("\r\n{");
sb.Append("\r\n\tpublic class ");
sb.Append(ClassName);
sb.Append("\r\n\t{");
sw.WriteLine(sb.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//Default Constructor
private void WriteDefaultConstructor(StreamWriter sw, string ClassName)
{
try
{
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\n\t#region Constructor");
sb.Append("\r\n\tpublic ");
sb.Append(ClassName);
sb.Append("()\r\n\t{}");
sb.Append("\r\n\t#endregion");
sw.WriteLine(sb.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
//PrivateVariables
private void WritePrivateVariables(StringBuilder sb,string tstrAttributeType_DotNet, string tstrAttributeName)
{
try
{
sb.Append("\r\n\t");
sb.AppendFormat("private {0} _{1};", new object[] { tstrAttributeType_DotNet, tstrAttributeName, "{", "}" });
}
catch (Exception ex)
{
throw ex;
}
}
//PublicProperties
private void WritePublicProperties(StringBuilder sb, string tstrAttributeType_DotNet, string tstrAttributeName)
{
try
{
sb.Append("\r\n\t");
sb.AppendFormat("public {0} {1}\r\n\t{2} \r\n\t\tget {2} return _{1}; {3}\r\n\t\tset {2} _{1} = value; {3}\r\n\t{3}", new object[] { tstrAttributeType_DotNet, tstrAttributeName, "{", "}" });
}
catch (Exception ex)
{
throw ex;
}
}
//Methods for BusinessLogicLayer
private void WriteSelectMethod_forBLL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
string strParameter = string.Empty;
string AttributeName = string.Empty;
string objName = "objcls" + strTable;
AttributeName = AttributeNameArrayList[0].ToString();
strParameter = strParameter + "\r\n\t\t\t" + objName + "." + AttributeName + " = " + AttributeName + ";";
sb.Append("\r\n\tpublic DataTable Select()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\tDataTable dt;");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\t" + "objcls" + strTable + " = new " + "cls" + strTable + "();");
sb.Append("\r\n\t\t\t" + strParameter);
sb.Append("\r\n\t\t");
sb.Append("\r\n\t\t\tdt = objcls" + strTable + ".Select();");
sb.Append("\r\n\t\t\treturn dt;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteInsertMethod_forBLL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
string strParameter = string.Empty;
string AttributeName = string.Empty;
string objName = "objcls" + strTable;
for (int i = 1; i < AttributeNameArrayList.Count; i++)
{
AttributeName = AttributeNameArrayList[i].ToString();
strParameter = strParameter + "\r\n\t\t\t" + objName + "." + AttributeName + " = " + AttributeName + ";";
}
sb.Append("\r\n\tpublic bool Insert()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\t" + "objcls" + strTable + " = new " + "cls" + strTable + "();");
sb.Append("\r\n\t\t\t" + strParameter);
sb.Append("\r\n\t\t");
sb.Append("\r\n\t\t\tif(objcls" + strTable + ".Insert())");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteUpdateMethod_forBLL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
string AttributeName = string.Empty;
string strParameter = string.Empty;
string objName = "objcls" + strTable;
for (int i = 0; i < AttributeNameArrayList.Count; i++)
{
AttributeName = AttributeNameArrayList[i].ToString();
strParameter = strParameter + "\r\n\t\t\t" + objName + "." + AttributeName + " = " + AttributeName + ";";
}
sb.Append("\r\n\tpublic bool Update()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\t" + "objcls" + strTable + " = new " + "cls" + strTable + "();");
sb.Append("\r\n\t\t\t" + strParameter);
sb.Append("\r\n\t\t");
sb.Append("\r\n\t\t\tif(objcls" + strTable + ".Update())");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteDeleteMethod_forBLL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
string strParameter = string.Empty;
string AttributeName = string.Empty;
string objName = "objcls" + strTable;
AttributeName = AttributeNameArrayList[0].ToString();
strParameter = strParameter + "\r\n\t\t\t" + objName + "." + AttributeName + " = " + AttributeName + ";";
sb.Append("\r\n\tpublic bool Delete()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\t" + "objcls" + strTable + " = new " + "cls" + strTable + "();");
sb.Append("\r\n\t\t\t" + strParameter);
sb.Append("\r\n\t\t");
sb.Append("\r\n\t\t\tif(objcls" + strTable + ".Delete())");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
//Methods for DataAccessLayer
private void WriteSelectMethod_forDAL(StringBuilder sb, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
string AttributeName = string.Empty;
string AttributeTypeSQL = string.Empty;
string strParameter = string.Empty;
string temp;
string temp2 = Environment.NewLine;
string strBlankSpace = string.Empty;
const string consTemp = @"@";
const string dq = @"""";
//sp Name
string spName = "SP_" + strTable + "_Select";
spName = dq + spName + dq;
for (int i = 0; i < AttributeTypeArrayList_Sql.Count; i++)
{
AttributeTypeSQL = AttributeTypeArrayList_Sql[i].ToString();
temp = dq + consTemp + AttributeTypeSQL + dq;
strParameter = strParameter + "\r\n\t\t\t\tnew SqlParameter(" + dq + consTemp + AttributeNameArrayList[i] + dq + "," + "SqlDbType." + AttributeTypeArrayList_Sql[i] + "),";
}
strParameter = strParameter.Substring(0, strParameter.Length - 1);
//conditions
for (int i = 0; i < AttributeTypeArrayList_Sql.Count; i++)
{
if (AttributeTypeArrayList_Sql[i].ToString().Contains("varchar"))
{
strBlankSpace = dq + "" + dq;
temp2 = temp2 + "\r\n\t\t\t\tif (" + AttributeNameArrayList[i] + " != " + strBlankSpace + " && " + AttributeNameArrayList[i] + " != null)";
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = " + AttributeNameArrayList[i].ToString() + ";\r\n\t\t\t\t}";
temp2 = temp2 + "\r\n\t\t\t\telse";
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = DBNull.Value;\r\n\t\t\t\t}\r\n";
}
else if (AttributeTypeArrayList_Sql[i].ToString().Contains("int"))
{
temp2 = temp2 + "\r\n\t\t\t\tif (" + AttributeNameArrayList[i] + " != 0)" ;
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = " + AttributeNameArrayList[i].ToString() + ";\r\n\t\t\t\t}";
temp2 = temp2 + "\r\n\t\t\t\telse";
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = DBNull.Value;\r\n\t\t\t\t}\r\n";
}
else
{
temp2 = temp2 + "\r\n\t\t\t\tif (" + AttributeNameArrayList[i] + " != null)";
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = " + AttributeNameArrayList[i].ToString() + ";\r\n\t\t\t\t}";
temp2 = temp2 + "\r\n\t\t\t\telse";
temp2 = temp2 + "\r\n\t\t\t\t{\r\n\t\t\t\t\tParams[" + i + "].Value = DBNull.Value;\r\n\t\t\t\t}\r\n";
}
}
sb.Append("\r\n\tpublic DataTable Select()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\tDataSet ds;");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tSqlParameter[] Params = \r\n\t\t\t{ " + strParameter + " \r\n\t\t\t};");
sb.Append("\r\n\t\t\t" + temp2 + "\r\n\t\t\t");
sb.Append("\r\n\t\t\tds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure," + spName + ",Params);");
sb.Append("\r\n\t\t\treturn ds.Tables[0];");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteInsertMethod_forDAL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
//Inverted commas
const string consTemp = @"@";
const string dq = @"""";
string temp;
//sp Name
string spName = "SP_" + strTable + "_Insert";
spName = dq + spName + dq;
string strParameter = string.Empty;
string AttributeName = string.Empty;
for (int i = 1; i <= AttributeNameArrayList.Count-1; i++)
{
AttributeName = AttributeNameArrayList[i].ToString();
temp = dq + consTemp + AttributeName + dq;
strParameter = strParameter + "\r\n\t\t\t\tnew SqlParameter(" + temp + "," + AttributeName + "),";
}
strParameter = strParameter.Substring(0, strParameter.Length - 1);
sb.Append("\r\n\tpublic bool Insert()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tSqlParameter[] Params = \r\n\t\t\t{ " + strParameter + " \r\n\t\t\t};");
sb.Append("\r\n\t\t\tint result = SqlHelper.ExecuteNonQuery(Transaction, CommandType.StoredProcedure," + spName + ",Params);");
sb.Append("\r\n\t\t\tif (result > 0)");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteUpdateMethod_forDAL(StringBuilder sb, ArrayList AttributeNameArrayList)
{
try
{
//Inverted commas
const string consTemp = @"@";
const string dq = @"""";
string temp;
//sp Name
string spName = "SP_" + strTable + "_Update";
spName = dq + spName + dq;
string AttributeName = string.Empty;
string strParameter = string.Empty;
for (int i = 0; i <= AttributeNameArrayList.Count-1; i++)
{
AttributeName = AttributeNameArrayList[i].ToString();
temp = dq + consTemp + AttributeName + dq;
strParameter = strParameter + "\r\n\t\t\t\tnew SqlParameter(" + temp + "," + AttributeName + "),";
}
strParameter = strParameter.Substring(0, strParameter.Length - 1);
sb.Append("\r\n\tpublic bool Update()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tSqlParameter[] Params = \r\n\t\t\t{ " + strParameter + " \r\n\t\t\t};");
sb.Append("\r\n\t\t\tint result = SqlHelper.ExecuteNonQuery(Transaction, CommandType.StoredProcedure," + spName + ",Params);");
sb.Append("\r\n\t\t\tif (result > 0)");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
private void WriteDeleteMethod_forDAL(StringBuilder sb, string tstrAttributeType_DotNet, string tstrAttributeName)
{
try
{
//Inverted commas
const string consTemp = @"@";
string temp = consTemp + tstrAttributeName;
const string dq = @"""";
temp = dq + temp + dq;
//sp Name
string spName = "SP_" + strTable + "_Delete";
spName = dq + spName + dq;
sb.Append("\r\n\tpublic bool Delete()");
sb.Append("\r\n\t{");
sb.Append("\r\n\t\ttry");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tSqlParameter[] Params = { new SqlParameter(" + temp + "," + tstrAttributeName + ") };");
sb.Append("\r\n\t\t\tint result = SqlHelper.ExecuteNonQuery(Transaction, CommandType.StoredProcedure," + spName + ",Params);");
sb.Append("\r\n\t\t\tif (result > 0)");
sb.Append("\r\n\t\t\t{");
sb.Append("\r\n\t\t\t\treturn true;");
sb.Append("\r\n\t\t\t}");
sb.Append("\r\n\t\t\treturn false;");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t\tcatch(Exception ex)");
sb.Append("\r\n\t\t{");
sb.Append("\r\n\t\t\tthrow new Exception(ex.Message);");
sb.Append("\r\n\t\t}");
sb.Append("\r\n\t}");
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Region Generate StoredProcedure
//............................................. User Define Methods ...............................................
private void GenerateStoredProcedures(SqlDataReader dr)
{
//Generate StoredProcedures automatically for selected tables ............................
try
{
if (dr != null)
{
StreamWriter sw = null;
System.Text.StringBuilder sb = null;
string strSPname = string.Empty;
ArrayList AttributeNameArrayList = new ArrayList();
ArrayList AttributeTypeArrayList_DotNet = new ArrayList();
ArrayList AttributeTypeArrayList_Sql = new ArrayList();
#region Get Table Name, Attributes Name and Attribute Types
while (dr.Read())
{
AttributeNameArrayList.Add(dr.GetString(1)); //Attribute Name
AttributeTypeArrayList_Sql.Add(dr.GetString(2)); //Attribute Type in Sql
AttributeTypeArrayList_DotNet.Add(GetSystemType(dr.GetString(2))); //Attribute Type in dotnet
}
#endregion
#region Create Empty txt file
sb = new System.Text.StringBuilder(strTable);
sb.Append(".txt");
FileInfo lobjFileInfo = new FileInfo(sb.ToString());
sw = lobjFileInfo.CreateText();
#endregion
if (this.chkSelectSP.Checked)
{
#region Write Select SP Script
strSPname = "SP_" + strTable + "_Select";
SP_Heading(sw, strSPname);
Search_SP_in_DB(sw, strSPname);
Set_QuotedIdentifierAndANSInull(sw);
Select_StoredProcedure(sw, strSPname, AttributeNameArrayList, AttributeTypeArrayList_Sql);
Set_QuotedIdentifierAndANSInull(sw);
sb = new StringBuilder();
sb.Append("\r\n");
sb.Append("\r\n");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
if (this.chkDeleteSP.Checked)
{
#region Write Delete SP Script
strSPname = "SP_" + strTable + "_Delete";
SP_Heading(sw, strSPname);
Search_SP_in_DB(sw, strSPname);
Set_QuotedIdentifierAndANSInull(sw);
SoftDelete_StoredProcedure(sw, strSPname, AttributeNameArrayList, AttributeTypeArrayList_Sql);
Set_QuotedIdentifierAndANSInull(sw);
sb = new StringBuilder();
sb.Append("\r\n");
sb.Append("\r\n");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
if (this.chkInsertSP.Checked)
{
#region Write Insert SP Script
strSPname = "SP_" + strTable + "_Insert";
SP_Heading(sw, strSPname);
Search_SP_in_DB(sw, strSPname);
Set_QuotedIdentifierAndANSInull(sw);
Insert_StoredProcedure(sw, strSPname, AttributeNameArrayList, AttributeTypeArrayList_Sql);
Set_QuotedIdentifierAndANSInull(sw);
sb = new StringBuilder();
sb.Append("\r\n");
sb.Append("\r\n");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
if (this.chkUpdateSP.Checked)
{
#region Write Update SP Script
strSPname = "SP_" + strTable + "_Update";
SP_Heading(sw, strSPname);
Search_SP_in_DB(sw, strSPname);
Set_QuotedIdentifierAndANSInull(sw);
Update_StoredProcedure(sw, strSPname, AttributeNameArrayList, AttributeTypeArrayList_Sql);
Set_QuotedIdentifierAndANSInull(sw);
sb = new StringBuilder();
sb.Append("\r\n");
sb.Append("\r\n");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
#region Close file
if (sw != null)
{
dr.Close();
sw.Close();
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
}
private void Search_SP_in_DB(StreamWriter sw, string strSPname)
{
try
{
//search SP in database if SP exist then delete it
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + strSPname + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.Append("\r\ndrop procedure [dbo].[" + strSPname + "]");
sb.Append("\r\nGo");
sw.WriteLine(sb.ToString());
}
catch (Exception ex)
{
LogError(ex);
}
}
private void Set_QuotedIdentifierAndANSInull(StreamWriter sw)
{
try
{
//set quote identifier and ANSI null
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nSET QUOTED_IDENTIFIER OFF ");
sb.Append("\r\nGo");
sb.Append("\r\nSET ANSI_NULLS OFF");
sb.Append("\r\nGo");
sw.WriteLine(sb.ToString());
}
catch (Exception ex)
{
LogError(ex);
}
}
private void SP_Heading(StreamWriter sw, string strSPname)
{
try
{
//write SP name
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\n--------- " + strSPname);
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
private void Select_StoredProcedure(StreamWriter sw, string strSPname, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
string strAttributeName = AttributeNameArrayList[0].ToString();
string strAttributeType = AttributeTypeArrayList_Sql[0].ToString();
const string consTemp = @"@";
string temp = consTemp + strAttributeName;
string strColumns = string.Empty;
string strParametersWithDataType = string.Empty;
string strWhereConditions = string.Empty;
for (int i = 0; i < AttributeNameArrayList.Count; i++)
{
strColumns = strColumns + "\r\n" + AttributeNameArrayList[i].ToString() + ",";
}
strColumns = strColumns.Substring(0, strColumns.Length - 1);
//Parameter with datatype
for (int i = 0; i < AttributeNameArrayList.Count; i++)
{
if (AttributeTypeArrayList_Sql[i].ToString().Contains("varchar"))
{
strParametersWithDataType = strParametersWithDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + "(50)" + ",";
}
else
{
strParametersWithDataType = strParametersWithDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + ",";
}
}
//remove "," from string
strParametersWithDataType = strParametersWithDataType.Substring(0, strParametersWithDataType.Length - 1);
//where conditions
for (int i = 0; i < AttributeNameArrayList.Count-1; i++)
{
strWhereConditions = strWhereConditions + "\r\n( " + consTemp + AttributeNameArrayList[i].ToString() + " is null or " + consTemp + AttributeNameArrayList[i].ToString() + " = " + AttributeNameArrayList[i].ToString() + " ) and";
}
#region Select SP
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nCREATE PROCEDURE " + strSPname);
sb.Append("\r\n");
sb.Append("\r\n" + strParametersWithDataType);
sb.Append("\r\n");
sb.Append("\r\nAS");
sb.Append("\r\n");
sb.Append("\r\nSelect ");
sb.Append("\r\n");
sb.Append(strColumns);
sb.Append("\r\n");
sb.Append("\r\nfrom " + strTable);
sb.Append("\r\n");
sb.Append("\r\nwhere " + strWhereConditions + "\r\n @IsActive = IsActive");
sb.Append("\r\n");
sb.Append("\r\nGo");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private void Insert_StoredProcedure(StreamWriter sw, string strSPname, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
const string consTemp = @"@";
string temp = string.Empty;
string strColumns = string.Empty;
string strParametersWithDataType = string.Empty;
string strParametersWithoutDataType = string.Empty;
//Parameters with datatype
for (int i = 1; i < AttributeNameArrayList.Count-1; i++)
{
if (AttributeTypeArrayList_Sql[i].ToString().Contains("varchar"))
{
strParametersWithDataType = strParametersWithDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + "(50)" + ",";
}
else
{
strParametersWithDataType = strParametersWithDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + ",";
}
}
//remove "," from string
strParametersWithDataType = strParametersWithDataType.Substring(0, strParametersWithDataType.Length - 1);
//Parameters without datatype
for (int i = 1; i < AttributeNameArrayList.Count-1; i++)
{
if (AttributeTypeArrayList_Sql[i].ToString().Contains("varchar"))
{
strParametersWithoutDataType = strParametersWithoutDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + ",";
}
else
{
strParametersWithoutDataType = strParametersWithoutDataType + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + ",";
}
}
//concatenate 1 for IsActive
strParametersWithoutDataType = strParametersWithoutDataType + "\r\n1";
for (int i = 1; i < AttributeNameArrayList.Count; i++)
{
strColumns = strColumns + "\r\n" + AttributeNameArrayList[i].ToString() + ",";
}
//remove "," from string
strColumns = strColumns.Substring(0, strColumns.Length - 1);
#region Insert SP
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nCREATE PROCEDURE " + strSPname);
sb.Append("\r\n");
sb.Append("\r\n" + strParametersWithDataType);
sb.Append("\r\n");
sb.Append("\r\nAS");
sb.Append("\r\n");
sb.Append("\r\nInsert into " + strTable);
sb.Append("\r\n(");
sb.Append(strColumns);
sb.Append("\r\n)");
sb.Append("\r\nvalues");
sb.Append("\r\n(");
sb.Append(strParametersWithoutDataType);
sb.Append("\r\n)");
sb.Append("\r\n");
sb.Append("\r\nGo");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private void Update_StoredProcedure(StreamWriter sw, string strSPname, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
const string consTemp = @"@";
string strTemp = string.Empty;
string strColumns = string.Empty;
string strParameters = string.Empty;
for (int i = 0; i < AttributeNameArrayList.Count-1; i++)
{
if (AttributeTypeArrayList_Sql[i].ToString().Contains("varchar"))
{
strParameters = strParameters + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + "(50)" + ",";
}
else
{
strParameters = strParameters + "\r\n" + consTemp + AttributeNameArrayList[i].ToString() + " " + AttributeTypeArrayList_Sql[i].ToString() + ",";
}
}
strParameters = strParameters.Substring(0, strParameters.Length - 1);
for (int i = 1; i < AttributeNameArrayList.Count-1; i++)
{
strTemp = strTemp + "\r\n\t" + AttributeNameArrayList[i].ToString() + " = " + consTemp + AttributeNameArrayList[i].ToString() + ",";
}
//strTemp = strTemp.Substring(0, strTemp.Length - 1);
strTemp = strTemp + "\r\n\t IsActive = 1";
#region Update SP
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nCREATE PROCEDURE " + strSPname);
sb.Append("\r\n");
sb.Append("\r\n" + strParameters);
sb.Append("\r\n");
sb.Append("\r\nAS");
sb.Append("\r\n");
sb.Append("\r\nUpdate " + strTable);
sb.Append("\r\n");
sb.Append("\r\nSet");
sb.Append("\r\n\t" + strTemp);
sb.Append("\r\nWhere");
sb.Append("\r\n");
sb.Append("\r\n" + AttributeNameArrayList[0].ToString() + " = " + consTemp + AttributeNameArrayList[0].ToString());
sb.Append("\r\n");
sb.Append("\r\nGo");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private void SoftDelete_StoredProcedure(StreamWriter sw, string strSPname, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
string strAttributeName = AttributeNameArrayList[0].ToString();
string strAttributeType = AttributeTypeArrayList_Sql[0].ToString();
const string consTemp = @"@";
string temp = consTemp + strAttributeName;
string strColumns = string.Empty;
#region Delete SP
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nCREATE PROCEDURE " + strSPname);
sb.Append("\r\n");
sb.Append("\r\n" + temp + " " + strAttributeType);
sb.Append("\r\n");
sb.Append("\r\nAS");
sb.Append("\r\n");
sb.Append("\r\nUpdate ");
sb.Append("\r\n" + strTable + "\r\n");
sb.Append("\r\nset IsActive = 0");
sb.Append("\r\n");
sb.Append("\r\nwhere " + strAttributeName + "=" + consTemp + strAttributeName);
sb.Append("\r\n");
sb.Append("\r\nGo");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private void PermanentlyDelete_StoredProcedure(StreamWriter sw, string strSPname, ArrayList AttributeNameArrayList, ArrayList AttributeTypeArrayList_Sql)
{
try
{
string strAttributeName = AttributeNameArrayList[0].ToString();
string strAttributeType = AttributeTypeArrayList_Sql[0].ToString();
const string consTemp = @"@";
string temp = consTemp + strAttributeName;
string strColumns = string.Empty;
#region Delete SP
System.Text.StringBuilder sb = new System.Text.StringBuilder("\r\nCREATE PROCEDURE " + strSPname);
sb.Append("\r\n");
sb.Append("\r\n" + temp + " " + strAttributeType);
sb.Append("\r\n");
sb.Append("\r\nAS");
sb.Append("\r\n");
sb.Append("\r\nDelete ");
sb.Append("\r\n");
sb.Append("\r\nfrom " + strTable);
sb.Append("\r\n");
sb.Append("\r\nwhere " + strAttributeName + "=" + temp);
sb.Append("\r\n");
sb.Append("\r\nGo");
sb.Append("\r\n");
sw.WriteLine(sb.ToString());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Region Connection string
private void ClassGenerator_Load(object sender, EventArgs e)
{
try
{
this.txtUserName.Enabled = false;
this.txtPassword.Enabled = false;
this.gboConnectionString.Enabled = true;
//this.gboConnectionStatus.Enabled = false;
//this.gboCreateClass.Enabled = false;
//Remove tab pages
this.tabAutomaticClassGenerator.TabPages.Remove(this.tbClassGenerator);
}
catch (Exception ex)
{
LogError(ex);
}
}
private void rboWindowsSecurity_CheckedChanged(object sender, EventArgs e)
{
try
{
if (this.rboWindowsSecurity.Checked)
{
this.rboUsernamePasswordSecurity.Checked = false;
this.txtUserName.Enabled = false;
this.txtPassword.Enabled = false;
}
}
catch(Exception ex)
{
LogError(ex);
}
}
private void rboUsernamePasswordSecurity_CheckedChanged(object sender, EventArgs e)
{
try
{
if (this.rboUsernamePasswordSecurity.Checked)
{
this.rboWindowsSecurity.Checked = false;
this.txtUserName.Enabled = true;
this.txtPassword.Enabled = true;
}
}
catch (Exception ex)
{
LogError(ex);
}
}
private void cboDatabaseNames_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (this.cboDatabaseNames.SelectedIndex != -1)
{
if (ConnectToDatabase(CreateConnectionStringWithDatabase()))
{
GetAllTableNames();
}
}
else
{
MessageBox.Show("Please select Database");
}
}
catch (Exception ex)
{
LogError(ex);
}
}
#endregion
}
}
No comments:
Post a Comment