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

9501 Points

2976 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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Reply

yrb.yogi

Star

10243 Points

2349 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