using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;
using DBUtility.ConfigurationHelp;//个人设定的配置类
namespace DBUtility.SqlHelp
{
/// <summary>
/// 数据库操作基类
/// 实现对Sql数据库的各种操作
/// </summary>
public abstract class DbHelperSQL
{
#region 变量声明
private static string connectionString = ConnectionStringAdmin.BaiHuoGu;//得到 连接数据库的字符串
private static SqlConnection connectionAnyWhere;//创建SQL连接
private DataSet ds = null; //创建数据集
#endregion
public DbHelperSQL()
{
}
#region 打开/关闭数据库连接
/// <summary>
/// 打开数据库连接
/// </summary>
private static void OpenConnection()
{
#region
try
{
connectionAnyWhere = new SqlConnection(connectionString);
if (connectionAnyWhere.State != System.Data.ConnectionState.Open)
{
connectionAnyWhere.Open();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
#endregion
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public static void CloseConnection()
{
#region
try
{
if (connectionAnyWhere != null)
{
if (connectionAnyWhere.State != System.Data.ConnectionState.Closed)
{
connectionAnyWhere.Close();
connectionAnyWhere.Dispose();
}
}
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
#endregion
}
#endregion
#region 关闭数据库和清除DateSet对象
/// <summary>
/// 关闭数据库和清除DateSet对象
/// </summary>
public void CloseConnectionAndDateSet()
{
if (ds != null) // 清除DataSet对象
{
ds.Clear();
}
if (connectionAnyWhere != null)
{
connectionAnyWhere.Close(); // 关闭数据库连接
connectionAnyWhere.Dispose();
}
}
#endregion
#region 执行简单Sql语句
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="strSql"></param>
public static int ExecuteNonQuery(string strSql)
{
#region
int rowscount = 0;
SqlCommand cmd = null;
try
{
OpenConnection();
cmd = new SqlCommand(strSql, connectionAnyWhere);
cmd.CommandTimeout = 60;
rowscount = cmd.ExecuteNonQuery();
}
catch (SqlException sqlex) { throw sqlex; }
finally
{
cmd.Connection.Close();
cmd.Dispose();
////CloseConnection();
}
return rowscount;
#endregion
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string strSql)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(strSql, connectionAnyWhere);
cmd.CommandTimeout = 60;
DataSet ds = null;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
try
{
ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
////CloseConnection();
}
return ds;
}
}
/// <summary>
/// 执行SQL语句，并返回第一行第一列结果
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static string ExecuteScalar(string strSql)
{
#region
string strReturn = "";
SqlCommand cmd = null;
OpenConnection();
try
{
cmd = new SqlCommand(strSql, connectionAnyWhere);
cmd.CommandTimeout = 60;
strReturn = cmd.ExecuteScalar().ToString();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
////CloseConnection();
}
return strReturn;
#endregion
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数[返回整型值]</returns>
public static int ExecuteNonQuery(string strSQL, string content)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(strSQL, connectionAnyWhere);
try
{
cmd.CommandTimeout = 60;
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数[返回object]</returns>
public static object ExecuteSqlGet(string strSQL, string content)
{
OpenConnection();
using (SqlCommand cmd = new SqlCommand(strSQL, connectionAnyWhere))
{
try
{
cmd.CommandTimeout = 60;
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connectionAnyWhere;
cmd.CommandTimeout = 60;
SqlTransaction tx = connectionAnyWhere.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);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
OpenConnection();
using (SqlCommand cmd = new SqlCommand(strSQL, connectionAnyWhere))
{
try
{
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
cmd.CommandTimeout = 60;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
OpenConnection();
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connectionAnyWhere, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
OpenConnection();
using (SqlTransaction trans = connectionAnyWhere.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, connectionAnyWhere, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch (SqlException ex)
{
trans.Rollback();
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object ExecuteScalar(string SQLString, params SqlParameter[] cmdParms)
{
OpenConnection();
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connectionAnyWhere, 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;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connectionAnyWhere, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch (SqlException ex)
{
throw ex;
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose();
// connection.//CloseConnection();
//}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connectionAnyWhere, null, SQLString, cmdParms);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
sda.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Dispose();
sda.SelectCommand.Connection = null;
//CloseConnection();
}
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.CommandTimeout = 60;
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 返回 DataSet 或 DataTable
/// <summary>
/// 返回DataSet数据集
/// </summary>
/// <param name="strSql">SQL语句</param>
public static DataSet GetDataSet(string strSql)
{
#region
DataSet ds = new DataSet();
SqlDataAdapter sda = null;
try
{
OpenConnection();
sda = new SqlDataAdapter(strSql, connectionAnyWhere);
sda.Fill(ds);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Connection.Dispose();
//CloseConnection();
}
return ds;
#endregion
}
/// <summary>
/// 添加DataSet表 返回 DataSet
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="strSql">Sql语句</param>
/// <param name="strTableName">表名</param>
public static DataSet GetDataSet(DataSet ds, string strSql, string strTabName)
{
#region
SqlDataAdapter sda = null;
try
{
OpenConnection();
sda = new SqlDataAdapter(strSql, connectionAnyWhere); ;
sda.Fill(ds, strTabName);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Connection.Dispose();
//CloseConnection();
}
return ds;
#endregion
}
/// <summary>
/// 返回DataTable对象
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static DataTable GetDataTable(string strSql)
{
#region
DataTable dt = null;
SqlDataAdapter sda = null;
try
{
OpenConnection();
dt = new DataTable();
sda = new SqlDataAdapter(strSql, connectionAnyWhere);
sda.Fill(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Connection.Dispose();
//CloseConnection();
}
return dt;
#endregion
}
/// <summary>
/// 返回 int 类型的值 用于统计一张表中符合条件的记录条数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public static int GetDataTableRecordCount(string strSql)
{
#region
int RecordCount = 0;
SqlDataAdapter sda = null;
try
{
OpenConnection();
sda = new SqlDataAdapter(strSql, connectionAnyWhere);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null)
{
RecordCount = dt.Rows.Count;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Connection.Dispose();
//CloseConnection();
}
return RecordCount;
#endregion
}
/// <summary>
/// 返回DataView数据视图
/// </summary>
/// <param name="strSql">Sql语句</param>
public static DataView GetDataView(string strSql)
{
#region
DataView dv = GetDataSet(strSql).Tables[0].DefaultView;
return dv;
#endregion
}
/// <summary>
/// 返回SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns></returns>
public static SqlDataReader GetDataReader(string strSql)
{
#region
OpenConnection();
SqlCommand cmd = null;
SqlDataReader sdr = null;
try
{
cmd = new SqlCommand(strSql, connectionAnyWhere);
cmd.CommandTimeout = 60;
sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
throw ex;
}
//finally
//{
// cmd.Connection.Close();
// cmd.Dispose();
//}
return sdr;
#endregion
}
#endregion
#region 执行存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public static int RunProcedure(string procName)
{
#region
int reVal = 0;
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
reVal = (int)cmd.Parameters["ReturnValue"].Value;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
return reVal;
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public static DataSet RunProcedure2(string procName)
{
OpenConnection();
DataSet dataSet = null;
SqlCommand cmd = null;
SqlDataAdapter sda = null;
try
{
cmd = new SqlCommand(procName, connectionAnyWhere);
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.StoredProcedure;
sda = new SqlDataAdapter(cmd);
dataSet = new DataSet();
sda.Fill(dataSet);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sda.SelectCommand.Connection.Close();
sda.SelectCommand.Dispose();
//CloseConnection();
}
return dataSet;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回存储过程返回值</returns>
public static int RunProcedure(string procName, SqlParameter[] prams)
{
#region
int reVal = 0;
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
reVal = (int)cmd.Parameters["ReturnValue"].Value;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
return reVal;
#endregion
}
//2009.6.4
/// <summary>
/// 执行存储过程 返回 ExecuteNonQuery 方法 影响的行数
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回 ExecuteNonQuery 方法 影响的行数</returns>
public static int RunProcedureReExecuteNonQueryVal(string procName, SqlParameter[] prams)
{
#region
int reVal = 0;
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, prams);
reVal = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
return reVal;
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataTable</returns>
public static DataTable RunProcedureReDataTable(string storedProcName, SqlParameter[] parameters)
{
OpenConnection();
DataTable dt = new DataTable();
SqlDataAdapter sqlDA = null;
try
{
sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = CreateCommand(storedProcName, parameters);
sqlDA.Fill(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//sqlDA.SelectCommand.Connection.Close();
//sqlDA.SelectCommand.Connection.Dispose();
CloseConnection();
}
return dt;
}
//2009.6.4
//2009.3.2
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回存储过程返回值</returns>
public static int RunProcedure(string procName, SqlParameter[] prams, out int rowsAffected)
{
#region
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
rowsAffected = (int)cmd.Parameters["ReturnValue"].Value;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
return rowsAffected;
#endregion
}
//2009.3.2
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, SqlParameter[] parameters, string tableName)
{
OpenConnection();
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = null;
try
{
sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = CreateCommand(storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
sqlDA.SelectCommand.Connection.Close();
sqlDA.SelectCommand.Dispose();
//CloseConnection();
}
return dataSet;
}
/// <summary>
/// 执行存储过程返回DataReader对象
/// </summary>
/// <param name="procName">Sql语句</param>
/// <param name="dataReader">DataReader对象</param>
public static void RunProcedure(string procName, SqlDataReader dataReader)
{
#region
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
throw ex;
}
//finally
//{
// cmd.Connection.Close();
// cmd.Dispose();
// //CloseConnection();
//}
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <param name="dataReader">DataReader对象</param>
public static void RunProcedure(string procName, SqlParameter[] prams, SqlDataReader dataReader)
{
#region
SqlCommand cmd = null;
try
{
cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
//CloseConnection();
}
#endregion
}
/// <summary>
/// 创建一个SqlCommand对象以此来执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回SqlCommand对象</returns>
private static SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
#region
// 确认打开连接
SqlCommand cmd = null;
try
{
OpenConnection();
cmd = new SqlCommand(procName, connectionAnyWhere);
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.StoredProcedure;
// 依次把参数传入存储过程
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
}
catch (SqlException sqlex)
{
throw sqlex;
}
return cmd;
#endregion
}
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
#region
SqlParameter param = null; ;
try
{
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
}
catch (SqlException ex)
{
throw ex;
}
return param;
#endregion
}
/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
#region
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
#endregion
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
#region
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
#endregion
}
#endregion
#region 读取Excel
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="Path"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet ExcelToDS(string Path, string tableName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;"; //HDR=Yes;
DataSet ds = null;
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter myCommand = null;
try
{
conn.Open();
string strExcel = "";
strExcel = "select * from [" + tableName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
}
catch (SqlException ex)
{
throw ex;
}
finally
{
myCommand.Dispose();
conn.Close();
}
return ds;
}
}
/// <summary>
/// 将 Excel 文件转成 DataTable
/// </summary>
/// <param name="strExcelFileName">Excel文件及其路径</param>
/// <param name="strSheetName">工作表名,如:Sheet1</param>
/// <param name="isTitleOrDataOfFirstRow">True 第一行是标题,False 第一行是数据</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName, bool isTitleOrDataOfFirstRow)
{
string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
if (isTitleOrDataOfFirstRow)
{
HDR = "YES";//第一行是标题
}
else
{
HDR = "NO";//第一行是数据
}
//源的定义
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";
//Sql语句
//string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
string strExcel = "select * from [" + strSheetName + "$]";
//定义存放的数据表
DataSet ds = new DataSet();
//连接数据源
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter adapter = null;
try
{
conn.Open();
//适配到数据源
adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
adapter.SelectCommand.Connection.Close();
adapter.SelectCommand.Dispose();
}
}
return ds.Tables[strSheetName];
}
#endregion
#region 用于分页
/// <summary>
/// 分页方法 Pager2005
/// </summary>
/// <param name="tblName">表名 50</param>
/// <param name="fldName">字段名[可以是一个字段,也可以是 * ] 5000</param>
/// <param name="PageSize">一共几页</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="order">排序类型 5000</param>
/// <param name="where">查询条件 5000</param>
/// <param name="isCount">是否要返回统计记录总数</param>
/// <param name="totalRecord">记录总数</param>
/// <returns>返回 DataSet </returns>
public static DataSet GetPagerList(int pageSize, int pageIndex, string tablename, string fieldname, string where, string order, bool isCount, out int totalRecord)
{
totalRecord = 0;
SqlParameter totalPageParm = new SqlParameter("@TotalPage", SqlDbType.Int);
totalPageParm.Direction = ParameterDirection.Output;
SqlParameter totalRecordParm = new SqlParameter("@totalRecord", SqlDbType.Int);
totalRecordParm.Direction = ParameterDirection.Output;
SqlParameter[] parameters = {
new SqlParameter("@TableName", SqlDbType.VarChar, 50),
new SqlParameter("@Fields", SqlDbType.VarChar, 5000),
new SqlParameter("@OrderField", SqlDbType.VarChar, 5000),
new SqlParameter("@sqlWhere", SqlDbType.VarChar, 5000),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@isCount", SqlDbType.Bit),
totalPageParm,totalRecordParm
};
parameters[0].Value = tablename; //表、视图名称
parameters[1].Value = fieldname; //选择字段，全选用*
parameters[2].Value = order;
parameters[3].Value = where;
parameters[4].Value = pageSize;
parameters[5].Value = pageIndex;
parameters[6].Value = isCount;
DataSet ds = RunProcedure("Pager2005", parameters, "PagerTable");
if (isCount)
{
int.TryParse(totalRecordParm.Value.ToString(), out totalRecord);
}
return ds;
}
#endregion
#region SQL 分页语句 及 cs 文件方法
/*
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Procedure [dbo].[Pager2005]
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@isCount bit,
@TotalPage int output, --返回总页数
@totalRecord int output
as
begin
Begin Tran --开始事务
Declare @sql nvarchar(4000);
--Declare @totalRecord int output;
if(@isCount =1)
begin
--计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
end
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
*
/// <summary>
///分页获取数据列表
/// </summary>
/// <returns></returns>
public static DataSet GetList(int pageSize, int pageIndex,string tablename,string fieldname, string where, string order, bool isCount, out int totalRecord)
{
totalRecord = 0;
SqlParameter totalPageParm = new SqlParameter("@TotalPage", SqlDbType.Int);
totalPageParm.Direction = ParameterDirection.Output;
SqlParameter totalRecordParm = new SqlParameter("@totalRecord", SqlDbType.Int);
totalRecordParm.Direction = ParameterDirection.Output;
SqlParameter[] parameters = {
new SqlParameter("@TableName", SqlDbType.VarChar, 50),
new SqlParameter("@Fields", SqlDbType.VarChar, 5000),
new SqlParameter("@OrderField", SqlDbType.VarChar, 5000),
new SqlParameter("@sqlWhere", SqlDbType.VarChar, 5000),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@isCount", SqlDbType.Bit),
totalPageParm,totalRecordParm
};
parameters[0].Value = tablename; //表、视图名称
parameters[1].Value = fieldname; //选择字段，全选用*
parameters[2].Value = order;
parameters[3].Value = where;
parameters[4].Value = pageSize;
parameters[5].Value = pageIndex;
parameters[6].Value = isCount;
CMS.DBUtility.SQL.DBHelpSP sqp = new CMS.DBUtility.SQL.DBHelpSP();
DataSet ds = sqp.ReturnDataSet(CMS.DBUtility.SQL.Configuration.ConnAdmin, "Page2005", parameters);
if (isCount)
{
int.TryParse(totalRecordParm.Value.ToString(), out totalRecord);
}
return ds;
}
*/
#endregion
}
}