form1.cs

主要实现的功能是：测试查询多表

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;

namespace 测试sql
{
    public partial class Form1 : Form
    {
        OleDbConnection m_OleDbCon;
        bool isCompositorCodeId;
        public Form1()
        {
            InitializeComponent();
            m_OleDbCon = GetDbCon();
            isCompositorCodeId = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //string strSQL = "select code.codename from code ,Equipattr where Equipattr.AttrId = 4 and code.CodeKind = 'AttrObjId' and code.CodeID = Equipattr.AttrObjId";
            //string strSQL = "update Equipattr set  AttrObjId = 1 where AttrId = 1";
            string strSQL = "select codename from code where CodeKind = 'Access'";
            OleDbCommand cmd = new OleDbCommand(strSQL, m_OleDbCon);
            //string strSQL1 = "select codeid from code where CodeKind = 'Access'";
            //OleDbCommand cmd1 = new OleDbCommand(strSQL1, m_OleDbCon);
            //cmd.ExecuteNonQuery();
            OleDbDataReader reader = cmd.ExecuteReader();
            //OleDbDataReader reader1 = cmd1.ExecuteReader();
            //reader.Read();
            string str1,str2;
            System.Windows.Forms.ListViewItem listViewItem1;
            while (reader.Read())
            {
                str1 = reader["codename"].ToString();
                //str2 = reader1["codeid"].ToString();
                listViewItem1 = new System.Windows.Forms.ListViewItem(new string[] {
                                            "Access","",str1}, -1);
                this.listViewEx1.Items.AddRange(new System.Windows.Forms.ListViewItem[] {
                                             listViewItem1});

            }
            strSQL = "select codeid from code where CodeKind = 'Access'";
            cmd = new OleDbCommand(strSQL, m_OleDbCon);
            reader = cmd.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {
                this.listViewEx1.Items[i].SubItems[1].Text = reader["codeid"].ToString();
                ++i;
            }
        }

        /// <summary>
        /// 获取数据库链接
        /// </summary>
        /// <returns></returns>
        private OleDbConnection GetDbCon()
        {
            OleDbConnection OleDbCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= d:\\Gvms\\Gvms.mdb");
            OleDbCon.Open();
            return OleDbCon;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.listViewEx1.Items.Clear();
            string str = this.textBox1.Text;
            string strSQL = "select codename from code where CodeKind = " + "'"+ str + "'";
            OleDbCommand cmd = new OleDbCommand(strSQL, m_OleDbCon);
            //string strSQL1 = "select codeid from code where CodeKind = 'Access'";
            //OleDbCommand cmd1 = new OleDbCommand(strSQL1, m_OleDbCon);
            //cmd.ExecuteNonQuery();
            OleDbDataReader reader = cmd.ExecuteReader();
            //OleDbDataReader reader1 = cmd1.ExecuteReader();
            //reader.Read();
            string str1,str2;
            System.Windows.Forms.ListViewItem listViewItem1;
            while (reader.Read())
            {
                str1 = reader["codename"].ToString();
                //str2 = reader1["codeid"].ToString();
                listViewItem1 = new System.Windows.Forms.ListViewItem(new string[] {
                                            str,"",str1}, -1);
                this.listViewEx1.Items.AddRange(new System.Windows.Forms.ListViewItem[] {
                                             listViewItem1});

            }
            strSQL = "select codeid from code where CodeKind = " + "'" + str + "'";
            cmd = new OleDbCommand(strSQL, m_OleDbCon);
            reader = cmd.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {
                this.listViewEx1.Items[i].SubItems[1].Text = reader["codeid"].ToString();
                ++i;
            }

        }

        private void listViewEx1_ColumnClick(object sender, ColumnClickEventArgs e)
        {
            string asm = this.GetType().Assembly.Location;
            string appPath = asm.Substring(0, asm.LastIndexOf("\\"));
            if (e.Column == 1)
            {
                for (int i = 0; i < this.listViewEx1.Items.Count -1; ++i)
                {
                    int index = i;
                    for (int j = i + 1; j < this.listViewEx1.Items.Count; ++j)
                    {
                        if (isCompositorCodeId == false)
                        {
                            if (Int32.Parse(listViewEx1.Items[i].SubItems[1].Text) < Int32.Parse(listViewEx1.Items[j].SubItems[1].Text))
                            {
                                index = j;
                            }
                        }
                        else
                        {
                            if (Int32.Parse(listViewEx1.Items[i].SubItems[1].Text) > Int32.Parse(listViewEx1.Items[j].SubItems[1].Text))
                            {
                                index = j;
                            }
                        }
                    }

                    if (index != i)
                    {
                        string CodeKind, CodeId, CodeName;
                        CodeKind = listViewEx1.Items[i].SubItems[0].Text;
                        CodeId = listViewEx1.Items[i].SubItems[1].Text;
                        CodeName = listViewEx1.Items[i].SubItems[2].Text;
                        listViewEx1.Items[i].SubItems[0].Text = listViewEx1.Items[index].SubItems[0].Text;
                        listViewEx1.Items[i].SubItems[1].Text = listViewEx1.Items[index].SubItems[1].Text;
                        listViewEx1.Items[i].SubItems[2].Text = listViewEx1.Items[index].SubItems[2].Text;
                        listViewEx1.Items[index].SubItems[0].Text = CodeKind;
                        listViewEx1.Items[index].SubItems[1].Text = CodeId;
                        listViewEx1.Items[index].SubItems[2].Text = CodeName;
                    }
                }

                if (isCompositorCodeId == false)
                    isCompositorCodeId = true;
                else
                    isCompositorCodeId = false;
            }
        }
    }
}