access 数据库操作
access 数据库操作#region access 数据库操作
* */
/**//// <summary>
/// 打开数据库,建立新的表和字段
/// </summary>
/// <param name="spath">数据库全路径</param>
/// <param name="dataname">表名</param>
/// <param name="items">字段数组</param>
private void newdatatable(string spath, string dataname, string[] items)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
// string strnew = " CREATE TABLE " + dataname + "( "
// + items[0] + " TEXT(100) CONSTRAINT PK_tblCustomers PRIMARY KEY , ";
string strnew = " CREATE TABLE [" + dataname + "] ( "
+ items[0] + " TEXT(255) , ";
for (int i = 1; i < items.Length - 1; i++)
{
strnew += items[i] + " TEXT(255) , ";
}
strnew += items[items.Length - 1] + " TEXT(255) )";
/**//*
string strnew = " CREATE TABLE "+ dataname +"("
+ " CustomerID INTEGER CONSTRAINT PK_tblCustomers PRIMARY KEY,"
+ " [Last Name] TEXT(50) NOT NULL,"
+ " [First Name] TEXT(50) NOT NULL,"
+ " Phone TEXT(10),"
+ " Email TEXT(50),"
+ " Address TEXT(40) DEFAULT Unknown)";
*/
OleDbCommand myCommand = new OleDbCommand(strnew, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception ed)
{
MessageBox.Show("新建表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 打开数据库,建立新的表和字段
/// </summary>
/// <param name="spath">数据库全路径</param>
/// <param name="dataname">表名</param>
/// <param name="items">字段集合</param>
private void newdatatable(string spath, string dataname, ArrayList items)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
// string strnew = " CREATE TABLE [" + dataname + "] ( "
// + items[0] + " TEXT(100) CONSTRAINT PK_tblCustomers PRIMARY KEY , ";
string strnew = " CREATE TABLE [" + dataname + "] ( "
+ items[0] + " TEXT(255) , ";
for (int i = 1; i < items.Count - 1; i++)
{
strnew += items[i].ToString() + " TEXT(255) , ";
}
strnew += items[items.Count - 1].ToString() + " TEXT(255) )";
/**//*
string strnew = " CREATE TABLE "+ dataname +"("
+ " CustomerID INTEGER CONSTRAINT PK_tblCustomers PRIMARY KEY,"
+ " [Last Name] TEXT(50) NOT NULL,"
+ " [First Name] TEXT(50) NOT NULL,"
+ " Phone TEXT(10),"
+ " Email TEXT(50),"
+ " Address TEXT(40) DEFAULT Unknown)";
*/
OleDbCommand myCommand = new OleDbCommand(strnew, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception ed)
{
MessageBox.Show("新建表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 删除数据库中的表,有则删除，无则忽略错误返回
/// </summary>
/// <param name="spath">数据库全名</param>
/// <param name="dataname">表名</param>
private void deletetable(string spath, string dataname)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
string strnew = " DROP TABLE " + dataname;
OleDbCommand myCommand = new OleDbCommand(strnew, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch// (Exception ed)
{
//MessageBox.Show("删除表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 修改表的字段（有错误，sql语法错误）
/// </summary>
/// <param name="spath">数据库全路径</param>
/// <param name="dataname">表名</param>
/// <param name="s1">原字段名</param>
/// <param name="s2">新字段名</param>
private void rename_column(string spath, string dataname, string s1, string s2)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
string strnew = " ALTER TABLE " + dataname + " ALTER COLUMN " + s1 + " TO " + s2 + "";
OleDbCommand myCommand = new OleDbCommand(strnew, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception ed)
{
MessageBox.Show("修改字段错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 获取数据库中的表名（不用,有错误）
/// </summary>
/// <param name="spath">数据库全名</param>
private void gettablename2(string spath)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
string strnew = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name";
OleDbCommand myCommand = new OleDbCommand(strnew, myConn);
OleDbDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader.GetInt32(0) + ", " + reader.GetString(1));
}
reader.Close();
myConn.Close();
}
catch (Exception ed)
{
MessageBox.Show("删除表错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 打开指定的access数据库
/// </summary>
/// <param name="spath">access数据库名</param>
/// <param name="dataname">access数据库中的表名</param>
public bool readdata(string spath, string dataname)
{
try
{
//创建一个 OleDbConnection对象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
// string strCom = " SELECT * FROM " + dataname + " ORDER BY id";
string strCom = "SELECT [" + dataname + "].* FROM [" + dataname + "]";
//string strCom = " SELECT * FROM " + dataname;
//创建一个 DataSet对象
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
if (this.dataSet1.Tables.Contains(dataname))
{
this.dataSet1.Tables.Remove(dataname);
}
myCommand.Fill(this.dataSet1, dataname);
myConn.Close();
return true;
}
catch// (Exception e)
{
// MessageBox.Show("连接数据库发生错误：" + e.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
/**//// <summary>
/// 打开指定的access数据库
/// </summary>
/// <param name="spath">access数据库名</param>
/// <param name="dataname">access数据库中的表名</param>
public bool readdata(string spath, string dataname, string sortkey)
{
try
{
//创建一个 OleDbConnection对象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
// string strCom = " SELECT * FROM " + dataname + " ORDER BY id";
string strCom = "SELECT [" + dataname + "].* FROM [" + dataname + "] ORDER BY [" + dataname + "]." + sortkey;
//string strCom = " SELECT * FROM " + dataname;
//创建一个 DataSet对象
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
if (this.dataSet1.Tables.Contains(dataname))
{
this.dataSet1.Tables.Remove(dataname);
}
myCommand.Fill(this.dataSet1, dataname);
myConn.Close();
return true;
}
catch// (Exception e)
{
// MessageBox.Show("连接数据库发生错误：" + e.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
/**//// <summary>
/// 新增记录
/// </summary>
/// <param name="spath">数据库全名</param>
/// <param name="dataname">表名</param>
/// <param name="captions">字段名</param>
/// <param name="items">添加的纪录内容</param>
public void newdata(string spath, string dataname, string[] captions, object[] items)
{
try
{
//连接到一个数据库
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open();
string strInsert;
int tt = captions.Length;
int sign = -1;//记录日期字段所在索引号,用来格式化日期格式(只要日期,不要时间)
strInsert = " INSERT INTO [" + dataname + "] ( "
+ captions[0] + " , ";
for (int i = 1; i < tt - 1; i++)
{
if (captions[i].Contains("日期"))
{
sign = i;
}
strInsert += captions[i] + " , ";
}
strInsert += captions[tt - 1] + " ) VALUES ( '";
for (int i = 0; i < tt - 1; i++)
{
if (i == sign)
{
string[] ss = items[i].ToString().Split(' ');
strInsert += ss[0] + "','";
}
else
{
strInsert += items[i].ToString() + "','";
}
}
strInsert += items[tt - 1].ToString() + "')";
/**//*
strInsert = " INSERT INTO " + dataname
+ " ( id,date,dirid,fileid,name,pid,gjjid,bgjjid,why,pages,oldid,bak ) VALUES ( ' "
+ items[0].ToString() + "' , '"
+ items[1].ToString() + "' , '"
+ items[2].ToString() + "' , '"
+ items[3].ToString() + "' , '"
+ items[4].ToString() + "' , '"
+ items[5].ToString() + "' , '"
+ items[6].ToString() + "' , '"
+ items[7].ToString() + "' , '"
+ items[8].ToString() + "' , '"
+ items[9].ToString() + "' , '"
+ items[10].ToString() + "' , '"
+ items[11].ToString() + " ')";
*/
OleDbCommand myCommand = new OleDbCommand(strInsert, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception ed)
{
MessageBox.Show("新增记录错误信息： " + ed.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/**//// <summary>
/// 搜索access数据库
/// </summary>
/// <param name="spath">access数据库名</param>
/// <param name="dataname">access数据库中的表名</param>
/// <param name="keyword">搜索关键字</param>
/// <param name="tablename">填充到dataset中的表名</param>
/// <returns></returns>
public bool searchdata(string spath, string dataname, string keyword, string tablename)
{
//string str = "";
bool yn = false;
//创建一个 OleDbConnection对象
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + spath;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "SELECT [" + dataname + "].* FROM [" + dataname + "] WHERE " + keyword;
try
{
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
if (this.dataSet1.Tables.Contains(tablename))
{
this.dataSet1.Tables.Remove(tablename);
}
myCommand.Fill(this.dataSet1, tablename);
yn = true;
}
catch //(Exception e)
{
//MessageBox.Show(dataname + " 数据库中不包含搜索的字段!\n" + e.ToString(), "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
MessageBox.Show(dataname + " 数据库中不包含搜索的字段!\n", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
myConn.Close();
}
return yn;
}
#endregion
原文地址：https://www.cnblogs.com/dreign/p/946253.html