none
How to update database through datarow RRS feed

  • Question

  • in VS2012 enterprise library 6.0, in datagrid i only one field in column,

    i have already filled in the fields in the row, and update with this row, however still
    not updated value in database  


        public class DataAccessLayer
        {
    
            //static string _connectionString = ConfigurationManager.ConnectionStrings["FoodConnectionString"].ConnectionString;
            //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("FoodConnectionString");
    
            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)
            {
                try
                {
                    SqlConnection conn = new SqlConnection("Data Source=x.x.x.x;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;
                    DataSet ds = new DataSet();
                    da.Fill(ds, table_name);
                    ds.Tables[table_name].ImportRow(r);
                    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;
    
            }
    
        }



    Datagrid's CellEditEnding function 

         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");
        
                }


                            

    Designer

    Wednesday, August 7, 2013 6:05 AM

Answers

  • Hello zvzzvz,

    Thank you for posting in MSDN Forums.

    From your description, I notice the issue you are experiencing is that how to update database through datarow.

    I also notice that you used the dataadapter.update() to update date. With the code provided by you, I have provided a sample code and it runs ok.

    Please see the sample code below:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    namespace ADONET
    {
        class UpdataWithDataRow
        {
            internal void Show()
            {
                //1.Create A Connection             
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        string connectionString = "server=(localdb)\\v11.0;Integrated Security=SSPI;database=TestDataBase";
                        SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
                        connectionStringBuilder.Add("min pool size", 0);
                        connectionStringBuilder.Add("max pool size", 1);
                        SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                        connection.Open();
                        SqlCommand selectCommand = connection.CreateCommand();
                        selectCommand.CommandText = "select * from Course";
                        SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        DataRow dr = dt.NewRow();
                        dr["CourseID"] = 6;
                        dr["CourseName"] = "Chinese";
                        dt.Rows.Add(dr);
                        dt.Rows[0]["CourseName"] = "Chinese";
                        da.InsertCommand = new SqlCommand("insert Course(CourseID,CourseName) values(@CourseID,@CourseName)", connection);
                        da.InsertCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.InsertCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.UpdateCommand = new SqlCommand("update Course set CourseName = @CourseName where CourseID = @CourseID", connection);
                        da.UpdateCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.UpdateCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.Update(dt);
                        connection.Close();
                        scope.Complete();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
                Console.ReadLine();
            }
        }
    }

    Result:

    Before Running:

    After Running:

    I also used the dataadapter.update() method to update data. I added a new row and changed a row in my program.

    The difference with yours is that I used the transactionScope which is a lightweight transaction framework,  and I added parameters for  insertCommand object or updateCommand object.

    I recommend that you try it in your code.

    Here is more information below about the dataadapter.update():

    http://msdn.microsoft.com/en-us/library/33y2221y.aspx

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 8, 2013 6:16 AM
    Moderator