一、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
