2015-09-17 001 存储过程数据操作类 H_data_Helper
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
/// <summary>
///H_data_Helper 的摘要说明
/// </summary>
//public class H_data_Helper
namespace P_ht
{
//public sealed class H_data_Helper
public class H_data_Helper
{
#region 变量
/// <summary>
/// 数据库类型
/// </summary>
//private static string dbProviderName = System.Configuration.ConfigurationManager.ConnectionStrings["DbHelperProvider"] == null ? string.Empty : System.Configuration.ConfigurationManager.ConnectionStrings["DbHelperProvider"].ConnectionString;
private static string dbProviderName = "System.Data.SqlClient";
/// <summary>
/// 数据链接字符串
/// </summary>
//private static string dbConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DbHelperConnectionString"] == null ? string.Empty : System.Configuration.ConfigurationManager.ConnectionStrings["DbHelperConnectionString"].ConnectionString;
private static string dbConnectionString = P_ht.H_dec.Decrypt_DES(System.Configuration.ConfigurationManager.ConnectionStrings["localConnectionString"].ToString());
/// <summary>
/// 数据链接对象
/// </summary>
private static System.Data.Common.DbConnection Connection;
#endregion
#region "已注释代码"
/*
/// <summary>
/// 构造函数
/// </summary>
private MyDBHelper()
{
Connection = CreateConnection(dbConnectionString);
}
/// <summary>
/// 带参数构造函数
/// </summary>
/// <param name="connectionString"></param>
public MyDBHelper(string connectionString, string strProviderName)
{
if (string.IsNullOrEmpty(strProviderName)) dbProviderName = "System.Data.SqlClient";
Connection = CreateConnection(connectionString);
dbConnectionString = connectionString;
}
/// <summary>
/// 带参数构造函数
/// </summary>
/// <param name="connectionString"></param>
public MyDBHelper(string connectionString)
{
dbProviderName = "System.Data.SqlClient";
Connection = CreateConnection(connectionString);
dbConnectionString = connectionString;
}
*/
#endregion
/// <summary>
/// 创建链接对象
/// </summary>
/// <returns></returns>
public static System.Data.Common.DbConnection CreateConnection()
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = dbConnectionString;
return dbconn;
}
/// <summary>
/// 创建链接对象
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public static System.Data.Common.DbConnection CreateConnection(string connectionString)
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = connectionString;
return dbconn;
}
/// <summary>
/// 对数据源执行命令对象
/// </summary>
/// <param name="storedProcedure">存储过程</param>
/// <returns></returns>
public System.Data.Common.DbCommand GetStoredProcCommond(string storedProcedure)
{
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = storedProcedure;
dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
return dbCommand;
}
/// <summary>
/// 对数据源执行命令对象
/// </summary>
/// <param name="sqlQuery">SQL 语句</param>
/// <returns></returns>
public System.Data.Common.DbCommand GetSqlStringCommond(string sqlQuery)
{
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
return dbCommand;
}
#region 存储过程参数
/// <summary>
/// 存储过程增加参数
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="dbParameterCollection">参数对象集合</param>
public void AddParameterCollection(System.Data.Common.DbCommand cmd, System.Data.Common.DbParameterCollection dbParameterCollection)
{
foreach (System.Data.Common.DbParameter dbParameter in dbParameterCollection)
{
cmd.Parameters.Add(dbParameter);
}
}
/// <summary>
/// 存储过程增加输出参数
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="parameterName">参数名称</param>
/// <param name="dbType">参数类型</param>
/// <param name="size">参数长度</param>
public void AddOutParameter(System.Data.Common.DbCommand cmd, string parameterName, System.Data.DbType dbType, int size)
{
System.Data.Common.DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Size = size;
dbParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 存储过程增加输入参数
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="parameterName">参数名称</param>
/// <param name="dbType">参数类型</param>
/// <param name="size">参数长度</param>
public void AddInParameter(System.Data.Common.DbCommand cmd, string parameterName, System.Data.DbType dbType, object value)
{
System.Data.Common.DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 存储过程增加返回参数
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="parameterName">参数名称</param>
/// <param name="dbType">参数类型</param>
public void AddReturnParameter(System.Data.Common.DbCommand cmd, string parameterName, System.Data.DbType dbType)
{
System.Data.Common.DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 获取存储过程参数
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="parameterName">参数名称</param>
/// <returns></returns>
public System.Data.Common.DbParameter GetParameter(System.Data.Common.DbCommand cmd, string parameterName)
{
return cmd.Parameters[parameterName];
}
#endregion
#region 执行
/// <summary>
/// 执行SQL语句获取数据
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <returns></returns>
public static System.Data.DataSet ExecuteSqlReturnDataSet(string sqlQuery, out string ExceptionMessage)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
try
{
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
dbDataAdapter.SelectCommand = dbCommand;
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
dbDataAdapter.Fill(ds);
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
//System.Console.WriteLine("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
//throw new Exception("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ds;
}
/// <summary>
/// 执行存储过程获取数据
/// </summary>
/// <param name="storedProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数组</param>
/// <param name="ExceptionMessage">异常信息</param>
/// <returns></returns>
public static System.Data.DataSet ExecuteProcedureReturnDataSet(string storedProcName, System.Data.Common.DbParameter[] parameters, out string ExceptionMessage)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
try
{
dbCommand.CommandText = storedProcName;
dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
foreach (System.Data.Common.DbParameter p in parameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == System.Data.ParameterDirection.InputOutput || p.Direction == System.Data.ParameterDirection.Input) && (p.Value == null))
{
p.Value = DBNull.Value;
}
if (p.DbType == System.Data.DbType.String)
{
p.DbType = System.Data.DbType.AnsiString;
}
dbCommand.Parameters.Add(p);
}
}
dbDataAdapter.SelectCommand = dbCommand;
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
dbDataAdapter.Fill(ds, "ds");
dbCommand.Parameters.Clear();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
//System.Console.WriteLine("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
//throw new Exception("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
ExceptionMessage = "页面在执行[" + storedProcName + "]存储过程时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ds;
}
/// <summary>
/// 返回存储过程参数对象
/// </summary>
/// <param name="ParameterName">参数名称</param>
/// <param name="dbtype">参数类型</param>
/// <param name="dbSize">参数长度</param>
/// <param name="Value">参数值</param>
/// <param name="ParameterType">参数类别(输入参数、输入和输出、输出参数)</param>
/// <returns></returns>
public static System.Data.Common.DbParameter ReturnParameters(string ParameterName, System.Data.DbType dbtype, int dbSize, string Value, System.Data.ParameterDirection ParameterType)
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbParameter dbParameter = dbfactory.CreateParameter();
dbParameter.DbType = dbtype;//DbType.AnsiString
dbParameter.Size = dbSize;
dbParameter.ParameterName = ParameterName;
dbParameter.Value = Value;
dbParameter.Direction = ParameterType;
return dbParameter;
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <returns></returns>
public static System.Data.DataTable ExecuteSqlReturnDataTable(string sqlQuery, out string ExceptionMessage)
{
System.Data.DataTable dataTable = new System.Data.DataTable();
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(dbProviderName);
System.Data.Common.DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
dbDataAdapter.SelectCommand = dbCommand;
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
dbDataAdapter.Fill(dataTable);
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return dataTable;
}
/// <summary>
/// 返回DataReader对象
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <returns></returns>
public static System.Data.Common.DbDataReader ExecuteReader(string sqlQuery, out string ExceptionMessage)
{
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
System.Data.Common.DbDataReader reader = null;
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
reader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return reader;
}
/// <summary>
/// 返回受影响的行数
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlQuery, out string ExceptionMessage)
{
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
int ret = -1;
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
ret = dbCommand.ExecuteNonQuery();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ret;
}
/// <summary>
/// 返回的第一行的第一列
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <returns></returns>
public static object ExecuteScalar(string sqlQuery, out string ExceptionMessage)
{
Connection = CreateConnection(dbConnectionString);
System.Data.Common.DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
object ret = new object();
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
ret = dbCommand.ExecuteScalar();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ret;
}
#endregion
#region 动态数据库执行脚步
/// <summary>
/// 返回的第一行的第一列
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据库类型</param>
/// <returns></returns>
public static object ExecuteScalar(string sqlQuery, string strConnectionString, string strdbProviderName, out string ExceptionMessage)
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbConnection dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = strConnectionString;
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
object ret = new object();
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
ret = dbCommand.ExecuteScalar();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ret;
}
/// <summary>
/// 通过事务的方式执行SQL语句返回受影响的行数
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据库类型</param>
/// <param name="ExceptionMessage">异常信息</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlQuery, string strConnectionString, string strdbProviderName, out string ExceptionMessage)
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbConnection dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = strConnectionString;
if (dbConnection.State == System.Data.ConnectionState.Closed)
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
System.Data.Common.DbTransaction myTran = dbConnection.BeginTransaction();
dbCommand.CommandText = sqlQuery;
int ret = -1;
try
{
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.Transaction = myTran;
ret = dbCommand.ExecuteNonQuery();
myTran.Commit();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行脚本语句时出现错误：" + ex.Message;
myTran.Rollback();
}
finally
{
if (dbConnection.State == System.Data.ConnectionState.Open)
dbConnection.Close();
}
return ret;
}
/// <summary>
/// 执行SQL语句获取数据
/// </summary>
/// <param name="sqlQuery">执行语句</param>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据库类型</param>
/// <param name="ExceptionMessage">异常信息</param>
/// <returns></returns>
public static System.Data.DataSet ExecuteSqlReturnDataSet(string sqlQuery, string strConnectionString, string strdbProviderName, out string ExceptionMessage)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
System.Data.Common.DbConnection dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = strConnectionString;
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
try
{
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = System.Data.CommandType.Text;
dbDataAdapter.SelectCommand = dbCommand;
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
dbDataAdapter.Fill(ds);
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
//System.Console.WriteLine("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
//throw new Exception("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
ExceptionMessage = "页面在执行[" + sqlQuery + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ds;
}
/// <summary>
/// 判断物理表是否已经存在
/// </summary>
/// <param name="strTableName">物理表名称</param>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据类型</param>
/// <param name="ExceptionMessage">异常信息</param>
/// <returns>0为不存在，1为存在</returns>
public static int ExistsTable(string strTableName, string strConnectionString, string strdbProviderName, out string ExceptionMessage)
{
string strTempSQL = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) BEGIN SELECT 1 END ELSE BEGIN SELECT 0 END";
strTempSQL = string.Format(strTempSQL, strTableName.Trim());
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbConnection dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = strConnectionString;
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = strTempSQL;
dbCommand.CommandType = System.Data.CommandType.Text;
int ret = -1;
try
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
ret = int.Parse(dbCommand.ExecuteScalar().ToString());
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
ExceptionMessage = "页面在执行[" + strTempSQL + "]语句时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ret;
}
/// <summary>
/// 返回存储过程参数对象
/// </summary>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据类型</param>
/// <param name="ParameterName">参数名称</param>
/// <param name="dbtype">参数类型</param>
/// <param name="dbSize">参数长度</param>
/// <param name="Value">参数值</param>
/// <param name="ParameterType">参数类别(输入参数、输入和输出、输出参数)</param>
/// <returns></returns>
public static System.Data.Common.DbParameter ReturnParameters(string strConnectionString, string strdbProviderName, string ParameterName, System.Data.DbType dbtype, int dbSize, string Value, System.Data.ParameterDirection ParameterType)
{
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbParameter dbParameter = dbfactory.CreateParameter();
dbParameter.DbType = dbtype;//DbType.AnsiString
dbParameter.Size = dbSize;
dbParameter.ParameterName = ParameterName;
dbParameter.Value = Value;
dbParameter.Direction = ParameterType;
return dbParameter;
}
/// <summary>
/// 执行存储过程获取数据
/// </summary>
/// <param name="strConnectionString">数据库连接字符串</param>
/// <param name="strdbProviderName">数据类型</param>
/// <param name="storedProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数组</param>
/// <param name="ExceptionMessage">异常信息</param>
/// <returns></returns>
public static System.Data.DataSet ExecuteProcedureReturnDataSet(string strConnectionString, string strdbProviderName, string storedProcName, System.Data.Common.DbParameter[] parameters, out string ExceptionMessage)
{
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.Common.DbProviderFactory dbfactory = System.Data.Common.DbProviderFactories.GetFactory(strdbProviderName);
System.Data.Common.DbConnection dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = strConnectionString;
System.Data.Common.DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
try
{
dbCommand.CommandText = storedProcName;
dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
foreach (System.Data.Common.DbParameter p in parameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == System.Data.ParameterDirection.InputOutput || p.Direction == System.Data.ParameterDirection.Input) && (p.Value == null))
{
p.Value = DBNull.Value;
}
if (p.DbType == System.Data.DbType.String)
{
p.DbType = System.Data.DbType.AnsiString;
}
dbCommand.Parameters.Add(p);
}
}
dbDataAdapter.SelectCommand = dbCommand;
if (dbCommand.Connection.State == System.Data.ConnectionState.Closed)
dbCommand.Connection.Open();
dbDataAdapter.Fill(ds, "ds");
dbCommand.Parameters.Clear();
ExceptionMessage = string.Empty;
}
catch (Exception ex)
{
//System.Console.WriteLine("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
//throw new Exception("在执行["+sqlQuery+"]语句时出现错误："+ex.Message);
ExceptionMessage = "页面在执行[" + storedProcName + "]存储过程时出现错误：" + ex.Message;
}
finally
{
if (dbCommand.Connection.State == System.Data.ConnectionState.Open)
dbCommand.Connection.Close();
}
return ds;
}
#endregion
#region 执行事务
#endregion
}
}
//////////////////////////////////////////
string out_info = string.Empty;
System.Data.Common.DbParameter[] Parameter = new System.Data.Common.DbParameter[5];
System.Data.Common.DbParameter str_user_num = P_ht.H_data_Helper.ReturnParameters("user_num", System.Data.DbType.String, 20, TextBox1.Text.Trim().ToString(), System.Data.ParameterDirection.Input);
System.Data.Common.DbParameter str_user_name = P_ht.H_data_Helper.ReturnParameters("user_name", System.Data.DbType.String, 20, TextBox2.Text.Trim().ToString(), System.Data.ParameterDirection.Input);
System.Data.Common.DbParameter str_user_psd = P_ht.H_data_Helper.ReturnParameters("user_psd", System.Data.DbType.String, 20, TextBox5.Text.Trim().ToString(), System.Data.ParameterDirection.Input);
System.Data.Common.DbParameter str_create_person = P_ht.H_data_Helper.ReturnParameters("create_person", System.Data.DbType.String, 20, Session["login_usesname"].ToString().Trim(), System.Data.ParameterDirection.Input);
System.Data.Common.DbParameter prtReturnValue = P_ht.H_data_Helper.ReturnParameters("ReturnValue", System.Data.DbType.String, 36, "-1", System.Data.ParameterDirection.ReturnValue);
Parameter[0] = str_user_num;
Parameter[1] = str_user_name;
Parameter[2] = str_user_psd;
Parameter[3] = str_create_person;
Parameter[4] = prtReturnValue;
System.Data.DataSet ds_t = P_ht.H_data_Helper.ExecuteProcedureReturnDataSet("sp_sys_user_add", Parameter, out out_info);
if (!string.IsNullOrEmpty(out_info))
{
//System.Windows.Forms.MessageBox.Show(out_info);
P_ht.H_public H_public = new P_ht.H_public();
H_public.msg(out_info);
return;
}
//////////////////////////////////////////
