SqlDBHelper常用方法

/*=============================================================

 *.net连接数据库常用方法

 *Author ： dongny,Li

 *E-mail ： xing.dong.li@163.com

 *Edition： Beta 1.0

 *=============================================================*/

using System;

using System.Collections.Generic;

using System.Text;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

 

namespace TreeViewHelper

{

   public class SqlDBHelper

    {

       //在配置文件Web.Config中获取连接字符串

       public static string connectionStr =ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;

 

       /// <summary>

       /// 执行增删改T-SQL语句，返回执行成功与否

       /// </summary>

       /// <param name="strSql">T-SQL语句或存储过程名称</param>

        /// <paramname="cmdType">T-SQL语句类别</param>

       /// <param name="param">参数列表</param>

       /// <returns>返回值为：true/false</returns>

       public static bool DoExecuteNonQuery(string strSql, CommandType cmdType,params SqlParameter[] param)

        {

           using (SqlConnection con = new SqlConnection(connectionStr))

           {

                try

                {

                    con.Open();

                    SqlCommand com = newSqlCommand(strSql, con);

                    com.CommandType = cmdType;

                    if (param.Length > 0)

                       com.Parameters.AddRange(param);

 

                    int flag =com.ExecuteNonQuery();

                    return flag > 0 ? true :false;

                }

                catch(Exception ex)

                {

                    throw newException("SQL Connection Faile,Bring Error DoExecuteNonQuery :"+ex.Message);

                }

                finally

                {

                    con.Close();

                }

           }

       }

 

       /// <summary>

       /// 执行查询T-SQL语句，返回SqlDataReader对象

       /// </summary>

       /// <param name="strSql">T-SQL语句或存储过程</param>

       /// <param name="cmdType">T-SQL语句类型</param>

       /// <param name="param">参数列表</param>

       /// <returns>SqlDataReader对象</returns>

       public static SqlDataReader GetSqlDataReader(string strSql, CommandTypecmdType, params SqlParameter[] param)

       {

           SqlConnection con = new SqlConnection(connectionStr);

           SqlDataReader sdr = null;

           SqlCommand com = null;

           try

           {

                con.Open();

                com = new SqlCommand();

                com.Connection = con;

                com.CommandType = cmdType;

                com.CommandText = strSql;

                if (param.Length > 0)

                   com.Parameters.AddRange(param);

                sdr =com.ExecuteReader(CommandBehavior.CloseConnection);

                return sdr;

           }

           catch(Exception ex)

           {

                con.Close();

                throw new Exception("SQLConnection Faile ,Bring Error GetSqlDataReader: " + ex.Message);

           }

           finally

           {

                com.Dispose();

           }

       }

 

       /// <summary>

       /// 执行查询T-SQL语句，返回DataSet

       /// </summary>

       /// <param name="strSql">T-SQL语句或存储过程</param>

       /// <param name="cmdType">T-SQL语句类型</param>

       /// <param name="param">参数列表</param>

       /// <returns>DataSet对象</returns>

       public static DataSet GetDataSet(string strSql, CommandType cmdType,params SqlParameter[] param)

       {

           using (SqlConnection con = new SqlConnection(connectionStr))

           {

               SqlDataAdapter sda = null;

                try

                {

                    con.Open();

                    SqlCommand com = newSqlCommand(strSql, con);

                    com.CommandType = cmdType;

                   com.Parameters.AddRange(param);

                    sda = newSqlDataAdapter(com);

                    DataSet ds = new DataSet();

                    sda.Fill(ds);

                    return ds;

                }

                catch(Exception ex)

                {

                    throw newException("SQL Connection Faile ,Bring error GetDataSet: " +ex.Message);

                }

                finally

                {

                    sda.Dispose();

                    con.Close();

                }

           }

       }

 

       /// <summary>

       /// 执行查询T-SQL语句，返回DataTable

       /// </summary>

       /// <param name="strSql">T-SQL语句或存储过程</param>

       /// <param name="cmdType">T-SQL语句类型</param>

       /// <param name="param">参数列表</param>

       /// <returns>DataTable对象</returns>

       public static DataTable GetDataTable(string strSql, CommandType cmdType,params SqlParameter[] param)

       {

           using (SqlConnection con = new SqlConnection(connectionStr))

           {

                SqlDataAdapter sda = null;

                try

                {

                    con.Open();

                    SqlCommand com = newSqlCommand(strSql, con);

                    com.CommandType = cmdType;

                   com.Parameters.AddRange(param);

                    sda = newSqlDataAdapter(com);

                    DataTable dt = newDataTable();

                    sda.Fill(dt);

                   return dt;

                }

                catch(Exception ex)

                {

                    throw newException("SQL Connection Faile ,Bring error GetDataTable: " +ex.Message);

                }

                finally

                {

                    sda.Dispose();

                    con.Close();

                }

           }

       }

 

       /// <summary>

       /// 执行查询T-SQL语句，返回单行记录

       /// </summary>

       /// <param name="strSql">T-SQL语句或存储过程</param>

       /// <param name="cmdType">T-SQL语句类型</param>

       /// <param name="param">参数列表</param>

       /// <returns>返回Object对象</returns>

       public static Object DoExecuteScalar(string strSql, CommandType cmdType,params SqlParameter[] param)

       {

           Object obj = null;

           using (SqlConnection con = new SqlConnection(connectionStr))

           {

                SqlCommand com = newSqlCommand(strSql, con);

                try

                {

                    com.CommandType = cmdType;

                   com.Parameters.AddRange(param);

                    com.Connection.Open();

                    obj = com.ExecuteScalar();

                    return obj;

                }

                catch(Exception ex)

                {

                    throw newException("SQL Connection Faile , Bring Error DoExecuteScalar: " +ex.Message);

                }

                finally

                {

                    com.Dispose();

                    con.Close();

                }

           }

       }

 

 

    }

}

转载于:https://www.cnblogs.com/beautiful-code/p/4895650.html