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; }
} } 
|