yang686526

ado.net access操作方法
www.dimcax.com
ado.net access操作方法
写了很长时间，没时间整理
好像有些朋友需要，还是先发上来
有兴趣的朋友自己改改吧
能新建mdb、表，更新表，判断表结构
执行sql语句查询等
public class oleadapter // : idisposable
{
private oledbconnection connection;
#region 初始化oleadapter
public oleadapter(string connectionfile)
{
connection(connectionfile);
}
public oleadapter(string connectionfile,string password)
{
connection(connectionfile,password);
}
public oleadapter()
{
}
#endregion
#region 连接mdb文件
/// <summary>
/// 连接数据表
/// </summary>
/// <param name="connectionfile">文件名</param>
/// <returns></returns>
public oledbconnection connection(string connectionfile)
{
string db_conn_string = connectionstring(connectionfile);
if (db_conn_string != null)
return connection = new oledbconnection(db_conn_string);
else
return null;
}
/// <summary>
/// 连接数据表
/// </summary>
/// <param name="connectionfile">文件名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public oledbconnection connection(string connectionfile,string password)
{
string db_conn_string = connectionstring(connectionfile,password);
if (db_conn_string != null)
return connection = new oledbconnection(db_conn_string);
else
return null;
}
/// <summary>
/// 返回mdb连接字符串
/// </summary>
/// <param name="connectionfile">文件名</param>
/// <returns></returns>
public string connectionstring(string connectionfile)
{
if (file.exists(connectionfile))
return "rovider=microsoft.jet.oledb.4.0;data source=" + connectionfile;
else
return null;
}
/// <summary>
/// 返回mdb连接字符串
/// </summary>
/// <param name="connectionfile">文件名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public string connectionstring(string connectionfile,string password)
{
if (file.exists(connectionfile))
return "rovider=microsoft.jet.oledb.4.0;data source=" + connectionfile
+"user id=adminassword=;jet oledbatabase password=" + password;
else
return null;
}
#endregion
#region 实行sql命令
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="commandtext">sql语句</param>
/// <returns></returns>
public datatable execuate(string commandtext)
{
try
{
dataset mydataset = new dataset();
if (connection.state != system.data.connectionstate.open)
connection.open();
oledbdataadapter adapter = new oledbdataadapter(commandtext, connection);
adapter.fill(mydataset, "newtal");
datatable table = mydataset.tables["newtal"];
if (table == null || table.rows.count <= 0)
table = null;
return table;
}
catch (exception)
{
return null;
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="mdbfile">mdb文件名</param>
/// <param name="commandtext">sql语句</param>
/// <returns></returns>
public datatable execuate(string mdbfile,string commandtext)
{
oledbconnection oledbconnection = new oledbconnection(connectionstring(mdbfile));
try
{
return execuate(oledbconnection, commandtext);
}
catch (exception)
{
return null;
}
finally
{
oledbconnection.close();
oledbconnection.dispose();
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="mdbfile">mdb文件名</param>
/// <param name="commandtext">sql语句</param>
/// <param name="password">密码</param>
/// <returns></returns>
public datatable execuate(string mdbfile,string commandtext,string password)
{
oledbconnection oledbconnection = new oledbconnection(connectionstring(mdbfile,password));
try
{
return execuate(oledbconnection, commandtext);
}
catch (exception)
{
return null;
}
finally
{
oledbconnection.close();
oledbconnection.dispose();
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="mdbfile">mdb文件名</param>
/// <param name="commandtext">sql语句</param>
/// <returns></returns>
public datatable execuate(oledbconnection oledbconnection, string commandtext)
{
try
{
dataset mydataset = new dataset();
if (oledbconnection.state != system.data.connectionstate.open)
oledbconnection.open();
oledbdataadapter adapter = new oledbdataadapter(commandtext, oledbconnection);
adapter.fill(mydataset, "newtal");
datatable table = mydataset.tables["newtal"];
if (table == null || table.rows.count <= 0)
table = null;
return table;
}
catch (exception)
{
return null;
}
}
#endregion

下载次数:9
restrictions = new object[4];
restrictions[3] = "table";
datatable schematable = connection.getoledbschematable(oledbschemaguid.tables, restrictions);
return schematable;
}
catch (exception)
{
return null;
}
}
/// <summary>
/// 返回mdb文件tables表名列表
/// </summary>
/// <param name="filename">mdb文件名</param>
/// <returns></returns>
public string[] gettablenames(string filename)
{
datatable datatable = null;
try
{
datatable = gettables(filename);
list<string> strlist = new list<string>();
foreach (datarow row in datatable.rows)
{
strlist.add(row.itemarray[2].tostring().toupper().trim());
}
return strlist.toarray();
}
catch (exception ex)
{
return null;
throw ex;
}
finally
{
datatable.dispose();
}
}
/// <summary>
/// 获取mdb文件table中的字段列表
/// </summary>
/// <param name="connectionfile">mdb文件</param>
/// <param name="tablename">表名</param>
/// <returns>返回datatable</returns>
public datatable gettablecolumn(string connectionfile, string tablename)
{
oledbconnection oledbconnection = null;
try
{
oledbconnection = new oledbconnection(connectionstring(connectionfile));
if (oledbconnection.state != connectionstate.open)
oledbconnection.open();
datatable dt = oledbconnection.getoledbschematable(oledbschemaguid.columns,
new object[] { null, null, tablename, null });
return dt;
}
catch (exception)
{
return null;
}
finally
{
oledbconnection.close();
oledbconnection.dispose();
}
}
/// <summary>
/// 获取mdb文件table中的字段名数组
/// </summary>
/// <param name="connectionfile">mdb文件名</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public string[] gettablecolumnnames(string connectionfile, string tablename)
{
datatable datatable = null;
try
{
datatable = gettablecolumn(connectionfile, tablename);
list<string> strlist = new list<string>();
foreach (column col in datatable.columns)
{
strlist.add(col.name);
}
return strlist.toarray();
}
catch (exception ex)
{
return null;
throw ex;
}
finally
{
datatable.dispose();
}
}
#endregion
#region 判断表结构
/// <summary>
/// 判断表中指定字段是否存在
/// </summary>
/// <param name="mdbfile">mdb文件名</param>
/// <param name="tablename">表名</param>
/// <param name="columnname">字段名</param>
/// <returns></returns>
public bool columnexists(string mdbfile, string tablename, string columnname)
{
oledbconnection connection = null;
try
{
connection = new oledbconnection(connectionstring(mdbfile));
if (connection.state != system.data.connectionstate.open)
connection.open();
datatable table = gettablecolumn(mdbfile, tablename);
foreach (datarow row in table.rows)
{
if (row["column_name"].tostring().trim().toupper() == columnname.trim().toupper())
return true;
}
return false;
}
catch (exception)
{
return false;
throw;
}
finally
{
connection.close();
connection.dispose();
}
}
/// <summary>
/// 判断mdb中是否存在指定表
/// </summary>
/// <param name="mdbfile">mdb文件</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public bool tableexists(string mdbfile, string tablename)
{
oledbconnection connection = null;
try
{
connection = new oledbconnection(connectionstring(mdbfile));
return tableexists(connection, tablename);
}
catch (exception)
{
return false;
}
finally
{
connection.close();
connection.dispose();
}
}
/// <summary>
/// 判断mdb中是否存在指定表
/// </summary>
/// <param name="connection">oledb连接</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public bool tableexists(oledbconnection connection,string tablename)
{
try
{
if (connection.state != system.data.connectionstate.open)
connection.open();
datatable tables = gettables(connection);
foreach (datarow row in tables.rows)
{
string tabname = (string)row.itemarray[2];
if (tablename.trim().toupper() == tabname.trim().toupper())
return true;
}
return false;
}
catch (exception)
{
return false;
}
}
#endregion
#region 输出mdb文件
/// <summary>
/// 创建mdb文件
/// </summary>
/// <param name="filename">文件名</param>
private bool createaccessfile(string mdbname)
{
try
{
if (!file.exists(mdbname))
{
adox.catalogclass cat = new catalogclass();
string str = "provider=microsoft.jet.oledb.4.0;data source=" + mdbname;
cat.create(str);
cat = null;
}
return true;
}
catch (exception)
{
return false;
throw;
}
}
private string getcreatetalbesql(datatable datatalbe)
{
try
{
return getcreatetalbesql(datatalbe,datatalbe.tablename);
}
catch
{
return null;
}
}
private string getcreatetalbesql(datatable datatable,string tabalename)
{
try
{
datacolumncollection dcs = datatable.columns;
string createsql = "create table " + tabalename + " (id counter primary key,";
foreach (datacolumn dc in dcs)
{
createsql += dc.columnname + " " + getdatatype(dc.datatype) + ",";
}
createsql = createsql.trim(',');
createsql += ")";
return createsql;
}
catch
{
return null;
}
}
/// <summary>
/// 根据datatable创建mdb文件及表
/// 并写入datatable数据
/// </summary>
/// <param name="mdbname">mdb文件名</param>
/// <param name="datatable">datatable表</param>
/// <param name="tablename">表名</param>
/// <param name="erasetable">是否删除已存在的表</param>
/// <returns></returns>
private bool createtablefromdatatable(string mdbname, datatable datatable,
string tablename, bool erasetable)
{
oledbconnection connection = null;
try
{
connection = connection(mdbname);
string createtablesql = getcreatetalbesql(datatable, tablename);
if (tableexists(connection, tablename))
{
if (erasetable && (createtablesql != null))
{
execuate(connection, "drop table " + tablename);
execuate(connection, createtablesql);
}
else
return false;
}
else if (createtablesql != null)
execuate(connection, createtablesql);
else
return false;
//if (columnexists(mdbname, tablename, "id"))
// execuate(connection, "constraint id ");
return true;
}
catch
{
return false;
}
finally
{
connection.close();
connection.dispose();
}
}
private bool createtablefromdatatable(string mdbname, datatable datatable, bool erasetable)
{
try
{
return createtablefromdatatable(mdbname, datatable, datatable.tablename, erasetable);
}
catch
{
return false;
}
}
/// <summary>
/// 更新改变的dataset
/// </summary>
/// <param name="conn">mdb文件名</param>
/// <param name="dataset">dataset</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public int acceptchanges(string mdbfile, dataset dataset, string tablename)
{
oledbconnection conn =null;
try
{
conn = connection(mdbfile);
return acceptchanges(conn, dataset, tablename);
}
catch (exception)
{
return -1;
}
finally
{
conn.dispose();
}
}

/// <summary>
/// 更新改变的dataset
/// </summary>
/// <param name="conn">连接定义</param>
/// <param name="dataset">dataset</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public int acceptchanges(oledbconnection conn, dataset dataset, string tablename)
{
if (dataset == null)
{
return 0;
}
dataset.tables[0].tablename = tablename;
dataset changes = dataset.getchanges();
if (changes == null)
{
return 0;
}
if (changes.haschanges())
{
if (!changes.haserrors)
{
try
{
oledbdataadapter dataadapter = new oledbdataadapter("select * from " + tablename, conn);
dataadapter.updatecommand = new oledbcommandbuilder(dataadapter).getupdatecommand();
int num = dataadapter.update(changes, tablename);
dataset.acceptchanges();
return num;
}
catch
{
return -1;
}
}
}
return -1;
}
/// <summary>
/// 将datatable输出到mdb文件
/// </summary>
/// <param name="mdbname">mdb文件名</param>
/// <param name="tablename">表名</param>
/// <param name="datatable">datatable表</param>
/// <param name="replacefile">如果文件存在是否替换文件</param>
/// <returns></returns>
public bool putdatatabletomdb(string mdbname, string tablename, datatable datatable, bool replacefile)
{
oledbconnection conn = null;
try
{
bool do = true;
if (file.exists(mdbname))
{
if (replacefile)
{
file.delete(mdbname);
do = createaccessfile(mdbname);
}
}
else
do = createaccessfile(mdbname);

if (do)
{
conn = connection(mdbname);
if (createtablefromdatatable(mdbname, datatable, tablename, true))
{
if (updatatable(conn, datatable, tablename))
return true;
return false;
}
else
{
if (updatatable(conn, datatable, tablename))
return true;
return false;
}
}
else
return false;
}
catch
{
return false;
}
finally
{
conn.close();
conn.dispose();
}
}
/// <summary>
/// 将datatable输出到mdb文件
/// 表名为datatablename
/// </summary>
/// <param name="mdbname">mdb文件名</param>
/// <param name="datatable">datatable表</param>
/// <param name="replacefile">如果文件存在是否替换文件</param>
/// <returns></returns>
public bool putdatatabletomdb(string mdbname, datatable datatable,bool replacefile)
{
try
{
return putdatatabletomdb(mdbname, datatable.tablename, datatable, replacefile);
}
catch
{
return false;
}
}
/// <summary>
/// 获取datatable字段对应mdb表字段类型
/// </summary>
/// <param name="type">datatable字段类型</param>
/// <returns></returns>
private string getdatatype(type type)
{
if (type == typeof(int16))
return oledbtype.smallint.tostring();
else if (type == typeof(int32))
return oledbtype.integer.tostring();
else if (type == typeof(int64))
return oledbtype.bigint.tostring();
else if (type == typeof(double))
return oledbtype.double.tostring();
else if (type == typeof(single))
return oledbtype.single.tostring();
else if (type == typeof(char))
return oledbtype.char.tostring();
else if (type == typeof(boolean))
return "bit"; // or "yesno"
else if (type == typeof(string))
return oledbtype.varchar.tostring();
else if (type == typeof(byte))
return oledbtype.unsignedtinyint.tostring();
else if (type == typeof(sbyte))
return oledbtype.tinyint.tostring();
else if (type == typeof(uint16))
return oledbtype.unsignedsmallint.tostring();
else if (type == typeof(uint32))
return oledbtype.unsignedint.tostring();
else if (type == typeof(uint64))
return oledbtype.unsignedbigint.tostring();
else if (type == typeof(decimal))
return oledbtype.numeric.tostring();
else if (type == typeof(datetime))
return "datetime";
else if (type == typeof(timespan))
return oledbtype.dbtime.tostring();
else if (type == typeof(exception))
return oledbtype.error.tostring();
else if (type == typeof(guid))
return oledbtype.guid.tostring();
else if (type == typeof(byte))
return oledbtype.unsignedtinyint.tostring();
else if (type == typeof(byte[]))
return "image";
else
return oledbtype.varchar.tostring();
}
#endregion