locked
need help on updating database table from wpf datagrid bound to datatable RRS feed

  • Question

  • basically i'm new in C# and WPF,

    i have a WPF DataGrid that bind with DataTable

    here the XAML for DataGrid

    <DataGrid AutoGenerateColumns="True" CanUserAddRows="False" HorizontalAlignment="Left" 
               ItemsSource="{Binding Path=., Mode=TwoWay}" 
               Margin="10,99,0,56" Name="dataGrid1" 
               SelectionUnit="CellOrRowHeader" Width="1044">  
    </DataGrid>

    here the public method from another class to load MySQL data into DataTable

    public DataTable dtQueries(string userQuery, string cnString = null)
    {
        //##Open the connection
        if (cnString == null)
        {
            this.open_connection("localConnectionString");
        }
        else
        {
            this.open_connection(cnString);
        }
    
        //##Create Command
        MySqlCommand cmd = new MySqlCommand(userQuery, mysqlConn);
    
        //##Create a data reader and Execute the command
    
        DataTable table = new DataTable();
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(table);
    
        return table;
    }

    and here the code to bind DataTable into DataGrid

    DataSet ds = new DataSet();
    string qGetDM = "SELECT * FROM mytable";
    
    dataGrid1.BeginInit();
    
    ds.Tables.Add(dboperation.dtQueries(qGetDM, "clientLegacyDM"));
    dataGrid1.DataContext = ds.Tables[0];
    
    dataGrid1.Items.Refresh();
    dataGrid1.EndInit();

    i have one command button as a SubmitButton to update MySQL database based on what user change in DataGrid..

    anyone have suggestion or better solutions how to do this(update MySQL table based on what user change in DataGrid when user click SubmitButton)?

    thx, i really really appreciate any help :D


    • Edited by dr.r3d Tuesday, March 5, 2013 11:17 AM
    Tuesday, March 5, 2013 11:12 AM

