ADO.NET之SQLHelper类的简单封装

来源：互联网发布：苹果手机网络时间校准编辑：程序博客网时间：2024/06/06 00:02

(1)在app.config中添加数据库连接字符串

<?xml version="1.0" encoding="utf-8"?><configuration>    <startup>         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>    </startup>  <connectionStrings>    <add connectionString="server = 127.0.0.1;database = DBTest;uid = sa;pwd = 123" name="conStr"/>  </connectionStrings></configuration>

(2)添加对System.Configuration的引用

(3)创建SQLHelper类并封装操作数据库的方法

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data.SqlClient;namespace BJSGS{    public class SqlHelper    {        //读取连接字符串        private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;        #region [主方法]<ExecuteNonQuery>此方法用于数据的增删改        /// <summary>        /// 此方法用于数据的增删改        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="ps">sql语句中的参数</param>        /// <returns>返回的受影响的行数,int类型</returns>        public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)        {            //连接数据库            using (SqlConnection con = new SqlConnection(str))            {                using (SqlCommand cmd = new SqlCommand(sql, con))                {                    try                    {                        con.Open();                        if (ps != null)                        {                            cmd.Parameters.AddRange(ps);                        }                        return cmd.ExecuteNonQuery();                    }                    catch (Exception ex)                    {                        con.Close();                        con.Dispose();                        throw ex;                    }                }            }        }        #endregion        #region [主方法]<ExecuteScalar>该方法用于查询读取数据,返回首行首列        /// <summary>        /// 该方法用于查询读取数据,返回首行首列        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="ps">sql语句中的参数</param>        /// <returns>首行首列,object类型</returns>        public static object ExecuteScalar(string sql, params SqlParameter[] ps)        {            using (SqlConnection con = new SqlConnection(str))            {                using (SqlCommand cmd = new SqlCommand(sql, con))                {                    try                    {                        con.Open();                        if (ps != null)                        {                            cmd.Parameters.AddRange(ps);                        }                        return cmd.ExecuteScalar();                    }                    catch (Exception ex)                    {                        con.Close();                        con.Dispose();                        throw ex;                    }                }            }        }        #endregion        #region [主方法]<ExecuteReader>该方法用于查询读取数据,返回SqlDataReader对象        /// <summary>        /// 该方法用于查询读取数据,返回SqlDataReader对象        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="ps">sql语句中的参数</param>        /// <returns>返回的是SqlDataReader对象,里面有数据</returns>        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] ps)        {            SqlConnection con = new SqlConnection(str);            using (SqlCommand cmd = new SqlCommand(sql, con))            {                if (ps != null)                {                    cmd.Parameters.AddRange(ps);                }                try                {                    con.Open();                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);                }                catch (Exception ex)                {                    con.Close();                    con.Dispose();                    throw ex;                }            }        }        #endregion        #region [主方法]<ExecuteTable>该方法用于查询读取数据,返回DataTable对象          /// <summary>          /// 该方法用于查询读取数据,返回DataTable对象          /// </summary>          /// <param name="sql">sql语句</param>          /// <param name="ps">sql语句中的参数</param>          /// <returns>返回的是DataTable对象,里面有数据</returns>        public static DataTable ExecuteTable(string sql, params SqlParameter[] ps)        {            DataTable dt = new DataTable();            using (SqlDataAdapter sda = new SqlDataAdapter(sql, str))            {                sda.SelectCommand.Parameters.AddRange(ps);                sda.Fill(dt);            }            return dt;        }        #endregion    }}

0 0