[其他] access数据库的简单配置及使用

[复制链接]

王者再临

2317 主题	54 听众	2万积分

资深设计师

Rank: 7 Rank: 7 Rank: 7

纳金币: 20645
精华: 62

电梯直达

楼主

发表于 2014-10-18 03:27:15 |只看该作者 |倒序浏览

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;//必须引用的命名空间
using ADOX;//必须引用的命名空间
using System.Windows.Forms;
namespace xxxxxxxxx
{
/// <summary>
/// 数据库管理，存储信息包括指纹信息和个人信息，查询主要以指纹查询和身份证查询为主
/// 显示数据则不包括指纹信息
/// </summary>
public class AccessManager
{
#region 变量声明
private const string filePath = @"C:\UserInfor.accdb";
private string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath;
private string sql = null;
OleDbConnection oleConn = null;
OleDbCommand oleCmd = null;
#endregion
public void CreateAccess()//新建数据库
{
if (!File.Exists(filePath))
{
ADOX.Catalog cata = new Catalog();
try
{
cata.Create(connStr);
Debug.Log("创建access数据库:User");
}
catch (Exception ex)
{
Debug.Log("创建数据库出错\n" + ex.ToString());
MessageBox.Show("创建数据库出错\n" + ex.ToString());
}
oleConn = new OleDbConnection(connStr);
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
oleConn.Open();
sql = @"CREATE TABLE UserTabel(身份证 CHAR(20) NOT NULL PRIMARY KEY,姓名 CHAR(10),性别 CHAR(2),年龄 CHAR(4),电话 CHAR(20))";
oleCmd.CommandText = sql;
oleCmd.ExecuteNonQuery();
Debug.Log("创建表1：UserTabel");
oleConn.Close();
oleConn.Dispose();
}
}
/// <summary>
/// 刷新数据，只显示人物信息，不显示指纹数据
/// </summary>
/// <returns></returns>
public DataTable RefreshDataView()
{
try
{
oleConn = new OleDbConnection(connStr);
DataSet set = new DataSet();
string s = @"SELECT 身份证,姓名,性别,年龄,电话 FROM UserTabel";
OleDbDataAdapter oleAdp = new OleDbDataAdapter(s, oleConn);
oleAdp.Fill(set, "UserTabel");
oleConn.Close();
oleConn.Dispose();
Debug.Log("刷新数据库");
return set.Tables["UserTabel"];
}
catch (Exception ex)
{
Debug.Log("刷新数据出错\n" + ex.ToString());
MessageBox.Show(ex.ToString());
return null;
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="card">身份证索引</param>
/// <param name="name">姓名</param>
/// <param name="sex">性别</param>
/// <param name="age">年龄</param>
/// <param name="tel">电话</param>
public void InsertData(string card, string name, string sex, string age, string tel)
{
try
{
oleConn = new OleDbConnection(connStr);
oleCmd = new OleDbCommand();
sql = string.Format("INSERT INTO UserTabel(身份证,姓名,性别,年龄,电话) VALUES({0},{1},{2},{3},{4})", "'" + card + "'", "'" + name + "'", "'" + sex + "'", "'" + age + "'", "'" + tel + "'");
oleCmd.Connection = oleConn;
oleCmd.CommandText = sql;
oleConn.Open();
oleCmd.ExecuteNonQuery();
Debug.Log("新插入一条数据");
oleConn.Close();
oleConn.Dispose();
}
catch (Exception ex)
{
Debug.Log("插入数据出错\n" + ex.ToString());
MessageBox.Show("插入数据出错\n" + ex.ToString());
}
}
/// <summary>
/// 根据身份证查找数据
/// </summary>
/// <param name="card"></param>
/// <returns></returns>
public string FindDataByCard(string card)
{
try
{
oleConn = new OleDbConnection(connStr);
sql = string.Format("SELECT 身份证,姓名,性别,年龄,电话 FROM UserTabel WHERE 身份证='{0}'", card);
DataSet set = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, oleConn);
da.Fill(set, "UserTabel");
Debug.Log("查询数据");
oleConn.Close();
oleConn.Dispose();
string infor = null;
foreach (DataColumn c in set.Tables["UserTabel"].Columns)
{
foreach (DataRow r in set.Tables["UserTabel"].Rows)//UserTabel
{
infor += r[c].ToString() + "|";
Debug.Log(r[c].ToString());
}
}
return infor;
}
catch (Exception ex)
{
Debug.Log("查询数据出错\n" + ex.ToString());
MessageBox.Show("查询数据出错\n" + ex.ToString());
return null;
}
}
/// <summary>
/// 根据身份证删除数据
/// </summary>
/// <param name="id"></param>
public void DeleteDataByCard(string card)
{
try
{
oleConn = new OleDbConnection(connStr);
oleCmd = new OleDbCommand();
oleCmd.Connection = oleConn;
sql = string.Format("DELETE * FROM UserTabel WHERE 身份证='{0}'", card);
oleCmd.CommandText = sql;
oleConn.Open();
oleCmd.ExecuteNonQuery();
Debug.Log("删除数据");
oleConn.Close();
oleConn.Dispose();
oleCmd.Dispose();
}
catch(Exception ex)
{
Debug.Log("删除数据出错" + ex.ToString());
MessageBox.Show("删除数据出错" +　ex.ToString());
}
}
}
}