/*
* 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
}
}
Submit an article or tip