Updating database view using SqlDataAdapter

Question

In my application, I want to bind GridControl to DataTable that contains data from multiple database tables (referenced with foreign keys).

I had a problem with updating data, since I was using SqlDataAdapter on a command that referenced multiple database tables. I was getting error:

Dynamic sql generation is not supported against multiple base tables

I tried to work it around by creating a view that was combining multiple tables into one "table" that I could then bind to my GridControl.

Inserting data was done using "instead of" trigger on the view in which I was adding appropriate data to appropriate tables. In plain t-sql, inserts work flawlessly. I can insert data into my view and it is being inserted into appropriate table. My SqlDataAdapter should have no problems updating since it's updating one "table" and the rest of updates is being done by triggers.

Unfortunately, I am still getting error

Dynamic sql generation is not supported against multiple base tables

Do you have any idea why SqlDataAdapter is still not letting me update, even thought it's now updating only one "table" - view?

My code:

The view:

CREATE view [dbo].[v_TestTypeParameter_grid]
as
(
select t1.Id, t3.Name, t3.Description, t2.MinValue, t2.MaxValue, t4.Symbol 
from TestTypes as t1 join 
TestTypeParameters as t2 on t1.Id = t2.TestType join 
Parameters as t3 on t2.Parameter = t3.Id left join 
Units as t4 on t2.Unit = t4.Id
)

Instead of trigger on view:

CREATE TRIGGER [dbo].[tr_TestTypeParameterAdded] ON [dbo] [v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitSymbol = Symbol FROM Inserted;
    SELECT @ParameterId = Id FROM Parameters WHERE Name = @ParameterName;
    SELECT @UnitId = Id from Units WHERESymbol = @UnitSymbol;

    INSERT INTO TestTypeParameters(TestType, Parameter, MinValue, MaxValue, Unit) VALUES(@TestTypeId, @ParameterId, @MinValue, @MaxValue, @UnitId);
END

My binding code:

mvarParameterListAdapter = DBService.GetDataAdapter("select * from v_TestTypeParameter_grid where Id  = " + mvarTestTypeId);

mvarParameterTable = new DataTable();
mvarParameterListAdapter.Fill(mvarParameterTable);
gcParameters.DataSource = mvarParameterTable;
gcParameters.RefreshDataSource();

DBService.GetDataAdapter method:

public static SqlDataAdapter GetDataAdapter(String command, DataTable parameters = null)
    {
        SqlConnection lvarConnection = GetConnection();

        SqlCommand lvarCommand = new SqlCommand(command, lvarConnection);
        if (parameters != null && parameters.Rows.Count > 0)
        {
            foreach (DataRow lvarRow in parameters.Rows)
            {
                lvarCommand.Parameters.AddWithValue("@" + lvarRow[0], lvarRow[1]);
            }
        }

        SqlDataAdapter lvarAdapter = new SqlDataAdapter(lvarCommand);
        SqlCommandBuilder lvarCommandBuilder = new SqlCommandBuilder(lvarAdapter);
        return lvarAdapter;
    }

Update code:

try
{
    mvarParameterListAdapter.Update(mvarParameterTable);
}
catch (Exception ex)
{
    // Here, I'm getting the error
}

Insert that works in t-sql:

INSERT INTO v_TestTypeParameter_grid VALUES (2, 'Fe', 'Iron', 5.2, 7.9, '%')

Since I have several GridControls that work like this (with different data), I am trying to not generate select, insert, update and delete commands for SqlDataAdapter myself, instead of that, I'm using SqlCommandBuilder to keep it simpler.

Thanks!

EDIT:

To (hopefully) make things clear:

This is the GridControl I am working on. Forgive me for the column names, they're in Polish. Identyfikator = Id, Nazwa = Name, Opis = Description, Minimum = MinValue, Maksimum = MaxValue, Jednostka = Unit

For now, I am adding another row to GridControl that only contains the "Nazwa (Name)" and "Opis (Description)" fields.

I am filling "Identyfikator(Id)" field with the Id of the TestType that I want to add parameters to and other fields with their default values. The new row is automatically being added to the DataTable that is bound to my GridControl and after user clicks "Save", I want to push those changes to the database using SqlDataAdapter.Update() method. This is where I am getting error:

Dynamic sql generation is not supported against multiple base tables

I hope it made it clear what I want to achieve and how I'm trying to do it.

EDIT:

I managed to find a solution to this problem. You can check it in my answer below.

Answer 1

I finally managed to finish that part of the project. I also found a solution to my problem.

First, I created a view that mimics my GridControl. It looks something like this:

CREATE VIEW [dbo].[v_TestTypeParameter_grid]
AS
SELECT t1.Id, t3.Name, t3.Description, t2.MinValue, t2.MaxValue, t2.Unit as Unit, 
t3.Id AS ParameterId
FROM            
dbo.TestTypes AS t1 INNER JOIN
    dbo.TestTypeParameters AS t2 ON t1.Id = t2.TestType INNER JOIN
    dbo.Parameters AS t3 ON t2.Parameter = t3.Id

It's important that the view contains Ids that make rows in my destination table unique (in this case: TestTypeId and ParameterId).

Then, because SQL still wouldn't allow me to insert, update or delete rows because of the

Dynamic sql generation is not supported against multiple base tables

error.

I skipped it by creating INSTEAD OF triggers on my view (one for insert, update and delete).

My triggers looked like this (yes, I know they should be set based):

Insert:

CREATE TRIGGER [dbo].[tr_TestTypeParameterAdded] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit from Inserted;
    SELECT @ParameterId = Id from Parameters where Name = @ParameterName;

    insert into TestTypeParameters(TestType, Parameter, MinValue, MaxValue, Unit) values (@TestTypeId, @ParameterId, @MinValue, @MaxValue, @UnitId);
END

Update:

CREATE TRIGGER [dbo].[tr_TestTypeParameterUpdated] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF UPDATE
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2)

    select * from inserted
    SELECT @TestTypeId = Id, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit, @ParameterId = ParameterId from Inserted;

    update TestTypeParameters set MinValue = @MinValue, MaxValue = @MaxValue, Unit = @UnitId where TestType = @TestTypeId and Parameter = @ParameterId;
