public class SqlCeBatchUpated {
public string ConnStr { set; get; }
public delegate void MyUpdate(SqlCeCommand cmd, SqlCeTransaction tran);
public SqlCeBatchUpated(string connstr) { this.ConnStr = connstr; }
public void BatchUpdate(MyUpdate myUpdate)
{
using (SqlCeConnection conn = new SqlCeConnection(ConnStr))
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
conn.Open();
SqlCeTransaction tran = conn.BeginTransaction();
if (null != myUpdate)
myUpdate(cmd, tran);
tran.Commit();
conn.Close();
}
}
}
public void ReCalc()
{
//2018.11.13 sqlce貌似不支持update from
string filename = AppDomain.CurrentDomain.BaseDirectory + "SQL\\SqlCe\\Calc.sql";
ExeceteNonQueryByFile(filename);
SqlCeBatchUpated sbu = new SqlCeBatchUpated(this.ConnStr);
sbu.BatchUpdate(UpDateBm);
sbu.BatchUpdate(updateOther);
}
private void updateOther(SqlCeCommand cmd, SqlCeTransaction tran)
{
string filename = AppDomain.CurrentDomain.BaseDirectory + "SQL\\SqlCe\\Calc02.sql";
string sql = MyCommon.ReadStringFromFile(filename, "\r\n");
DataTable dt = SqlCeHelper.ExecuteDataSet(this.ConnStr, sql).Tables[0];
int count = 0;
foreach (DataRow dr in dt.Rows)
{
string oid = dr["object_id"].ToString();
string s = string.Format("update zd set sz={0},xz={1},yf={2},gxfs='{3}' where object_id='{4}'",
dr["sz"].ToString(), dr["xz"].ToString(), dr["yf"].ToString(), dr["gxfs"].ToString(),
oid);
cmd.CommandText = s;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
count++;
}
MessageBox.Show(count.ToString());
}
/// <summary>
/// 更新部门
/// </summary>
/// <param name="cmd"></param>
/// <param name="tran"></param>
private void UpDateBm(SqlCeCommand cmd, SqlCeTransaction tran)
{
string sql = "select jsr,bm from jsrgzb";
Dictionary<string, string> bmDict = GetDictionary(sql, "jsr", "bm");
DataTable dt = SqlCeHelper.ExecuteDataSet(this.ConnStr, "select object_id,jsr from zd").Tables[0];
int count = 0;
foreach (DataRow dr in dt.Rows)
{
string oid = dr["object_id"].ToString();
string jsr = dr["jsr"].ToString();
if (bmDict.ContainsKey(jsr))
{
cmd.CommandText = string.Format("update zd set bm='{0}' where object_id='{1}'", bmDict[jsr], oid);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
count++;
}
}
//MessageBox.Show(count.ToString());
}