首先需要引用命名空间 ,同时也需要右击'引用' --> '添加引用' --> '程序集' --> '框架' --> 'System.Configuration',SqlHelper属于三层中的DAL层：
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class SqlHelper { private static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString(); public static SqlConnection Open() //存储过程 { SqlConnection con = new SqlConnection(conStr); con.Open(); return con; } /// <summary> /// Insert update delete /// </summary> /// <param name="commandText">SQL</param> /// <param name="type">命令类型</param> /// <param name="pars">参数列表</param> /// <returns></returns> public static int ExecuteNoQuery(string commandText, CommandType type, params SqlParameter[] pars) { int result = 0; using (SqlConnection con = new SqlConnection(conStr)) { con.Open(); SqlCommand cmd = new SqlCommand(commandText, con); cmd.CommandType = type; if (pars != null) { cmd.Parameters.AddRange(pars); } result = cmd.ExecuteNonQuery(); } return result; } /// <summary> /// select /// </summary> /// <param name="commandText"></param> /// <param name="type"></param> /// <param name="pars"></param> /// <returns></returns> public static DataTable ExecuteDataTable(string commandText, CommandType type, params SqlParameter[] pars) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(conStr)) { con.Open(); SqlCommand cmd = new SqlCommand(commandText, con); cmd.CommandType = type; if (pars != null) { cmd.Parameters.AddRange(pars); } SqlDataAdapter adpt = new SqlDataAdapter(cmd); adpt.Fill(ds); dt = ds.Tables[0]; } return dt; } private static SqlConnection con = null; public static void CloseCon() { if (con != null && con.State == ConnectionState.Open) { con.Close(); } } /// <summary> /// select /// </summary> /// <param name="commandText"></param> /// <param name="type"></param> /// <param name="pars"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(string commandText, CommandType type, SqlParameter[] pars) { SqlCommand cmd = null; SqlDataReader reader = null; try { con = new SqlConnection(conStr); con.Open(); cmd = new SqlCommand(commandText, con); if (pars != null) { cmd.Parameters.AddRange(pars); } reader = cmd.ExecuteReader(); } catch (SqlException ex) { } return reader; } public static object ExecScalre(string commandText, CommandType type, SqlParameter[] pars) { object obj = null; using (SqlConnection con = new SqlConnection(conStr)) { con.Open(); SqlCommand cmd = new SqlCommand(commandText, con); cmd.CommandType = type; if (pars != null) { cmd.Parameters.AddRange(pars); } obj = cmd.ExecuteScalar(); } return obj; }
Web.config 代码如下:
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="conStr" connectionString="Data Source=LS--20161126TNE;Initial catalog=TestOne;User Id = sa ;Pwd=as123123"></add>
</connectionStrings>
</configuration>
