How to clean a string in ASP.NET using comma separated values from database?

Question

How to get all values along with control Id using Jquery in ASP.NET?

Answer 1

I have faced a scenario when I need to clean a user input string and remove some specific words from it but the words are in a database table as comma separated values. I have find the solution for my problem and written a function which will get comma separated values string and user input string as parameters and compare database words with input string words and replace them with empty string one by one and return a clean string. I am going to share this code with you in this article.

1.Create a database and table and insert some words in it
2.Create a new website in Visual Studio 2012 either in VB.NET or C#

<asp:TextBox ID="txtInputString" runat="server">asp:TextBox>
<br />
<asp:Button ID="btnCleanString" runat="server" Text="Button" />
<br />
<asp:Label ID="lblCleanString" runat="server" Text="Label">asp:Label>

3.Write below code in button click event

VB.NET

Protected Sub btnCleanString_Click(sender As Object, e As EventArgs) Handles btnCleanString.Click

    Dim Words As String = ""
    Dim CleanString As String = ""
    Dim InputString As String = txtInputString.Text

    Dim myConnectionString As String = "Data Source=Local;Initial Catalog=MyDatabase;Integrated Security=True;Pooling=False"
    Dim myConnection As New SqlConnection(myConnectionString)
    Dim myQuery As String = "SELECT Words FROM MyDatabase.dbo.MyTable"
    Dim myCommand As New SqlCommand(myQuery, myConnection)


    myConnection.Open()

    Dim myAdapter As New SqlDataAdapter(myCommand)
    Dim myTable As New DataTable()
    myAdapter.Fill(myTable)

    For Each myRow As DataRow In myTable.Rows
      Words = myRow("Words")
      Exit For
    Next

    myConnection.Close()
    CleanString = CleanStringWithWords(Words, InputString)
    lblCleanString.Text = CleanString
  End Sub

C#

protected void btnCleanString_Click(object sender, EventArgs e)
{
   string Words = "";
   string CleanString = "";
   string InputString = txtInputString.Text;

   string myConnectionString = "Data Source=Local;Initial Catalog=MyDatabase;Integrated Security=True;Pooling=False";
   SqlConnection myConnection = new SqlConnection(myConnectionString);
   string myQuery = "SELECT Words FROM MyDatabase.dbo.MyTable";
   SqlCommand myCommand = new SqlCommand(myQuery, myConnection);


   myConnection.Open();

   SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
   DataTable myTable = new DataTable();
   myAdapter.Fill(myTable);

   foreach (DataRow myRow in myTable.Rows)
   {
      Words = Convert.ToString(myRow["Words"]);
      break;
   }

   myConnection.Close();
   CleanString = CleanStringWithWords(Words, InputString);
   lblCleanString.Text = CleanString;
 }

4.Write below function in your file to clean string

VB.NET

Public Shared Function CleanStringWithWords(ByVal Words As String, ByVal InputString As String) As String

  Dim CleanString As String = InputString

  Dim arrValues1() As String = Split(Words, ",")
  Dim arrValues2() As String = Split(InputString, " ")
  Dim ThisValue1 As String = ""
  Dim ThisValue2 As String = ""
  Dim Counter As Int32 = 0
  Dim Counter2 As Int32 = 0

  For intCounter1 As Int32 = 0 To arrValues1.Length - 1

    ThisValue1 = arrValues1(intCounter1)

    For intCounter2 As Int16 = 0 To arrValues2.Length - 1

      ThisValue2 = arrValues2(intCounter2)

      If ThisValue1.ToLower.Trim = ThisValue2.ToLower.Trim Then
        CleanString = Replace(CleanString, ThisValue2, "")
      End If
      Counter2 += 1
    Next

    Counter += 1
  Next

  CleanString = CleanString.Replace("  ", " ")
  CleanString = Trim(CleanString)

  Return CleanString
End Function

C#

public static string CleanStringWithWords(string Words, string InputString)
{

   string CleanString = InputString;

   string[] arrValues1 = Words.Split(',');
   string[] arrValues2 = InputString.Split(' ');
   string ThisValue1 = "";
   string ThisValue2 = "";
   Int32 Counter = 0;
   Int32 Counter2 = 0;


   for (Int32 intCounter1 = 0; intCounter1 <= arrValues1.Length - 1; intCounter1++)
   {
     ThisValue1 = arrValues1[intCounter1];

     for (Int16 intCounter2 = 0; intCounter2 <= arrValues2.Length - 1; intCounter2++)
     {
       ThisValue2 = arrValues2[intCounter2];

       if (ThisValue1.ToLower().Trim() == ThisValue2.ToLower().Trim())
       {
          CleanString = CleanString.Replace(ThisValue2, "");
       }
       Counter2 += 1;
     }

     Counter += 1;
   }

   CleanString = CleanString.Replace("  ", " ");
   CleanString = CleanString.Trim();

   return CleanString;
}

5.Now start debugging, enter some string and see the result