queryfrm.cs

简单数据库

                    if (res == DialogResult.OK)
                    {
                        incompleteFrm.Close();
                    }
                }
            }
        
        }
        private string FormQueryString()
        {
            dataGridViewQuery.ReadOnly = true;
            DataGridViewTextBoxCell tx = new DataGridViewTextBoxCell();

            tx.Value = dataGridViewQuery.Rows[dataGridViewQuery.RowCount - 2].Cells[dataGridViewQuery.ColumnCount - 1].Value;
            dataGridViewQuery.Rows[dataGridViewQuery.RowCount - 2].Cells[dataGridViewQuery.ColumnCount - 1] = tx;

            StringBuilder sb = new StringBuilder();
            sb.Append("Select * from EmployeeInfo ");
            for (int r = 1; r < dataGridViewQuery.RowCount - 1; r++)
            {
                for (int c = 0; c < dataGridViewQuery.ColumnCount; c++)
                {

                    if (1 == r && 0 == c)
                        sb.Append("where ");
                    else
                    {
                        if (c > 0)
                        {
                            if (dataGridViewQuery[c - 1, r].Value.ToString().Contains("Like") || dataGridViewQuery[c - 1, r].Value.ToString().Contains("Not Like"))
                            {
                                //string
                                sb.Append("'");
                                sb.Append(dataGridViewQuery[c, r].Value.ToString());
                                sb.Append("'");
                            }
                            else
                            {
                                //Integer
                                sb.Append(dataGridViewQuery[c, r].Value.ToString());
                            }
                        }
                        else
                        {
                            //no need to check
                            sb.Append(dataGridViewQuery[c, r].Value.ToString());
                        }
                    }
                    sb.Append(" ");
                }
            }
            MessageBox.Show(sb.ToString());
            return sb.ToString();
        }

        private void listViewQueryResults_ColumnClick(object sender, ColumnClickEventArgs e)
        {
            if (0 == e.Column)
            {
                ListView lv = (ListView)sender;
                MenuItem mListViewItem = new MenuItem();
                mListViewItem.Text = "Field Chooser";
                mListViewItem.Click += new EventHandler(mListViewItem_Click);
                ContextMenu _cMenu = new ContextMenu();

                _cMenu.MenuItems.Add(mListViewItem);
                lv.ContextMenu = _cMenu;                
            }
        }

        private void mListViewItem_Click(object sender, EventArgs e)
        {
            //SelectColumns();
            columnSelector colSelectFrm = new columnSelector();
            
            for (int s = 0; s < _columnListSelected.Count; s++)
                colSelectFrm.listBox2.Items.Add(_columnListSelected[s]);

            if (0 < colSelectFrm.listBox2.Items.Count)
                colSelectFrm.button2.Enabled = true;

            for (int us = 0; us < _columnListNotSelected.Count; us++)
                colSelectFrm.listBox1.Items.Add(_columnListNotSelected[us]);

            if (0 < colSelectFrm.listBox1.Items.Count)
                colSelectFrm.button1.Enabled = true;

            DialogResult res = colSelectFrm.ShowDialog();
            if (res == DialogResult.OK)
            {
                _columnListSelected.Clear();
                for (int s = 0; s < colSelectFrm.listBox2.Items.Count; s++)
                {
                    _columnListSelected.Add(colSelectFrm.listBox2.Items[s].ToString());
                }
                _columnListNotSelected.Clear();
                for (int us = 0; us < colSelectFrm.listBox1.Items.Count; us++)
                {
                    _columnListNotSelected.Add(colSelectFrm.listBox1.Items[us].ToString());
                }
                RefreshListView();
            }
            if (res == DialogResult.Cancel)
            {
                colSelectFrm.Close();
                colSelectFrm = null;
            }
        }
        

        private void RefreshListView()
        {
            ListView.ColumnHeaderCollection headerCol = new ListView.ColumnHeaderCollection(listViewQueryResults);
            headerCol.Clear();
            for (int s = 0; s < _columnListSelected.Count; s++)
                headerCol.Add(_columnListSelected[s].ToString());
            // Clear List View Items
            // Clear Items
            listViewQueryResults.Items.Clear();

            // Add Items again
            // Set the View property of the ListView to details either in design mode or in the code
            listViewQueryResults.View = View.Details;

            // Populate listview
            // Display the number of rows found
            this.lblStatus.Text = "Query Results: " + resultTable.Rows.Count + " results found";
            if (0 < resultTable.Rows.Count)
            {

                ListViewItem lvItem = null;
                foreach (DataRow dr in resultTable.Rows)
                {
                    lvItem = new ListViewItem();
                    ListViewItem.ListViewSubItem itms0 = new ListViewItem.ListViewSubItem(lvItem, dr[_columnListSelected[0].ToString()] != System.DBNull.Value ? dr[_columnListSelected[0].ToString()].ToString() : string.Empty);
                    lvItem.SubItems[0] = itms0;
                    if (1 < _columnListSelected.Count)
                    {
                        for (int c = 1; c < _columnListSelected.Count; c++)
                        {
                            ListViewItem.ListViewSubItem itms1 = new ListViewItem.ListViewSubItem(lvItem, dr[_columnListSelected[c].ToString()] != System.DBNull.Value ? dr[_columnListSelected[c].ToString()].ToString() : string.Empty);
                            lvItem.SubItems.Add(itms1);
                        }
                    }
                    this.listViewQueryResults.Items.Add(lvItem);
                }
            }
        }

        private void listViewQueryResults_DoubleClick(object sender, EventArgs e)
        {
            // Generic Code
            ListView lvResult = (ListView)sender;
            if (0 < lvResult.SelectedItems.Count)
            {
                // Create a new page
                TabPage newPage = new TabPage("Id " + lvResult.SelectedItems[0].Text + " [Result Page]");
                TableLayoutPanel tableLayoutPanelResultTemplate = new TableLayoutPanel();
                // 
                // lblEmployeeID
                //
                lblEmployeeID = new Label();
                lblEmployeeID.AutoSize = true;
                lblEmployeeID.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                lblEmployeeID.Location = new System.Drawing.Point(3, 0);
                lblEmployeeID.Name = "lblEmployeeID";
                lblEmployeeID.Size = new System.Drawing.Size(69, 16);
                lblEmployeeID.TabIndex = 0;
                lblEmployeeID.Text = "Employee ID";
                // 
                // txtEmployeeID
                //
                txtEmployeeID = new TextBox();
                txtEmployeeID.BackColor = System.Drawing.SystemColors.InactiveCaption;
                txtEmployeeID.Location = new System.Drawing.Point(115, 3);
                txtEmployeeID.Name = "txtEmployeeID";
                txtEmployeeID.Text = lvResult.SelectedItems[0].Text;
                txtEmployeeID.ReadOnly = true;
                txtEmployeeID.Size = new System.Drawing.Size(99, 20);
                txtEmployeeID.TabIndex = 1;
                // 
                // lblEmployeeName
                //                 
                lblEmployeeName = new Label();
                lblEmployeeName.AutoSize = true;
                lblEmployeeName.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                lblEmployeeName.Location = new System.Drawing.Point(3, 48);
                lblEmployeeName.Name = "lblEmployeeName";
                lblEmployeeName.Size = new System.Drawing.Size(93, 16);
                lblEmployeeName.TabIndex = 2;
                lblEmployeeName.Text = "Employee Name";
                // 
                // txtEmployeeName
                //            
                txtEmployeeName = new TextBox();
                txtEmployeeName.Location = new System.Drawing.Point(115, 51);
                txtEmployeeName.Name = "txtEmployeeName";

                DataRow[] drSelect = resultTable.Select("EmployeeId = " + lvResult.SelectedItems[0].Text);
                foreach (DataRow dr in drSelect)
                {
                    txtEmployeeName.Text = dr[1] != System.DBNull.Value ? dr[1].ToString() : string.Empty;
                }
                txtEmployeeName.Size = new System.Drawing.Size(99, 20);
                txtEmployeeName.TabIndex = 3;


                // Add controls to new Page
                newPage.Controls.Add(lblEmployeeID);
                newPage.Controls.Add(txtEmployeeID);
                newPage.Controls.Add(lblEmployeeName);
                newPage.Controls.Add(txtEmployeeName);

                // Add tab page to tab Control
                tabControl1.TabPages.Add(newPage);

            }
        }

        private void dataGridViewQuery_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                DataGridView gv = (DataGridView)sender;
                if (gv.SelectedRows.Count > 0)
                {
                    if(gv.CurrentCell.RowIndex > 0)
                    {
                        MenuItem mGridViewItem = new MenuItem();
                        mGridViewItem.Text = "Delete Clause";
                        mGridViewItem.Click += new EventHandler(mGridViewItem_Click);
                        ContextMenu _cMenu = new ContextMenu();

                        _cMenu.MenuItems.Add(mGridViewItem);
                        gv.ContextMenu = _cMenu; 
                    }
                }
            }
        }

        private void mGridViewItem_Click(object sender, EventArgs e)
        {
            if (!dataGridViewQuery.SelectedRows[0].IsNewRow)
            {
                dataGridViewQuery.Rows.Remove(dataGridViewQuery.SelectedRows[0]);
            }
        }

        private void deleteClauseToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dataGridViewQuery.SelectedRows.Count > 0)
            {
                if (dataGridViewQuery.CurrentCell.RowIndex > 0)
                {
                    if (!dataGridViewQuery.SelectedRows[0].IsNewRow)
                    {
                        dataGridViewQuery.Rows.Remove(dataGridViewQuery.SelectedRows[0]);
                        // Re-Create Cell_list
                        cell_list.Clear();
                        for (int r = 1; r < dataGridViewQuery.RowCount - 1; r++)
                        {
                            for (int c = 0; c < dataGridViewQuery.ColumnCount; c++)
                            {
                                DataGVCell cellValue = new DataGVCell(1, 0, true, dataGridViewQuery[c, r].Value.ToString(), true);
                                cell_list.Add(cellValue);
                            }
                        }
                    }
                }
            }
        }        
    }
}