如果有成熟的架构，如何根据数据库关系的表、视图等，进行代码生成架构？减少写代码的时间？
-- 考虑主键外键
--
create database geovindu;
use geovindu;
--2
create table EnterpriseType
(
EnterpriseTypeID INTEGER PRIMARY KEY AUTOINCREMENT,
EnterpriseTypeName nvarchar(100) not null --企业类型名称
);
insert into EnterpriseType (EnterpriseTypeName) values(N'分公司');
insert into EnterpriseType (EnterpriseTypeName) values(N'店铺');
select * from OperatingUser;
-- 3
drop table CompanyBranch;
create table CompanyBranch
(
CompanyID INTEGER PRIMARY KEY AUTOINCREMENT,
CompanyName nvarchar(100) not null,
CompanyTypeID int not null,
CompanyDate datetime DEFAULT (datetime('now','localtime')),
CompanyDesc NVARCHAR(500) null,
CompanyTel varchar(100) null,
CompanyFax VARCHAR(100) NULL,
CompanyAddress NVARCHAR(500) NULL,
FOREIGN KEY(CompanyTypeID) REFERENCES EnterpriseType(EnterpriseTypeID)
);
insert into CompanyBranch(CompanyName,CompanyTypeID,CompanyTel,CompanyFax,CompanyAddress,CompanyDesc) values('六福珠宝营销策划(深圳）有限公司',1,'','','','');
select * from CompanyBranch
select * from EnterpriseType
drop table OperatingUser;
-- 1
create table OperatingUser
(
UserID INTEGER PRIMARY KEY AUTOINCREMENT,
UserName nvarchar(200) not null, --用户名
RealName NVARCHAR(50) NOT NULL, --真姓名
UserPassword varchar(100) not null, --密码
UserCompanyId int not null, --公司ID
UserPasswordProblems nvarchar(100), --找回密码问题
UserMail varchar(100) null, --密码
UserDate datetime DEFAULT (datetime('now','localtime')), --默认日期
FOREIGN KEY(UserCompanyId) REFERENCES CompanyBranch(CompanyID)
);
select * from OperatingUser;
--4
drop table LoginDiaryList;
CREATE TABLE LoginDiaryList
(
LoginDiaryID INTEGER PRIMARY KEY AUTOINCREMENT,
LoginDiaryUserName nvarchar(50) null, --登錄用戶名
LoginDiaryUserId int not null, --員工ID
LoginDiaryBrowser varchar(50) null, --客戶端瀏覽
LoginDiaryScreen varchar(50) null, --显示器大小
LoginDiaryOpertor varchar(50) null, --操作系統
LoginDiaryInput nvarchar(150) null, --輸入法
LoginDiaryDate datetime DEFAULT (datetime('now','localtime')), --日期
FOREIGN KEY(LoginDiaryUserId) REFERENCES OperatingUser(UserID)
);
select * from LoginDiaryList;
select * from PrintWordDocumentTemplateList;
-- 5
create table PrintWordDocumentTemplateList
(
PrintWordDocumentId INTEGER PRIMARY KEY AUTOINCREMENT,
--PrintWordDocumentUid Uniqueidentifier DEFAULT(NEWID()) PRIMARY KEY,
--PrintWordPayTypeUidKey Uniqueidentifier, ---考核類型(試用期,年終,特別)
--PrintWordJobTypeUidKey Uniqueidentifier, ---職位類型(文職類,員工類,管理級別類等)
PrintWordOnlyPassIs bit default(0), ---通過試用期並成為正式員工
PrintWordPlusSalaryIs bit default(0), ---通過並加薪
PrintWordPromotionIs bit default(0), ---通過晉升
PrintWordExtensionIs bit default(0), --延長試用期
PrintWordDismissIs bit default(0), --解僱
PrintWordDepartmentIs bit default(0), --新部門
PrintWordDocumentName Nvarchar(100) not null, --文檔標題
PrintWordDocumentUrl nvarchar(200) null, --文檔鏈接
PrintWordDocumentContent nvarchar(300) null, --文檔簡要描述
PrintWordDocumentAddDate datetime DEFAULT (datetime('now','localtime')) ,
PrintWordDocumentByte BLOB null,
PrintWordType int default(1) --文檔類型 1.分公司，2.分店
--PrintWordLetterSignature nvarchar(100) null --信函簽名
);
-- 6
-- 客戶表Customer（需方） 名稱，工地名稱
drop table CustomerList;
CREATE TABLE CustomerList
(
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
CustomerName NVARCHAR(200) NOT NULL,
CustomerNamePin VARCHAR(500) NULL,
CustomerContact NVARCHAR(50) NULL, --聯系人
CustomerTel VARCHAR(100) NULL, -- 聯系人電話
CustomerDate datetime DEFAULT (datetime('now','localtime'))
);
-- 7表單關聯人類型RelationshipsType： 指定收貨人 跟單業務員 工地驗收人 運輸人
drop table RelationshipsType;
CREATE TABLE RelationshipsType
(
RelationshipsTypeID INTEGER PRIMARY KEY AUTOINCREMENT,
RelationshipsTypeName NVARCHAR(100) NOT NULL,
RelationshipsTypePin VARCHAR(500) NULL
);
insert into RelationshipsType(RelationshipsTypeName) values('指定收货人');
insert into RelationshipsType(RelationshipsTypeName) values('跟单业务员');
insert into RelationshipsType(RelationshipsTypeName) values('工地验收人');
insert into RelationshipsType(RelationshipsTypeName) values('运输人');
-- 8表單關係人錶RelationshipsPerson
drop table RelationshipsPerson;
CREATE TABLE RelationshipsPerson
(
PersonID INTEGER PRIMARY KEY AUTOINCREMENT,
PersonName NVARCHAR(100) NOT NULL,
PersonNamePin VARCHAR(500) NULL,
PersonTel VARCHAR(100),
PersonType int NOT NULL,
PersonDate datetime DEFAULT (datetime('now','localtime')),
FOREIGN KEY(PersonType) REFERENCES RelationshipsType(RelationshipsTypeID)
);
select * from RelationshipsPerson;
-- 9產品名稱表 ProductTypeList
drop table ProductTypeList;
CREATE TABLE ProductTypeList
(
ProductTypeID INTEGER PRIMARY KEY AUTOINCREMENT,
ProductTypeName NVARCHAR(800) NOT NULL,
ProductTypePin VARCHAR(500) NULL --字首字母
);
-- 10單位表 UnitList
drop table UnitList;
CREATE TABLE UnitList
(
UnitID INTEGER PRIMARY KEY AUTOINCREMENT,
UnitName NVARCHAR(100) NOT NULL,
UnitPin VARCHAR(500) NULL
);
select * from UnitList;
delete from UnitList where UnitName='';
drop table ProductModel;
-- 产品规格
CREATE TABLE ProductModel
(
ModelID INTEGER PRIMARY KEY AUTOINCREMENT,
ModelProductTypeID INT NOT NULL, -- 产品名称ID 外键 ProductTypeList
ModelName NVARCHAR(800) NOT NULL,
ModelPin VARCHAR(500) NULL,
FOREIGN KEY(ModelProductTypeID) REFERENCES ProductTypeList(ProductTypeID)
);
drop table UnitPrice;
-- 单价表
CREATE TABLE UnitPrice
(
UnitPriceID INTEGER PRIMARY KEY AUTOINCREMENT,
UnitProductTypeID INT NOT NULL, -- 产品名称ID 外键 ProductTypeList
UnitPriceNuber DECIMAL(20,2) NOT NULL,
UnitPricePin VARCHAR(500) NULL,
FOREIGN KEY(UnitProductTypeID) REFERENCES ProductTypeList(ProductTypeID)
);
select * from UnitPrice;
drop table CustomerAddress;
-- 客户地址表
CREATE TABLE CustomerAddress
(
AddressID INTEGER PRIMARY KEY AUTOINCREMENT,
AddressName NVARCHAR(100) NOT NULL,
AddressPin VARCHAR(500) NULL
);
-- 11級別表 LevelList
drop table LevelList;
CREATE TABLE LevelList
(
LevelID INTEGER PRIMARY KEY AUTOINCREMENT,
LevelName NVARCHAR(100) NOT NULL,
LevelPin VARCHAR(500) NULL
);
select * from LevelList;
delete from LevelList where LevelName='';
-- 12工地名名稱表 建筑工地名Construction site name
drop table ConstructionNameList;
CREATE TABLE ConstructionNameList
(
ConstructionID INTEGER PRIMARY KEY AUTOINCREMENT,
ConstructionName VARCHAR(100) NOT NULL,
ConstructionPin VARCHAR(500) NULL
);
select * from ConstructionNameList;
delete from ConstructionNameList where ConstructionName='';
-- 訂單產品詳情表
drop table OrderItDetails;
CREATE TABLE OrderItDetails
(
OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
OrderDate datetime DEFAULT (datetime('now','localtime')), -- 送货日期
OrderWord VARCHAR(50) NULL, -- 字
OrderNo VARCHAR(100) NULL, -- 号
OrderCustomerId INT NOT NULL, -- 客户名稱
OrderAddressID INT NOT NULL, -- 客户地址名稱
OrderPrepared INT NULL, -- 製單人
OrderBusiness INT NULL, -- 指定收货人
OrderPrintDate datetime DEFAULT (datetime('now','localtime')), --打單時間
FOREIGN KEY(OrderCustomerId) REFERENCES CustomerList(CustomerID),
FOREIGN KEY(OrderAddressID) REFERENCES CustomerAddress(AddressID),
FOREIGN KEY(OrderPrepared) REFERENCES RelationshipsPerson(PersonID),
FOREIGN KEY(OrderBusiness) REFERENCES RelationshipsPerson(PersonID)
);
-- 訂單產品詳情
drop table ProductItOrderDetails;
CREATE TABLE ProductItOrderDetails
(
ProductDetailsId INTEGER PRIMARY KEY AUTOINCREMENT,
ProductOrderId INT NOT NULL, -- 產品訂單ID 外錶OrderDetails
ProductOrderTypeId INT NOT NULL, --產品名稱
ProductModleId INT, --規格
ProductUnitID INT , -- 單位
ProductQty DECIMAL(18,2) DEFAULT(0), --數量
ProductPriceID INT NOT NULL, --单价
ProductMeters DECIMAL(25,2) DEFAULT(0), --金额
ProductDescription VARCHAR(1000) NULL, --說明
FOREIGN KEY(ProductOrderId) REFERENCES OrderItDetails(OrderID),
FOREIGN KEY(ProductOrderTypeId) REFERENCES ProductTypeList(ProductTypeID),
FOREIGN KEY(ProductModleId) REFERENCES ProductModel(ModelID),
FOREIGN KEY(ProductUnitID) REFERENCES UnitList(UnitID),
FOREIGN KEY(ProductPriceID) REFERENCES UnitPrice(UnitPriceID)
);
-- 13打單表內容 ProductDetails
-- https://www.sqlite.org/datatype3.html
drop table OrderDetails;
CREATE TABLE OrderDetails
(
OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
OrderDate datetime DEFAULT (datetime('now','localtime')), --日期
OrderWord NVARCHAR(50) NULL, -- 字
OrderNo NVARCHAR(100) NULL, --号
OrderCustomerId int NOT NULL, --需求方
OrderConstructionId int NOT NULL, --工地名稱
--OrderProductId int NOT NULL, -- 產品訂單詳情ID 外錶ProductOrderDetails
OrderAcceptor int NULL, -- 工地驗收人
OrderTransportation INTEGER NULL, -- 運輸人
OrderPrepared int NULL, --製單人
OrderBusiness int NULL, -- 指定業務人
OrderMerchandiser int NULL, --跟單業務員
OrderPrintDate datetime DEFAULT (datetime('now','localtime')), --打單時間
FOREIGN KEY(OrderCustomerId) REFERENCES CustomerList(CustomerID),
FOREIGN KEY(OrderConstructionId) REFERENCES ConstructionNameList(ConstructionID),
FOREIGN KEY(OrderAcceptor) REFERENCES RelationshipsPerson(PersonID),
FOREIGN KEY(OrderTransportation) REFERENCES RelationshipsPerson(PersonID),
FOREIGN KEY(OrderPrepared) REFERENCES RelationshipsPerson(PersonID),
FOREIGN KEY(OrderBusiness) REFERENCES RelationshipsPerson(PersonID),
FOREIGN KEY(OrderMerchandiser) REFERENCES RelationshipsPerson(PersonID)
);
-- 14訂單產品詳情表 NUMERIC
drop table ProductOrderDetails;
CREATE TABLE ProductOrderDetails
(
ProductDetailsId INTEGER PRIMARY KEY AUTOINCREMENT,
ProductOrderId INT NOT NULL, -- 產品訂單ID 外錶OrderDetails
ProductTypeId int NOT NULL, --產品名稱規格
ProductUnitID int NOT NULL, -- 單位
ProductQty DECIMAL(18,2) DEFAULT(0), --數量
ProductLevelID int NOT NULL, --級別
ProductMeters DECIMAL(25,2) DEFAULT(0), --米數
ProductDescription NVARCHAR(1000) NULL, --說明
FOREIGN KEY(ProductOrderId) REFERENCES OrderDetails(OrderID),
FOREIGN KEY(ProductTypeId) REFERENCES ProductTypeList(ProductTypeID),
FOREIGN KEY(ProductUnitID) REFERENCES UnitList(UnitID),
FOREIGN KEY(ProductLevelID) REFERENCES LevelList(LevelID)
);
-- 設置移動打印X，Y坐標糾正值
drop table PrintSetNumber;
CREATE TABLE PrintSetNumber
(
PrintSetId INTEGER PRIMARY KEY AUTOINCREMENT,
PrintSetX int DEFAULT(0), --com 列坐标
PrintSetY int DEFAULT(0) , -- row 行坐标
PrintPrinter VARCHAR(200) NULL, --默认打印机名
PrintFont VARCHAR(200) NULL default '宋体' ,---默认字体名
PrintBottom boolean default(0), --底部文字是否双排
TitleFontSize int default(8), -- 标题字体大小
ConFontSize int default(8), -- 内容字体大小
HeadFontSize int default(8), --表头字体大小
BoomFontSize int default(8) -- 表底字体大小
);
--表的描述
drop table DataTableDesc;
CREATE TABLE DataTableDesc
(
TableId INTEGER PRIMARY KEY AUTOINCREMENT,
TableName nvahrcar(100) not null,
TableDesc nvarchar(100) null
);
--列的列描述 columnName
drop table DataColumnDesc;
CREATE TABLE DataColumnDesc
(
ColumnId INTEGER PRIMARY KEY AUTOINCREMENT,
ColumnTableId int not null,
ColumnName nvahrcar(100) not null,
ColumnDesc nvarchar(100) null,
FOREIGN KEY(ColumnTableId) REFERENCES DataTableDesc(TableId)
);
select * from DataTableDesc;
select * from DataColumnDesc;
select * from PrintSetNumber;
--视图
CREATE VIEW v_DataTableColumn as select DataColumnDesc.*,DataTableDesc.TableName,DataTableDesc.TableDesc from DataColumnDesc,DataTableDesc
where DataTableDesc.TableId=DataColumnDesc.ColumnTableId;
CREATE VIEW v_OperatingUser as select OperatingUser.*,CompanyBranch.CompanyName,CompanyBranch.companyTel,CompanyBranch.CompanyFax,CompanyBranch.CompanyAddress from OperatingUser,CompanyBranch where OperatingUser.UserCompanyId=CompanyBranch.CompanyID
drop view v_CompanyBranch;
--公司
create view v_CompanyBranch
as
select CompanyBranch.*,EnterpriseType.EnterpriseTypeName from CompanyBranch, EnterpriseType where CompanyBranch.CompanyTypeID=EnterpriseType.EnterpriseTypeID;
select CompanyBranch.*,EnterpriseType.EnterpriseTypeName from CompanyBranch, EnterpriseType where CompanyBranch.CompanyTypeID=EnterpriseType.EnterpriseTypeID;
select * from v_CompanyBranch;
drop view v_OperatingUser;
--用户
create view v_OperatingUser
as
select OperatingUser.*,CompanyBranch.CompanyName,CompanyBranch.companyTel,CompanyBranch.CompanyFax,CompanyBranch.CompanyAddress from OperatingUser,CompanyBranch where OperatingUser.UserCompanyId=CompanyBranch.CompanyID;
select * from v_OperatingUser;
--錶單用戶視圖
CREATE VIEW view_relationshipsPerson
AS
select RelationshipsPerson.*,RelationshipsType.RelationshipsTypeName FROM RelationshipsPerson,RelationshipsType
WHERE RelationshipsPerson.PersonType=RelationshipsType.RelationshipsTypeID;
主、外键关联要考虑
实体类层：
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
namespace Geovin.Du.Model
{
/// <summary>
/// ProductTypeList的实体类產品名稱表
///生成時間2018/9/14 15:11:05
///塗聚文(Geovin Du)
///</summary>
public class ProductTypeListInfo
{
private int _ProductTypeID;
/// <summary>
/// ID,主键
/// </summary>
public int ProductTypeID
{
get { return _ProductTypeID; }
set {_ProductTypeID = value; }
}
private string _ProductTypeName;
/// <summary>
/// 产品名称
/// </summary>
public string ProductTypeName
{
get { return _ProductTypeName; }
set {_ProductTypeName = value; }
}
private string _ProductTypePin;
/// <summary>
/// 字首字母
/// </summary>
public string ProductTypePin
{
get { return _ProductTypePin; }
set {_ProductTypePin = value; }
}
private List<ProductModelInfo> _ProductModel;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductModel产品规格,外键字段：ModelProductTypeID;
///</summary>
public List<ProductModelInfo> ProductModelList
{
get { return _ProductModel; }
set {_ProductModel = value; }
}
private DataTable _ProductModelData;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductModel产品规格,外键字段：ModelProductTypeID;
///</summary>
public DataTable ProductModelData
{
get { return _ProductModelData; }
set {_ProductModelData = value; }
}
private DataSet _ProductModelDaset;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductModel产品规格,外键字段：ModelProductTypeID;
///</summary>
public DataSet ProductModelDaset
{
get { return _ProductModelDaset; }
set {_ProductModelDaset = value; }
}
private List<UnitPriceInfo> _UnitPrice;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：UnitPrice单价表,外键字段：UnitProductTypeID;
///</summary>
public List<UnitPriceInfo> UnitPriceList
{
get { return _UnitPrice; }
set {_UnitPrice = value; }
}
private DataTable _UnitPriceData;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：UnitPrice单价表,外键字段：UnitProductTypeID;
///</summary>
public DataTable UnitPriceData
{
get { return _UnitPriceData; }
set {_UnitPriceData = value; }
}
private DataSet _UnitPriceDaset;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：UnitPrice单价表,外键字段：UnitProductTypeID;
///</summary>
public DataSet UnitPriceDaset
{
get { return _UnitPriceDaset; }
set {_UnitPriceDaset = value; }
}
private List<ProductItOrderDetailsInfo> _ProductItOrderDetails;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductItOrderDetails,外键字段：ProductTypeId;
///</summary>
public List<ProductItOrderDetailsInfo> ProductItOrderDetailsList
{
get { return _ProductItOrderDetails; }
set {_ProductItOrderDetails = value; }
}
private DataTable _ProductItOrderDetailsData;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductItOrderDetails,外键字段：ProductTypeId;
///</summary>
public DataTable ProductItOrderDetailsData
{
get { return _ProductItOrderDetailsData; }
set {_ProductItOrderDetailsData = value; }
}
private DataSet _ProductItOrderDetailsDaset;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductItOrderDetails,外键字段：ProductTypeId;
///</summary>
public DataSet ProductItOrderDetailsDaset
{
get { return _ProductItOrderDetailsDaset; }
set {_ProductItOrderDetailsDaset = value; }
}
private List<ProductOrderDetailsInfo> _ProductOrderDetails;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductOrderDetails,外键字段：ProductTypeId;
///</summary>
public List<ProductOrderDetailsInfo> ProductOrderDetailsList
{
get { return _ProductOrderDetails; }
set {_ProductOrderDetails = value; }
}
private DataTable _ProductOrderDetailsData;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductOrderDetails,外键字段：ProductTypeId;
///</summary>
public DataTable ProductOrderDetailsData
{
get { return _ProductOrderDetailsData; }
set {_ProductOrderDetailsData = value; }
}
private DataSet _ProductOrderDetailsDaset;
/// <summary>
/// 主表：ProductTypeList產品名稱表,外键表：ProductOrderDetails,外键字段：ProductTypeId;
///</summary>
public DataSet ProductOrderDetailsDaset
{
get { return _ProductOrderDetailsDaset; }
set {_ProductOrderDetailsDaset = value; }
}
}
}
数据处理层：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Drawing;
using Geovin.Du.Commn;
using Geovin.Du.Model;
using Geovin.Du.Interface;
namespace Geovin.Du.SQLiteDAL
{
/// <summary>
/// ProductItOrderDetails数据访问层
///生成時間2018/9/14 14:36:05
///塗聚文(Geovin Du)
///</summary>
public class ProductItOrderDetailsDAL : IProductItOrderDetails
{
///<summary>
/// 追加记录
///</summary>
///<param name="ProductItOrderDetailsInfo"></param>
///<returns></returns>
public int InsertProductItOrderDetails(ProductItOrderDetailsInfo productItOrderDetails)
{
int ret = 0;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO ProductItOrderDetails([ProductOrderId] ,[ProductTypeId] ,[ProductModleId] ,[ProductUnitID] ,[ProductQty] ,[ProductPriceID] ,[ProductMeters] ,[ProductDescription]");
strSql.Append(") VALUES (");
strSql.Append("@ProductOrderId ,@ProductTypeId ,@ProductModleId ,@ProductUnitID ,@ProductQty ,@ProductPriceID ,@ProductMeters ,@ProductDescription)");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",DbType.Int32,4),
new SQLiteParameter("@ProductTypeId",DbType.Int32,4),
new SQLiteParameter("@ProductModleId",DbType.Int32,4),
new SQLiteParameter("@ProductUnitID",DbType.Int32,4),
new SQLiteParameter("@ProductQty",DbType.Decimal,8),
new SQLiteParameter("@ProductPriceID",DbType.Int32,4),
new SQLiteParameter("@ProductMeters",DbType.Decimal,8),
new SQLiteParameter("@ProductDescription",DbType.String,1000),
};
par[0].Value = productItOrderDetails.ProductOrderId;
par[1].Value = productItOrderDetails.ProductTypeId;
par[2].Value = productItOrderDetails.ProductModleId;
par[3].Value = productItOrderDetails.ProductUnitID;
par[4].Value = productItOrderDetails.ProductQty;
par[5].Value = productItOrderDetails.ProductPriceID;
par[6].Value = productItOrderDetails.ProductMeters;
par[7].Value = productItOrderDetails.ProductDescription;
ret = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 追加记录返回值
///</summary>
///<param name="ProductItOrderDetailsInfo"></param>
///<returns></returns>
public int InsertProductItOrderDetailsOutput (ProductItOrderDetailsInfo productItOrderDetails,out int productDetailsId)
{
int ret = 0;
productDetailsId= 0;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO ProductItOrderDetails([ProductOrderId] ,[ProductTypeId] ,[ProductModleId] ,[ProductUnitID] ,[ProductQty] ,[ProductPriceID] ,[ProductMeters] ,[ProductDescription]");
strSql.Append(") VALUES (");
strSql.Append("@ProductOrderId ,@ProductTypeId ,@ProductModleId ,@ProductUnitID ,@ProductQty ,@ProductPriceID ,@ProductMeters ,@ProductDescription)");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",DbType.Int32,4),
new SQLiteParameter("@ProductTypeId",DbType.Int32,4),
new SQLiteParameter("@ProductModleId",DbType.Int32,4),
new SQLiteParameter("@ProductUnitID",DbType.Int32,4),
new SQLiteParameter("@ProductQty",DbType.Decimal,8),
new SQLiteParameter("@ProductPriceID",DbType.Int32,4),
new SQLiteParameter("@ProductMeters",DbType.Decimal,8),
new SQLiteParameter("@ProductDescription",DbType.String,1000),
new SQLiteParameter("@ProductDetailsId",DbType.Int32,8),
};
par[0].Value = productItOrderDetails.ProductOrderId;
par[1].Value = productItOrderDetails.ProductTypeId;
par[2].Value = productItOrderDetails.ProductModleId;
par[3].Value = productItOrderDetails.ProductUnitID;
par[4].Value = productItOrderDetails.ProductQty;
par[5].Value = productItOrderDetails.ProductPriceID;
par[6].Value = productItOrderDetails.ProductMeters;
par[7].Value = productItOrderDetails.ProductDescription;
//par[8].Direction = ParameterDirection.Output;
ret = SQLiteHelper.ExecuteSql(strSql.ToString(), out productDetailsId, par);
if(ret>0)
{
//productDetailsId=(int)par[8].Value;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
///<summary>
///修改记录
///</summary>
///<param name="ProductItOrderDetailsInfo"></param>
///<returns></returns>
public int UpdateProductItOrderDetails(ProductItOrderDetailsInfo productItOrderDetails)
{
int ret = 0;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE ProductItOrderDetails SET ");
strSql.Append("[ProductOrderId]=@ProductOrderId ,");
strSql.Append("[ProductTypeId]=@ProductTypeId ,");
strSql.Append("[ProductModleId]=@ProductModleId ,");
strSql.Append("[ProductUnitID]=@ProductUnitID ,");
strSql.Append("[ProductQty]=@ProductQty ,");
strSql.Append("[ProductPriceID]=@ProductPriceID ,");
strSql.Append("[ProductMeters]=@ProductMeters ,");
strSql.Append("[ProductDescription]=@ProductDescription");
strSql.Append(" where ");
strSql.Append("[ProductDetailsId]=@ProductDetailsId");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",DbType.Int32,4),
new SQLiteParameter("@ProductTypeId",DbType.Int32,4),
new SQLiteParameter("@ProductModleId",DbType.Int32,4),
new SQLiteParameter("@ProductUnitID",DbType.Int32,4),
new SQLiteParameter("@ProductQty",DbType.Decimal,8),
new SQLiteParameter("@ProductPriceID",DbType.Int32,4),
new SQLiteParameter("@ProductMeters",DbType.Decimal,8),
new SQLiteParameter("@ProductDescription",DbType.String,1000),
new SQLiteParameter("@ProductDetailsId",DbType.Int32),
};
par[0].Value = productItOrderDetails.ProductOrderId;
par[1].Value = productItOrderDetails.ProductTypeId;
par[2].Value = productItOrderDetails.ProductModleId;
par[3].Value = productItOrderDetails.ProductUnitID;
par[4].Value = productItOrderDetails.ProductQty;
par[5].Value = productItOrderDetails.ProductPriceID;
par[6].Value = productItOrderDetails.ProductMeters;
par[7].Value = productItOrderDetails.ProductDescription;
par[8].Value = productItOrderDetails.ProductDetailsId;
ret = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 删除记录
///</summary>
///<param name="productDetailsIdInfo"></param>
///<returns></returns>
public bool DeleteProductItOrderDetails(int productDetailsId)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from productItOrderDetails");
strSql.Append(" where ProductDetailsId=");
strSql.Append("@ProductDetailsId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductDetailsId",productDetailsId)
};
int temp = 0 ;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if(temp!=0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public bool DeleteProductPriceID(int productPriceID)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from ProductItOrderDetails");
strSql.Append(" where ProductPriceID=");
strSql.Append("@ProductPriceID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductPriceID",productPriceID)
}
int temp = 0;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if (temp != 0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public bool DeleteProductUnitID(int productUnitID)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from ProductItOrderDetails");
strSql.Append(" where ProductUnitID=");
strSql.Append("@ProductUnitID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductUnitID",productUnitID)
}
int temp = 0;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if (temp != 0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public bool DeleteProductModleId(int productModleId)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from ProductItOrderDetails");
strSql.Append(" where ProductModleId=");
strSql.Append("@ProductModleId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductModleId",productModleId)
}
int temp = 0;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if (temp != 0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public bool DeleteProductTypeId(int productTypeId)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from ProductItOrderDetails");
strSql.Append(" where ProductTypeId=");
strSql.Append("@ProductTypeId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductTypeId",productTypeId)
}
int temp = 0;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if (temp != 0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 删除外键的记录，当此关联的主键表要删除记录，外键记录要删除
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public bool DeleteProductOrderId(int productOrderId)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from ProductItOrderDetails");
strSql.Append(" where ProductOrderId=");
strSql.Append("@ProductOrderId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",productOrderId)
}
int temp = 0;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if (temp != 0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 查询记录
///</summary>
///<param name="productDetailsIdInfo"></param>
///<returns></returns>
public ProductItOrderDetailsInfo SelectProductItOrderDetails(int productDetailsId)
{
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from productItOrderDetails");
strSql.Append(" where ProductDetailsId=");
strSql.Append("@ProductDetailsId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductDetailsId",productDetailsId)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
if (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return productItOrderDetails;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductPriceID">外键ID</param>
/// <returns></returns>
public List<ProductItOrderDetailsInfo> SelectListProductItOrderDetailsProductPriceID(int productPriceID)
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductPriceID=");
strSql.Append("@ProductPriceID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductPriceID",productPriceID)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductPriceID">外键ID</param>
/// <returns></returns>
public DataTable SelectProductItOrderDetailsProductPriceID(int productPriceID)
{
DataTable dt = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductPriceID=");
strSql.Append("@ProductPriceID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductPriceID",productPriceID)
};
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, par))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductUnitID">外键ID</param>
/// <returns></returns>
public List<ProductItOrderDetailsInfo> SelectListProductItOrderDetailsProductUnitID(int productUnitID)
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductUnitID=");
strSql.Append("@ProductUnitID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductUnitID",productUnitID)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductUnitID">外键ID</param>
/// <returns></returns>
public DataTable SelectProductItOrderDetailsProductUnitID(int productUnitID)
{
DataTable dt = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductUnitID=");
strSql.Append("@ProductUnitID ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductUnitID",productUnitID)
};
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, par))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductModleId">外键ID</param>
/// <returns></returns>
public List<ProductItOrderDetailsInfo> SelectListProductItOrderDetailsProductModleId(int productModleId)
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductModleId=");
strSql.Append("@ProductModleId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductModleId",productModleId)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductModleId">外键ID</param>
/// <returns></returns>
public DataTable SelectProductItOrderDetailsProductModleId(int productModleId)
{
DataTable dt = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductModleId=");
strSql.Append("@ProductModleId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductModleId",productModleId)
};
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, par))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductTypeId">外键ID</param>
/// <returns></returns>
public List<ProductItOrderDetailsInfo> SelectListProductItOrderDetailsProductTypeId(int productTypeId)
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductTypeId=");
strSql.Append("@ProductTypeId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductTypeId",productTypeId)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductTypeId">外键ID</param>
/// <returns></returns>
public DataTable SelectProductItOrderDetailsProductTypeId(int productTypeId)
{
DataTable dt = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductTypeId=");
strSql.Append("@ProductTypeId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductTypeId",productTypeId)
};
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, par))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductOrderId">外键ID</param>
/// <returns></returns>
public List<ProductItOrderDetailsInfo> SelectListProductItOrderDetailsProductOrderId(int productOrderId)
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductOrderId=");
strSql.Append("@ProductOrderId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",productOrderId)
};
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(), CommandType.Text, par))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
/// <summary>
/// 查询外键ID的所有记录
/// </summary>
/// <param name="ProductOrderId">外键ID</param>
/// <returns></returns>
public DataTable SelectProductItOrderDetailsProductOrderId(int productOrderId)
{
DataTable dt = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ProductItOrderDetails");
strSql.Append(" where ProductOrderId=");
strSql.Append("@ProductOrderId ");
SQLiteParameter[] par = new SQLiteParameter[]{
new SQLiteParameter("@ProductOrderId",productOrderId)
};
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, par))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public List<ProductItOrderDetailsInfo> SelectProductItOrderDetailsAll()
{
List<ProductItOrderDetailsInfo> list = new List<ProductItOrderDetailsInfo>();
ProductItOrderDetailsInfo productItOrderDetails = null;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from productItOrderDetails");
using (SQLiteDataReader reader = SQLiteHelper.GetReader(strSql.ToString(),CommandType.Text, null))
{
while (reader.Read())
{
productItOrderDetails = new ProductItOrderDetailsInfo();
productItOrderDetails.ProductDetailsId =(!object.Equals(reader["ProductDetailsId"],null))? int.Parse(reader["ProductDetailsId"].ToString()):0;
productItOrderDetails.ProductOrderId =(!object.Equals(reader["ProductOrderId"],null))? int.Parse(reader["ProductOrderId"].ToString()):0;
productItOrderDetails.ProductTypeId =(!object.Equals(reader["ProductTypeId"],null))? int.Parse(reader["ProductTypeId"].ToString()):0;
productItOrderDetails.ProductModleId =(!object.Equals(reader["ProductModleId"],null))? int.Parse(reader["ProductModleId"].ToString()):0;
productItOrderDetails.ProductUnitID =(!object.Equals(reader["ProductUnitID"],null))? int.Parse(reader["ProductUnitID"].ToString()):0;
productItOrderDetails.ProductQty = (Decimal) reader["ProductQty"];
productItOrderDetails.ProductPriceID =(!object.Equals(reader["ProductPriceID"],null))? int.Parse(reader["ProductPriceID"].ToString()):0;
productItOrderDetails.ProductMeters = (Decimal) reader["ProductMeters"];
productItOrderDetails.ProductDescription =(!object.Equals(reader["ProductDescription"],null))? (string) reader["ProductDescription"]:"";
list.Add(productItOrderDetails);
}
}
}
catch (SQLiteException ex)
{
throw ex;
}
return list;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public DataTable SelectProductItOrderDetailsDataTableAll()
{
DataTable dt = new DataTable();
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from productItOrderDetails");
using (DataTable reader = SQLiteHelper.GetTable(strSql.ToString(), CommandType.Text, null))
{
dt = reader;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
///<summary>
///SQL脚本 删除多条记录
///</summary>
///<param name="productDetailsIdInfo"></param>
///<returns></returns>
public bool DeleteProductItOrderDetailsId(string productDetailsId)
{
bool ret = false;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from productItOrderDetails");
strSql.Append(" where ProductDetailsId in (");
strSql.Append("@ProductDetailsId )");
//SQLiteParameter[] par = new SQLiteParameter[]{};
SQLiteParameter par =new SQLiteParameter("@ProductDetailsId",productDetailsId);
int temp = 0 ;
temp = SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text, par);
if(temp!=0)
{
ret = true;
}
}
catch (SQLiteException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// SQL script查询分页
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="currentPage">当前页码</param>
/// <param name="strWhere">查询的条件</param>
/// <param name="filedOrder">排序字段</param>
/// <param name="recordCount">每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)
{
int topNum = pageSize * currentPage;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM ProductItOrderDetails");
if (strWhere.Trim() !="")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(SQLiteHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return SQLiteHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder));
}
/// <summary>
/// SQL语句分页
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="filedOrder">排序字段</param>
/// <param name="recordCount">返回每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int pageIndex, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM ProductItOrderDetails");
recordCount = Convert.ToInt32(SQLiteHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return SQLiteHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));分页弄错了。这是sql sever,Access的分页
}
/// <summary>
/// SQL语句分页
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="recordCount">返回每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int pageIndex, out int recordCount)
{
string filedOrder = " order by ProductDetailsId desc";
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM ProductItOrderDetails");
recordCount = Convert.ToInt32(SQLiteHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return SQLiteHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
}
/// <summary>
///SQL脚本 模糊查询
/// </summary>
/// <param name="filedlist">显示字段列表</param>
/// <param name="strkey">输入的关键字</param>
/// <returns></returns>
public DataTable GetDataTableProductItOrderDetailsFuzzySearch(string filedlist, string strkey)
{
DataTable dt = new DataTable();
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (string.IsNullOrEmpty(filedlist))
{
strSql.Append(" * ");
}
else
{
strSql.Append(" from ProductItOrderDetails");
}
if (!string.IsNullOrEmpty(strkey))
{
strSql.Append(" where " + StringConvert.getStrWhere("ProductDescription",strkey));
}
dt = SQLiteHelper.Query(strSql.ToString()).Tables[0];
}
catch (SQLiteException ex)
{
throw ex;
}
return dt;
}
/// <summary>
///SQL脚本 是否存在该记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Exists(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from ProductItOrderDetails");
strSql.Append(" where ProductDetailsId=@ProductDetailsId ");
SQLiteParameter[] parameters = {
new SQLiteParameter("@ProductDetailsId", DbType.Int32,4)};
parameters[0].Value = id;
return SQLiteHelper.Exists(strSql.ToString(), parameters);
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool ExistsId(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from ProductItOrderDetails");
strSql.Append(" where ProductDetailsId=@ProductDetailsId ");
SQLiteParameter[] parameters = {
new SQLiteParameter("@ProductDetailsId", DbType.Int32,10)};
parameters[0].Value = id;
return SQLiteHelper.Exists(strSql.ToString(), parameters);
}
/// <summary>
/// 检查单位名是否存在
/// </summary>
/// <param name="Name"></param>
/// <returns></returns>
public bool ExistsName(string columnName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from ProductItOrderDetails");
strSql.Append(" where ColumnName=@ColumnName ");
SQLiteParameter[] parameters = {
new SQLiteParameter("@ColumnName", DbType.String,100)};
parameters[0].Value = columnName;
return SQLiteHelper.Exists(strSql.ToString(), parameters);
}
/// <summary>
/// 找到名称ID
/// </summary>
/// <param name="Name"></param>
/// <returns></returns>
public int GetName(string name)
{
int name = 0;
StringBuilder strSql = new StringBuilder();
strSql.Append("select ProductDetailsId from ProductItOrderDetails");
strSql.Append(" where ColumnName=@ColumnName ");
SQLiteParameter[] parameters = {
new SQLiteParameter("@ColumnName", DbType.String,100)
};
parameters[0].Value = name;
name = Convert.ToInt32(SQLiteHelper.GetSingle(strSql.ToString(), parameters));
return name;
}
/// <summary>
///SQL脚本 返回视图数据总数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int GetCountView(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) as H ");
strSql.Append(" from ProductItOrderDetailsView");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return Convert.ToInt32(SQLiteHelper.GetSingle(strSql.ToString()));
}
/// <summary>
///SQL脚本 返回数据总数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int GetCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) as H ");
strSql.Append(" from ProductItOrderDetails");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return Convert.ToInt32(SQLiteHelper.GetSingle(strSql.ToString()));
}
/// <summary>
///SQL脚本 修改一列数据
/// </summary>
/// <param name="id"></param>
/// <param name="strValue"></param>
/// <returns></returns>
public int UpdateField(int Id, string fieldValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update ProductItOrderDetails set @fieldValue");
strSql.Append(" where ProductDetailsId= @Id");
SQLiteParameter[] parameters = {
new SQLiteParameter("@fieldValue", DbType.String),
new SQLiteParameter("@ProductDetailsId", DbType.Int32,8),
};
parameters[0].Value = Id;
parameters[1].Value = fieldValue;
return SQLiteHelper.ExecuteSql(strSql.ToString(), CommandType.Text,parameters);
}
/// <summary>
///SQL脚本 返回指字字段的字串
/// </summary>
/// <param name="id"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public string GetTitle(int Id, string fieldName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 " + fieldName + " from ProductItOrderDetails");
strSql.Append(" where ProductDetailsId=" + Id);
string title = Convert.ToString(SQLiteHelper.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(title))
{
return string.Empty;
}
return title;
}
}
}
用于UI层要操作：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Drawing;
using System.Text.RegularExpressions;
using Geovin.Du.Model;
using Geovin.Du.Commn;
using Geovin.Du.BasicUI;
using Geovin.Du.BLL;
namespace Geovin.Du.CommnUI
{
/// <summary>
/// ProductTypeList表產品名稱表Windows窗体数据处理
///生成時間2018/9/14 15:11:06
///塗聚文(Geovin Du)
///</summary>
public class ProductTypeListControl
{
ProductTypeListBLL dal = new ProductTypeListBLL();
///<summary>
/// 追加记录
///</summary>
///<param name="ProductTypeList">输入参数:ProductTypeListInfo</param>
///<returns>返回添加的记录条数</returns>
public int Add(ProductTypeListInfo productTypeList)
{
return dal.InsertProductTypeList(productTypeList);
}
///<summary>
/// 追加记录返回值
///</summary>
///<param name="ProductTypeList">输入参数:ProductTypeListInfo</param>
///<param name="ProductTypeID">返回参数:ProductTypeID</param>
///<returns>返回是否添加的个数</returns>
public int AddOutput(ProductTypeListInfo productTypeList,out int productTypeID)
{
return dal.InsertProductTypeListOutput(productTypeList,out productTypeID);
}
///<summary>
///修改记录
///</summary>
///<param name="ProductTypeList">输入参数:ProductTypeListInfo</param>
///<returns>返回修改的多少记录数</returns>
public int Edit(ProductTypeListInfo productTypeList)
{
return dal.UpdateProductTypeList(productTypeList);
}
///<summary>
/// 删除记录
///</summary>
///<param name="productTypeID">输入参数:ProductTypeID</param>
///<returns>返回删除记录条数</returns>
public bool Del(int productTypeID)
{
bool isdel = dal.DeleteProductTypeList(productTypeID);
if (isdel)
{
//删除关联的外键记录
ProductModelBLL productModelbll = new ProductModelBLL();
productModelbll.DeleteModelProductTypeID(int.Parse(productTypeID));
//删除关联的外键记录
UnitPriceBLL unitPricebll = new UnitPriceBLL();
unitPricebll.DeleteUnitProductTypeID(int.Parse(productTypeID));
//删除关联的外键记录
ProductItOrderDetailsBLL productItOrderDetailsbll = new ProductItOrderDetailsBLL();
productItOrderDetailsbll.DeleteProductTypeId(int.Parse(productTypeID));
//删除关联的外键记录
ProductOrderDetailsBLL productOrderDetailsbll = new ProductOrderDetailsBLL();
productOrderDetailsbll.DeleteProductTypeId(int.Parse(productTypeID));
}
return isdel;
}
///<summary>
/// 删除多条记录
///</summary>
///<param name="productTypeID">输入参数:ProductTypeID</param>
///<returns>返回删除多少记录</returns>
public bool DelId(string productTypeID)
{
bool isdel = dal.DeleteProductTypeListId(productTypeID);
if (isdel)
{
//删除关联的外键记录，要考虑
string[] sArray = tableId.Split(new string[] { ",", "，" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string tid in sArray)
{
ProductModelBLL productModelbll = new ProductModelBLL();
productModelbll.DeleteModelProductTypeID(int.Parse(productTypeID));
UnitPriceBLL unitPricebll = new UnitPriceBLL();
unitPricebll.DeleteUnitProductTypeID(int.Parse(productTypeID));
ProductItOrderDetailsBLL productItOrderDetailsbll = new ProductItOrderDetailsBLL();
productItOrderDetailsbll.DeleteProductTypeId(int.Parse(productTypeID));
ProductOrderDetailsBLL productOrderDetailsbll = new ProductOrderDetailsBLL();
productOrderDetailsbll.DeleteProductTypeId(int.Parse(productTypeID));
}
}
return isdel;
}
///<summary>
/// 查询记录
///</summary>
///<param name="productTypeID">输入参数:ProductTypeID</param>
///<returns>返回ProductTypeListInfo</returns>
public ProductTypeListInfo Select(int productTypeID)
{
DataTableDescInfo info = dal.SelectProductTypeList(productTypeID);
//根据主键所涉涉及子表的做为外键的查询
ProductModelBLL productModelbll = new ProductModelBLL();
info.ProductModelData = productModelbll.SelectDataProductModelModelProductTypeID(productTypeID);
info.ProductModelList = productModelbll.SelectListProductModelModelProductTypeID(productTypeID);
//根据主键所涉涉及子表的做为外键的查询
UnitPriceBLL unitPricebll = new UnitPriceBLL();
info.UnitPriceData = unitPricebll.SelectDataUnitPriceUnitProductTypeID(productTypeID);
info.UnitPriceList = unitPricebll.SelectListUnitPriceUnitProductTypeID(productTypeID);
//根据主键所涉涉及子表的做为外键的查询
ProductItOrderDetailsBLL productItOrderDetailsbll = new ProductItOrderDetailsBLL();
info.ProductItOrderDetailsData = productItOrderDetailsbll.SelectDataProductItOrderDetailsProductTypeId(productTypeID);
info.ProductItOrderDetailsList = productItOrderDetailsbll.SelectListProductItOrderDetailsProductTypeId(productTypeID);
//根据主键所涉涉及子表的做为外键的查询
ProductOrderDetailsBLL productOrderDetailsbll = new ProductOrderDetailsBLL();
info.ProductOrderDetailsData = productOrderDetailsbll.SelectDataProductOrderDetailsProductTypeId(productTypeID);
info.ProductOrderDetailsList = productOrderDetailsbll.SelectListProductOrderDetailsProductTypeId(productTypeID);
return info;
}
///<summary>
/// 查询所有记录
///</summary>
///<param name="productTypeID">无输入参数</param>
///<returns>返回表所有记录(List)ProductTypeListInfo</returns>
public List<ProductTypeListInfo> SelectAll()
{
return dal.SelectProductTypeListAll();
}
///<summary>
/// 查询所有记录
///</summary>
///<param name="productTypeID">无输入参数</param>
///<returns>返回(DataTable)ProductTypeList表所有记录</returns>
public DataTable SelectDataTableAll()
{
return dal.SelectProductTypeListDataTableAll();
}
/// <summary>
/// SQL script查询分页
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="currentPage">当前页码</param>
/// <param name="strWhere">查询的条件</param>
/// <param name="filedOrder">排序字段</param>
/// <param name="recordCount">每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)
{
return dal.GetPageList(pageSize, currentPage, strWhere, filedOrder, out recordCount);
}
/// <summary>
/// SQL script查询分页无查询条件无排序
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="currentPage">当前页码</param>
/// <param name="recordCount">每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int currentPage, out int recordCount)
{
return dal.GetPageList(pageSize, currentPage, out recordCount);
}
/// <summary>
/// SQL script查询分页无排序
/// </summary>
/// <param name="pageSize">每页页数</param>
/// <param name="currentPage">当前页码</param>
/// <param name="strWhere">查询的条件</param>
/// <param name="recordCount">每页的记录数</param>
/// <returns></returns>
public DataSet GetPageList(int pageSize, int currentPage, string strWhere, out int recordCount)
{
return dal.GetPageList(pageSize, currentPage, strWhere, out recordCount);
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="filedlist">显示字段列表</param>
/// <param name="strkey">输入的关键字</param>
/// <returns></returns>
public DataTable GetDataTableFuzzySearch(string filedlist, string strkey)
{
return dal.GetDataTableProductTypeListFuzzySearch(filedlist, strkey);
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public bool Exists(int Id)
{
return dal.Exists(Id);
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool ExistsId(int id)
{
return dal.ExistsId(id);
}
/// <summary>
/// 检查单位名是否存在
/// </summary>
/// <param name="levelName"></param>
/// <returns></returns>
public bool ExistsName(string columnName)
{
return dal.ExistsName(columnName);
}
/// <summary>
/// 找到名称ID
/// </summary>
/// <param name="unitName"></param>
/// <returns></returns>
public int GetName(string columnName)
{
return dal.GetName(columnName);
}
/// <summary>
/// 返回数据总数
/// </summary>
/// <param name="strWhere">查询条件</param>
/// <returns></returns>
public int GetCount(string where)
{
return dal.GetCount(where);
}
/// <summary>
/// 返回视图数据总数
/// </summary>
/// <param name="strWhere">查询条件</param>
/// <returns></returns>
public int GetCountView(string where)
{
return dal.GetCountView(where);
}
/// <summary>
/// 更新一列数据
/// </summary>
/// <param name="Id"></param>
/// <param name="strValue">字段名=值</param>
/// <returns></returns>
public int UpdateField(int Id, string fieldValue)
{
return dal.UpdateField(Id, fieldValue);
}
/// <summary>
/// 返回指字字段的字串
/// </summary>
/// <param name="Id"></param>
/// <param name="fieldName">字段名</param>
/// <returns></returns>
public string GetTitle(int Id, string fieldName)
{
return dal.GetTitle(Id, fieldName);
}
/// <summary>
/// Combox绑定
/// </summary>
/// <param name="comb"></param>
public void SetCombox(ComboBox comb)
{
SetComBoxDataBinding.setComboList(comb, "id", "typename", dal.SelectProductTypeListDataTableAll());
}
/// <summary>
/// DataGridView绑定
/// </summary>
/// <param name="dataGridView1"></param>
/// <param name="dt"></param>
/// <param name="bindingSource1"></param>
/// <param name="bindingNavigator1"></param>
private void SetDataGridView(DataGridView dataGridView1, DataTable dt, BindingSource bindingSource1, BindingNavigator bindingNavigator1)
{
SetDataGridViewBindingSourceNavigatorAll set = new SetDataGridViewBindingSourceNavigatorAll();
dataGridView1.DataSource = null;
set.SetDataGridViewBindingSourceNavigatorBinds(dataGridView1, dt, bindingSource1, bindingNavigator1);
}
/// <summary>
///
/// </summary>
/// <param name="dataGridView1"></param>
/// <param name="dt"></param>
private void SetDataGridView(DataGridView dataGridView1, DataTable dt)
{
dataGridView1.DataSource = null;
dataGridView1.DataSource = dt;
}
/// <summary>
/// sql 查询分页无搜条件和排序
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl分页控件</param>
/// <param name="Index"></param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, int Index)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = dal.GetPageList(Index, pagerControl1.PageSize, out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
}
/// <summary>
/// sql 查询分页 无排序
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl分页控件</param>
/// <param name="Index">当前页</param>
/// <param name="strWhere">查询关键字</param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, int Index, string strWhere)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
}
/// <summary>
/// sql 查询分页
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl分页控件</param>
/// <param name="Index">当前页</param>
/// <param name="strWhere">查询关键字</param>
/// <param name="filedOrder">排序字段名</param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, int Index, string strWhere, string filedOrder)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, filedOrder, out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
}
/// <summary>
/// sql 查询分页 无查询条件无排序
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl自定义分页控件</param>
/// <param name="bindingSource1">BindingSource控件</param>
/// <param name="bindingNavigator1">BindingNavigator控件</param>
/// <param name="Index">当前页</param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, BindingSource bindingSource1, BindingNavigator bindingNavigator1, int Index)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = GetPageList(Index, pagerControl1.PageSize, out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
bindingSource1.DataSource = GetPageList(Index, pagerControl1.PageSize, out Count).Tables[0];
bindingNavigator1.BindingSource = bindingSource1;
dataGridView1.DataSource = bindingSource1;
}
/// <summary>
/// sql 查询分页 無排序
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl自定义分页控件</param>
/// <param name="bindingSource1">BindingSource控件</param>
/// <param name="bindingNavigator1">BindingNavigator控件</param>
/// <param name="Index">当前页</param>
/// <param name="strWhere">查询条件关键字</param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, BindingSource bindingSource1, BindingNavigator bindingNavigator1, int Index, string strWhere)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
bindingSource1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, out Count);
bindingNavigator1.BindingSource = bindingSource1;
dataGridView1.DataSource = bindingSource1;
}
/// <summary>
/// sql 查询分页 無排序
/// </summary>
/// <param name="dataGridView1">DataGridView控件</param>
/// <param name="pagerControl1">PagerControl自定义分页控件</param>
/// <param name="bindingSource1">BindingSource控件</param>
/// <param name="bindingNavigator1">BindingNavigator控件</param>
/// <param name="Index">当前页</param>
/// <param name="strWhere">查询条件关键字</param>
/// <param name="filedOrder">排序字段</param>
public void setBindDataSqlPage(DataGridView dataGridView1, PagerControl pagerControl1, BindingSource bindingSource1, BindingNavigator bindingNavigator1, int Index, string strWhere, string filedOrder)
{
int Count = 0;
pagerControl1.PageIndex = Index;
//pagerControl1.PageSize = 20;
dataGridView1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, filedOrder,out Count).Tables[0];
//获取并设置总记录数
pagerControl1.RecordCount = Count;
pagerControl1.DrawControl(Count);
bindingSource1.DataSource = GetPageList(Index, pagerControl1.PageSize, strWhere, out Count);
bindingNavigator1.BindingSource = bindingSource1;
dataGridView1.DataSource = bindingSource1;
}
/// <summary>
/// DataGridView顯示語言類型
/// </summary>
/// <param name="dv"></param>
/// <param name="lname"></param>
public void setGridViewName(DataGridView dv, ChangeLanguage.LanguageName lname)
{
DataGridViewCellStyle date5 = new DataGridViewCellStyle();
date5.Format = "yyyy-MM-dd HH:mm:ss";
DataGridViewCellStyle date4 = new DataGridViewCellStyle();
date4.Format = "yyyy-MM-dd";
switch (lname)
{
case ChangeLanguage.LanguageName.GBK:
dv.Columns["ProductTypeID"].HeaderText = "ID,主键";
dv.Columns["ProductTypeName"].HeaderText = "产品名称";
dv.Columns["ProductTypePin"].HeaderText = "字首字母";
break;
case ChangeLanguage.LanguageName.GBig:
dv.Columns["ProductTypeID"].HeaderText = "ID,主鍵";
dv.Columns["ProductTypeName"].HeaderText = "產品名稱";
dv.Columns["ProductTypePin"].HeaderText = "字首字母";
break;
case ChangeLanguage.LanguageName.USEN:
dv.Columns["ProductTypeID"].HeaderText = "ProductTypeID";
dv.Columns["ProductTypeName"].HeaderText = "ProductTypeName";
dv.Columns["ProductTypePin"].HeaderText = "ProductTypePin";
break;
default:
dv.Columns["ProductTypeID"].HeaderText = "ID,主键";
dv.Columns["ProductTypeName"].HeaderText = "产品名称";
dv.Columns["ProductTypePin"].HeaderText = "字首字母";
break;
}
}
}
}
/// <summary>
/// MetaDataCollections MetaData集合
/// </summary>
/// <returns></returns>
DataTable getSchema(string connString)
{
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("CollectionName", typeof(string));
dt.Columns.Add("NumberOfRestrictions", typeof(string));
dt.Columns.Add("NumberOfIdentifierParts", typeof(string));
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
//http://www.devart.com/dotconnect/sqlite/docs/MetaData.html
//Catalogs,Columns,DatasourceInformation,DataTypes,ForeignKeyColumns,ForeignKeys,IndexColumns,Indexes,MetaDataCollections,PrimaryKeys,ReservedWords,Restrictions,Tables,UniqueKeys,ViewColumns,Views
DataTable schemaTable = conn.GetSchema("MetaDataCollections");// conn.GetSchema()和conn.GetSchema("MetaDataCollections")效果是一样的
int i = 1;
foreach (DataRow row in schemaTable.Rows)
{
dt.Rows.Add(i,row["CollectionName"], row["NumberOfRestrictions"].ToString(), row["NumberOfIdentifierParts"].ToString());
i++;
}
}
return dt;
}