分享一个自己整的asp.net数据库操作的类

木头 · 发表于 2014-5-12 13:29:49

自己总结的，提前声明，我只是一个技术初学者，写的不好，不要喷粪，谢谢。

[C#] 纯文本查看 复制代码

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using OIFrameWork.Common;

namespace OIFrameWork.DBUtility
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    public class DataAccess
    {
        #region "数据操作类访问接口"
        private static readonly String SqlConnectionString = StringFunction.GetInstance().GetConfigAppString("SqlConnectionString");
        private static readonly object DataAccessLock = new object();
        private static DataAccess _instance;

        /// <summary>
        /// 通过缓存访问
        /// </summary>
        /// <returns></returns>
        public static DataAccess GetInstance()
        {
            if (_instance != null) return _instance;
            lock (DataAccessLock)
            {
                return _instance ?? (_instance = new DataAccess());
            }
        }

        #endregion

        #region "数据访问操作方法"

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public bool ColumnExists(string tableName, string columnName)
        {
            var sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            var res = (ExecuteSqlGetObject(sql));
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }


        /// <summary>
        /// 通过事物执行语句操作
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>返回执行影响的行数</returns>
        public int ExecuteSqlByTran(String sqlString)
        {
            var result = 0;
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    if (conn.State.Equals( ConnectionState.Closed))
                        conn.Open();
                    var tran = conn.BeginTransaction();
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = sqlString;
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = tran;
                        result = cmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 通过事物执行语句并限制执行的时间
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="times"></param>
        /// <returns></returns>
        public int ExecuteSqlByTranByTimes(String sqlString, Int32 times)
        {
            var result = 0;
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    var tran = conn.BeginTransaction();
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = sqlString;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = times;
                        cmd.Transaction = tran;
                        result = cmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 使用事物执行多条语句的操作
        /// </summary>
        /// <param name="lst"></param>
        /// <returns></returns>
        public int ExecuteSqlByTran(List<String> lst)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var tran = conn.BeginTransaction();
                 
                    var result = 0;
                    try
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = tran;
                        cmd.CommandType = CommandType.Text;
                        foreach (var s in lst)
                        {
                            cmd.CommandText = s;
                            result += cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                    return result;
                }
            }
        }

        /// <summary>
        /// 执行一个查询，返回查询的结果
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public object ExecuteSqlGetObject(String sqlString)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlString;
                    var obj = cmd.ExecuteScalar();
                    if (Equals(obj, null) || Equals(obj, DBNull.Value))
                    {
                        return null;
                    }
                    return obj;
                }
            }
        }

        /// <summary>
        /// 执行一个查询返回查询结果，超时就退出
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="times"></param>
        /// <returns></returns>
        public object ExecuteSqlGetObject(String sqlString, Int32 times)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlString;
                    cmd.CommandTimeout = times;
                    var obj = cmd.ExecuteScalar();
                    if (Equals(obj, null) || Equals(obj, DBNull.Value))
                    {
                        return null;
                    }
                    return obj;
                }
            }
        }

        /// <summary>
        /// 执行存储过程的查询
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool ExecuteProcedureQueryByParam(String procName, SqlParameter[] param)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, procName, param, CommandType.StoredProcedure);
                    var obj = cmd.ExecuteScalar();
                    if (Equals(obj, null) || Equals(obj, DBNull.Value))
                    {
                        return false;
                    }
                    return Convert.ToInt32(obj) > 0;
                }
            }
        }

        /// <summary>
        /// 通过参数填充sql语句并执行查询返回结果
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ExecuteSqlByTranAndParam(String sqlString, params SqlParameter[] param)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    var tran = conn.BeginTransaction();
                    try
                    {
                        PrepareCommand(cmd, conn, tran, sqlString, param, CommandType.Text);
                        var result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch
                    {
                        tran.Rollback();
                        return 0;
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 执行存储过程的查询，返回真假并返回影响的行数
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回执行的结果</returns>
        public bool ExecuteProcedureByParam(String procName, SqlParameter[] param)
        {
            var flage = false;
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    var trans = conn.BeginTransaction();
                    try
                    {
                        PrepareCommand(cmd, conn, trans, procName, param, CommandType.StoredProcedure);
                        var rowsAffected = cmd.ExecuteNonQuery();
                        trans.Commit();

                        if (rowsAffected > 0)
                        {
                            flage = true;
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        trans.Dispose();
                    }

                }
            }
            return flage;
        }

        /// <summary>
        /// 执行存储过程的查询，返回真假并返回影响的行数
        /// </summary>
        /// <param name="procName">存储过程</param>
        /// <param name="param">参数集合</param>
        /// <param name="rowsAffect">受影响的行数</param>
        /// <param name="times">允许执行的时间</param>
        /// <returns>返回执行结果</returns>
        public bool ExecuteProcedureByParam(String procName, SqlParameter[] param, out int rowsAffect, int times)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    var flage = false;
                    var tran = conn.BeginTransaction();
                    try
                    {
                        PrepareCommand(cmd, conn, tran, procName, param, CommandType.StoredProcedure);
                        cmd.CommandTimeout = times;
                        rowsAffect = cmd.ExecuteNonQuery();
                        tran.Commit();
                        if (rowsAffect > 0)
                        {
                            flage = true;
                        }
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                    return flage;
                }
            }
        }

        /// <summary>
        /// 执行查询返回填充数据的DataSet
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlGetDataSet(String sqlString)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var sda = new SqlDataAdapter(sqlString, conn))
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    var ds = new DataSet();
                    sda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行查询返回填充数据的DataSet
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="times"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlGetDataSet(String sqlString, Int32 times)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var sda = new SqlDataAdapter(sqlString, conn))
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    var ds = new DataSet();
                    sda.SelectCommand.CommandTimeout = times;
                    sda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行存储过程获取Dataset
        /// </summary>
        /// <param name="procName">存储过程</param>
        /// <param name="param">参数集合</param>
        /// <param name="tableName"></param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteProcedureGetDataSet(String procName, SqlParameter[] param, String tableName)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    var ds = new DataSet(tableName);
                    PrepareCommand(cmd, conn, null, procName, param, CommandType.StoredProcedure);
                    var sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行存储过程的查询并返回填充数据的DataSet
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="param"></param>
        /// <param name="tableName"></param>
        /// <param name="times"></param>
        /// <returns></returns>
        public DataSet ExecuteProcedureGetDataSet(String procName, SqlParameter[] param, String tableName, Int32 times)
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    var ds = new DataSet(tableName);
                    cmd.CommandTimeout = times;
                    PrepareCommand(cmd, conn, null, procName, param, CommandType.StoredProcedure);
                    var sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 为查询准备参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="cmdText"></param>
        /// <param name="parms"></param>
        /// <param name="ct"></param>
        protected void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] parms, CommandType ct)
        {
            PrepareCommand(cmd, conn, null, cmdText, parms, ct);
        }

        /// <summary>
        /// 为查询准备参数
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <param name="conn">SqlConnection</param>
        /// <param name="trans">SqlTransation</param>
        /// <param name="cmdText">Cmd命令</param>
        /// <param name="parms">参数</param>
        /// <param name="ct">CommandType类型</param>
        protected void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] parms, CommandType ct)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = ct;
            if (parms == null) return;
            foreach (var parameter in parms)
            {
                if (( parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input ) &&
                    ( parameter.Value == null ))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }

        #endregion

        #region "Excel操作方法"

        /// <summary>
        /// 读取Excel文件的内容并填充到Datatable中
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetName">数据工作簿名称</param>
        /// <returns>返回填充数据的DataTable</returns>
        public DataSet ExcelDataSource(String filePath, String sheetName)
        {
            var strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
            var ds = new DataSet();
            using (var connection = new OleDbConnection(strConn))
            {
                using (var cmdCommand = new OleDbCommand())
                {
                    if(connection.State==ConnectionState.Closed)connection.Open();
                    cmdCommand.CommandText = "select * from [" + sheetName + "$]";
                    cmdCommand.CommandType = CommandType.Text;
                    cmdCommand.Connection = connection;
                    var oda=new OleDbDataAdapter(cmdCommand);
                    oda.Fill(ds);
                }
            }
            return ds;
        }


        /// <summary>
        /// 获取Excel表哥的Sheet名称
        /// </summary>
        /// <param name="filePath">文件的路径信息</param>
        /// <returns></returns>
        public ArrayList ExcelSheetName(string filePath)
        {
            var al = new ArrayList();
            var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            using (var conn = new OleDbConnection(strConn))
            {
                if (conn.State == ConnectionState.Closed) conn.Open();
                var sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] {null, null, null, "TABLE"});
                if (sheetNames == null) return al;
                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2]);
                }
                return al;
            }
        }

        #endregion
    }
}

sqyfzx · 发表于 2014-12-11 15:14:34

数据库的Helper，很好

qq451354 · 发表于 2015-1-12 11:45:00

感谢分享小白先去学习了

		自动登录	找回密码
密码			注册 (请使用非IE浏览器)

分享一个自己整的asp.net数据库操作的类

评分