C#操作Access数据库源码

【实例简介】

        基于SQL语句新建，添加，查找，等操作
【实例截图】

    
【核心代码】

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;
using System.Data.OleDb;
using ADOX;
using System.Collections;
using System.IO;

namespace Access数据库
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        #region //access调用方法

        //创建mdb 
        public  bool CreateMDBDataBase(string mdbPath)
        {
            try
            {
                ADOX.CatalogClass cat = new ADOX.CatalogClass();
                cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";");
                cat = null;
                return true;
            }
            catch { return false; }
        }
        // 新建mdb的表 
        //mdbHead是一个ArrayList，存储的是table表中的具体列名。
        public static bool CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)
        {
            try
            {
                ADOX.CatalogClass cat = new ADOX.CatalogClass();
                string sAccessConnection
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath;
                ADODB.Connection cn = new ADODB.Connection();
                cn.Open(sAccessConnection, null, null, -1);
                cat.ActiveConnection = cn;

                //新建一个表 
                ADOX.TableClass tbl = new ADOX.TableClass();
                tbl.ParentCatalog = cat;
                tbl.Name = tableName;

                int size = mdbHead.Count;
                for (int i = 0; i < size; i  )
                {
                    //增加一个文本字段 
                    ADOX.ColumnClass col2 = new ADOX.ColumnClass();
                    col2.ParentCatalog = cat;
                    col2.Name = mdbHead[i].ToString();//列的名称 
                    col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                    tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
                }
                cat.Tables.Append(tbl);   //这句把表加入数据库(非常重要) 
                tbl = null;
                cat = null;
                cn.Close();
                return true;
            }
            catch { return false; }
        }
        //读取mdb文件内数据表个数
        public string[] GetShemaTableName(string mdbPath)
        {     
            try  
            {     
                //获取数据表 
                
                string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath;
                OleDbConnection conn = new OleDbConnection(ConnectionString); 
                conn.Open(); 
                DataTable shemaTable = conn.GetOleDbSchemaTable
                    (OleDbSchemaGuid.Tables, new object[]{ null, null, null, "TABLE" });  
                int n = shemaTable.Rows.Count;
                string[] strTable = new string[n]; 
                int m = shemaTable.Columns.IndexOf("TABLE_NAME"); 
                for (int i = 0; i < n; i  )
                {                  
                    DataRow m_DataRow = shemaTable.Rows[i];  
                    strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString(); 
                }                
                return strTable;  
            }          
            catch (OleDbException ex)  
            {             
                MessageBox.Show("指定的限制集无效:\n"   ex.Message);   
                return null;    
            }   
        }
        //读取表内列个数
        public string[] GetShemaColName(string mdbPath,string tableName)
        {
            try
            {
                //获取数据表 

                string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath;
                OleDbConnection conn = new OleDbConnection(ConnectionString);
                conn.Open();
               
                DataTable shemaTable = conn.GetOleDbSchemaTable
                    (OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
                //foreach (DataRow dr in shemaTable.Rows)
                //{
                //  string s=dr["TABLE_NAME"].ToString();
                //}
                //获取表内列数
                int n = shemaTable.Rows.Count;
                string[] strTable = new string[n];
                int m = shemaTable.Columns.IndexOf("COLUMN_NAME");
                for (int i = 0; i < n; i  )
                {
                    DataRow m_DataRow = shemaTable.Rows[i];
                    strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString();
                }
                 
                return strTable;
            }
            catch (OleDbException ex)
            {
                MessageBox.Show("指定的限制集无效:\n"   ex.Message);
                return null;
            }
        }
        // 读取mdb数据 
        public static DataTable ReadAllData(string tableName, string mdbPath, ref bool success)
        {
            DataTable dt = new DataTable();
            try
            {
                DataRow dr;
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();
                //建立SQL查询 
                OleDbCommand odCommand = odcConnection.CreateCommand();
                //3、输入查询语句 
                odCommand.CommandText = "select * from "   tableName;
                //建立读取 
                OleDbDataReader odrReader = odCommand.ExecuteReader();
                //查询并显示数据 
                int size = odrReader.FieldCount;
                for (int i = 0; i < size; i  )
                {
                    DataColumn dc;
                    dc = new DataColumn(odrReader.GetName(i));
                    dt.Columns.Add(dc);
                }
                while (odrReader.Read())
                {
                    dr = dt.NewRow();
                    for (int i = 0; i < size; i  )
                    {
                        dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                    }
                    dt.Rows.Add(dr);
                }
                //关闭连接 
                odrReader.Close();
                odcConnection.Close();
                success = true;
                return dt;
            }
            catch
            {
                success = false;
                return dt;
            }
        }
        // 读取mdb数据 (按列)
        public static DataTable ReadDataByColumns(string mdbPath, string tableName, string[] columns, ref bool success)
        {
            DataTable dt = new DataTable();
            try
            {
                DataRow dr;
                //1、建立连接 
                string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();
                //建立SQL查询 
                OleDbCommand odCommand = odcConnection.CreateCommand();
                //3、输入查询语句 
                string strColumn = "";
                for (int i = 0; i < columns.Length; i  )
                {
                    strColumn  = columns[i].ToString()   ",";
                }
                strColumn = strColumn.TrimEnd(',');
                odCommand.CommandText = "select "   strColumn   " from "   tableName;
                //建立读取 
                OleDbDataReader odrReader = odCommand.ExecuteReader();
                //查询并显示数据 
                int size = odrReader.FieldCount;
                for (int i = 0; i < size; i  )
                {
                    DataColumn dc;
                    dc = new DataColumn(odrReader.GetName(i));
                    dt.Columns.Add(dc);
                }

                while (odrReader.Read())
                {
                    dr = dt.NewRow();
                    for (int i = 0; i < size; i  )
                    {
                        dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                    }
                    dt.Rows.Add(dr);
                }
                //关闭连接 
                odrReader.Close();
                odcConnection.Close();
                success = true;
                return dt;
            }
            catch
            {
                success = false;
                return dt;
            }
        }
        //普通的节点 
        public struct Node
        {
            private string nodeType;
            public string NodeType//表的字段名 
            {
                set { nodeType = value; }
                get { return nodeType; }
            }

            private string nodeValue;
            public string NodeValue//具体的值 
            {
                set { nodeValue = value; }
                get { return nodeValue; }
            }
        }

        //照片节点 
        public struct PictureNode
        {
            private string nodeType;
            public string NodeType//照片的列名 
            {
                set { nodeType = value; }
                get { return nodeType; }
            }

            private byte[] nodeValue;
            public byte[] NodeValue//照片的值，注意类型 
            {
                set { nodeValue = value; }
                get { return nodeValue; }
            }
        }
        /* //另外的写法有错误
          
        //插入数据 
        public static bool InsertRow(string mdbPath, string tableName, ArrayList insertArray,
             PictureNode pictureNode, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();

                string str_col = "";
                int size_col = insertArray.Count;
                for (int i = 0; i < size_col; i  )
                {
                    Node vipNode = new Node();
                    vipNode = (Node)insertArray[i];
                    str_col  = vipNode.NodeType   ",";
                }
                str_col = str_col.TrimEnd(',');


                int size_row = insertArray.Count;
                string str_row = "";
                for (int i = 0; i < size_row; i  )
                {
                    Node vipNode = new Node();
                    vipNode = (Node)insertArray[i];
                    string v = vipNode.NodeValue.ToString();
                    v = DealString(v);
                    if (v == "")
                    {
                        str_row  = "null"   ',';
                    }
                    else
                    {
                        str_row  = "'"   v   "'"   ',';
                    }
                }
                str_row = str_row.TrimEnd(',');
                if (pictureNode != null && pictureNode.NodeValue != null)
                {
                    str_col  = ','   pictureNode.NodeType;
                    str_row  = ",@Image";
                }
                string sql = "insert into "   tableName   @" ("   str_col   ") values"   @"("   str_row   ")";
                OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);
                if (pictureNode != null && pictureNode.NodeValue != null)
                {
                    odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;
                }
                odCommand.ExecuteNonQuery();
                odcConnection.Close();
                return true;
            }
            catch (Exception err)
            {
                errinfo = err.Message;
                return false;
            }
        }
        //更新一行数据 
        public static bool UpdateRow(string mdbPath, string tableName,
            Node keyNode, ArrayList insertArray, PictureNode pictureNode, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();

                int size = insertArray.Count;
                string str = "";
                for (int i = 0; i < size; i  )
                {
                    Node node = new Node();
                    node = (Node)insertArray[i];
                    string v = node.NodeValue.ToString();
                    v = DealString(v);
                    str  = node.NodeType   " = ";
                    if (v == "")
                    {
                        str  = "null"   ',';
                    }
                    else
                    {
                        str  = "'"   v   "'"   ',';
                    }

                }
                str = str.TrimEnd(',');
                if (pictureNode.NodeValue != null)
                {
                    str  = ','   pictureNode.NodeType;
                    str  = " = @Image";
                }
                string sql = "update "   tableName   " set "   str  
                    " where "   keyNode.NodeType   " = "   "'"   keyNode.NodeValue   "'";
                OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);
                if (pictureNode.NodeValue != null)
                {
                    odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;
                }
                odCommand.ExecuteNonQuery();
                odcConnection.Close();
                return true;
            }
            catch (Exception err)
            {
                errinfo = err.Message;
                return false;
            }
        }
       */
        //插入图片数据 
        public static bool InsertPictureToMDB(string mdbPath, string tableName, Node keyNode,PictureNode pictureNode, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();
                string sql = @"update "   tableName   " set "   pictureNode.NodeType   "="
                      "@Image where "   keyNode.NodeType   "="   "'"   keyNode.NodeValue   "'";

                OleDbCommand comm = new OleDbCommand(sql, odcConnection);
                byte[] pic = pictureNode.NodeValue;
                comm.Parameters.Add("@Image", OleDbType.VarBinary, pic.Length).Value = pic;
                comm.ExecuteNonQuery();
                odcConnection.Close();
                return true;
            }
            catch (Exception err)
            {
                errinfo = err.Message;
                return false;
            }
        }
        //从mdb中获得照片 
        public static byte[] GetImageFromMDB(string tableName,string pictureCol, Node keyNode)
        {
            try
            {
                string sql = "Select "   pictureCol   " From "   tableName  
                    " member Where "   keyNode.NodeType   " = "
                      "'"   keyNode.NodeValue   "'";
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();
                OleDbCommand comm = new OleDbCommand(sql, odcConnection);
                OleDbDataReader sdr = comm.ExecuteReader();
                sdr.Read();

                byte[] pic = (byte[])sdr[0];
                sdr.Close();
                odcConnection.Close();
                return pic;
            }
            catch
            {
                return null;
            }
        }
        //修改mdb的一条数据 
        public static bool UpdateMDBNode(string mdbPath, string tableName, Node keyNode, Node saveNode, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 
                odcConnection.Open();

                string sql = @"update "   tableName   " set "   saveNode.NodeType   " = '"   saveNode.NodeValue  
                    "' where "   keyNode.NodeType   " = "   "'"   keyNode.NodeValue   "'";
                OleDbCommand comm = new OleDbCommand(sql, odcConnection);
                comm.ExecuteNonQuery();
                odcConnection.Close();
                return true;
            }
            catch (Exception err)
            {
                errinfo = err.Message;
                return false;
            }
        }
         //插入数据 
        public static bool InsertRow(string mdbPath, string tableName, string[] colArray, string[] dataArray, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接
                odcConnection.Open();
                string col="";
                string data="";
                for (int i = 0; i < colArray.Length; i  )
                {
                    if (i==colArray.Length-1)
                    {
                        col = col   colArray[i];
                    }
                    else
                    {
                        col = col   colArray[i]   ",";
                    }
                }
                for (int i = 0; i < colArray.Length; i  )
                {
                    if (i == colArray.Length - 1)
                    {
                        data = data   "'"   dataArray[i]   "'";
                    }
                    else
                    {
                        data = data   "'"   dataArray[i]   "'"   ",";
                    }
                }
                string sql = "insert into "   tableName   " ("   col   ") values ("   data ")";
                OleDbCommand comm = new OleDbCommand(sql, odcConnection);
                comm.ExecuteNonQuery();
                odcConnection.Close();

                return true;
            }
            catch
            {
                return false;
            }
        }
        //删除数据 
        public static bool DeleteData(string mdbPath, string tableName, Node keyNode, ref string errinfo)
        {
            try
            {
                //1、建立连接 
                string strConn
                    = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   mdbPath   ";Jet OLEDB:Database Password=haoren";
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接
                odcConnection.Open();
                string  col=keyNode.NodeType;
                string  value=keyNode.NodeValue;
                string sql = "delete from "   tableName   " where "   col   "='"   value   "'";
                //string sql = "delete from "   tableName;
                OleDbCommand comm = new OleDbCommand(sql, odcConnection);
                comm.ExecuteNonQuery();
                odcConnection.Close();

                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion
       # region
        static string mdbPath ="";
        static string tableName = "";
        static string[] strCol=new  string[4];
        private void button2_Click(object sender, EventArgs e)
        {
            //选择创建路径
            saveFileDialog1.InitialDirectory = Environment.CurrentDirectory   "\\..\\..\\..\\";
            saveFileDialog1.ShowDialog();
            textBox1.Text = saveFileDialog1.FileName;
            mdbPath = textBox1.Text;
            CreateMDBDataBase(mdbPath);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            tableName = textBox3.Text;
            int col =Convert .ToInt32( numericUpDown1.Value);
            ArrayList a = new ArrayList(col);
            for (int i = 0; i < col; i  )
            {
                a.Add(strCol[i]);
            }
            CreateMDBTable(mdbPath, tableName, a);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            tableName = comboBox2.Text;
            bool bo1=false;
            DataTable dataTable=new DataTable();
            dataTable = ReadAllData(tableName, mdbPath, ref bo1);
            dataGridView1.DataSource = dataTable;
        }

        private void button5_Click(object sender, EventArgs e)
        {
            tableName = comboBox2.Text;
            DataTable dataTable=new DataTable();
            bool bo1=false;
            string[] columns={comboBox1.Text};
            dataTable= ReadDataByColumns( mdbPath,  tableName, columns, ref bo1);
            dataGridView1.DataSource = dataTable;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string bol = "";
            Node a=new Node();
            Node b = new Node();
            a.NodeType=textBox8.Text;
            a.NodeValue=textBox9.Text;
            b.NodeType = textBox8.Text;
            b.NodeValue=textBox10.Text;
            UpdateMDBNode(mdbPath, tableName, a, b, ref bol);
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string bol = "";
            string[] colArray = GetShemaColName(mdbPath, tableName);
            string[] dataArray = { textBox5.Text, textBox6.Text, textBox7.Text };
            InsertRow(mdbPath, tableName, colArray,dataArray, ref bol);
        }

        private void button9_Click(object sender, EventArgs e)
        {
            string bol = "";
            Node a = new Node();
            a.NodeType = textBox11.Text;
            a.NodeValue = textBox12.Text;
           DeleteData (mdbPath, tableName, a, ref bol);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.InitialDirectory = Environment.CurrentDirectory   "\\..\\..\\..\\";
            openFileDialog1.ShowDialog();
            textBox1.Text = openFileDialog1.FileName;
            mdbPath = textBox1.Text;
            
            string[] strA = GetShemaTableName(mdbPath);
            textBox2.Text = strA.Length.ToString();
            for (int i = 0; i < strA.Length; i  )
            {
                comboBox2.Items.Add(strA[i ]);
            }
           
        }

        private void textBox4_Leave(object sender, EventArgs e)
        {
            int col = Convert.ToInt32(numericUpDown2.Value);
            strCol[col] = textBox4.Text;
        }

        private void comboBox1_Enter(object sender, EventArgs e)
        {
            if (comboBox1.Text=="")
            {
                //MessageBox.Show("");
                string[] strA = GetShemaColName(mdbPath, tableName);
                for (int i = 0; i < strA.Length; i  )
                {
                    comboBox1.Items.Add(strA[i]);
                }
            }
            
        }

        private void comboBox2_Leave(object sender, EventArgs e)
        {
            tableName = comboBox2.Text;
        }
       #endregion
        private void button8_Click(object sender, EventArgs e)
        {
            Node keyNode = new Node();
            PictureNode pictureNode = new PictureNode();
            string errinfo = "";
            FileStream myStream = new FileStream(openPicture.FileName, FileMode.Open, FileAccess.Read);
            byte[] imagebytes = new byte[myStream.Length];//fs.Length文件流的长度，用字节表示 
            BinaryReader br = new BinaryReader(myStream);//二进制文件读取器 
            imagebytes = br.ReadBytes(Convert.ToInt32(myStream.Length));//从当前流中将count个字节读入字节数组中 
            myStream.Close();           
            keyNode.NodeType = textBox15.Text;
            keyNode.NodeValue = textBox16.Text;
            pictureNode.NodeType = textBox13.Text;
            pictureNode.NodeValue = imagebytes;
            InsertPictureToMDB(mdbPath, tableName, keyNode, pictureNode,ref errinfo);
        }
        OpenFileDialog openPicture = new OpenFileDialog();
        private void textBox14_Enter(object sender, EventArgs e)
        {
            openPicture.Filter = "Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* ";
            openPicture.ShowDialog();
            textBox14.Text = openPicture.FileName;
            //openPicture.OpenFile();
        }

        private void button10_Click(object sender, EventArgs e)
        {
            Node keyNode = new Node();
            keyNode.NodeType = textBox15.Text;
            keyNode.NodeValue = textBox16.Text;
            string pictureCol = textBox13.Text;
            byte[] pictureByt= GetImageFromMDB(tableName, pictureCol, keyNode);
            MemoryStream stream = new MemoryStream(pictureByt, true);
            stream.Write(pictureByt, 0, pictureByt.Length); 
            pictureBox1.Image = new Bitmap(stream); stream.Close();
        }

    }
    
}