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

背景

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

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

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

设计原理

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

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

辅助类和接口

添加一个参数接口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;
        }
    }
}

项目解决方案目录

测试案例

以下是以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);
            }
        }
    }
}

测试结果如下：

应用程序配置

<?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>

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

总结

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

参考源码

posted on 2013-11-24 13:40 aganqin 阅读(2994) 评论(6) 编辑收藏举报

刷新页面返回顶部