access|访问|数据|数据库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("
<font color=#ff0000>ACCESS_Database_Access:</font>"+e.Message+"
");
}
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("
<font color=#ff0000>ACCESS_Database_Select:</font>"+tableName2+"
");

_tables[TableNameToIndex(tableName2)].Clear();
adapter.Fill(_tables[TableNameToIndex(tableName2)]);

adapter=null;
}
catch(Exception e)
{
System.Web.HttpContext.Current.Response.Write("
<font color=#ff0000>ACCESS_Database_Select:</font>"+e.Message+"
");
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("
<font color=#ff0000>ACCESS_Database_Select:</font>"+tableName2+"
");

// _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("
<font color=#ff0000>ACCESS_Database_Join:</font>"+e.Message+"
");
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("
<font color=#ff0000>ACCESS_Database_View:</font>"+e.Message+"
");
return false;
}
finally
{
}
return true;
}



private bool Other(string commandString)
{
return true;
}



}
}