END

Delete:

CREATE TRIGGER [dbo].[tr_TestTypeParameterDeleted] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF DELETE
AS
BEGIN
    DECLARE 
    @TestTypeId int,
    @ParameterId int,
    @UnitId int,
    @ParameterName varchar(100),
    @MinValue decimal(18,2),
    @MaxValue decimal(18,2),
    @UnitSymbol varchar(100)

    SELECT @TestTypeId = Id, @ParameterName = Name, @MinValue = MinValue, @MaxValue = MaxValue, @UnitId = Unit, @ParameterId = ParameterId from deleted;

    delete from TestTypeParameters where TestType = @TestTypeId and Parameter = @ParameterId;
END

I also had to change the way I create SqlDataAdapters:

SqlCommand lvarInsert =
    new SqlCommand(
        "insert into v_TestTypeParameter_grid values (@Id, @Name, @Description, @MinValue, @MaxValue, @Unit, @ParameterId)", DBService.Connection);
mvarParameterListAdapter.InsertCommand = lvarInsert;   
lvarInsert.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarInsert.Parameters.Add("@Name", SqlDbType.NVarChar, 5, "Name");
lvarInsert.Parameters.Add("@Description", SqlDbType.NVarChar, 5, "Description");
lvarInsert.Parameters.Add("@MinValue", SqlDbType.Decimal, 5, "MinValue");
lvarInsert.Parameters.Add("@MaxValue", SqlDbType.Decimal, 5, "MaxValue");
lvarInsert.Parameters.Add("@Unit", SqlDbType.Int, 5, "Unit");
lvarInsert.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");

SqlCommand lvarSelect = new SqlCommand("select * from v_TestTypeParameter_grid where Id = " + mvarTestTypeId, DBService.Connection);
mvarParameterListAdapter.SelectCommand = lvarSelect;

SqlCommand lvarUpdate =
new SqlCommand(
"update v_TestTypeParameter_grid set Id = @Id, ParameterId = @ParameterId, Name = @Name, Description = @Description, MinValue = @MinValue, MaxValue = @MaxValue, Unit = @Unit where Id = @Id and ParameterId = @ParameterId", DBService.Connection);
mvarParameterListAdapter.UpdateCommand = lvarUpdate; 
lvarUpdate.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarUpdate.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");
lvarUpdate.Parameters.Add("@Name", SqlDbType.NVarChar, 5, "Name");
lvarUpdate.Parameters.Add("@Description", SqlDbType.NVarChar, 5, "Description");
lvarUpdate.Parameters.Add("@MinValue", SqlDbType.Decimal, 5, "MinValue");
lvarUpdate.Parameters.Add("@MaxValue", SqlDbType.Decimal, 5, "MaxValue");
lvarUpdate.Parameters.Add("@Unit", SqlDbType.Int, 5, "Unit");

SqlCommand lvarDelete =new SqlCommand(
"delete from v_TestTypeParameter_grid where Id = @Id and ParameterId = @ParameterId", DBService.Connection);
mvarParameterListAdapter.DeleteCommand = lvarDelete;   
lvarDelete.Parameters.Add("@Id", SqlDbType.Int, 5, "Id");
lvarDelete.Parameters.Add("@ParameterId", SqlDbType.Int, 5, "ParameterId");

I'm nearly sure that it could be done in a different (better) way, but I want to share it since it took me some time to fix it and I couldn't find any solution online.

If you have any idea on how to make it better, feel free to comment.

Enjoy!