using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class dataTier
{
///
/// Methods and Functions for Returning Data
///
public class ReturnMethods
{
public ReturnMethods()
{ }
///
/// Return a Record Count from a Table
///
///
///
///
///
/// Returns the record count as an Integer
/// Graham 26/09/08
public static int ReturnRecordCount(ref SqlCommand command, string tableName, string fieldName, string strWhere)
{
//Variable Declaration
command.CommandText = "SELECT COUNT (" + fieldName + ") AS RecordCount FROM " + tableName + " WHERE " + strWhere;
SqlDataReader reader;
int Result = 0;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Execute the Reader
reader = command.ExecuteReader();
//Check the Reader
if (reader.Read())
{
//Assign the value to the Result
Result = reader.GetInt32(0);
}
//Dispose the Objects
reader.Close();
//Return the Result
return Result;
}
///
/// Function to return an Integer value
///
///
/// An Integer
/// Graham 29/09/08
public static int ReturnIntValue(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
int Result = 0;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//execute Reader
reader = command.ExecuteReader();
if (reader.Read())
{
Result = int.Parse(reader[0].ToString());
}
//Dispose the objects
reader.Close();
//return result
return Result;
}
///
/// Function to return a Decimal value
///
///
/// An Integer
/// Graham 29/09/08
public static decimal ReturnDecimalValue(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
decimal Result = 0;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//execute Reader
reader = command.ExecuteReader();
if (reader.Read())
{
Result = Convert.ToDecimal(reader[0].ToString());
}
//Dispose the objects
reader.Close();
//return result
return Result;
}
///
/// Return a Bit Value from a Table
///
///
/// A bit value
/// Graham 26/09/08
public static int ReturnBitValue(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
int intResult = 0;
//Check the connection state
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Execute the DataReader
reader = command.ExecuteReader();
if (reader.Read())
{
if (reader.IsDBNull(0) == false)
intResult = int.Parse(reader[0].ToString());
}
//Dispose the objects
reader.Close();
//Return the result
return intResult;
}
///
/// Function to return an String value
///
///
/// A String
/// Graham 29/09/08
public static string ReturnStringValue(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
string Result = String.Empty;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//execute Reader
reader = command.ExecuteReader();
if (reader.Read())
{
Result = reader.GetString(0);
}
//Dispose the objects
reader.Close();
//return result
return Result;
}
///
/// Function to return a DataSet from a SQL Table
///
///
/// A DataSet
/// Graham 26/09/08
public static DataSet ReturnDataSet(ref SqlCommand command)
{
//Variable declaration
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Assign the Data Adapter Select Command
adapter.SelectCommand = command;
//Fill the adapter
adapter.Fill(dataset);
//Dispose Objects
adapter.Dispose();
//Return the DataSet
return dataset;
}
///
/// Sql Data Reader
///
///
/// DataReader
public static SqlDataReader ReturnDataReader(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
//Check connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Execute Reader
reader = command.ExecuteReader();
//return Reader
return reader;
}
///
/// Boolean to check if a database record exists
///
///
/// True if record does exist
/// Graham 29/09/08
public static bool RecordExists(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
bool bRead = false;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Execute DataReader
reader = command.ExecuteReader();
//check reader
if (reader.HasRows)
{
bRead = true;
}
//Dispose the objects
reader.Close();
//return bRead
return bRead;
}
///
/// Boolean function to test a SQL query
///
///
/// True if query is correct
/// Graham 29/09/08
public static bool TestQuery(ref SqlCommand command)
{
//Variable Declaration
SqlDataReader reader;
bool bRead = false;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open Connection
command.Connection.Open();
//Execute SqlDataReader
reader = command.ExecuteReader();
//Dispozse the Objects
reader.Close();
//Assign bRead to true
bRead = true;
//Return bRead
return bRead;
}
///
/// Function returns the SQL connection
///
///
/// Graham 26/09/08
public static SqlConnection Connection(string key)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings[key]);
//Return Connection
return connection;
}
}
public class TransactionalMethods
{
public TransactionalMethods()
{ }
///
/// SQL Command to initialise a Transaction
///
/// SQL Command
public static SqlCommand BeginSQLTransaction()
{
SqlCommand command = new SqlCommand();
SqlConnection connection = ReturnMethods.Connection("conn");
//initialise the command
connection.Open();
command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = connection.BeginTransaction();
return command;
}
///
/// SQL Command to initialise a Transaction
///
///
/// SQL Command
public static SqlCommand BeginSQLTransaction(string connectionKey)
{
SqlCommand command = new SqlCommand();
SqlConnection connection = ReturnMethods.Connection(connectionKey);
//initialise the command
connection.Open();
command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = connection.BeginTransaction();
return command;
}
///
/// Method to commit the transaction
///
///
public static void CompleteSQLTransaction(ref SqlCommand command)
{
if (command != null)
{
if (command.Transaction != null)
{
//commit Transaction
command.Transaction.Commit();
}
if (command.Connection != null && command.Connection.State == ConnectionState.Open)
{
//close connection
command.Connection.Close();
}
//dispose command
command.Dispose();
}
}
}
///
/// Methods and Functions for Saving Data
///
public class SavingMethods
{
public SavingMethods()
{ }
///
/// Boolean to Add a Record to the Database
///
///
/// True if record was added successfully
/// Graham 29/09/08
public static bool AddRecord(ref SqlCommand command)
{
//Variable Declaration
bool Result = false;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open Connection
command.Connection.Open();
//Execute the Query
command.ExecuteNonQuery();
//Assign result
Result = true;
//Return result
return Result;
}
///
/// Boolean to Add a Record to the Database
///
///
/// True if record was added successfully
/// Graham 29/09/08
public static bool UpdateRecord(ref SqlCommand command)
{
//Variable Declaration
bool Result = false;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open Connection
command.Connection.Open();
//Execute the Query
command.ExecuteNonQuery();
//Assign result
Result = true;
//Return result
return Result;
}
///
/// Boolean to apply a query to the Database
///
///
/// True if successful
/// Graham 29/09/08
public static bool ApplyQuery(ref SqlCommand command)
{
//Variable Declaration
bool Result = false;
//Check Connection State
if (command.Connection.State == ConnectionState.Closed)
//Open Connection
command.Connection.Open();
//Execute the Query
command.ExecuteNonQuery();
//Assign result
Result = true;
//Return result
return Result;
}
///
/// Method to import a CSV file into a SQL Table
///
///
///
///
/// Graham 29/09/08
public static void ConvertDataSetToSql(ref SqlCommand command, DataSet Data, string TableName)
{
//Check connection State
if (command.Connection.State == ConnectionState.Closed)
//Open the connection
command.Connection.Open();
//Enumerate the Tables in the DataSet
foreach (DataTable Table in Data.Tables)
{
//Default the Column List
List ColumnsList = new List();
//Enumerate the Columns
foreach (DataColumn Column in Table.Columns)
{
//Append the column Name
ColumnsList.Add(String.Format("[{0}]", Column.ColumnName));
}
//Enumerate the table rows
foreach (DataRow Row in Table.Rows)
{
//Default the Columns List
List ValuesList = new List();
//Enumerate the Columns
foreach (DataColumn Column in Table.Columns)
{
if (Column.ColumnName == "CODE")
{
string NewCode = String.Empty;
NewCode = Row[Column].ToString();
if (NewCode.Length == 1)
{
NewCode = "000" + NewCode;
}
else if (NewCode.Length == 2)
{
NewCode = "00" + NewCode;
}
else if (NewCode.Length == 3)
{
NewCode = "0" + NewCode;
}
//Append the Values
ValuesList.Add(string.Format("'{0}'", NewCode));
}
else
{
//Append the Values
ValuesList.Add(string.Format("'{0}'", Row[Column].ToString()));
}
}
//Create Command for inserting the record
command.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", TableName, string.Join(", ", ColumnsList.ToArray()), string.Join(", ", ValuesList.ToArray()));
//Insert the record
command.ExecuteNonQuery();
}
}
}
}
///
/// Methods and Functions for Removing Data
///
public class RemoveMethods
{
public RemoveMethods()
{ }
///
/// Boolean function to Remove a Record
///
///
///
/// True if file is record is deleted
/// Graham 29/09/08
public static bool Delete(ref SqlCommand command)
{
//Variable declaration
bool Result = false;
//Check connection state
if (command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
//Execute query
command.ExecuteNonQuery();
//Assign result
Result = true;
//return result
return Result;
}
///
/// Method to Truncate the Table
///
/// Graham 30/09/08
public static bool ClearTable(ref SqlCommand command, string tableName)
{
//Variable declaration
bool Result = false;
//Variable Declaration
string strQuery = "Truncate Table " + tableName;
//set command text
command.CommandText = strQuery;
//Truncate Table
Result = Delete(ref command);
return Result;
}
}
}