SQL ce RDA模式 学习测试代码

开发环境：win xp sp3 ,vs 2008,sql ce 3.0 sql 2000 sp4 

using System;
using System.IO;
using System.Data.SqlServerCe;
using System.Data.SqlClient;
using SmartDeviceTest1.Data;
using System.Data;
using System.Windows.Forms;

namespace SmartDeviceTest1
{
    public partial class FrmParkRda : Form
    {
        private string _localDbName = "RdaDb.sdf";
        private string _localConnectionString = @"Data Source=RdaDb.sdf;Password=''";
        private string _remoteDbName = "SmartDeviceTestDB";
        private string _serverConnectString = "Data Source=192.168.74.84;Initial Catalog=SmartDeviceTestDB;User ID=sa;Provider=SQLOLEDB; Persist Security Info=True;";
        private string _internetUrl = "http://192.168.74.84/SqlCeShare/sqlcesa30.dll";

        private string _localTblName = "TBL_Park";

        public SqlCeRemoteDataAccess _rda = null;

        public FrmParkRda()
        {
            InitializeComponent();
        }

        //设置_rda
        private void InitRDa()
        {
            _rda = new SqlCeRemoteDataAccess();
            _rda.InternetUrl = _internetUrl;
            _rda.LocalConnectionString = _localConnectionString;
        }

        //创建本地数据库
        public void CreateLocalDB()
        {
            SqlCeEngine engine = new SqlCeEngine(_localConnectionString);
            try
            {
                if (File.Exists(_localDbName))
                    File.Delete(_localDbName);

                engine.CreateDatabase();

                MessageBox.Show("创建成功");
            }
            catch (Exception ee)
            {
                // MessageBox.Show("创建失败");
                MessageBox.Show(ee.Message);
            }
            finally
            {
                engine.Dispose();
            }

        }

        //读取本地数据
        protected DataTable GetLocalData()
        {
            DataSet ds = new DataSet();
            SqlCeConnection con = new SqlCeConnection();
            con.ConnectionString = _localConnectionString;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from " + _localTblName;
            try
            {
                con.Open();
                SqlCeDataAdapter adp = new SqlCeDataAdapter();
                adp.SelectCommand = cmd;
                adp.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
                return null;
            }
            finally
            {
                con.Dispose();
            }


        }

        //读取服务器数据（这是直接连接服务器的模式，仅作为测试使用）
        private DataTable GetServerData()
        {
            DataSet ds = new DataSet();
            SqlConnection con = new SqlConnection();
            con.ConnectionString = PublicInfo.serverConStr;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from TBL_Park";
            try
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmd;
                adp.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ee)
            {
                MessageBox.Show("读取服务器信息失败！");
                MessageBox.Show(ee.Message);
                return null;
            }
            finally
            {
                con.Dispose();
            }

        }

        //执行创建本地数据库
        private void menuItem2_Click(object sender, EventArgs e)
        {
            dg1.DataSource = null;
            CreateLocalDB();
        }

        //从服务器上获取最新的数据到本地表（如果存在本地表，先删除本地表）
        private void menuItem3_Click(object sender, EventArgs e)
        {
            InitRDa();
            SqlCeConnection con = new SqlCeConnection();
            con.ConnectionString = _localConnectionString;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = con;
            //cmd.CommandText = "if exists(select 1 from sysobjects where name = 'TBL_Park') drop table TBL_Park";
            try
            {
                con.Open();
                cmd.CommandText = "select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'TBL_Park' ";
                if (cmd.ExecuteScalar() != null)
                {
                    cmd.CommandText = "drop table TBL_Park";
                    cmd.ExecuteNonQuery();
                }

                //执行存储过程HV_QueryParks 返回一个本地“视图”。
                _rda.Pull(_localTblName, " exec HV_QueryParks 2 ", _serverConnectString, RdaTrackOption.TrackingOn);
                MessageBox.Show("同步下载成功");
            }
            catch (Exception ee)
            {
                con.Dispose();
                MessageBox.Show(ee.Message);
            }

        }

