SQL连接基础类

/**/ /**/ /**/ /*********************************************************************
 * App.config 文件内容
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <appSettings>
        <!--add key="conn" value="server=192.168.1.2;database=dataBaseName;uid=sa;pwd=******;" /-->
        <add key="conn" value="data source=(LOCAL);persist security info=True;initial catalog=dataBaseName;user id=sa;password=******"/>
      </appSettings>
    </configuration>
 * *********************************************************************/
using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Configuration;

namespace  Langer
... {
    /**//// <summary>
    /// 边城浪．数据层
    /// http://blog.csdn.cn/yeerh
    /// </summary>
    public class SqlHelper
    ...{
        private string _connectionString;
        /**//// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        ...{
            set ...{ _connectionString = value; }
            get ...{ return _connectionString; }
        }

        构造函数#region 构造函数
        /**//// <summary>
        /// 创建连接实例
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public SqlHelper(string connectionString)
        ...{
            this._connectionString = connectionString;
        }
        #endregion

        // 创建一个新连接
        private SqlConnection GetSqlConnection()
        ...{
            return new SqlConnection(_connectionString);
        }

        //创建可执行的SqlCommand;
        private SqlCommand CreatSqlCommand(SqlConnection connection, string storedProcedureName, SqlParameter[] parameters)
        ...{
            if (connection == null)
            ...{
                connection = GetSqlConnection();
            }
            SqlCommand cmd = new SqlCommand(storedProcedureName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            ...{
                foreach (SqlParameter p in parameters)
                ...{
                    if (p.Value == null)
                    ...{
                        p.Value = DBNull.Value;
                    }
                }
                cmd.Parameters.AddRange(parameters);
            }
            if (connection.State != ConnectionState.Open)
            ...{
                connection.Open();
            }
            return cmd;
        }

        /**//// <summary>
        /// 执行请求
        /// </summary>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <param name="connection">SqlConnection</param>
        /// <returns>影响数</returns>
        public int ExecuteNonQuery(SqlConnection connection, string storedProcedureName, SqlParameter[] parameters)
        ...{
            SqlCommand cmd = CreatSqlCommand(connection, storedProcedureName, parameters);
            try
            ...{
                return cmd.ExecuteNonQuery();
            }
            finally
            ...{
                if (connection == null)
                ...{
                    cmd.Connection.Close();
                }
                cmd.Dispose();
            }　
        }

        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string storedProcedureName, SqlParameter[] parameters)
        ...{
            return ExecuteNonQuery(null, storedProcedureName, parameters);
        }

        /**//// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="connection">SqlConnection</param>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteReader(SqlConnection connection, string storedProcedureName, SqlParameter[] parameters)
        ...{
            SqlCommand cmd = CreatSqlCommand(connection, storedProcedureName, parameters);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /**//// <summary>
        /// 读取数据放入DataSet
        /// </summary>
        /// <param name="connection">SqlConnection</param>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataset(SqlConnection connection, string storedProcedureName, SqlParameter[] parameters)
        ...{
            DataSet ds = new DataSet();
            ds.Locale = new System.Globalization.CultureInfo("zh-CHS", false);
            using (SqlCommand cmd = CreatSqlCommand(connection, storedProcedureName, parameters))
            ...{
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                ...{
                    try
                    ...{
                        da.Fill(ds);
                    }
                    finally
                    ...{
                        if (connection == null)
                        ...{
                            cmd.Connection.Close();
                        }
                    }
                }
            }
            return ds;
        }

        /**//// <summary>
        /// 读取数据放入DataSet
        /// </summary>
        /// <param name="command">sql语句</param>
        /// <param name="connection">SqlConnection</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataset(string command, SqlConnection connection)
        ...{
            using (SqlCommand cmd = new SqlCommand(command))
            ...{
                bool clear;
                if (connection == null)
                ...{
                    cmd.Connection = GetSqlConnection();
                    clear = true;
                }
                else
                ...{
                    cmd.Connection = connection;
                    clear = false;
                }
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                ...{
                    DataSet ds = new DataSet();
                    ds.Locale = new System.Globalization.CultureInfo("zh-CHS", false);
                    try
                    ...{
                        da.Fill(ds);
                        return ds;
                    }
                    finally
                    ...{
                        if (clear)
                        ...{
                            cmd.Connection.Close();
                        }
                    }
                }
            }
        }

        /**//// <summary>
        /// 读取数据放入DataSet
        /// </summary>
        /// <param name="connection">SqlConnection</param>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataset(SqlConnection connection, string storedProcedureName)
        ...{
            return ExecuteDataset(connection, storedProcedureName, null);
        }

        /**//// <summary>
        /// 更简单的操作方法，临时SqlConnect
        /// </summary>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataset(string storedProcedureName)
        ...{
            return ExecuteDataset(null, storedProcedureName, null);
        }

        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public int UpdateDataTable(string command, DataTable dt)
        ...{
            using (SqlCommand cmd = new SqlCommand(command, GetSqlConnection()))
            ...{
                DataSet ds = new DataSet();
                ds.Locale = new System.Globalization.CultureInfo("zh-CHS", false);
                ds.Tables.Add(dt);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                try
                ...{
                    return da.Update(ds);
                }
                finally
                ...{
                    cmd.Connection.Close();
                    da.Dispose();
                }
            }
        }

        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="dataAdapter"></param>
        /// <param name="dataSet"></param>
        public static void UpdateDataset(SqlDataAdapter dataAdapter, DataSet dataSet)
        ...{
            if (dataAdapter == null)
            ...{
                return;
            }
            using (SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(dataAdapter))
            ...{
                if (dataSet != null)
                ...{
                    dataAdapter.Update(dataSet);
                }
            }
        }

        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <param name="dataSet"></param>
        public void UpdateDataset(string selectCommand, DataSet dataSet)
        ...{
            if (dataSet == null) ...{
                return ;
            }
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
            ...{
                dataAdapter.SelectCommand = new SqlCommand(selectCommand, GetSqlConnection());
                using (SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(dataAdapter))
                ...{
                    try
                    ...{
                        dataAdapter.Update(dataSet);
                    }
                    finally
                    ...{
                        dataAdapter.SelectCommand.Connection.Close();
                        dataAdapter.SelectCommand.Dispose();
                    }
                }
            }
        }
    }
}