DBHelper类

1.先在Web.config 文件中加入要连接的数据库

<connectionStrings >
<add name="logisDb" connectionString="Data Source=.;Initial Catalog=logis;Integrated Security=True"/>
</connectionStrings>
————————————————

2.编写DBHelper类
先引入命名空间：using System.Configuration;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
namespace HP_DAL
{
public class DBHelper
{
/// <summary>
/// 连接字符串
/// </summary>
private readonly static string config = ConfigurationManager.ConnectionStrings["logisDb"].ConnectionString;
/// <summary>
/// 离线查询，返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public static DataTable ExecuteTable(string sql,params SqlParameter[] par)
{
using (SqlDataAdapter sda = new SqlDataAdapter(sql, config))
{
if (par != null && par.Length > 0)
{
sda.SelectCommand.Parameters.AddRange(par);
}
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}

}

//调用 ExecuteTable

 

string sql = @"Select * from [User] Where Id=@id";
int m = Convert.ToInt32( Request.QueryString["id"]);
SqlParameter[] Para = { new SqlParameter("@id",m) };
DataTable dt = DbHelper.ExecuteTable(sql,Para);
foreach (DataRow row in dt.Rows)
{
User uu = new User();
uu.Id = Convert.ToInt32( row["Id"]);
uu.Name = row["Name"].ToString();
uu.Password = row["Password"].ToString();
userList.Add(uu);
}

 

 

 

 

 

 

 

 

/// <summary>
/// 查询首行首列，返回object
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] par)
{
using (SqlConnection con = new SqlConnection(config))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
if (par != null && par.Length > 0)
{
com.Parameters.AddRange(par);
}
if (con.State!=ConnectionState.Open)
{
con.Open();
}
return com.ExecuteScalar();
}
}
}
/// <summary>
/// 在线查询，返回SqlDataReader，存储过程
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader1(string procname, params SqlParameter[] par)
{
SqlConnection con = new SqlConnection(config);

SqlCommand com = new SqlCommand(procname, con);

com.CommandType = CommandType.StoredProcedure;
if (par != null && par.Length > 0)
{
com.Parameters.AddRange(par);
}
if (con.State != ConnectionState.Open)
{
con.Open();
}
return com.ExecuteReader(CommandBehavior.CloseConnection);
}


/// <summary>
/// 在线查询，返回SqlDataReader  不用 Using  回收机制 因为有   CommandBehavior.CloseConnection
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>

public static SqlDataReader ExecuteDataReader(string sql,params SqlParameter[] para)
{

SqlConnection con = new SqlConnection(Connect);
SqlCommand cmd = new SqlCommand(sql, con);

if (para != null && para.Length > 0)
{
cmd.Parameters.AddRange(para);
}
if (con.State != ConnectionState.Open)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

// 调用 ExecuteReader ()

string sql = @"Select * from [User]";
SqlDataReader sdr = DBHelper.ExecuteDataReader(sql);
List<User> userList = new List<User>();
while (sdr.Read())
{
User usred = new User();
usred.Id = Convert.ToInt32(sdr["Id"]);
usred.Name = sdr["Name"].ToString();
usred.Password = sdr["Password"].ToString();
userList.Add(usred);
}
sdr.Close();

 

 

 

 

 

 

 
/// <summary>
/// 增删改方法
/// </summary>
/// <param name="sql"></param>
public static int ExecuteNonQuery(string sql, params SqlParameter[] par)
{
using (SqlConnection con = new SqlConnection(config))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
if (par != null && par.Length > 0)
{
com.Parameters.AddRange(par);
}
if (con.State != ConnectionState.Open)
{
con.Open();
}
return com.ExecuteNonQuery();
}
}
}
/// <summary>
/// 增删改方法，存储过程
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public static int ExecuteNonQueryProc(string sql, params SqlParameter[] par)
{
using (SqlConnection con = new SqlConnection(config))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandTimeout = 60;
com.CommandType = CommandType.StoredProcedure;
if (par != null && par.Length > 0)
{
com.Parameters.AddRange(par);
}
if (con.State != ConnectionState.Open)
{
con.Open();
}
return com.ExecuteNonQuery();
}
}
}

 

 

//Dataset       调用 Dataset.Tables[0].rows   或者 Dataset.Tables["表名"].rows  循环遍历值

public static DataSet GetDataSet(string sql, params SqlParameter[] paras)
{
DataSet ds = null;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);
ds = new DataSet();
adapter.Fill(ds);
}
return ds;
}

}
}