Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 13-11-2009
Member
 
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?
Reply With Quote
  #2  
Old 13-11-2009
Member
 
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
Reply With Quote
  #3  
Old 13-11-2009
Member
 
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):

?, ?, ?, ?, ?, ?, ?, ?, ? ...........
Reply With Quote
  #4  
Old 13-11-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
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();
      }
Reply With Quote
  #5  
Old 13-11-2009
Member
 
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.
Reply With Quote
  #6  
Old 13-11-2009
Member
 
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Making a DataTable case-insensitive"
Thread Thread Starter Forum Replies Last Post
Ado.Net: Reorder columns in a DataTable Sergio 1 Software Development 3 29-12-2010 02:45 AM
How to sort a datatable Udayachal Software Development 6 23-01-2010 01:23 PM
Nested DataTable for GridView Renderman21 Software Development 3 08-05-2009 02:08 PM
Exporting datatable to excel with ADO.net Sanket07 Software Development 2 03-02-2009 05:52 PM
How can i export datatable to excel Leena Software Development 3 30-01-2009 11:45 PM


All times are GMT +5.5. The time now is 11:05 PM.