datamanipulation.cs

sqlce查询分析器

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;

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

namespace CEQuery
{
    public partial class DataManipulation : Form
    {        
        public DataManipulation()
        {
            InitializeComponent();
        }
        public DataOperation Operation { get; set; }
        public string TableName { get; set; }
        public string Query { get; private set; }
        public string ConnectionString { get; set; }
        private void DataManipulation_Load(object sender, EventArgs e)
        {
            try
            {
                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT Column_name, data_type, is_nullable,'' AS VALUE FROM information_schema.columns WHERE table_Name='" + TableName + "'";

                        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                        DataSet dsResult = new DataSet();
                        da.Fill(dsResult);
                        dgvTable.DataSource = dsResult.Tables[0];                       
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void btnSave_Click(object sender, EventArgs e)
        {
            bool isValid = true;
            foreach (DataGridViewRow row in dgvTable.Rows)
            {
                if (row.Cells[2].Value.ToString().ToUpper() == "NO" && string.IsNullOrEmpty(row.Cells[3].Value.ToString()))
                {
                    isValid = false;
                    break;
                }
            }
            if (!isValid)
            {
                MessageBox.Show("Please enter value for the non-nullable Columns");
                return;
            }
           
            btnHidden.PerformClick();
        }
        private void generateGUIDToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                
                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT newid()";
                        dgvTable.SelectedRows[0].Cells[3].Value = cmd.ExecuteScalar().ToString();
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void dgvTable_MouseUp(object sender, MouseEventArgs e)
        {
            DataGridView.HitTestInfo Hti;
            Hti = dgvTable.HitTest(e.X, e.Y);
            
            if (e.Button == MouseButtons.Right 
                && Hti.ColumnIndex == 3 && 
                (dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "UNIQUEIDENTIFIER"
                || dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "DATETIME"))
            {
                if (Hti.Type == DataGridViewHitTestType.Cell)
                {
                    if (!((DataGridViewRow)(dgvTable.Rows[Hti.RowIndex])).Selected)
                    {
                        dgvTable.ClearSelection();
                        ((DataGridViewRow)dgvTable.Rows[Hti.RowIndex]).Selected = true;
                    }
                    if (dgvTable.Rows[Hti.RowIndex].Cells[1].Value.ToString().ToUpper() == "UNIQUEIDENTIFIER")
                    {
                        ctxGrid.Items[0].Enabled = true;
                        ctxGrid.Items[1].Enabled = false;
                    }
                    else
                    {
                        ctxGrid.Items[0].Enabled = false;
                        ctxGrid.Items[1].Enabled = true;
                    }
                    ctxGrid.Visible = true;
                }
            }
            else
            {
                ctxGrid.Visible = false;
            }
        }
        private void generateDateTimeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {

                if (ConnectionString != string.Empty)
                {
                    using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                    {
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = connection;
                        connection.Open();
                        cmd.CommandText = "SELECT getdate()";
                        dgvTable.SelectedRows[0].Cells[3].Value = cmd.ExecuteScalar().ToString();
                    }
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void generateInsertQuery()
        {
            Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
            foreach (DataGridViewRow row in dgvTable.Rows)
            {

                keyValuePairs.Add(row.Cells[0].Value.ToString(), row.Cells[3].Value.ToString());
            }
            string cols = string.Empty;
            string vals = string.Empty;
            foreach (string key in keyValuePairs.Keys)
            {
                if (keyValuePairs[key] != string.Empty)
                {
                    cols += cols == string.Empty ? string.Concat("[",key,"]") : string.Concat(",[", key, "]");
                    vals += vals == string.Empty ? string.Concat("'", keyValuePairs[key], "'") : string.Concat(",", "'", keyValuePairs[key], "'");
                }
            }
            Query = string.Concat("INSERT INTO [", TableName, "](", cols, ") VALUES (", vals, ")");
            return;
        }
        private void btnHidden_Click(object sender, EventArgs e)
        {
            generateInsertQuery();
        }        
    }
    public enum DataOperation
    {
        INSERT,
        UPDATE,
        DELETE
    }
}