Update with ExecuteNonQuery Enterprise Lib 5

Apr 27, 2012 at 10:36 PM

Hi,

I am using Enterprise lib 5, The Problem I am facing in my DataAccess layer Get, Insert functions are running fine. Problem is with the Update Function by using ExecuteNonQuery command is not working not even giving me any error or exception. i.e. Execute properly but there is no change in DataBase occur. Please find my Code below.

 

 public static void Update(ApplicationObjects _data, string _db_name)
        {

            //Database _db = new SqlDatabase(_db_name);
            //Database _db = DatabaseFactory.CreateDatabase(_db_name);
            Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
            string sql = "[dbo].[usp_Application_update]";
            DbCommand _cmd = _db.GetStoredProcCommand(sql);

            _db.AddInParameter(_cmd, "@prm_app_id", DbType.Int32, _data._AppID);
            _db.AddInParameter(_cmd, "@prm_app_cat_id", DbType.Int32, _data._AppCategoryID);
             _db.AddInParameter(_cmd, "@prm_app_locale_id", DbType.Int32, _data._LocaleID);
             _db.AddInParameter(_cmd, "@prm_app_name", DbType.String, _data._Name);
             _db.AddInParameter(_cmd, "@prm_app_isDefault", DbType.Boolean, _data._IsDefault);
            _db.AddInParameter(_cmd, "@prm_app_parent_name", DbType.String, _data._ParentName);
            _db.AddInParameter(_cmd, "@prm_app_Website", DbType.String, _data._Website);
            _db.AddInParameter(_cmd, "@prm_app_DataSource", DbType.String, _data._DataSource);

           
            using (DbConnection conn = _db.CreateConnection())
            {
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    _db.ExecuteNonQuery(_cmd, trans);
                    trans.Commit();
                }
                catch
                {
                    // Roll back the transaction. 
                    trans.Rollback();
                }
                conn.Close();
            }
        }

Waiting for your replies.

 

Thank You!

Apr 30, 2012 at 4:39 AM
Edited Apr 30, 2012 at 7:36 AM

Are you sure that the stored procedure is executing successfully?  If an exception is thrown, the code is rolling back the transaction and swallowing the exception so the update will appear to succeed.  Except that no data will actually be updated in the database.  Typically a rethrow (i.e. "throw;") would appear after the Rollback call.

On an unrelated note: since you have the DbConnection wrapped in a using statement there is no need to explicitly call conn.Close() since at the end of the using block Dispose() will be called on the DbConnection object which is functionally equivalent to Close().

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 30, 2012 at 3:05 PM

Hi Randy,

Thank you so much for your reply. I checked my stored procedure twice before opening the discussion, This morning I test the whole process again with some simple stuff but no luck, Please find my code along with stored procedure below.

public static void Update(ApplicationObjects _data, string _db_name)
        {

            //Database _db = DatabaseFactory.CreateDatabase(_db_name);
            Database _db = new SqlDatabase(_db_name);
            string _sql = "[dbo].[usp_App_update_Test]";
            DbCommand _cmd = _db.GetStoredProcCommand(_sql);
            _db.AddInParameter(_cmd, "@prm_app_id", DbType.Int32, _data._AppID);
            _db.AddInParameter(_cmd, "@prm_app_name", DbType.String, _data._Name);
            _db.ExecuteNonQuery(_cmd);
        }

And my stored Procedure is below.

CREATE PROCEDURE [dbo].[usp_App_update_test]
	@prm_app_id [int] ,
	@prm_app_name [nvarchar](1000) = null


AS
BEGIN

	declare @sql nvarchar(max)
	set @sql = '
    update
		[dbo].[tblApplication]
	set '
	
	
	if (@prm_app_name is not null)
		set @sql = @sql + '[Name] = @xprm_app_name,'
		
	set @sql = @sql + ' 	
		[CreateDate] = getdate()
	where
		[AppID] = @xprm_app_id
'
		
	declare @paramlist nvarchar(max)
	set @paramlist = '
	@xprm_app_id [int],
	@xprm_app_name [nvarchar](1000)
	'
		
	exec sp_executesql @sql, @paramlist,
	@prm_app_id,
	@prm_app_name
END

The Stored Procedure is runinng fine and Update function never update the Table though it runs fine without any exception or errors.

Thank you

 

Apr 30, 2012 at 6:23 PM

What are you passing in for _db_name?  Is it a connection string or the database name?  CreateDatabase takes the database name while SqlDatabase takes a connection string.

Everything looks good to me.  Could it be the program is connecting to a different database instance than expected so the update is happening in another database (local vs. dev, etc.)?

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 30, 2012 at 6:29 PM

Thank you for your reply, I am passing the ConnectionString i.e.

connectionString="Data Source=server;Initial Catalog=Name;User ID=copernicus;Password=pass;Connection Timeout=100"
in _db_name parameter, Yes I already verify it twice about the connectionstring credential? Any Idea?

Apr 30, 2012 at 6:31 PM

Just for your information, Insert and Get Methods working properly Only the Update part is not working :(.

May 1, 2012 at 4:21 AM

As I mentioned, your code is working fine for me so it shouldn't be a problem with the code.

The behavior you are seeing is that the code runs successfully without an exception but the record is not updated.

So what could cause this behavior?  One thing it could be is if the "wrong" database was being updated (I suggested this above) but that wasn't it.  Another way to see the observed behavior is if the appID was not in the database.  Then the update would succeed but nothing would be updated in the table.

If that doesn't help then I would suggest running SQL Server Profiler to see exactly what is being executed in the database.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com