小菜模块化框架设计-简化数据库操作组件_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 小菜模块化框架设计-简化数据库操作组件

小菜模块化框架设计-简化数据库操作组件

 2013/11/24 16:25:44  aganqin  博客园  我要评论(0)
  • 摘要:背景前期在做一个装配线系统自动化扫描打标应用时,系统要求操作实时性比较强,所以没有用ORM之类的工具,可是开发组成员设计的数据库操作类显得有些复杂,调用代码量大,可扩展性低下,更不用说灵活性了,换个数据库类型,这个组件就废掉了。我自已尝试封装了个数据库操作帮助类,自认为还算简单。同时,考虑到将来可能涉及到其他项目不同数据库类型也有同样的需求,在设计方面也提供了扩展的接口,总之,尽量使之简单化,可扩展,可复用,增强灵活性。设计原理原理很简单,设计一个抽象类(Helper),具体的子类
  • 标签:数据库 数据 操作 设计

背景

前期在做一个装配线系统自动化扫描打标应用时,系统要求操作实时性比较强,所以没有用ORM之类的工具,可是开发组成员设计的数据库操作类显得有些复杂,调用代码量大,可扩展性低下,更不用说灵活性了,换个数据库类型,这个组件就废掉了。

我自已尝试封装了个数据库操作帮助类,自认为还算简单。

同时,考虑到将来可能涉及到其他项目不同数据库类型也有同样的需求,在设计方面也提供了扩展的接口,总之,尽量使之简单化,可扩展,可复用,增强灵活性。

设计原理

原理很简单,设计一个抽象类(Helper),具体的子类(SQLHelper,OracleHelper…)继承此抽象类即可。

 

image

从方法使用者角度考虑,我把方法设计想像成一个加工厂,我只需要关注输入源料和输入产品,具体复杂的工艺加工过程不需要关心,如下图所示:

image

辅助类和接口

  • 添加一个参数接口IDbParameter
  • Add(string paramName, DbType sqlType, object paramValue, DbParameter dbParam);
  • 解析:paramName为参数名称,sqlType数据库类型,paramValue参数值,dbParam参数对象
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace XiaoCai.DataAccess
{
    public interface IDbParameter
    {
        void Add(string paramName, DbType sqlType, object paramValue, DbParameter dbParam);
        void Add(string paramName, object paramValue, DbParameter dbParam);
        void Add(string paramName, DbType sqlType, DbParameter dbParam);
        List<System.Data.Common.DbParameter> GetParameters();
        void Clear();
    }
}
  • 实例化参数对象
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    
    namespace XiaoCai.DataAccess
    {
        public class DBParameter:IDbParameter
        {
            private readonly List<System.Data.Common.DbParameter> _dbParams;//声明一个SQL参数列表
            /// <summary>
            /// 构造函数,实例化SQL参数列表
            /// </summary>
            public DBParameter()
            {
                _dbParams = new List<System.Data.Common.DbParameter>();  
            }
    
            public void Add(string paramName, DbType dbType, object paramValue,DbParameter dbParam)
            {
                System.Data.Common.DbParameter addSqlParam = dbParam;
                addSqlParam.DbType = dbType;
                addSqlParam.ParameterName = paramName;
                addSqlParam.Value = paramValue;
                _dbParams.Add(addSqlParam);  
            }
            public void Add(string paramName, object paramValue, DbParameter dbParam)
            {
                System.Data.Common.DbParameter addSqlParam = dbParam;
                addSqlParam.ParameterName = paramName;
                addSqlParam.Value = paramValue;
                _dbParams.Add(addSqlParam);
            }
    
            public void Add(string paramName, DbType dbType, DbParameter dbParam)
            {
                System.Data.Common.DbParameter addSqlParam = dbParam;
                addSqlParam.DbType = dbType;
                addSqlParam.ParameterName = paramName;
                addSqlParam.Direction = ParameterDirection.Output;
                addSqlParam.Size = 200;
                _dbParams.Add(addSqlParam);
            }
    
    
            public List<System.Data.Common.DbParameter> GetParameters()
            {
                return _dbParams;
            }
    
            public void Clear()
            {
                _dbParams.Clear();
            }
        }
    }

