using System; using System.Data; using System.Data.OleDb; using System.Collections.Generic; using System.Data.Common;
/// <summary> /// Connection to a foxpro database/folder. /// The FoxPro OleDB provider (VFPOLEDB) must be installed on the executing machine (// oledb driver: http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en) /// </summary> public class FoxProConnection:IDisposable {
/// <param name="DBFolder">The folder containing the tables, or the dbc file of the foxpro database. /// This value can be null, but no data can be obtained until the <see cref="DataBaseFolder"/> can be set</param> /// <seealso cref="DataBaseFolder"/> public FoxProConnection(string DBFolder) {
if (on == current) return; current = on; if (ConnectionIsOpen)
ExecuteNonQuery(GetSetString(value, on));
}
string GetSetString(string par, bool on) {
return "SET " + par + " " + (on ? "ON" : "OFF");
}
private bool setnull=false; /// <summary> /// Changes the way alter/create table and inserts handle null values. /// The default FoxPro value is on, the default value for this connection is off, because /// that allows null values for columns not included in an insert statement, which is needed /// for a lot of (automatic) update executions. /// </summary> public bool SetNull {
get { return setnull; } set {
SetParam("NULL", value, ref setnull);
}
}
private bool exclusive; /// <summary> /// Specifies whether Visual FoxPro opens table files for exclusive or shared use on a network. The default /// for FoxPro is On, the default for this connection object is Off. /// </summary> public bool Exclusive {
get { return exclusive; } set {
SetParam("EXCLUSIVE", value, ref exclusive);
}
}
#endregion
/// <summary> /// Creates an <see cref="OleDbCommand"/> based on the provided sql string /// </summary> /// <param name="sql"></param> /// <returns></returns> public OleDbCommand GetCommand(string sql) {
return new OleDbCommand(sql, Connection);
}
/// <summary> /// Executes the sql statement /// </summary> public int ExecuteNonQuery(string sql) {
OpenConnection(); try {
return GetCommand(sql).ExecuteNonQuery();
} finally {
CloseConnection();
}
} public object ExecuteScalar(string sql) {
OpenConnection(); try {
return GetCommand(sql).ExecuteScalar();
} finally {
CloseConnection();
}
} /// <summary> /// Returns the collection of tables listed in the directory/dbc file /// </summary> /// <returns></returns> public string[] GetTables() {
try {
OpenConnection(); DataTable dt = Connection.GetSchema("Tables"); string[] res = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) {
res[i++] = dr["Table_name"].ToString();
} return res;
} finally {
CloseConnection();
}
}
/// <summary> /// Returns the collection of fieldnames in the specified table /// </summary> /// <param name="Table"></param> /// <returns></returns> public string[] GetFields(string Table) {
try {
OpenConnection(); DataTable dt = Connection.GetSchema("Columns", new string[] { null, null, Table }); string[] res = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) {
res[i++] = dr["Column_name"].ToString();
} return res;
} finally {
CloseConnection();
}
}
Filler FillData(string sql,DataTable dt) {
OpenConnection(); try {
Filler f = new Filler(sql, this); f.Fill(dt); return f;
} finally {
CloseConnection();
}
}
internal class Filler {
public readonly OleDbDataAdapter da; FoxProConnection conn; string sql; public Filler(string sql,FoxProConnection conn) {
da = conn.GetDataAdapter(sql); this.sql = sql; this.conn = conn;
}
public List<Exception> FillErrors;
public void Fill(DataTable dt) {
da.FillError += new FillErrorEventHandler(da_FillError); try {
da.Fill(dt);
} finally {
da.FillError -= new FillErrorEventHandler(da_FillError);
/// <summary> /// Specifies that all oledb fill errors should be ignored and filling should continue. /// Errored rows are simply skipped so be careful with use. In general: only use if you /// know where the fault is and you just want to skip it ;-) /// </summary> public bool ContinueFillOnError {
get { return continueonErr; } set { continueonErr = value; }
}
public QueryResult GetData(string sql) {
return GetData(sql, new DataTable());
} public QueryResult GetData(string sql,DataTable dt) {
Filler f = FillData(sql, dt); QueryResult res = new QueryResult(this, dt, f); OnAfterGetData(res); return res;
o = Activator.CreateInstance(dt.Columns[i].DataType);
dr[i] = o;
}
}
}
public void UpdateData(OleDbDataAdapter da, DataTable dt) {
FoxProCommandBuilder builder = new FoxProCommandBuilder(da,dt);
try {
da.Update(dt);
} catch {
throw;
}
}
public OleDbDataAdapter GetDataAdapter(string sql) {
return new OleDbDataAdapter(sql, Connection);
} //public const int MaxQueryLength = 5000;
#region temp tables #region IDisposable Members
public void Dispose() {
Dispose(false);
} void Dispose(bool fromFinalizer) {
if (temptables == null) return; if(!fromFinalizer)
GC.SuppressFinalize(this);
foreach (TempTable t in temptables) {
t.Dispose();
} temptables.Clear(); temptables = null;
} ~FoxProConnection() {
Dispose(true);
}
List<TempTable> temptables;
public TempTable CreateTempTable(DataTable dt) {
return CreateTempTable(dt, null);
} public TempTable CreateTempTable(DataTable dt,string FileName) {
if (FileName == null)
FileName = DataBaseFolder + @"\" + FileName;
string Name = dt.TableName; if(Name.Length==0)dt.TableName= "tmpTable"; TempTable t = new TempTable(this,dt); if (temptables == null) {
temptables = new List<TempTable>(); GC.ReRegisterForFinalize(this);
} temptables.Add(t); return t;
}
#endregion #endregion
}
/// <summary> /// The result returned by a <see cref="FoxProConnect.GetData"/> command by a foxpro connection /// </summary> public class QueryResult:System.ComponentModel.IListSource {
public readonly DataTable Table; public readonly OleDbDataAdapter Adapter; public readonly FoxProConnection Connection; /// <summary> /// In case the <see cref="Connection"/> had the <see cref="FoxProConnection.ContinueFillOnError"/> property /// set to true, this array contains the errors encountered while filling (if any) /// </summary> public readonly Exception[] FillErrors; internal QueryResult(FoxProConnection conn, DataTable dt, FoxProConnection.Filler f) {
if (dc.MaxLength > 0 || dc.DataType != typeof(string)) return dc.MaxLength; int c = dt.Columns.IndexOf(dc); int max = 20; for (int i = 0; i < dt.Rows.Count; i++) {
if (!dt.Rows[i].IsNull(c)) {
int len = (dt.Rows[i][c] as string).Length; if (len > max)
max = len;
}
} return max;
}
public abstract string GetTypeIndicator(Type DataType, int Length); public int MaximumFieldNameLength = 255; public string NotAllowed = @" ()[]<>-\/?;"; public char ReplacementChar = '_'; public virtual void FormatFieldName(ref string Name) {
if (MaximumFieldNameLength > 0 && Name.Length > MaximumFieldNameLength)
Name = Name.Remove(MaximumFieldNameLength);
foreach (char c in NotAllowed) {
Name = Name.Replace(c, ReplacementChar);
}
}
}
public class FoxProTableCreator:TableCreator {
public FoxProTableCreator(IDbConnection conn, DataTable dt, bool StructureOnly)
:base(conn,dt,StructureOnly)
{
MaximumFieldNameLength = 10;
}
/* * FieldType nFieldWidth nPrecision Description
C n Character field of width n D Date T DateTime N n d Numeric field of width n with d decimal places F n d Floating numeric field of width n with d decimal places I Integer B d Double Y Currency L Logical M Memo G General
*/ public override string GetTypeIndicator(Type dt,int Length) {
if (dt == typeof(int) || dt == typeof(long) || dt==typeof(byte))
return "I";
if (dt == typeof(double) || dt == typeof(float))
return "B";
if (dt == typeof(decimal))
return "Y";
throw new Exception("DataType not supported: " + dt.ToString());
}
}
/// <summary> /// A temporary table. Do not create directly, but create with the /// CreateTempTable function /// </summary> public class TempTable:FoxProTableCreator, IDisposable {
public readonly FoxProConnection conn; internal TempTable(FoxProConnection conn, DataTable dt)