public class DBHelper
{
/// <summary>
/// 执行一个增删改存储过程(有参)
/// </summary
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <param name="values">参数列表</param>
/// <returns>影响行数</returns>
public int ExecuteProc(string connString, string procName, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
cmd.CommandTimeout = 180;
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行一个查询无参存储过程，要关闭
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <returns>SqlDataReader</returns>
/* public SqlDataReader ExecuteProcSelect(string Connection,string procName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = connString;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteReader();
}*/
/// <summary>
/// 执行一个带参查询存储过程,注意要关闭
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <param name="values">参数列表</param>
/// <returns>SqlDataReader</returns>
/* public SqlDataReader ExecuteProcSelect(string Connection,string procName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = connString;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
return cmd.ExecuteReader();
}*/
/// <summary>
/// 执行一个无参增删改存储过程
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <returns>影响行数</returns>
public int ExecuteProc(string connString, string procName)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行一个(无参)增删改语句
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="safeSql">语句</param>
/// <returns>影响行数</returns>
public int ExecuteCommand(string connString, string safeSql)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, connection);
int result = cmd.ExecuteNonQuery();
return result;
}
}
/// <summary>
/// 执行一个有参增删改操作
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="sql">语句</param>
/// <param name="values">参数</param>
/// <returns>影响行数 </returns>
public int ExecuteCommand(string connString, string sql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 查询第一行第一列数据（无参）（返回的是什么类型就转换成什么类型）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="safeSql">语句</param>
/// <returns>object</returns>
public object GetScalar(string connString, string safeSql)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, connection)
{
CommandType = CommandType.StoredProcedure
};
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 查询第一行第一列数据（有参）（返回的是什么类型就转换成什么类型）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="values">参数</param>
/// <returns>object</returns>
public object GetScalar(string connString, string safeSql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, connection);
cmd.Parameters.AddRange(values);
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 返回一个SqlDataReader（注意要关闭）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="safeSql">语句</param>
/// <returns>SqlDataReader</returns>
/*public SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, connString);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}*/
/// <summary>
/// 返回int
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public int GetScalarInt(string connString, string sql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
cmd.CommandType = CommandType.StoredProcedure;
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
/// <summary>
/// 返回第一行第一列string
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public string GetScalarString(string connString, string sql, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
cmd.CommandType = CommandType.StoredProcedure;
return Convert.ToString(cmd.ExecuteScalar());
}
}
/// <summary>
/// 返回一个有参SqlDataReader（注意要关闭）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="sql">语句</param>
/// <param name="values">参数</param>
/// <returns>SqlDataReader</returns>
/* public SqlDataReader GetReader(string connString,string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, connString);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}*/
/// <summary>
/// 返回一个Datatable（无参）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <returns>DataTable</returns>
public DataTable GetDataSet(string connString, string procName)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
DataSet ds = new DataSet();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables.Count > 0)
return ds.Tables[0];
else
return null;
}
}
}
/// <summary>
/// 返回一个Datatable（有参）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <param name="values">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataSet(string connString, string procName, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
DataSet ds = new DataSet();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables.Count > 0)
return ds.Tables[0];
else
return null;
}
}
}
/// <summary>
/// 返回多个Datatable（有参）
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="procName">存储过程名字</param>
/// <param name="values">参数</param>
/// <returns>DataTable</returns>
public DataSet GetDataSets(string connString, string procName, params SqlParameter[] values)
{
using (SqlConnection connection = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
DataSet ds = new DataSet();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables.Count > 1)
return ds;
else
return null;
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(string connString, ArrayList SQLStringList)
{
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand
{
Connection = connection
};
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 将table导入数据库
/// </summary>
/// <param name = "connString" > 数据库连接语句 </ param >
/// <param name="table">table数据</param>
/// <param name="name">table名称</param>
/// <returns></returns>
public int SqlCopySource(string connString, DataTable table, string name)
{
SqlConnection conn = new SqlConnection(connString);
conn.Open();
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
{
bulk.BatchSize = table.Rows.Count;
bulk.DestinationTableName = name;
try
{
bulk.WriteToServer(table);
return 1;
}
catch
{
return -1;
}
finally
{
bulk.Close();
conn.Close();
conn.Dispose();
}
}
}
}