执行结果类:执行一个方法结束后,不管执行成功或失败,返回一个结果(执行状态,返回数据,返回消息)

#region Copyright & License
/******************************************************************************
* This document is the property of XiaoCai
* No exploitation or transfer of any information contained herein is permitted 
* in the absence of an agreement with XiaoCai
* and neither the document nor any such information
* may be released without the written consent of XiaoCai
*  
* All right reserved by XiaoCai
*******************************************************************************
* Owner: Agan
* Version: 1.0.0.0
* Component:*
* Function Description:*
* Revision / History
*------------------------------------------------------------------------------
* Flag     Date     Who             Changes Description
* -------- -------- --------------- -------------------------------------------
*   1       20120815 Agan           File created

*------------------------------------------------------------------------------
*/
#endregion

using System;

namespace XiaoCai.DataAccess.Messages
{   
    [Serializable]
    public class ExecutionResult
    {
        private bool _statusField;
        private string _messageField;
        private object _anythingField;
        

        /// <summary>
        /// Returns true or false
        /// </summary>
        public  bool Status
        {
            get { return _statusField; }
            set { _statusField = value; }
        }

        /// <summary>
        /// Returns message of string type
        /// </summary>
        public  string Message
        {
            get { return _messageField; }
            set { _messageField = value; }
        }

        /// <summary>
        /// Returns a object
        /// </summary>
         public object Anything
        {
            get { return _anythingField; }
            set { _anythingField = value; }
        }


    }
}

基类(Helper)

目前只添加了增,删,改,查几个操作(暂不支持事务),如下:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using XiaoCai.DataAccess.Messages;

namespace XiaoCai.DataAccess
{
    public abstract class Helper
    {
       private DbConnection _dbConnection;

       private readonly AppLog _appLog;

       public Helper(DbConnection dbConnection)
       {
           _dbConnection = dbConnection;
            _appLog=new AppLog(GetType().Name);
       }

       public abstract ExecutionResult ExecuteQueryDS(string sqlCommandText, List<System.Data.Common.DbParameter> dbParams);
       public abstract ExecutionResult ExecuteQueryDS(string sqlCommandText);

       public ExecutionResult ExecuteUpdate(string sqlCommandText, List<DbParameter> dbParams)
       {
           DbCommand dbCommand;
           ExecutionResult result;
           result = new ExecutionResult();
           try
           {
               if (_dbConnection.State != ConnectionState.Open)
               {
                   _dbConnection.Open();
               }
               dbCommand = _dbConnection.CreateCommand();
               dbCommand.CommandType = CommandType.Text;
               dbCommand.CommandText = sqlCommandText;
               foreach (DbParameter tmpSqlParam in dbParams)
               {
                   dbCommand.Parameters.Add(tmpSqlParam);
               }
               dbCommand.ExecuteNonQuery();
               result.Status = true;
               result.Message = "OK";
           }
           catch (Exception ex)
           {
               result.Status = false;
               result.Message = ex.Message;
               //write exception log.
               if (_appLog.IsErrorEnabled)
               {
                   _appLog.Error(ex.Message);
                   _appLog.Error(ex.StackTrace);
               }
           }
           finally
           {

               if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
               {
                   _dbConnection.Close();
               }
           }
           return result;
       }

