c#sql_oracle_mysql 封装dbhelper_【SQLServer】DBHelper即Ｃ＃数据库底层封装

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

namespace MYHelper

{

//不加public，只有dal私有引用；

//封装执行sql server增删改查辅助类，提供相应执行sql语句和存储过程的方法

//作者：白宁超(http://www.cnblogs.com/baiboy/)

static class SQLHelper

{

//读取配置文件中的连接字符串

static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

///

/// 执行非查询sql语句，返回受影响行数，如果执行非增删改则返回-1

///

/// sql语句

/// 参数数组

/// 影响行数res

public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)

{

int res = -1;

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

cmd.CommandType = CommandType.StoredProcedure;

if (paras != null || paras.Length > 0)

{

cmd.Parameters.AddRange(paras);

}

conn.Open();

res = cmd.ExecuteNonQuery();

}

}

return res;

}

///

/// 执行非查询sql语句，返回受影响行数，如果执行非增删改则返回-1

///

/// sql语句

/// 参数数组

/// 影响行数res

public static int ExecuteNonParaQuery(string sql)

{

int res = -1;

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

conn.Open();

res = cmd.ExecuteNonQuery();

}

}

return res;

}

///

/// 执行读取数据，返回一个对象

///

/// sql语句

/// 参数数组

/// 返回一个对象o

public static object ExecuteScalar(string sql, params SqlParameter[] paras)

{

object o = null;

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

cmd.CommandType = CommandType.StoredProcedure;

if (paras != null)

{

cmd.Parameters.AddRange(paras);

}

conn.Open();

o = cmd.ExecuteScalar();

}

}

return o;

}

///

/// 执行查询sql语句，返回一个对象

///

/// sql语句

/// 查询参数

/// 返回DataReader对象

public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras)

{

SqlConnection conn = new SqlConnection(connstr);

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

cmd.CommandType = CommandType.StoredProcedure;

if (paras != null)

{

cmd.Parameters.AddRange(paras);

}

conn.Open();

try

{

return cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (Exception ex)

{

cmd.Dispose();

throw ex;

}

}

}

///

/// 执行查询sql语句，返回一个无参数dataset对象

///

/// sql语句

///

/// 返回dataset 对象

public static DataSet GetDataSetNotPara(string sql)

{

DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

//根据传来的参数。决定是sql语句还是存储过程

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

sda.Fill(ds);

}

}

}

return ds;

}

///

/// 执行查询sql语句，返回一个无参数dataset对象

///

/// sql语句

///

/// 返回dataset 对象

public static DataTable GetDataTableNotPara(string sql)

{

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

//根据传来的参数。决定是sql语句还是存储过程

conn.Open();

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

sda.Fill(dt);

}

}

}

return dt;

}

///

/// 执行查询sql语句，返回一个dataset对象

///

/// sql语句

/// 查询参数

/// 返回dataset 对象

public static DataSet GetDataSet(string sql, params SqlParameter[] paras)

{

DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

//根据传来的参数。决定是sql语句还是存储过程

cmd.CommandType = CommandType.StoredProcedure;

//添加参数

cmd.Parameters.AddRange(paras);

conn.Open();

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

sda.Fill(ds);

}

}

}

return ds;

}

///

/// 可以执行sql语句或存储过程

///

///

///

///

///

public static DataTable ProcGetTable(string sql, params SqlParameter[] param)

{

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

//根据传来的参数。决定是sql语句还是存储过程

cmd.CommandType = CommandType.StoredProcedure;

//添加参数

cmd.Parameters.AddRange(param);

//cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = param[0];

//cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 20).Value = param[1];

conn.Open();

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

sda.Fill(dt);

}

}

}

return dt;

}

///

/// 实现分页功能

///

/// sql语句

/// 参数数组(显示index页和每页显示条数size)

/// 查询结果

public static DataTable GetParaTable(string sql, params SqlParameter[] paras)

{

DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connstr))

{

using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))

{

if (paras != null)

{

da.SelectCommand.Parameters.AddRange(paras);

}

da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.Fill(ds);

}

}

return ds.Tables[0];

}

}

}