Get SQl Data Using Comma Value

----------------------------------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',',')
)

Yogesh Bhadauriya
Technical Lead
Ahmedabad, India

Reply

Brando ZWZ

Star

8921 Points

2721 Posts

Re: Get SQl Data Using Comma Value

Feb 04, 2019 08:58 AM|Brando ZWZ|LINK

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: