220 views

ADO.NET封装数据库操作类

今天赶ASP作业时发现了一个ADO.NET对数据库访问的封装类,DbManagerSQL,可以当做一个工具类在操作数据库中使用,很方便。

使用前首先需要配置ConnectionString,只需在Web.config配置文件加入一个字段即可。

<appSettings>
    <add key="ConnectionString" 
        value="server=.;
        database=mydatabase;
        integrated security=true"/>
</appSettings>
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Odbc;
//ADO.NET数据库操作基础类。
namespace ADO.NetTest1
{
    public abstract class DbManagerSQL
    {
        //数据库连接字符串
        protected static string connectionString = 
            ConfigurationManager.AppSettings["ConnectionString"];
        public DbManagerSQL()
        {
        }
        ///执行SQL语句,返回影响的记录数
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = 
                    new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        // 执行两条SQL语句,实现数据库事务。
        public static void ExecuteSqlTran(string SQLString1, string SQLString2)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    cmd.CommandText = SQLString1;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = SQLString2;
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
                catch (SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        //执行多条SQL语句,实现数据库事务,每条语句以“;”分割。
        public static void ExecuteSqlTran(string SQLStringList)
        {
            using (OdbcConnection conn = 
                new OdbcConnection(connectionString))
            {
                conn.Open();
                OdbcCommand cmd = new OdbcCommand();
                cmd.Connection = conn;
                OdbcTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    string[] split = SQLStringList.Split(new Char[] { ';' });
                    foreach (string strsql in split)
                    {
                        if (strsql.Trim() != "")
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (OdbcException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        //执行带一个存储过程参数的的SQL语句。
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                SqlCommand cmd = 
                    new SqlCommand(SQLString, connection);
                SqlParameter myParameter = 
                    new SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        //执行一条计算查询结果语句,返回查询结果(object)。
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                SqlCommand cmd = 
                    new SqlCommand(SQLString, connection);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || 
                        (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        //执行查询语句,返回SqlDataReader
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                SqlCommand cmd = 
                    new SqlCommand(strSQL, connection);
                SqlDataReader myReader;
                try
                {
                    connection.Open();
                    myReader = cmd.ExecuteReader();
                    return myReader;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        //执行查询语句,返回DataSet
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = 
                new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = 
                        new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }
}
Fork me on GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">