Update the DataSet with the Database

Topics: Data Access Application Block
Jun 7, 2012 at 8:36 AM

Hello All,

My task is as follows:

I have a table called "Employees" in the Database. And the requirement is I want to Insert, Update a few rows in this table.(No deletes)

I want this thing to happen automatically without writing any StoredProc or Insert/Update command. For this, I found a way that CommandBuilders can do this job. After doing a bit of research, I came to know that they get the Input dataset with select * from tableName, Perform whatever changes to this database and then call the Update method which will merge this changes to the database.

But, in my case, I am not doing select * at the beginning. Here, the users are just passing the dataRows directly for which the change has to happen in a dataSet. For example, this dataset will have only 2 rows; 1 for Insert and other for Update (as opposed to select *). And now I want too merge this dataSet with the actual database Employees table.

How to achive this with UpdateDataSet()? Any help is greatly appreciated.

Thanks in advance,

Jun 7, 2012 at 9:19 AM

You should be able to perform a select * from tableName to get the current records from the table.  Then you can perform a Merge of the DataRows with the DataSet.  If you don't have a primary key then you may have to perform a manual merge as in this example: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/53176903-35ef-4718-a57a-85626bcfbbf4/ .

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

Jun 7, 2012 at 10:36 AM

Thanks for your reply.

But, in the referred link, I do not any guideline for updating the dataset to the DataBase. Could you pls help on these lines?

Unlike the general practice that I hv seen is that everyone is getting the source dataset using select * and then they perform Insert/Update on this and at last Merge this dataSet with the Database. But, in my case scenario is different in a way that I am just getting the rows ready made which are to be Updated/Inserted And then I want these rows to be merged with that of database.

thanks,

Amit

Jun 8, 2012 at 7:54 AM

I'm not sure if the Merge will work; it appears that the RowState values are not maintained as you would want for your scenario (e.g. a "new" DataRow which matches an existing row seemed to be treated as a new row instead of matching the existing row.  That could have been my error though.).

Probably the easiest/quickest (but probably not the most elegant) would be to just do a brute force select from the database, check for differences and then update the existing dataset and then update the database.

This code should get you started:

    static void Do()
    {
        DataRow[] rows = GetDataRows();

        var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
        var ds = db.ExecuteDataSet(System.Data.CommandType.Text, "Select * from Employees");

        foreach (var row in rows)
        {
            // No Emp ID so new record
            if (row["EmployeeId"] == DBNull.Value)
            {
                ds.Tables[0].ImportRow(row);
            }
            else
            {
                // Get the existing row
                DataRow oldRow = ds.Tables[0].Select("EmployeeId = " + row["EmployeeId"]).First();
                oldRow.ItemArray = row.ItemArray;
            }
        }

        DbCommand updateCommand = db.GetSqlStringCommand("UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Department = @Department WHERE EmployeeId = @EmployeeId");

        db.AddInParameter(updateCommand, "@FirstName", DbType.String, "FirstName", DataRowVersion.Current);
        db.AddInParameter(updateCommand, "@LastName", DbType.String, "LastName", DataRowVersion.Current);
        db.AddInParameter(updateCommand, "@Department", DbType.String, "Department", DataRowVersion.Current);
        db.AddInParameter(updateCommand, "@EmployeeId", DbType.Int32, "EmployeeId", DataRowVersion.Current);

        DbCommand insertCommand = db.GetSqlStringCommand("insert into Employees (FirstName, LastName, Department) values (@FirstName, @LastName, @Department)");

        db.AddInParameter(insertCommand, "@FirstName", DbType.String, "FirstName", DataRowVersion.Current);
        db.AddInParameter(insertCommand, "@LastName", DbType.String, "LastName", DataRowVersion.Current);
        db.AddInParameter(insertCommand, "@Department", DbType.String, "Department", DataRowVersion.Current);

        db.UpdateDataSet(ds, "Table", insertCommand, updateCommand, null, UpdateBehavior.Standard);
    }

    static DataRow[] GetDataRows()
    {
        DataTable dt = new DataTable("Table");

        DataColumn dc = new DataColumn()
        {
            ColumnName = "EmployeeId",
            DataType = typeof(int),
        };

        dt.Columns.Add(dc);

        dc = new DataColumn()
        {
            ColumnName = "FirstName",
            DataType = typeof(string)
        };

        dt.Columns.Add(dc);

        dc = new DataColumn()
        {
            ColumnName = "LastName",
            DataType = typeof(string)
        };

        dt.Columns.Add(dc);

        dc = new DataColumn()
        {
            ColumnName = "Department",
            DataType = typeof(string)
        };

        dt.Columns.Add(dc);

        DataRow dr = dt.NewRow();
        dr.ItemArray = new object[] { 1, "Jimmy", "Smits", "Sales" };
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr.ItemArray = new object[] { null, "Irving", "Berlin", "Accounting" };
        dt.Rows.Add(dr);

        return dt.Select();
    }

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

Jun 14, 2012 at 6:53 AM

Thanks for your reply Rendy.

But, db.AddInParameter() is something which I really want to avoid. The table that I deal with is a complex table and has more than 30 columns.

So, is there any way out?

Thanks,