using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public class DBHelper
{
//连接字符串 【注意:Data Source指:当前计算机域名(一般写一个“.”即可),Initial Catalog指:连接的数据库名称,Integrated Security=True指:数据库的身份认证方式(Windows身份认证),Persist Security Info=True;指:SQL Server身份认证(后面跟随的是账号与密码)】
//public static string connStr = "Data Source=.;Initial Catalog=XXXX;Persist Security Info=True;User ID=sa;Password=JSB903..";
//public static string connStr = "Data Source=.;Initial Catalog=Shicaiwang;Integrated Security=True";
//连接对象
public static SqlConnection conn = null;
//连接开发方法
public static void Open()
{
if(conn == null)
{
conn = new SqlConnection(connStr);
}
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
}
//连接关闭方法
public static void Close()
{
if(conn == null)
{
return;
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
//查询---DataReader方法
public static SqlDataReader GetData(string sql)
{
Open();
SqlCommand cmd = new SqlCommand(sql,conn);
return cmd.ExecuteReader();
}
//查询---DataTable方法
public static DataTable GetTableData(string sql)
{
Open();
SqlDataAdapter sa = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
sa.Fill(dt);
sa.Dispose();
conn.Close();
return dt;
}
//执行Sql聚合函数
public static object ExecuteScalar(string sql)
{
Open();
SqlCommand cmd = new SqlCommand(sql, conn);
object result = cmd.ExecuteScalar();
conn.Close();
return result;
}
//带参数的Sql语句
public static object ExecuteScalar(string sql,SqlParameter[] paras)
{
Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(paras);
object result = cmd.ExecuteScalar();
conn.Close();
return result;
}
//【增删改】通用方法
public static bool Update(string sql)
{
Open();
SqlCommand cmd = new SqlCommand(sql, conn);
return cmd.ExecuteNonQuery() > 0; //返回影响行数
}
}
}