c winform mysql类_c#操作数据库通用类

using System;

using System.Collections;

using System.Collections.Specialized;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Data.Common;

using System.Collections.Generic;

namespace Maticsoft.DBUtility

{

///

/// 数据访问抽象基础类

/// Copyright (C) 2004-2008 By LiTianPing

///

public abstract class DbHelperSQL

{

//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.

public static string connectionString =  ConfigurationManager.AppSettings["ZuLinBaoConnectionKey"];

public static string connectionBugString = PubConstant.GetConnectionString("ZuLinBaoBugConnectionKey");

public DbHelperSQL()

{

}

#region 公用方法

public static bool Exists(string strSql)

{

object obj = DbHelperSQL.GetSingle(strSql);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

}

///

/// 表是否存在

///

///

///

public static bool TabExists(string TableName)

{

string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";

//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";

object obj = DbHelperSQL.GetSingle(strsql);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

}

public static bool Exists(string strSql, params SqlParameter[] cmdParms)

{

object obj = DbHelperSQL.GetSingle(strSql, cmdParms);

int cmdresult;

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

cmdresult = 0;

}

else

{

cmdresult = int.Parse(obj.ToString());

}

if (cmdresult == 0)

{

return false;

}

else

{

return true;

}

}

#endregion

#region  执行简单SQL语句

///

/// 执行SQL语句，返回影响的记录数

///

/// SQL语句

/// 影响的记录数

public static int ExecuteSql(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

}

}

public static int ExecuteSqlByTime(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

connection.Open();

cmd.CommandTimeout = Times;

int rows = cmd.ExecuteNonQuery();

return rows;

}

}

}

///

/// 执行Sql和Oracle滴混合事务

///

/// SQL命令行列表

/// Oracle命令行列表

/// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功

public static int ExecuteSqlTran(List list, List oracleCmdSqlList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

SqlTransaction tx = conn.BeginTransaction();

cmd.Transaction = tx;

try

{

foreach (CommandInfo myDE in list)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

PrepareCommand(cmd, conn, tx, cmdText, cmdParms);

if (myDE.EffentNextType == EffentNextType.SolicitationEvent)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

tx.Rollback();

throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");

//return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (isHave)

{

//引发事件

myDE.OnSolicitationEvent();

}

}

if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");

//return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");

//return 0;

}

if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");

//return 0;

}

continue;

}

int val = cmd.ExecuteNonQuery();

if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

{

tx.Rollback();

throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");

//return 0;

}

cmd.Parameters.Clear();

}

string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");

bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);

if (!res)

{

tx.Rollback();

throw new Exception("Oracle执行失败");

// return -1;

}

tx.Commit();

return 1;

}

catch (System.Data.SqlClient.SqlException e)

{

tx.Rollback();

throw e;

}

catch (Exception e)

{

tx.Rollback();

throw e;

}

}

}

///

/// 执行多条SQL语句，实现数据库事务。

///

/// 多条SQL语句

public static int ExecuteSqlTran(List SQLStringList)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = connection;

SqlTransaction tx = connection.BeginTransaction();

cmd.Transaction = tx;

try

{

int count = 0;

for (int n = 0; n < SQLStringList.Count; n++)

{

string strsql = SQLStringList[n];

if (strsql.Trim().Length > 1)

{

cmd.CommandText = strsql;

count += cmd.ExecuteNonQuery();

}

}

tx.Commit();

return count;

}

catch

{

tx.Rollback();

return 0;

}

finally

{

cmd.Dispose();

connection.Close();

}

}

}

///

/// 执行带一个存储过程参数的的SQL语句。

///

/// SQL语句

/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加

/// 影响的记录数

public static int ExecuteSql(string SQLString, string content)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

myParameter.Value = content;

cmd.Parameters.Add(myParameter);

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

}

}

///

/// 执行带一个存储过程参数的的SQL语句。

///

/// SQL语句

/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加

/// 影响的记录数

public static object ExecuteSqlGet(string SQLString, string content)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

myParameter.Value = content;

cmd.Parameters.Add(myParameter);

connection.Open();

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

}

}

}

///

/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)

///

/// SQL语句

/// 图像字节,数据库的字段类型为image的情况

/// 影响的记录数

public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(strSQL, connection))

{

System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);

myParameter.Value = fs;

cmd.Parameters.Add(myParameter);

connection.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

}

}

///

/// 执行一条计算查询结果语句，返回查询结果(object)。

///

/// 计算查询结果语句

/// 查询结果(object)

public static object GetSingle(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

connection.Open();

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

}

}

}

public static object GetSingle(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand(SQLString, connection))

{

connection.Open();

cmd.CommandTimeout = Times;

object obj = cmd.ExecuteScalar();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

}

}

}

///

/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

///

/// 查询语句

/// SqlDataReader

public static SqlDataReader ExecuteReader(string strSQL)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(strSQL, connection);