       public ExecutionResult ExecuteUpdate(string sqlCommandText)
       {
           ExecutionResult result = new ExecutionResult();
           try
           {
               if (_dbConnection.State != ConnectionState.Open)
               {
                   _dbConnection.Open();
               }
               DbCommand sqlCommand = _dbConnection.CreateCommand();
               sqlCommand.CommandType = CommandType.Text;
               sqlCommand.CommandText = sqlCommandText;
               sqlCommand.ExecuteNonQuery();
               result.Status = true;
               result.Message = "OK";
           }
           catch (Exception ex)
           {
               result.Status = false;
               result.Message = ex.Message;
               //write exception log.
               if (_appLog.IsErrorEnabled)
               {
                   _appLog.Error(ex.Message);
                   _appLog.Error(ex.StackTrace);
               }
           }
           finally
           {

               if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
               {
                   _dbConnection.Close();
               }
           }
           return result;
       }
       public ExecutionResult ExecuteSP(string spName, List<DbParameter> dbParams)
       {
           DbParameter outputParam = null;
           ExecutionResult result = new ExecutionResult();
           try
           {
               if (_dbConnection.State != ConnectionState.Open)
               {
                   _dbConnection.Open();
               }
               DbCommand dbCommand = _dbConnection.CreateCommand();
               dbCommand.CommandType = CommandType.StoredProcedure;
               dbCommand.CommandText = spName;
               foreach (DbParameter tmpSqlParam in dbParams)
               {
                   dbCommand.Parameters.Add(tmpSqlParam);
                   if (tmpSqlParam.Direction.Equals(ParameterDirection.Output))
                   {
                       outputParam = tmpSqlParam;
                   }
               }
               dbCommand.ExecuteNonQuery();
               if (outputParam == null)
               {
                   result.Message = "OK";
                   result.Anything = "OK";
               }
               else
               {
                   result.Message = "OK";
                   result.Anything = outputParam.Value.ToString();

               }
               result.Status = true;
           }
           catch (Exception ex)
           {
               result.Message = "Helper:ExecuteSP," + ex.Message;
               result.Anything = "Helper:ExecuteSP," + ex.Message;
               result.Status = false;
               //write exception log.
               if (_appLog.IsErrorEnabled)
               {
                   _appLog.Error(ex.Message);
                   _appLog.Error(ex.StackTrace);
               }
           }
           finally
           {

               if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
               {
                   _dbConnection.Close();
               }
           }
           return result;
       }
    }
}

具体操作类(子类)

继承父类Helper,重写子类方法即可。如下我只需要重写两个方法ExecuteQueryDS及其重载方法,根据子类不同点需要和父类区分。

如下是:SQLHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using XiaoCai.DataAccess.Messages;

namespace XiaoCai.DataAccess.SQLServer
{
    public class SQLHelper:Helper
    {
        private readonly DbConnection _dbConnection;

        private readonly AppLog _appLog;

        public SQLHelper(DbConnection dbConnection) : base(dbConnection)
        {
            _dbConnection = dbConnection;
            _appLog = new AppLog(this.GetType().Name);
        }

        public override ExecutionResult ExecuteQueryDS(string sqlCommandText, List<DbParameter> dbParams)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                SqlCommand sqlCommand = ((SqlConnection)_dbConnection).CreateCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlCommandText;
                foreach (DbParameter tmpSqlParam in dbParams)
                {
                    sqlCommand.Parameters.Add(tmpSqlParam);
                }
                DataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }
        
        public override ExecutionResult ExecuteQueryDS(string sqlCommandText)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                SqlCommand sqlCommand = ((SqlConnection)_dbConnection).CreateCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlCommandText;
                DataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }
    }
}

其他的具体操作子类,也同理,如下:

OracleHelper.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using XiaoCai.DataAccess.Messages;

namespace XiaoCai.DataAccess.Ora
{
    public class OracleHelper:Helper
    {
        private readonly DbConnection _dbConnection;

        private readonly AppLog _appLog;


        public OracleHelper(DbConnection dbConnection) : base(dbConnection)
        {
            _dbConnection = dbConnection;
            _appLog = new AppLog(this.GetType().Name);
        }

        public override ExecutionResult ExecuteQueryDS(string sqlCommandText, List<DbParameter> dbParams)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                OracleCommand oraCommand = ((OracleConnection)_dbConnection).CreateCommand();
                oraCommand.CommandType = CommandType.Text;
                oraCommand.CommandText = sqlCommandText;
                foreach (DbParameter tmpSqlParam in dbParams)
                {
                    oraCommand.Parameters.Add(tmpSqlParam);
                }
               
                DataAdapter dataAdapter = new OracleDataAdapter(oraCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }

