这里总结一下ASP.Net程序中存储过程的使用方法,以及它如何与ADO.NET中的SqlDataAdapter对象,DataSet对象等结合使用.
一,不带参数查询的存储过程:
1,创建存储过程语句:
Create proc [dbo].[sp_SelectUsers]
as
begin
select * from tb_Users;
end
2,ASP.Net中对存储过程的调用代码:
public partial class SP_Select : System.Web.UI.Page
{
DBClass dbc = new DBClass();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1BindData();
}
}
public void GridView1BindData()
{
SqlCommand cmd = dbc.GetCommandProc("sp_SelectUsers");
DataSet ds = dbc.GetDS(cmd);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
注:DBClass公共类的代码为:
View Code
public class DBClass
{
public DBClass()
{
//todo:構造函數.
}
#region 創建與數據庫SP的連接
/// <summary>
/// 創建與數據庫SP的連接
/// </summary>
/// <returns></returns>
public SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SP"].ConnectionString);
return con;
}
#endregion
#region 執行存儲過程,返回SqlCommand對象
/// <summary>
/// 執行存儲過程,返回SqlCommand對象
/// </summary>
/// <param name="strProcName"></param>
/// <returns></returns>
public SqlCommand GetCommandProc(string strProcName)
{
//DBClass dbc = new DBClass();
SqlConnection con = GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcName;
return cmd;
}
#endregion
#region 執行执行SQL语句命令的SqlCommand对象,用于更新數據庫
/// <summary>
/// 執行执行SQL语句命令的SqlCommand对象,用于更新數據庫
/// </summary>
/// <param name="cmd"></param>
public void ExecNonQuery(SqlCommand cmd)
{
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
//StoredProcedure(); //刷新數據,加這個會報錯.
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
private void StoredProcedure()
{
throw new NotImplementedException();
}
#endregion
#region 執行SQL語句的SqlCommand對象,返回DataSet
/// <summary>
/// 執行SQL語句的SqlCommand對象,返回DataSet
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public DataSet GetDS(SqlCommand cmd)
{
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
#endregion
}
二,带参数查询的存储过程:
1,创建存储过程代码:
Create proc [dbo].[sp_SelectParUsers]
@Id int
as
begin
select * from tb_Users where UserId=@Id;
end
2,项目中存储过程的调用代码:
View Code
public partial class SP_SelectWithPar : System.Web.UI.Page
{
DBClass dbc = new DBClass();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1BindData(2);
}
}
public void GridView1BindData(int Id)
{
SqlCommand cmd = dbc.GetCommandProc("sp_SelectParUsers");
cmd.Parameters.AddWithValue("@Id",Id);
DataSet ds = dbc.GetDS(cmd);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
}
三,新增的存储过程:
1,创建存储过程的代码:
Create proc [dbo].[sp_InserUsers]
(
@UserName nvarchar(50),
@Pwd varchar(50)
)
as
begin
insert into tb_Users (UserName,pwd) values (@UserName,@Pwd);
end
2,项目中对存储过程的调用代码:
View Code
public partial class SP_InsertUser : System.Web.UI.Page
{
DBClass dbc = new DBClass();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnAdd_Click(object sender, EventArgs e)
{
AddUser();
}
public void AddUser()
{
SqlCommand cmd = dbc.GetCommandProc("sp_InserUsers");
cmd.Parameters.AddWithValue("@UserName", txtUserName.Text.Trim());
cmd.Parameters.AddWithValue("@Pwd", txtPwd.Text.Trim());
if (txtUserName.Text.Trim() == "" || txtPwd.Text.Trim() == "")
{
Response.Write("<script>alert(\'用戶名和密碼不能為空!\')</script>");
return;
}
//todo:此处省略了对用户名是否存在的检测.
dbc.ExecNonQuery(cmd);
Response.Write("<script>alert(\'增加成功!\')</script>");
}
}
四,删除数据的存储过程
1,创建存储过程的代码:
Create proc [dbo].[sp_DeleteUsers]
(
@Id int
)
as
begin
delete from tb_Users where UserId=@Id;
end
2,项目调用存储过程的代码:
View Code
public partial class SP_DeleteUsers : System.Web.UI.Page
{
DBClass dbc = new DBClass();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDelete_Click(object sender, EventArgs e)
{
DeleteUsers();
}
public void DeleteUsers()
{
SqlCommand cmd = dbc.GetCommandProc("sp_DeleteUsers");
cmd.Parameters.AddWithValue("@Id",txtId.Text.Trim());
if (txtId.Text.Trim() == "")
{
Response.Write("<script>alert(\'Id不能為空!\')</script>");
return;
}
dbc.ExecNonQuery(cmd);
Response.Write("<script>alert(\'刪除成功!\')</script>");
}
}
五,修改数据的存储过程:
1,创建存储过程的代码:
Create proc [dbo].[sp_UpdateUsers]
(
@pwd varchar(50),
@UserId varchar(50)
)
as
begin
update tb_Users set pwd=@pwd where UserId=@UserId;
end
2,项目对存储过程的调用代码:
View Code
public partial class SP_UpdateUsers : System.Web.UI.Page
{
DBClass dbc = new DBClass();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
UpdateUsersById();
}
public void UpdateUsersById()
{
SqlCommand cmd = dbc.GetCommandProc("sp_UpdateUsers");
cmd.Parameters.AddWithValue("@UserId",txtId.Text.Trim());
cmd.Parameters.AddWithValue("@pwd", txtPwd.Text.Trim());
if (txtId.Text.Trim() == "" || txtPwd.Text.Trim() == "")
{
Response.Write("<script>alert(\'Id和密碼不能為空!\')</script>");
return;
}
if (CheckUserId()) //檢查UserId是否存在,如果存在才執行修改動作,否則提供Id不存在并退出方法.
{
dbc.ExecNonQuery(cmd);
Response.Write("<script>alert(\'密碼修改成功!\')</script>");
}
else
{
Response.Write("<script>alert(\'Id不存在!\')</script>");
return;
}
}
public bool CheckUserId()
{
SqlCommand cmd = dbc.GetCommandProc("sp_SelectParUsers");
cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());
DataSet ds = dbc.GetDS(cmd);
if (ds.Tables[0].Rows.Count < 1)
{
return false;
}
else
{
return true;
}
}
}
以上就是常用的存储过程的创建及调用方法了,在这里记录下来,一方面与各位园友分享,欢迎各位园友与我交流,另一方面,记录在这里方便自己以后温习.
