Thursday, May 10, 2012

Bind Datatable with Database


Recently research on datatable bind with database.  Those programmers are brilliant to create binding method.
Let’s see how to use it.

database structure:
CREATE TABLE  testdecimal (
  `c1` decimal(10,8) DEFAULT NULL,
  `c2` decimal(10,8) DEFAULT NULL,
  `id` double NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert some data to test


code look like this:

MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "server=testbed-pc;user id=root;password=123;Database=test;charset=utf8;";
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = new MySqlCommand("select * from testdecimal where c1 =10", conn);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                da.Fill(testdecimal);
                while (i < testdecimal.Rows.Count)
                {
                    testdecimal.Rows[i]["c2"] = 2 + i ;
                    i++;
                }
                da.Update(testdecimal);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.ToString());
                conn.Close();
            }
            finally
            {
                conn.Close();
            }


when execute, all rows with c1 =10 updated! database update successfully! fast and convenient way!