关闭

数据库封装类DataOperation(支持事务)

标签: 数据库stringcmd存储objectnull
835人阅读 评论(0) 收藏 举报
分类:

由于自己工作需要 经常重复写数据库的操作代码.今天我把他们写成了dll, 拿来和大家分享.希望大家提出意见.支持事务,目前还不支持所有存储过程.

DataMsSql.cs

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

namespace DataOperation
{
    /// <summary>
    /// MsSql操作类
    /// </summary>
    sealed public class DataMsSql
    {
        private SqlConnection dataConnection = null;

        #region 构造函数
        /// <summary>
        /// 无参构造函数
        /// </summary>
        public DataMsSql()
        {
            dataConnection = new SqlConnection();
        }
        /// <summary>
        /// 有参构造函数
        /// </summary>
        /// <param name="connectionString">数据库联接串</param>
        public DataMsSql(string connectionString)
        {
            dataConnection = new SqlConnection(connectionString);
        }
        #endregion

        #region 属性 
        /// <summary>
        /// 设置或获取SqlConnection类型数据库联接dataConnection的值
        /// </summary>
        public SqlConnection DataConnection
        {
            get
            {
                if (dataConnection == null)
                    dataConnection = new SqlConnection();
                return dataConnection;
            }
            set
            {
                dataConnection = value;
            }
        }
        /// <summary>
        /// 获取数据库联接的状态
        /// </summary>
        public ConnectionState SqlState
        {
            get
            {
                return dataConnection.State;
            }
        }
        #endregion

        #region 状态操作
        /// <summary>
        /// 打开数据库联接
        /// </summary>
        public void Open()
        {
            if (dataConnection.State != ConnectionState.Open)
                dataConnection.Open();
        }
        /// <summary>
        /// 关闭数据库联接
        /// </summary>
        public void Close()
        {
            if (dataConnection.State != ConnectionState.Closed)
                dataConnection.Close();
        }
        #endregion

        #region 一般数据语句操作
        /// <summary>
       /// 获取检索出来首行首列的值
       /// </summary>
       /// <param name="sqlStr">sql查询串</param>
       /// <returns>结果</returns>
        public object ExecuteScalar(string sqlStr)
        {
            if (SqlState == ConnectionState.Closed)
                this.Open();
            SqlCommand cmd = new SqlCommand(sqlStr, dataConnection);
            return cmd.ExecuteScalar();
        }
        /// <summary>
        ///检索数据以SqlDataReader形式返检索结果
        /// </summary>
        /// <param name="sqlStr">sql查询串</param>
        /// <returns>SqlDataReader数据集</returns>
        public SqlDataReader GetDataReadr(string sqlStr)
        {
            if (SqlState == ConnectionState.Closed)
                this.Open();
            SqlCommand cmd = new SqlCommand(sqlStr, dataConnection);
            return cmd.ExecuteReader();
        }
        /// <summary>
        /// 检索数据以DataTable形式返检索结果
        /// </summary>
        /// <param name="sqlStr">sql查询串</param>
        /// <returns>DataTale数据集</returns>
        public DataTable GetDataTable(string sqlStr)
        {
            DataTable dt = new  DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, dataConnection);
            adapter.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 执行出检索以外的其它数据操作返回影响条数
        /// </summary>
        /// <param name="sqlStr">sql操作语句</param>
        /// <returns> 操作影响条数</returns>
        public int ExecuteSql(String sqlStr)
        {
            if (SqlState == ConnectionState.Closed)
                this.Open();
            SqlCommand cmd = new SqlCommand(sqlStr, DataConnection);
            return cmd.ExecuteNonQuery();
        }
        #endregion

        #region 存储过程操作
       /// <summary>
       /// 执行存储过程获得DataTable数据
       /// </summary>
        /// <param name="sqlParameters">存储过程参数ArrayList类型的SqlParameter集合</param>
       /// <param name="storedProcedureName">存储过程名</param>
       /// <returns></returns>
        public DataTable ProGetDataTable(ArrayList sqlParameters, string storedProcedureName)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = dataConnection;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;
            if(sqlParameters != null && sqlParameters.Count != 0)
                foreach(object parameter in sqlParameters)
                {
                    cmd.Parameters.Add((SqlParameter)parameter);
                }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            dataAdapter.Fill(dt);
            return dt;
        }
        #endregion

        #region 事务操作
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            ExecuteSql("begin transaction;");
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            ExecuteSql("commit;");
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            ExecuteSql("rollback;");
        }
        #endregion

    }
}

有要已编译成dll的 留邮箱

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:40959次
    • 积分:672
    • 等级:
    • 排名:千里之外
    • 原创:19篇
    • 转载:5篇
    • 译文:0篇
    • 评论:68条
    文章分类
    最新评论