Create Data Classes

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

namespace JGCreateDataClasses
{
	static class JSavedProjectUDSQLite
	{
		internal static string m_DBPath = JCommon.DatabasePath;
		internal static string m_TableName = "tblSavedProject";

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

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

				SQLiteCommand command = GetInsertCommand(savedProject);
				command.Connection = dbConnection;

				// Execute the command
				command.ExecuteScalar();

				// Create Command to get IDENTITY Value
				SQLiteCommand oleCmdGetIdentity = new SQLiteCommand();
				oleCmdGetIdentity.CommandText = "SELECT last_insert_rowid()";
				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<JSavedProject.JSavedProjectIndexData> AllIndexData()
		{
			List<JSavedProject.JSavedProjectIndexData> listRecords = new List<JSavedProject.JSavedProjectIndexData>();

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

			return GetIndexDataList(selectString);
		}

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

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

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

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

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

		//	ExecuteNonQuery
		internal static bool ExecuteNonQuery(string commandString)
		{
			try
			{
				using (SQLiteConnection dbConnection = GetDBConnection())
				{
					dbConnection.Open();
					SQLiteCommand dbCommand = new SQLiteCommand(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 (SQLiteConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				SQLiteDataAdapter 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<JSavedProject.JSavedProjectIndexData> GetIndexDataList(string selectString)
		{
			List<JSavedProject.JSavedProjectIndexData> listRecords = new List<JSavedProject.JSavedProjectIndexData>();
			JSavedProject.JSavedProjectIndexData indexData;

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

				SQLiteDataAdapter 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 JSavedProject.JSavedProjectIndexData();
					GetRecordDetailsForIndexdata(dataRow, ref indexData);
					listRecords.Add(indexData);
				}
			}
			return listRecords;
		}

		//	GetInsertCommand
		private static SQLiteCommand GetInsertCommand(JSavedProject savedProject)
		{
			string insertCommand;

			insertCommand = "INSERT INTO " + m_TableName;
			insertCommand += "(";
			insertCommand += " LastUpdated,";
			insertCommand += " ClassNameSpace,";
			insertCommand += " ClassName,";
			insertCommand += " ClassFileSaveDirectory,";
			insertCommand += " ClassDataSource,";
			insertCommand += " ClassScope,";
			insertCommand += " ModuleVariablePrefix,";
			insertCommand += " AdditionalFunctions,";
			insertCommand += " AdditionalFields,";
			insertCommand += " AccessDatabasePath,";
			insertCommand += " AccessTableName,";
			insertCommand += " IsAccess12,";
			insertCommand += " UseADODB,";
			insertCommand += " SQLiteDatabasePath,";
			insertCommand += " SQLiteTableName,";
			insertCommand += " XMLDatabasePath,";
			insertCommand += " XMLTableName,";
			insertCommand += " XMLNameSpace,";
			insertCommand += " XMLDataSetName";
			insertCommand += " )";

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

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

			commandInsert.Parameters.Add(new SQLiteParameter("@LastUpdated", DateTime.Now.ToString()));
			commandInsert.Parameters.Add(new SQLiteParameter("@ClassNameSpace", savedProject.ClassNameSpace));
			commandInsert.Parameters.Add(new SQLiteParameter("@ClassName", savedProject.ClassName));
			commandInsert.Parameters.Add(new SQLiteParameter("@ClassFileSaveDirectory", savedProject.ClassFileSaveDirectory));
			commandInsert.Parameters.Add(new SQLiteParameter("@ClassDataSource", savedProject.ClassDataSource));
			commandInsert.Parameters.Add(new SQLiteParameter("@ClassScope", savedProject.ClassScope));
			commandInsert.Parameters.Add(new SQLiteParameter("@ModuleVariablePrefix", savedProject.ModuleVariablePrefix));
			commandInsert.Parameters.Add(new SQLiteParameter("@AdditionalFunctions", savedProject.AdditionalFunctions));
			commandInsert.Parameters.Add(new SQLiteParameter("@AdditionalFields", savedProject.AdditionalFields));
			commandInsert.Parameters.Add(new SQLiteParameter("@AccessDatabasePath", savedProject.AccessDatabasePath));
			commandInsert.Parameters.Add(new SQLiteParameter("@AccessTableName", savedProject.AccessTableName));
			commandInsert.Parameters.Add(new SQLiteParameter("@IsAccess12", savedProject.IsAccess12));
			commandInsert.Parameters.Add(new SQLiteParameter("@UseADODB", savedProject.UseADODB));
			commandInsert.Parameters.Add(new SQLiteParameter("@SQLiteDatabasePath", savedProject.SQLiteDatabasePath));
			commandInsert.Parameters.Add(new SQLiteParameter("@SQLiteTableName", savedProject.SQLiteTableName));
			commandInsert.Parameters.Add(new SQLiteParameter("@XMLDatabasePath", savedProject.XMLDatabasePath));
			commandInsert.Parameters.Add(new SQLiteParameter("@XMLTableName", savedProject.XMLTableName));
			commandInsert.Parameters.Add(new SQLiteParameter("@XMLNameSpace", savedProject.XMLNameSpace));
			commandInsert.Parameters.Add(new SQLiteParameter("@XMLDataSetName", savedProject.XMLDataSetName));

			return commandInsert;
		}

		//	GetDBConnection
		internal static SQLiteConnection GetDBConnection()
		{
			string connectionString = "Data Source = ";
			connectionString += m_DBPath;
			SQLiteConnection connection = null;
			try
			{
				connection = new SQLiteConnection(connectionString);
			}
			catch
			{
			}
			return connection;
		}

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

			GetSingleRecord(selectString, ref savedProject);
		}

		//	GetRecord (From string Override)
		internal static void GetRecord(string className, JSavedProject savedProject)
		{
			//	Create the SelectCommand.
			string selectString = "SELECT *  FROM " + m_TableName;
			selectString += " WHERE ClassName = " + "\"" + className + "\"";

			GetSingleRecord(selectString, ref savedProject);
		}

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

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

			try
			{
				savedProject.RecordNumber = Convert.ToInt32(dataRow["RecordNumber"]);
			}
			catch { }
			savedProject.LastUpdated = Convert.ToString(dataRow["LastUpdated"]);
			savedProject.ClassNameSpace = Convert.ToString(dataRow["ClassNameSpace"]);
			savedProject.ClassName = Convert.ToString(dataRow["ClassName"]);
			savedProject.ClassFileSaveDirectory = Convert.ToString(dataRow["ClassFileSaveDirectory"]);
			try
			{
				savedProject.ClassDataSource = (JCommon.ClassDataTypes)Convert.ToInt32(dataRow["ClassDataSource"]);
			}
			catch { }
			savedProject.ClassScope = Convert.ToString(dataRow["ClassScope"]);
			savedProject.ModuleVariablePrefix = Convert.ToString(dataRow["ModuleVariablePrefix"]);
			savedProject.AdditionalFunctions = Convert.ToString(dataRow["AdditionalFunctions"]);
			savedProject.AdditionalFields = Convert.ToString(dataRow["AdditionalFields"]);
			savedProject.AccessDatabasePath = Convert.ToString(dataRow["AccessDatabasePath"]);
			savedProject.AccessTableName = Convert.ToString(dataRow["AccessTableName"]);
			savedProject.IsAccess12 = Convert.ToBoolean(dataRow["IsAccess12"]);
			savedProject.UseADODB = Convert.ToBoolean(dataRow["UseADODB"]);
			savedProject.SQLiteDatabasePath = Convert.ToString(dataRow["SQLiteDatabasePath"]);
			savedProject.SQLiteTableName = Convert.ToString(dataRow["SQLiteTableName"]);
			savedProject.XMLDatabasePath = Convert.ToString(dataRow["XMLDatabasePath"]);
			savedProject.XMLTableName = Convert.ToString(dataRow["XMLTableName"]);
			savedProject.XMLNameSpace = Convert.ToString(dataRow["XMLNameSpace"]);
			savedProject.XMLDataSetName = Convert.ToString(dataRow["XMLDataSetName"]);

			return result;
		}

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

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

			return result;
		}

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

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

