自己写的dbhelper有意思可以参考纯属性调用流 - 黑马程序员技术交流社区

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
namespace SqlExcelServices
{
public class DBHelper
{
/// <summary>
/// 连接字符串
/// </summary>
public string CmdConStr { get; set; }
/// <summary>
/// 存储过程名
/// </summary>
public string CmdProStr { get; set; }
/// <summary>
/// 传入参数new SqlParameter[] {new SqlParameter("@存储过程参数",参数)};
/// </summary>
public SqlParameter[] CmdPar { get; set; }
private SqlConnection con = new SqlConnection();
private SqlCommand cmd = new SqlCommand() { CommandType = CommandType.StoredProcedure };
private SqlDataReader dr;
/// <summary>
/// 返回遍历需要的DR
/// </summary>
protected SqlDataReader Dr
{
get {
if (dr==null)
{
this.GetdataReader();
}
return dr; }
}
private bool isExecuteNonQuery;
/// <summary>
/// 返回增改删的BOOL值
/// </summary>
protected bool IsExecuteNonQuery
{
get
{
this.GetExecuteNonQuery();
return isExecuteNonQuery; }
}
private bool isExecuteScalar;
/// <summary>
/// 返回登陆判断的BOOL或只需要一个判断时 SQL语句列名改成1 比如: select 1 from 表 where 列=列
/// </summary>
protected bool IsExecuteScalar
{
get
{
this.GetExecuteScalar();
return isExecuteScalar; }
}
/// <summary>
/// 返回遍历需要的DR
/// </summary>
/// <param name="constring">连接字符串</param>
/// <param name="protrct">存储过程名</param>
/// <param name="par">参数数组写法 new SqlParameter[]{ new SqlParameter("@存储过程参数名",参数)}</param>
/// <returns></returns>
private void GetdataReader()
{
con.ConnectionString = ConfigurationManager.ConnectionStrings[CmdConStr].ToString();
if (con.State != ConnectionState.Open || con.State == ConnectionState.Broken)
{
dr = null;
con.Close();
con.Open();
}
cmd.Connection = con;
cmd.CommandText = CmdProStr;
if (CmdPar != null)
{
cmd.Parameters.AddRange(this.CmdPar);
}
dr = cmd.ExecuteReader();
}
/// <summary>
/// 返回增改删的BOOL值
/// </summary>
/// <param name="constring">连接字符串</param>
/// <param name="protrct">存储过程名</param>
/// <param name="par">参数数组写法 new SqlParameter[]{ new SqlParameter("@存储过程参数名",参数)}</param>
/// <returns></returns>
private void GetExecuteNonQuery()
{
con.ConnectionString = ConfigurationManager.ConnectionStrings[CmdConStr].ToString();
if (con.State != ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close();
con.Open();
}
cmd.Connection = con;
cmd.CommandText = CmdProStr;
if (CmdPar != null)
{
cmd.Parameters.AddRange(CmdPar);
}
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
isExecuteNonQuery= true;
return;
}
isExecuteNonQuery=false;
}
/// <summary>
/// 返回登陆判断的BOOL或只需要一个判断时 SQL语句列名改成1 比如: select 1 from 表 where 列=列
/// </summary>
/// <param name="constring">连接字符串</param>
/// <param name="protrct">存储过程名</param>
/// <param name="par">参数数组写法 new SqlParameter[]{ new SqlParameter("@存储过程参数名",参数)}</param>
private void GetExecuteScalar()
{
con.ConnectionString = ConfigurationManager.ConnectionStrings[CmdConStr].ToString();
if (con.State != ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close();
con.Open();
}
cmd.Connection = con;
cmd.CommandText = CmdProStr;
if (CmdPar != null)
{
cmd.Parameters.AddRange(CmdPar);
}
int i = Convert.ToInt32(cmd.ExecuteScalar());
if (i > 0)
{
isExecuteScalar= true;
return;
}
isExecuteScalar= false;
}
}
}

复制代码