在asp.net中使用sql server存储过程 (包括SqlDataAdapter调用存储过程)

数据库名称:company,

表名:company.dbo.department

 

存储过程的定义:

//获取所有department信息 create proc getalldep as select * from company.dbo.department //插入一个department create proc insertdep @ID int output, @Name varchar(50) as insert into company.dbo.department (Name) values(@Name); set @ID=@@IDENTITY

 

下面是在asp.net中使用,新建一个类 ,比如department.cs

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.SqlClient; using System.Data; namespace WebApplication7 { public class department { private string constring; public Emp() { constring = "Data Source=localhost;Initial Catalog=company;user id=tt;password=y357395775@;"; } public Emp(string connectionstring) { constring = connectionstring; } public int insertemp(string name) { SqlConnection con = new SqlConnection(constring); SqlCommand cmd = new SqlCommand("insertdep", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50)); cmd.Parameters["@Name"].Value = name; cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)); cmd.Parameters["@ID"].Direction = ParameterDirection.Output; try { con.Open(); cmd.ExecuteNonQuery(); return (int)cmd.Parameters["@ID"].Value; } catch (SqlException exc) { Console.Write(exc.Message.ToString()); } finally { con.Close(); } } public DataTable getalldep() { SqlConnection con = new SqlConnection(constring); SqlCommand cmd = new SqlCommand("getalldep", con); cmd.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); SqlDataAdapter da=new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds, "dep"); return ds.Tables["dep"]; } } }

然后在页面上新建一个gridview控件引用之

protected void Page_Load(object sender, EventArgs e) { department dep = new department(); gvdep.DataSource = dep.getalldep(); gvdep.DataBind(); } //gridview控件的id为gvdep

这里只是实现了getalldep的使用.

 

需要注意的是SqlDataAdapter类对存储过程的调用过程:

SqlConnection con = new SqlConnection(constring); SqlCommand cmd = new SqlCommand("getalldep", con); cmd.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); SqlDataAdapter da=new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds, "dep"); return ds.Tables["dep"];