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
Sr Technical Lead
Ahmedabad, India

Reply

Brando ZWZ

Star

9831 Points

3120 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:

Bert Regards,

Brando

.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.

Reply

yrb.yogi

Star

10303 Points

2360 Posts

Re: Get SQl Data Using Comma Value

Feb 04, 2019 12:21 PM|yrb.yogi|LINK

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

Check more details here

Yogesh Bhadauriya
Sr Technical Lead
Ahmedabad, India

Get SQl Data Using Comma Value[Answered] RSS

3 replies

Shortcuts