在ASP.NET页面里执行存储过程
SqlCommand myCommand=new SqlCommand();
myCommand.Connection=new SqlConnection(strCon);
myCommand.Connection.Open();
myCommand.CommandText = "sp_DeletePolicy" ;//存储过程名
myCommand.CommandType = CommandType.StoredProcedure ;//命令类型
SqlParameter myUserId = new SqlParameter("@usercode", SqlDbType.VarChar,20);
myUserId.Value =username;
myCommand.Parameters.Add(myUserId); //绑定参数
myCommand.ExecuteNonQuery();
这是返回一个DATASET的语句,没有参数
public DataSet GetFreightInfo()
{
SqlCommand cmd=new SqlCommand("GetFreightInfo",con);
cmd.CommandType=CommandType.StoredProcedure;
DataSet ds=new DataSet();
cmdAdp.SelectCommand=cmd;
con.Open();
cmdAdp.Fill(ds);
con.Close();
return ds;
}
这是插入
public bool InsertCity(int provinceid,string city)
{
SqlCommand cmd=new SqlCommand("InsertCity",con);
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter parProvinceID=new SqlParameter("@provinceid",SqlDbType.Int);
parProvinceID.Value=provinceid;
cmd.Parameters.Add(parProvinceID);
SqlParameter parCity=new SqlParameter("@city",SqlDbType.VarChar,20);
parCity.Value=city;
cmd.Parameters.Add(parCity);
con.Open();
int result=cmd.ExecuteNonQuery();
con.Close();
if(result>0)
{
return true;
}
else
{
return false;
}
}
这是有一个IN参数,一个OUT参数
public string GetPY(string name)
{
SqlCommand cmd=new SqlCommand();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="pro_GetPY";
cmd.Connection=con;
con.Open();
SqlParameter parname=new SqlParameter();
parname.ParameterName="@str";
parname.SqlDbType=SqlDbType.NVarChar;
parname.Value=name;
SqlParameter parmsg=new SqlParameter();
parmsg.ParameterName="@s";
parmsg.Direction=ParameterDirection.Output;
parmsg.SqlDbType=SqlDbType.NVarChar;
parmsg.Size=50;
cmd.Parameters.Add(parname);
cmd.Parameters.Add(parmsg);
cmd.ExecuteReader();
string strpy=parmsg.Value.ToString();
con.Close();
return strpy;
}