using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DBUlititly
{
/// <summary>
/// 操作SQL Server数据库通用访问类
/// </summary>
public static class SQLHelper
{
//sql连接 调用APP.config连接字符串
//SqlConnection连接参数是connString
public static string connString= ConfigurationManager.ConnectionStrings["connString"].ToString();
#region 标准sql语句
//1.对数据库增、删、改,操作方法
public static int Update(string sql) //返回一个受影响行数
{
//实现实例化连接SqlConnection
SqlConnection conn = new SqlConnection(connString);
//实例化sqlcommand
//SqlCommand是数据库命令类
SqlCommand cmd = new SqlCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();//返回受影响的行数
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//获取单一结果(第一行第一列)
public static object GetOneResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();//执行查询,查询返回的结果集中的第一行的第一列
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//获取结果集 SqlDataReader类型
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection); //返回结果一个SqlDataReader对象
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#region 带参数的sql语句
//实现对数据库增删改
public static int Update(string sql,SqlParameter [] para)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql,conn);
try
{
conn.Open();
if(para!=null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();//返回结果,返回值受影响的行数 int类型
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
// //获取单一结果(第一行第一列)
public static object GetOneResult(string sql,SqlParameter[]para)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
if(para!=null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteScalar();//返回结果,返回值是第一行第一列,object
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//获取结果集 SqlDataReader类型
public static SqlDataReader GetReader(string sql,SqlParameter[]para)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
if(para!=null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
/// <summary>
/// 获取数据库服务器时间
/// </summary>
/// <returns></returns>
public static DateTime GetServerTime()
{
//sql语句:获取sqlserver数据库时间
string sql = "select getdate()";
return Convert.ToDateTime(GetOneResult(sql));//返回第一行第一列结果
}
}
}