        public override ExecutionResult ExecuteQueryDS(string sqlCommandText)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                OracleCommand oraCommand = ((OracleConnection)_dbConnection).CreateCommand();
                oraCommand.CommandType = CommandType.Text;
                oraCommand.CommandText = sqlCommandText;
                DataAdapter dataAdapter = new OracleDataAdapter(oraCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }
    }
}

MySQLHelper.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XiaoCai.DataAccess.Messages;
using MySql.Data.MySqlClient;

namespace XiaoCai.DataAccess.MySQL
{
    public class MySQLHelper : Helper
    {
        private DbConnection _dbConnection;

        private readonly AppLog _appLog;


        public MySQLHelper(DbConnection dbConnection) : base(dbConnection)
        {
            _dbConnection = dbConnection;
            _appLog = new AppLog(this.GetType().Name);
        }

        public override ExecutionResult ExecuteQueryDS(string sqlCommandText, List<DbParameter> dbParams)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                MySqlCommand sqlCommand = ((MySqlConnection)_dbConnection).CreateCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlCommandText;
                foreach (DbParameter tmpSqlParam in dbParams)
                {
                    sqlCommand.Parameters.Add(tmpSqlParam);
                }
                DataAdapter dataAdapter = new MySqlDataAdapter(sqlCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }

        public override ExecutionResult ExecuteQueryDS(string sqlCommandText)
        {
            ExecutionResult result = new ExecutionResult();
            DataSet resultDS = new DataSet();

            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                MySqlCommand sqlCommand = ((MySqlConnection)_dbConnection).CreateCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlCommandText;
                DataAdapter dataAdapter = new MySqlDataAdapter(sqlCommand);
                dataAdapter.Fill(resultDS);
                result.Status = true;
                result.Message = "OK";
                result.Anything = resultDS;
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = ex.Message;
                //write exception log.
                if (_appLog.IsErrorEnabled)
                {
                    _appLog.Error(ex.Message);
                    _appLog.Error(ex.StackTrace);
                }
            }
            finally
            {

                if (_appLog != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
            }
            return result;
        }
    }
}

项目解决方案目录

image

测试案例

以下是以SQL Server为例,实例化一个帮助类(SQLHelper)对象,往方法里传SQL字符串和参数,输入结果,就这么简单。

  • TestSQLHelper.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using XiaoCai.DataAccess.Messages;

namespace XiaoCai.DataAccess.SQLServer.Test
{
    [NUnit.Framework.TestFixture]
    class TestSQLHelper
    {
        private Helper _helper = new SQLHelper(new SqlConnection(ConfigurationManager.AppSettings["Database.SqlServerConn"]));

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteQueryDS()
        {
            string sqlText = @"select * from T_AUTH_USER";
            ExecutionResult result=_helper.ExecuteQueryDS(sqlText);
            if (result.Status)
            {
                DataSet ds = (DataSet) result.Anything;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] +
                                          ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }

