void Insert_GenerateSQLQuery(string DBType, string TableName, string SPName,Boolean refreshGrid)
{
try
{
if (this.isQueryExist(TableName, SPName) == true)
{
return;
}
string HeaderComments = "-- ======================================================" + Environment.NewLine +
"-- Name: " + SPName + Environment.NewLine +
"-- Created By: " + this.ToolName + Environment.NewLine +
"-- Author: " + this.Author + Environment.NewLine +
"-- Created At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
"-- Updated At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
"-- Description : Insert" + Environment.NewLine +
"-- ======================================================";
string QueryContent = "";
DataRowView myRows;
int ColumnsNo = 0;
string inputerParameters = "";
string ColumnsName = "";
string ColumnsValues = "";
string OutputQuery = "";
Boolean isIdentityInserted = false;
SolutionsDBQueryColumnsDAL SolutionsDBQueryColumnsDAL = new SolutionsDBQueryColumnsDAL();
SolutionsDBQueryColumnsDAL.QueryId = 0;
SolutionsDBQueryColumnsDAL.SolutionsDBID = this.CurrentDBID;
SolutionsDBQueryColumnsDAL.QueryName = SPName;
SolutionsDBQueryColumnsDAL.TableId=0;
SolutionsDBQueryColumnsDAL.TableName = TableName;
SolutionsDBQueryColumnsDAL.isActive = true;
if (DBType.ToLower() == "my sql")
{
#region "my sql"
foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
{
DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];
if (myCell.Value.ToString() == "1")
{
myRows = (DataRowView)myRow.DataBoundItem;
if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
{
if (ColumnsNo == 0)
{
inputerParameters = " in " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString(); ;
ColumnsName = myRows["ColumnName"].ToString();
ColumnsValues = myRows["ColumnName"].ToString();
}
else
{
inputerParameters = inputerParameters + " , " + Environment.NewLine + " in " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
if (ColumnsNo % 5 == 0)
{
ColumnsName = ColumnsName + "," + Environment.NewLine + " " + myRows["ColumnName"].ToString();
ColumnsValues = ColumnsValues + "," + Environment.NewLine + " " + myRows["ColumnName"].ToString();
}
else
{
if (isIdentityInserted == false)
{
isIdentityInserted = true;
ColumnsName = myRows["ColumnName"].ToString();
ColumnsValues = myRows["ColumnName"].ToString();
}
else
{
ColumnsName = ColumnsName + " , " + myRows["ColumnName"].ToString();
ColumnsValues = ColumnsValues + " , " + myRows["ColumnName"].ToString();
}
}
}
}
else
{
if (ColumnsNo == 0)
{
inputerParameters = " out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
OutputQuery = " Set " + myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
}
else
{
inputerParameters = inputerParameters + " , " + Environment.NewLine + " out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
OutputQuery = " Set " + myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
}
}
ColumnsNo++;
SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
SolutionsDBQueryColumnsDAL.Insert();
}
}
QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
" DELIMITER $$ " + Environment.NewLine +
" Create procedure " + SPName + " (" + Environment.NewLine +
inputerParameters + Environment.NewLine +
") " + Environment.NewLine +
" Begin " + Environment.NewLine +
" Insert into " + TableName + Environment.NewLine +
" ( " + Environment.NewLine + " " + ColumnsName + Environment.NewLine + " )" + Environment.NewLine +
" Values " + Environment.NewLine +
" ( " + Environment.NewLine + " " + ColumnsValues + Environment.NewLine + " ); " + Environment.NewLine +
" " + OutputQuery + "" + Environment.NewLine +
" END$$ " + Environment.NewLine +
" DELIMITER ;";
#endregion
}
else if (DBType.ToLower() == "ms sql")
{
#region "Ms sql"
foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
{
DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];
if (myCell.Value.ToString() == "1")
{
myRows = (DataRowView)myRow.DataBoundItem;
if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
{
if (ColumnsNo == 0)
{
inputerParameters = " @" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();
ColumnsName = myRows["ColumnName"].ToString();
ColumnsValues = "@" + myRows["ColumnName"].ToString();
}
else
{
inputerParameters = inputerParameters + " , " + Environment.NewLine + " @" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
if (ColumnsNo % 5 == 0)
{
ColumnsName = ColumnsName + "," + Environment.NewLine + " " + myRows["ColumnName"].ToString();
ColumnsValues = ColumnsValues + "," + Environment.NewLine + " " + "@" + myRows["ColumnName"].ToString();
}
else
{
if (isIdentityInserted == false)
{
isIdentityInserted = true;
ColumnsName = myRows["ColumnName"].ToString();
ColumnsValues = "@" + myRows["ColumnName"].ToString();
}
else
{
ColumnsName = ColumnsName + " , " + myRows["ColumnName"].ToString();
ColumnsValues = ColumnsValues + " , " + "@" + myRows["ColumnName"].ToString();
}
}
}
ColumnsNo++;
}
else
{
if (ColumnsNo == 0)
{
//inputerParameters = " out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
OutputQuery = " Select @@Identity;";
}
else
{
//inputerParameters = inputerParameters + " , " + " out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
OutputQuery = " Select @@Identity;";
}
}
SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
SolutionsDBQueryColumnsDAL.Insert();
}
}
//HeaderComments = " SET ANSI_NULLS ON " + Environment.NewLine +
// " GO " + Environment.NewLine +
// " SET QUOTED_IDENTIFIER ON " + Environment.NewLine +
// " GO" + Environment.NewLine + Environment.NewLine +
// HeaderComments;
QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
" Create procedure " + SPName + " (" + Environment.NewLine +
inputerParameters + Environment.NewLine +
") " + Environment.NewLine +
"AS " + Environment.NewLine +
" Begin " + Environment.NewLine + Environment.NewLine + Environment.NewLine +
" SET NOCOUNT ON; " + Environment.NewLine +
" Insert into " + TableName + Environment.NewLine +
" ( " + Environment.NewLine + " " + ColumnsName + Environment.NewLine + " )" + Environment.NewLine +
" Values " + Environment.NewLine +
" ( " + Environment.NewLine + " " + ColumnsValues + Environment.NewLine + " ); " + Environment.NewLine +
" " + OutputQuery + "" + Environment.NewLine +
" END " + Environment.NewLine;
#endregion
}
this.Insert_SolutionsDBQuery(TableName, SPName, "Insert", "Store Procedure", QueryContent,refreshGrid);
}
catch (Exception ex)
{
CommonClasses.Messages.GeneralError(ex.Message, " Insert Query");
}
}
void GetList_GenerateSQLQuery(string DBType, string TableName, string SPName, Boolean refreshGrid)
{
try
{
if (this.isQueryExist(TableName, SPName) == true)
{
return;
}
string HeaderComments = "/*" + Environment.NewLine +
"Name: " + SPName + Environment.NewLine +
"Created By: " + this.ToolName + Environment.NewLine +
"Author: " + this.Author + Environment.NewLine +
"Created At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
"Updated At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
"Description : Get List" + Environment.NewLine +
"*/";
string QueryContent = "";
DataRowView myRows;
int ColumnsNo = 0;
string inputerParameters = "";
string SelectedColumns = "";
string OutputQuery = "";
string myWhereCondition = "";
Boolean isIdentityInserted = false;
SolutionsDBQueryColumnsDAL SolutionsDBQueryColumnsDAL = new SolutionsDBQueryColumnsDAL();
SolutionsDBQueryColumnsDAL.QueryId = 0;
SolutionsDBQueryColumnsDAL.SolutionsDBID = this.CurrentDBID;
SolutionsDBQueryColumnsDAL.QueryName = SPName;
SolutionsDBQueryColumnsDAL.TableId = 0;
SolutionsDBQueryColumnsDAL.TableName = TableName;
SolutionsDBQueryColumnsDAL.isActive = true;
if (DBType.ToLower() == "my sql")
{
#region "My Sql"
foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
{
DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];
if (myCell.Value.ToString() == "1")
{
myRows = (DataRowView)myRow.DataBoundItem;
if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
{
if (ColumnsNo == 0)
{
SelectedColumns = myRows["ColumnName"].ToString();
}
else
{
if (ColumnsNo % 5 == 0)
{
SelectedColumns = SelectedColumns + "," + Environment.NewLine + " " + myRows["ColumnName"].ToString();
}
else
{
if (isIdentityInserted == false)
{
isIdentityInserted = true;
SelectedColumns = myRows["ColumnName"].ToString(); ;
}
else
{
SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString(); ;
}
}
}
}
else
{
isIdentityInserted = true;
if (ColumnsNo == 0)
{
SelectedColumns = myRows["ColumnName"].ToString();
inputerParameters = " in org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
OutputQuery = " select 1 as ReturnValue; ";// +myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
myWhereCondition = "(org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = org_" + myRows["ColumnName"].ToString() + ")";
}
else
{
SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString();
inputerParameters = inputerParameters + " , " + " in org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
myWhereCondition = "(org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = org_" + myRows["ColumnName"].ToString() + ")";
}
}
ColumnsNo++;
SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
SolutionsDBQueryColumnsDAL.Insert();
}
}
QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
" DELIMITER $$ " + Environment.NewLine +
" Create procedure " + SPName + " (" + Environment.NewLine +
inputerParameters + Environment.NewLine +
") " + Environment.NewLine +
" Begin " + Environment.NewLine +
" Select " + SelectedColumns + Environment.NewLine +
" From " + TableName + " " + Environment.NewLine +
" Where " + myWhereCondition + " ;" + Environment.NewLine +
" END$$ " + Environment.NewLine +
" DELIMITER ;";
#endregion
}
else if (DBType.ToLower() == "ms sql")
{
#region "Ms Sql"
foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
{
DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];
if (myCell.Value.ToString() == "1")
{
myRows = (DataRowView)myRow.DataBoundItem;
if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
{
if (ColumnsNo == 0)
{
SelectedColumns = myRows["ColumnName"].ToString();
}
else
{
if (ColumnsNo % 5 == 0)
{
SelectedColumns = SelectedColumns + "," + Environment.NewLine + " " + myRows["ColumnName"].ToString();
}
else
{
if (isIdentityInserted == false)
{
isIdentityInserted = true;
SelectedColumns = myRows["ColumnName"].ToString(); ;
}
else
{
SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString(); ;
}
}
}
}
else
{
isIdentityInserted = true;
if (ColumnsNo == 0)
{
SelectedColumns = myRows["ColumnName"].ToString();
inputerParameters = " @org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
OutputQuery = " select 1 as ReturnValue; ";// +myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
myWhereCondition = "(@org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = @org_" + myRows["ColumnName"].ToString() + ")";
}
else
{
SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString();
inputerParameters = inputerParameters + " , " + " @org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
myWhereCondition = "(@org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = @org_" + myRows["ColumnName"].ToString() + ")";
}
}
ColumnsNo++;
SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
SolutionsDBQueryColumnsDAL.Insert();
}
}
QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
" Create procedure " + SPName + " (" + Environment.NewLine +
inputerParameters + Environment.NewLine +
") " + Environment.NewLine +
" AS " + Environment.NewLine +
" Begin " + Environment.NewLine +
" Select " + SelectedColumns + Environment.NewLine +
" From " + TableName + " " + Environment.NewLine +
" Where " + myWhereCondition + " ;" + Environment.NewLine +
" END " + Environment.NewLine;
#endregion
}
this.Insert_SolutionsDBQuery(TableName, SPName, "Get", "Store Procedure", QueryContent,refreshGrid);
}
catch (Exception ex)
{
CommonClasses.Messages.GeneralError(ex.Message, " Get Query");
}
}
