DBHelper类
记得引用System.Configuration
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TestDAL
{
public class DBHelperDAL
{
//数据库链接字符串
public static string ConnString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//数据库连接对象
public static SqlConnection Conn = null;
//初始化数据库连接
public static void InitConnection()
{
//如果连接对象不存在,则创建连接
if (Conn == null)
{
Conn = new SqlConnection(ConnString);
}
//如果连接对象关闭,则打开连接
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
//如果连接中断,则重启连接
if (Conn.State == ConnectionState.Broken)
{
Conn.Close();
Conn.Open();
}
}
//查询,获取DataReader
public static SqlDataReader GetDataReader(string sqlStr)
{
InitConnection();
SqlCommand cmd = new SqlCommand(sqlStr, Conn);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//查询,获取DataSet
public static DataSet GetDataSet(string sqlStr)
{
InitConnection();
DataSet ds = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter(sqlStr, Conn);
dap.Fill(ds);
Conn.Close();
return ds;
}
//查询,获取DataTable
public static DataTable GetDataTable(string sqlStr)
{
return GetDataSet(sqlStr).Tables[0];
}
//增删改
public static bool ExecuteNonQuery(string sqlStr)
{
InitConnection();
SqlCommand cmd = new SqlCommand(sqlStr, Conn);
int result = cmd.ExecuteNonQuery();
Conn.Close();
return result > 0;
}
//执行集合函数
public static string ExectuteScalar(string sqlStr)
{
InitConnection();
SqlCommand cmd = new SqlCommand(sqlStr, Conn);
string result = cmd.ExecuteScalar().ToString();
Conn.Close();
return result;
}
}
}
web.config 配置
<connectionStrings>
<add name="DefaultConnection" providerName="System.Data.sqlClient" connectionString="server=.;database=DBTest;uid=sa;pwd=123456;"/>
</connectionStrings>