1、由于有大量数据,怎么考虑事务的处理?
2、dataGridView1编辑时未绑定数据源,且当记录未满屏时,有相应多的空白行填充,是否插入或更新时不考虑这些空白行?
3、当更改dataGridView1里的数据时,如果某些行没有修改,未修改的行是否可以不再重复向数据库提交写入或更新操作?
4、听说可能用SqlDataAdapter操作,却未找到相关文档,能否实现上面说的功能?
2、dataGridView1编辑时未绑定数据源,且当记录未满屏时,有相应多的空白行填充,是否插入或更新时不考虑这些空白行?
3、当更改dataGridView1里的数据时,如果某些行没有修改,未修改的行是否可以不再重复向数据库提交写入或更新操作?
4、听说可能用SqlDataAdapter操作,却未找到相关文档,能否实现上面说的功能?
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(List<String> SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return 0;
}
}
}
DataSet ds = new DataSet();
SqlDataAdapter sda;SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(ds);
this.dataGridView1.DataSource = ds.Tables[0];
事务使用
TransactionScope scope = new
TransactionScope()) {}
transaction = connection.BeginTransaction();
try{
SqlCommandBuilder.GetDeleteCommand().ExecuteNonQuery();
SqlCommandBuilder.GetUpdateCommand().ExecuteNonQuery();
SqlBulkCopy.WriteToServer(DataTable,DataRowState.Added);
catch{transaction.Rollback();}
finally{connection.Close();}
{
string c_id = GridView1.DataKeys[e.RowIndex].Value.ToString(); string c_user = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString();
string c_stardate = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString();
string c_costName = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text.ToString();
string c_price = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString();
string c_real = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[6].Controls[0])).Text.ToString();
string c_costMoney = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[7].Controls[0])).Text.ToString();
string c_userName = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[8].Controls[0])).Text.ToString();
string c_res = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[9].Controls[0])).Text.ToString(); string strSql = "update cost set c_user='" + c_user + "',c_stardate='" + c_stardate + "',c_costName='" + c_costName + "',c_price='" + c_price + "',c_real='" + c_real + "',c_costMoney='" + c_costMoney + "',c_userName='" + c_userName + "',c_res='" + c_res + "' where c_id='" + c_id + "'";
SqlConnection con = DB.GetCon();
con.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
RegisterStartupScript("", "<script>alert('修改成功')</script>");
}
else
{
RegisterStartupScript("", "<script>alert('修改失败')</script>");
}
cmd.Dispose();
con.Close();
GridView1.EditIndex = -1;
this.bind();
}
更新数据 看看
批量插入与更新时,DataSet的列名与顺序是不是要跟SQL数据库的表列名与顺序保持完全一致?
我是dataGridView1数据修改后,用一个方法得到修改后的数据,DataSet dt = GetDataSetFromDataGridView(dataGridView1);测试将其dataGridView2.DataSource = dt.Tables[0];都是成功的。现在就是想将这个dt更新到数据库相应记录,总也不成功!但如果之前绑定dataGridView1数据源又可以。
不知道SqlCommandBuilder可不可以单独取一个DataSet的内容去更新,而非绑定过的内容。
public void DataSetUpdate(DataSet ds, string connectstr, string tablename, string sqlstr)
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connectstr); //使用SqlCommandBuilder 对像填充SqlDataAdapter 的InsertCommand、DeleteCommand、UpdateCommand对像
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
int val = adapter.Update(ds, tablename);
ds.AcceptChanges();
}
catch
{
throw;
}
}
{
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand(sqlStr, connection);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); myAdapter.Update(ds, ds.Tables[0].TableName);
}没有搞懂,新增的数据保存时没有问题,修改数据保存就会提示:
违反了 PRIMARY KEY 约束 'PK_TC'。不能在对象 'dbo.TC' 中插入重复键。
语句已终止。感觉修改数据时也插入了数据,不知道哪时错了?!!!
期待高手解决!
Private Sub GetDataToDbYh(ByVal num As Integer)
Try
For i As Integer = 1 To num Dim n1 As String = Dgv2.Rows(i - 1).Cells(0).Value.ToString
Dim n2 As Double = CType(Dgv2.Rows(i - 1).Cells(1).Value, Double)
Dim n3 As Double = CType(Dgv2.Rows(i - 1).Cells(2).Value, Double)
Dim n4 As Double = CType(Dgv2.Rows(i - 1).Cells(3).Value, Double)
Dim n5 As Double = CType(Dgv2.Rows(i - 1).Cells(4).Value, Double)
Dim n6 As Double = CType(Dgv2.Rows(i - 1).Cells(5).Value, Double)
Dim n7 As Double = CType(Dgv2.Rows(i - 1).Cells(6).Value, Double)
Dim n8 As Double = CType(Dgv2.Rows(i - 1).Cells(7).Value, Double)
Dim n9 As Double = CType(Dgv2.Rows(i - 1).Cells(8).Value, Double)
Dim n10 As Double = CType(Dgv2.Rows(i - 1).Cells(9).Value, Double)
Dim n11 As Double = CType(Dgv2.Rows(i - 1).Cells(10).Value, Double)
Dim n12 As Double = CType(Dgv2.Rows(i - 1).Cells(11).Value, Double)
Dim n13 As Double = CType(Dgv2.Rows(i - 1).Cells(12).Value, Double)
Dim n14 As Double = CType(Dgv2.Rows(i - 1).Cells(13).Value, Double)
Dim n15 As Double = CType(Dgv2.Rows(i - 1).Cells(14).Value, Double)
Dim n16 As Double = CType(Dgv2.Rows(i - 1).Cells(15).Value, Double)
Dim n17 As Double = CType(Dgv2.Rows(i - 1).Cells(16).Value, Double)
Dim n18 As Double = CType(Dgv2.Rows(i - 1).Cells(17).Value, Double)
Dim n19 As Double = CType(Dgv2.Rows(i - 1).Cells(18).Value, Double)
Dim n20 As Double = CType(Dgv2.Rows(i - 1).Cells(19).Value, Double)
Dim n21 As String = Dgv2.Rows(i - 1).Cells(20).Value.ToString
If n1 <> "" Then
Dim ins As New chenhui.chycom
Dim actab As DataTable = ins.CreateDataTable("select * from qdshjyh", "qdshjyh")
Dim oii As Integer = ins.QdShjYhInsert("qdshjyh", n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16, n17, n18, n19, n20, n21, Dtp1) If oii = 0 Then
MsgBox("已存在相同的试验编号!", MsgBoxStyle.Information, "无侧限抗压强度试验试件养护")
End If
Else
MsgBox("试件编号不能为空!")
Exit Sub
End If Next
Catch ex As Exception End Try
End Sub