c#连接sql数据库查询修改类

本人新手，没什么开发经验，下面代码是网上资料整理所得，经测试验证没问题，仅供参考

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DbTest
{
    public class DbHelper
    {
        private readonly string SQLCONNECTSTR = "server=172.16.125.103;uid=sa;pwd=sa;database=CDDB;Timeout=10";
        private SqlConnection dbConnection;

        //创建打开dbConnection对象 
        public void OpenConnection()
        {
            if (dbConnection == null)
            {
                dbConnection = new SqlConnection(SQLCONNECTSTR);
            }

            if (dbConnection.State == ConnectionState.Closed)
            {
                try
                {
                    dbConnection.Open();
                }
                catch (Exception ex)
                {
                    dbConnection.Close();
                    throw ex;
                }
            }
        }

        //释放dbConnection对象 
        public void CloseConnection()
        {
            if (dbConnection != null)
            {
                if (dbConnection.State == ConnectionState.Open)
                {
                    dbConnection.Close();
                    dbConnection.Dispose();
                    dbConnection = null;
                }
            }
        }

        // 
        //创建cmd,注意dbconnection在该函数中创建，但没有在这函数中释放。 
        //在正确的面向对象设计方法中，对象应该是谁创建，谁就应该负责释放。按这个观点，这个过程有些不安全!!!! 
        private SqlCommand CreateCommand(string ProName)
        {
            OpenConnection();
            SqlCommand cmd = new SqlCommand(ProName, dbConnection);
            return cmd;

        }

        /// <summary> 
        /// 返回受影响行数。
        /// 创建cmd,并执行相应的操作。 然后释放cmd! 
        /// 该函数是执行cmd没有返回值，且没有参数的方法。 
        /// </summary> 
        /// <param name="ProName"></param> 
        public int getExecuteNonQuery(string ProName)
        {
            SqlCommand cmd = CreateCommand(ProName);
            int k = 0;

            try
            {
                k = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                cmd.Dispose();
            }
            return k;
        }

        /// <summary> 
        /// 返回查询结果第一行第一列
        /// 创建cmd,并执行相应的操作。 然后释放cmd! 
        /// 该函数是执行cmd带有返回值,但没有参数的方法。    
        /// </summary> 
        /// <param name="ProName"></param> 
        public object getExecuteScalar(string ProName)
        {
            object result;
            SqlCommand cmd = CreateCommand(ProName);
            try
            {
                result = cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Parameters.Clear();
                CloseConnection();
            }
            return result;
        }

        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataTable getDataTable(string ProName)
        {
            SqlCommand cmd = CreateCommand(ProName);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                cmd.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// 返回DataTable对象
        /// 用法示例：
        /// DbHelper db = new DbHelper();
        /// dataGridView1.DataSource=db.getDataTable( "select * from users");
        /// </summary>
        /// <param name="ProName"></param>
        /// <returns></returns>
        public DataSet getDataSet(string ProName)
        {
            SqlCommand cmd = CreateCommand(ProName);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                cmd.Dispose();
            }
            return ds;
        }


    }
}