All geek questions in one place

Updating a Database View Using SqlDataAdapter

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

I had a problem updating the data since I used the SqlDataAdapter for a command that references multiple database tables. I was getting the error:

Dynamic sql building is not supported for multiple base tables

I tried to process it by creating a view that combines several tables into one “table”, which I could bind to my GridControl.

The data was inserted using the “instead of” trigger in the view, in which I added the corresponding data to the corresponding tables. In regular t-sql, inserts work flawlessly. I can paste the data into my view and paste it into the corresponding table. My SqlDataAdapter should not update the problems, since it updates the "table", and the rest of the updates are performed by triggers.

Sorry, I'm still getting an error

Dynamic sql building is not supported for multiple base tables

Do you have an idea why SqlDataAdapter still doesn’t allow me to update, even thought that now it only updates one “table” - view?

My code is:

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 triggering a trigger:

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
}

Paste what works in t-sql:

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

GridControls, ( ), , , SqlDataAdapter , SqlCommandBuilder, .

!

EDIT:

() :

gcParameters

GridControl, . , .   Identyfikator = Id,    = ,   Opis = ,   Minimum = MinValue,   Maksimum = MaxValue,   Jednostka =

GridControl, "Nazwa (Name)" "Opis (Description)".

Identyfikator (Id) TestType, . DataTable, GridControl, , "", SqlDataAdapter.Update(). :

sql

, , .

EDIT:

. .

+4
1

- . .

, GridControl. :

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

, , ( : TestTypeId ParameterId).

, SQL - , -

sql

.

, INSTEAD OF ( , ).

(, , ):

:

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


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

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");

, (), , , , - .

, , .

!

0

Source: https://habr.com/ru/post/1658829/

More articles:


All Articles