How to work with autoincrement keys and SqlDataAdapters to add new rows do database

Question

So, i have this in-house data access framework that i have to maintain and i'm to add Transactions to it. I am having problems putting in one package: Transactions, SqlDataAdapters, SqlCommandBuilders and tables with primary keys defined as auto-increments.

Here is what i have so far

 public int PutEntity(DaoContext ctx, bool useTransaction=false)
        {
            int returnValue=ErrorCodes.ERR_SUCCESS;            
            {
                try
                {   
                    using (ctx.Connection = DBUtil.GetSqlConnection(ctx.AppCode, this.DBName))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter(BuildSelect(), ctx.Connection);
                        adapter.FillSchema(ctx.Data.Tables[MainTableName], SchemaType.Source);
                        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                        SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(adapter);

                        if (!useTransaction & ctx.Transaction == null) {
                            sqlBuilder.GetUpdateCommand();
                            ctx.Transaction = ctx.Connection.BeginTransaction();
                            IsTransactionOwner = true;
                        }

                        sqlBuilder.GetInsertCommand().Transaction = ctx.Transaction;
                        sqlBuilder.GetUpdateCommand().Transaction = ctx.Transaction;
                        sqlBuilder.GetDeleteCommand().Transaction = ctx.Transaction;

                        adapter.Update(ctx.Data, MainTableName);

                        ctx.ErrorCode = ErrorCodes.ERR_SUCCESS;

                        if (!useTransaction & IsTransactionOwner)
                        {
                            ctx.Transaction.Commit();                    
                        }
                        return ctx.ErrorCode; //-->                
                    }
                }
                catch (Exception ex)
                {
                    if (!useTransaction & IsTransactionOwner)
                    {
                        ctx.Transaction.Rollback();                    
                    }
                    ex = ErrorHandler.OnError("Error saving data. Record changed while in edit.", ex,
                            ErrorCodes.ErrorConsequence.None,
                            ErrorCodes.ERR_DBEXE_PUT_CONCURRENCY,
                            Thread.CurrentThread.ManagedThreadId,
                            "application : " + ctx.AppCode,
                            "on database: " + this.DBName,
                            ErrorCodes.ERR_DBEXE_PUT_CONCURRENCY_MSG,
                            "Frw.Common.BizEntity",
                            "PutEntity()"
                            );
                }
                //finally{
                //    if (!useTransaction & IsTransactionOwner)
                //    {
                //        ctx.Transaction.Dispose();
                //        ctx.Transaction=null;
                //    }
                //}    
            }            
        }

my problem is that when i try to insert data it never gets inserted and when i inspect the datasets they never have the new autoincrement keys. The new record i add is always at 1

Answer 1

I found the solution to the problem. It involved extracting the SqlCommands from the SqlCommandBuilder and altering them, something in the lines of

                SqlDataAdapter daAutoNum = new SqlDataAdapter();

                using (ctx.Connection = DBUtil.GetSqlConnection(ctx.AppCode, this.DBName))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(BuildSelect(), ctx.Connection);
                    SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(adapter);
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;


                    adapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
                    adapter.InsertCommand = sqlBuilder.GetInsertCommand();

                    string scope_id = string.Format(" ; select {0} from {1} where {0} = SCOPE_IDENTITY();", PrimaryKeyName, MainTableName);
                    adapter.InsertCommand.CommandText += scope_id;

                    adapter.DeleteCommand = sqlBuilder.GetDeleteCommand();

                    SqlParameter identParam = new SqlParameter("@Identity", PrimaryKeyType, 0, PrimaryKeyName);
                    identParam.Direction = ParameterDirection.Output;
                    adapter.InsertCommand.Parameters.Add(identParam);
                    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

                    daAutoNum.DeleteCommand = adapter.DeleteCommand;
                    daAutoNum.InsertCommand = adapter.InsertCommand;
                    daAutoNum.UpdateCommand = adapter.UpdateCommand;

                    daAutoNum.InsertCommand.Transaction = ctx.Transaction;
                    daAutoNum.DeleteCommand.Transaction = ctx.Transaction;
                    daAutoNum.UpdateCommand.Transaction = ctx.Transaction;

                    daAutoNum.Update(ctx.Data, MainTableName);

                }