sqlce不支持关联更新（update from)的替代方案

 

 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());
        }