Problem while Using UpdateDataSet() method

Topics: Data Access Application Block, Enterprise Library Core
Jan 20, 2009 at 5:45 PM

Hi
I am facing a problem while using SqlDatabase.UpdateDataSet() function of enterprise library 4.0 The dataset that I want to update has two tables. One of them contains few computed columns. First table is being updated without any problem but when it tries to update the second table that contains computed columns it throws exception. If I am doing this wihout using Enterprise library with the help of SqlDataAdapter everything works fine. I want to do this using Enterprise LIbrary. Please suggest me how can I do this.
I am using Sql Server 2005, Enterprise Library 4.0, Visual Studio Team System 2008.

Below is the code that I am using:
-----------------------------------------

Dim connectionString As String = connString
            Dim DbConnection As New SqlDatabase(connectionString)
     Dim updateCommand As DbCommand
     Dim modifiedData As New DataTable
            Dim instance As DbProviderFactory = DbConnection.DbProviderFactory
            Dim cmdBuilder As DbCommandBuilder = instance.CreateCommandBuilder()
            modifiedData = dtTable.GetChanges()
            cmdBuilder.DataAdapter = DbConnection.GetDataAdapter()
            cmdBuilder.DataAdapter.SelectCommand = DbConnection.GetSqlStringCommand("SELECT * FROM CUSTOMER")
            cmdBuilder.DataAdapter.SelectCommand.Connection = DbConnection.CreateConnection()
            cmdBuilder.ConflictOption = ConflictOption.OverwriteChanges

            updateCommand = cmdBuilder.GetUpdateCommand()

            If modifiedData.Rows.Count > 0 Then
                Dim descriptior As Integer = DbConnection.UpdateDataSet(dst, modifiedData.TableName, Nothing, updateCommand, _
                                    Nothing, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard, modifiedData.Rows.Count)
            End If

 

Thanks

Jan 21, 2009 at 4:08 AM
Edited Jan 21, 2009 at 7:14 AM
I'm currently trying to repro your case but could you be more specific what error you're encountering?  And is this the actual code which throws the exception or the one which works?  Cause some of the method calls don't belong to the right object, I'm a bit confused. 


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 21, 2009 at 9:04 AM

Hi Sarah,

Error is :
The column "FULLNAME" cannot be modified because it is either a computed column or is the result of a UNION operator. The column "AGE" cannot be modified because it is either a computed column or is the result of a UNION operator. The column "DAYOFBIRTHDAY" cannot be modified because it is either a computed column or is the result of a UNION operator. The column "MONTHOFBIRTHDAY" cannot be modified because it is either a computed column or is the result of a UNION operator. The column "LENGTHOFSERVICE" cannot be modified because it is either a computed column or is the result of a UNION operator.

There are two tables into the DataSet namely "CUSTOMER" and "PEOPLE". CUSTOMER table is being updated successfully. This error occurs while updating the PEOPLE table because it contains few computed columns. Below is the actual code that I am using :

 

Imports

 

System.Data.Common
Imports System.Data.SqlClient
Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql

 

 

Public Class UpdateDataSet

 

 

'dstData contains two tables namely "CUSTOMER" AND "PEOPLE". These two tables have the data to be updated into the database.
'PEOPLE table has few computed columns.
Public Sub UpdateDataSet(ByVal dstData As DataSet)
    Dim connectionString As String = "Data Source=01HW189209\SQLEXPRESS;Initial Catalog=CUSTOMER_DATABASE;User ID=AdminUser;Password=password123"

    'Create an object of SqlDatabase class.
    Dim ObjDatabase As New SqlDatabase(connectionString)

    'Create an object of DataTable Class.
    Dim modifiedData As New DataTable

 

 

    'Create an instance of DbProviderFactory class which will be used to create an obejct of DbCommandBuilder.
    Dim instance As DbProviderFactory = ObjDatabase.DbProviderFactory

 

 

    'Create an object of DbCommandBuilder to create update query.
    Dim cmdBuilder As DbCommandBuilder = instance.CreateCommandBuilder()

    Dim