				SQLiteDataAdapter 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)
				{
					savedProject = new JSavedProject();
					GetRecordDetails(dataRow, ref savedProject);
					listRecords.Add(savedProject);
				}
			}
			return listRecords;
		}

		//	GetSingleRecord
		internal static JSavedProject GetSingleRecord(string selectString)
		{
			JSavedProject savedProject = new JSavedProject();

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

				SQLiteDataAdapter 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 savedProject);
				}
			}
			return savedProject;
		}

		//	GetSingleRecord
		private static void GetSingleRecord(string selectString, ref JSavedProject savedProject)
		{
			//	Create Connection
			using (SQLiteConnection dbConnection = GetDBConnection())
			{
				dbConnection.Open();

				SQLiteDataAdapter 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 savedProject);
				}
			}
		}

		//	GetUpdateCommand
		private static SQLiteCommand GetUpdateCommand(JSavedProject savedProject)
		{
			string cmdText;

			cmdText = "UPDATE " + m_TableName;
			cmdText += " set LastUpdated = ?,";
			cmdText += " ClassNameSpace = ?,";
			cmdText += " ClassName = ?,";
			cmdText += " ClassFileSaveDirectory = ?,";
			cmdText += " ClassDataSource = ?,";
			cmdText += " ClassScope = ?,";
			cmdText += " ModuleVariablePrefix = ?,";
			cmdText += " AdditionalFunctions = ?,";
			cmdText += " AdditionalFields = ?,";
			cmdText += " AccessDatabasePath = ?,";
			cmdText += " AccessTableName = ?,";
			cmdText += " IsAccess12 = ?,";
			cmdText += " UseADODB = ?,";
			cmdText += " SQLiteDatabasePath = ?,";
			cmdText += " SQLiteTableName = ?,";
			cmdText += " XMLDatabasePath = ?,";
			cmdText += " XMLTableName = ?,";
			cmdText += " XMLNameSpace = ?,";
			cmdText += " XMLDataSetName = ?";
			cmdText += " WHERE RecordNumber = ";
			cmdText += savedProject.RecordNumber.ToString();

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

			commandUpdate.Parameters.Add(new SQLiteParameter("LastUpdated", DateTime.Now.ToString()));
			commandUpdate.Parameters.Add(new SQLiteParameter("ClassNameSpace", savedProject.ClassNameSpace));
			commandUpdate.Parameters.Add(new SQLiteParameter("ClassName", savedProject.ClassName));
			commandUpdate.Parameters.Add(new SQLiteParameter("ClassFileSaveDirectory", savedProject.ClassFileSaveDirectory));
			commandUpdate.Parameters.Add(new SQLiteParameter("ClassDataSource", savedProject.ClassDataSource));
			commandUpdate.Parameters.Add(new SQLiteParameter("ClassScope", savedProject.ClassScope));
			commandUpdate.Parameters.Add(new SQLiteParameter("ModuleVariablePrefix", savedProject.ModuleVariablePrefix));
			commandUpdate.Parameters.Add(new SQLiteParameter("AdditionalFunctions", savedProject.AdditionalFunctions));
			commandUpdate.Parameters.Add(new SQLiteParameter("AdditionalFields", savedProject.AdditionalFields));
			commandUpdate.Parameters.Add(new SQLiteParameter("AccessDatabasePath", savedProject.AccessDatabasePath));
			commandUpdate.Parameters.Add(new SQLiteParameter("AccessTableName", savedProject.AccessTableName));
			commandUpdate.Parameters.Add(new SQLiteParameter("IsAccess12", savedProject.IsAccess12));
			commandUpdate.Parameters.Add(new SQLiteParameter("UseADODB", savedProject.UseADODB));
			commandUpdate.Parameters.Add(new SQLiteParameter("SQLiteDatabasePath", savedProject.SQLiteDatabasePath));
			commandUpdate.Parameters.Add(new SQLiteParameter("SQLiteTableName", savedProject.SQLiteTableName));
			commandUpdate.Parameters.Add(new SQLiteParameter("XMLDatabasePath", savedProject.XMLDatabasePath));
			commandUpdate.Parameters.Add(new SQLiteParameter("XMLTableName", savedProject.XMLTableName));
			commandUpdate.Parameters.Add(new SQLiteParameter("XMLNameSpace", savedProject.XMLNameSpace));
			commandUpdate.Parameters.Add(new SQLiteParameter("XMLDataSetName", savedProject.XMLDataSetName));

			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 (SQLiteConnection dbConnection = GetDBConnection())
				{
					dbConnection.Open();

					SQLiteCommand commandUpdate = new SQLiteCommand(cmdText, dbConnection);
					commandUpdate.Parameters.Add(new SQLiteParameter(fieldName, value));

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

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

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

				SQLiteCommand command = GetUpdateCommand(savedProject);
				command.Connection = dbConnection;

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

			}
			return result;
		}

	}



	#region Additional Functions Class


	static class JSavedProjectFunctionsSQLite
	{
	}


	#endregion


}