Dotnet 的C#用SQL語(yǔ)句直接操作ACCESS的相關(guān)示例

Dotnet 的C#用SQL語(yǔ)句直接操作ACCESS的相關(guān)示例
C#操作ACCESS的基本操作方法示例，主要給C#新手操控Access數(shù)據(jù)庫(kù)的入門

有這方麵需要的網(wǎng)友可以蔘考一下，主要是對(duì)Access數(shù)據(jù)庫(kù)的增刪改查等操作

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練習(xí) : Form
    {
        public access練習(xí)()
        {
            InitializeComponent();
        }

        private void 創(chuàng)建access_Click(object sender, EventArgs e)
        {
            //創(chuàng)建ACCESS文件需要ADOX，創(chuàng)建引用，導(dǎo)入COM中的Microsoft ADO Ext. 2.8 for DLL and Security，併using ADOX;
            FileInfo myfile = new FileInfo("sldb.mdb");
            if (myfile.Exists)
            { MessageBox.Show("當(dāng)前文件夾下已存在sldb.mdb文件，放棄創(chuàng)建"); }
            else
            {
                try
                {
                    CatalogClass sldb = new CatalogClass();
                    sldb.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=sldb.mdb;" + "Jet OLEDB:Engine Type=5");//創(chuàng)建sldb.mdb
                    sldb = null;
                    MessageBox.Show("在程序文件夾下創(chuàng)建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;//設(shè)置命令的對(duì)像是SLDB數(shù)據(jù)庫(kù)
                //建錶Folder,括號(hào)內(nèi)爲(wèi):字段1 類型,字段2 類型,......(NOT NULL錶示不能空)
                cmd.CommandText = "Create Table Folder (FolderID INT NOT NULL,SuperiorID INT,FolderGrade INT,FolderName TEXT(255),AllPathName TEXT(255))";//SQLCOMMAND語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("增加錶Folder，錶項(xiàng)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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("增加錶Folder內(nèi)記録一條成功");
            }
            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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("修改錶Folder內(nèi)記録一條成功");
            }
            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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("增加錶Folder內(nèi)記録一條成功");
            }
            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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行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)
        {
            //清除錶格內(nèi)的所有信息
            //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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("清空錶Folder內(nèi)記録成功");
            }
            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("沒(méi)有找到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語(yǔ)句
                int number=(int)cmd.ExecuteScalar();//執(zhí)行SQLCMMAD，ExecuteScalar是反迴一箇行第一列的結(jié)果
                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語(yǔ)句
                int number = (int)cmd.ExecuteScalar();//執(zhí)行SQLCMMAD，ExecuteScalar是反迴一箇行第一列的結(jié)果,
                MessageBox.Show("Folder錶中FolderID爲(wèi)1001的記録，F(xiàn)olderGrade項(xiàng)的值是："+ 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內(nèi)增加一箇字段，名字爲(wèi)Condition（狀況），長(zhǎng)度爲(wèi)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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("Folder錶中增加readme字段，文本型長(zhǎng)度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內(nèi)增加一箇字段，名字爲(wèi)Condition（狀況），長(zhǎng)度爲(wèi)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語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行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沒(méi)有直接改名方法，所以用複製錶，然後再刪除舊錶的方法。
            //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語(yǔ)句
                //如果隻要某些字段過(guò)去："SELECT FolderID,SuperiorID INTO File FROM folder"
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                cmd.CommandText = "DROP TABLE Folder";//SQLCOMMAND語(yǔ)句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("將Folder錶改名爲(wèi)File");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }
    }
}