我在SQL中建了一个存储过程,想在C#中调用此过程并填充到dataGridView1控件中,过程中的三个参数是C#中的三个筛选条件。烦请高手帮我看看,谢谢!create procedure proc_POOrder01
@FBillNo varchar(50),
@FItemName varchar(255),
@FItemID varchar(20)
as
select
c.FEntrySelfP0253 as '工程编号',
c.FEntrySelfP0254 as '工程名称',
a.FBillNo as '订单编号',
b.FNumber as '物料编号',
b.FName as '物料名称',
b.FModel as '物料规格',
b.F_103 as '物料型号'
from POOrder a
left join POOrderEntry c on c.FInterID = a.FInterID
left join t_icitem b on b.FItemID = c.FItemID
where (@FBillNo = '' OR a.FBillNo like '%' + @FBillNo + '%')
and (@FItemID = '' OR b.FNumber like '%' + @FItemID + '%')
and (@FItemName = '' OR b.FName like '%' + @FItemName + '%')
go
@FBillNo varchar(50),
@FItemName varchar(255),
@FItemID varchar(20)
as
select
c.FEntrySelfP0253 as '工程编号',
c.FEntrySelfP0254 as '工程名称',
a.FBillNo as '订单编号',
b.FNumber as '物料编号',
b.FName as '物料名称',
b.FModel as '物料规格',
b.F_103 as '物料型号'
from POOrder a
left join POOrderEntry c on c.FInterID = a.FInterID
left join t_icitem b on b.FItemID = c.FItemID
where (@FBillNo = '' OR a.FBillNo like '%' + @FBillNo + '%')
and (@FItemID = '' OR b.FNumber like '%' + @FItemID + '%')
and (@FItemName = '' OR b.FName like '%' + @FItemName + '%')
go
在Gridview中绑定数据源的时候,选择你的存储过程,会让你选择参数源,就选择Control,分别选择三个Dropdownlist就哦了如果不用Sqldatesource,自己写数据源,那也是一样的,把要的参数读出来直接给存储过程就行了string procname = "proc_POOrder01";
SqlParameter[] prams ={
new SqlParameter("@FBillNo",SqlDbType.VarChar,50),
new SqlParameter("@FItemName",SqlDbType.VarChar,50),
new SqlParameter("@FItemID",SqlDbType.VarChar,50)
};
prams[0].Value = Dropdownlist1.selectvalue;
prams[1].Value = Dropdownlist2.selectvalue;
prams[2].Value = Dropdownlist3.selectvalue;
...
conn.ConnectionString = "server = erp05;uid=sa;pwd=;database=AIS20091202193748";
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_POOrder01";
SqlParameter[] spa = {
new SqlParameter ("@billno",SqlDbType.VarChar,50),
new SqlParameter ("@itemname",SqlDbType.VarChar,255 ),
new SqlParameter ("@itemid",SqlDbType.VarChar,20 )
};
spa[0].Value = textBox1 .Text ;
spa[1].Value = textBox5.Text ;
spa[2].Value = textBox6 .Text ;
cmd.ExecuteNonQuery();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
sda.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server = erp05;uid=sa;pwd=;database=AIS20091202193748";
//conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_POOrder01";
SqlParameter[] spa = {
new SqlParameter ("@billno",SqlDbType.VarChar,50),
new SqlParameter ("@itemname",SqlDbType.VarChar,255 ),
new SqlParameter ("@itemid",SqlDbType.VarChar,20 )
};
spa[0].Value = textBox1.Text;
spa[1].Value = textBox5.Text;
spa[2].Value = textBox6.Text;
//cmd.ExecuteNonQuery();
cmd.Parameters.AddRange(spa);//把参数添加到cmd中 SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;//把cmd赋值给sda的内置SelectCommand对象
DataSet ds = new DataSet();
sda.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];