locked
Get SQl Data Using Comma Value RRS feed

  • Question

  • User-807418713 posted

    Hello

    This Is my table Data

    RefNo ItemName Qty
    R1 Apple 2
    R2 Mango 8
    R3 Banana 4
    R4 Lemon 6

    In c# asp.net textbox user will enter for example like this format R1,R3

    I want resultset to be show like this

    RefNo ItemName Qty
    R1 Apple 2
    R3 Banana 4
    Friday, February 1, 2019 7:31 AM

Answers

  • User-2082239438 posted

    You will need to use the SQL String Split function to get the result.

    ----------------------------------CREATE THIS FUNCTION----------------------------------
    CREATE FUNCTION [dbo].[SplitString]
    (
    	@String varchar(8000), @Delimiter char(1)
    )
    returns @temptable TABLE (items varchar(8000),itemno int)     
    as     
    begin     
    	declare @idx int
    	declare @i int=1
    	declare @slice varchar(8000)     
        
    	select @idx = 1     
    		if len(@String)<1 or @String is null  return     
        
    	while @idx!= 0     
    	begin     
    		set @idx = charindex(@Delimiter,@String)     
    		if @idx!=0     
    			set @slice = left(@String,@idx - 1)     
    		else     
    			set @slice = @String     
    		
    		if(len(@slice)>0)
    		BEGIN
    			insert into @temptable(Items,itemno) values(@slice,@i)
    			SET @i+=1
    		END
    
    		set @String = right(@String,len(@String) - @idx)     
    		if len(@String) = 0 break     
    	end 
    return     
    end
    ----------------------------------CREATE THIS FUNCTION----------------------------------
    
    ----------------------------RUN THIS QUERY TO FETCH DATA----------------------------
    SELECT *
    FROM YourTable
    WHERE RefNo IN
    (
    	SELECT items
    	FROM dbo.SplitString('R1,R2',',')
    )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 2, 2019 6:05 AM
  • User283571144 posted

    Hi Gopi.MCA,

    According to your description, I suggest you could try to use select [table] where RefNo in to achieve your requirement.

    You could parameterize each value according to the textbox value.

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GetDataFromSQLDatabase.aspx.cs" Inherits="WebForm.GetDataFromSQLDatabase" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
    
    
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
           
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>    
            </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace WebForm
    {
        public partial class GetDataFromSQLDatabase : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
         
     
                    using (
                      SqlConnection con =
                          new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-IdentitySQL-20190204011940;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
                    {
                    string[] tags = TextBox1.Text.Split(',');
    
                    string cmdText = "SELECT * FROM [table] where RefNo IN ({0})";
    
                    string[] paramNames = tags.Select(
                        (s, i) => "@tag" + i.ToString()
                    ).ToArray();
    
                    string inClause = string.Join(", ", paramNames);
                    using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause),con))
                    {
                        con.Open();
                        for (int i = 0; i < paramNames.Length; i++)
                        {
                            cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
                        }
                        GridView1.DataSource = cmd.ExecuteReader();
                        GridView1.DataBind();
                        con.Close();
                    }
    
              
                }
                
            }
        }
    }

    Result:

    Bert Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2019 8:58 AM

All replies

  • User-2082239438 posted

    You will need to use the SQL String Split function to get the result.

    ----------------------------------CREATE THIS FUNCTION----------------------------------
    CREATE FUNCTION [dbo].[SplitString]
    (
    	@String varchar(8000), @Delimiter char(1)
    )
    returns @temptable TABLE (items varchar(8000),itemno int)     
    as     
    begin     
    	declare @idx int
    	declare @i int=1
    	declare @slice varchar(8000)     
        
    	select @idx = 1     
    		if len(@String)<1 or @String is null  return     
        
    	while @idx!= 0     
    	begin     
    		set @idx = charindex(@Delimiter,@String)     
    		if @idx!=0     
    			set @slice = left(@String,@idx - 1)     
    		else     
    			set @slice = @String     
    		
    		if(len(@slice)>0)
    		BEGIN
    			insert into @temptable(Items,itemno) values(@slice,@i)
    			SET @i+=1
    		END
    
    		set @String = right(@String,len(@String) - @idx)     
    		if len(@String) = 0 break     
    	end 
    return     
    end
    ----------------------------------CREATE THIS FUNCTION----------------------------------
    
    ----------------------------RUN THIS QUERY TO FETCH DATA----------------------------
    SELECT *
    FROM YourTable
    WHERE RefNo IN
    (
    	SELECT items
    	FROM dbo.SplitString('R1,R2',',')
    )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 2, 2019 6:05 AM
  • User283571144 posted

    Hi Gopi.MCA,

    According to your description, I suggest you could try to use select [table] where RefNo in to achieve your requirement.

    You could parameterize each value according to the textbox value.

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GetDataFromSQLDatabase.aspx.cs" Inherits="WebForm.GetDataFromSQLDatabase" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
    
    
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
           
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>    
            </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace WebForm
    {
        public partial class GetDataFromSQLDatabase : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
         
     
                    using (
                      SqlConnection con =
                          new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-IdentitySQL-20190204011940;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
                    {
                    string[] tags = TextBox1.Text.Split(',');
    
                    string cmdText = "SELECT * FROM [table] where RefNo IN ({0})";
    
                    string[] paramNames = tags.Select(
                        (s, i) => "@tag" + i.ToString()
                    ).ToArray();
    
                    string inClause = string.Join(", ", paramNames);
                    using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause),con))
                    {
                        con.Open();
                        for (int i = 0; i < paramNames.Length; i++)
                        {
                            cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
                        }
                        GridView1.DataSource = cmd.ExecuteReader();
                        GridView1.DataBind();
                        con.Close();
                    }
    
              
                }
                
            }
        }
    }

    Result:

    Bert Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2019 8:58 AM
  • User-2082239438 posted

    Don't use the inline SQL Query in Application Side, this leads to SQL Injection. 

    Check more details here

    Monday, February 4, 2019 12:21 PM