c# 数据库操作之ACCESS
c#连接access数据库
注意一般要写成类MDBHelp：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
OleDbConnection dbconn;
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="strConnection">数据库名字</param>
private void linkdb(string strConnection)
{
dbconn = new OleDbConnection(strConnection); //dbcoon已设全局变量，
dbconn.Open();//建立连接
}
private void button1_Click(object sender, EventArgs e)
{
#region
// MessageBox.Show("Hello~~~~");
// Class1 c1 = new Class1();
// c1.Name = "葫芦娃";
#endregion
linkdb("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\practice\\ACCESS\\demo1.mdb");
string sql = "select * from 姓名表";
string instert_str = "insert into 姓名表(姓名,年龄,性别) values ('" + "葫芦娃" +"','"+"16"+"','"+"M"+"')";
OleDbCommand myCommand = new OleDbCommand(instert_str, dbconn);//执行命令
myCommand.ExecuteNonQuery();
OleDbDataAdapter inst = new OleDbDataAdapter(sql, dbconn);
DataSet ds = new DataSet();//临时存储
inst.Fill(ds);//用inst 填充ds
dataGridView1.DataSource = ds.Tables[0];//展示ds第一张表
dbconn.Close();//关闭连接
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
增删改查
增
string instert_str = "insert into 姓名表(姓名,年龄,性别) values ('" + "葫芦娃" +"','"+"16"+"','"+"M"+"')
OleDbCommand myCommand = new OleDbCommand(Insert, dbconn);//执行命令
myCommand.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功
注：可以用textbox.text 作为输入值
注意 输入语句的写法 : '"+"str"+"'
删
string s = "'" + textBox1.Text + "'";//接受textBox1的字符串 string Delete = "DELETE FROM student WHERE studentName = "+ s; //delete from 表名 where 字段名='字段值';以上代码执行后会将所有studentName为textbox中内容的行删除 OleDbCommand myCommand = new OleDbCommand(Delete, dbconn);//执行命令 myCommand.ExecuteNonQuery(); //更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功
改
string s = "'" + textBox1.Text + "'", x = "'" + textBox2.Text + "'"; 接受textBox的字符串
string Update = "UPDATE student SET studentName=" + x + "WHERE studentName = " + s;
//update 表名 set 字段名='字段值' where 字段值='字段值';上一行代码执行后将所有studentName中的s替换为x
OleDbCommand myCommand = new OleDbCommand(Update, dbconn);//执行命令
myCommand.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功
查
string s = "'" + textBox1.Text + "'";//接受textBox1的字符串
string Select = "SELECT *FROM student WHERE studentName = " + s;
//select *from 表名 where 字段名='字段值';*表示全表，从全表中
OleDbDataAdapter inst = new OleDbDataAdapter(Select, dbconn);//只匹配满足条件的行
inst.FILL(ds);
查询后：
MessageBox.Show(ds.Tables[0].Rows[0]["studentNO"].ToString());
//这行代码可以展示ds中第一张表(Tables[0])第一行(Rows[0])["字段名"]的信息;在查找后可以用这种方式输出提示相关信息
语句也可以：
string sql = string.Format("INSERT INTO RuleSetInfo(guid,rsName,rsType,rsAdmin,rsPath,rsDate,rsDesc,rsNote) VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')", guid, ruleSetName, fileType, admin, rsPath,dateNow, rsDesc, Note);
类的写法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
class MDBHelp
{
private string _fileName;
private string _connectionString;
private OleDbConnection _odcConnection;
private int row=0;
/// <summary>
/// 构建函数
/// </summary>
/// <param name="fileName">MDB文件（含完整路徑）</param>
public MDBHelp(string fileName)
{
this._fileName = fileName;
this._connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";";
}
/// <summary>
/// 建立连接（打开数据库文件）
/// </summary>
public void Open()
{
try
{
// 建立连接
this._odcConnection = new OleDbConnection(this._connectionString);
// 打开连接
this._odcConnection.Open();
}
catch (Exception)
{
throw new Exception("嘗試打开 " + this._fileName + " 失敗, 請確認文件是否存在！");
}
}
/// <summary>
/// 断开连接（关闭据库文件）
/// </summary>
public void Close()
{
this._odcConnection.Close();
}
/// <summary>
/// 增
/// </summary>
/// <param name="sql">sql命令</param>
/// <returns>以DataTable形式返回数据</returns>
public int GetDataIns(string sql)
{
try
{
row = 0;
OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
row = adapter.ExecuteNonQuery();
}
catch (Exception)
{
}
return row;
}
/// <summary>
/// 删
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int GetDataDel(string sql)
{
DataSet ds = new DataSet();
try
{
row = 0;
OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
row = adapter.ExecuteNonQuery();//更新数据库，返回受影响行数;可通过判断其是否>0来判断操作是否成功
}
catch (Exception)
{
//throw new Exception("sql語句： " + sql + " 執行失敗！");
}
return row;
}
/// <summary>
/// 改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int GetDataUpd(string sql)
{
DataSet ds = new DataSet();
try
{
row = 0;
OleDbCommand adapter = new OleDbCommand(sql, this._odcConnection);//执行命令
row = adapter.ExecuteNonQuery();
}
catch (Exception)
{
//throw new Exception("sql語句： " + sql + " 執行失敗！");
}
return row;
}
/// <summary>
/// 查
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSel(string sql)
{
DataSet ds = new DataSet();
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
adapter.Fill(ds);
}
catch (Exception)
{
throw new Exception("sql語句： " + sql + " 執行失敗！");
}
return ds;
}
}
}
