使用逗号分隔含有ID的输入参数(sql 查询)

 

提问:

怎么获取数据表格行中的 ID号?

 

 For Each row As GridViewRow In gvManagerList.Rows
               Dim cb As CheckBox  =  row.FindControl( " cbSelect " )
               If cb.Checked  =  True Then
                ' HELP! Anyone here know the command here to get the IDs from datatable ?
                 ' GetID = how to GET the IDs column value of the database table
                 Label.Text  =  Label.Text  +  getID  +   " , "

              End If

解决方案:

使用分隔功能

 

IF   EXISTS  (  SELECT   *   FROM  INFORMATION_SCHEMA.ROUTINES  WHERE  SPECIFIC_NAME  =  N ' Split '  )
DROP   FUNCTION  Split
GO  
CREATE   FUNCTION  dbo.Split (  @ItemList   NVARCHAR ( 4000 ),  @delimiter   CHAR ( 1 ) )
RETURNS   @IDTable   TABLE  (Item  VARCHAR ( 50 )) 
AS  
BEGIN  
DECLARE   @tempItemList   NVARCHAR ( 4000 )  SET   @tempItemList   =   @ItemList  
DECLARE   @i   INT   DECLARE   @Item   NVARCHAR ( 4000 )
SET   @tempItemList   =   REPLACE  ( @tempItemList ,  '   ' ,  '' )
SET   @i   =   CHARINDEX ( @delimiter ,  @tempItemList ) 

    WHILE  ( LEN ( @tempItemList )  >   0 )
BEGIN  
IF   @i   =   0   SET   @Item   =   @tempItemList  
ELSE   SET   @Item   =   LEFT ( @tempItemList ,  @i   -   1 )
INSERT   INTO   @IDTable (Item)  VALUES ( @Item )
IF   @i   =   0   SET   @tempItemList   =   ''  
ELSE   SET   @tempItemList   =   RIGHT ( @tempItemList ,  LEN ( @tempItemList )  -   @i )
SET   @i   =   CHARINDEX ( @delimiter ,  @tempItemList )
END   RETURN  
END   GO  

 

我建立了以下的存储过程来调用分隔功能.

 

CREATE   PROCEDURE  USP_RetrieveInformationForSelectedEmployees  @p_selectedEmployees   NVARCHAR ( 50 )  AS   BEGIN   SELECT   *   FROM  Employees  WHERE  EmployeeID  in  ( SELECT  Item  FROM  split( @p_selectedEmployees ,  ' , ' ))  END

下面是ASP.NET应用程序:

 

1 :  <% @ Page Language = " C# "   %>
  2 :  <% @ Import Namespace = " System.Data.SqlClient "   %>
  3 :  <% @ Import Namespace = " System.Web.Configuration "   %>
  4 :  <% @ Import Namespace = " System.Data "   %>
  5 :  
  6 :  <! DOCTYPE html PUBLIC  " -//W3C//DTD XHTML 1.0 Transitional//EN "  
  7 :   " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
  8 :  
  9 :  < script runat = " server " >
  10 :  
  11 :   protected   void  Button1_Click( object  sender, EventArgs e)
  12 :  ... {
 13: RetrieveInformationForSelectedEmployees();
 14: }
  15 :  
  16 :   private   void  RetrieveInformationForSelectedEmployees()
  17 :  ... {
 18: StringBuilder sb = new StringBuilder();
 19:  
 20:  // First loop through the GridView and see which
 21:  // employees were selected. I use the StringBuilder
 22:  // since the list could be a very long list.
 23:  foreach (GridViewRow row in GridView1.Rows)
 24: ...{
 25:  if (((CheckBox)row.FindControl("CheckBox1")).Checked)
 26: ...{
 27: sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
 28: }
 29: }
 30:  
 31:  using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
 32: ["NorthwindConnectionString"].ConnectionString))
 33: ...{
 34:  using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
 35: ...{
 36: cmd.CommandType = CommandType.StoredProcedure;
 37: cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
 38:  
 39: DataSet ds = new DataSet();
 40: SqlDataAdapter da = new SqlDataAdapter(cmd);
 41: da.Fill(ds);
 42:  
 43: GridViewResult.DataSource = ds;
 44: GridViewResult.DataBind();
 45: }
 46: }
 47: }
  48 :  
  49 :  </ script >
  50 :  
  51 :  < html xmlns = " http://www.w3.org/1999/xhtml "   >
  52 :  < head runat = " server " >
  53 :   < title > Untitled Page </ title >
  54 :  </ head >
  55 :  < body >
  56 :   < form id = " form1 "  runat = " server " >
  57 :   < div >
  58 :   < asp:GridView ID = " GridView1 "  runat = " server "  AutoGenerateColumns = " False "  
  59 :  DataKeyNames = " EmployeeID "  DataSourceID = " SqlDataSource1 " >
  60 :   < Columns >
  61 :   < asp:TemplateField >
  62 :   < ItemTemplate >
  63 :   < asp:CheckBox ID = " CheckBox1 "  runat = " server "   />
  64 :   </ ItemTemplate >
  65 :   </ asp:TemplateField >
  66 :   < asp:BoundField DataField = " LastName "  HeaderText = " LastName "  
  67 :  SortExpression = " LastName "   />
  68 :   < asp:BoundField DataField = " FirstName "  HeaderText = " FirstName "  
  69 :  SortExpression = " FirstName "   />
  70 :   < asp:BoundField DataField = " Title "  HeaderText = " Title "  
  71 :  SortExpression = " Title "   />
  72 :   </ Columns >
  73 :   </ asp:GridView >
  74 :   < asp:SqlDataSource ID = " SqlDataSource1 "  runat = " server "  
  75 :  ConnectionString = " <%$ ConnectionStrings:NorthwindConnectionString %> "
  76 :  SelectCommand = " SELECT [EmployeeID], [LastName], [FirstName], [Title]
  77 : FROM [Employees] ORDER BY [LastName], [FirstName] " >
  78 :   </ asp:SqlDataSource >
  79 :  
  80 :   < asp:Button ID = " Button1 "  runat = " server "  Text = " Retrieve data "  OnClick = " Button1_Click "   />
  81 :   < p >
  82 :   < asp:GridView runat = " server "  ID = " GridViewResult "   />
  83 :   </ p >
  84 :   </ div >
  85 :   </ form >
  86 :  </ body >
  87 :  </ html >

其中,从23到29行,循环了GridView中的行,判断第一列中的CheckBox是否选中,若选中,粘贴相应的DataKey到StringBuilder字符串实例中,在第37行,我传递了所有雇员Employee的ID号,存储过程在IN从句中使用了分隔功能.