自已在做网站时用到的,整理了一下,因为是整合DZ论坛所以用到连接MySql的操作。l

DataAccess.cs

using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using MySql.Data.MySqlClient;


namespace net.shanglin.dataAccess
{
    /// <summary>
    /// DataAccess 的摘要说明。
    /// </summary>
    public class DataAccess
    {
        public DataAccess()
        {
        }

        private static bool _mustCloseConnection = true;
        private static string _connectionString = string.Empty;
        private static string _mySqlConnectionString = string.Empty;

        /// <summary>
        /// 是否必须关闭数据库连接
        /// </summary>
        public static bool mustCloseConnection
        {
            get
            {
                return _mustCloseConnection;
            }
            set
            {
                _mustCloseConnection = value;
            }
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        public static string connectionString
        {
            get
            {
                if (_connectionString == string.Empty)
                    return System.Configuration.ConfigurationSettings.AppSettings.Get("strConnection");
                else
                    return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        public static string mySqlConnectionString
        {

            get
            {
                if (_mySqlConnectionString == string.Empty)
                    return System.Configuration.ConfigurationSettings.AppSettings.Get("mySqlConnection");
                else
                    return _mySqlConnectionString;
            }
            set
            {
                _mySqlConnectionString = value;
            }
        }
        

        /// <summary>
        /// 执行Sql查询语句
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        ///<returns >i返回受影响的行数 </returns>
        public static int ExecuteSql(string sqlstr)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                try
                {
                    conn.Open();
                    i = comm.ExecuteNonQuery();
                    return i;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                    comm.Dispose();
                }
            }

        }

        public static int mySqlExecuteSql(string sqlstr)
        {
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(mySqlConnectionString))
            {
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                try
                {
                    conn.Open();
                    i = comm.ExecuteNonQuery();
                    return i;
                }
                catch (MySqlException e)
                {
                    throw new Exception(e.Message + sqlstr);
                }
                finally
                {
                    conn.Close();
                    comm.Dispose();
                }
            }

        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">SqlParameters 集合</param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                ExecutePorcedure(procName, coll, conn, comm);
            }
        }

