是笔记 所以代码没有优化。一切从简
存储过程:
方法1:返回一个集合,此方法灵活。
ALTER PROCEDURE [dbo].[Porc_temp]
AS
BEGIN
select * from admin ;
END
代码:
public ActionResult Index()
{
DataTable dt;
string conString = "server=.;database=xx;uid=sa;pwd=xxx;";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand com = con.CreateCommand();
com.CommandText = "Porc_temp";//和存储过程名称要一致
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
}
return View(dt);
}
方法2:存储过程返回单个的值。
ALTER PROCEDURE [dbo].[porc_retrun]
AS
BEGIN
return 1;
END
代码:
public ActionResult Details(int id)
{
string conString = "server=.;database=xx;uid=sa;pwd=xxx;";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand com = con.CreateCommand();
com.CommandText = "porc_retrun";
com.CommandType = CommandType.StoredProcedure;
SqlParameter parms = new SqlParameter("@returnValue", SqlDbType.Int);
parms.Direction = ParameterDirection.ReturnValue;
com.Parameters.Add(parms);
con.Open();
int result = com.ExecuteNonQuery();
string std_id = com.Parameters["@returnValue"].Value.ToString();
ViewBag.id = std_id;
}
return View();
}
3:存储过程指定参数返回,其实也有点类似方法2
存储过程:
ALTER PROCEDURE [dbo].[Proc_add]
@st_id int output,//指定返回的参数
@userName varchar(20),
@pwd varchar(20)
AS
BEGIN
insert into admin values(@userName,@pwd);
select @st_id=@@identity;
END
代码:
public ActionResult Create(FormCollection collection)
{
string conString = "server=.;database=xx;uid=sa;pwd=xxx;";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand com = con.CreateCommand();
com.CommandText = "Proc_add";
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] parms = {
new SqlParameter("@st_id",SqlDbType.Int),
new SqlParameter("@userName",SqlDbType.VarChar),
new SqlParameter("@pwd",SqlDbType.VarChar)
};
parms[0].Direction = ParameterDirection.Output;
parms[1].Value = "sp";
parms[2].Value = "112102";
com.Parameters.AddRange(parms);
con.Open();
int result = com.ExecuteNonQuery();
string std_id = com.Parameters["@st_id"].Value.ToString();
ViewBag.id = std_id;
}
return View();
}