Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Duane Wingett — Tue, 08 Jun 2010 18:35:03 GMT

Today I hit hit this error message while filling a typed DataSet Datatable. The odd thing was I could preview the data in the DataSet designer and the data looked fine.

I had to resort to Google and that returned this page, which if you hit this error, I would definately recommend. Look specifically for Sanjeys post about six posts down.

Roy Osherove's Blog - Dataset Hell

Separate database connections for TableAdapter for individual website user roles

Duane Wingett — Sun, 26 Oct 2008 15:34:07 GMT

It’s been quite a while since I have written anything (yes I really need to finish the last article!), but this morning I came across a problem where I need to provide separate database connections for different website visitors who will be using the same underlying .Net TableAdapters. So I thought I’d write about it.

Background:

I have set up my Sql Server 2005 database to have four roles; DbAdmin, WebAdmin, ProductSeller, ProductBuyer. Each of these roles has been given different levels of read / write / execute permission on the various database objects. It is my intention to let the built in .Net 2.0 Membership and Roles objects handle assigning of roles (mirroring the four database roles) to all of the website visitors.

As far as I know to make use the database roles properly I will need four separate database logins and database users, and hence in my application I will need four different connection strings.

Problem:

The problem is that I could not see any exposed properties of a table adapter to allow the connection to be changed dynamically.

My solution:

My solution is to set up the application to have four connection strings for the four database logins, and create an overridden constructor for each TableAdapter that requires role specific database connections.

Setting up the database

For the purpose of this example I have created the following database logins.

Login	Login Name	Password
Database administrator	MyWesite_DbAdministrator	da1
Website administrator	MyWesite_WebAdministrator	wa1
Product Seller	MyWesite_ProductSeller	ps1
Product Buyer	MyWesite_ProductBuyer	pb1

The database roles I have created are as follows.

Role name	Description
DatabaseAdmin	The role assigned to the database administrator
WebsiteAdmin	The role assigned to the web site administrator
ProductSeller	The role assigned to users who can list a product on the site.
ProductBuyer	The role given to public users of the site

Application Architecture Overview:

The application is written in a layered architecture with the web front end having visibility of the Interfaces layer and the Services layer, and the Services layer having visibility of the Interfaces layer, the Business Entities layer, and the Data Access layer. The data access layer contains all the typed datasets and any other database related objects.

Connection strings:

The four connection strings are entered into the Settings tab of the Properties page of the DataAccess project.

	Name	Type	Scope	Value
