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()); }
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 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()); }