C#調用SQL預存程序完整例子

SQL預存程序中的代碼：

預存程序查詢出兩個表

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#代碼：

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();
            //調用預存程序
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "pro_C#";//預存程序的名字
            comm.CommandType = CommandType.StoredProcedure;
            IDataParameter[] parameters = 
                { 
                    new SqlParameter("@intYear", SqlDbType.VarChar,4) , //預存程序中參數的名字
                    new SqlParameter("@intSeason", SqlDbType.VarChar,1)   //預存程序中參數的名字
                };
            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];
        }
    }