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.SqlServerCe;
using System.IO;

namespace sql_demo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlCeConnection connection = new SqlCeConnection();
        DataTable dt = new DataTable();

        private void btnCreateDB_Click_1(object sender, EventArgs e)
        {
            string sdf_name = txtbsdf_file.Text;
            if (File.Exists(sdf_name))
                File.Delete(sdf_name);

            //Create database
            SqlCeEngine engine = new SqlCeEngine("Data Source =" + sdf_name);
            engine.CreateDatabase();

            //Create table
            connection = new SqlCeConnection("Data Source =" + sdf_name);
            connection.Open();
            SqlCeCommand cmd = connection.CreateCommand();
            cmd.CommandText = "CREATE TABLE test(ID nvarchar(50)  PRIMARY KEY, NAME nvarchar(50)  , TEL nvarchar(50))";
            //Execute command
            cmd.ExecuteNonQuery();
            connection.Close();
            this.Text = "database create success!";
        }

        private void insert_Click_1(object sender, EventArgs e)
        {
            DateTime datetime1;
            DateTime datetime2;

            datetime1 = DateTime.Now;
            txtbStartTime.Text = datetime1.ToString();
            
            //Open database
            connection.Open();

            //Insert data to database
            SqlCeCommand createCmd = connection.CreateCommand();

            for (int i = 0; i < Convert.ToInt32(textCount.Text); i++)
            {
                string str1;
                string str2;
                string str3;
                str1 = string.Format("ID{0}", i);
                str2 = string.Format("NAME{0}", i);
                str3 = string.Format("TEL{0}", i);
                string cmd = string.Format("INSERT INTO test (ID, NAME , TEL) VALUES ('{0}', '{1}' , '{2}' )", str1, str2, str3);

                createCmd.CommandText = cmd;
                createCmd.ExecuteNonQuery();  
                if ((i % 100) == 0)                                     
                    this.Text = i.ToString();

            }

            //Close database
            connection.Close();

            datetime2 = DateTime.Now;
            txtbEndTime.Text = datetime2.ToString();
            TimeSpan ts = datetime2 - datetime1;
            txtbTTime.Text = ts.ToString();
            this.Text = "Inserted success!";
        }

        private void btnUpdate_Click_1(object sender, EventArgs e)
        {
            try
            {
                //Open database
                connection.Open();
                
                DateTime datetime3;
                DateTime datetime4;
                datetime3 = DateTime.Now;
                txtbStartTime.Text = datetime3.ToString();

                //Update data to database
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE test SET NAME = 'some new value' WHERE ID = 'ID2'  ";
                cmd.ExecuteNonQuery();

                //Close database
                connection.Close();

                datetime4 = DateTime.Now;
                txtbEndTime.Text = datetime4.ToString();
                TimeSpan ts = datetime4 - datetime3;
                txtbTTime.Text = ts.ToString();
                
                MessageBox.Show("update ok!");
            }

            catch (Exception)
            {
                MessageBox.Show("update error!");

            }
        }

        private void btnDel_Click_1(object sender, EventArgs e)
        {
            try
            {
                //Open database
                connection.Open();
                
                DateTime datetime3;
                DateTime datetime4;
                datetime3 = DateTime.Now;
                txtbStartTime.Text = datetime3.ToString();

                //Delete data from database
                SqlCeCommand cmd = connection.CreateCommand();
                cmd.CommandText = "Delete From test Where ID = 'ID1'   ";
                cmd.ExecuteNonQuery();

                //Close database
                connection.Close();

                datetime4 = DateTime.Now;
                txtbEndTime.Text = datetime4.ToString();
                TimeSpan ts = datetime4 - datetime3;
                txtbTTime.Text = ts.ToString();
                
                MessageBox.Show("delete ok!");

            }

            catch (Exception)
            {
                MessageBox.Show("delete error !");
            }
        }
    }
}