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