Why can not update edited row to database

Topics: Data Access Application Block
Aug 7, 2013 at 8:54 AM
Edited Aug 7, 2013 at 9:32 AM
there is no error, but value in database not changed
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data;
using System.Data.Common;
using System.Configuration;

using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Common.Utility;
using Microsoft.Practices.EnterpriseLibrary.Common.Properties;
using Microsoft.Practices.EnterpriseLibrary.Common.Instrumentation;

using Microsoft.Practices.EnterpriseLibrary;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Logging.Filters;
using Microsoft.Practices.EnterpriseLibrary.Logging.ExtraInformation;


using System.Data.SqlClient;

namespace testDataGrid
{
    public class DataAccessLayer
    {

        static string _connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("ConString");
        
        public DataTable getTable(String table_name, String where)
        {
            //DatabaseProviderFactory factory = new DatabaseProviderFactory();
            //Database db = factory.Create("ConString");


            String queryString = "Select * from dbo.[" + table_name+"]";
            queryString = queryString + " " + where;
            if (!IsSelect(queryString))
                return null;
            DbCommand command = db.GetSqlStringCommand(queryString);
            DataTable table = db.ExecuteDataSet(command).Tables[0];
            return table;
        }
        public void updateTable(DataRow r, string table_name, int key)
        {
            try
            {
                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;

                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                //ds.Tables[table_name].Rows.Add(r.ItemArray);

                DataColumn[] keyColumns = new DataColumn[1];
                keyColumns[0] = ds.Tables[0].Columns["a"];
                ds.Tables[0].PrimaryKey = keyColumns;
                //ds.Tables[table_name].Rows[key].SetParentRow(r);
                //ds.Tables[table_name].Rows[key].SetModified();

                ds.Tables[table_name].Rows[key].BeginEdit();
                //ds.Tables[table_name].Rows[key].ItemArray = r.ItemArray;
                ds.Tables[table_name].Rows[key].SetParentRow(r);
                ds.Tables[table_name].Rows[key].EndEdit();
                ds.Tables[table_name].Rows[key].AcceptChanges();
                //for(int j = 0; j < r.ItemArray.Count(); ++j)
                //{
                    //ds.Tables[table_name].Rows[key].ItemArray.SetValue(r.ItemArray[j], j);
                //}

                //.AcceptChanges();
                //ds.AcceptChanges();
                da.Update(ds, table_name);
                /*
                SqlConnection conn = new SqlConnection("Data Source=x.x.x.x;Initial Catalog=Testing002; Integrated Security=SSPI");
                String queryString = "Select * from dbo.[" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                DbDataAdapter da = db.GetDataAdapter();

                SqlCommand cmd = new SqlCommand(queryString, conn);
                cmd.CommandType = CommandType.Text;
                da.SelectCommand = cmd;

                //da.SelectCommand = db.GetSqlStringCommand(queryString);
                //da.SelectCommand.CommandType = CommandType.Text;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);

                //for (int i = 0; i < r.ItemArray.Count(); ++i)
                //{
                    //ds.Tables[table_name].Rows[i] = r[i].ToString();    
                //}
                ds.Tables[table_name].Rows.Add(r.ItemArray);
                //ds.Tables[table_name].AcceptChanges();

                da.Update(ds, table_name);*/
                /*
                SqlConnection conn = new SqlConnection("Data Source=172.16.17.9;Initial Catalog=Testing002; Integrated Security=SSPI");
                //DatabaseProviderFactory factory = new DatabaseProviderFactory();
                //Database db = factory.Create("ConString");
                String queryString = "Select * from dbo.[" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                DbDataAdapter da = db.GetDataAdapter();

                SqlCommand cmd = new SqlCommand(queryString, conn);
                da.SelectCommand = cmd;
                //SqlCommand updatecmd = new SqlCommand("UPDATE Table1 SET c = @c, d = @d WHERE a = @a and b = @b", conn);
                //da.UpdateCommand = updatecmd;
                //SqlParameter workParm = da.UpdateCommand.Parameters.Add("@c");
                //workParm.SourceColumn = "c";
                //workParm.SourceVersion = DataRowVersion.Original;

                //da.SelectCommand = db.GetSqlStringCommand(queryString);
                //da.UpdateCommand = db.GetSqlStringCommand();

                //da.SelectCommand = db.GetSqlStringCommand(queryString);
                //da.SelectCommand.CommandType = CommandType.Text;

                DataSet ds = new DataSet();
                //da.AcceptChangesDuringFill = true;
                da.Fill(ds, table_name);
                ds.Tables[table_name].ImportRow(r);
                //ds.AcceptChanges();
                //da.AcceptChangesDuringUpdate = true;
                da.Update(ds, table_name);*/
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
        Boolean IsSelect(String queryString)
        {
            if (queryString.ToUpper().Contains("EXEC") ||
               queryString.Contains("INSERT") ||
               queryString.Contains("UPDATE"))
                return false;
            return true;

        }
        
    }
}

private void myGrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
        {
            DataAccessLayer dal = new DataAccessLayer();
            DataTable order = dal.getTable("Table1", "");
            DataRow newOrderRow = order.NewRow();

            for (int i = 0; i < dgStatus.Columns.Count; ++i)
            {
                if (e.Column.DisplayIndex != i)
                {
                    newOrderRow[i] = (dgStatus.Columns[i].GetCellContent(e.Row) as TextBlock).Text.Trim();
                }
            }
            newOrderRow[e.Column.DisplayIndex] = (e.Column.GetCellContent(e.Row) as TextBox).Text.Trim();

            dal.updateTable(newOrderRow, "Table1", e.Row.GetIndex());

            //columns[e.Column.DisplayIndex].InputValue = (e.Column.GetCellContent(e.Row) as TextBox).Text;
        }
Aug 7, 2013 at 9:48 AM
After trial many times, success with the following
public void updateTable(DataRow r, string table_name, int key)
        {
            try
            {
                DatabaseProviderFactory factory = new DatabaseProviderFactory();
                Database db = factory.Create("ConString");

                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;

                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                //ds.Tables[table_name].Rows.Add(r.ItemArray);
                //DataColumn[] keyColumns = new DataColumn[1];
                //keyColumns[0] = ds.Tables[0].Columns["a"];
                //ds.Tables[0].PrimaryKey = keyColumns;
                
                for(int j = 0; j < r.ItemArray.Count(); ++j)
                {
                    ds.Tables[table_name].Rows[key][j] = r.ItemArray[j];
                }
                
                da.Update(ds, table_name);
                ds.AcceptChanges();

                if (da.ContinueUpdateOnError == true)
                    Console.WriteLine("");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
Marked as answer by randylevy on 10/7/2013 at 11:06 PM