using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public class DBhelper
{
string dbStr = "Data Source=.;Initial Catalog=EducationManagement;Integrated Security=True";
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//创建增删改的方法
public int ExecDML(string sql)
{
try
{
int res = 0;
//连接数据库
using (SqlConnection con = new SqlConnection(dbStr))
{
//打开连接数据库
con.Open();
//执行sql
SqlCommand com = new SqlCommand(sql, con);
res = com.ExecuteNonQuery();
}
return res;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：ExecDML" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//调用存储过程增删改的方法
public int ProcedureExecDML(string sql,SqlParameter [] parameters)
{
try
{
int res = 0;
//连接数据库
using (SqlConnection con = new SqlConnection(dbStr))
{
//执行sql
SqlCommand com = new SqlCommand(sql, con);
//设置命令的类型是存储过程的类型
com.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Length>0)
{
com.Parameters.AddRange(parameters);
}
//打开连接数据库
con.Open();
res = com.ExecuteNonQuery();
}
return res;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：PreventSqlInjectionExecDML" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//防止sql注入增删改的方法
public int PreventSqlInjectionExecDML(string sql, SqlParameter[] parameters)
{
try
{
int res = 0;
//连接数据库
using (SqlConnection con = new SqlConnection(dbStr))
{
//执行sql
SqlCommand com = new SqlCommand(sql, con);
//设置命令的类型是存储过程的类型
if (parameters != null && parameters.Length > 0)
{
com.Parameters.AddRange(parameters);
}
//打开连接数据库
con.Open();
res = com.ExecuteNonQuery();
}
return res;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：PreventSqlInjectionExecDML" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//创建查询总数的方法,即查询一行一列的数据的方法
public object ExecCount(string sql)
{
try
{
object obj = null;
using (SqlConnection con = new SqlConnection(dbStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
obj = com.ExecuteScalar();
}
return obj;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：ExecCount"+ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//读取单条或多条数据，如果是单行，直接去取值就行，如果是多行，使用for循环或foreach逐行遍历
public SqlDataReader FindDataRead(string sql)
{
try
{
SqlDataReader sdr = null;
SqlConnection con = new SqlConnection(dbStr);
con.Open();
SqlCommand com = new SqlCommand(sql, con);
sdr = com.ExecuteReader();
return sdr;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：FindDataRead" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//创建查询单表的方法
public DataTable DataTable(string sql)
{
try
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(dbStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
SqlDataAdapter sda = new SqlDataAdapter(com);
sda.Fill(dt);
}
return dt;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：DataTable" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//带参数的单表查询
public DataTable FindDataTable(string sql, SqlParameter[] sqlParameters)
{
try
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(dbStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
if (sqlParameters != null)
{
com.Parameters.AddRange(sqlParameters);
}
SqlDataAdapter sda = new SqlDataAdapter(com);
sda.Fill(dt);
}
return dt;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：DataTable" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//存储过程单表查询
public PageData FindExecDataTable(string sql, SqlParameter[] sqlParameters)
{
try
{
using (SqlConnection con = new SqlConnection(dbStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
//调用存储过程必须要加的一行
com.CommandType = CommandType.StoredProcedure;
if (sqlParameters != null&& sqlParameters.Length>0)
{
com.Parameters.AddRange(sqlParameters);
}
//获取存储过程的返回值
SqlParameter countpageOutPut = com.Parameters.Add("@totalPage", SqlDbType.Int);
countpageOutPut.Direction = ParameterDirection.Output;
SqlParameter countOutPut = com.Parameters.Add("@totalRow", SqlDbType.Int);
countOutPut.Direction = ParameterDirection.Output;
SqlDataAdapter sda = new SqlDataAdapter(com);
DataTable dt = new DataTable();
sda.Fill(dt);
//获取返回值
string count = countOutPut.Value.ToString();
string countpage = countpageOutPut.Value.ToString();
PageData pageData = new PageData();
pageData.DataTable = dt;
pageData.Count = int.Parse(count);
pageData.Countpage = int.Parse(countpage);
return pageData;
}
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：DataTable" + ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//查询创建多表的方法
public DataSet DataSet(string sql)
{
try
{
DataSet dst = new DataSet();
using (SqlConnection con = new SqlConnection(dbStr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
SqlDataAdapter sda = new SqlDataAdapter(com);
sda.Fill(dst);
}
return dst;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：DataSet"+ex);
throw ex;
}
}
/// <summary>
/// 模块：DBhelper
/// 作者：杨萍
/// 时间：2020-4-1
/// </summary>
//创建查询多表的方法
public SqlDataAdapter SqlDataAdapter(string sql)
{
try
{
SqlDataAdapter sda = null;
SqlConnection con = new SqlConnection(dbStr);
con.Open();
SqlCommand com = new SqlCommand(sql, con);
sda = new SqlDataAdapter(com);
return sda;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper：SqlDataAdapter"+ex);
throw ex;
}
}
}
}