none
How do I assign a collation (CultureInfo object) to a DataColumn and DataTable? RRS feed

  • Question

  • I'd like to mirror SQL Server's behaviour of assigning different collations to different string columns of a table.

    How can I assign a collation to a DataColumn object?

    Your answers are appreciated!

    Sunday, November 21, 2010 7:13 PM

All replies

  • Hello,

    Would you please be more specific about your requirement as I did not quite understand what do you want to achieve. Do you want to add multiple rows to a datatable? For example,

    dt.Rows.Add(1, "some name", DateTime.Now);

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Monday, November 22, 2010 8:36 AM
  • Hi Roahn,

    thank you for replying.

    I want to create a DataTable containing some case insensitive UNIQUE string columns. I want to achieve the DataTable to throw a ConstraintException whenever the user adds a string that already exists in another row.


    The DataTable is supposed to resemble very much the following SQL Server 2008 DDL script:

    CREATE TABLE
      (english NVARCHAR(100) COLLATE Latin1_General_CI_AI NOT NULL UNIQUE
      ,turkish NVARCHAR(100) COLLATE Turkish_CI_AI NOT NULL UNIQUE
      )

    Here the english column uses another collation than the turkish column does. This is a well-know .NET example for the String class and the TextInfo class.


    So, mimicking this design, inserting the following text into that DataTable is supposed to throw:

    myDataTable.Rows.Add(new object[] { "file", "+" });
    myDataTable.Rows.Add(new object[] { "FILE", "-" });

    whereas the following should not throw:

    myDataTable.Rows.Add(new object[] { "+", "file" });
    myDataTable.Rows.Add(new object[] { "-", "FILE" });


    Thanks for trying to help,
    Axel Dahmen
    www.axeldahmen.de

    Tuesday, November 23, 2010 11:08 AM
  • Perhaps try setting the Constraint propert of the DataTable to UniqueConstraint will acheive what you want?  Not sure.

    Public Sub CreateDataTable()
      Dim dataTable As DataTable = New DataTable
      dataTable.Columns.Add("CustomerID", Type.GetType("System.String"))
      dataTable.Columns.Add("CompanyName", Type.GetType("System.String"))
    
      Dim uniqueConstraint As UniqueConstraint = _
       New UniqueConstraint("CustConstraint", _
       New DataColumn() {dataTable.Columns("CustomerID"), _
       dataTable.Columns("CompanyName")})
    
      dataTable.Constraints.Add(uniqueConstraint)
    End Sub
    

    or...

    Private Sub DemonstrateEnforceConstraints()
    
     Try
    
     ' Create a DataSet with one table, one column and 
     ' a UniqueConstraint.
     Dim dataSet As DataSet = New DataSet("dataSet")
     Dim table As DataTable = New DataTable("table")
     Dim column As DataColumn = New DataColumn("col1")
     column.Unique = True
     table.Columns.Add(column)
     dataSet.Tables.Add(table)
     
     ' add five rows.
     Dim row As DataRow
     Dim i As Integer
     For i = 0 To 4
      row = table.NewRow()
      row("col1") = i
      table.Rows.Add(row)
     Next
     table.AcceptChanges()
    
     dataSet.EnforceConstraints = True
     
     Catch e As System.Data.ConstraintException
    	 ' Process exception and return.
      Console.WriteLine("Exception of type {0} occurred.", _
       e.GetType().ToString())
     End Try
    End Sub
    
    

     

    You might want to check the MSDN library for details:

    http://msdn.microsoft.com/en-us/library/system.data.uniqueconstraint.aspx

    http://msdn.microsoft.com/en-us/library/system.data.dataset.enforceconstraints.aspx


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, November 23, 2010 12:45 PM
  • Hello Axel Dahmen,

    Thanks for your clarification and other detail information!

    I think James has suggested a good way to add unique constraint to your table.

    Per the language specified columns, I think you need the CultureInfo class to provider better cultural information. For example, for the Turkish language, you can format string with:
    ===========================================
    CultureInfo turkishlang = new CultureInfo(“tr-TR”);
    String output = String.Format(turkishlang, “{0, -11}-{1, -35:D}-{2:N}”, turkishlang.Name, DateTime.Now, 9123.45);
    // you can compare the output string with user’s input to decide whether throw an exception or not.
    Console.WriteLine(output);
    ===========================================
    You can get a list of culture information here:
    http://authors.aspalliance.com/aspxtreme/sys/globalization/demos/CultureInfo.aspx

    If you are interested in changing and setting collations in sql server, I would recommend you refer to:
    http://msdn.microsoft.com/en-us/library/ms190920.aspx
    or post thread in a sql forum:
    http://social.technet.microsoft.com/Forums/en-us/transactsql/threads

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Wednesday, November 24, 2010 5:42 AM
  • Thanks, guys, for trying to help.

    Unfortunately your answers don't quite hit my point.

    I have already created a DataTable and added a unique constraint to it's columns. But I don't find a way to assign different cultures to each column.

    I am missing a way to assign a culture to a DataColumn object so the correct UNIQUE constraint will be used on that column.

    Currently I assume this kind of functionality is missing in the DataColumn object. If that is the case then one won't be able to use a DataTable for a grid control to fill above SQL Server table reliably.

    Thursday, November 25, 2010 3:19 PM
  • I have created a corresponsing issue at the MS Connect website .

    Please vote.

    Friday, November 26, 2010 12:16 AM