一、C#组合查询的便捷方法。
object objImportDateFrom = DBNull.Value;
if (importDateFrom != "")
{
objImportDateFrom = importDateFrom;
}
new SqlParameter(" @ProductSaleTimeFrom",ObjproductSaleTimeFrom),
new SqlParameter(" @ProductSaleTimeTo",productSaleTimeTo),
...................................
//sql存储过程
CREATE PROCEDURE [ dbo ]. [ UP_ProductFocus_SalesStatistics ]
@Code nvarchar( 10) = '',
@ProductCreateTimeFrom DATETIME = null, -- 导入系统时间
@ProductCreateTimeTo DateTime = null
as
begin
AND ( @SearchCodeList = '' OR cn.Code IN ( Select * From dbo.fun_split( @SearchCodeList, ' , ')))
AND ( @ProductCreateTimeFrom is null OR cn.CreateTime between @ProductCreateTimeFrom and @ProductCreateTimeTo)
end
（ 2）、时间范围的处理方法，这样可以防止不同电脑上的时间设置格式的不同。
productSaleTimeFrom = this.dtpStartSellDateFrom.Value.Date.ToString("yyyy -MM -dd") + " 0: 00: 00"; // 2012 - 09 - 14 0: 00: 00
productSaleTimeTo = this.dtpStartSellDateTo.Value.Date.AddDays( 1).AddMilliseconds( - 100).ToString("yyyy -MM -dd") + " 23: 59: 59"; // 2012 - 09 - 14 23: 59: 59
（ 3）、sql中的事务写法
1、方法一
begin tran(开启事务)
begin try
//语句块
//……….
//……….
//……….
commit tran(提交事务)
-- return 1
end try
begin catch
rollback tran（回滚事务）
-- return 0
end catch
2、写法二
begin transaction
declare @error int
set @error = 0
insert into borrows(rid,bid,borrowdate) values
( 5, 10, 2008 - 6 - 11)
set @error = @error + @@error
update books set bstate = 1
where bid = 10
set @error = @error + @@error
if @error <> 0
rollback transaction
else
commit transaction
源文档 <http: //blog.sina.com.cn /s /blog_75a83e8b0100u7nx.html >
CREATE PROCEDURE UP_EbestPostOrderHeader_UpdateExportConfim
@EbestPostOrderHeaderId INT,
@User NVARCHAR( 50)
AS
BEGIN
object objImportDateFrom = DBNull.Value;
if (importDateFrom != "")
{
objImportDateFrom = importDateFrom;
}
new SqlParameter(" @ProductSaleTimeFrom",ObjproductSaleTimeFrom),
new SqlParameter(" @ProductSaleTimeTo",productSaleTimeTo),
...................................
//sql存储过程
CREATE PROCEDURE [ dbo ]. [ UP_ProductFocus_SalesStatistics ]
@Code nvarchar( 10) = '',
@ProductCreateTimeFrom DATETIME = null, -- 导入系统时间
@ProductCreateTimeTo DateTime = null
as
begin
AND ( @SearchCodeList = '' OR cn.Code IN ( Select * From dbo.fun_split( @SearchCodeList, ' , ')))
AND ( @ProductCreateTimeFrom is null OR cn.CreateTime between @ProductCreateTimeFrom and @ProductCreateTimeTo)
end
（ 2）、时间范围的处理方法，这样可以防止不同电脑上的时间设置格式的不同。
productSaleTimeFrom = this.dtpStartSellDateFrom.Value.Date.ToString("yyyy -MM -dd") + " 0: 00: 00"; // 2012 - 09 - 14 0: 00: 00
productSaleTimeTo = this.dtpStartSellDateTo.Value.Date.AddDays( 1).AddMilliseconds( - 100).ToString("yyyy -MM -dd") + " 23: 59: 59"; // 2012 - 09 - 14 23: 59: 59
（ 3）、sql中的事务写法
1、方法一
begin tran(开启事务)
begin try
//语句块
//……….
//……….
//……….
commit tran(提交事务)
-- return 1
end try
begin catch
rollback tran（回滚事务）
-- return 0
end catch
2、写法二
begin transaction
declare @error int
set @error = 0
insert into borrows(rid,bid,borrowdate) values
( 5, 10, 2008 - 6 - 11)
set @error = @error + @@error
update books set bstate = 1
where bid = 10
set @error = @error + @@error
if @error <> 0
rollback transaction
else
commit transaction
源文档 <http: //blog.sina.com.cn /s /blog_75a83e8b0100u7nx.html >
CREATE PROCEDURE UP_EbestPostOrderHeader_UpdateExportConfim
@EbestPostOrderHeaderId INT,
@User NVARCHAR( 50)
AS
BEGIN
Begin try
Begin Tran
UPDATE dbo.EbestPostOrderHeader SET IsExport = 1, ExportDate = GETDATE(), LastUpdTime = GETDATE(),LastUpdUser = @User
WHERE EbestPostOrderHeaderId = @EbestPostOrderHeaderId
UPDATE dbo.PromotionOrderHeader SET IsExport = 1, ExportTime = GETDATE(), LastUpdTime = GETDATE(),LastUpdUser = @User
WHERE EbestPostOrderHeaderId = @EbestPostOrderHeaderId
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
Begin Tran
UPDATE dbo.EbestPostOrderHeader SET IsExport = 1, ExportDate = GETDATE(), LastUpdTime = GETDATE(),LastUpdUser = @User
WHERE EbestPostOrderHeaderId = @EbestPostOrderHeaderId
UPDATE dbo.PromotionOrderHeader SET IsExport = 1, ExportTime = GETDATE(), LastUpdTime = GETDATE(),LastUpdUser = @User
WHERE EbestPostOrderHeaderId = @EbestPostOrderHeaderId
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