>	DatabaseAdminCon…	(Connection…	Application	Data Source=…
	WebsiteAdminConn…	(Connection…	Application	Data Source=…
	ProductSellerConne…	(Connection…	Application	Data Source=…
	ProductBuyerConne…	(Connection…	Application	Data Source=…
*

Creating the connection

As we need to provide a role specific connection to the table adapter we need an object that can create and return a connection.

using System; using System.Data.SqlClient; using ConnectionStrings = MyProject.DataAccess.Properties.Settings; namespace MyProject.DataAccess
{ public class DatabaseConnection
{ ///
<summary> ///
Returns a connection for use with the role name passed in ///
</summary> ///
<param name="roleName"> ///
Represents the name of the role to get connection string for. ///
</param> ///
<returns> ///
Returns a Sql Server connection. ///
</returns> public SqlConnection
GetConnectionForRole(string roleName)
{ SqlConnection connection = null; string connectionString = string.Empty;
connectionString = GetConnectionStringForRole(roleName);
connection = new SqlConnection(connectionString); return connection;
} ///
<summary> ///
Returns a connection string based on the passed in rolename. ///
</summary> ///
<param name="roleName"> ///
Represents the name of the role to get connection string for. ///
</param> ///
<returns></returns> private string GetConnectionStringForRole(string roleName)
{ string connectionString = string.Empty; switch (roleName)
{ case "DatabaseAdmin":
connectionString = ConnectionStrings.Default.DatabaseAdminConnectionString; break; case "WebsiteAdmin":
connectionString = ConnectionStrings.Default.WebsiteAdminConnectionString; break; case "ProductSeller":
connectionString = ConnectionStrings.Default.ProductSellerConnectionString; break; case "ProductBuyer":
connectionString = ConnectionStrings.Default.ProductBuyerConnectionString; break; default: throw new ArgumentOutOfRangeException("roleName", "Unknown
role name encountered."); } return connectionString;
} } }

Default Constructor:

Next we need to look at the TableAdapter and create a constructor that overrides the default non parametricised constructor. The default constructor for our table adapter looks like this.

[System.Diagnostics.DebuggerNonUserCodeAttribute()] public MyTableAdapter()
{ this.ClearBeforeFill = true;
}

New Constructor:

To override this constructor we are going to take advantage of .Net 2.0’s partial classes. Using these we do not have to modify the auto-generated code of the dataset, and neither do we have to attempt to create a class that inherits from the dataset.

using System; using System.Data.SqlClient; namespace MyProject.DataAccess.ApplicationDataSetTableAdapters
{ public partial class MyTableAdapter:
System.ComponentModel.Component { ///
<summary> ///
Used to provide an overridden constructor for the MyTableAdapter ///
allowing a connection to be passed in. ///
</summary> ///
<param name="sqlConnection"></param> [System.Diagnostics.DebuggerNonUserCodeAttribute()] public MyTableAdapter
(SqlConnection sqlConnection) { this.ClearBeforeFill = true; this.Connection = sqlConnection;
} } }

We take care to set the connection via the “Connection” property rather than just setting the _connection fields as the auto-generated "Connection" property ensure all adapter commands get the correct connection assigned to them, see below.

[System.Diagnostics.DebuggerNonUserCodeAttribute()] internal System.Data.SqlClient.SqlConnection
Connection { get { if ((this._connection
== null))
{ this.InitConnection();
} return this._connection;
} set { this._connection = value; if ((this.Adapter.InsertCommand
!= null))
{ this.Adapter.InsertCommand.Connection = value;
} if ((this.Adapter.DeleteCommand
!= null))
{ this.Adapter.DeleteCommand.Connection = value;
} if ((this.Adapter.UpdateCommand
!= null))
{ this.Adapter.UpdateCommand.Connection = value;
} for (int i = 0;
(i < this.CommandCollection.Length);
i = (i + 1))
{ if ((this.CommandCollection[i]
!= null))
{ ((System.Data.SqlClient.SqlCommand)(this.CommandCollection[i])).Connection = value;
} } } }

Initialising the TableAdapter

So now the code for accessing the adapter should look something like this.

///
<summary> ///
Used to add an object that implements the IMyItem interface to the database. ///
</summary> ///
<param name="product"> ///
Represents the object implementing the IMyItem interface to add to the database. ///
</param> void IMyItem.AddToDatabase(IMyItem
product) { ApplicationAdapters.MyTableAdapter myTableAdapter = null;
SqlConnection connection = null; try {
connection = SqlServerConnectionService.GetConnectionForRole(RoleName);
myTableAdapter = new ApplicationAdapters.
MyTableAdapter (connection); MyTableAdapter.Add(product.Id, product.Name); } catch (Exception
ex) { throw ex;
} finally {
SqlServerConnectionService.CloseConnection(connection); connection.Dispose(); myTableAdapter.Dispose();
} }

Connection Handling:

This code still allows the adapter to open and close the connection as necessary, we have just provided the connection we want. This approach has allowed me to ensure that a user logged in as a ProductBuyer should not have the permission to call procedures they shouldn’t even if I end up with a bug in the website code that allows a non authorised user to reach an administration page with add / update / delete functions.

As always your opinions and comments on this subject or the approach taken to overcome the problem are most welcome. I hope some one may find this useful.

Foot Note:

Some error handling in code has been omitted for sake of clarity

{ Duane.Wingett } - Database