存储过程是 CREATE Procedure Up_Bap_GetAddressBook ( @EmployeeID varchar(20)='a', @employeesName varchar(10)='b', @employeesSex varchar(2)='c' ) AS if(@EmployeeID != 'a' and @employeesName != 'b' and @employeesSex != 'c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex else if(@EmployeeID = 'a' and @employeesName!='b' and @employeesSex!='c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex if(@EmployeeID!='a' and @employeesName = 'b' and @employeesSex!='c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesSex=@employeesSex if(@EmployeeID!='a' and @employeesName!= 'b' and @employeesSex = 'c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesName='%'+@employeesName+'%' if(@EmployeeID = 'a' and @employeesName!= 'b' and @employeesSex = 'c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesName like '%'+@employeesName+'%' if(@EmployeeID != 'a' and @employeesName = 'b' and @employeesSex = 'c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesID like '%'+@EmployeeID+'%' else if(@EmployeeID = 'a' and @employeesName = 'b' and @employeesSex!= 'c') SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress from EmployeesInfo where employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex 程序中的部分 SqlDataAdapter da = new SqlDataAdapter("Up_Bap_GetAddressBook", conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.VarChar,20).Value = employeeID; da.SelectCommand.Parameters.Add("@employeesName", SqlDbType.VarChar,10).Value = employeeName; da.SelectCommand.Parameters.Add("@employeesSex", SqlDbType.VarChar,2).Value = employeeSex; da.Fill(MyDataSet, "AddressBookTable");
不同的数据返回方式要用不同的方式执行。
参数返回,执行后要处理传出参数的值。
结果集返回,要用ExcuteReader或者ExcuteScaler等方式执行并获取数据。
CREATE Procedure Up_Bap_GetAddressBook
(
@EmployeeID varchar(20)='a',
@employeesName varchar(10)='b',
@employeesSex varchar(2)='c'
)
AS
if(@EmployeeID != 'a' and @employeesName != 'b' and @employeesSex != 'c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex
else if(@EmployeeID = 'a' and @employeesName!='b' and @employeesSex!='c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex
if(@EmployeeID!='a' and @employeesName = 'b' and @employeesSex!='c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesSex=@employeesSex
if(@EmployeeID!='a' and @employeesName!= 'b' and @employeesSex = 'c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesID='%'+@EmployeeID+'%' and employeesName='%'+@employeesName+'%'
if(@EmployeeID = 'a' and @employeesName!= 'b' and @employeesSex = 'c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesName like '%'+@employeesName+'%'
if(@EmployeeID != 'a' and @employeesName = 'b' and @employeesSex = 'c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesID like '%'+@EmployeeID+'%'
else if(@EmployeeID = 'a' and @employeesName = 'b' and @employeesSex!= 'c')
SELECT employeesID,employeesName,employeesSex,employeesPhone,employeesEmail,employeesAddress
from EmployeesInfo where employeesName='%'+@employeesName+'%' and employeesSex=@employeesSex
程序中的部分
SqlDataAdapter da = new SqlDataAdapter("Up_Bap_GetAddressBook", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.VarChar,20).Value = employeeID;
da.SelectCommand.Parameters.Add("@employeesName", SqlDbType.VarChar,10).Value = employeeName;
da.SelectCommand.Parameters.Add("@employeesSex", SqlDbType.VarChar,2).Value = employeeSex;
da.Fill(MyDataSet, "AddressBookTable");
存储过程通过出参返回数据,如果非要通过存储过程取数据集,似乎可以返回游标。
SqlConnection conn = new SqlConnection(connStr); //生成执行命令
SqlCommand cmd = new SqlCommand("proc_test", conn);
//说明是存储过程
cmd.CommandType = CommandType.StoredProcedure; //添加参数
cmd.Parameters.Add("@iXType", SqlDbType.Char);
cmd.Parameters["@iXType"].Direction = ParameterDirection.Input;
cmd.Parameters["@iXType"].Value = report.xtype.ToString().ToUpper(); //加载适配器,生成数据集,并关闭数据库连接
ds.Clear();
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, "group");
conn.Close(); gridControl1.DataSource = ds;