ADO.NET,你的极限是什么?--ADO.NET之另类绑定-阿里云开发者社区

在.NET应用中，数据库的成分不仅是整个.NET框架重要的部分,也是设计页面和后台的关键.

ADO.NET你的极限是什么?

1. 页面文件有两个TEXTBOX控件用来设置查找范围。下面一个DATAGRID，用来绑定数据库的数据。

2.后台代码片段:

public partial class FinanceReport : PageBase

{

public static String CONN = System.Configuration.ConfigurationManager.AppSettings.Get( "ConnectionString");

protected void Page_Load( object sender, EventArgs e)

{

PageBegin( true, false);

if (IsPostBack)

return;

FromDate.Text = DateTime.Now.Year.ToString() + "-1-1";

ToDate.Text = (DateTime.Now.Year + 1).ToString() + "-1-1";

PageFill();

}

protected void QueryButton_Click( object sender, EventArgs e)

{

if (DateUtil.IsDateTime(FromDate.Text) == false || DateUtil.IsDateTime(FromDate.Text) == false)

{

Session[ "CM_report_FromDate"] = "";

Session[ "CM_report_ToDate"] = "";

}

else

{

Session[ "CM_report_FromDate"] = FromDate.Text;

Session[ "CM_report_ToDate"] = ToDate.Text;

}

BuildTable();

}

protected void PageFill()

{

if (Session[ "CM_Year"] != null)

{

FromDate.Text = Session[ "CM_Year"] + "-1-1";

ToDate.Text = int.Parse(Session[ "CM_Year"].ToString()) + 1 + "-1-1";

}

if (Session[ "CM_report_FromDate"] != null)

FromDate.Text = Session[ "CM_report_FromDate"].ToString();

if (Session[ "CM_report_ToDate"] != null)

ToDate.Text = Session[ "CM_report_ToDate"].ToString();

BuildTable();

}

protected void BuildTable()

{

SqlConnection conn = new SqlConnection();

conn.ConnectionString = CONN;

SqlCommand command = new SqlCommand();

command.CommandText = "CM_Test";

command.CommandType = CommandType.StoredProcedure;

command.Connection = conn;

SqlParameter param = new SqlParameter( "@FromDate", SqlDbType.DateTime);

if (DateUtil.IsDateTime(FromDate.Text) == false)

{

param.Value = DateTime.Parse(DateTime.Now.Year.ToString() + "-1-1");

}

else

{

param.Value = DateTime.Parse(FromDate.Text);

}

command.Parameters.Add(param);

param = new SqlParameter( "@ToDate", SqlDbType.DateTime);

if (DateUtil.IsDateTime(ToDate.Text) == false)

{

param.Value = DateTime.Parse((DateTime.Now.Year + 1).ToString() + "-1-1");

}

else

{

param.Value = DateTime.Parse(ToDate.Text);

}

command.Parameters.Add(param);

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = command;

da.Fill(ds);

DG.DataSource = ds;

DG.DataBind();

}

说明：后台代码中设计时间范围的小技巧很好用，借助JS，点击TEXTBOX控件的时候就会激发封装好的日历，然后选择日期，就可以自动完成填充了。下面就是绑定了。调用了“CM_Test”

3.数据库存储过程，精彩的来了：

CREATE PROCEDURE CM_Test

    @FromDate datetime,
    @ToDate datetime

AS
     declare @title varchar(50)
     declare @ic float
     declare @asb float
     declare @sales float

     declare @tmpSales table(CID varchar(50),Company varchar(50), Money float,TPSOMoney float,TPSIMoney float,
    TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
    OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
    ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)
     declare @tmpIC table(CID varchar(50),Company varchar(50), Money float,TPSOMoney float,TPSIMoney float,
    TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
    OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
    ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)
     declare @tmpASB table(CID varchar(50),Company varchar(50), Money float,TPSOMoney float,TPSIMoney float,
    TPHOMoney float,TPHIMoney float,SPMoney float,SPIMoney float,CDMoney float,IMoney float,
    OMoney float,GSMoney float,Dep varchar(50),InvoiceMoney float,NotInvoiceMoney float,
    ReceiveMoney float,NotReceiveMoney float,InvoiceNotReMoney float,RealReceiveMoney float)

     declare @tmp    table(item varchar(50),ic float,asb float,sales float)

     insert @tmpSales select CID,Company, Money,TPSOMoney,TPSIMoney,
    TPHOMoney,TPHIMoney,SPMoney,SPIMoney,CDMoney,IMoney,
    OMoney,GSMoney,Dep,InvoiceMoney,NotInvoiceMoney,
    ReceiveMoney,NotReceiveMoney,InvoiceNotReMoney,RealReceiveMoney
     from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate
     insert @tmpIC select * from @tmpSales where Company not like '%公司名字%'
     insert @tmpASB select * from @tmpSales where Company like '%公司名字%'

     select @ic= Count(CID) from @tmpIC select @asb= Count(CID) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '合同数:',@ic,@asb,@sales)

     select @ic= Sum( Money) from @tmpIC select @asb= Sum( Money) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '合同总额:',@ic,@asb,@sales)
