CODEWITHSUNDEEP
c#asp.netdatatableconstraints
nirav
nirav

Reputation: 373

UNIQUE constraint on DataTable

Can I have a clustered key on DataTable in C# ?

There's a requirement in my code to have constraints for possible combinations of 3 columns to be unique .....

Upvotes: 4

Views: 17804

Answers (2)

yogi
yogi

Reputation: 19591

To make your columns enforce a UNIQUE constraint you could use

DataTable dt = new DataTable();
dt.Columns.Add("UniqueColumn");
dt.Columns["UniqueColumn"].Unique = true;

Solution two

If you want some combination of the values in some columns to have unique value, you can try this.

DataTable dt = new DataTable();
dt.Columns.Add("UniqueColumn1");
dt.Columns.Add("UniqueColumn2");
dt.Columns.Add("UniqueColumn3");
dt.Columns.Add("NormalColumn");

string 
       value1 = string.Empty, 
       value2 = string.Empty, 
       value3 = string.Empty, 
       value4 = string.Empty;

//Logic to take values in string values variables goes here

DataRow[] founded = dt.Select("UniqueColumn1 = '"+ value1+
                              "' and UniqueColumn2 = '"+value2+
                              "' and UniqueColumn3 = '"+value3+"'");
if (founded.Length > 0)
      // Message to say values already exist.
else
      // Add a new row to your dt.

In this code you check the data present in DT to enforce uniqueness

Upvotes: 1

marc_s
marc_s

Reputation: 754268

What you need is really a unique constraint on your DataTable. Clustered keys are a SQL Server on-disk feature and not applicable to a DataTable.

Check out MSDN doc on DataTable constraints:

The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor.

So try something like this:

// this is your DataTable
DataTable custTable ;

// create a UniqueConstraint instance and set its columns that should make up
// that uniqueness constraint - in your case, that would be a set of *three*
// columns, obviously! Adapt to your needs!
UniqueConstraint custUnique = 
   new UniqueConstraint(new DataColumn[] { custTable.Columns["CustomerID"], 
                                           custTable.Columns["CompanyName"] });

// add unique constraint to the list of constraints for your DataTable
custTable.Constraints.Add(custUnique);

And that should do the trick for you!

Upvotes: 7

Related Questions