Create Data Classes

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace JGCreateDataClasses
{
	static class JFieldsDataUDAC
	{
		private static string m_DBPath = JCommon.DatabasePath;
		internal static string m_TableName = "tblFieldsData";

		//	AddRecord
		internal static int AddRecord(JFieldsData fieldsData)
		{
			//	Add Only - Use UpdateRecord for existing records
			int result = 0;

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbCommand command = GetInsertCommand(fieldsData);
				command.Connection = dbConnection;

				// Execute the command
				command.ExecuteScalar();

				// Create Command to get IDENTITY Value
				OleDbCommand oleCmdGetIdentity = new OleDbCommand();
				oleCmdGetIdentity.CommandText = "SELECT @@IDENTITY";
				oleCmdGetIdentity.Connection = dbConnection;
				result = Int32.Parse(oleCmdGetIdentity.ExecuteScalar().ToString());

				// Close and dispose
				command.Dispose();
				oleCmdGetIdentity.Dispose();
			}
			// Set return value
			return result;
		}

		//	Returns all index data in a List
		internal static List<JFieldsData.JFieldsDataIndexData> AllIndexData()
		{
			List<JFieldsData.JFieldsDataIndexData> listRecords = new List<JFieldsData.JFieldsDataIndexData>();

			//	Create the SelectCommand.
			string selectString = "SELECT RecordNumber, PublicName FROM " + m_TableName;
			selectString += " ORDER BY PublicName";

			return GetIndexDataList(selectString);
		}

		//	Returns all records in a List
		internal static List<JFieldsData> AllRecords()
		{
			//	Create the SelectCommand.
			string selectString = "SELECT *  FROM " + m_TableName;
			selectString += " ORDER BY PublicName";
			return GetRecordList(selectString);
		}

		//	Returns all records in a List
		internal static List<JFieldsData> AllRecords(string sortField)
		{
			//	Create the SelectCommand.
			string selectString = "SELECT *  FROM " + m_TableName;
			selectString += " ORDER BY " + sortField;
			return GetRecordList(selectString);
		}

		private static OleDbDataAdapter CreateSelectAdapter(OleDbConnection connection, string selectString)
		{
			OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(selectString, connection);
			return oledbAdapter;
		}

		//	DeleteRecord
		internal static bool DeleteRecord(JFieldsData fieldsData)
		{
			//	Create Delete Command
			string deleteString = "DELETE * FROM ";
			deleteString += m_TableName;
			deleteString += " WHERE RecordNumber = ";
			deleteString += fieldsData.RecordNumber.ToString();

			try
			{
				using (OleDbConnection dbConnection = GetDBConnection())
				{
					dbConnection.Open();
					OleDbCommand dbCommand = new OleDbCommand(deleteString, dbConnection);
					dbCommand.ExecuteScalar();
				}
				return true;
			}
			catch
			{
				return false;
			}
		}

		//	ExecuteNonQuery
		internal static bool ExecuteNonQuery(string commandString)
		{
			try
			{
				using (OleDbConnection dbConnection = GetDBConnection())
				{
					dbConnection.Open();
					OleDbCommand dbCommand = new OleDbCommand(commandString, dbConnection);
					dbCommand.ExecuteNonQuery();
				}
				return true;
			}
			catch
			{
				return false;
			}
		}

		//	GetField
		internal static object GetField(Int32 recordNumber, string fieldName)
		{
			object obTemp = null;

			// Create the SelectCommand.
			string selectString = "SELECT " + fieldName + " FROM " + m_TableName;
			selectString += " WHERE RecordNumber = ";
			selectString += recordNumber.ToString();

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection, selectString);

				//	Create / Fill the dataset
				DataSet dataSet = new DataSet();
				dbDataAdapter.Fill(dataSet, m_TableName);

				// Get the Table from the Dataset
				DataTable dataTable = dataSet.Tables[0];
				DataRow dataRow = dataTable.Rows[0];
				obTemp = dataRow[fieldName].ToString();
			}
			return obTemp;
		}

		//	Returns all index data in a List
		internal static List<JFieldsData.JFieldsDataIndexData> GetIndexDataList(string selectString)
		{
			List<JFieldsData.JFieldsDataIndexData> listRecords = new List<JFieldsData.JFieldsDataIndexData>();
			JFieldsData.JFieldsDataIndexData indexData;

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection, selectString);

				//	Create / Fill the dataset
				DataSet dataSet = new DataSet();
				dbDataAdapter.Fill(dataSet, m_TableName);

				//	Get the Table from the Dataset
				DataTable dataTable = dataSet.Tables[0];

				// Loop through the Dataset and add each Row to the List
				foreach (DataRow dataRow in dataTable.Rows)
				{
					indexData = new JFieldsData.JFieldsDataIndexData();
					GetRecordDetailsForIndexdata(dataRow, ref indexData);
					listRecords.Add(indexData);
				}
			}
			return listRecords;
		}

		//	GetInsertCommand
		private static OleDbCommand GetInsertCommand(JFieldsData fieldsData)
		{
			string insertCommand;

			insertCommand = "INSERT INTO " + m_TableName;
			insertCommand += "(";
			insertCommand += " LastUpdated,";
			insertCommand += " FieldIndex,";
			insertCommand += " ParentRecordNumber,";
			insertCommand += " PublicName,";
			insertCommand += " DbType,";
			insertCommand += " TypeID,";
			insertCommand += " FieldLength,";
			insertCommand += " NetDataType,";
			insertCommand += " CSharpDataType,";
			insertCommand += " AccessDataType,";
			insertCommand += " SQLiteDataType,";
			insertCommand += " XMLDataType,";
			insertCommand += " IsAutoIncrement,";
			insertCommand += " IsKeyField,";
			insertCommand += " IsStringOverride,";
			insertCommand += " WantsOwnGet,";
			insertCommand += " WantsOwnUpdate,";
			insertCommand += " IsUserDefined,";
			insertCommand += " FormattedInitialValue,";
			insertCommand += " UDInitialValue";
			insertCommand += " )";

			insertCommand += " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			// Create the InsertCommand
			OleDbCommand commandInsert = new OleDbCommand(insertCommand);

			commandInsert.Parameters.Add(new OleDbParameter("@LastUpdated", DateTime.Now.ToString()));
			commandInsert.Parameters.Add(new OleDbParameter("@FieldIndex", fieldsData.FieldIndex));
			commandInsert.Parameters.Add(new OleDbParameter("@ParentRecordNumber", fieldsData.ParentRecordNumber));
			commandInsert.Parameters.Add(new OleDbParameter("@PublicName", fieldsData.PublicName));
			commandInsert.Parameters.Add(new OleDbParameter("@DbType", fieldsData.DbType));
			commandInsert.Parameters.Add(new OleDbParameter("@TypeID", fieldsData.TypeID));
			commandInsert.Parameters.Add(new OleDbParameter("@FieldLength", fieldsData.FieldLength));
			commandInsert.Parameters.Add(new OleDbParameter("@NetDataType", fieldsData.NetDataType));
			commandInsert.Parameters.Add(new OleDbParameter("@CSharpDataType", fieldsData.CSharpDataType));
			commandInsert.Parameters.Add(new OleDbParameter("@AccessDataType", fieldsData.AccessDataType));
			commandInsert.Parameters.Add(new OleDbParameter("@SQLiteDataType", fieldsData.SQLiteDataType));
			commandInsert.Parameters.Add(new OleDbParameter("@XMLDataType", fieldsData.XMLDataType));
			commandInsert.Parameters.Add(new OleDbParameter("@IsAutoIncrement", fieldsData.IsAutoIncrement));
			commandInsert.Parameters.Add(new OleDbParameter("@IsKeyField", fieldsData.IsKeyField));
			commandInsert.Parameters.Add(new OleDbParameter("@IsStringOverride", fieldsData.IsStringOverride));
			commandInsert.Parameters.Add(new OleDbParameter("@WantsOwnGet", fieldsData.WantsOwnGet));
			commandInsert.Parameters.Add(new OleDbParameter("@WantsOwnUpdate", fieldsData.WantsOwnUpdate));
			commandInsert.Parameters.Add(new OleDbParameter("@IsUserDefined", fieldsData.IsUserDefined));
			commandInsert.Parameters.Add(new OleDbParameter("@FormattedInitialValue", fieldsData.FormattedInitialValue));
			commandInsert.Parameters.Add(new OleDbParameter("@UDInitialValue", fieldsData.UDInitialValue));

			return commandInsert;
		}

		//	GetDBConnection
		internal static OleDbConnection GetDBConnection()
		{
			string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=";
			connectionString += m_DBPath;
			OleDbConnection connection = null;
			try
			{
				connection = new OleDbConnection(connectionString);
			}
			catch
			{
			}
			return connection;
		}

		//	GetRecord (From Key Field)
		internal static void GetRecord(Int32 recordNumber, JFieldsData fieldsData)
		{
			//	Create the SelectCommand
			string selectString = "SELECT *  FROM " + m_TableName;
			selectString += " WHERE RecordNumber = ";
			selectString += recordNumber.ToString();

			GetSingleRecord(selectString, ref fieldsData);
		}

		//	GetRecord (From string Override)
		internal static void GetRecord(string publicName, JFieldsData fieldsData)
		{
			//	Create the SelectCommand.
			string selectString = "SELECT *  FROM " + m_TableName;
			selectString += " WHERE PublicName = " + "\"" + publicName + "\"";

			GetSingleRecord(selectString, ref fieldsData);
		}

		//	GetRecordDetails
		private static bool GetRecordDetails(DataRow dataRow, ref JFieldsData fieldsData)
		{
			bool result = true;

			//	Use try catch for numeric fields which may not have been initialised

			try
			{
				fieldsData.RecordNumber = Convert.ToInt32(dataRow["RecordNumber"]);
			}
			catch { }
			fieldsData.LastUpdated = Convert.ToString(dataRow["LastUpdated"]);
			try
			{
				fieldsData.FieldIndex = Convert.ToInt32(dataRow["FieldIndex"]);
			}
			catch { }
			try
			{
				fieldsData.ParentRecordNumber = Convert.ToInt32(dataRow["ParentRecordNumber"]);
			}
			catch { }
			fieldsData.PublicName = Convert.ToString(dataRow["PublicName"]);
			fieldsData.DbType = Convert.ToString(dataRow["DbType"]);
			try
			{
				fieldsData.TypeID = Convert.ToInt32(dataRow["TypeID"]);
			}
			catch { }
			try
			{
				fieldsData.FieldLength = Convert.ToInt32(dataRow["FieldLength"]);
			}
			catch { }
			fieldsData.NetDataType = Convert.ToString(dataRow["NetDataType"]);
			fieldsData.CSharpDataType = Convert.ToString(dataRow["CSharpDataType"]);
			fieldsData.AccessDataType = Convert.ToString(dataRow["AccessDataType"]);
			fieldsData.SQLiteDataType = Convert.ToString(dataRow["SQLiteDataType"]);
			fieldsData.XMLDataType = Convert.ToString(dataRow["XMLDataType"]);
			fieldsData.IsAutoIncrement = Convert.ToBoolean(dataRow["IsAutoIncrement"]);
			fieldsData.IsKeyField = Convert.ToBoolean(dataRow["IsKeyField"]);
			fieldsData.IsStringOverride = Convert.ToBoolean(dataRow["IsStringOverride"]);
			fieldsData.WantsOwnGet = Convert.ToBoolean(dataRow["WantsOwnGet"]);
			fieldsData.WantsOwnUpdate = Convert.ToBoolean(dataRow["WantsOwnUpdate"]);
			fieldsData.IsUserDefined = Convert.ToBoolean(dataRow["IsUserDefined"]);
			fieldsData.FormattedInitialValue = Convert.ToString(dataRow["FormattedInitialValue"]);
			fieldsData.UDInitialValue = Convert.ToString(dataRow["UDInitialValue"]);

			return result;
		}

		//	GetRecordDetailsForIndexdata
		private static bool GetRecordDetailsForIndexdata(DataRow dataRow, ref JFieldsData.JFieldsDataIndexData indexData)
		{
			bool result = true;

			indexData.RecordNumber = Convert.ToInt32(dataRow["RecordNumber"]);
			indexData.PublicName = Convert.ToString(dataRow["PublicName"]);

			return result;
		}

		//	Returns required records in a List
		internal static List<JFieldsData> GetRecordList(string selectString)
		{
			List<JFieldsData> listRecords = new List<JFieldsData>();
			JFieldsData fieldsData;

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection, selectString);

				//	Create / Fill the dataset
				DataSet dataSet = new DataSet();
				dbDataAdapter.Fill(dataSet, m_TableName);

				//	Get the Table from the Dataset
				DataTable dataTable = dataSet.Tables[0];

				// Loop through the Dataset and add each Row to the List
				foreach (DataRow dataRow in dataTable.Rows)
				{
					fieldsData = new JFieldsData();
					GetRecordDetails(dataRow, ref fieldsData);
					listRecords.Add(fieldsData);
				}
			}
			return listRecords;
		}

		//	GetSingleRecord
		internal static JFieldsData GetSingleRecord(string selectString)
		{
			JFieldsData fieldsData = new JFieldsData();

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection, selectString);

				//	Create / Fill the dataset
				DataSet dataSet = new DataSet();
				dbDataAdapter.Fill(dataSet, m_TableName);

				//	Get the Table from the Dataset
				DataTable dataTable = dataSet.Tables[0];

				if (dataTable.Rows.Count > 0)
				{
					DataRow dataRow = dataTable.Rows[0];
					GetRecordDetails(dataRow, ref fieldsData);
				}
			}
			return fieldsData;
		}

		//	GetSingleRecord
		private static void GetSingleRecord(string selectString, ref JFieldsData fieldsData)
		{
			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbDataAdapter dbDataAdapter = CreateSelectAdapter(dbConnection, selectString);

				//	Create / Fill the dataset
				DataSet dataSet = new DataSet();
				dbDataAdapter.Fill(dataSet, m_TableName);

				//	Get the Table from the Dataset
				DataTable dataTable = dataSet.Tables[0];

				if (dataTable.Rows.Count > 0)
				{
					DataRow dataRow = dataTable.Rows[0];
					GetRecordDetails(dataRow, ref fieldsData);
				}
			}
		}

		//	GetUpdateCommand
		private static OleDbCommand GetUpdateCommand(JFieldsData fieldsData)
		{
			string cmdText;

			cmdText = "UPDATE " + m_TableName;
			cmdText += " set LastUpdated = ?,";
			cmdText += " FieldIndex = ?,";
			cmdText += " ParentRecordNumber = ?,";
			cmdText += " PublicName = ?,";
			cmdText += " DbType = ?,";
			cmdText += " TypeID = ?,";
			cmdText += " FieldLength = ?,";
			cmdText += " NetDataType = ?,";
			cmdText += " CSharpDataType = ?,";
			cmdText += " AccessDataType = ?,";
			cmdText += " SQLiteDataType = ?,";
			cmdText += " XMLDataType = ?,";
			cmdText += " IsAutoIncrement = ?,";
			cmdText += " IsKeyField = ?,";
			cmdText += " IsStringOverride = ?,";
			cmdText += " WantsOwnGet = ?,";
			cmdText += " WantsOwnUpdate = ?,";
			cmdText += " IsUserDefined = ?,";
			cmdText += " FormattedInitialValue = ?,";
			cmdText += " UDInitialValue = ?";
			cmdText += " WHERE RecordNumber = ";
			cmdText += fieldsData.RecordNumber.ToString();

			// Create the UpdateCommand
			OleDbCommand commandUpdate = new OleDbCommand(cmdText);

			commandUpdate.Parameters.Add(new OleDbParameter("LastUpdated", DateTime.Now.ToString()));
			commandUpdate.Parameters.Add(new OleDbParameter("FieldIndex", fieldsData.FieldIndex));
			commandUpdate.Parameters.Add(new OleDbParameter("ParentRecordNumber", fieldsData.ParentRecordNumber));
			commandUpdate.Parameters.Add(new OleDbParameter("PublicName", fieldsData.PublicName));
			commandUpdate.Parameters.Add(new OleDbParameter("DbType", fieldsData.DbType));
			commandUpdate.Parameters.Add(new OleDbParameter("TypeID", fieldsData.TypeID));
			commandUpdate.Parameters.Add(new OleDbParameter("FieldLength", fieldsData.FieldLength));
			commandUpdate.Parameters.Add(new OleDbParameter("NetDataType", fieldsData.NetDataType));
			commandUpdate.Parameters.Add(new OleDbParameter("CSharpDataType", fieldsData.CSharpDataType));
			commandUpdate.Parameters.Add(new OleDbParameter("AccessDataType", fieldsData.AccessDataType));
			commandUpdate.Parameters.Add(new OleDbParameter("SQLiteDataType", fieldsData.SQLiteDataType));
			commandUpdate.Parameters.Add(new OleDbParameter("XMLDataType", fieldsData.XMLDataType));
			commandUpdate.Parameters.Add(new OleDbParameter("IsAutoIncrement", fieldsData.IsAutoIncrement));
			commandUpdate.Parameters.Add(new OleDbParameter("IsKeyField", fieldsData.IsKeyField));
			commandUpdate.Parameters.Add(new OleDbParameter("IsStringOverride", fieldsData.IsStringOverride));
			commandUpdate.Parameters.Add(new OleDbParameter("WantsOwnGet", fieldsData.WantsOwnGet));
			commandUpdate.Parameters.Add(new OleDbParameter("WantsOwnUpdate", fieldsData.WantsOwnUpdate));
			commandUpdate.Parameters.Add(new OleDbParameter("IsUserDefined", fieldsData.IsUserDefined));
			commandUpdate.Parameters.Add(new OleDbParameter("FormattedInitialValue", fieldsData.FormattedInitialValue));
			commandUpdate.Parameters.Add(new OleDbParameter("UDInitialValue", fieldsData.UDInitialValue));

			return commandUpdate;
		}

		//	UpdateField
		internal static bool UpdateField(Int32 recordNumber, string fieldName, object value)
		{
			// Create the UpdateCommand
			string cmdText = "UPDATE " + m_TableName;
			cmdText += " set " + fieldName + " = ?";
			cmdText += " WHERE RecordNumber = ";
			cmdText += recordNumber.ToString();

			try
			{
				//	Create Connection)
				using (OleDbConnection dbConnection = GetDBConnection())
				{
					dbConnection.Open();

					OleDbCommand commandUpdate = new OleDbCommand(cmdText, dbConnection);
					commandUpdate.Parameters.Add(new OleDbParameter(fieldName, value));

					//	Execute
					commandUpdate.ExecuteNonQuery();
				}
				return true;
			}
			catch
			{
				return false;
			}
		}

		internal static bool UpdateRecord(JFieldsData fieldsData)
		{
			//	Update Only - Use AddRecord for new records
			bool result = false;

			//	Create Connection
			using (OleDbConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				OleDbCommand command = GetUpdateCommand(fieldsData);
				command.Connection = dbConnection;

				//Execute
				try
				{
					command.ExecuteNonQuery();
					result = true;
				}
				catch
				{
					result = false;
				}

			}
			return result;
		}

	}



	#region Additional Functions Class


	static class JFieldsDataFunctionsAC
	{
		internal static bool DeleteRecordsForParent(int parentRecNum)
		{
			string commandString = "DELETE FROM " + JFieldsDataUDAC.m_TableName;
			commandString += " WHERE ParentRecordNumber = " + parentRecNum.ToString();

			return JFieldsDataUDAC.ExecuteNonQuery(commandString);
		}

		internal static List<JFieldsData> GetFieldsDataList(int parentRecNum)
		{
			string selectString = "SELECT *  FROM " + JFieldsDataUDAC.m_TableName;
			selectString += " WHERE ParentRecordNumber = " + parentRecNum.ToString();
			selectString += " ORDER BY FieldIndex";

			return JFieldsDataUDAC.GetRecordList(selectString);
		}

	}


	#endregion

}