c#访问access 数据库

来源：互联网发布：淘宝美工注意事项编辑：程序博客网时间：2024/05/13 10:11

using System.Collections;
using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using CMMS_SVR.Event;

public class AccessData {

public DataTable dtYourData;

public string datapath ;

// private static string user = ";Jet OLEDB:Database Password=ccrbcn;";

private string con ;
private OleDbConnection oCon;
public IList mycopy_colunm_name = new ArrayList();
private IList mycopy_colunm_type = new ArrayList();

public AccessData()
{
datapath = CMMS_SVR.Properties.Settings.Default._datapath;//"C:\\cmms.mdb";
// con = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + datapath + ";";
con =//"Driver={Microsoft Access Driver (*.mdb)};"+
"Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=" + datapath +
//";UID=管理员;PassWord=sa;"+
";Persist Security Info=False;Jet OLEDB:Database Password=" + CMMS_SVR.Properties.Settings.Default._datapass;
}
public AccessData(string datapath,string datapass)
{
this.datapath = datapath;
con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + datapath +
//";UID=管理员;PassWord=sa;"+
";Persist Security Info=False;Jet OLEDB:Database Password=" + CMMS_SVR.Properties.Settings.Default._datapass;
}

public Boolean MyUpdatasql(string strsql)///////////跟新数据库
{
oCon = new OleDbConnection(con);
OleDbCommand oCmd = new OleDbCommand(strsql, oCon);
try
{
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();

oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return false;
}
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return true;
//Debug.Log(dtYourData.Rows[0]["work_1"].ToString());
}
public void myselect(string strsql, string tableName)
{

oCon = new OleDbConnection(con);
OleDbCommand oCmd = new OleDbCommand(strsql, oCon);
dtYourData = null;
dtYourData = new DataTable(tableName);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
OleDbDataReader rData = oCmd.ExecuteReader();
// now lets blast that into the table by sheer man power!
dtYourData.Load(rData);
// close that reader!
rData.Close();
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();

}
}

public void inser_or_delete(string strsql)
{
oCon = new OleDbConnection(con);
OleDbCommand oCmd = new OleDbCommand(strsql, oCon);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();
// now lets blast that into the table by sheer man power!
// close your connection to the spreadsheet!
oCon.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
}

internal void myselect(string sql1)
{
throw new NotImplementedException();
}
public DataSet select(string sql)
{
DataSet dsReturn = new DataSet();
OleDbConnection conn = new OleDbConnection(con);
try
{

conn.Open();
OleDbDataAdapter comm = new OleDbDataAdapter(sql, conn);
comm.Fill(dsReturn);
conn.Close();
}
catch (Exception ex)
{
conn.Close();
Console.WriteLine("数据库操作失败，错误打印："+ex.ToString());
}

{
return dsReturn;

}
}
public Boolean deleteTable(string tablename)
{
oCon = new OleDbConnection(con);
OleDbCommand oCmd = new OleDbCommand("drop table " + tablename, oCon);
try
{
oCon.Open();
// lets use a datareader to fill that table!
oCmd.ExecuteNonQuery();

oCon.Close();
}
catch (Exception ex)
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return false;
}
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
return true;
}

public Boolean manyInsert(string sqlTableName,DataSet mydataset)
{

sqlTableName += "select * From " + sqlTableName + " ";

DataSet mydataset1 = new AccessData().select(sqlTableName);
if (mydataset1.Tables.Count == 0 )
{
EventTool.createAccessTable(EventTool.createAccessMaterialPlan);
}

OleDbConnection conn = new OleDbConnection(con);
try
{

conn.Open();
OleDbDataAdapter comm = new OleDbDataAdapter(sqlTableName, conn);

OleDbCommandBuilder cmdbld = new OleDbCommandBuilder(comm);
cmdbld.SetAllValues = true;
comm.InsertCommand = cmdbld.GetInsertCommand();
mydataset.Tables[0].BeginLoadData();
　　 int rowcount = mydataset.Tables[0].Rows.Count;
　　 for (int n = 0; n < rowcount; n++)
　　 {
　　mydataset.Tables[0].Rows[n].SetAdded();
　　 }
　　mydataset.Tables[0].EndLoadData();
　　 comm.UpdateBatchSize = 2000;
　 comm.Update(mydataset, mydataset.Tables[0].TableName);
comm.Dispose();
conn.Close();
conn.Dispose();
mydataset.Dispose();

}
catch (Exception ex)
{
conn.Close();
conn.Dispose();
return false;
Console.WriteLine("数据库操作失败，错误打印：" + ex.ToString());
}

{
return true;

}
}

}

0 0