SqlServerCe 数据库操作示例

 

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;


namespace SmartDeviceTest1
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [MTAThread]
        static void Main()
        {

            CreatDB();

            //Application.Run(new Form1());
        }

        public static void CreatDB()
        {
            SqlCeConnection conn = null;
            try
            {
                if (File.Exists("Test.sdf")) File.Delete("Test.sdf");
                SqlCeEngine engine = new SqlCeEngine("Data Source = Test.sdf");
                engine.CreateDatabase();

                conn = new SqlCeConnection("Data Source = Test.sdf");
                conn.Open();

                SqlCeCommand cmd = conn.CreateCommand();
                cmd.CommandText = "CREATE TABLE TestTbl(col1 int PRIMARY KEY, col2 ntext, col3 money)";

                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO TestTbl(col1, col2, col3) VALUES (0, 'abc', 15.66)";

                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO TestTbl(col1, col2, col3) VALUES (?, ?, ?)";

                cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
                cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NText));
                cmd.Parameters.Add(new SqlCeParameter("p3", SqlDbType.Money));
                cmd.Parameters["p2"].Size = 50;
                cmd.Prepare();
                cmd.Parameters["p1"].Value = 1;
                cmd.Parameters["p2"].Value = "abc";
                cmd.Parameters["p3"].Value = 15.66;
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                cmd.CommandText = "SELECT * FROM TestTbl";

                SqlCeDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    MessageBox.Show("col1 =" + rdr.GetInt32(0) +
                        "col2 =" + rdr.GetString(1) +
                        "col3 =" + rdr.GetSqlMoney(2));
                }


                cmd.CommandText = "UPDATE TestTbl SET col2 = 'some new value' WHERE col1 = 0";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT * FROM TestTbl";
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    MessageBox.Show("col1 = " + rdr.GetInt32(0) +
                    "col2 =  " + rdr.GetString(1) +
                   " col3 =  " + rdr.GetSqlMoney(2));
                }


            }//end try
            catch (SqlCeException ee)
            {
                ShowErrors(ee);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        public static void ShowErrors(SqlCeException e)
        {
            SqlCeErrorCollection errorCollection = e.Errors;
            StringBuilder bld = new StringBuilder();
            foreach (SqlCeError err in errorCollection)
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X"));
                bld.Append("\n Message :" + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source:" + err.Source);
                foreach (int numPar in err.NumericErrorParameters)
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }
                foreach (string errPar in err.ErrorParameters)
                {
                    if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
                }
                MessageBox.Show(bld.ToString());
                bld.Remove(0, bld.Length);
            }
        }

    }


}