        [NUnit.Framework.Test] //带参数的方法
        public void ExecuteQueryDS2()
        {
            string sqlText = @"select * from T_AUTH_USER t where t.NAME=@UserName";
            DBParameter dbParam=new DBParameter();//实例化一个参数对象
            dbParam.Add("@UserName", "aganqin",new SqlParameter());//添加参数
            ExecutionResult result = _helper.ExecuteQueryDS(sqlText,dbParam.GetParameters());//执行查询方法
            if (result.Status)
            {
                DataSet ds = (DataSet)result.Anything;//取得结果
                if (ds.Tables[0].Rows.Count > 0)
                {
                    //显示结果
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] + ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
        }

        [NUnit.Framework.Test] //参数的方法
        public void ExecuteUpdate()
        {
            string sqlText = @"UPDATE T_AUTH_USER  SET [PASSWORD]=@PASSWORD where [NAME]=@UserName";
            DBParameter dbParam = new DBParameter();
            dbParam.Add("@UserName", "aganqin",new SqlParameter());
            dbParam.Add("@PASSWORD", "123456",new SqlParameter());
            ExecutionResult result = _helper.ExecuteUpdate(sqlText, dbParam.GetParameters());
            if (result.Status)
            {

                ExecuteQueryDS2();
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }
    }
}
  • TestOracleHelper.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using XiaoCai.DataAccess.Messages;
using XiaoCai.DataAccess.Ora;

namespace XiaoCai.DataAccess.Ora.Test
{
    [NUnit.Framework.TestFixture]
    class TestOracleHelper
    {
        private Helper _helper = new OracleHelper(new OracleConnection(ConfigurationManager.AppSettings["Database.SqlServerConn"]));

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteQueryDS()
        {
            string sqlText = @"select * from T_AUTH_USER";
            ExecutionResult result=_helper.ExecuteQueryDS(sqlText);
            if (result.Status)
            {
                DataSet ds = (DataSet) result.Anything;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] +
                                          ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteQueryDS2()
        {
            string sqlText = @"select * from T_AUTH_USER t where t.NAME=@UserName";
            DBParameter dbParam=new DBParameter();
            dbParam.Add("@UserName", "aganqin",new OracleParameter());
            ExecutionResult result = _helper.ExecuteQueryDS(sqlText,dbParam.GetParameters());
            if (result.Status)
            {

                DataSet ds = (DataSet)result.Anything;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] + ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
        }

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteUpdate()
        {
            string sqlText = @"UPDATE T_AUTH_USER  SET [PASSWORD]=@PASSWORD where [NAME]=@UserName";
            DBParameter dbParam = new DBParameter();
            dbParam.Add("@UserName", "aganqin", new OracleParameter());
            dbParam.Add("@PASSWORD", "123456", new OracleParameter());
            ExecutionResult result = _helper.ExecuteUpdate(sqlText, dbParam.GetParameters());
            if (result.Status)
            {

                ExecuteQueryDS2();
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }
    }
}
  • TestMySQLHelper.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using XiaoCai.DataAccess.Messages;
using XiaoCai.DataAccess.MySQL;

namespace XiaoCai.DataAccess.TestMySQLHelper.Test
{
    [NUnit.Framework.TestFixture]
    class TestOracleHelper
    {
        private Helper _helper = new MySQLHelper(new MySqlConnection(ConfigurationManager.AppSettings["Database.SqlServerConn"]));

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteQueryDS()
        {
            string sqlText = @"select * from T_AUTH_USER";
            ExecutionResult result=_helper.ExecuteQueryDS(sqlText);
            if (result.Status)
            {
                DataSet ds = (DataSet) result.Anything;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] +
                                          ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteQueryDS2()
        {
            string sqlText = @"select * from T_AUTH_USER t where t.NAME=@UserName";
            DBParameter dbParam=new DBParameter();
            dbParam.Add("@UserName", "aganqin",new MySqlParameter());
            ExecutionResult result = _helper.ExecuteQueryDS(sqlText,dbParam.GetParameters());
            if (result.Status)
            {

                DataSet ds = (DataSet)result.Anything;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        Console.WriteLine("UserId:" + ds.Tables[0].Rows[i][0] + ",User_Name:" + ds.Tables[0].Rows[i][1] + ",PASSWORD:" + ds.Tables[0].Rows[i][2]);
                    }
                }
            }
        }

        [NUnit.Framework.Test] //不带参数的方法
        public void ExecuteUpdate()
        {
            string sqlText = @"UPDATE T_AUTH_USER  SET [PASSWORD]=@PASSWORD where [NAME]=@UserName";
            DBParameter dbParam = new DBParameter();
            dbParam.Add("@UserName", "aganqin", new MySqlParameter());
            dbParam.Add("@PASSWORD", "123456", new MySqlParameter());
            ExecutionResult result = _helper.ExecuteUpdate(sqlText, dbParam.GetParameters());
            if (result.Status)
            {

                ExecuteQueryDS2();
            }
            else
            {
                Console.WriteLine(result.Message);
            }
        }
    }
}

测试结果如下:

image

    • 应用程序配置
<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="Database.SqlServerConn" value="Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=SecurityDB;User ID=root;Password=12345;" />
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
  </startup>
</configuration>

不同数据库类型相应变动。

总结

基本到这里结束,简单增加几个增,删,改,查的功能,后续添加上支持事务的数据库帮助类。

 

参考源码

发表评论
用户名: 匿名