WinCE下对数据库的操作

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using System.Data.SqlServerCe;

using System.IO;



namespace ReadSDFFileOnDesktop

{

	public partial class MainForm : Form

	{

		public static string strSQLPath = "DB.sdf";

		public static string strConString = "Data Source = " + strSQLPath + "; Password = ";

		public static string strAccessString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Pub.mdb;";



		public MainForm()

		{

			InitializeComponent();

		}



		private void buttonCreateDB_Click(object sender, EventArgs e)

		{

			// 创建数据库

			if (File.Exists(strSQLPath))

			{

				// 删除数据库

				File.Delete(strSQLPath);

				CreateDataBase();

			}

			else

			{

				// 创建数据库

				CreateDataBase();

			}

		}



		private void CreateDataBase()

		{

			// 创建数据库文件

			SqlCeEngine engine = new SqlCeEngine(strConString);

			engine.CreateDatabase();

			engine.Dispose();

			MessageBox.Show("数据库创建成功！");

		}



		private void buttonCreateTable_Click(object sender, EventArgs e)

		{

			if (File.Exists(strSQLPath))

			{

				// 创建表

				SqlCeConnection cn = new SqlCeConnection(strConString);

				cn.Open();

				try

				{

					string strSQL = @"CREATE TABLE employee(emp_id nvarchar(9), 

                                    fname nvarchar(20),minit nvarchar(1), lname nvarchar(30),

                                    job_id nvarchar(8),job_lvl nvarchar(8), pub_id nvarchar(4),

                                    hire_date datetime)";



					SqlCeCommand SqlCeCmd = new SqlCeCommand(strSQL, cn);

					SqlCeCmd.ExecuteNonQuery();



					MessageBox.Show("数据库中的表创建成功", "提示信息");

				}

				catch (SqlCeException ex)

				{

					MessageBox.Show(ex.Message);

				}

				finally

				{

					cn.Close();

				}

			}

			else

			{

				MessageBox.Show("请先创建数据库！");

			}

		}



		private void buttonImportData_Click(object sender, EventArgs e)

		{

			// 导入数据

			OleDbConnection OleDbCn = new OleDbConnection(strAccessString);

			try

			{

				OleDbCn.Open();

				OleDbCommand OleDbCmd = new OleDbCommand("SELECT * FROM employee", OleDbCn);

				OleDbDataReader OleDbDr = OleDbCmd.ExecuteReader();



				SqlCeConnection SqlCeCn = new SqlCeConnection(strConString);

				try

				{

					SqlCeCn.Open();

					SqlCeCommand SqlCeCmd = new SqlCeCommand();

					SqlCeCmd.Connection = SqlCeCn;



					while (OleDbDr.Read())

					{

						string strSQL = "";

						strSQL = String.Format("INSERT INTO employee " +

							"(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date) 

                                VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",

							OleDbDr.GetValue(OleDbDr.GetOrdinal("emp_id")), 

                            OleDbDr.GetValue(OleDbDr.GetOrdinal("fname")),

							OleDbDr.GetValue(OleDbDr.GetOrdinal("minit")), 

                            OleDbDr.GetValue(OleDbDr.GetOrdinal("lname")),

							OleDbDr.GetValue(OleDbDr.GetOrdinal("job_id")), 

                            OleDbDr.GetValue(OleDbDr.GetOrdinal("job_lvl")),

							OleDbDr.GetValue(OleDbDr.GetOrdinal("pub_id")),

                            OleDbDr.GetValue(OleDbDr.GetOrdinal("hire_date")));

						SqlCeCmd.CommandText = strSQL;

						SqlCeCmd.ExecuteNonQuery();

					}

					MessageBox.Show("数据导入成功！");

				}

				catch (SqlCeException ex)

				{

					MessageBox.Show(ex.Message);

				}

				finally

				{

					SqlCeCn.Close();

				}

			}

			catch (OleDbException ex)

			{

				MessageBox.Show(ex.Message);

			}

			finally

			{

				OleDbCn.Close();

			}

		}



		private void buttonShowData_Click(object sender, EventArgs e)

		{

			// 显示数据

			SqlCeConnection SqlCeCn = new SqlCeConnection(strConString);

			try

			{

				SqlCeCn.Open();

				string strSQL = "";

				strSQL = String.Format("SELECT * FROM employee ");

				SqlCeCommand SqlCeCmd = new SqlCeCommand(strSQL, SqlCeCn);

				SqlCeDataReader SqlCeDr = SqlCeCmd.ExecuteReader();



				listView1.Items.Clear();

				while (SqlCeDr.Read())

				{

					ListViewItem item;

					item = new ListViewItem(SqlCeDr.GetValue

                        (SqlCeDr.GetOrdinal("emp_id")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("fname")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("minit")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("lname")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr

                        .GetOrdinal("job_id")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("job_lvl")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("pub_id")).ToString());

					item.SubItems.Add(SqlCeDr.GetValue(SqlCeDr.

                        GetOrdinal("hire_date")).ToString());

					

					listView1.Items.Add(item);

				}

			}

			catch (SqlCeException ex)

			{

				MessageBox.Show(ex.Message);

			}

			finally

			{

				SqlCeCn.Close();

			}

		}

	}

}