Results 1 to 6 of 6

Thread: Making a DataTable case-insensitive

  1. #1
    Join Date
    Nov 2009
    Posts
    47

    Making a DataTable case-insensitive

    Is it possible to make a DataTable case-insensitive? Example, in a table I have a list of names, and among these name I have:
    - frédéric
    - frederic

    I only filter the DataTable that contains data with
    Code:
    RowFilter = "first_name like 'frederic'"
    the lines "frédéric" and "frederic" be reduced.

    Is this possible?

  2. #2
    Join Date
    Nov 2008
    Posts
    1,022

    Re: Making a DataTable case-insensitive

    I think DataTable.CaseSensitive Property will be helpful to you. It indicates whether string comparisons within the table are case-sensitive. The CaseSensitive property affects string comparisons in sorting, searching, and filtering.

    For example:

    Code:
    Private Sub ToggleCaseSensitive()
        Dim t As DataTable
        Dim foundRows() As DataRow
    
        t = CreateDataSet().Tables(0)
    
        t.CaseSensitive = False
        foundRows = t.Select("item = 'abc'")
    
        ' Print out DataRow values. Row 0 contains the value we're looking for.
        PrintRowValues(foundRows, "CaseSensitive = False")
    
        t.CaseSensitive = True
        foundRows = t.Select("item = 'abc'")
    
        PrintRowValues(foundRows, "CaseSensitive = True")
    End Sub
    
    Public Function CreateDataSet() As DataSet
        ' Create a DataSet with one table, two columns
        Dim ds As New DataSet
        Dim t As New DataTable("Items")
    
        ' Add table to DataSet
        ds.Tables.Add(t)
    
        ' Add two columns
        Dim c As DataColumn
    
        ' First column
        c = t.Columns.Add("id", Type.GetType("System.Int32"))
        c.AutoIncrement = True
    
        ' Second column
        t.Columns.Add("item", Type.GetType("System.String"))
    
        ' Set primary key
        t.PrimaryKey = New DataColumn() {t.Columns("id")}
    
        For i As Integer = 0 To 9
            t.Rows.Add(New Object() {i, i.ToString()})
        Next
        t.Rows.Add(New Object() {11, "abc"})
        t.Rows.Add(New Object() {15, "ABC"})
    
        CreateDataSet = ds
    End Function
    
    Private Sub PrintRowValues(ByRef rows As DataRow(), ByVal label As String)
        Console.WriteLine()
        Console.WriteLine(label)
        If rows.Length <= 0 Then
            Console.WriteLine("no rows found")
            Return
        End If
    
        For Each r As DataRow In rows
            For Each c As DataColumn In r.Table.Columns
                Console.Write(vbTab & " {0}", r(c))
            Next
            Console.WriteLine()
        Next
    End Sub

  3. #3
    Join Date
    Nov 2009
    Posts
    47

    Re: Making a DataTable case-insensitive

    My DataTable is already sensitive. What I want is to make it insensitive to accented characters. I mean some of the accented character given below (foreign characters):

    Ç, ü, é, â, ä, Ã*, ê, ë, è ...........

  4. #4
    Join Date
    Oct 2005
    Posts
    2,393

    Re: Making a DataTable case-insensitive

    I have a method that removes accents and other diacritical marks. this is not the answer to your question, but you'll find your happiness ...

    Code:
    private string RemoveDiacritics(string s)
          {
             string normalizedString = s.Normalize(NormalizationForm.FormD);
             StringBuilder stringBuilder = new StringBuilder();
     
             for(int i = 0; i < normalizedString.Length; i++)
             {
                Char c = normalizedString[i];
                if(CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
                   stringBuilder.Append(c);
             }
     
             return stringBuilder.ToString();
          }

  5. #5
    Join Date
    Nov 2009
    Posts
    47

    Re: Making a DataTable case-insensitive

    I have read your solution, and I had already found this on internet during my research but it is not entirely to my need.

    I use this method to remove these characters from user input, however, the accents are always in the database: the comparison between "frederic" entered by the user and "frédéric" in the database is always false. Therein lies my problem.

    For now the only solution I see is to select the database by replacing the diacritics in the request. It works but it is not optimized, given the number of rows.

  6. #6
    Join Date
    Nov 2008
    Posts
    1,185

    Re: Making a DataTable case-insensitive

    If you want to do in the database, I am tempted to tell you to add a column to your table with either the name without accents or a foreign key to a table containing all the names without accents (the latter option allows reduce the size of the database, but increases the processing time of applications).

    You fill this column on inserts and updates in the table containing the names and you will only use this column to your accents-insensitive searches.

Similar Threads

  1. Ado.Net: Reorder columns in a DataTable
    By Sergio 1 in forum Software Development
    Replies: 3
    Last Post: 29-12-2010, 02:45 AM
  2. How to sort a datatable
    By Udayachal in forum Software Development
    Replies: 6
    Last Post: 23-01-2010, 01:23 PM
  3. Nested DataTable for GridView
    By Renderman21 in forum Software Development
    Replies: 3
    Last Post: 08-05-2009, 02:08 PM
  4. Exporting datatable to excel with ADO.net
    By Sanket07 in forum Software Development
    Replies: 2
    Last Post: 03-02-2009, 05:52 PM
  5. How can i export datatable to excel
    By Leena in forum Software Development
    Replies: 3
    Last Post: 30-01-2009, 11:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,751,726,519.07903 seconds with 16 queries