C#用SQL语句操作ACCESS
网上关于C#的ACCESS操作很多，我这儿合在一起，更复杂的没写下去，给新手们一起研究，老鸟请无视。using System;
using System.Collections.Generic;
using
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ADOX;
using System.Data.OleDb;
using
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表示不能空)
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "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;
= "SELECT * INTO File FROM Folder";//SQLCOMMAND语句
//如果只要某些字段过去："SELECT FolderID,SuperiorID INTO File FROM folder"
cmd.ExecuteNonQuery();//执行SQLCMMAD
= "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; }
}
}
}
accesssql.rar
支持