        /// <summary>
        /// 执行存储过程,返回dataset
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">SqlParameters 集合</param>
        /// <param name="ds">返回dataset</param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                ExecutePorcedure(procName, coll, conn, comm, ref ds);
            }
        }

        /// <summary>
        /// 执行存储过程类
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">SqlParameters 集合</param>
        /// <param name="conn">数据库连接</param>
        /// <param name="comm">sql执行SqlCommand</param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll, SqlConnection conn, SqlCommand comm)
        {
            if (procName == null &#124;&#124; procName == "")
                throw new Exception();
            try
            {
                conn.Open();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                conn.Close();
                comm.Dispose();
            }
        }

        /// <summary>
        /// 执行存储过程类
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">SqlParameters 集合</param>
        /// <param name="conn">数据库连接</param>
        /// <param name="comm">sql执行SqlCommand</param>
        /// <param name="ds">返回dataset</param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll, SqlConnection conn, SqlCommand comm, ref DataSet ds)
        {
            if (procName == null &#124;&#124; procName == "")
                throw new Exception();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                conn.Open();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                conn.Close();
                comm.Dispose();
            }
        }

        /// <summary>
        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>object 返回值 </returns>
        public static object ExecuteScalar(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();

            object obj = new object();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                try
                {
                    conn.Open();
                    comm.CommandText = sqlstr;
                    obj = comm.ExecuteScalar();
                    return obj;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                    comm.Dispose();
                }
            }

        }

        public static object MySqlExecuteScalar(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();

            object obj = new object();
            using (MySqlConnection conn = new MySqlConnection(mySqlConnectionString))
            {
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                try
                {
                    conn.Open();
                    comm.CommandText = sqlstr;
                    obj = comm.ExecuteScalar();
                    return obj;
                }
                catch (MySqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                    comm.Dispose();
                }
            }

        }

        /// <summary>
        /// 执行Sql查询语句,同时进行事务处理
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        public static void ExecuteSqlWithTransaction(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //可以在事务中创建一个保存点,同时回滚到保存点
                SqlTransaction trans;
                conn.Open();
                trans = conn.BeginTransaction();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.Transaction = trans;
                try
                {
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    comm.ExecuteNonQuery();
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                }
                finally
                {
                    trans.Dispose();

                    conn.Close();
                    comm.Dispose();
                }
            }
        }

        /// <summary>
        /// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader dataReader(string sqlstr)
        {
            SqlDataReader _dataReader = null;
            dataReader(sqlstr, ref _dataReader);
            return _dataReader;
        }

        public static MySqlDataReader mySqlDataReader(string sqlstr)
        {
            MySqlDataReader _dataReader = null;
            mySqlDataReader(sqlstr, ref _dataReader);
            return _dataReader;
        }


        /// <summary>
        /// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="dr">传入的ref DataReader 对象</param>
        public static void dataReader(string sqlstr, ref SqlDataReader _dataReader)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                conn.Open();
                if (_mustCloseConnection)
                {
                    _dataReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    _dataReader = comm.ExecuteReader();
                }
            }
            catch (SqlException e)
            {
                _dataReader = null;
                //输出错误原因
                throw e;
            }
        }

        public static void mySqlDataReader(string sqlstr, ref MySqlDataReader _dataReader)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            try
            {
                MySqlConnection conn = new MySqlConnection(mySqlConnectionString);
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn;
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                conn.Open();
                if (_mustCloseConnection)
                {
                    _dataReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    _dataReader = comm.ExecuteReader();
                }
            }
            catch (MySqlException e)
            {
                _dataReader = null;
                //输出错误原因
               throw e;
                
            }
        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataSet</returns>
        public static DataSet dataSet(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                try
                {
                    conn.Open();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    da.SelectCommand = comm;
                    da.Fill(ds);
                    return ds;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="ds">传入的引用DataSet对象</param>
        public static void dataSet(string sqlstr, ref DataSet ds)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                try
                {
                    conn.Open();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    da.SelectCommand = comm;
                    da.Fill(ds);
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 返回指定Sql语句的DataTable
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable dataTable(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                try
                {
                    conn.Open();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    da.SelectCommand = comm;
                    da.Fill(datatable);
                    return datatable;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 执行指定Sql语句,同时给传入DataTable进行赋值
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="dt">ref DataTable dt </param>
        public static void dataTable(string sqlstr, ref DataTable dt)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            if (dt == null)
                dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                try
                {
                    conn.Open();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    da.SelectCommand = comm;
                    da.Fill(dt);
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                }

            }
        }


        /// <summary>
        /// 执行带参数存储过程并返回数据集合
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">SqlParameterCollection 输入参数</param>
        /// <returns></returns>
        public static DataTable dataTable(string procName, SqlParameterCollection parameters)
        {
            if (procName == null &#124;&#124; procName == "")
                throw new Exception();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                try
                {
                    comm.Parameters.Clear();
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.CommandText = procName;
                    foreach (SqlParameter para in parameters)
                    {
                        SqlParameter p = (SqlParameter)para;
                        comm.Parameters.Add(p);
                    }
                    conn.Open();

                    da.SelectCommand = comm;
                    da.Fill(datatable);
                    return datatable;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    conn.Close();
                }
            }


        }

        /// <summary>
        /// 执行sql语句,返回DataView
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>返回DefaultView</returns>
        public static DataView dataView(string sqlstr)
        {
            if (sqlstr == null &#124;&#124; sqlstr == "")
                throw new Exception();
            SqlDataAdapter da = new SqlDataAdapter();
            DataView dv = new DataView();
            DataSet ds = new DataSet();
            dataSet(sqlstr, ref ds);
            dv = ds.Tables[0].DefaultView;
            return dv;
        }

    }
}



web.config


<?xml version="1.0"?>
<!--
    注意: 除了手动编辑此文件以外,您还可以使用
    Web 管理工具来配置应用程序的设置。可以使用 Visual Studio 中的
     “网站”->“Asp.Net 配置”选项。
    设置和注释的完整列表在
    machine.config.comments 中,该文件通常位于
    \Windows\Microsoft.Net\Framework\v2.x\Config 中
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings>
    <add key="strConnection" value="data source=(local);initial catalog=datebase_name;persist security info=False;user id=sa;pwd=123456;workstation id=(local);packet size=4096"/>
    <add key="mySqlConnection" value="server=localhost; user id=root; password=123456; database=datebase_db; charset=gb2312"/>
  </appSettings>  
  <system.web>
    <httpHandlers>
      <add path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" validate="false"/>
    </httpHandlers>
    <httpModules>
      <add type="URLRewriter.ModuleRewriter, URLRewriter" name="ModuleRewriter"/>
    </httpModules>
    <!--
            设置 compilation debug="true" 将调试符号插入
            已编译的页面中。但由于这会
            影响性能,因此只在开发过程中将此值
            设置为 true。
        -->
    <compilation debug="true" defaultLanguage="c#">
   <assemblies>
    <add assembly="System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
    <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
    <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
    <add assembly="MySql.Data, Version=5.0.5.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D" />
   </assemblies>
   <buildProviders>
    <add extension=".rdlc" type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
   </buildProviders>
  </compilation>
    <!--
            通过 <authentication> 节可以配置 ASP.NET 使用的
            安全身份验证模式,
            以标识传入的用户。
        -->
    <authentication mode="Windows"/>
    <!--
            如果在执行请求的过程中出现未处理的错误,
            则通过 <customErrors> 节可以配置相应的处理步骤。具体说来,
            开发人员通过该节可以配置
            要显示的 html 错误页
            以代替错误堆栈跟踪。

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
<globalization fileEncoding="gbk" requestEncoding="gbk" responseEncoding="gbk" />
        -->
    <globalization
            requestEncoding="gb2312"
            responseEncoding="gb2312"
responseHeaderEncoding="gb2312"
   />
  <pages enableEventValidation="false" viewStateEncryptionMode="Never"  />

  </system.web>
</configuration>

Comments(0) | Trackbacks(0) | Reads(2745)
Add a comment
Nickname [Register]
Password Optional
Site URI
Email
Enable HTML Enable UBB Enable Emots Hidden Remember