一直想自己动手实现一个DATAGRIDVIEW实时更新数据到数据库,可是光想不做,都过去了很久。
正好趁今天下午有空,于是完成了一个,中间也碰到了不少问题。
言规正传,操作步骤如下:
1、新建一个表test_table,建立主键FID;
create table TEST_TABLE
(
FID INTEGER not null,
FNAME NVARCHAR2(20),
FLEVELID INTEGER
)
alter table TEST_TABLE add constraint PRIMARY_KEY_TEST_TABLE primary key (FID)
(
FID INTEGER not null,
FNAME NVARCHAR2(20),
FLEVELID INTEGER
)
alter table TEST_TABLE add constraint PRIMARY_KEY_TEST_TABLE primary key (FID)
2、由于是使用ORACLE,故添加了一个系列test_seq,以便添加数据时从这个系列取值;
3、使用dataadapter.update方法进行更新数据库,取数据的方法如下:
public static DataInfo GetDataSet(string safeSql, string tblname, string primarykey)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(safeSql, connection);
OracleDataAdapter da = new OracleDataAdapter(cmd);
OracleCommandBuilder ocb = new OracleCommandBuilder(da);
da.Fill(ds, tblname);
dt = ds.Tables[tblname];
dt.PrimaryKey = new DataColumn[] { dt.Columns[primarykey] };
DataInfo dif = new DataInfo(tblname, ds, da, dt);
return dif;
}
4、在主程序的某个取数据处动态创建一个DataInfo变量,并把获取到的datatable赋值给datagridview的databinding的datasource:{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(safeSql, connection);
OracleDataAdapter da = new OracleDataAdapter(cmd);
OracleCommandBuilder ocb = new OracleCommandBuilder(da);
da.Fill(ds, tblname);
dt = ds.Tables[tblname];
dt.PrimaryKey = new DataColumn[] { dt.Columns[primarykey] };
DataInfo dif = new DataInfo(tblname, ds, da, dt);
return dif;
}
DataInfo _di = DbHelperOra.GetDataSet("select * from test_table", "a", "FID");
this.bindingSource1.DataSource = _di.Dt;
this.bindingSource1.ResetBindings(true);
5、由于针对FLEVELID的操作,我弄了一个COMBOBOX来显示名称让其选择,在执行上面的3行语句前,需要加入以下代码:
DataTable _dtLevel = new DataTable();
_dtLevel.Columns.Add("FNAME", Type.GetType("System.String"));
_dtLevel.Columns.Add("FVALUE", Type.GetType("System.Decimal"));
for (int i = 1; i < 10; ++i)
{
_dtLevel.Rows.Add("年级"+i.ToString(), i);
}
DataGridViewComboBoxColumn column = this.dataGridView1.Columns["FLEVEL"] as DataGridViewComboBoxColumn;
column.DataSource = _dtLevel.DefaultView;
column.DisplayMember = "FNAME";
column.ValueMember = "FVALUE";
column.DataPropertyName = "FLEVELID";
_dtLevel.Columns.Add("FNAME", Type.GetType("System.String"));
_dtLevel.Columns.Add("FVALUE", Type.GetType("System.Decimal"));
for (int i = 1; i < 10; ++i)
{
_dtLevel.Rows.Add("年级"+i.ToString(), i);
}
DataGridViewComboBoxColumn column = this.dataGridView1.Columns["FLEVEL"] as DataGridViewComboBoxColumn;
column.DataSource = _dtLevel.DefaultView;
column.DisplayMember = "FNAME";
column.ValueMember = "FVALUE";
column.DataPropertyName = "FLEVELID";
切记:ORACLE里面的INTEGER对应到代码里面的类型是System.Decimal,否则显示不了。
6、然后在添加、删除以及DATAGRIDVIEW的CellEndEdit事件处加入相应的代码即可。贴出代码如下:
private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
{
string nextId = DbHelperOra.GetSingle("select test_seq.nextval from dual").ToString();
DataRow dr = _di.Dt.NewRow();
dr["FID"] = Int32.Parse(nextId);
_di.Dt.Rows.Add(dr);
_di.Oda.Update(_di.Ds, "a");
this.bindingSource1.ResetBindings(true);
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (this.bindingSource1.Current != null)
{
DataRowView drv = this.bindingSource1.Current as DataRowView;
DataRow dr = drv.Row;
dr.Delete();
_di.Oda.Update(_di.Ds, "a");
this.bindingSource1.ResetBindings(true);
}
}
{
string nextId = DbHelperOra.GetSingle("select test_seq.nextval from dual").ToString();
DataRow dr = _di.Dt.NewRow();
dr["FID"] = Int32.Parse(nextId);
_di.Dt.Rows.Add(dr);
_di.Oda.Update(_di.Ds, "a");
this.bindingSource1.ResetBindings(true);
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (this.bindingSource1.Current != null)
{
DataRowView drv = this.bindingSource1.Current as DataRowView;
DataRow dr = drv.Row;
dr.Delete();
_di.Oda.Update(_di.Ds, "a");
this.bindingSource1.ResetBindings(true);
}
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
_di.Oda.Update(_di.Ds, "a");
}
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
}
{
_di.Oda.Update(_di.Ds, "a");
}
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
}
最后运行效果: