DBherpel 类的使用方法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class DBherpel
{
//查询一和查询二是相同的功能用的方法不同
//连接字符
private static string strConn = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
//查询1
public static DataTable ExecQuery(string sql, params SqlParameter[] parameters)
{
//using加载完之后自动关闭
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, strConn))
{
using (DataTable table = new DataTable())
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
//将参数集合加载到SelectCommand
dataAdapter.SelectCommand.Parameters.AddRange(parameters);
}
try
{
dataAdapter.Fill(table);
}
catch (Exception ex) //执行出错,释放资源
{
table.Dispose();
dataAdapter.Dispose();
//抛出异常
throw ex;
}
return table;
}
}
}
//查询2
public static SqlDataReader ExecReader(string sql, CommandType type = CommandType.Text, params SqlParameter[] parameters)
{
//创建连接对象
//【为什么不能使用using?】
SqlConnection sqlConnection = new SqlConnection(strConn);
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
try
{
sqlConnection.Open();
//将Reader与Connection进行绑定,关闭Reader的同时释放Connection
return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex) //执行出错,释放资源
{
sqlCommand.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
}
}
//非查询
public static int ExecNonQuery(string sql, CommandType type = CommandType.Text, params SqlParameter[] parameters)
{
using (SqlConnection sqlConnection = new SqlConnection(strConn))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
sqlCommand.CommandType = type;
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
var num = 0;
try
{
sqlConnection.Open();
//返回受影响的行数
num=sqlCommand.ExecuteNonQuery();
}
catch //(Exception ex)
{
num = 0;
//throw ex;
}
finally
{
sqlConnection.Close();
}
return num;
}
}
}
}
}