private static List<StoreProcedure> FindProc(List<StoreProcedure> lstSP, StoreProcedure SP)
{
List<StoreProcedure> lsp = new List<StoreProcedure>();
foreach (StoreProcedure var in lstSP)
{
if (var.Name.ToUpper().Trim() == SP.Name.ToUpper().Trim())
{
lsp.Add(var);
}
}
return lsp;
}
private void FillProcSchemaTable(StoreProcedure storeProcedure, DataTable schemaProcTable)
{
storeProcedure.Parameters.Clear();
foreach (DataRow dr in schemaProcTable.Rows)
{
SPField spField = new SPField();
FillProcSchemaField(spField, dr);
storeProcedure.AddParameter(spField);
}
//��Ӵ����������
storeProcedure.CreateCmdText = GetObjectCreateText(storeProcedure.Name);
}
public void GetAllStoreProcedure()
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
this.storeProcedures.Clear();
foreach (string spName in GetDatabaseObjectList(this.connectionString, DatabaseObjectType.StoreProcedure))
{
StoreProcedure storeProcedure = new StoreProcedure(spName.Trim());
FillProcSchemaTable(storeProcedure, GetProcSchemaTable(this.connectionString, spName.Trim()));
this.storeProcedures.Add(storeProcedure);
}
}
public bool RemoveStoreProcedure(StoreProcedure StoreProcedure)
{
return this.StoreProcedures.Remove(StoreProcedure);
}
public bool AddStoreProcedure(StoreProcedure StoreProcedure)
{
foreach (StoreProcedure var in this.StoreProcedures)
{
if (var.Name.ToUpper().Trim() == StoreProcedure.Name.ToUpper().Trim())
{
return false;
}
}
this.StoreProcedures.Add(StoreProcedure);
return true;
}
public bool AddStoreProcedure(string storeProcedureName)
{
storeProcedureName = storeProcedureName.Trim();
foreach (StoreProcedure storeProcedure in this.storeProcedures )
{
if (storeProcedure.Name.ToUpper().Trim() == storeProcedureName.ToUpper().Trim())
{
return false;
}
}
StoreProcedure newProc = new StoreProcedure(storeProcedureName.Trim());
FillProcSchemaTable(newProc, GetProcSchemaTable(this.connectionString, storeProcedureName.Trim()));
this.storeProcedures.Add(newProc);
return true;
}
private string GetCallExecFuction(StoreProcedure storeProcedure, int tabCnt)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
int iTabCnt = tabCnt;
string strTabs = CodeHelper.AddTab(iTabCnt);
StringBuilder sb = new StringBuilder();
string _execSqlClassName = execSqlClassName.Length > 0 ? execSqlClassName + "." : "";
if (needTransaction)
{
switch (storeProcedure.ProcExecuteReturnType)
{
case StoreProcedure.ExecuteReturnType.NonQuery:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteNonQuery(command, connection, transaction);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.DataReader:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteReader(command, connection, transaction);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.Scalar:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteScalar(command, connection, transaction);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.Dataset:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteDataset(command, connection, transaction);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.DataTable:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteDataTable(command, connection, transaction);", _execSqlClassName)); break;
}
}
else
{
switch (storeProcedure.ProcExecuteReturnType)
{
case StoreProcedure.ExecuteReturnType.NonQuery:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteNonQuery(command, connection);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.DataReader:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteReader(command, connection);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.Scalar:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteScalar(command, connection);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.Dataset:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteDataset(command, connection);", _execSqlClassName)); break;
case StoreProcedure.ExecuteReturnType.DataTable:
sb.Append(strTabs + string.Format("retVal = {0}ExecuteDataTable(command, connection);", _execSqlClassName)); break;
}
}
return sb.ToString();
}
public bool RemoveStoreProcedure(StoreProcedure StoreProcedure)
{
return(this.StoreProcedures.Remove(StoreProcedure));
}
private string GetSetByRefVal(StoreProcedure storeProcedure, int tabCnt)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
int iTabCnt = tabCnt;
string strTabs = CodeHelper.AddTab(iTabCnt);
StringBuilder sb = new StringBuilder();
foreach (SPField var in storeProcedure.Parameters)
{
if (var.PARAMETER_MODE.Trim().ToUpper() != "IN")
{
sb.AppendLine(strTabs + string.Format("{0} = ({1})command.Parameters[\"@{0}\"].Value;", CodeHelper.GetPureParmName(var.PARAMETER_NAME), CodeHelper.ConvertDBTypeToDoNetType(var.DATA_TYPE)));
}
}
if (storeProcedure.NeedReturnValue)
{
sb.AppendLine (strTabs + "RETURN_VALUE = (int)command.Parameters[\"@RETURN_VALUE\"].Value;");
}
return sb.ToString();
}
private string GetFunSummary(StoreProcedure storeProcedure)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
string strTabs = " /// ";
StringBuilder sb = new StringBuilder();
sb.AppendLine(strTabs + "<summary>");
sb.AppendLine(strTabs + storeProcedure.Name);
sb.AppendLine(strTabs + "</summary>");
foreach (SPField var in storeProcedure.Parameters)
{
sb.AppendLine(strTabs + string.Format("<param name=\"{0}\">{0}</param>", CodeHelper.GetPureParmName(var.PARAMETER_NAME)));
}
if (storeProcedure.NeedReturnValue)
sb.AppendLine(strTabs + string.Format("<param name=\"{0}\">{0}</param>", "RETURN_VALUE"));
switch (this.connectType)
{
case ConnectType.ConnectionString:
sb.AppendLine(strTabs + string.Format("<param name=\"{0}\">{0}</param>", "connectionString")); break;
case ConnectType.SqlConnection:
sb.AppendLine(strTabs + string.Format("<param name=\"{0}\">{0}</param>", "connection")); break;
}
if (needTransaction && this.connectType == ConnectType.SqlConnection)
sb.AppendLine(strTabs + string.Format("<param name=\"{0}\">{0}</param>", "transaction"));
sb.AppendLine(strTabs + "<returns></returns>");
return sb.ToString();
}
private string GetFunctionDefinition(StoreProcedure storeProcedure)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
int iTabCnt = 2;
StringBuilder sb = new StringBuilder();
sb.Append(GetFunSummary(storeProcedure));
sb.Append(CodeHelper.AddTab(2) + functionAuthority);
switch (storeProcedure.ProcExecuteReturnType)
{
case StoreProcedure.ExecuteReturnType.NonQuery:
sb.Append("int "); break;
case StoreProcedure.ExecuteReturnType.DataReader:
sb.Append("SqlDataReader "); break;
case StoreProcedure.ExecuteReturnType.Scalar:
sb.Append("object "); break;
case StoreProcedure.ExecuteReturnType.Dataset:
sb.Append("DataSet "); break;
case StoreProcedure.ExecuteReturnType.DataTable:
sb.Append("DataTable "); break;
}
#region ��Ӳ���
sb.Append(storeProcedure.Name.Replace(" ", "") + "(");
foreach (SPField var in storeProcedure.Parameters)
{
sb.Append(", ");
if (var.PARAMETER_MODE.Trim().ToUpper() == "IN")
sb.Append(CodeHelper.ConvertDBTypeToDoNetType(var.DATA_TYPE) + " " + CodeHelper.GetPureParmName(var.PARAMETER_NAME));
else
sb.Append("ref " + CodeHelper.ConvertDBTypeToDoNetType(var.DATA_TYPE) + " " + CodeHelper.GetPureParmName(var.PARAMETER_NAME));
}
if (storeProcedure.NeedReturnValue)
sb.Append(", ref int RETURN_VALUE");
switch (this.connectType)
{
case ConnectType.ConnectionString:
sb.Append(", string connectionString"); break;
case ConnectType.SqlConnection:
sb.Append(", SqlConnection connection"); break;
}
if (needTransaction && this.connectType == ConnectType.SqlConnection)
sb.Append(", SqlTransaction transaction");
sb.Replace("(, ", "(");
sb.Append(")");
#endregion
sb.Append("\r\n" + CodeHelper.AddTab(2) + "{\r\n");
iTabCnt = 3;
sb.AppendLine(CodeHelper.AddTab(iTabCnt) + "@@CreateCommand\r\n");
sb.AppendLine(CodeHelper.AddTab(iTabCnt) + "@@CommandExecute");
sb.AppendLine(CodeHelper.AddTab(2) + "}\r\n");
return sb.ToString();
}
private string GetCmdExecuteCode(StoreProcedure storeProcedure)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
int iTabCnt = 3;
string strTabs = CodeHelper.AddTab(iTabCnt);
StringBuilder sb = new StringBuilder();
switch (storeProcedure.ProcExecuteReturnType)
{
case StoreProcedure.ExecuteReturnType.NonQuery:
sb.AppendLine("int retVal = 0;"); break;
case StoreProcedure.ExecuteReturnType.DataReader:
sb.AppendLine("SqlDataReader retVal = new SqlDataReader();"); break;
case StoreProcedure.ExecuteReturnType.Scalar:
sb.AppendLine("object retVal;"); break;
case StoreProcedure.ExecuteReturnType.Dataset:
sb.AppendLine("DataSet retVal = new DataSet();"); break;
case StoreProcedure.ExecuteReturnType.DataTable:
sb.AppendLine("DataTable retVal = new DataTable();"); break;
}
sb.AppendLine();
switch (this.connectType)
{
case ConnectType.SqlConnection:
sb.AppendLine(GetCallExecFuction(storeProcedure, iTabCnt) + "\r\n");
sb.Append(GetSetByRefVal(storeProcedure, iTabCnt));
break;
case ConnectType.ConnectionString:
sb.AppendLine(CodeHelper.AddTab(iTabCnt) + "using (SqlConnection connection = new SqlConnection(connectionString))");
sb.AppendLine(CodeHelper.AddTab(iTabCnt) + "{");
sb.AppendLine(CodeHelper.AddTab(iTabCnt + 1) + "connection.Open();");
if (needTransaction)
sb.AppendLine(CodeHelper.AddTab(iTabCnt + 1) + "SqlTransaction transaction = connection.BeginTransaction(System.Guid.NewGuid().ToString().Substring(1, 32));");
sb.AppendLine();
sb.AppendLine(GetCallExecFuction(storeProcedure, iTabCnt + 1) + "\r\n");
sb.Append(GetSetByRefVal(storeProcedure, iTabCnt + 1));
sb.AppendLine(CodeHelper.AddTab(iTabCnt) + "}");
break;
}
sb.Append("\r\n" + strTabs + "return retVal;");
return sb.ToString();
}
private string GetCmdDefinition(StoreProcedure storeProcedure)
{
if (storeProcedure == null) throw new ArgumentNullException("StoreProcedure");
int iTabCnt = 3;
string strTabs = CodeHelper.AddTab(iTabCnt);
StringBuilder sb = new StringBuilder("SqlCommand command = new SqlCommand();\r\n");
sb.AppendLine(strTabs + "command.CommandText = \"" + storeProcedure.Name + "\";");
sb.AppendLine(strTabs + "command.CommandType = System.Data.CommandType.StoredProcedure;\r\n");
#region ����������
StringBuilder sbCmdParm = new StringBuilder();
string dbType, size, direction, isNullable = "false", precision, scale, sourceColumn = "", sourceVersion = "DataRowVersion.Current";
foreach (SPField var in storeProcedure.Parameters)
{
string strAddParm = "", strPureName = CodeHelper.GetPureParmName(var.PARAMETER_NAME);
dbType = CodeHelper.ConvertDBTypeToDoNetSqlDbType(var.DATA_TYPE);
size = var.CHARACTER_MAXIMUM_LENGTH == null ? "0" : var.CHARACTER_MAXIMUM_LENGTH.ToString();
direction=var.PARAMETER_MODE.Trim().ToUpper() == "IN"?"ParameterDirection.Input":"ParameterDirection.InputOutput";
precision = var.NUMERIC_PRECISION == null ? "0" : var.NUMERIC_PRECISION.ToString();
scale = var.NUMERIC_SCALE == null ? "0" : var.NUMERIC_SCALE.ToString();
// 0 1 2 3 4 5 6 7 8 9
//command.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 11, ParameterDirection.Input, true, 0, 0, "Description", DataRowVersion.Current, "garden hose"));
strAddParm = string.Format(strTabs + "command.Parameters.Add(new SqlParameter(\"@{0}\", {1}, {2}, {3}, {4}, {5}, {6}, \"{7}\", {8}, {9}));",
strPureName, dbType, size, direction, isNullable, precision, scale, sourceColumn, sourceVersion, strPureName
);
sb.AppendLine(strAddParm);
}
if (storeProcedure.NeedReturnValue)
{
sb.AppendLine(strTabs + "command.Parameters.Add(new SqlParameter(\"@RETURN_VALUE\", SqlDbType.Int));");
sb.Append(strTabs + "command.Parameters[\"@RETURN_VALUE\"].Direction = ParameterDirection.ReturnValue;");
}
#endregion
return sb.ToString();
}
