/// <summary>
/// Open a command based on the query string, connection and transaction,
/// and run ExecuteNonQuery.
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <param name="tran"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query, DbConnection conn, DbTransaction tran)
{
try
{
using (DbCommand cmd = GetCommand(query, conn))
{
cmd.Transaction = tran;
AddReturnValueParameter(cmd);
// NOTE: the connection state is explicitly control by the calling method ...
cmd.ExecuteNonQuery();
//return (int)cmd.Parameters["@ReturnValue"].Value;
if (cmd.Parameters.Contains("@ReturnValue"))
{
return((int)cmd.Parameters["@ReturnValue"].Value);
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "conn", conn);
DebuggerTool.AddData(ex, "tran", DebuggerTool.Dump(tran));
throw;
}
}
/// <summary>
/// Perform the GetOleDbSchemaTable operation and return a result as DataTable
/// </summary>
/// <param name="connectionString"></param>
/// <param name="schema"></param>
/// <param name="restrictions"></param>
/// <returns></returns>
public static DataTable QuerySchema(string connectionString, Guid schema, params object[] restrictions)
{
if (restrictions.Length != 4)
{
throw new ArgumentOutOfRangeException("Expected 4 restrictions; but found " + restrictions.Length);
}
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
try
{
return(conn.GetOleDbSchemaTable(schema, restrictions));
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "connectionString", connectionString);
DebuggerTool.AddData(ex, "schema", schema.ToString());
DebuggerTool.AddData(ex, "restrictions", restrictions.ToString());
throw;
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// Execute the stored procedure and return the @ReturnValue parameter
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public int ExecuteProcedure(DbCommand cmd)
{
try
{
bool isOpen = false;
AddReturnValueParameter(cmd);
if (cmd.Connection == null)
{
cmd.Connection = GetConnection();
}
isOpen = cmd.Connection.State == ConnectionState.Open;
if (!isOpen)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
if (!isOpen)
{
cmd.Connection.Close();
}
return((int)cmd.Parameters["@ReturnValue"].Value);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "cmd", DebuggerTool.Dump(cmd));
throw;
}
}
/// <summary>
/// fill a table
/// </summary>
/// <returns></returns>
public DataSet FillTables(string tablename, QueryFilter tableFilter)
{
try
{
var tdl = GetTables(tablename, tableFilter);
DataSet ds = new DataSet();
foreach (var item in tdl)
{
try
{
DataTable dt = FillTable("select * from [" + item.TableName + "]", item.TableName);
ds.Tables.Add(dt);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "item", item);
throw;
}
}
return(ds);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "tablename", tablename);
DebuggerTool.AddData(ex, "tableFilter", tableFilter);
throw;
}
}
/// <summary>
/// Execute a scalar function and return the result
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public object ExecuteScalar(DbCommand cmd)
{
try
{
if (cmd.Connection == null)
{
cmd.Connection = GetConnection();
}
bool isOpen = cmd.Connection.State == ConnectionState.Open;
if (!isOpen)
{
cmd.Connection.Open();
}
object retValue = cmd.ExecuteScalar();
if (!isOpen)
{
cmd.Connection.Close();
}
return(retValue);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "cmd", DebuggerTool.Dump(cmd));
throw;
}
}
////public DataTable GetColumns(string tableName)
////{
//// crudwork.DataAccess.TableManager tm = new crudwork.DataAccess.TableManager(crudwork.DataAccess.DatabaseProvider.OleDb, tableName, false);
//// return QuerySchema(this.connectionString, OleDbSchemaGuid.Columns, tm.Database, tm.Owner, tm.Tablename, null);
////}
////public DataTable GetPrimaryKeys(string tableName)
////{
//// crudwork.DataAccess.TableManager tm = new crudwork.DataAccess.TableManager(crudwork.DataAccess.DatabaseProvider.OleDb, tableName, false);
//// return QuerySchema(this.connectionString, OleDbSchemaGuid.Primary_Keys, tm.Database, tm.Owner, tm.Tablename, null);
////}
#endregion
/// <summary>
/// Query the Schema
/// </summary>
/// <param name="connectionString"></param>
/// <param name="schema"></param>
/// <param name="restrictions"></param>
/// <returns></returns>
public static DataTable QuerySchema(string connectionString, Guid schema, params object[] restrictions)
{
if (restrictions.Length != 4)
{
throw new ArgumentOutOfRangeException("Expected 4 restrictions; but found " + restrictions.Length);
}
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
List <string> results = new List <string>();
conn.Open();
try
{
// TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE,
// TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED
return(conn.GetOleDbSchemaTable(schema, restrictions));
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "schema", schema.ToString());
DebuggerTool.AddData(ex, "restrictions", restrictions.ToString());
throw;
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// Parse a SET clause
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static List <KeyValue> ParseSet(string value)
{
try
{
List <KeyValue> results = new List <KeyValue>();
if (string.IsNullOrEmpty(value))
{
return(results);
}
string[] tokens = Common.SplitTrim(value, StringSplitOptions.None, ",");
for (int i = 0; i < tokens.Length; i++)
{
string token = tokens[i];
string[] pair = Common.SplitTrim(token, StringSplitOptions.None, "=");
if (pair.Length != 2)
{
throw new ArgumentException("expected 2 tokens, but found " + pair.Length + "(token=" + token + ")");
}
results.Add(new KeyValue(pair[0], Common.Unqoute(pair[1])));
}
return(results);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "value", value);
throw;
}
}
public void TestConnection()
{
try
{
/*
* csm.Service = Filename;
*
* csm.IsIntegratedSecurity = false;
* csm.Password = Password;
*
* csm.SetOtherPair("read only", ReadOnly ? "True" : "False");
* csm.SetOtherPair("failifmissing", FileMustExist ? "True" : "False");
*/
DataFactory dataFactory = new DataFactory(DatabaseProvider, ConnectionString);
dataFactory.TestConnection();
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "Filename", Filename);
DebuggerTool.AddData(ex, "Password", Password);
DebuggerTool.AddData(ex, "ReadOnly", ReadOnly);
DebuggerTool.AddData(ex, "FileMustExist", FileMustExist);
DebuggerTool.AddData(ex, "Compress", Compress);
throw;
}
}
private static TList MapFieldToProperty <TList, TItem>(DataTable dt, FieldMapperList map)
{
try
{
var result = (TList)InstanceGenerator.Create(typeof(TList));
foreach (DataRow dr in dt.Rows)
{
var innerItem = (TItem)InstanceGenerator.Create(typeof(TItem));
#region Invoke property setter calls
foreach (var item in map)
{
try
{
object value = dr[item.ColumnName];
if (Convert.IsDBNull(value))
{
value = null;
}
DynamicCode.SetProperty(innerItem, item.PropertyName, value);
}
catch (Exception ex)
{
if (item.IsRequired)
{
// set breakpoint here to find out why something fails
Debug.WriteLine(ex.Message);
Debug.WriteLine(DebuggerTool.Dump(ex));
throw;
}
}
}
#endregion
#region Invoke the Add() method to add the inner item to list
try
{
DynamicCode.InvokeMethod(result, "Add", innerItem);
}
catch (Exception ex)
{
// set breakpoint here to find out why something fails
Debug.WriteLine(ex.Message);
throw;
}
#endregion
}
return(result);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt(XML)", DataUtil.DataTableToXml(dt));
Debug.WriteLine(ex.ToString());
throw;
}
}
void ITelephoneParser.Parse(string value)
{
try
{
StringBuilder buffer = new StringBuilder(value);
// remove format chars...
TestRoutine.RemoveFormatChar(buffer, delimiters);
if (!TestRoutine.IsDigits(buffer.ToString()))
{
// look for possible extension
int pos = -1, len = -1;
if (TestRoutine.FindToken(buffer, extensionTokens, out pos, out len))
{
// get the extension number.
int pos2 = pos + len;
string extension = buffer.ToString().Substring(pos2);
this.Extension = extension;
// remove everything past the extension token.
buffer.Remove(pos, buffer.Length);
}
}
string buf = buffer.ToString();
switch (buf.Length)
{
case 11:
if (buf[0] != '1')
{
goto default;
}
buf.Remove(0, 1);
goto case 10;
case 10:
this.Areacode = buf.Substring(0, 3);
this.Prefix = buf.Substring(3, 3);
this.Suffix = buf.Substring(6, 4);
break;
case 7:
this.Prefix = buf.Substring(0, 3);
this.Suffix = buf.Substring(3, 4);
break;
default:
throw new ArgumentOutOfRangeException("invalid phone: " + buf);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "value", value);
throw;
}
}
private static void InternalSetter(object myObject, string name, object value, BindingFlags invokeAttr)
{
try
{
try
{
Type t = myObject.GetType();
t.InvokeMember(name, invokeAttr, null, myObject, new object[] { value });
return;
}
catch (MissingMemberException ex)
{
Debug.WriteLine("Try 1: " + ex.Message);
}
int count = 1;
Type baseT = myObject.GetType().BaseType;
while (baseT != null)
{
count++;
try
{
Type t = baseT;
t.InvokeMember(name, invokeAttr, null, myObject, new object[] { value });
return;
}
catch (MissingMemberException ex)
{
Debug.WriteLine(string.Format("Try {0}: {1}", count, ex.Message));
}
baseT = baseT.BaseType;
}
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
throw ex.InnerException;
}
throw ex;
}
// --------------------------------------------------------------
// if all fails, throw an exception.
// --------------------------------------------------------------
{
var ex = new MissingMemberException("Unable to set property/field name: " + name);
DebuggerTool.AddData(ex, "name", name);
DebuggerTool.AddData(ex, "value", value);
DebuggerTool.AddData(ex, "value.GetType()", value.GetType().FullName);
throw ex;
}
}
/// <summary>
/// Update changes (insert,update,delete) from DataSet to database provider, and
/// retrieve any changes from the database provider to the DataSet.
/// </summary>
/// <param name="dt"></param>
public void UpdateTable(DataTable dt)
{
try
{
UpdateTable(dt, "select * from " + dt.TableName);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
throw;
}
}
/// <summary>
/// Update changes (insert,update,delete) from DataSet to database provider, and
/// retrieve any changes from the database provider to the DataSet.
/// </summary>
/// <param name="dt"></param>
/// <param name="query"></param>
public void SychronizeTable(DataTable dt, string query)
{
try
{
UpdateTable(dt, query);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
DebuggerTool.AddData(ex, "query", query);
throw;
}
}
/// <summary>
/// Open a DataAdapter with a query string, and fill the DataTable.
/// </summary>
/// <param name="dt"></param>
/// <param name="query"></param>
/// <param name="parameters"></param>
public void FillTable(DataTable dt, string query, params DbParameter[] parameters)
{
try
{
FillTable(dt, query, this.connectionString, parameters);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "parameters", DebuggerTool.Dump(parameters));
throw;
}
}
public static void Export(DataSet ds, string filename, ConverterOptionList options)
{
try
{
IFileConverter converter = GetConverter(filename, options);
converter.Export(ds, filename);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
DebuggerTool.AddData(ex, "filename", filename);
DebuggerTool.AddData(ex, "options", options);
throw;
}
}
public static DataSet Import(string filename, ConverterOptionList options)
{
try
{
IFileConverter converter = GetConverter(filename, options);
return(converter.Import(filename));
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
DebuggerTool.AddData(ex, "filename", filename);
DebuggerTool.AddData(ex, "options", options);
throw;
}
}
/// <summary>
/// Test connectivity of the default connection string.
/// </summary>
public void TestConnection()
{
try
{
if (String.IsNullOrEmpty(this.connectionString))
{
throw new ArgumentNullException("connectionString");
}
TestConnection(this.connectionString);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "connectionString", this.connectionString);
throw;
}
}
/// <summary>
/// Open a command baesd on the query string, connection and transaction,
/// adn run ExecuteNonQuery
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public int ExecuteNonQuery(DbCommand cmd)
{
bool isOpen = false;
try
{
AddReturnValueParameter(cmd);
if (cmd.Connection == null)
{
cmd.Connection = GetConnection();
}
isOpen = cmd.Connection.State == ConnectionState.Open;
if (!isOpen)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
if (!cmd.Parameters.Contains("@ReturnValue"))
{
return(0);
}
else
{
return((int)cmd.Parameters["@ReturnValue"].Value);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "cmd", DebuggerTool.Dump(cmd));
throw;
}
finally
{
if (!isOpen)
{
cmd.Connection.Close();
}
}
}
/// <summary>
/// Fill the DataTable with the command
/// </summary>
/// <param name="dt"></param>
/// <param name="command"></param>
public void FillTable(DataTable dt, DbCommand command)
{
try
{
using (DbDataAdapter da = GetDataAdapter(command))
{
dt.BeginLoadData();
dt.Clear();
da.Fill(dt);
dt.EndLoadData();
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
DebuggerTool.AddData(ex, "command", DebuggerTool.Dump(command));
throw;
}
}
/// <summary>
/// Execute a SQL command and return a DataSet instance
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet Fill(string query, params DbParameter[] parameters)
{
try
{
using (DbCommand cmd = GetCommand(query, parameters))
using (DbDataAdapter da = GetDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "parameters", DebuggerTool.Dump(parameters));
throw;
}
}
/// <summary>
/// parses the ip number in dotted notation
/// </summary>
/// <param name="value"></param>
public void Parse(string value)
{
try
{
string[] tokens = value.Split('.');
if (tokens.Length != 4)
{
throw new ArgumentException("Invalid value. (Expected 4 values in '" + value + "'");
}
for (int i = 0; i < tokens.Length; i++)
{
byte n = Convert.ToByte(tokens[i]);
switch (i)
{
case 0:
N1 = n;
break;
case 1:
N2 = n;
break;
case 2:
N3 = n;
break;
case 3:
N4 = n;
break;
default:
throw new ArgumentOutOfRangeException("i=" + i);
}
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "value", value);
throw;
}
}
/// <summary>
/// Create a number of given type
/// </summary>
/// <param name="numberThreads"></param>
/// <param name="type"></param>
/// <param name="args"></param>
public void InitializeThread(int numberThreads, Type type, params object[] args)
{
try
{
Clear();
for (int i = 0; i < numberThreads; i++)
{
MultiThreadingBase <T, U> mtObject = (MultiThreadingBase <T, U>)Activator.CreateInstance(type, args);
Add(mtObject);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "numberThreads", numberThreads);
DebuggerTool.AddData(ex, "type", type.GetType());
DebuggerTool.AddData(ex, "args", args);
throw;
}
}
/// <summary>
/// Test connectivity of the specified connection string.
/// </summary>
/// <param name="connectionString"></param>
public void TestConnection(string connectionString)
{
try
{
if (String.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
using (DbConnection conn = GetConnection(connectionString))
{
conn.Open();
conn.Close();
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "connectionString", connectionString);
throw;
}
}
/// <summary>
/// Set the output value to the specified index.
/// </summary>
/// <param name="index"></param>
/// <param name="output"></param>
protected void SetOutput(int index, U output)
{
try
{
if (index < outputList.Count)
{
outputList[index] = output;
}
else
{
outputList.Add(output);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "index", index);
DebuggerTool.AddData(ex, "output", output);
throw;
}
}
/// <summary>
/// Open a command based on the query string with parameter,
/// and run ExecuteScalar.
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string query, params DbParameter[] parameters)
{
try
{
using (DbCommand cmd = GetCommand(query, this.connectionString, parameters))
{
cmd.Connection.Open();
object retValue = cmd.ExecuteScalar();
cmd.Connection.Close();
return(retValue);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "parameters", DebuggerTool.Dump(parameters));
throw;
}
}
/// <summary>
/// Fill a table
/// </summary>
/// <param name="query"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable FillTable(string query, string tableName)
{
try
{
using (OleDbConnection conn = new OleDbConnection(this.connectionString))
using (OleDbCommand cmd = new OleDbCommand(query, conn))
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataTable dt = new DataTable(tableName);
da.Fill(dt);
return(dt);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "tableName", tableName);
throw;
}
}
/// <summary>
/// Open a DataAdapter with a query string, and fill the DataTable.
/// </summary>
/// <param name="dt"></param>
/// <param name="query"></param>
/// <param name="conn"></param>
public void FillTable(DataTable dt, string query, DbConnection conn)
{
// NOTE: the connection state is explicitly control by the calling method ...
try
{
using (DbDataAdapter da = GetDataAdapter(query, conn))
{
dt.BeginLoadData();
dt.Clear();
da.Fill(dt);
dt.EndLoadData();
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "conn", conn);
throw;
}
}
/// <summary>
/// Open a command based on the query string and connection,
/// and run ExecuteNonQuery.
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query, DbConnection conn)
{
try
{
using (DbCommand cmd = GetCommand(query, conn))
{
AddReturnValueParameter(cmd);
// NOTE: the connection state is explicitly control by the calling method ...
cmd.ExecuteNonQuery();
return((int)cmd.Parameters["@ReturnValue"].Value);
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "conn", conn);
throw;
}
}
/// <summary>
/// Open a DataAdapter with a query string, and fill the DataTable.
/// </summary>
/// <param name="dt"></param>
/// <param name="query"></param>
/// <param name="connectionString"></param>
/// <param name="parameters"></param>
public void FillTable(DataTable dt, string query, string connectionString, params DbParameter[] parameters)
{
try
{
using (DbDataAdapter da = GetDataAdapter(query, connectionString, parameters))
{
dt.BeginLoadData();
dt.Clear();
da.Fill(dt);
dt.EndLoadData();
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "dt", dt);
DebuggerTool.AddData(ex, "query", query);
DebuggerTool.AddData(ex, "connectionString", connectionString);
DebuggerTool.AddData(ex, "parameters", DebuggerTool.Dump(parameters));
// TODO: save exception data.
throw;
}
}
/// <summary>
/// Execute a group of SQL commands
/// </summary>
/// <param name="queries"></param>
public void ScriptRunner(string[] queries)
{
try
{
using (DbConnection conn = GetConnection())
{
conn.Open();
using (DbTransaction tran = conn.BeginTransaction())
{
for (int lineNum = 0; lineNum < queries.Length; lineNum++)
{
//ScriptCommand sc = new ScriptCommand(queries[lineNum]);
try
{
ExecuteNonQuery(queries[lineNum], conn, tran);
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "lineNum", lineNum);
DebuggerTool.AddData(ex, "queries[lineNum]", queries[lineNum]);
tran.Rollback();
throw;
}
}
tran.Commit();
}
conn.Close();
}
}
catch (Exception ex)
{
DebuggerTool.AddData(ex, "queries", DebuggerTool.Dump(queries));
throw;
}
}
