using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
namespace SqlHelper_MSSQL
{
#region 对数据库操作的助手类SqlHelper
/// <summary>
/// 对数据库操作的助手类
/// </summary>
public class SqlHelper
{
/// <summary>
/// 获得一个数据库连接对象
/// </summary>
/// <returns>返回一个指向数据库连接对象的实例</returns>
private static IDbConnection GetIDbConnectionn()
{
//此处返回连接数据库对象可根据需要选择(修改此处实现多数据库操作)
return new SqlConnection("数据库连接");
}
/// <summary>
/// 获取一个数据源适配器对象
/// </summary>
/// <param name="cmd">传入的IDbCommand对象</param>
/// <returns></returns>
private static IDbDataAdapter GetDataAdapter(IDbCommand cmd)
{
//此处返回DataSet适配器对象可根据需要选择(修改此处实现多数据库操作)
return new SqlDataAdapter((SqlCommand)cmd);
}
/// <summary>
/// 获取当前应用程序的数据库连接对象的实例
/// </summary>
public IDbConnection GetIDbConnection
{
get
{
return GetIDbConnectionn();
}
}
/// <summary>
/// 对数据源执行指定的查询并返回DataSet类型的结果集
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回DataTable类型的结果集</returns>
public static DataTable ExcuteDataTable(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
PushSqlParamter(cmd, param);
DataSet ds = new DataSet();
IDbDataAdapter adapter = GetDataAdapter(cmd);
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
/// <summary>
/// 对数据源执行指定存储过程并返回DataSet类型的结果集
/// </summary>
/// <param name="sql">要执行的存储过程名称</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回DataTable类型的结果集</returns>
public static DataTable ExcuteDataTablePro(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
PushSqlParamter(cmd, param);
DataSet ds = new DataSet();
IDbDataAdapter adapter = GetDataAdapter(cmd);
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
/// <summary>
/// 对数据源执行指定的查询并返回一行一列的结果集
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回一行一列的Object类型结果集</returns>
public static object ExcuteScalcr(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
PushSqlParamter(cmd, param);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 对数据源执行指定存储过程并返回一行一列的结果集
/// </summary>
/// <param name="sql">要执行的存储过程名称</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回一行一列的Object类型结果集</returns>
public static object ExcuteScalcrPro(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
PushSqlParamter(cmd, param);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 对数据源执行指定的查询并返回受影响的行数
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExcuteNonQuery(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
PushSqlParamter(cmd, param);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 对数据源执行指定存储过程并返回受影响的行数
/// </summary>
/// <param name="sql">要执行存储过程名称</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExcuteNonQueryPro(string sql, params IDbDataParameter[] param)
{
using (IDbConnection sqlconn = GetIDbConnectionn())
{
sqlconn.Open();
using (IDbCommand cmd = sqlconn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
PushSqlParamter(cmd, param);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 对数据源执行指定的查询并返回一个IDataReader
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回一个IDataReader</returns>
public static IDataReader ExcuteReader(string sql, params IDbDataParameter[] param)
{
IDbConnection sqlconn = GetIDbConnectionn();
sqlconn.Open();
IDbCommand cmd = sqlconn.CreateCommand();
cmd.CommandText = sql;
PushSqlParamter(cmd, param);
return cmd.ExecuteReader();
}
/// <summary>
/// 对数据源执行指定存储过程并返回一个IDataReader
/// </summary>
/// <param name="sql">要执行的存储过程名称</param>
/// <param name="param">传入的SqlParameter参数</param>
/// <returns>返回一个IDataReader</returns>
public static IDataReader ExcuteReaderPro(string sql, params IDbDataParameter[] param)
{
IDbConnection sqlconn = GetIDbConnectionn();
sqlconn.Open();
IDbCommand cmd = sqlconn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
PushSqlParamter(cmd, param);
return cmd.ExecuteReader();
}
/// <summary>
/// 将SqlParamter参数赋值给IDbCommand对象
/// </summary>
/// <param name="cmd">要赋值的IDbCommand对象</param>
/// <param name="param">添加的SqlParamter对象</param>
private static void PushSqlParamter(IDbCommand cmd, params IDbDataParameter[] param)
{
if (param.Length > 0)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}
}
}
#endregion
}
做项目中经常会对数据库执行操作,所以就写了这个数据库操作助手类封装大部分常用的操作,网上也有大量的SqlHelper
我做了些特殊处理,可以对多种数据库操作而不用改代码
Code: