using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace VaccinationManger.DAL
{
public class SqlHelper
{
private string constr = "server=.;database=VaccinationDB;uid=sa;pwd=1169";
//private string constr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlString"].ToString();
//获取Web.Config文件中的连接字符串
//private string constr = System.Configuration.ConfigurationSettings.AppSettings["sqlstr"].ToString();
//private string constr = DataProvider.Constr;
/// <summary>
/// 创建连接对象
/// </summary>
/// <returns>返回连接对象</returns>
public SqlConnection CreateSqlConnection()
{
return new SqlConnection(constr);//匿名对象写法
}
/// <summary>
/// 创建命令对象
/// </summary>
/// <param name="cmdtext">Sql文本命令</param>
/// <param name="con">连接对象</param>
/// <returns>返回命令对象</returns>
public SqlCommand CreateSqlCommand(string cmdtext,SqlConnection con)
{
return new SqlCommand(cmdtext, con);
}
/// <summary>
/// 创建命令对象
/// </summary>
/// <param name="cmdtext">Sql文本命令</param>
/// <param name="con">连接对象</param>
/// <param name="sp">参数对象数组</param>
/// <returns>返回命令对象</returns>
public SqlCommand CreateSqlCommand(string cmdtext, SqlConnection con,SqlParameter[] sp)
{
SqlCommand com= new SqlCommand(cmdtext, con);
com.Parameters.AddRange(sp);
return com;
}
/// <summary>
/// 创建命令对象
/// </summary>
/// <param name="cmdtext">文本命令或存储过程名称</param>
/// <param name="ct">决定是存储过程还是文本命令</param>
/// <param name="con">连接对象</param>
/// <returns>返回命令对象</returns>
public SqlCommand CreateSqlCommand(string cmdtext,CommandType ct, SqlConnection con)
{
SqlCommand com= new SqlCommand(cmdtext, con);
com.CommandType = ct;
return com;
}
/// <summary>
/// 创建命令对象
/// </summary>
/// <param name="cmdtext">文本命令或存储过程名称</param>
/// <param name="ct">决定是存储过程还是文本命令</param>
/// <param name="con">连接对象</param>
/// <param name="sp">参数对象数组</param>
/// <returns>返回命令对象</returns>
public SqlCommand CreateSqlCommand(string cmdtext,CommandType ct, SqlConnection con, SqlParameter[] sp)
{
SqlCommand com = new SqlCommand(cmdtext, con);
com.Parameters.AddRange(sp);
com.CommandType = ct;
return com;
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="cmdtext">Sql文本命令</param>
/// <param name="sp">参数数组</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string cmdtext, SqlParameter[] sp)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com=this.CreateSqlCommand(cmdtext, con, sp);
con.Open();
int temp=com.ExecuteNonQuery();
con.Close();
return temp;
}
public int ExecuteNonQuery(string cmdtext,CommandType ct,SqlParameter[] sp)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, ct,con, sp);
con.Open();
int temp = com.ExecuteNonQuery();
con.Close();
return temp;
}
public int ExecuteNonQuery(string cmdtext)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, con);
con.Open();
int temp = com.ExecuteNonQuery();
con.Close();
return temp;
}
public int ExecuteNonQuery(string cmdtext, CommandType ct)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, ct, con);
con.Open();
int temp = com.ExecuteNonQuery();
con.Close();
return temp;
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="cmdtext">文本命令</param>
/// <param name="tablename">表名</param>
/// <returns>返回数据集</returns>
public DataSet ExecuteDataSet(string cmdtext,string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, con);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
public DataSet ExecuteDataSet(string cmdtext,CommandType ct, string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext,ct, con);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
/// <summary>
///
/// </summary>
/// <param name="cmdtext"></param>
/// <param name="ct">命令类型</param>
/// <param name="sp">参数数组</param>
/// <param name="tablename"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string cmdtext, CommandType ct,SqlParameter[] sp, string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, ct, con,sp);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="cmdtext">文本命令</param>
/// <param name="tablename">表名</param>
/// <returns>返回数据集</returns>
public DataSet ExecuteDataSet(string cmdtext,DataSet ds, string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, con);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
//DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
public DataSet ExecuteDataSet(string cmdtext, CommandType ct, DataSet ds,string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, ct, con);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
// DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
/// <summary>
///
/// </summary>
/// <param name="cmdtext"></param>
/// <param name="ct">命令类型</param>
/// <param name="sp">参数数组</param>
/// <param name="tablename"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string cmdtext, CommandType ct, SqlParameter[] sp,DataSet ds,string tablename)
{
SqlConnection con = this.CreateSqlConnection();
SqlCommand com = this.CreateSqlCommand(cmdtext, ct, con, sp);
SqlDataAdapter da = new SqlDataAdapter(com);
//da.SelectCommand = com;
// DataSet ds = new DataSet();
da.Fill(ds, tablename);
con.Close();
return ds;
}
public object ExecuteScalar(string cmdtext,SqlParameter[] sp)
{
using(SqlConnection con = this.CreateSqlConnection())
{
SqlCommand com = this.CreateSqlCommand(cmdtext, CommandType.StoredProcedure, con, sp);
con.Open();
return com.ExecuteScalar();
}
}
}
}