        //绑定本地数据到datagrid1
        private void menuItem4_Click(object sender, EventArgs e)
        {
            dg1.DataSource = GetLocalData();
        }

        //绑定服务器数据到datagrid2
        private void menuItem5_Click(object sender, EventArgs e)
        {
            dg2.DataSource = GetServerData();

        }

        //在本地插入数据
        private void menuItem6_Click(object sender, EventArgs e)
        {
            FrmAddPark fm = new FrmAddPark();
            fm.ShowDialog();
            if (fm.DialogResult == DialogResult.Yes)
            {
                SqlCeConnection con = new SqlCeConnection();
                con.ConnectionString = _localConnectionString;
                SqlCeCommand cmd = new SqlCeCommand();
                cmd.Connection = con;
                cmd.CommandText = " insert into TBL_Park(ParkID,CityID,Park) values('" + fm.ParkId.ToString() + "',2, '" + fm.ParkName + "')";
                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("插入成功！");
                }
                catch (Exception ee)
                {
                    con.Dispose();
                    MessageBox.Show(ee.Message);
                }
            }
        }

        //修改本地数据
        private void menuItem7_Click(object sender, EventArgs e)
        {
            MessageBox.Show("请双击DataGrid要修改的行");
        }

        //修改本地数据
        private void dg1_DoubleClick(object sender, EventArgs e)
        {
            if (dg1.VisibleRowCount > 0)
            {
                FrmAddPark fm = new FrmAddPark();
                fm.textBox1.Text = dg1[dg1.CurrentRowIndex, 0].ToString();
                fm.textBox1.ReadOnly = true;
                fm.textBox2.Text = dg1[dg1.CurrentRowIndex, 2].ToString();
                fm.ShowDialog();
                if (fm.DialogResult == DialogResult.Yes)
                {

                    SqlCeConnection con = new SqlCeConnection();
                    con.ConnectionString = _localConnectionString;
                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = con;
                    cmd.CommandText = " update  TBL_Park  set Park ='" + fm.ParkName + "' where ParkID = " + fm.ParkId;
                    try
                    {
                        con.Open();
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("修改成功！");
                        //dg1.DataSource = GetLocalData();
                    }
                    catch (Exception ee)
                    {
                        con.Dispose();
                        MessageBox.Show(ee.Message);
                    }

                }

            }
        }

        //删除本地数据
        private void menuItem8_Click(object sender, EventArgs e)
        {
            if (dg1.VisibleRowCount > 0)
            {
                SqlCeConnection con = new SqlCeConnection();
                con.ConnectionString = _localConnectionString;
                SqlCeCommand cmd = new SqlCeCommand();
                cmd.Connection = con;
                cmd.CommandText = " delete from TBL_Park where parkId= '" + dg1[dg1.CurrentRowIndex, 0].ToString() + "'";
                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("删除成功！");
                    //dg1.DataSource = GetLocalData();
                }
                catch (Exception ee)
                {
                    con.Dispose();
                    MessageBox.Show(ee.Message);
                }

            }

        }

        //向服务器提交本地数据
        private void menuItem9_Click(object sender, EventArgs e)
        {
            InitRDa();
            try
            {
                _rda.Push(_localTblName, _serverConnectString, RdaBatchOption.BatchingOn);
                MessageBox.Show("同步上传成功");
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        //直接添加到服务器数据（调用rda的SubmitSql方法，执行服务器上的存储过程，在服务器的表中插入数据）
        private void menuItem15_Click(object sender, EventArgs e)
        {
            InitRDa();
            try
            {
                _rda.SubmitSql("exec HV_SavePark '直发', 2 ,1", _serverConnectString);
                MessageBox.Show("向服务器发送数据成功");
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }



    }




}

转载于:https://www.cnblogs.com/fightLonely/archive/2010/05/18/1738497.html