try

{

connection.Open();

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

if (connection != null)

{

connection.Close(); connection.Dispose();

}

if (cmd != null)

{

cmd.Dispose();

}

throw e;

}

}

///

/// 执行查询语句，返回DataSet

///

/// 查询语句

/// DataSet

public static DataSet Query(string SQLString)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlDataAdapter command = new SqlDataAdapter(SQLString, connection))

{

DataSet ds = new DataSet();

connection.Open();

command.Fill(ds, "ds");

return ds;

}

}

}

public static DataSet Query(string SQLString, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlDataAdapter command = new SqlDataAdapter(SQLString, connection))

{

DataSet ds = new DataSet();

connection.Open();

command.SelectCommand.CommandTimeout = Times;

command.Fill(ds, "ds");

return ds;

}

}

}

#endregion

#region 执行带参数的SQL语句

///

/// 执行SQL语句，返回影响的记录数

///

/// SQL语句

/// 影响的记录数

public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand())

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

}

}

public static int ExecuteBugSql(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionBugString))

{

using (SqlCommand cmd = new SqlCommand())

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

}

}

///

/// 执行多条SQL语句，实现数据库事务。

///

/// SQL语句的哈希表(key为sql语句，value是该语句的SqlParameter[])

public static void ExecuteSqlTran(Hashtable SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

//循环

foreach (DictionaryEntry myDE in SQLStringList)

{

string cmdText = myDE.Key.ToString();

SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

}

}

}

///

/// 执行多条SQL语句，实现数据库事务。

///

/// SQL语句的哈希表(key为sql语句，value是该语句的SqlParameter[])

public static int ExecuteSqlTran(System.Collections.Generic.List cmdList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{ int count = 0;

//循环

foreach (CommandInfo myDE in cmdList)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

{

if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

{

trans.Rollback();

return 0;

}

object obj = cmd.ExecuteScalar();

bool isHave = false;

if (obj == null && obj == DBNull.Value)

{

isHave = false;

}

isHave = Convert.ToInt32(obj) > 0;

if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

{

trans.Rollback();

return 0;

}

if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

{

trans.Rollback();

return 0;

}

continue;

}

int val = cmd.ExecuteNonQuery();

count += val;

if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

{

trans.Rollback();

return 0;

}

cmd.Parameters.Clear();

}

trans.Commit();

return count;

}

catch

{

trans.Rollback();

throw;

}

}

}

}

///

/// 执行多条SQL语句，实现数据库事务。

///

/// SQL语句的哈希表(key为sql语句，value是该语句的SqlParameter[])

public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

int indentity = 0;

//循环

foreach (CommandInfo myDE in SQLStringList)

{

string cmdText = myDE.CommandText;

SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.InputOutput)

{

q.Value = indentity;

}

}

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.Output)

{

indentity = Convert.ToInt32(q.Value);

}

}

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

}

}

}

///

/// 执行多条SQL语句，实现数据库事务。

///

/// SQL语句的哈希表(key为sql语句，value是该语句的SqlParameter[])

public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

using (SqlTransaction trans = conn.BeginTransaction())

{

SqlCommand cmd = new SqlCommand();

try

{

int indentity = 0;

//循环

foreach (DictionaryEntry myDE in SQLStringList)

{

string cmdText = myDE.Key.ToString();

SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.InputOutput)

{

q.Value = indentity;

}

}

PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

foreach (SqlParameter q in cmdParms)

{

if (q.Direction == ParameterDirection.Output)

{

indentity = Convert.ToInt32(q.Value);

}

}

cmd.Parameters.Clear();

}

trans.Commit();

}

catch

{

trans.Rollback();

throw;

}

}

}

}

///

/// 执行一条计算查询结果语句，返回查询结果(object)。

///

/// 计算查询结果语句

/// 查询结果(object)

public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand())

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

object obj = cmd.ExecuteScalar();

cmd.Parameters.Clear();

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

}

}

}

///

/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

///

/// 查询语句

/// SqlDataReader

public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

try

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

if (connection != null)

{

connection.Close(); connection.Dispose();

}

if (cmd != null)

{

cmd.Dispose();

}

throw e;

}

}

public static int Execute18(string storedProcName, params SqlParameter[] cmdParms)

{

using(SqlConnection connection=new SqlConnection(connectionString))

{

using (SqlCommand command = BuildQueryCommand(connection, storedProcName, cmdParms))

{

connection.Open();

command.CommandType = CommandType.StoredProcedure;

int returnReader = command.ExecuteNonQuery();

command.Dispose();

connection.Close();

return returnReader;

}

}

}

///

/// 执行查询语句，返回DataSet

///

/// 查询语句

/// DataSet

public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand cmd = new SqlCommand())

{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataSet ds = new DataSet();

da.Fill(ds, "ds");

cmd.Parameters.Clear();

return ds;

}

}

}

}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = CommandType.Text;//cmdType;

