开发环境：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);
}
}
}
}
