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

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

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

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 創(chuàng)建access_Click(object sender, EventArgs e)
        {
            //創(chuàng)建ACCESS文件需要ADOX，創(chuàng)建引用，導入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文件，放棄創(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;//設置命令的對像是SLDB數(shù)據(jù)庫
                //建表Folder,括號內(nè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語句
                cmd.ExecuteNonQuery();//執(zhí)行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();//執(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語句
                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語句
                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語句
                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語句
                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語句
                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("沒有找到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();//執(zhí)行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();//執(zhí)行SQLCMMAD，ExecuteScalar是反回一個行第一列的結果,
                MessageBox.Show("Folder表中FolderID為1001的記錄，F(xiàn)olderGrade項的值是："+ 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)增加一個字段，名字為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();//執(zhí)行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內(nèi)增加一個字段，名字為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();//執(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沒有直接改名方法，所以用復制表，然后再刪除舊表的方法。
            //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();//執(zhí)行SQLCMMAD
                cmd.CommandText = "DROP TABLE Folder";//SQLCOMMAND語句
                cmd.ExecuteNonQuery();//執(zhí)行SQLCMMAD
                MessageBox.Show("將Folder表改名為File");
            }
            catch (Exception ex)
            {
                MessageBox.Show(Convert.ToString(ex.Message));
            }
            finally { sldb.Close(); sldb = null; }

        }
    }
}