Dotnet 的C#用SQL語句直接操作ACCESS的相關示例

Dotnet 的C#用SQL語句直接操作ACCESS的相關示例
C#操作ACCESS的基本操作方法示例，主要給C#新手操控Access數據庫的入門

有這方麵需要的網友可以蔘考一下，主要是對Access數據庫的增刪改查等操作

C#操作Access的具體代碼如下

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

namespace accesssql
{
    public partial class access練習 : Form
    {
        public access練習()
        {
            InitializeComponent();
        }

        private void 創建access_Click(object sender, EventArgs e)
        {
            //創建ACCESS文件需要ADOX，創建引用，導入COM中的Microsoft ADO Ext. 2.8 for DLL and Security，併using ADOX;
            FileInfo myfile = new FileInfo("sldb.mdb");
            if (myfile.Exists)
            { MessageBox.Show("當前文件夾下已存在sldb.mdb文件，放棄創建"); }
            else
            {
                try
                {
                    CatalogClass sldb = new CatalogClass();
                    sldb.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=sldb.mdb;" + "Jet OLEDB:Engine Type=5");//創建sldb.mdb
                    sldb = null;
                    MessageBox.Show("在程序文件夾下創建sldb.mdb成功。");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(Convert.ToString(ex.Message));
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;//設置命令的對像是SLDB數據庫
                //建錶Folder,括號內爲:字段1 類型,字段2 類型,......(NOT NULL錶示不能空)
                cmd.CommandText = "Create Table Folder (FolderID INT NOT NULL,SuperiorID INT,FolderGrade INT,FolderName TEXT(255),AllPathName TEXT(255))";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("增加錶Folder，錶項FolderID SuperiorID FolderGrade FolderName成功");
            }
            catch (Exception ex)
                {
                    MessageBox.Show(Convert.ToString(ex.Message));
                }
            finally { sldb.Close();sldb =null;}
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //清除錶格
            //DROP TABLE "錶格名"
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "DROP TABLE Folder";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("刪除Folder錶成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            //增加一條記録
            //INSERT INTO "錶格名" ("欄位1", "欄位2", ...) VALUES ("值1", "值2", ...) 
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "INSERT INTO Folder (FolderID,SuperiorID,FolderGrade,FolderName,AllPathName) VALUES (1001,2001,3001,'SLonline','SL1')";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("增加錶Folder內記録一條成功");
            }
            catch (Exception ex)
                {
                    MessageBox.Show(Convert.ToString(ex.Message));
                }
            finally { sldb.Close();sldb =null;}
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //修改某條記録
            //UPDATE "錶格名" SET "欄位1" = [新值] WHERE {條件}
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "UPDATE Folder SET AllPathName = 'SL2' WHERE FolderID = 1001";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("修改錶Folder內記録一條成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //刪除記録
            //DELETE FROM "錶格名" WHERE {條件}
           
            //先添加一條記録
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "INSERT INTO Folder (FolderID,SuperiorID,FolderGrade,FolderName,AllPathName) VALUES (1003,2003,3003,'SLonline','SL3')";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("增加錶Folder內記録一條成功");
            }
            catch (Exception ex)
                {
                    MessageBox.Show(Convert.ToString(ex.Message));
                }
            finally { sldb.Close();sldb =null;}
            
            //再把這條記録刪除
            OleDbConnection sldb1 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb1.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb1;
                cmd.CommandText = "DELETE FROM Folder WHERE FolderID = 1001";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("刪除FolderID o 1001的記録成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb1.Close(); sldb1 = null; }

        }

        private void button6_Click(object sender, EventArgs e)
        {
            //清除錶格內的所有信息
            //DELETE   *   FROM   錶1;
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "DELETE * FROM Folder";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("清空錶Folder內記録成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }

        private void button8_Click(object sender, EventArgs e)
        {
            if (File.Exists("sldb.mdb"))
            {
                try
                {
                    File.Delete("sldb.mdb");
                    MessageBox.Show("文件sldb.mdb刪除成功。");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(Convert.ToString(ex.Message));
                }
            }
            else { MessageBox.Show("沒有找到sldb.mdb文件"); }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "SELECT COUNT(*) FROM Folder";//SQLCOMMAND語句
                int number=(int)cmd.ExecuteScalar();//執行SQLCMMAD，ExecuteScalar是反迴一箇行第一列的結果
                MessageBox.Show("Folder錶中共有" + Convert.ToString(number));
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "SELECT FolderGrade FROM Folder WHERE FolderID = 1001";//SQLCOMMAND語句
                int number = (int)cmd.ExecuteScalar();//執行SQLCMMAD，ExecuteScalar是反迴一箇行第一列的結果,
                MessageBox.Show("Folder錶中FolderID爲1001的記録，FolderGrade項的值是："+ Convert.ToString(number));
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }

        private void button10_Click(object sender, EventArgs e)
        {
            //ALTER TABLE Cars ADD COLUMN Condition TEXT(10)
            //在錶Cars內增加一箇字段，名字爲Condition（狀況），長度爲10
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "ALTER TABLE Folder ADD COLUMN readme TEXT(255)";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("Folder錶中增加readme字段，文本型長度255");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }
        private void button11_Click(object sender, EventArgs e)
        {
            //ALTER TABLE Cars ADD COLUMN Condition TEXT(10)
            //在錶Cars內增加一箇字段，名字爲Condition（狀況），長度爲10
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "ALTER TABLE Folder DROP readme";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("Folder錶中刪除瞭readme字段");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }

        private void button13_Click(object sender, EventArgs e)
        {
            //除非用ADOX，ADO沒有直接改名方法，所以用複製錶，然後再刪除舊錶的方法。
            //SELECT * INTO newtable FROM oldtable
            OleDbConnection sldb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sldb.mdb");
            try
            {
                sldb.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = sldb;
                cmd.CommandText = "SELECT * INTO File FROM Folder";//SQLCOMMAND語句
                //如果隻要某些字段過去："SELECT FolderID,SuperiorID INTO File FROM folder"
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                cmd.CommandText = "DROP TABLE Folder";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執行SQLCMMAD
                MessageBox.Show("將Folder錶改名爲File");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }
    }
}