Answers

  • Hi dr.r3d,

    SqlCommandBuilder  is a class which automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database.

    Therefore, you can use sqlcommandbuilder and adapter to update your DB after  clicking the submit button.

    Here is a code sample I used to test(SQL DB):

    public partial class MainWindow : Window { SqlConnection conn = new SqlConnection("Server=(local); Database =Test; USer ID = UID;Password=Passwo;"); SqlDataAdapter adapter; public MainWindow() { InitializeComponent(); DataSet ds = new DataSet(); dataGrid1.BeginInit(); ds.Tables.Add(CreateTable()); dataGrid1.DataContext = ds.Tables[0]; dataGrid1.Items.Refresh(); dataGrid1.EndInit(); }

    //submit button click,update DB private void SubmitButton_Click(object sender, RoutedEventArgs e) { DataTable dt = dataGrid1.DataContext as DataTable; SqlCommandBuilder com = new SqlCommandBuilder(adapter); adapter.Update(dt); } public DataTable CreateTable() { string query ="Select * from Login"; adapter = new SqlDataAdapter(query,conn); DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } }

    In addition, from your code, I guess you are using MySQL DB, so, use MySqlCommandBuilder instead .

    Here are links maybe helpful:

    http://www.codeproject.com/Articles/30905/WPF-DataGrid-Practical-Examples

    http://msdn.microsoft.com/en-us/library/ms233819(v=vs.80).aspx

    Regards,


    Lisa Zhu [MSFT]
    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.

    • Proposed as answer by HappyAmy Thursday, March 14, 2013 12:41 PM
    • Marked as answer by Lisa Zhu Tuesday, March 19, 2013 9:23 AM
    Wednesday, March 6, 2013 5:27 AM
  • Dear Lisa Zhu

    I really appreciate your help to answer my problem, thats very useful for me, now i have made my temporary solutions for my problem above and heres my temporary (this solution might not the best solution, but at least its work for me :D)

    ok, heres the XAML

    <DataGrid AutoGenerateColumns="True" CanUserAddRows="False" HorizontalAlignment="Left" 
            ItemsSource="{Binding Path=., Mode=TwoWay}" 
    
            Margin="10,99,0,56" Name="dataGrid1" SelectionUnit="CellOrRowHeader" 
            Width="1044" 
    </DataGrid>

    next, i recreate the "public method from another class to load MySQL data into DataTable" for now i'm using DataSet heres the method, let name this class "MySQLOp"

    public MySqlDataAdapter mysqlDA; //Member variable of class "MySQLOp"
    public DataSet mysqlDS; //Member variable of class "MySQLOp"
    
    //public method to return user query as a DataSet
    public DataSet dsQueries(string userQuery, string cnString = null)
    {
        //##Open the connection
        if (cnString == null)
        {
            this.open_connection("localConnectionString");
        }
        else
        {
            this.open_connection(cnString);
        }
    
        //##Create Command
        MySqlCommand cmd = new MySqlCommand(userQuery, mysqlConn);
        mysqlDA = new MySqlDataAdapter(cmd);
    
        mysqlDS = new DataSet();
    
        mysqlDA.Fill(mysqlDS);
    
        return mysqlDS;
    
    }

    on wpf window lets named it "FormCRUD", i create one commandbutton to load the data, heres the code:

    MySQLOp dboperation;
    public yourForm()
    {
        InitializeComponent();
    
        dboperation = new MySQL();
    }
    
    private void btnClLoad_Click(object sender, RoutedEventArgs e)
    {
        string tablename = "your_table_name";
        qGetDM = "SELECT * FROM " + tablename;
    
        //#### Bind the DataSet to the GridView ####
        dataGrid1.BeginInit();
    
        //## Get Clien Data and return as DataSet
        dboperation.dsQueries(qGetDM, "clientLegacyDM");
    
        //## Set DataGrid ItemSource
        dataGrid1.SetBinding(ItemsControl.ItemsSourceProperty, new Binding
        {
            Source = dboperation.mysqlDS.Tables[0]
        });
        dataGrid1.Items.Refresh();
        dataGrid1.EndInit();
    
    }

    on the MySQLOp class, i create another method to save DataSet to save back to MySQL DB, heres the method

    public DataSet CreateCommandAndUpdate()
    {            
        MySqlCommandBuilder myBuilder = new MySqlCommandBuilder(mysqlDA);
        mysqlDA.Update(mysqlDS);
        return mysqlDS;
    }

    finally, i add another commandbutton in FormCRUD to as a SubmitUpdate, to submit data change back to database that call CreateCommandAndUpdate() method, heres the code

    private void btnClUpdate_Click(object sender, RoutedEventArgs e)
    {
        dboperation.CreateCommandAndUpdate();
    }

    for me this just a temporary solution, i'm still lookin for best and elegant solution :D maybe you can give me another solutions :D

    before i got this, i try many way, including use INotifyPropertyChanged, but i found that no use to implement that (CMIIW), besides i'm still confuse about Binding :D

    thx, cheerss :D



    • Edited by dr.r3d Wednesday, March 6, 2013 11:19 AM
    • Marked as answer by Lisa Zhu Tuesday, March 19, 2013 9:23 AM
    Wednesday, March 6, 2013 11:19 AM

All replies

  • Hi dr.r3d,

    SqlCommandBuilder  is a class which automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database.

    Therefore, you can use sqlcommandbuilder and adapter to update your DB after  clicking the submit button.

    Here is a code sample I used to test(SQL DB):

    public partial class MainWindow : Window { SqlConnection conn = new SqlConnection("Server=(local); Database =Test; USer ID = UID;Password=Passwo;"); SqlDataAdapter adapter; public MainWindow() { InitializeComponent(); DataSet ds = new DataSet(); dataGrid1.BeginInit(); ds.Tables.Add(CreateTable()); dataGrid1.DataContext = ds.Tables[0]; dataGrid1.Items.Refresh(); dataGrid1.EndInit(); }

    //submit button click,update DB private void SubmitButton_Click(object sender, RoutedEventArgs e) { DataTable dt = dataGrid1.DataContext as DataTable; SqlCommandBuilder com = new SqlCommandBuilder(adapter); adapter.Update(dt); } public DataTable CreateTable() { string query ="Select * from Login"; adapter = new SqlDataAdapter(query,conn); DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } }

    In addition, from your code, I guess you are using MySQL DB, so, use MySqlCommandBuilder instead .

    Here are links maybe helpful:

    http://www.codeproject.com/Articles/30905/WPF-DataGrid-Practical-Examples

    http://msdn.microsoft.com/en-us/library/ms233819(v=vs.80).aspx

    Regards,


    Lisa Zhu [MSFT]
    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.

    • Proposed as answer by HappyAmy Thursday, March 14, 2013 12:41 PM
    • Marked as answer by Lisa Zhu Tuesday, March 19, 2013 9:23 AM
    Wednesday, March 6, 2013 5:27 AM
  • Dear Lisa Zhu

    I really appreciate your help to answer my problem, thats very useful for me, now i have made my temporary solutions for my problem above and heres my temporary (this solution might not the best solution, but at least its work for me :D)

    ok, heres the XAML

    <DataGrid AutoGenerateColumns="True" CanUserAddRows="False" HorizontalAlignment="Left" 
            ItemsSource="{Binding Path=., Mode=TwoWay}" 
    
            Margin="10,99,0,56" Name="dataGrid1" SelectionUnit="CellOrRowHeader" 
            Width="1044" 
    </DataGrid>

    next, i recreate the "public method from another class to load MySQL data into DataTable" for now i'm using DataSet heres the method, let name this class "MySQLOp"

    public MySqlDataAdapter mysqlDA; //Member variable of class "MySQLOp"
    public DataSet mysqlDS; //Member variable of class "MySQLOp"
    
    //public method to return user query as a DataSet
    public DataSet dsQueries(string userQuery, string cnString = null)
    {
        //##Open the connection
        if (cnString == null)
        {
            this.open_connection("localConnectionString");
        }
        else
        {
            this.open_connection(cnString);
        }
    
        //##Create Command
        MySqlCommand cmd = new MySqlCommand(userQuery, mysqlConn);
        mysqlDA = new MySqlDataAdapter(cmd);
    
        mysqlDS = new DataSet();
    
        mysqlDA.Fill(mysqlDS);
    
        return mysqlDS;
    
    }

    on wpf window lets named it "FormCRUD", i create one commandbutton to load the data, heres the code:

    MySQLOp dboperation;
    public yourForm()
    {
        InitializeComponent();
    
        dboperation = new MySQL();
    }
    
    private void btnClLoad_Click(object sender, RoutedEventArgs e)
    {
        string tablename = "your_table_name";
        qGetDM = "SELECT * FROM " + tablename;
    
        //#### Bind the DataSet to the GridView ####
        dataGrid1.BeginInit();
    
        //## Get Clien Data and return as DataSet
        dboperation.dsQueries(qGetDM, "clientLegacyDM");
    
        //## Set DataGrid ItemSource
        dataGrid1.SetBinding(ItemsControl.ItemsSourceProperty, new Binding
        {
            Source = dboperation.mysqlDS.Tables[0]
        });
        dataGrid1.Items.Refresh();
        dataGrid1.EndInit();
    
    }

    on the MySQLOp class, i create another method to save DataSet to save back to MySQL DB, heres the method

    public DataSet CreateCommandAndUpdate()
    {            
        MySqlCommandBuilder myBuilder = new MySqlCommandBuilder(mysqlDA);
        mysqlDA.Update(mysqlDS);
        return mysqlDS;
    }

    finally, i add another commandbutton in FormCRUD to as a SubmitUpdate, to submit data change back to database that call CreateCommandAndUpdate() method, heres the code

    private void btnClUpdate_Click(object sender, RoutedEventArgs e)
    {
        dboperation.CreateCommandAndUpdate();
    }

    for me this just a temporary solution, i'm still lookin for best and elegant solution :D maybe you can give me another solutions :D

    before i got this, i try many way, including use INotifyPropertyChanged, but i found that no use to implement that (CMIIW), besides i'm still confuse about Binding :D

    thx, cheerss :D



    • Edited by dr.r3d Wednesday, March 6, 2013 11:19 AM
    • Marked as answer by Lisa Zhu Tuesday, March 19, 2013 9:23 AM
    Wednesday, March 6, 2013 11:19 AM
  • Hi,

    I think INotifyPropertyChanged only works for a property value changed. As the changes in your scenario is DataTable, it won't work.  


    Cheers, Amy

    Thursday, March 14, 2013 12:44 PM