为了方便维护,我们在Web.config中写连接数据库的字符串,代码如下:
- <connectionStrings>
- <add name="connstr" connectionString="server=(local);database = newsstem; uid = sa;pwd=123456"/>
- </connectionStrings>
- /*
- *创建人:屈文哲
- *创建时间:2013-12-23
- *说明:数据库的助手类
- *版权声明:信息技术提高班
- */
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- namespace DAL
- {
- public class SQLHelper
- {
- private SqlConnection conn = null;
- private SqlCommand cmd = null;
- private SqlDataReader sdr = null;
- public SQLHelper()
- {
- string connStr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
- conn = new SqlConnection(connStr);
- }
- private SqlConnection GetConn()
- {
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
- return conn;
- }
- ///<summary>
- ///执行不带参数的增删改SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText,CommandType ct)
- {
- int res;
- try
- {
- cmd = new SqlCommand(cmdText, GetConn());
- cmd.CommandType = ct;
- res = cmd.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- if (conn.State == ConnectionState.Open)
- {
- conn.Close();
- }
- }
- return res;
- }
- ///<summary>
- ///执行不带参数的查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText,CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText, GetConn());
- cmd.CommandType = ct;
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- ///<summary>
- ///执行带参数的增删改SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">增删改SQL语句或存储过程</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public int ExecuteNonQuery(string cmdText, SqlParameter[] paras,CommandType ct)
- {
- int res;
- using (cmd = new SqlCommand(cmdText, GetConn()))
- {
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- res = cmd.ExecuteNonQuery();
- }
- return res;
- }
- ///<summary>
- ///执行带参数的查询SQL语句或存储过程
- /// </summary>
- /// <param name="cmdText">查询SQL语句或存储过程</param>
- /// <param name="paras">参数集合</param>
- /// <param name="ct">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras,CommandType ct)
- {
- DataTable dt = new DataTable();
- cmd = new SqlCommand(cmdText, GetConn());
- cmd.CommandType = ct;
- cmd.Parameters.AddRange(paras);
- using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- dt.Load(sdr);
- }
- return dt;
- }
- }
- }
- 定义好sqlhelper类,我们就能在D层的各种DAO类中方便的执行SQL语句。希望能对大家有所帮助。