//Stored Procedure Convet to DAL Layer Function
private void btnConvertSP2Fun_Click(object sender, EventArgs e)
{
try
{
int i = txtSP.Text.Trim().ToUpper().IndexOf("BEGIN");
int j = txtSP.Text.Trim().ToUpper().LastIndexOf("END");
//=== Read Template ===
System.IO.StreamReader myFile = new System.IO.StreamReader(@"Template.txt");
string myString = myFile.ReadToEnd();
myFile.Close();
//===Replace Template Tags===
StringBuilder tagBuff = new StringBuilder();
StringBuilder pramsBuff = new StringBuilder();
tagBuff.Append(txtSP.Text.Trim().Substring(i + 5, j - (i + 5)));
myString = ReplaceTag(myString, "TAG2", tagBuff.ToString());
BLLGetData gData = new BLLGetData();
DataTable dt = gData.GetSpparameters(cboSPList.Text);
string sFunParms = "";
for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
{
sFunParms += SqlTypeString2CsharpTypeString(dt.Rows[iRow]["DATA_TYPE"].ToString()) + " " + dt.Rows[iRow]["PARAMETER_NAME"].ToString().Replace("@", "") + " ,";
if (dt.Rows[iRow]["CHARACTER_OCTET_LENGTH"].ToString() == "")
{
pramsBuff.Append(" cmd.Parameters.Add(\"" + dt.Rows[iRow]["PARAMETER_NAME"].ToString() + "\", SqlDbType." + BLLSqlDbTypeToCSharpType.SqlTypeString2SqlType(dt.Rows[iRow]["DATA_TYPE"].ToString()) + ").Value = " + dt.Rows[iRow]["PARAMETER_NAME"].ToString().Replace("@", "") + "; " + Environment.NewLine);
}
else
{
pramsBuff.Append(" cmd.Parameters.Add(\"" + dt.Rows[iRow]["PARAMETER_NAME"].ToString() + "\", SqlDbType." + BLLSqlDbTypeToCSharpType.SqlTypeString2SqlType(dt.Rows[iRow]["DATA_TYPE"].ToString()) + "," + dt.Rows[iRow]["CHARACTER_OCTET_LENGTH"].ToString() + ").Value = " + dt.Rows[iRow]["PARAMETER_NAME"].ToString().Replace("@", "") + "; " + Environment.NewLine);
}
}
myString = ReplaceTag(myString, "TAG3", sFunParms.TrimEnd(','));
myString = ReplaceTag(myString, "TAG1", pramsBuff.ToString());
txtFun.Text = myString;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static Type SqlTypeString2CsharpType(string sqlTypeString)
{
SqlDbType dbTpe = BLLSqlDbTypeToCSharpType.SqlTypeString2SqlType(sqlTypeString);
return(BLLSqlDbTypeToCSharpType.SqlType2CsharpType(dbTpe));
}