IDbDataParameter的使用及数据工厂

IDbDataParameter[] para=DbFactory.CreateDbParameters(11)//DbFactory为数据工厂，括号里的为要创建的参数的个数

param[0].ParameterName=''@CreateUser";

param[0].Value=''CreaterUser的值";

..............................................................

param[11].ParameterName=''@SearchUser";

param[0].Value=''SearchUser的值";

 public static List<InvoiceEntity> getInvoiceByCondition(ConditionEntity ce)
        {
            var sql = new StringBuilder();
            List<InvoiceEntity> list = new List<InvoiceEntity>();
            InvoiceEntity ie = null;
            sql.Append(@"select a.ID, a.InvoiceType, a.CreateTime, a.CreateUser, a.FilePath, a.InvoiceStatus, a.ProjectNumber,
                    a.ContractNumber, a.CostCenter, a.Rate, a.Extension, a.CompanyName, a.CompanyAddress,
                    a.PhoneNumber, a.IdentifyNumber, a.OpenAccountBank, a.BankAccount, a.ClientPoNo,
                    a.ClientName, a.Currency, a.InvoiceSum, a.OperateTime, a.ExpressCompany, a.ExpressNo,
                    a.ExpressTime, a.InvoiceNumber, a.OperateUser, a.Memo, a.InvoiceID,");
            sql.Append(@"b.ContractNo, b.ProjectNo, b.MoneyType, b.ContractMoney, b.LastUpdateTime,");
            sql.Append(@"c.ProjectNo, c.ProjectName, c.AM, c.PM, c.LastUpdateTime");
            sql.Append(" from tbInvoice a");
            sql.Append(" left join forinvoicesystem_Po b on a.contractnumber=b.contractno");
            sql.Append(" left join forinvoicesystem_project c on b.projectno=c.projectno where 1=1");
            if (!string.IsNullOrEmpty(ce.CreateUser.ToString()))
            {
                sql.Append(" and (a.CreateUser=@CreateUser or c.AM=@CreateUser or c.PM=@CreateUser)");
            }
            if (!string.IsNullOrEmpty(ce.startTime) && !string.IsNullOrEmpty(ce.endTime))
            {
                sql.Append("  and (a.CreateTime>@startTime and CreateTime<@endTime)");
            }
            if (!string.IsNullOrEmpty(ce.ProjectNumber))
            {
                sql.Append("  and a.ProjectNumber like @ProjectNumber");
            }
            if (!string.IsNullOrEmpty(ce.CostCenter))
            {
                sql.Append("  and a.CostCenter=@CostCenter");
            }
            if (!string.IsNullOrEmpty(ce.InvoiceType))
            {
                sql.Append("  and a.InvoiceType=@InvoiceType");
            }
            else
            {
                ce.InvoiceType = string.Empty;
            }
            if (!string.IsNullOrEmpty(ce.invoiceStatus))
            {
                sql.Append("  and a.invoiceStatus=@invoiceStatus");
            }
            if (!string.IsNullOrEmpty(ce.AM) && !string.IsNullOrEmpty(ce.PM))
            {
                sql.Append("  and (c.AM=@AM and c.PM=@PM)");
            }
            else
            {
                if (!string.IsNullOrEmpty(ce.AM))
                {
                    sql.Append("  and c.AM=@AM");
                }
                if (!string.IsNullOrEmpty(ce.PM))
                {
                    sql.Append("  and c.PM=@PM");
                }
            }
            if (!string.IsNullOrEmpty(ce.ProjectName))
            {
                sql.Append("  and c.ProjectName like @ProjectName");
            }
            if (!string.IsNullOrEmpty(ce.SearchUser))
            {
                sql.Append("  and a.CreateUser = @SearchUser");
            }
            sql.Append(" order by a.ID desc");

            IDbDataParameter[] param = DbFactory.CreateDbParameters(11);
            param[0].ParameterName = "@CreateUser";
            param[0].Value = ce.CreateUser;
            param[1].ParameterName = "@startTime";
            param[1].Value = ce.startTime;
            param[2].ParameterName = "@endTime";
            param[2].Value = ce.endTime;
            param[3].ParameterName = "@ProjectNumber";
            param[3].Value = "%" + ce.ProjectNumber.Trim() + "%";
            param[4].ParameterName = "@CostCenter";
            param[4].Value = ce.CostCenter;
            param[5].ParameterName = "@InvoiceType";
            param[5].Value = ce.InvoiceType;
            param[6].ParameterName = "@invoiceStatus";
            param[6].Value = ce.invoiceStatus;
            param[7].ParameterName = "@AM";
            param[7].Value = ce.AM;
            param[8].ParameterName = "@PM";
            param[8].Value = ce.PM;
            param[9].ParameterName = "@ProjectName";
            param[9].Value = "%" + ce.ProjectName + "%";
            param[10].ParameterName = "@SearchUser";
            param[10].Value = ce.SearchUser;
            using (IDataReader reader = AdoHelper.ExecuteReader(AdoHelper.ConnectionString, System.Data.CommandType.Text, sql.ToString(), param))
            {
                while (reader.Read())
                {
                    ie = new InvoiceEntity();
                    ie.ID = Field.GetInt32(reader, "ID");
                    ie.InvoiceType = Field.GetInt32(reader, "InvoiceType");
                    ie.FilePath = Field.GetString(reader, "FilePath");
                    ie.Rate = Field.GetString(reader, "Rate");
                    ie.CompanyAddress = Field.GetString(reader, "CompanyAddress");
                    ie.PhoneNumber = Field.GetString(reader, "PhoneNumber");
                    ie.IdentifyNumber = Field.GetString(reader, "IdentifyNumber");
                    ie.OpenAccountBank = Field.GetString(reader, "OpenAccountBank");
                    ie.BankAccount = Field.GetString(reader, "BankAccount");
                    ie.ClientPoNo = Field.GetString(reader, "ClientPoNo");
                    ie.ClientName = Field.GetString(reader, "ClientName");
                    ie.Currency = Field.GetString(reader, "Currency");
                    ie.InvoiceSum = Field.GetDecimal(reader, "InvoiceSum");
                    ie.OperateTime = Field.GetDateTime(reader, "OperateTime");
                    ie.Memo = Field.GetString(reader, "Memo");
                    ie.ExpressTime = Field.GetString(reader, "ExpressTime");
                    ie.InvoiceNumber = Field.GetString(reader, "InvoiceNumber");
                    ie.OperateUser = Field.GetString(reader, "OperateUser");
                    ie.InvoiceStatus = Field.GetInt32(reader, "InvoiceStatus");
                    ie.ExpressCompany = Field.GetString(reader, "ExpressCompany");
                    ie.ExpressNo = Field.GetString(reader, "ExpressNo");
                    ie.CreateTime = Field.GetDateTime(reader, "CreateTime");
                    ie.CreateUser = Field.GetString(reader, "CreateUser");
                    ie.ContractNumber = Field.GetString(reader, "ContractNumber");
                    ie.ProjectNumber = Field.GetString(reader, "ProjectNumber");
                    ie.CostCenter = Field.GetString(reader, "CostCenter");
                    ie.Extension = Field.GetString(reader, "Extension");
                    ie.InvoiceID = Field.GetGuid(reader, "InvoiceID").ToString();
                    ie.CompanyName = Field.GetString(reader, "CompanyName");
                    //ID, InvoiceType, CreateTime, CreateUser, FilePath, InvoiceStatus, ProjectNumber,
                    //ContractNumber, CostCenter, Rate, Extension, CompanyName, CompanyAddress,
                    //PhoneNumber, IdentifyNumber, OpenAccountBank, BankAccount, ClientPoNo,
                    //ClientName, Currency, InvoiceSum, OperateTime, ExpressCompany, ExpressNo,
                    //ExpressTime, InvoiceNumber, OperateUser, Memo, InvoiceID

                    ie.Po = new PoEntity();
                    ie.Po.ContractMoney = Field.GetDecimal(reader, "ContractMoney");
                    ie.Po.ContractNo = Field.GetString(reader, "ContractNo");
                    ie.Po.LastUpdateTime = Field.GetDateTime(reader, "LastUpdateTime");
                    ie.Po.MoneyType = Field.GetString(reader, "MoneyType");
                    ie.Po.ProjectNo = Field.GetInt64(reader, "ProjectNo");

                    ie.Po.Project = new ProjectEntity();
                    ie.Po.Project.ProjectName = Field.GetString(reader, "ProjectName");
                    ie.Po.Project.ProjectNo = Field.GetInt64(reader, "ProjectNo").ToString();
                    ie.Po.Project.LastUpdateTime = Field.GetDateTime(reader, "LastUpdateTime");
                    ie.Po.Project.AM = Field.GetString(reader, "am");
                    ie.Po.Project.PM = Field.GetString(reader, "pm");
                    list.Add(ie);
                }
            }
            return list;
        }

DbFactory的工厂

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Utility;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;

namespace BaseDataAccess
{
    public class DbFactory
    {


        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 来获取命令参数中的参数符号oracle为":",sqlserver为"@"
        /// </summary>
        /// <returns></returns>
        public static string CreateDbParmCharacter()
        {
            string character = string.Empty;

            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    character = "@";
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    character = ":";
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    character = "@";
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");
            }

            return character;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型和传入的
        /// 数据库链接字符串来创建相应数据库连接对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static IDbConnection CreateDbConnection(string connectionString)
        {
            IDbConnection conn = null;
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    conn = new SqlConnection(connectionString);
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    conn = new OracleConnection(connectionString);
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    conn = new OleDbConnection(connectionString);
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");
            }

            return conn;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 来创建相应数据库命令对象
        /// </summary>
        /// <returns></returns>
        public static IDbCommand CreateDbCommand()
        {
            IDbCommand cmd = null;
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    cmd = new SqlCommand();
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    cmd = new OracleCommand();
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    cmd = new OleDbCommand();
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");
            }

            return cmd;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 来创建相应数据库适配器对象
        /// </summary>
        /// <returns></returns>
        public static IDbDataAdapter CreateDataAdapter()
        {
            IDbDataAdapter adapter = null;
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    adapter = new SqlDataAdapter();
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    adapter = new OracleDataAdapter();
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    adapter = new OleDbDataAdapter();
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");
            }

            return adapter;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 和传入的命令对象来创建相应数据库适配器对象
        /// </summary>
        /// <returns></returns>
        public static IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
        {
            IDbDataAdapter adapter = null;
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    adapter = new SqlDataAdapter((SqlCommand)cmd);
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    adapter = new OracleDataAdapter((OracleCommand)cmd);
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    adapter = new OleDbDataAdapter((OleDbCommand)cmd);
                    break;
                default: throw new Exception("数据库类型目前不支持！");
            }

            return adapter;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 来创建相应数据库的参数对象
        /// </summary>
        /// <returns></returns>
        public static IDbDataParameter CreateDbParameter()
        {
            IDbDataParameter param = null;
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    param = new SqlParameter();
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    param = new OracleParameter();
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    param = new OleDbParameter();
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");
            }

            return param;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 和传入的参数来创建相应数据库的参数数组对象
        /// </summary>
        /// <returns></returns>
        public static IDbDataParameter[] CreateDbParameters(int size)
        {
            int i = 0;
            IDbDataParameter[] param = null;
            string a = AdoHelper.DbType.ToString();
            switch (AdoHelper.DbType)
            {
                case AppEmun.DatabaseType.SQLSERVER:
                    param = new SqlParameter[size];
                    while (i < size) { param[i] = new SqlParameter(); i++; }
                    break;
                case AppEmun.DatabaseType.ORACLE:
                    param = new OracleParameter[size];
                    while (i < size) { param[i] = new OracleParameter(); i++; }
                    break;
                case AppEmun.DatabaseType.ACCESS:
                    param = new OleDbParameter[size];
                    while (i < size) { param[i] = new OleDbParameter(); i++; }
                    break;
                default:
                    throw new Exception("数据库类型目前不支持！");

            }

            return param;
        }

        /// <summary>
        /// 根据配置文件中所配置的数据库类型
        /// 来创建相应数据库的事物对象
        /// </summary>
        /// <returns></returns>
        public static IDbTransaction CreateDbTransaction()
        {
            IDbConnection conn = CreateDbConnection(AdoHelper.ConnectionString);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            return conn.BeginTransaction();
        }
    }
}