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 actno");
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.dTime))
{
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;parameter数据类型
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("数据库类型⽬前不⽀持！");
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论