using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LX_Project
{
class DBHelper
{
//一个简单实用的数据库访问类
public class DbHelper
{
private static SqlConnection cnn = null;
public static SqlConnection Cnn
{
get
{
if (cnn == null)
{
cnn = new SqlConnection("server=.;uid=sa;pwd=111;database=MyOffice;MultipleActiveResultSets=true;");
cnn.Open();
//cnn = ConfigurationManager.ConnectionStrings["OAConnectionString"].ConnectionString;
//cnn.Open();
}
else if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
else if (cnn.State == ConnectionState.Broken)
{
cnn.Close();
cnn.Open();
}
return cnn;
}
}
//2.增删改
public int CommanNonQuery(string sql)
{
SqlCommand cmm = new SqlCommand(sql, Cnn);
return cmm.ExecuteNonQuery();
}
//*******************调用存储过程
public static int ComandNonQuery(string proname, SqlParameter[] sp)
{
SqlCommand cmm = new SqlCommand(proname, Cnn);
cmm.CommandType = CommandType.StoredProcedure;
if (sp != null)
cmm.Parameters.AddRange(sp);
return cmm.ExecuteNonQuery();
}
//3. a：统计
public static object CommandScalar(string sql)
{
SqlCommand cmm = new SqlCommand(sql, Cnn);
return cmm.ExecuteScalar();
}
//*******************调用存储过程
public object ComandScalar(string proname, SqlParameter[] sp)
{
SqlCommand cmm = new SqlCommand(proname, Cnn);
cmm.CommandType = CommandType.StoredProcedure;
if (sp != null)
cmm.Parameters.AddRange(sp);
return cmm.ExecuteScalar();
}
//b:记录集
public SqlDataReader CommandReader(string sql)
{
SqlCommand cmm = new SqlCommand(sql, Cnn);
return cmm.ExecuteReader();
}
//*******************调用存储过程
public static SqlDataReader ComandDataReader(string proname, SqlParameter[] sp)
{
SqlCommand cmm = new SqlCommand(proname, Cnn);
cmm.CommandType = CommandType.StoredProcedure;
if (sp != null)
cmm.Parameters.AddRange(sp);
return cmm.ExecuteReader();
}
//c:数据表
public DataTable CommandDataTable(string sql)
{
SqlCommand cmm = new SqlCommand(sql, Cnn);
SqlDataAdapter sdp = new SqlDataAdapter(cmm);
DataTable tb = new DataTable();
sdp.Fill(tb);
return tb;
}
//调用存储过程
public static DataTable CommandDataTable(string proname, SqlParameter[] sp)
{ //2.命令对象
SqlCommand cmm = new SqlCommand(proname, cnn);
cmm.CommandType = CommandType.StoredProcedure;
if (sp != null)
cmm.Parameters.AddRange(sp);
//3.适配器对象
SqlDataAdapter sdp = new SqlDataAdapter(cmm);
//4.填充数据
DataTable bt = new DataTable();
sdp.Fill(bt);
return bt;
}
//4.关闭连接
public void CloseConnection()
{
if (cnn.State == ConnectionState.Open)
cnn.Close();
}
}
}
}
