ACCESS数据库访问组件（一）ACCESS_Database.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace XLang.VideoOnline.Framework.Database.Access
{
/// <summary>
/// XLang.VideoOnline.Framework.Database is designed for create, update, insert and delete operations.
/// </summary>
public class Access
{
private OleDbConnection _connection;
private string _connectionString;
private string _databaseName;
private Database.Access.DataTablesCollection _tables;
private Database.Access.DataViewsCollection _views;
private DateTime _creationTime;
private DateTime _lastAccessTime;
private DateTime _lastWriteTime;
public string Name
{
get
{
return _databaseName;
}
}
public Database.Access.DataTablesCollection Tables
{
get
{
return _tables;
}
}
public Database.Access.DataViewsCollection Views
{
get
{
return _views;
}
}
public DateTime CreationTime
{
get
{
return _creationTime;
}
}
public DateTime LastAccessTime
{
get
{
return _lastAccessTime;
}
}
public DateTime LastWriteTime
{
get
{
return _lastWriteTime;
}
}
public Access()
{
}
public Access(string databaseName)
{
string delimStr = " :\\./";
char [] delimiter = delimStr.ToCharArray();
string [] split = null;
split=databaseName.Split(delimiter);
_databaseName = split[split.Length-2];
_connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+databaseName;
_creationTime = System.IO.File.GetCreationTime(databaseName);
_lastAccessTime = System.IO.File.GetLastAccessTime(databaseName);
_lastWriteTime = System.IO.File.GetLastWriteTime(databaseName);
_connection = new OleDbConnection( _connectionString );
try
{
if(!(_connection.State==ConnectionState.Open)) _connection.Open();
_tables=new Database.Access.DataTablesCollection(_connection);
_views=new DataViewsCollection(_connection);
}
catch(Exception e)
{
System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_Access:</font>"+e.Message+"<br>");
}
finally
{
if(_connection.State==ConnectionState.Open) _connection.Close();
}
}
public bool ExecuteCommand(string commandString,CommandType commandType)
{
switch(commandType)
{
case CommandType.Create:
return Create(commandString);
case CommandType.Delete:
return Delete(commandString);
case CommandType.Insert:
return Insert(commandString);
case CommandType.Select:
return Select(commandString);
case CommandType.Join:
return Join(commandString);
case CommandType.Update:
return Update(commandString);
case CommandType.View:
return View(commandString);
case CommandType.Other:
return Other(commandString);
default:
break;
}
return true;
}
private bool Create(string commandString)
{
return CreateDeleteInsertUpdate(commandString);
}
private bool Delete(string commandString)
{
return CreateDeleteInsertUpdate(commandString);
}
private bool Insert(string commandString)
{
return CreateDeleteInsertUpdate(commandString);
}
private bool Select(string commandString)
{
try
{
OleDbDataAdapter adapter=new OleDbDataAdapter(commandString,_connection);
// string tableName=null;
// string delimStr = " ";
// char [] delimiter = delimStr.ToCharArray();
// string [] split = null;
// split=commandString.Split(delimiter);
// tableName=split[4].Trim();
string from="FROM";
int fromIndex=commandString.IndexOf(from);
string subCommandString=commandString.Substring(fromIndex+4).Trim();
int endIndex=subCommandString.IndexOf(' ');
string tableName2=null;
if(endIndex<0)
tableName2=subCommandString.Substring(0).Trim();
else
tableName2=subCommandString.Substring(0,endIndex).Trim();
//System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_Select:</font>"+tableName2+"<br>");
_tables[TableNameToIndex(tableName2)].Clear();
adapter.Fill(_tables[TableNameToIndex(tableName2)]);
adapter=null;
}
catch(Exception e)
{
System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_Select:</font>"+e.Message+"<br>");
return false;
}
finally
{
}
return true;
}
private bool Join(string commandString)
{
try
{
OleDbDataAdapter adapter=new OleDbDataAdapter(commandString,_connection);
// string tableName=null;
// string delimStr = " ";
// char [] delimiter = delimStr.ToCharArray();
// string [] split = null;
// split=commandString.Split(delimiter);
// tableName=split[4].Trim();
// string from="FROM";
// int fromIndex=commandString.IndexOf(from);
// string subCommandString=commandString.Substring(fromIndex+4).Trim();
// int endIndex=subCommandString.IndexOf(' ');
// string tableName2=null;
// if(endIndex<0)
// tableName2=subCommandString.Substring(0).Trim();
// else
// tableName2=subCommandString.Substring(0,endIndex).Trim();
//System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_Select:</font>"+tableName2+"<br>");
// _tables[TableNameToIndex(tableName2)].Clear();
// adapter.Fill(_tables[TableNameToIndex(tableName2)]);
// if(_tables["temp"]!=null)
// _tables[TableNameToIndex("temp")].Clear();
// else
// {
// _tables[_tables.Count]=new Database.Access.DataTable("temp");
// _tables[TableNameToIndex("temp")].Clear();
// }
_tables[TableNameToIndex("temp")].Clear();
adapter.Fill(_tables[TableNameToIndex("temp")]);
adapter=null;
}
catch(Exception e)
{
System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_Join:</font>"+e.Message+"<br>");
return false;
}
finally
{
}
return true;
}
private int TableNameToIndex(string tableName)
{
for(int i=0;i<_tables.Count;i++)
{
if(_tables[i].Name.ToUpper()==tableName.ToUpper())
return i;
}
return -1;
}
private int ViewNameToIndex(string viewName)
{
for(int i=0;i<_tables.Count;i++)
{
if(_views[i].Name.ToUpper()==viewName.ToUpper())
return i;
}
return -1;
}
private bool Update(string commandString)
{
return CreateDeleteInsertUpdate(commandString);
}
private bool CreateDeleteInsertUpdate(string commandString)
{
if(!(_connection.State==ConnectionState.Open)) _connection.Open();
// Start a local transaction.
OleDbTransaction myTrans =_connection.BeginTransaction();
// Enlist the command in the current transaction.
OleDbCommand myCommand = _connection.CreateCommand();
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = commandString;
myCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (OleDbException ex)
{
if (myTrans.Connection != null)
{
//Console.WriteLine("An exception of type " + ex.GetType() +
// " was encountered while attempting to roll back the transaction.");
}
}
return false;
}
finally
{
if(_connection.State==ConnectionState.Open) _connection.Close();
}
return true;
}
private bool View(string commandString)
{
try
{
string from="FROM";
int fromIndex=commandString.IndexOf(from);
string subCommandString=commandString.Substring(fromIndex+4).Trim();
int endIndex=subCommandString.IndexOf(' ');
string viewName=null;
if(endIndex<0)
viewName=subCommandString.Substring(0).Trim();
else
viewName=subCommandString.Substring(0,endIndex).Trim();
OleDbDataAdapter adapter=new OleDbDataAdapter(commandString,_connection);
_views[ViewNameToIndex(viewName)].Clear();
adapter.Fill(_views[ViewNameToIndex(viewName)]);
adapter=null;
}
catch(Exception e)
{
System.Web.HttpContext.Current.Response.Write("<br><font color=#ff0000>ACCESS_Database_View:</font>"+e.Message+"<br>");
return false;
}
finally
{
}
return true;
}
private bool Other(string commandString)
{
return true;
}
}
}
