using System;
using System.Data;
using System.Data.Odbc;
using System.Text;
namespace Postec.Micros.Database
{
public class MicrosDatabase : IDisposable
{
private OdbcConnection _OdbcConnection;
/// <summary>
/// Constructs a MicrosDatabase object with the default custom,custom username and password; also assumes default DSN of micros has been configured
/// </summary>
public MicrosDatabase()
{
_OdbcConnection = new OdbcConnection("DSN=micros;UID=custom;PWD=custom");
_OdbcConnection.Open();
}
/// <summary>
/// Coonstructs a MicrosDatabase object using default DSN of micros using provided Username and Password
/// </summary>
/// <param name="Username">Database username to use for connection; using DBA account is NOT recommended</param>
/// <param name="Password">Database user's password</param>
public MicrosDatabase(string Username, string Password)
{
_OdbcConnection = new OdbcConnection(string.Format("DSN=micros;UID={0};PWD={1}", Username, Password));
_OdbcConnection.Open();
}
/// <summary>
/// Constructions a MicrosDatabase using the provided DSN. Connects using the provided Username and Password.
/// </summary>
/// <param name="Username">Database username to use for connection; using DBA account is NOT recommended</param>
/// <param name="Password">Database user's password</param>
/// <param name="DSN">The DSN configured to connect to micros on the machine. See SysWOW64 folder for connection manager</param>
public MicrosDatabase(string Username, string Password, string DSN)
{
_OdbcConnection = new OdbcConnection(string.Format("DSN={0};UID={1};PWD={2}", DSN, Username, Password));
_OdbcConnection.Open();
}
/// <summary>
/// Returns a DataTable containing the results from the database query provided.
/// </summary>
/// <param name="Cmd">Database query to run against the database</param>
/// <returns>DataTable containing SQL result set</returns>
public DataTable Query(string Cmd)
{
using (OdbcCommand OdbcCmd = new OdbcCommand(Cmd, _OdbcConnection))
using (OdbcDataReader OdbcReader = OdbcCmd.ExecuteReader())
{
if (!OdbcReader.HasRows)
return null;
DataTable tempTable = new DataTable();
DataColumn tempColumn;
using (DataTable schemaTable = OdbcReader.GetSchemaTable())
{
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
tempColumn = new DataColumn((string)schemaTable.Rows[i]["ColumnName"], (Type)schemaTable.Rows[i]["DataType"]);
//have to do this because numeric fields can be null in the micros database
tempColumn.AllowDBNull = true;
tempTable.Columns.Add(tempColumn);
}
}
DataRow tempRow;
while (OdbcReader.Read())
{
tempRow = tempTable.NewRow();
for (int i = 0; i < OdbcReader.FieldCount; i++)
tempRow[i] = OdbcReader[i];
tempTable.Rows.Add(tempRow);
}
return tempTable;
}
}
/// <summary>
/// Runs a stored procedure
/// </summary>
/// <param name="SPName">The name of the stored procedure; full name. Eg: micros.sp_PurgeHistory</param>
/// <param name="SpParams">The parameters, if any, that the stored procedure requires</param>
/// <returns>DataTable containing SQL result set</returns>
public DataTable RunStoredProcedure(string SPName, SPParam[] SpParams = null)
{
if (SpParams == null || SpParams.Length < 1)
return this.Query(string.Format("call {0}", SPName));
StringBuilder sb = new StringBuilder();
sb.Append("call ").Append(SPName).Append("(").Append(SpParams[0].ToString());
for (int i = 1; i < SpParams.Length; i++)
sb.Append(",").Append(SpParams[i].ToString());
sb.Append(")");
return this.Query(sb.ToString());
}
/// <summary>
/// Executes a command against the database for which no return is expected.
/// Typically used for UPDATE, INSERT, and DELETE statements
/// </summary>
/// <param name="Cmd">Command to run agaisnt database</param>
/// <returns>The number of rows affected by the command.</returns>
public int Execute(string Cmd)
{
using (OdbcCommand OdbcCmd = new OdbcCommand(Cmd, _OdbcConnection))
{
return OdbcCmd.ExecuteNonQuery();
}
}
/// <summary>
/// Attempts to Reopen the database connection
/// </summary>
/// <returns>If the connection is now open</returns>
public bool ReopenConnection()
{
if (Connected)
return true;
_OdbcConnection.Open();
return Connected;
}
/// <summary>
/// Returns if the current Database Connection is open
/// </summary>
public bool Connected
{
get
{
return _OdbcConnection.State == ConnectionState.Open;
}
}
/// <summary>
/// Calls dispose
/// </summary>
public void Close()
{
this.Dispose();
}
/// <summary>
/// Calls Dispose(true)
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// Clean up of database connection is performed here
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_OdbcConnection != null)
{
_OdbcConnection.Dispose();
_OdbcConnection = null;
}
}
}
}
}
