代码拉取完成,页面将自动刷新
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.IO;
using Microsoft.Win32;
namespace Free.Database
{
// TODO: Fix syntax (' vs. ")
// TODO: Fix syntax (always close using ';' )
public class Access
{
static string keyName="Software\\Free Framework\\Free.Database.Access";
/// <summary>
/// Returns the name of the Microsoft Access Driver, if overridden in registry. Empty string if not.
/// </summary>
static string GetOverriddenMicrosoftAccessDriver()
{
RegistryKey kDlg=Registry.CurrentUser.OpenSubKey(keyName, true);
if(kDlg==null) kDlg=Registry.CurrentUser.CreateSubKey(keyName);
string key=(IntPtr.Size==8)?"DriverName64":"DriverName32";
string drivername=(string)kDlg.GetValue(key, "");
kDlg.Close();
return drivername;
}
/// <summary>
/// Returns true if the name of the Microsoft Access Driver is the only one to be used.
/// </summary>
static bool IsForceOverriddenMicrosoftAccessDriver()
{
RegistryKey kDlg=Registry.CurrentUser.OpenSubKey(keyName, true);
if(kDlg==null) kDlg=Registry.CurrentUser.CreateSubKey(keyName);
string key=(IntPtr.Size==8)?"ForceDriverName64":"ForceDriverName32";
bool force=((int)kDlg.GetValue(key, 0)==1);
kDlg.Close();
return force;
}
/// <summary>
/// Returns true if the name of the Microsoft Access pre-2010 Driver is also to be used.
/// </summary>
static bool IsForceMicrosoftAccess2000Driver()
{
RegistryKey kDlg=Registry.CurrentUser.OpenSubKey(keyName, true);
if(kDlg==null) kDlg=Registry.CurrentUser.CreateSubKey(keyName);
bool force=((int)kDlg.GetValue("Force2000DriverName", 0)==1);
kDlg.Close();
return force;
}
/// <summary>
/// Returns true if the name of the Microsoft Access 2010 Driver is also to be used.
/// </summary>
static bool IsForceMicrosoftAccess2010Driver()
{
RegistryKey kDlg=Registry.CurrentUser.OpenSubKey(keyName, true);
if(kDlg==null) kDlg=Registry.CurrentUser.CreateSubKey(keyName);
bool force=((int)kDlg.GetValue("Force2010DriverName", 0)==1);
kDlg.Close();
return force;
}
/// <summary>
/// Retrieves the Connection String
/// </summary>
/// <param name="filename"></param>
/// <returns></returns>
private static List<string> GetConnectionStrings(string filename)
{
string template="Driver={{{1}}};DBQ={0}";
List<string> ret=new List<string>();
string over=GetOverriddenMicrosoftAccessDriver();
if(over!="") ret.Add(string.Format(template, filename, over));
if(IsForceOverriddenMicrosoftAccessDriver()) return ret;
List<string> drivers=FilterDriversForAccess(ListODBCDrivers());
if(IsForceMicrosoftAccess2000Driver()&&!drivers.Contains("Microsoft Access Driver (*.mdb)"))
drivers.Insert(0, "Microsoft Access Driver (*.mdb)");
if(IsForceMicrosoftAccess2010Driver()&&!drivers.Contains("Microsoft Access Driver (*.mdb, *.accdb)"))
drivers.Insert(0, "Microsoft Access Driver (*.mdb, *.accdb)");
foreach(string driver in drivers)
ret.Add(string.Format(template, filename, driver));
return ret;
}
#region Static methods
public static bool CreateDatabase(string filename, bool overwrite=false)
{
if(!overwrite&&File.Exists(filename)) return false;
using(Stream s=typeof(Access).Assembly.GetManifestResourceStream("Free.Database.Access.Template.mdb"))
{
byte[] buffer=new byte[s.Length];
s.Read(buffer, 0, (int)s.Length);
File.WriteAllBytes(filename, buffer);
}
return true;
}
/// <summary>
/// Converts .NET data types to SQL data types
/// </summary>
/// <param name="datatype"></param>
/// <returns></returns>
public static string GetAccessDatatype(string datatype)
{
switch(datatype)
{
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64": return "INTEGER";
case "System.Decimal":
case "System.Double": return "DOUBLE";
case "System.String": return "TEXT";
case "System.Byte[]": return "IMAGE";
default: throw new Exception("Type not supported!");
}
}
/// <summary>
/// Converts .NET data types to DBType
/// </summary>
/// <param name="datatype"></param>
/// <returns></returns>
public static DbType GetDBType(string datatype)
{
switch(datatype)
{
case "System.Boolean": return DbType.Boolean;
case "System.Int16": return DbType.Int16;
case "System.Int32": return DbType.Int32;
case "System.Int64": return DbType.Int64;
case "System.UInt16": return DbType.UInt16;
case "System.UInt32": return DbType.UInt32;
case "System.UInt64": return DbType.UInt64;
case "System.Decimal": return DbType.Decimal;
case "System.Double": return DbType.Double;
case "System.String": return DbType.String;
case "System.Byte[]": return DbType.Binary;
default: throw new Exception("Type not supported!");
}
}
#endregion
public bool Connected { get; private set; }
OdbcConnection odbcConnection=null;
#region Basics
/// <summary>
/// Opens an access database
/// </summary>
/// <param name="filename"></param>
public bool OpenDatabase(string filename)
{
if(Connected) CloseDatabase(); // Close database, if still open
Connected=false;
List<string> connectionStrings=GetConnectionStrings(filename);
foreach(string cs in connectionStrings)
{
try
{
odbcConnection=new OdbcConnection(cs);
odbcConnection.Open();
Connected=true;
return true;
}
catch
{
Connected=false;
}
}
return false;
}
/// <summary>
/// Closes an access database
/// </summary>
public void CloseDatabase()
{
odbcConnection.Close();
odbcConnection.Dispose();
odbcConnection=null;
Connected=false;
}
/// <summary>
/// Start a transaction
/// </summary>
public void StartTransaction()
{
}
/// <summary>
/// Commits a transaction
/// </summary>
public void CommitTransaction()
{
}
#endregion
#region Data Edits
/// <summary>
/// Creates a table
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public void CreateTable(DataTable table)
{
List<string> columns=new List<string>();
string tableName=table.TableName;
if(tableName=="") throw new Exception("Can't create table without name!");
for(int i=0; i<table.Columns.Count; i++)
{
string column=table.Columns[i].Caption;
string type=GetAccessDatatype(table.Columns[i].DataType.FullName);
if(type=="INTEGER"&&table.Columns[i].AutoIncrement) column+=" AUTOINCREMENT";
else column+=" "+type;
if(table.Columns[i].AllowDBNull==false) column+=" NOT NULL";
columns.Add(column);
}
// Build SQL command
string sqlCommand="CREATE TABLE \""+tableName+"\" (";
for(int i=0; i<columns.Count; i++)
{
if(i==columns.Count-1) sqlCommand+=" "+columns[i];
else sqlCommand+=" "+columns[i]+",";
}
sqlCommand+=");";
ExecuteNonQuery(sqlCommand);
}
/// <summary>
/// Deletes a table
/// </summary>
/// <param name="tableName"></param>
public void RemoveTable(string tableName)
{
ExecuteNonQuery(string.Format("DROP TABLE \"{0}\";", tableName));
}
/// <summary>
/// Deletes multiple tables
/// </summary>
/// <param name="tables"></param>
public void RemoveTables(List<string> tables)
{
foreach(string i in tables) RemoveTable(i);
}
public void RemoveEntry(string table, string key, string value)
{
ExecuteNonQuery(string.Format("DELETE FROM \"{0}\" WHERE {1}={2};", table, key, value));
}
/// <summary>
/// Inserts data to a table
/// </summary>
/// <param name="addData"></param>
public void InsertData(DataTable insertData, string indexToRemove="")
{
string sqlCommand=string.Format("INSERT INTO \"{0}\" (", insertData.TableName);
bool first=true;
// Columns
for(int i=0; i<insertData.Columns.Count; i++)
{
if(insertData.Columns[i].ToString()==indexToRemove) continue;
if(first) sqlCommand+=insertData.Columns[i];
else sqlCommand+=", "+insertData.Columns[i];
first=false;
}
sqlCommand+=") VALUES";
string tmpSqlCommand=sqlCommand;
StartTransaction();
// Rows (Data)
for(int i=0; i<insertData.Rows.Count; i++)
{
sqlCommand=tmpSqlCommand;
sqlCommand+=" (";
using(OdbcCommand cmd=new OdbcCommand())
{
first=true;
for(int j=0; j<insertData.Columns.Count; j++)
{
OdbcParameter parameter=new OdbcParameter();
parameter.DbType=GetDBType(insertData.Columns[j].DataType.FullName);
parameter.ParameterName=insertData.Columns[j].Caption;
parameter.Value=insertData.Rows[i].ItemArray[j];
if(parameter.OdbcType==OdbcType.VarBinary)
parameter.OdbcType=OdbcType.Image; // to prevent errors for byte[511] to byte[2000]
if(parameter.ParameterName==indexToRemove) continue;
cmd.Parameters.Add(parameter);
// Build SQL command
if(first) sqlCommand+="?";
else sqlCommand+=",?";
first=false;
}
sqlCommand+=");";
// Execute command (per Dataset)
cmd.CommandText=sqlCommand;
ExecuteNonQuery(cmd);
}
}
CommitTransaction();
}
/// <summary>
/// Updates data
/// </summary>
/// <param name="insertData"></param>
public void UpdateData(DataTable updateData, string primaryKey, string tableToUpdate)
{
string tmpSqlCommand=string.Format("UPDATE \"{0}\" SET ", tableToUpdate);
StartTransaction();
// Rows (Data)
for(int i=0; i<updateData.Rows.Count; i++)
{
string sqlCommand=tmpSqlCommand;
using(OdbcCommand cmd=new OdbcCommand())
{
// Columns
for(int j=0; j<updateData.Columns.Count; j++)
{
OdbcParameter parameter=new OdbcParameter();
parameter.DbType=GetDBType(updateData.Columns[j].DataType.FullName);
parameter.ParameterName=updateData.Columns[j].Caption;
parameter.Value=updateData.Rows[i].ItemArray[j];
cmd.Parameters.Add(parameter);
// Build SQL command
if(j==updateData.Columns.Count-1) sqlCommand+=string.Format("{0}=?", updateData.Columns[j].Caption);
else sqlCommand+=string.Format("{0}=?,", updateData.Columns[j].Caption);
}
sqlCommand+=" WHERE "+primaryKey+" = '"+updateData.Rows[i][primaryKey].ToString()+"';";
// Execute command (per Dataset)
cmd.CommandText=sqlCommand;
ExecuteNonQuery(cmd);
}
}
CommitTransaction();
}
#endregion
#region Queries
/// <summary>
/// Executes a command
/// </summary>
/// <param name="sqlCommand"></param>
public int ExecuteNonQuery(string sqlCommand)
{
using(OdbcCommand odbcCommand=new OdbcCommand(sqlCommand, odbcConnection))
{
return odbcCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a OdbcCommand
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public int ExecuteNonQuery(OdbcCommand sqlCommand)
{
sqlCommand.Connection=odbcConnection;
return sqlCommand.ExecuteNonQuery();
}
/// <summary>
/// Executes a query
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public DataTable ExecuteQuery(string sqlCommand)
{
using(OdbcCommand odbcCommand=new OdbcCommand(sqlCommand, odbcConnection))
{
DataTable ret=new DataTable();
OdbcDataReader tmpDataReader=odbcCommand.ExecuteReader();
ret.Load(tmpDataReader);
tmpDataReader.Close();
return ret;
}
}
/// <summary>
/// Indicates whether a table exists
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public bool ExistsTable(string tableName)
{
List<string> tables=GetTables();
if(tables.IndexOf(tableName)==-1) return false;
return true;
}
/// <summary>
/// Retrieves a list of all table names
/// </summary>
/// <returns></returns>
public List<string> GetTables()
{
DataTable dt=odbcConnection.GetSchema("Tables");
List<string> ret=new List<string>();
for(int i=0; i<dt.Rows.Count; i++)
{
if(dt.Rows[i]["TABLE_TYPE"].ToString()=="TABLE") ret.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
return ret;
}
/// <summary>
/// Retrieves a empty dataset for a table, e.g. for add
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable GetTableStructure(string tableName)
{
string sqlCommand="SELECT * FROM \""+tableName+"\";";
DataTable ret=ExecuteQuery(sqlCommand);
ret.Rows.Clear();
ret.TableName=tableName;
return ret;
}
/// <summary>
/// Retrieves a table
/// </summary>
/// <param name="tblName"></param>
/// <returns></returns>
public DataTable GetTable(string tableName)
{
string sqlCommand="SELECT * FROM \""+tableName+"\";";
DataTable ret=ExecuteQuery(sqlCommand);
ret.TableName=tableName;
return ret;
}
#endregion
#if ODBC
[DllImport("odbc32.dll")]
static extern int SQLDataSources(int EnvironmentHandle, int Direction,
StringBuilder ServerName, int ServerNameBufferLenIn, ref int ServerNameBufferLenOut,
StringBuilder Description, int DescriptionBufferLenIn, ref int DescriptionBufferLenOut);
[DllImport("odbc32.dll")]
static extern int SQLDrivers(int EnvironmentHandle, int Direction,
StringBuilder DriverDescription, int DriverDescriptionBufferLenIn, ref int DriverDescriptionBufferLenOut,
StringBuilder DriverAttributes, int DriverAttributesLenIn, ref int DriverAttributesLenOut);
[DllImport("odbc32.dll")]
static extern int SQLAllocEnv(ref int EnvironmentHandle);
const int SQL_FETCH_NEXT=1;
const int SQL_FETCH_FIRST=2;
const int SQL_FETCH_FIRST_USER=31;
const int SQL_FETCH_FIRST_SYSTEM=32;
public static List<string> ListODBCSourcesAPI()
{
List<string> result=new List<string>();
int environmentHandle=0;
if(SQLAllocEnv(ref environmentHandle)!=-1)
{
StringBuilder serverName=new StringBuilder(1024);
StringBuilder description=new StringBuilder(1024);
int serverNameLen=0;
int descriptionLen=0;
int ret=SQLDataSources(environmentHandle, SQL_FETCH_FIRST_SYSTEM,
serverName, serverName.Capacity, ref serverNameLen,
description, description.Capacity, ref descriptionLen);
while(ret==0)
{
result.Add(serverName+System.Environment.NewLine+description);
ret=SQLDataSources(environmentHandle, SQL_FETCH_NEXT,
serverName, serverName.Capacity, ref serverNameLen,
description, description.Capacity, ref descriptionLen);
}
}
return result;
}
public static List<string> ListODBCDriversAPI()
{
List<string> result=new List<string>();
int environmentHandle=0;
if(SQLAllocEnv(ref environmentHandle)!=-1)
{
StringBuilder driverDescription=new StringBuilder(1024);
StringBuilder driverAttributes=new StringBuilder(1024);
int driverDescriptionLen=0;
int driverAttributesLen=0;
int ret=SQLDrivers(environmentHandle, SQL_FETCH_FIRST,
driverDescription, driverDescription.Capacity, ref driverDescriptionLen,
driverAttributes, driverAttributes.Capacity, ref driverAttributesLen);
while(ret==0)
{
result.Add(driverDescription+System.Environment.NewLine+driverAttributes);
driverDescriptionLen=0;
driverAttributesLen=0;
ret=SQLDataSources(environmentHandle, SQL_FETCH_NEXT,
driverDescription, driverDescription.Capacity, ref driverDescriptionLen,
driverAttributes, driverAttributes.Capacity, ref driverAttributesLen);
}
}
return result;
}
#endif
public static List<string> ListODBCDrivers()
{
List<string> ret=new List<string>();
Microsoft.Win32.RegistryKey reg=Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"Software\ODBC\ODBCINST.INI\ODBC Drivers");
if(reg==null) return ret;
foreach(string name in reg.GetValueNames()) ret.Add(name);
reg.Close();
return ret;
}
public static List<string> FilterDriversForAccess(List<string> drivers)
{
List<string> ret=new List<string>();
if(drivers==null) return ret;
foreach(string driver in drivers)
{
string lower=driver.ToLower();
if(lower.Contains("microsoft")&&lower.Contains("access")&&lower.Contains("*.mdb")) ret.Add(driver);
}
return ret;
}
}
}
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。