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; }

        }
    }
}