[Database]创建存储过程并在ASP.NET中的调用带参数的存储过程与及调用带参数的SQL语句对比

[基础知识]

==============================================================

创建存储过程
use Northwind
go

create  proc myproc_qghboy02
(
@select_ID varchar(5)
)
as

select * from Customers where CustomerID like '%'+ @select_ID +'%'

go

--测试存储过程
exec  myproc_qghboy02   'AL'

存ASP.NET中调用存储过程

1. using System.Data.Sql;
2. using System.Data.SqlClient;
6.         string strConn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
8.         SqlConnection conn = new SqlConnection(strConn);
10.         SqlDataAdapter adapter = new SqlDataAdapter("myproc_qghboy02", conn);
12.         adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
14.         SqlParameter parm=new SqlParameter("@select_ID",SqlDbType.VarChar);
16.         parm.Value = this.TextBox1.Text.Trim();
18.         adapter.SelectCommand.Parameters.Add(parm);
20.         conn.Open();
22.         DataSet ds = new DataSet();
24.         adapter.Fill(ds);
26.         this.GridView2.DataSource = ds.Tables[0].DefaultView;
28.         this.GridView2.DataBind();
30.         conn.Close();

==============================================================
调用带参数的SQL语句

1. using System.Data.Sql;
2. using System.Data.SqlClient;
6.         string strConn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
8.         SqlConnection conn = new SqlConnection(strConn);
10.         string sql = "select * from Customers where CustomerID like '%'+ @select_ID + '%'";
12.         SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
14.         adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
16.         //SqlParameter parm = new SqlParameter("@select_ID", SqlDbType.VarChar);
18.         parm.Value = this.TextBox1.Text.Trim();
20.         adapter.SelectCommand.Parameters.Add(parm);
22.         conn.Open();
24.         DataSet ds = new DataSet();
26.         adapter.Fill(ds);
28.         this.GridView3.DataSource = ds.Tables[0].DefaultView;
30.         this.GridView3.DataBind();
32.         conn.Close();