/*
    select @ic=Sum(TPSIMoney)+Sum(TPHIMoney)+Sum(SPIMoney) from @tmpIC select @asb=Sum(TPSIMoney)+Sum(TPHIMoney)+Sum(SPIMoney) from @tmpASB select @sales=@ic+@asb
    insert @tmp values('采购总额:',@ic,@asb,@sales)
*/
     select @ic= sum( Money) from CM_SalesTProduct where CID in ( select ID from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate and Company not like '%公司名字%')
     select @asb= sum( Money) from CM_SalesTProduct where CID in ( select ID from CM_Sales where DelFlag=0 and SignDate >= @FromDate and SignDate < @ToDate and Company like '%公司名字%')
     select @sales=@ic+@asb
     insert @tmp values( '采购总额:',@ic,@asb,@sales)

insert @tmp values( '————————————————', null, null, null)

     select @ic= Sum(SPMoney)+ Sum(GSMoney) from @tmpIC select @asb= Sum(SPMoney)+ Sum(GSMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '（自有＋服务）合同额:',@ic,@asb,@sales)

     select @ic= Sum(TPSOMoney)+ Sum(TPHOMoney) from @tmpIC select @asb= Sum(TPSOMoney)+ Sum(TPHOMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '第三方软硬件合同额:',@ic,@asb,@sales)

insert @tmp values( '————————————————', null, null, null)

     select @ic= Sum(TPHOMoney) from @tmpIC select @asb= Sum(TPHOMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '第三方硬件合同额:',@ic,@asb,@sales)

     select @ic= Sum(TPHIMoney) from @tmpIC select @asb= Sum(TPHIMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '第三方硬件采购额:',@ic,@asb,@sales)

     select @ic= Sum(TPSOMoney) from @tmpIC select @asb= Sum(TPSOMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '第三方软件合同额:',@ic,@asb,@sales)

     select @ic= Sum(TPSIMoney) from @tmpIC select @asb= Sum(TPSIMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '第三方软件采购额:',@ic,@asb,@sales)

     select @ic= Sum(SPMoney) from @tmpIC select @asb= Sum(SPMoney) from @tmpASB select @sales=@ic+@asb

     insert @tmp values( '自有产品合同额:',@ic,@asb,@sales)

     select @ic= Sum(SPIMoney) from @tmpIC select @asb= Sum(SPIMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '自有产品采购额:',@ic,@asb,@sales)

     select @ic= Sum(CDMoney)+ Sum(IMoney)+ Sum(GSMoney)+ Sum(OMoney) from @tmpIC select @asb= Sum(CDMoney)+ Sum(IMoney)+ Sum(GSMoney)+ Sum(OMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '服务及其他合同额:',@ic,@asb,@sales)

insert @tmp values( '————————————————', null, null, null)

     select @ic= Sum(InvoiceMoney) from @tmpIC select @asb= Sum(InvoiceMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '已开票总额:',@ic,@asb,@sales)

     select @ic= Sum(ReceiveMoney) from @tmpIC select @asb= Sum(ReceiveMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '已回款总额:',@ic,@asb,@sales)

     select @ic= Sum(NotReceiveMoney) from @tmpIC select @asb= Sum(NotReceiveMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '未回款总额:',@ic,@asb,@sales)

     select @ic= Sum(InvoiceNotReMoney) from @tmpIC select @asb= Sum(InvoiceNotReMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '已开票未回款总额:',@ic,@asb,@sales)

     select @ic= Sum(RealReceiveMoney) from @tmpIC select @asb= Sum(RealReceiveMoney) from @tmpASB select @sales=@ic+@asb
     insert @tmp values( '实际应收款总额 :',@ic,@asb,@sales)

insert @tmp values( '————————————————', null, null, null)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=57 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=57 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=57
     insert @tmp values( 'BU1（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=43 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=43 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=43
     insert @tmp values( 'BU2（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=60 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=60 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=60
     insert @tmp values( 'BU3（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=88 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=88 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=88
     insert @tmp values( 'BU4（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=50 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=50 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=50
     insert @tmp values( 'BU5（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=58 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=58 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=58
     insert @tmp values( 'BU6（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=78 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=78 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=78
     insert @tmp values( 'BU7（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=83 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=83 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=83
     insert @tmp values( 'BU8（自有＋服务）合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=70 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=70 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=70
     insert @tmp values( 'SRS服务合同额 :',@ic,@asb,@sales)

     select @ic= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpIC where dep=66 select @asb= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpASB where dep=66 select @sales= Sum(SPMoney)+ Sum(GSMoney)+ Sum(IMoney) from @tmpSales where dep=66
     insert @tmp values( 'SIS服务合同额 :',@ic,@asb,@sales)

     select * from @tmp
GO

说明：。。。这个存储过程直接搞定这个页面，数据库也可以这样完成，我也是第一次接触这样的存储过程。里面带"Money"的变量都是数据库存储金额的类型，这个页面的功能是统计功能。红字的就直接显示在表里面了--即“统计项”；ic--即集成合同；asb--即ASB合同；Sales--即销售合同。

本文转自L.net 51CTO博客，原文链接：http://blog.51cto.com/mixangel/56258 ，如需转载请自行联系原作者

ADO.NET,你的极限是什么?--ADO.NET之另类绑定

热门文章

最新文章

相关电子书