i As Integer = 0
    Dim updateCommand As DbCommand

 

 

    'This array contains the name of tables that are to be updated. This array will be used in the below For loop to generate SELECT query.
    Dim arr(2) As String
    arr(0) = "ABSENCE"    
    arr(1) = "PEOPLE"

    'Loop through each table of DataSet to update into database.
    For Each dtTable As DataTable In dstData.Tables

        'Get the changed data only from the first datatable i.e. "CUSTOMER" table and store it into a datatable.
        modifiedData = dtTable.GetChanges()
        cmdBuilder.DataAdapter = ObjDatabase.GetDataAdapter()
        cmdBuilder.DataAdapter.SelectCommand = ObjDatabase.GetSqlStringCommand(
"SELECT * FROM " & arr(i))
        cmdBuilder.DataAdapter.SelectCommand.Connection = ObjDatabase.CreateConnection()

 

 

        'Get the update command that is to be passed into the UpdateDataSet() method.
        updateCommand = cmdBuilder.GetUpdateCommand()

 

 

        'If there is any data to be updated then call the UpdateDataSet() method.
        If modifiedData.Rows.Count > 0 Then
            Dim descriptior As Integer = ObjDatabase.UpdateDataSet(dstData, modifiedData.TableName, Nothing, updateCommand, _
                                                            Nothing, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard, modifiedData.Rows.Count)
        End If
        cmdBuilder.RefreshSchema()
        i = i + 1
    Next

End
Sub
End Class

 

Jan 21, 2009 at 11:13 AM
Edited Jan 21, 2009 at 11:28 AM
I still haven't repro your error.  So, you didn't actually modified anything in the datatable? or you just didn't include it in the code you posted? Was that datatable something you manually created or is a result of a select query from a database?  I'm asking cause I can only see that you passed the DataSet object from somewhere so I don't know if you made any changes there prior to calling that method and it would help to know exactly what your code does so I can repro it.  One more thing, is your updatecommand a stored proc or a sql statement you just created in your code?  Your update command might be trying to set the value of the computed columns.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 21, 2009 at 11:44 AM
First I load a dataset with these two tables that I have mentioned earlier by calling LoadDataSet(dbcommand, DataSet, tableName) method of Enterprise Library. After that I made few changes using the code below 

dstData.Tables(0).Rows(0)(

"CREATEDBY") = "BR" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(0).Rows(1)(

"CREATEDBY") = "BRI" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(0).Rows(3)(

"CREATEDBY") = "BRIJ" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(0).Rows(4)(

"CREATEDBY") = "BRIJE" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(1).Rows(0)(

"CREATEDBY") = "BRIJES" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(1).Rows(0)(

"REFERENCENUMBER") = "239896" 'DataType in Database is (nvarchar(10))

 

dstData.Tables(1).Rows(1)(

"CREATEDBY") = "SUN" 'DataType in Database is (nvarchar(20))

 

dstData.Tables(1).Rows(1)(

"REFERENCENUMBER") = "190223" 'DataType in Database is (nvarchar(10))

 


After making these changes I call the function UpdateDataSet(ByVal dstData As DataSet)  by passing this changed dataset as a parmeter.
For creating UpdateCommand I am using DbCommandBuilder class which takes a SELECT statement as SelectCommand and creates UpdateCommand dynamically with respect to that SELECT command. As you can see in above code:
        cmdBuilder.DataAdapter.SelectCommand = ObjDatabase.GetSqlStringCommand("SELECT * FROM " & arr(i))
        cmdBuilder.DataAdapter.SelectCommand.Connection = ObjDatabase.CreateConnection() 

        'Get the update command that is to be passed into the UpdateDataSet() method.
        updateCommand = cmdBuilder.GetUpdateCommand()
Jan 21, 2009 at 11:56 AM
I see, the generated update command also includes set statements for the computed columns.  That's what's causing the error.  Try creating your own update command.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 21, 2009 at 1:15 PM
I have made this demo project only for two tables. But it will be implemented on around 100 tables and its very hard to create update command for so many tables. So I am using DbCommandBuilder Class. I think there is not problem in UpdateCommand created by CommandBuilder because the same code woks fine if I use Update() method of SqlDataAdapter class. It means there is some problem in UpdateDataSet() method of Enterprise Library. I thought there must be some workaround for this in Enterprise Library.
Jan 21, 2009 at 3:12 PM
Ok, I was now able to repro the error.  If I change my updatecommand to remove the set statement for the computed column, it works as expected.  I then tried using the SqlDataAdapter.Update method using the same updatecommand generated from the DbCommandBuilder and got the same error.  Are you sure you used the same updatecommand for both approach?


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 21, 2009 at 4:29 PM