if (cmdParms != null)

{

foreach (SqlParameter parameter in cmdParms)

{

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

cmd.Parameters.Add(parameter);

}

}

}

#endregion

#region 存储过程操作

///

/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

///

/// 存储过程名

/// 存储过程参数

/// SqlDataReader

public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

try

{

SqlDataReader returnReader;

connection.Open();

command.CommandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return returnReader;

}

catch(Exception e)

{

if (connection != null)

{

connection.Close(); connection.Dispose();

}

if (command != null)

{

command.Dispose();

}

throw e;

}

}

///

/// 执行存储过程

///

/// 存储过程名

/// 存储过程参数

/// DataSet结果中的表名

/// DataSet

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

DataSet dataSet = new DataSet();

connection.Open();

using (SqlDataAdapter sqlDA = new SqlDataAdapter())

{

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.Fill(dataSet, tableName);

return dataSet;

}

}

}

public static DataSet RunPro(string pn)//pn是存储过程的名字

{

DataSet dataSet = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand com = new SqlCommand())

{

using (SqlDataAdapter sqlDA = new SqlDataAdapter())

{

com.Connection = connection;

com.CommandType = CommandType.StoredProcedure;

com.CommandText = pn;

if (connection.State == ConnectionState.Closed)

{

connection.Open();

}

sqlDA.SelectCommand = com;

sqlDA.Fill(dataSet);

return dataSet;

}

}

}

}

public static string  RunProstring(string pn,SqlParameter sp)//pn是存储过程的名字,返回一个string

{

string dd = "";

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand com = new SqlCommand())

{

com.Connection = connection;

com.CommandType = CommandType.StoredProcedure;

com.CommandText = pn;

if (connection.State == ConnectionState.Closed)

{

connection.Open();

}

com.Parameters.Add(sp);

if (com.ExecuteScalar() != null)

{

dd =Convert.ToString(com.ExecuteScalar());

}

return dd;

}

}

}

public static string RunProstring1(string pn, IDataParameter[] sp)//pn是存储过程的名字,返回一个string

{

string dd = "";

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand com = new SqlCommand())

{

com.Connection = connection;

com.CommandType = CommandType.StoredProcedure;

com.CommandText = pn;

if (connection.State == ConnectionState.Closed)

{

connection.Open();

}

foreach (SqlParameter parameter in sp)

{

if (parameter != null)

{

// 检查未分配值的输出参数,将其分配以DBNull.Value.

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

com.Parameters.Add(parameter);

}

}

object ob=com.ExecuteScalar();

if ( ob!= null)

{

dd = Convert.ToString(ob);

}

return dd;

}

}

}

public static DataSet RunProcedure1(string storedProcName, IDataParameter[] parameters)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlDataAdapter sqlDA = new SqlDataAdapter())

{

DataSet dataSet = new DataSet();

connection.Open();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.Fill(dataSet);

return dataSet;

}

}

}

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlDataAdapter sqlDA = new SqlDataAdapter())

{

DataSet dataSet = new DataSet();

connection.Open();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.SelectCommand.CommandTimeout = Times;

sqlDA.Fill(dataSet, tableName);

return dataSet;

}

}

}

///

/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)

///

/// 数据库连接

/// 存储过程名

/// 存储过程参数

/// SqlCommand

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

SqlCommand command = new SqlCommand(storedProcName, connection);

command.CommandType = CommandType.StoredProcedure;

if (parameters != null)

{

foreach (SqlParameter parameter in parameters)

{

if (parameter != null)

{

// 检查未分配值的输出参数,将其分配以DBNull.Value.

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

command.Parameters.Add(parameter);

}

}

}

return command;

}

///

/// 执行存储过程，返回影响的行数

///

/// 存储过程名

/// 存储过程参数

/// 影响的行数

///

public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand command = BuildIntCommand(connection, storedProcName, parameters))

{

int result;

connection.Open();

rowsAffected = command.ExecuteNonQuery();

result = (int)command.Parameters["ReturnValue"].Value;

return result;

}

}

}

public static int RunProcedure17(string storedProcName, IDataParameter[] parameters)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

using (SqlCommand command = BuildIntCommand(connection, storedProcName, parameters))

{

int result;

connection.Open();

int rowsAffected = command.ExecuteNonQuery();

result = (int)command.Parameters["ReturnValue"].Value;

return result;

}

}

}

///

/// 创建 SqlCommand 对象实例(用来返回一个整数值)

///

/// 存储过程名

/// 存储过程参数

/// SqlCommand 对象实例

private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.Parameters.Add(new SqlParameter("ReturnValue",

SqlDbType.Int, 4, ParameterDirection.ReturnValue,

false, 0, 0, string.Empty, DataRowVersion.Default, null));

command.CommandTimeout = 200;

return command;

}

#endregion

}

}