// create document code for each stored procedure that retrieve from database
private void CreateMethodForExecuteSP(CodeTypeDeclaration ctd, string spName, string connectionString, StoredProcedureParameterCollection parameterCollection)
{
// declaration method
CodeMemberMethod method = new CodeMemberMethod();
method.Attributes = MemberAttributes.Public;
method.ReturnType = new CodeTypeReference(typeof(System.Data.DataTable));
method.Name = spName;
// declare variable with type SqlConnection and assign value of field's property (ConnectionString) to it
CodeVariableDeclarationStatement cvds_con = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlConnection), "con");
CodeAssignStatement assignment_new_con = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_con.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), "ConnectionString")));
// declare variable with type SqlCommand and assign value of cvds_con to SqlCommand.Connection property
CodeVariableDeclarationStatement cvds_cmd = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "cmd");
CodeAssignStatement assignment_new_cmd = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_cmd.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand)));
CodeAssignStatement assignment_cmd_commandText = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "CommandText"), new CodePrimitiveExpression(spName));
CodeAssignStatement assignment_cmd_commandType = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "CommandType"), new CodeSnippetExpression("CommandType.StoredProcedure"));
// Assign con object to cmd.Connection property
CodeAssignStatement assign_cmd_con = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con.Name));
// decalre a SqlDataReader object to hold data
CodeVariableDeclarationStatement cvds_dr = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");
// declare a DataTable object to load data from SqlDataReader object
CodeVariableDeclarationStatement cvds_dt = new CodeVariableDeclarationStatement(typeof(System.Data.DataTable), "dt");
CodeAssignStatement assignment_new_dt = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dt.Name), new CodeObjectCreateExpression(typeof(System.Data.DataTable)));
// open the SqlConnection object
CodeMethodInvokeExpression invoke_con_open = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con.Name), "Open");
// run ExecuteReader method and pass resultset to SqlDataReader object
CodeMethodInvokeExpression invoke_execute_reader = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "ExecuteReader");
CodeAssignStatement assignment_dr_cmdExecute = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dr.Name), invoke_execute_reader);
// load data from SqlDataReader to DataTable object
CodeMethodInvokeExpression dt_load = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_dt.Name), "Load", new CodeVariableReferenceExpression(cvds_dr.Name));
// close SqlConnection object
CodeMethodInvokeExpression invoke_con_close = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con.Name), "Close");
// return DataTable object
CodeMethodReturnStatement return_method = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(cvds_dt.Name));
// insert our method statements into TryCatch clause
CodeTryCatchFinallyStatement tryCatch_method = new CodeTryCatchFinallyStatement();
tryCatch_method.TryStatements.Add(cvds_con);
tryCatch_method.TryStatements.Add(assignment_new_con);
tryCatch_method.TryStatements.Add(cvds_cmd);
tryCatch_method.TryStatements.Add(assignment_new_cmd);
tryCatch_method.TryStatements.Add(assignment_cmd_commandText);
tryCatch_method.TryStatements.Add(assignment_cmd_commandType);
tryCatch_method.TryStatements.Add(assign_cmd_con);
if (parameterCollection.Count > 0)
{
foreach (StoredProcedureParameter parameter in parameterCollection)
{
if (parameter.ParameterName != "NULL")
{
CodeParameterDeclarationExpression method_parameter = new CodeParameterDeclarationExpression(this.GetParameterType(parameter), parameter.ParameterName.Replace("@", ""));
method.Parameters.Add(method_parameter);
CodeVariableDeclarationStatement commandParameter = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlParameter), "_" + parameter.ParameterName.Replace("@", ""), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlParameter)));
CodeAssignStatement assign_parameter_name = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(commandParameter.Name), "ParameterName"), new CodePrimitiveExpression(parameter.ParameterName));
CodeAssignStatement assign_parameter_size = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(commandParameter.Name), "Size"), new CodeSnippetExpression(parameter.ParameterLength.ToString()));
CodeAssignStatement assign_parameter_value = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(commandParameter.Name), "Value"), new CodeArgumentReferenceExpression(method_parameter.Name));
CodeMethodInvokeExpression add_parameter = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "Parameters.Add", new CodeVariableReferenceExpression(commandParameter.Name));
tryCatch_method.TryStatements.Add(commandParameter);
tryCatch_method.TryStatements.Add(assign_parameter_name);
tryCatch_method.TryStatements.Add(assign_parameter_size);
tryCatch_method.TryStatements.Add(assign_parameter_value);
tryCatch_method.TryStatements.Add(add_parameter);
if (parameter.IsParameterOutput)
{
CodeAssignStatement assign_parameter_direction = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(commandParameter.Name), "Direction"), new CodeSnippetExpression("ParameterDirection.Output"));
tryCatch_method.TryStatements.Add(assign_parameter_direction);
}
}
}
}
tryCatch_method.TryStatements.Add(cvds_dr);
tryCatch_method.TryStatements.Add(cvds_dt);
tryCatch_method.TryStatements.Add(assignment_new_dt);
tryCatch_method.TryStatements.Add(invoke_con_open);
tryCatch_method.TryStatements.Add(assignment_dr_cmdExecute);
tryCatch_method.TryStatements.Add(dt_load);
tryCatch_method.TryStatements.Add(invoke_con_close);
tryCatch_method.TryStatements.Add(return_method);
// catch 1 for sql errors (Note : this catch clause must define first)
CodeCatchClause catchSql = new CodeCatchClause("ex", new CodeTypeReference("System.Data.SqlClient.SqlException"));
CodeMethodInvokeExpression error_showSql = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageSql = new CodeSnippetExpression("ex.Message");
CodeVariableDeclarationStatement titleSqlVar1 = new CodeVariableDeclarationStatement(typeof(string), "errorTitle", new CodePrimitiveExpression("Error "));
CodeVariableDeclarationStatement titleSqlVar2 = new CodeVariableDeclarationStatement(typeof(string), "numError", new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(catchSql.LocalName), "Number.ToString"));
CodeAssignStatement error_titleSqlAssignment = new CodeAssignStatement(new CodeVariableReferenceExpression(titleSqlVar1.Name), new CodeSnippetExpression(titleSqlVar1.Name + " + " + titleSqlVar2.Name));
error_showSql.Parameters.Add(error_messageSql);
error_showSql.Parameters.Add(new CodeVariableReferenceExpression(titleSqlVar1.Name));
catchSql.Statements.Add(titleSqlVar1);
catchSql.Statements.Add(titleSqlVar2);
catchSql.Statements.Add(error_titleSqlAssignment);
catchSql.Statements.Add(error_showSql);
catchSql.Statements.Add(new CodeSnippetExpression("return null"));
tryCatch_method.CatchClauses.Add(catchSql);
// catch 2 for general errors
CodeCatchClause catchGeneral = new CodeCatchClause("ex", new CodeTypeReference("System.Exception"));
CodeMethodInvokeExpression error_showGeneral = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageGeneral = new CodeSnippetExpression("ex.Message");
CodePrimitiveExpression error_titleGeneral = new CodePrimitiveExpression("Error!");
error_showGeneral.Parameters.Add(error_messageGeneral);
error_showGeneral.Parameters.Add(error_titleGeneral);
catchGeneral.Statements.Add(error_showGeneral);
catchGeneral.Statements.Add(new CodeSnippetExpression("return null"));
tryCatch_method.CatchClauses.Add(catchGeneral);
// add try-catch to our method
method.Statements.Add(tryCatch_method);
// add method to the class
ctd.Members.Add(method);
}
// initialize document to generate code for stored procedures and create DAL (Data Access Layer)
private void GenerateDocumentForSps(string NameSpace, string Class, Providers languageProvider)
{
this.PrintToLog(string.Format("Generating some initialized code and methods ..."));
System.Windows.Forms.Application.DoEvents();
CodeCompileUnit compileUnit = new CodeCompileUnit();
CodeNamespace ns = new CodeNamespace(NameSpace);
ns.Imports.Add(new CodeNamespaceImport("System"));
ns.Imports.Add(new CodeNamespaceImport("System.Text"));
ns.Imports.Add(new CodeNamespaceImport("System.Data"));
ns.Imports.Add(new CodeNamespaceImport("System.Data.SqlClient"));
// create our class
CodeTypeDeclaration ctd = new CodeTypeDeclaration(Class);
ctd.IsClass = true;
ns.Types.Add(ctd);
compileUnit.Namespaces.Add(ns);
// create private field for our class (connectionString)
string strConnectionString = this.ToLowerFirstChar("connectionString");
CodeMemberField class_field_connectionString = new CodeMemberField(typeof(string), strConnectionString);
class_field_connectionString.Attributes = MemberAttributes.Private;
ctd.Members.Add(class_field_connectionString);
// encapsulate our field(connectionString)
CodeMemberProperty propertyCon = new CodeMemberProperty();
propertyCon.Name = this.ToUpperFirstChar(strConnectionString);
propertyCon.Type = new CodeTypeReference(typeof(string));
propertyCon.Attributes = MemberAttributes.Public;
propertyCon.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString)));
propertyCon.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString), new CodePropertySetValueReferenceExpression()));
ctd.Members.Add(propertyCon);
#region Create 3 constructor
// create constructor for our class
CodeConstructor constructor1 = new CodeConstructor();
constructor1.Attributes = MemberAttributes.Public;
ctd.Members.Add(constructor1);
// add parameter to our constructor mothod
CodeParameterDeclarationExpression constructor1_connectionString = new CodeParameterDeclarationExpression(class_field_connectionString.Type, strConnectionString);
constructor1.Parameters.Add(constructor1_connectionString);
// in body of constructor, assign value of parameter to field's property of class (ConnectionString)
CodeAssignStatement fieldAssignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString), new CodeArgumentReferenceExpression(constructor1_connectionString.Name));
constructor1.Statements.Add(fieldAssignment);
// create constructor2 for our class with 2 args
CodeConstructor constructor2 = new CodeConstructor();
constructor2.Attributes = MemberAttributes.Public;
CodeParameterDeclarationExpression constructor2_server = new CodeParameterDeclarationExpression(typeof(string), "server");
CodeParameterDeclarationExpression constructor2_database = new CodeParameterDeclarationExpression(typeof(string), "database");
constructor2.Parameters.Add(constructor2_server);
constructor2.Parameters.Add(constructor2_database);
CodeAssignStatement constructor2_assignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), class_field_connectionString.Name), new CodeSnippetExpression("string.Format(\"Server={0};Database={1};Trusted_Connection=True;\", server, database)"));
constructor2.Statements.Add(constructor2_assignment);
ctd.Members.Add(constructor2);
// create constructor3 for our class with 4 args
CodeConstructor constructor3 = new CodeConstructor();
constructor3.Attributes = MemberAttributes.Public;
CodeParameterDeclarationExpression constructor3_server = new CodeParameterDeclarationExpression(typeof(string), "server");
CodeParameterDeclarationExpression constructor3_database = new CodeParameterDeclarationExpression(typeof(string), "database");
CodeParameterDeclarationExpression constructor3_user = new CodeParameterDeclarationExpression(typeof(string), "user");
CodeParameterDeclarationExpression constructor3_pass = new CodeParameterDeclarationExpression(typeof(string), "pass");
constructor3.Parameters.Add(constructor3_server);
constructor3.Parameters.Add(constructor3_database);
constructor3.Parameters.Add(constructor3_user);
constructor3.Parameters.Add(constructor3_pass);
CodeAssignStatement constructor3_assignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), class_field_connectionString.Name), new CodeSnippetExpression("string.Format(\"Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False\", server, database,user,pass)"));
constructor3.Statements.Add(constructor3_assignment);
ctd.Members.Add(constructor3);
#endregion
#region Create some useful methods (GetData(SqlCommand cmd))
// create method named GetData()
CodeMemberMethod method_getData = new CodeMemberMethod();
method_getData.Attributes = MemberAttributes.Public;
method_getData.Name = "GetData";
method_getData.ReturnType = new CodeTypeReference(typeof(DataTable));
ctd.Members.Add(method_getData);
// add a SqlCommand object as parameter in 'method_getData' method
CodeParameterDeclarationExpression method_getData_p1 = new CodeParameterDeclarationExpression(typeof(System.Data.SqlClient.SqlCommand), "cmd");
method_getData.Parameters.Add(method_getData_p1);
// declare variable with type SqlConnection and assign value of field's property (ConnectionString) to it
CodeVariableDeclarationStatement cvds_con_getData = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlConnection), "con");
method_getData.Statements.Add(cvds_con_getData);
CodeAssignStatement assignment_newConnection_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_con_getData.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), propertyCon.Name)));
method_getData.Statements.Add(assignment_newConnection_getData);
// Assign con object to cmd.Connection property
CodeAssignStatement assign_cmd_con_getData = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(method_getData_p1.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con_getData.Name));
method_getData.Statements.Add(assign_cmd_con_getData);
// decalre a SqlDataReader object to hold data
CodeVariableDeclarationStatement cvds_dr_getData = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");
// declare a DataTable object to load data from SqlDataReader object
CodeVariableDeclarationStatement cvds_dt_getData = new CodeVariableDeclarationStatement(typeof(System.Data.DataTable), "dt");
CodeAssignStatement assignment_new_dt_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dt_getData.Name), new CodeObjectCreateExpression(typeof(System.Data.DataTable)));
// open the SqlConnection object
CodeMethodInvokeExpression invoke_con_open_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_getData.Name), "Open");
// run ExecuteReader method and pass resultset to SqlDataReader object
CodeMethodInvokeExpression invoke_execute_reader_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(method_getData_p1.Name), "ExecuteReader");
CodeAssignStatement assignment_execReaderToReader_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dr_getData.Name), invoke_execute_reader_getData);
CodeMethodInvokeExpression invoke_dt_load_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_dt_getData.Name), "Load", new CodeVariableReferenceExpression(cvds_dr_getData.Name));
// close SqlConnection object
CodeMethodInvokeExpression invoke_con_close_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_getData.Name), "Close");
// return DataTable object
CodeMethodReturnStatement return_method_getData = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(cvds_dt_getData.Name));
CodeTryCatchFinallyStatement tryCatch_method_getData = new CodeTryCatchFinallyStatement();
tryCatch_method_getData.TryStatements.Add(invoke_con_open_getData);
tryCatch_method_getData.TryStatements.Add(cvds_dr_getData);
tryCatch_method_getData.TryStatements.Add(cvds_dt_getData);
tryCatch_method_getData.TryStatements.Add(assignment_new_dt_getData);
tryCatch_method_getData.TryStatements.Add(assignment_execReaderToReader_getData);
tryCatch_method_getData.TryStatements.Add(invoke_dt_load_getData);
tryCatch_method_getData.TryStatements.Add(invoke_con_close_getData);
tryCatch_method_getData.TryStatements.Add(return_method_getData);
// catch 1 for sql errors (Note : this catch clause must define first)
CodeCatchClause catchSql_getData = new CodeCatchClause("ex", new CodeTypeReference("System.Data.SqlClient.SqlException"));
CodeMethodInvokeExpression error_showSql_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageSql_getData = new CodeSnippetExpression("ex.Message");
CodeVariableDeclarationStatement titleSqlVar1_getData = new CodeVariableDeclarationStatement(typeof(string), "errorTitle", new CodePrimitiveExpression("Error "));
CodeVariableDeclarationStatement titleSqlVar2_getData = new CodeVariableDeclarationStatement(typeof(string), "numError", new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(catchSql_getData.LocalName), "Number.ToString"));
CodeAssignStatement error_titleSqlAssignment_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(titleSqlVar1_getData.Name), new CodeSnippetExpression(titleSqlVar1_getData.Name + " + " + titleSqlVar2_getData.Name));
error_showSql_getData.Parameters.Add(error_messageSql_getData);
error_showSql_getData.Parameters.Add(new CodeVariableReferenceExpression(titleSqlVar1_getData.Name));
catchSql_getData.Statements.Add(titleSqlVar1_getData);
catchSql_getData.Statements.Add(titleSqlVar2_getData);
catchSql_getData.Statements.Add(error_titleSqlAssignment_getData);
catchSql_getData.Statements.Add(error_showSql_getData);
catchSql_getData.Statements.Add(new CodeSnippetExpression("return null"));
tryCatch_method_getData.CatchClauses.Add(catchSql_getData);
// catch 2 for general errors
CodeCatchClause catchGeneral_getData = new CodeCatchClause("ex", new CodeTypeReference("System.Exception"));
CodeMethodInvokeExpression error_showGeneral_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageGeneral_getData = new CodeSnippetExpression("ex.Message");
CodePrimitiveExpression error_titleGeneral_getData = new CodePrimitiveExpression("Error!");
error_showGeneral_getData.Parameters.Add(error_messageGeneral_getData);
error_showGeneral_getData.Parameters.Add(error_titleGeneral_getData);
catchGeneral_getData.Statements.Add(error_showGeneral_getData);
catchGeneral_getData.Statements.Add(new CodeSnippetExpression("return null"));
tryCatch_method_getData.CatchClauses.Add(catchGeneral_getData);
method_getData.Statements.Add(tryCatch_method_getData);
#endregion
#region Create some useful methods (ExecNonQuery(SqlCommand cmd))
// create method named ExecNonQuery
CodeMemberMethod method_execNonQuery = new CodeMemberMethod();
method_execNonQuery.Attributes = MemberAttributes.Public;
method_execNonQuery.Name = "ExecNonQuery";
method_execNonQuery.ReturnType = new CodeTypeReference(typeof(int));
ctd.Members.Add(method_execNonQuery);
// add a SqlCommand object as parameter in 'method_execNonQuery' method
CodeParameterDeclarationExpression method_execNonQuery_p1 = new CodeParameterDeclarationExpression(typeof(System.Data.SqlClient.SqlCommand), "cmd");
method_execNonQuery.Parameters.Add(method_execNonQuery_p1);
// declare variable with type SqlConnection and assign value of field's property (ConnectionString) to it
CodeVariableDeclarationStatement cvds_con_execNonQuery = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlConnection), "con");
method_execNonQuery.Statements.Add(cvds_con_execNonQuery);
CodeAssignStatement assignment_execNonQuery = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_con_execNonQuery.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), propertyCon.Name)));
method_execNonQuery.Statements.Add(assignment_execNonQuery);
// Assign con object to cmd.Connection property
CodeAssignStatement assign_cmd_con_execNonQuery = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(method_execNonQuery_p1.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con_execNonQuery.Name));
method_execNonQuery.Statements.Add(assign_cmd_con_execNonQuery);
// open the SqlConnection object
CodeMethodInvokeExpression invoke_con_open_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_execNonQuery.Name), "Open");
// run ExecuteNoneQuery method and pass resultset to our int variable
CodeVariableDeclarationStatement output_execNonQuery = new CodeVariableDeclarationStatement(typeof(int), "output");
CodeMethodInvokeExpression invoke_executeNoneQuery_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(method_execNonQuery_p1.Name), "ExecuteNonQuery");
CodeAssignStatement assignment_noneQueryToOutput_execNonQuery = new CodeAssignStatement(new CodeVariableReferenceExpression(output_execNonQuery.Name), invoke_executeNoneQuery_execNonQuery);
// close SqlConnection object
CodeMethodInvokeExpression invoke_con_close_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_execNonQuery.Name), "Close");
// return DataTable object
CodeMethodReturnStatement return_method_execNonQuery = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(output_execNonQuery.Name));
CodeTryCatchFinallyStatement tryCatch_method_execNonQuery = new CodeTryCatchFinallyStatement();
tryCatch_method_execNonQuery.TryStatements.Add(output_execNonQuery);
tryCatch_method_execNonQuery.TryStatements.Add(invoke_con_open_execNonQuery);
tryCatch_method_execNonQuery.TryStatements.Add(assignment_noneQueryToOutput_execNonQuery);
tryCatch_method_execNonQuery.TryStatements.Add(invoke_con_close_execNonQuery);
tryCatch_method_execNonQuery.TryStatements.Add(return_method_execNonQuery);
// catch 1 for sql errors (Note : this catch clause must define first)
CodeCatchClause catchSql_execNonQuery = new CodeCatchClause("ex", new CodeTypeReference("System.Data.SqlClient.SqlException"));
CodeMethodInvokeExpression error_showSql_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageSql_execNonQuery = new CodeSnippetExpression("ex.Message");
CodeVariableDeclarationStatement titleSqlVar1_execNonQuery = new CodeVariableDeclarationStatement(typeof(string), "errorTitle", new CodePrimitiveExpression("Error "));
CodeVariableDeclarationStatement titleSqlVar2_execNonQuery = new CodeVariableDeclarationStatement(typeof(string), "numError", new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(catchSql_execNonQuery.LocalName), "Number.ToString"));
CodeAssignStatement error_titleSqlAssignment_execNonQuery = new CodeAssignStatement(new CodeVariableReferenceExpression(titleSqlVar1_execNonQuery.Name), new CodeSnippetExpression(titleSqlVar1_execNonQuery.Name + " + " + titleSqlVar2_execNonQuery.Name));
error_showSql_execNonQuery.Parameters.Add(error_messageSql_execNonQuery);
error_showSql_execNonQuery.Parameters.Add(new CodeVariableReferenceExpression(titleSqlVar1_execNonQuery.Name));
catchSql_execNonQuery.Statements.Add(titleSqlVar1_execNonQuery);
catchSql_execNonQuery.Statements.Add(titleSqlVar2_execNonQuery);
catchSql_execNonQuery.Statements.Add(error_titleSqlAssignment_execNonQuery);
catchSql_execNonQuery.Statements.Add(error_showSql_execNonQuery);
catchSql_execNonQuery.Statements.Add(new CodeSnippetExpression("return -1"));
tryCatch_method_execNonQuery.CatchClauses.Add(catchSql_execNonQuery);
// catch 2 for general errors
CodeCatchClause catchGeneral_execNonQuery = new CodeCatchClause("ex", new CodeTypeReference("System.Exception"));
CodeMethodInvokeExpression error_showGeneral_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
CodeSnippetExpression error_messageGeneral_execNonQuery = new CodeSnippetExpression("ex.Message");
CodePrimitiveExpression error_titleGeneral_execNonQuery = new CodePrimitiveExpression("Error!");
error_showGeneral_execNonQuery.Parameters.Add(error_messageGeneral_execNonQuery);
error_showGeneral_execNonQuery.Parameters.Add(error_titleGeneral_execNonQuery);
catchGeneral_execNonQuery.Statements.Add(error_showGeneral_execNonQuery);
catchGeneral_execNonQuery.Statements.Add(new CodeSnippetExpression("return -1"));
tryCatch_method_execNonQuery.CatchClauses.Add(catchGeneral_execNonQuery);
method_execNonQuery.Statements.Add(tryCatch_method_execNonQuery);
#endregion
#region Retrieve storedProcedure(s) and create method for it
// create new DataTable object (dtHelper) to import unique records from dtSPs
// because in dtSPs, each stored Procedure + parameter has a single record, therefore, each sp that has a multiple parameters
// cause multiple rows insert into dtSPs object
DataTable dtHelper = this.dtSPs.Clone();
dtHelper.PrimaryKey = new DataColumn[] { dtHelper.Columns[0] };
foreach (DataRow row in this.dtSPs.Rows)
{
if (!dtHelper.Rows.Contains(row["SpName"]))
{
dtHelper.ImportRow(row);
DataRow[] rowCol = this.dtSPs.Select("SpName = '" + row["SpName"].ToString() + "'");
StoredProcedureParameterCollection parameterCollection = new StoredProcedureParameterCollection();
foreach (DataRow rowParameter in rowCol)
{
StoredProcedureParameter parameter = new StoredProcedureParameter();
parameter.ParameterName = rowParameter["ParameterName"].ToString();
parameter.ParameterType = rowParameter["SystemType"].ToString();
parameter.ParameterLength = rowParameter["Length"].ToString() != "NULL" ? int.Parse(rowParameter["Length"].ToString()) : -1;
if (rowParameter["IsOutputParameter"].ToString() != "NULL")
{
if (rowParameter["IsOutputParameter"].ToString() == "1")
parameter.IsParameterOutput = true;
else
parameter.IsParameterOutput = false;
}
else
{
parameter.IsParameterOutput = false;
}
parameterCollection.Add(parameter);
}
this.PrintToLog(string.Format("Generating method for {0} ...", row["SpName"]));
System.Windows.Forms.Application.DoEvents();
// create method for each storedProcedure in this loop and pass parameters to this method as StoredProcedureParameterCollection object
this.CreateMethodForExecuteSP(ctd, row["SpName"].ToString(), strConnectionString, parameterCollection);
}
}
#endregion
// define language provider and generate code
CodeDomProvider provider;
if (languageProvider == Providers.CsProvider)
{
// output is in csharp
provider = new CSharpCodeProvider();
}
else
{
// output is in viusal basic
provider = new VBCodeProvider();
}
// finally, generate our code to specified codeProvider
this.GenerateCode(provider, compileUnit, this.txtSaveTo.Text + "\\" + Class);
this.PrintToLog(string.Format("Code generation successfully completed for {0} Stored Procedure(s) in Database : {1}", dtHelper.Rows.Count, NameSpace));
}
// load databases,tables and sps into treeView
private void GetServerObjects()
{
if (this.InvokeRequired)
{
this.BeginInvoke(new MethodInvoker(this.GetServerObjects), null);
}
else
{
this.treeView1.Nodes.Clear();
SQLServerClass m_sqlServer = new SQLServerClass();
string serverName = this.cmbInstances.Text;
string userName = string.Empty;
string pass = string.Empty;
if (!this.checkTrustConnection.Checked)
{
userName = this.txtUser.Text;
pass = this.txtPass.Text;
m_sqlServer.Connect(serverName, userName, pass);
}
else
{
m_sqlServer.LoginSecure = true;
m_sqlServer.Connect(serverName, "", "");
}
TreeNode database;
TreeNode procedures;
TreeNode storedProcedure;
TreeNode storedProcedureParameter;
TreeNode tables;
TreeNode table;
TreeNode column;
#region Iterate through all databases to extract sps and tables
foreach (Database2 db in m_sqlServer.Databases)
{
if (!db.SystemObject)
{
database = new TreeNode(db.Name);
database.ImageIndex = 0;
procedures = new TreeNode("Procedures");
procedures.ImageIndex = 8;
procedures.SelectedImageIndex = 8;
#region Get All stored procedures for each database
// Get all procedures for each database
foreach (StoredProcedure2 sp in db.StoredProcedures)
{
if (!sp.SystemObject && sp.Name != "sp_alterdiagram" && sp.Name != "sp_creatediagram" && sp.Name != "sp_dropdiagram" && sp.Name != "sp_helpdiagrams" && sp.Name != "sp_renamediagram" && sp.Name != "sp_upgraddiagrams" && sp.Name != "sp_helpdiagramdefinition")
{
storedProcedure = new TreeNode(sp.Name);
storedProcedure.ImageIndex = 10;
storedProcedure.SelectedImageIndex = 10;
QueryResults res = sp.EnumParameters();
StoredProcedureParameter spParameter;
StoredProcedureParameterCollection spParameterCollection = new StoredProcedureParameterCollection();
int row, col;
for (row = 1; row <= res.Rows; row++)
{
for (col = 1; col <= res.Columns; col++)
{
string spParamName = res.GetColumnString(row, col);
string spParamType = res.GetColumnString(row, col + 1);
string spParamLength = res.GetColumnString(row, col + 2);
string spIsOutput = res.GetColumnString(row, col + 4) != "0" ? ",OutPut" : "";
bool checkBooleanExpression = res.GetColumnString(row, col + 4) != "0" ? true : false;
storedProcedureParameter = new TreeNode(string.Format("{0},{1}({2}){3}", spParamName, spParamType, spParamLength, spIsOutput));
storedProcedureParameter.ImageIndex = 9;
storedProcedureParameter.SelectedImageIndex = 11;
spParameter = new StoredProcedureParameter(spParamName, spParamType, int.Parse(spParamLength), checkBooleanExpression);
spParameterCollection.Add(spParameter);
storedProcedure.Nodes.Add(storedProcedureParameter);
break;
}
}
procedures.Nodes.Add(storedProcedure);
}
}
database.Nodes.Add(procedures);
#endregion
#region Get all tables for each database
// Get all tables for each database
tables = new TreeNode("Tables");
tables.ImageIndex = 1;
tables.SelectedImageIndex = 1;
foreach (Table2 tbl in db.Tables)
{
if (!tbl.SystemObject && tbl.Name != "sysdiagrams")
{
table = new TreeNode(tbl.Name);
table.ImageIndex = 1;
table.SelectedImageIndex = 1;
foreach (Column2 col in tbl.Columns)
{
column = new TreeNode(string.Format("{0},{1}", col.Name, col.Datatype));
column.ImageIndex = 6;
column.SelectedImageIndex = 6;
table.Nodes.Add(column);
}
tables.Nodes.Add(table);
}
}
database.Nodes.Add(tables);
#endregion
// Add database to treeView1.Nodes
this.treeView1.Nodes.Add(database);
}
}
#endregion
}
}
