一个关于批量增加及更新的问题
注意:此DataGridView中的数据源来自两个表.
Command对象.CommandText=“SQL语句1”
Command对象.CommandText=“SQL语句2”
然后直接打开 适配器.update();
连接不用打开
updtate 自动打开 自动关闭
我想我可能没有表述清楚,我的意思不是要同时更新两个表,而是同时提交对一个表的多种更新(如同时添加多笔记录,并修改或删除部份记录等),我所说到的两个表是说我DataGridView中的数据源是我用"SELECT tableA.*,tableB.* FROM tableA LEFT OUTER JOIN tableB ON tableA.partID = tableB.partID WHERE WHERE table.ID = 'S70015'"
我做过,首先我做个自定义控件MYDataGridView,在后台先把数据源及表结构邦定好,代码:public partial class DataGridViewBB11 : JEControlLibrary.JEDataGridView
{
/// <summary>
/// 报价单,询价单--数据控件
/// </summary>
private System.Windows.Forms.ContextMenuStrip myContextMenu;
private System.Windows.Forms.ToolStripMenuItem delMenuItem;
private DataTable myDataTable = new DataTable();
public DataGridViewBB11()
{
InitializeComponent();
}
private void InitContextMenu()
{
delMenuItem = new ToolStripMenuItem();
delMenuItem.Text = "删除记录";
delMenuItem.Click += new System.EventHandler(this.delMenuItem_Click);
myContextMenu.Items.AddRange(new ToolStripItem[] { delMenuItem });
}
private void delMenuItem_Click(object sender, System.EventArgs e)
{
if (this.CurrentRow.Index>=0&& this.CurrentRow.Cells[0].Value.ToString() != "" && this.CurrentRow.Cells[0].Value.ToString() != null)
{
if (myDataTable.DefaultView[this.CurrentRow.Index].IsNew)
{
myDataTable.DefaultView[this.CurrentRow.Index].CancelEdit();
}
else
{
myDataTable.DefaultView[this.CurrentRow.Index].Row["DeleteFlag"] = true;
}
}
}
protected override bool ProcessCmdKey(ref System.Windows.Forms.Message msg, System.Windows.Forms.Keys keyData)
{
if (msg.WParam.ToInt32() == (int)Keys.Enter)
{
SendKeys.Send("{down}");
return (true);
}
return base.ProcessCmdKey(ref msg, keyData);
}
public void InitdgvBB11()
{
myContextMenu = new System.Windows.Forms.ContextMenuStrip();
InitContextMenu();
myDataTable.TableName = "myDataTable";
DataColumn dc;
dc = myDataTable.Columns.Add("BB1101",typeof(string) );
dc.DefaultValue = "";
dc.ColumnMapping = MappingType.Hidden;
dc = myDataTable.Columns.Add( "BB1102",typeof(string));
dc.DefaultValue = "";
dc.Unique = true;
dc = myDataTable.Columns.Add( "BB0502",typeof(string));
dc.DefaultValue = "";
dc = myDataTable.Columns.Add( "BB0503",typeof(string) );
dc.DefaultValue = "";
dc = myDataTable.Columns.Add("BB0506",typeof(string) );
dc.DefaultValue = "";
dc = myDataTable.Columns.Add("BB0509",typeof(string) );
dc.DefaultValue = "";
dc = myDataTable.Columns.Add("BB1103",typeof(decimal) );
dc.DefaultValue = 0;
dc = myDataTable.Columns.Add("AA9904",typeof(string) );
dc.DefaultValue = "";
dc = myDataTable.Columns.Add("DeleteFlag",typeof(bool) );
dc.DefaultValue = false;
dc.ColumnMapping = MappingType.Hidden;
this.ReadOnly = false;
this.AllowUserToAddRows = true;
this.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
this.DataSource = myDataTable;
myDataTable.DefaultView.RowFilter = "DeleteFlag = false";
this.Columns.Clear();
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[0].DataPropertyName = "BB1102";
this.Columns[0].AutoSizeMode= System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[0].HeaderText = "商品编码";
((DataGridViewTextBoxColumn)this.Columns[0]).ContextMenuStrip = myContextMenu;
((DataGridViewTextBoxColumn)this.Columns[0]).MaxInputLength = 10;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[1].DataPropertyName = "BB0502";
this.Columns[1].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[1].HeaderText = "自定义码";
((DataGridViewTextBoxColumn)this.Columns[1]).ContextMenuStrip = myContextMenu;
this.Columns[1].ReadOnly = true;
this.Columns[1].DefaultCellStyle.BackColor = Color.LightGray;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[2].DataPropertyName = "BB0503";
this.Columns[2].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[2].HeaderText = "商品名称";
((DataGridViewTextBoxColumn)this.Columns[2]).ContextMenuStrip = myContextMenu;
this.Columns[2].ReadOnly = true;
this.Columns[2].DefaultCellStyle.BackColor = Color.LightGray;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[3].DataPropertyName = "BB0506";
this.Columns[3].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[3].HeaderText = "单位";
((DataGridViewTextBoxColumn)this.Columns[3]).ContextMenuStrip = myContextMenu;
this.Columns[3].ReadOnly = true;
this.Columns[3].DefaultCellStyle.BackColor = Color.LightGray;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[4].DataPropertyName = "BB0509";
this.Columns[4].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[4].HeaderText = "商品规格";
((DataGridViewTextBoxColumn)this.Columns[4]).ContextMenuStrip = myContextMenu;
this.Columns[4].ReadOnly = true;
this.Columns[4].DefaultCellStyle.BackColor = Color.LightGray;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[5].DataPropertyName = "BB1103";
this.Columns[5].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
this.Columns[5].HeaderText = "商品价格";
((DataGridViewTextBoxColumn)this.Columns[5]).ContextMenuStrip = myContextMenu;
this.Columns.Add(new DataGridViewTextBoxColumn());
this.Columns[6].DataPropertyName = "AA9904";
this.Columns[6].AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;
this.Columns[6].HeaderText = "备注";
((DataGridViewTextBoxColumn)this.Columns[6]).ContextMenuStrip = myContextMenu;
}
直接在界面上删除,新增,修改任何记录,后执行一个事务,执行批量SQL语句:
如:
private void SaveData()
{
this.cbkBB1007.Focus();
Jmeport.DataService.BLL.BB10 bll = new Jmeport.DataService.BLL.BB10();
Jmeport.DataService.Model.BB10 model = new Jmeport.DataService.Model.BB10();
model.BB1001 = this.tbxBB1001.Text;
model.BB1002 = this.cbxBA01.SelectedValue.ToString();
model.BB1003 = this.dtpBB1003.Value;
model.BB1004 = int.Parse(this.tbxBB1004.Text);
model.BB1005 = DateTime.Parse(this.tbxBB1005.Text);
model.BB1006 = "0";
model.BB1007 = this.cbkBB1007.Checked ? "1" : "0";
model.BB1008 = "";
model.BB1009 = "";
model.AA9904 = this.tbxAA9904.Text;
model.AA9901 = "1010";
if (addnew)
{
if (bll.Exists(model.BB1001))
{
MessageBox.Show("此报价单编码已被他人占用,请刷新后再试!", "提醒对话框", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.lblRefresh.Visible = true;
return;
}
else
{
this.arrayList.Add(bll.AddSQL(model));
}
}
else
{
this.arrayList.Add(bll.UpdateSQL(model));
}
SaveDgvBB11SQL();
try
{
bll.ExecuteSqlTran(arrayList);//执行多条SQL语句,事务处理
kp.ClearHashtable();
kp.KeepData(this.Controls);
this.dgvBB11.Tag = "false";
callBack();
MessageBox.Show("数据成功保存!", "提醒对话框", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ee)
{
MessageBox.Show("系统异常,数据保存失败!"+ee.Message, "警告对话框", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
修改记录时加载数据:
private void filldgvDataRow(string BB1001)
{
Jmeport.DataService.BLL.BB11 bll = new Jmeport.DataService.BLL.BB11();
DataTable rDt = bll.GetList("BB1101='" + BB1001 + "'").Tables[0];
DataTable tDt = this.dgvBB11.DataSource as DataTable;
tDt.Rows.Clear();
if (rDt.Rows.Count > 0)
{
foreach (DataRow rDr in rDt.Rows)
{
tDt.Rows.Add(rDr["BB1101"], rDr["BB1102"], rDr["BB0502"], rDr["BB0503"], rDr["BB0506"], rDr["BB0509"], rDr["BB1103"], rDr["AA9904"]);
}
}
}
大概就这样,某些细节再自己完善吧