using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace PersistentSys.DAL
{
public static class DBHelper
{
//创建数据库连接字符串
private static string _conStr = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
//创建数据库连接对象
private static SqlConnection _con;
//数据库连接
public static SqlConnection Con
{
get
{
//创建连接
if (_con==null)
_con = new SqlConnection(_conStr);
//连接状态打开
if (_con.State!=ConnectionState.Open)
_con.Open();
return _con;
}
}
//获取表数据
public static DataTable GetTable(string sql)
{
SqlDataAdapter adapter = new SqlDataAdapter(sql,_conStr);
DataSet set = new DataSet();
adapter.Fill(set);
return set.Tables[0];
}
//执行增删改查的方法,使用SqlParameter传递数据
public static bool ExecuteNonQuery(string sql,List<SqlParameter> paras=null)
{
SqlCommand cmd = Con.CreateCommand();
cmd.CommandText = sql;
if (paras!=null)
cmd.Parameters.AddRange(paras.ToArray());// 添加参数列表
try
{
return cmd.ExecuteNonQuery() > 0;//返回受影响行数
}
catch (Exception e)
{
Console.WriteLine(e);
return false;
}
finally
{
cmd.Connection.Close();
}
}
}
}
以上内容是简化版。
-------------------------------------------------------------
更新一个完整版:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace MySchool.DAL { public static class DBHelper { private static string _constr = ConfigurationManager.ConnectionStrings["myschool"].ConnectionString; private static SqlConnection _con; public static SqlConnection Con { get { if (_con == null) _con = new SqlConnection(_constr); if (_con.State != ConnectionState.Open) _con.Open(); return _con; } } private static SqlCommand PrepareCMD(string sql,List<SqlParameter> paras, CommandType type) { SqlCommand cmd = Con.CreateCommand(); cmd.CommandText = sql; if (paras!=null) // 绑定 命令对象与 参数列表 cmd.Parameters.AddRange(paras.ToArray()); cmd.CommandType = type;//指定 CMD 对象操作类型(SQL文本,存储过程) return cmd; } public static object ExecuteScalar(string sql, List<SqlParameter> paras = null, CommandType type= CommandType.Text) { SqlCommand c = PrepareCMD(sql, paras,type); try { return c.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } finally { c.Connection.Close(); } } public static SqlDataReader ExecuteReader(string sql, List<SqlParameter> paras=null, CommandType type= CommandType.Text) { SqlCommand c = PrepareCMD(sql, paras, type); try { return c.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } public static bool ExecuteNonQuery(string sql,List<SqlParameter> paras=null,CommandType type = CommandType.Text) { SqlCommand c = PrepareCMD(sql,paras, type); try { return c.ExecuteNonQuery()>0; } catch (Exception ex) { Console.WriteLine(ex.Message); return false; } finally { c.Connection.Close(); } } public static DataSet GetSet(string sql) { SqlDataAdapter adapter = new SqlDataAdapter(sql, _constr); DataSet set = new DataSet(); adapter.Fill(set); return set; } public static DataTable GetTable(string sql) { return GetSet(sql).Tables[0]; } } }