C # complete example of calling an SQL stored procedure

Code in the SQL stored procedure:

The stored procedure queries two tables.

Set ansi_nulls on
Set quoted_identifier on
Go
Alter procedure [DBO]. [pro_c #]
@ Intyear varchar (4 ),
@ Intseason varchar (1)
As
Begin
Declare @ strsqlc varchar (2000)
Declare @ strsqls varchar (2000)
Declare @ strsql varchar (2000)
Declare @ strsql1 varchar (200)
Declare @ strstartdatetime varchar (30)
Declare @ strenddatetime varchar (30)
Declare @ tblname varchar (30)
Set @ tblname = 'tbl' + @ intyear
If (@ intseason = '1 ')
Begin
Set @ strstartdatetime = @ intyear + '-1-1'
Set @ strenddatetime = @ intyear + '-3-31'
End
If (@ intseason = '2 ')
Begin
Set @ strstartdatetime = @ intyear + '-4-1'
Set @ strenddatetime = @ intyear + '-6-30'
End
If (@ intseason = '3 ')
Begin
Set @ strstartdatetime = @ intyear + '-7-1'
Set @ strenddatetime = @ intyear + '-10-31'
End
If (@ intseason = '4 ')
Begin
Set @ strstartdatetime = @ intyear + '-10-1'
Set @ strenddatetime = @ intyear + '-12-31'
End
Set @ strsql = 'sum (case when casetype = 1 then 1 else 0 end) as bjtotal,
Sum (case when casetype = 1 and bigclass = 1 then 1 else 0 end) as bjpublic,
Sum (case when casetype = 1 and bigclass = 2 then 1 else 0 end) as bjtraffic,
Sum (case when casetype = 1 and bigclass = 3 then 1 else 0 end) as bjsurround,
Sum (case when casetype = 1 and bigclass = 4 then 1 else 0 end) as bjgreen,
Sum (case when casetype = 1 and bigclass = 5 then 1 else 0 end) as bjhouse,

Sum (case when casetype = 2 then 1 else 0 end) as sjtotal,
Sum (case when casetype = 2 and bigclass = 1 then 1 else 0 end) as sjsurround,
Sum (case when casetype = 2 and bigclass = 2 then 1 else 0 end) as sjadvertise,
Sum (case when casetype = 2 and bigclass = 3 then 1 else 0 end) as sjmanage,
Sum (case when casetype = 2 and bigclass = 4 then 1 else 0 end) as sjurgent,
Sum (case when casetype = 2 and bigclass = 5 then 1 else 0 end) as sjstreet
Into'

Set @ strsql1 = 'set val = (bjpublic * 0.24 + bjtraffic * 0.21 + bjsurround * 0.03 + bjgreen * 0.51 + bjhouse * 0.01) * 0.05 + (sjsurround * 0.33 + sjadvertise * 0.22 + sjmanage * 0.03 + sjurgent * 0.01 + sjstreet * 0.41) * 0.95)/area ;'

Set @ strsqlc = 'select communitycode, communityname, stname, '+ @ strsql +' # tblrc
From '+ @ tblname +'
Where uploadtime between ''' + @ strstartdatetime + ''' and ''' + @ strenddatetime + '''
Group by communitycode, communityname, stname order by stname ASC;
Alter table # tblrc add area float, Val float;
Update # tblrc set # tblrc. Area = community. area from community where # tblrc. communityname = community. sqname;
Update # tblrc '+ @ strsql1;
Exec (@ strsqlc + 'select * from # tblrc ')

Set @ strsqls = 'select stcode, stname, '+ @ strsql +' # tblrs
From '+ @ tblname +'
Where uploadtime between ''' + @ strstartdatetime + ''' and ''' + @ strenddatetime + '''
Group by stcode, stname order by stname ASC;
Alter table # tblrs add area float, Val float;
Update # tblrs set # tblrs. Area = Street. area from street where # tblrs. stname = Street. jdname;
Update # tblrs '+ @ strsql1;
Exec (@ strsqls + 'select * from # tblrs ')
End

C # code:

Public partial class form1: Form
{
Public form1 ()
{
Initializecomponent ();
}
Sqlconnection conn;
Sqlcommand comm;
Sqldataadapter SDA;
Dataset mydataset;
Private void form1_load (Object sender, eventargs E)
{
Conn = new sqlconnection ("Server = 10.19.1.55; uid = sa; Pwd = 123; database = 08_09cgywdb; connection timeout = 2 ");
Conn. open ();
// Call the Stored Procedure
Comm = new sqlcommand ();
Comm. Connection = conn;
Comm. commandtext = "pro_c #"; // name of the stored procedure
Comm. commandtype = commandtype. storedprocedure;
Idataparameter [] parameters =
{
New sqlparameter ("@ intyear", sqldbtype. varchar, 4), // name of the parameter in the Stored Procedure
New sqlparameter ("@ intseason", sqldbtype. varchar, 1) // name of the parameter in the Stored Procedure
};
Parameters [0]. value = "2009 ";
Parameters [1]. value = "2 ";
Comm. Parameters. Add (parameters [0]);
Comm. Parameters. Add (parameters [1]);
SDA = new sqldataadapter ();
SDA. selectcommand = comm;
Mydataset = new dataset ();
SDA. Fill (mydataset );
Conn. Close ();
Datagridview1.datasource = mydataset. Tables [0];
Datagridview2.datasource = mydataset. Tables [1];
}
}