Please replace the For loop with the below For loop in the code I have posted earlier :
'Loop through each table of DataSet to update into database.
For Each dtTable As DataTable In dstData.Tables
        'Get the changed data only from the first datatable i.e. "CUSTOMER" table and store it into a datatable.
        modifiedData = dtTable.GetChanges()

        cmdBuilder.DataAdapter = ObjDatabase.GetDataAdapter()
        cmdBuilder.DataAdapter.SelectCommand = ObjDatabase.GetSqlStringCommand(
"SELECT * FROM " & arr(i))
        cmdBuilder.DataAdapter.SelectCommand.Connection = ObjDatabase.CreateConnection()

        'Get the update command that is to be passed into the UpdateDataSet() method.
        updateCommand = cmdBuilder.GetUpdateCommand()

        'If there is any data to be updated then call the UpdateDataSet() method.
        If modifiedData.Rows.Count > 0 Then
                cmdBuilder.DataAdapter.Update(dstData, dtTable.TableName)
        End If
        cmdBuilder.RefreshSchema()
        i = i + 1
Next

 

 

 

I have used DbDataAdapter here instead of SqlDataAdapter. However I have checked it for SqlDataAdapter as well and in both conditions it worked fine. But as soon as I changed the DataAdapter's Update() method to UpdateDataSet() method of Enterprise Library it burst out. For your assistance I am sending you the create script of PEOPLE table for which I am getting error. You can run this script on you Sql Server Database to create the PEOPLE table, fill this table with some dummy data and run the code for this table. 

 

SET

 

ANSI_NULLS ON

 

 

 

GO

 

 

SET QUOTED_IDENTIFIER ON

 

 

 

GO

 

 

SET ANSI_PADDING ON

 

 

 

GO

 

 

CREATE TABLE [dbo].[PEOPLE](

 

 

 

[PEOPLE_ID] [uniqueidentifier] NOT NULL,

 

 

 

[CREATEDBY] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,

 

 

 

[REFERENCENUMBER] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,

 

 

 

[LASTNAME] [nvarchar](30) COLLATE Latin1_General_CI_AS NULL,

 

 

 

[FIRSTNAME] [nvarchar](30) COLLATE Latin1_General_CI_AS NULL,

 

 

 

[FULLNAME] AS ([FIRSTNAME] + ' ' + [LASTNAME]),

 

 

 

[DATEOFBIRTH] [datetime] NULL,

 

 

 

[AGE] AS (datediff(month,[DATEOFBIRTH],getdate())),

 

 

 

[DAYOFBIRTHDAY] AS (datepart(day,[DATEOFBIRTH])),

 

 

 

[MONTHOFBIRTHDAY] AS (datepart(month,[DATEOFBIRTH])),

 

 

 

CONSTRAINT [PK_PEOPLE] PRIMARY KEY CLUSTERED

 

 

(

 

 

 

[PEOPLE_ID] ASC

 

 

 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

 

 

 

CONSTRAINT [IX_PEOPLE_UNIQUE] UNIQUE NONCLUSTERED

 

 

(

 

 

 

[REFERENCENUMBER] ASC

 

 

 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

 

 

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

 

Jan 22, 2009 at 1:24 PM
Hi,

You can try specifying the columns(dont include the computed columns) for the selectcommand, since the generated UpdateCommand, DeleteCommand and InsertCommand are dependent on the SelectCommand.

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 22, 2009 at 1:49 PM
Yes...it may be a solution. But I want to use "SELECT * " because I have around 100 tables and each table have around 50 columns. Apart from this I don't know how many tables have computed columns and how many have not. According to your suggestion, first I have to find all the computed columns in each table and then create SELECT command. Because of all these problems I want to use Command builder. Your suggestion is last resort for me.
Only thing that makes me curious is that, If same code can run for Update() method of DataAdapter than why it is not running for UpdateDataSet() method of Enterprise Library. This means we can not update tables having computed columns using Command Builder and Enterprise Library.
Jan 23, 2009 at 12:13 PM
I was able to repro your error.  It's weird, I already tried to copy all of the codes from entlib which are different compared to the one which uses the SqlDataAdapter.  I copied how it created its commandbuilder, dataadapter, etc.  I'm considering that it may be a ADO.NET (since the entlib code still didn't work when I copied it) but still, I can't be sure.  The only remaining workaround I can see, (aside from the ones previously mentioned before),  is to set the UpdateBehavior to Continue, but this is not ideal of course.  I'll try to verify this with the entlib team. 


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 28, 2009 at 7:34 AM
I have also tried setting UpdateBehavior to Continue but it is not the solution as it leaves all the computed columns un updated. By the way talking to microsoft expertise is a good idea. If you a get any solution please let me know.

Thanks
Jan 29, 2009 at 10:20 AM
Sure, I will.  I'm gonna have to verify this with the EntLib team first.

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 10, 2009 at 3:30 AM
I've logged an issue for this - http://www.codeplex.com/entlib/WorkItem/View.aspx?WorkItemId=21065


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com