Net 中使用 ADO.NET 的一些心得 5.13
//我的存储过程
create procedure proc_SelectFlightInfor
@cityfrom varchar(20), --@cityfrom
@cityto varchar(20), --@cityto
@mintime datetime, --@mintime
@maxtime datetime --@maxtime
as
select flightid,flightname,
(select cityname from city where city.cityid=flight.departurecityid) as 起飞城市名称,
(select cityname from city where city.cityid=flight.arrivalcityid) as 到达城市名称,
departuretime,arrivaltime
from flight
where departurecityid= @cityfrom and arrivalcityid=@cityto and
departuretime between @mintime and @maxtime
exec proc_SelectFlightInfor 3,1,'2005-06-12','2006-06-12'
//给存储过程传参数和值 并且调用存储过程
public DataSet GetFlightDataSetDataAccess(QueryFlight flight)
{
DataSet flightDataSet;
SqlParameter[] pars = new SqlParameter[4];
//这里的给参数起的名字和要和自己的存储过程中的名字要一致啊
pars[0]= new SqlParameter("@cityfrom",SqlDbType.VarChar,100);
pars[1]= new SqlParameter("@cityto",SqlDbType.VarChar,100);
pars[2]= new SqlParameter("@mintime",SqlDbType.DateTime,8);
pars[3]= new SqlParameter("@maxtime",SqlDbType.DateTime,8);
pars[0].Value=flight.Tables[0].Rows[0][0].ToString();
pars[1].Value=flight.Tables[0].Rows[0][1].ToString();
pars[2].Value=flight.Tables[0].Rows[0][3].ToString();
pars[3].Value=flight.Tables[0].Rows[0][2].ToString();
flightDataSet=myDataOperate.GetDataSetObject(pars,"proc_SelectFlightInfor");
return flightDataSet;
}
//接收存储过程的公有方法
public DataTable GetDataTableObject(SqlParameter[] par,string procName)
{
try
{
sqlConn.Open();
sqlComm = new SqlCommand(procName,sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
for(int i = 0; i< par.Length;i++)
{
sqlComm.Parameters.Add(par[i]);
}
dt = new DataTable();
dataAdapter = new SqlDataAdapter(sqlComm);
dataAdapter.Fill(dt);
}
catch(SqlException ex)
{
string str = ex.ToString();
throw new Exception(ex.Message);
}
finally
{
sqlConn.Close();
}
return dt;
}