A C# WPF user interface has been developed for the Book Inventory MySQL database previously shown in this question. Since the database had already been developed this was a database first implementation.
I did find that additional stored procedures were necessary to implement the Add Book dialog, due to the table normalization strategy used in the design of the database I was unable to utilize the stored procedures of adding a book to the library or buying a book. This question is specifically about the database interaction model.
Why didn’t I use the Entity Framework?
- At the time I started creating the models I didn’t know the entity framework could use stored procedures. I learned this after half the data table models were created.
- I looked through the code generated for the entity framework and didn’t see how I could implement the early error checking I wanted to perform. I really didn’t want to catch database errors for every possible problem to perform error checking.
Questions
- Was inheritance abused or over used?
- Is this a SOLID OOP design?
- Are there any odors?
- Were C# parameters used correctly?
- Are there any possible performance issues?
- Are the methods for Dictionaries or Lists that I didn’t use that would have reduced the amount of code or simplified the code?
The entire code for this project can be found on GitHub, including a newer version of the SQL files that create the database.
Figure 1 Select Author control.
The Select Author control, the Select Author Series List Box, each button in the more options group and each list box on the Add Book dialog all represent tables in the database. The values of each list box are stored as foreign keys in some of the tables.
Figure 2 Add Book Dialog
The architecture of the application is divided into models and views to separate the data and business model from the user interface. Within the models there is an additional division, there are models that represent the database tables and there are models that represent a row of data within each database table. A specialized super class was developed for the tables that contain the data for the list boxes in the Add Book dialog except for the Select Author List Box and the Select Author Series List Box, these are called dictionary table models as a class.
The database table models provide the actual interface to the database. In addition to calling stored procedures to store and retrieve the data they provide data about each table and stored procedure to the row data models for early error checking and validation. The database table models always reside in memory, but the data from the database is retrieved as necessary.
The database row data models provide storage of data until the data is inserted and perform error checking on the data as it is added to the row data model and prior to the insertion into the database. The base class for each database row data model is the DataTableItemBaseModel class.
The CDataTableModel is the base class for all database table classes. It
contains aggregations of the DbColumnParameterData and SqlCmdParameter
classes. The purpose of the DbColumnParameterData class is to provide
necessary information for each column in a database table. The purpose of the
SqlCmdParameter class is to provide information about every parameter in a
stored procedure call. The three dictionaries in each CDataTableModel provide
quick look up for the SqlCmdParameter aggregations based on 3 different naming
schemes, the common name within the application, the column name in the
database table and the parameter name in the stored procedure. This file
contains almost all the calls to stored procedure. Each class that inherits
from this class defines the names of the tables and the stored procedures.
This allows for generic SQL database calls.
Figure 3 Class diagram for CDataTableModel, DbColumnParameterData and SqlCmdParameter
Each instance of a database table row model references its data table model to acquire the DbColumnParameterData and the SqlCmdParameter for error checking and validation purposes.
Figure 4 The CDataTableModel and the DataTableItemBaseModel both aggregate the SqlCmdParameter class
Each public parameter in a DataTableItemBaseModel super class references an instance of the SqlCmdParameter class.
Figure 5 All of the super classes of the DataTableItemBaseModel currently in use
Figure 6 All of the Data Table Models and Inheritance
The Code:
CategoryTableModel.cs
This is an example of a super class of the DictionaryTableModel. In the user
interface category was renamed as Genre.
using System.Data;
using MySql.Data.MySqlClient;
using ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel;
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.Category
{
public class CategoryTableModel : DictionaryTableModel
{
public DataTable CategoryTable { get { return DataTable; } }
public CategoryTableModel() : base("bookcategories", "getAllBookCategoriesWithKeys", "addCategory")
{
}
public string CategoryTitle(uint Key)
{
return KeyToName(Key);
}
public uint CategoryKey(string CategoryTitle)
{
return NameToKey(CategoryTitle);
}
public void AddCategory(CategoryModel Category)
{
AddItemToDictionary(Category);
}
protected override void InitializeSqlCommandParameters()
{
MySqlParameterCollection parameters = AddItemParameters;
_addSqlCommandParameter("Name", GetDBColumnData("CategoryName"), parameters["@categoryName"]);
_addSqlCommandParameter("Primary Key", GetDBColumnData("idBookCategories"), parameters["@primaryKey"]);
}
}
}
AuthorTableModel.cs
This is an example of one of the more complex implementations of the
CDataTableModel class.
using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using ExperimentSimpleBkLibInvTool.ModelInMVC.DataTableModel;
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.Author
{
public class AuthorTableModel : CDataTableModel
{
private int AuthorIDColumnIndex;
private int LastNameColumnIndex;
private int FirstNameColumnIndex;
private int MiddleNameColumnIndex;
private int DobColumnIndex;
private int DodColumnIntex;
public DataTable AuthorTable { get { return DataTable; } }
public AuthorTableModel() : base("authorstab", "getAllAuthorsData", "addAuthor")
{
AuthorIDColumnIndex = GetDBColumnData("idAuthors").IndexBasedOnOrdinal;
LastNameColumnIndex = GetDBColumnData("LastName").IndexBasedOnOrdinal;
FirstNameColumnIndex = GetDBColumnData("FirstName").IndexBasedOnOrdinal;
MiddleNameColumnIndex = GetDBColumnData("MiddleName").IndexBasedOnOrdinal;
DobColumnIndex = GetDBColumnData("YearOfBirth").IndexBasedOnOrdinal;
DodColumnIntex = GetDBColumnData("YearOfDeath").IndexBasedOnOrdinal;
}
public bool AddAuthor(AuthorModel NewAuthor)
{
return addItem(NewAuthor);
}
#region Author Selector tool support
public DataRow[] FindAuthors(string lastName, string firstname=null)
{
DataTable dt = AuthorTable;
string filterString = "LastName LIKE '" + lastName + "*'";
DataRow[] authors = dt.Select(filterString);
return authors;
}
public uint AuthorKey(AuthorModel author)
{
uint key = author.AuthorId;
if (key < 1)
{
DataTable dt = AuthorTable;
string filterString = "LastName = '" + author.LastName + "' AND FirstName = '" + author.FirstName + "' AND MiddleName Like '" + author.MiddleName + "'";
DataRow[] authors = dt.Select(filterString);
if (authors.Length > 0)
{
if (!uint.TryParse(authors[0][AuthorIDColumnIndex].ToString(), out key))
{
key = 0;
}
}
else
{
key = 0;
}
}
return key;
}
public AuthorModel GetAuthorFromId(uint key)
{
AuthorModel author = null;
DataTable dt = AuthorTable;
string filterString = "idAuthors = '" + key.ToString() + "'";
DataRow[] authors = dt.Select(filterString);
if (authors.Length > 0)
{
author = ConvertDataRowToAuthor(authors[0]);
}
return author;
}
// Keeping all internal information about columns and rows encapsulated.
public AuthorModel ConvertDataRowToAuthor(DataRow AuthorInfo)
{
AuthorModel author = new AuthorModel(AuthorInfo[AuthorIDColumnIndex].ToString(), AuthorInfo[FirstNameColumnIndex].ToString(), AuthorInfo[LastNameColumnIndex].ToString(), AuthorInfo[MiddleNameColumnIndex].ToString(),
AuthorInfo[DobColumnIndex].ToString(), AuthorInfo[DodColumnIntex].ToString());
return author;
}
public List<string> AuthorNamesForSelector(DataRow[] AuthorDataRows)
{
List<string> authorNames = new List<string>();
foreach (DataRow author in AuthorDataRows)
{
string LastFirstMiddle = author[LastNameColumnIndex].ToString() + ", " + author[FirstNameColumnIndex].ToString() + " " + author[MiddleNameColumnIndex].ToString();
authorNames.Add(LastFirstMiddle);
}
return authorNames;
}
public string AuthorNamesCombinedString(DataRow author)
{
string LastFirstMiddle = author[LastNameColumnIndex].ToString() + ", " + author[FirstNameColumnIndex].ToString() + " " + author[MiddleNameColumnIndex].ToString();
return LastFirstMiddle;
}
protected override void InitializeSqlCommandParameters()
{
MySqlParameterCollection parameters = AddItemParameters;
_addSqlCommandParameter("Last Name", GetDBColumnData("LastName"), parameters["@authorLastName"]);
_addSqlCommandParameter("First Name", GetDBColumnData("FirstName"), parameters["@authorFirstName"]);
_addSqlCommandParameter("Middle Name", GetDBColumnData("MiddleName"), parameters["@authorMiddleName"]);
_addSqlCommandParameter("Year of Birth", GetDBColumnData("YearOfBirth"), parameters["@dob"]);
_addSqlCommandParameter("Year of Death", GetDBColumnData("YearOfDeath"), parameters["@dod"]);
_addSqlCommandParameter("ID", GetDBColumnData("idAuthors"), parameters["@primaryKey"]);
}
#endregion
}
}
AuthorModel.cs
This is the implementation of the DataTableItemBaseModel for the previous
table.
using System.Windows;
using ExperimentSimpleBkLibInvTool.ModelInMVC.ItemBaseModel;
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.Author
{
public class AuthorModel : DataTableItemBaseModel, IAuthorModel
{
private bool errorWasReported;
public string FirstName {
get { return GetParameterValue("First Name"); }
set { SetFirstName(value); }
}
public string MiddleName {
get { return GetParameterValue("Middle Name"); }
set { SetParameterValue("Middle Name", value); }
}
public string LastName {
get { return GetParameterValue("Last Name"); }
set { SetLastName(value); }
}
public string YearOfBirth {
get { return GetParameterValue("Year of Birth"); }
set { SetParameterValue("Year of Birth", value); }
}
public string YearOfDeath {
get { return GetParameterValue("Year of Death"); }
set { SetParameterValue("Year of Death", value); }
}
public uint AuthorId {
get { return GetParameterKValue("ID"); }
private set { SetParameterValue("ID", value); }
}
public AuthorModel()
: base(((App)Application.Current).Model.AuthorTable)
{
errorWasReported = false;
AuthorId = 0;
}
public AuthorModel(string firstName, string lastName, string middleName=null, string yearOfBirth=null, string yearOfDeath=null)
: base(((App)Application.Current).Model.AuthorTable)
{
errorWasReported = false;
AuthorId = 0;
FirstName = firstName;
LastName = lastName;
if (!string.IsNullOrEmpty(middleName))
{
MiddleName = middleName;
}
if (!string.IsNullOrEmpty(yearOfBirth))
{
YearOfBirth = yearOfBirth;
}
if (!string.IsNullOrEmpty(yearOfDeath))
{
YearOfDeath = yearOfDeath;
}
}
public AuthorModel(string idAuthor, string firstName, string lastName, string middleName = null, string yearOfBirth = null, string yearOfDeath = null)
: base(((App)Application.Current).Model.AuthorTable)
{
errorWasReported = false;
uint IdAuthor;
uint.TryParse(idAuthor, out IdAuthor);
AuthorId = IdAuthor;
FirstName = firstName;
LastName = lastName;
if (!string.IsNullOrEmpty(middleName))
{
MiddleName = middleName;
}
if (!string.IsNullOrEmpty(yearOfBirth))
{
YearOfBirth = yearOfBirth;
}
if (!string.IsNullOrEmpty(yearOfDeath))
{
YearOfDeath = yearOfDeath;
}
}
public override bool AddToDb()
{
return ((App)Application.Current).Model.AuthorTable.AddAuthor(this);
}
private void SetFirstName(string textBoxInput)
{
if (string.IsNullOrEmpty(textBoxInput))
{
string errorMsg = "The first name of the author is a required field!";
MessageBox.Show(errorMsg);
errorWasReported = true;
}
else
{
SetParameterValue("First Name", textBoxInput);
}
}
private void SetLastName(string textBoxInput)
{
if (string.IsNullOrEmpty(textBoxInput))
{
string errorMsg = "The last name of the author is a required field!";
MessageBox.Show(errorMsg);
errorWasReported = true;
}
else
{
SetParameterValue("Last Name", textBoxInput);
}
}
protected override bool _dataIsValid()
{
bool isValid = _defaultIsValid();
if (isValid)
{
return isValid;
}
isValid = GetParameterIsValid("First Name");
if (isValid)
{
isValid = GetParameterIsValid("Last Name");
}
if (!isValid && !errorWasReported)
{
string errorMsg = "Add Series error: The first and last names of the author are required fields";
MessageBox.Show(errorMsg);
}
return isValid;
}
}
}
SqlCmdParameter.cs
using System;
using System.Data;
using System.Windows;
using MySql.Data.MySqlClient;
/*
* This class is used to generate SQL command parameters to a call of a
* stored procedure.
*
* This class is a data value for a single column in a single row of data.
* Incoming data will generally be user input and there will be 2 forms of input, either
* a string from a text field or a boolean value from a checkbox.
*
* During the creation of the SQL command parameter the data will be returned as the proprer
* type for the stored procedure. The coversion from input string to the expected SQL type
* will occur during the input phase as an additional check on the validity of the input.
*/
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.DataTableModel
{
public class SqlCmdParameter
{
protected string _publicName; // The name the user knows this field by
protected string _dataBaseColumnName;
protected string _storedProcedureParameterName;
protected ParameterDirection _direction;
protected int _valueInt;
protected string _value; // used for input as the basis of the conversion, and storage for string parameters.
protected double _valueDouble;
protected uint _valueKey;
protected bool _isRequired; // Is this field required to have a value in the database? This is used in the validity check
protected MySqlDbType _type;
protected bool _isValueSet; // internal, used in the validity check
protected bool _skipInsertOfPrimaryKey;
public SqlCmdParameter(string PublicName, string DataBaseColumnName, string SBParamName, MySqlDbType Type, bool IsRequired = false, ParameterDirection Direction=ParameterDirection.Input, bool SkipInserOfPrimaryKey=false)
{
if (string.IsNullOrEmpty(PublicName))
{
ArgumentNullException ex = new ArgumentNullException("PublicName");
throw ex;
}
if (string.IsNullOrEmpty(SBParamName))
{
ArgumentNullException ex = new ArgumentNullException("SBParamName");
throw ex;
}
switch (Type)
{
case MySqlDbType.Int16:
case MySqlDbType.Int32:
case MySqlDbType.Double:
case MySqlDbType.String:
case MySqlDbType.UInt32:
case MySqlDbType.Byte: // TinyInt for boolean representation
break;
case MySqlDbType.VarChar:
case MySqlDbType.Date:
// In the user interface handle VarChar as a string.
Type = MySqlDbType.String;
break;
default:
ArgumentOutOfRangeException ex = new ArgumentOutOfRangeException("Type");
throw ex;
}
_publicName = PublicName;
_storedProcedureParameterName = SBParamName;
_direction = Direction;
_isRequired = IsRequired;
_type = Type;
_isValueSet = false;
_value = null;
_valueKey = 0;
_valueInt = 0;
_valueDouble = 0.0;
_skipInsertOfPrimaryKey = SkipInserOfPrimaryKey;
}
public SqlCmdParameter(SqlCmdParameter original)
{
_publicName = original._publicName;
_storedProcedureParameterName = original._storedProcedureParameterName;
_direction = original._direction;
_isRequired = original._isRequired;
_type = original._type;
_isValueSet = original._isValueSet;
_value = original._value;
_valueKey = original._valueKey;
_valueInt = original._valueInt;
_valueDouble = original._valueDouble;
_skipInsertOfPrimaryKey = original._skipInsertOfPrimaryKey;
}
public string PublicName
{
get { return _publicName; }
}
public ParameterDirection Direction
{
get { return _direction; }
set { _direction = value; }
}
public bool IsValid { get { return _dataIsValid(); } }
public bool IsRequired
{
get { return _isRequired; }
set { _isRequired = value; }
}
public string Value
{
get { return _value; }
set { SetValue(value); }
}
public bool BValue
{
get { return (_valueInt > 0); }
set { SetValue(value); }
}
public uint KeyValue
{
get { return _valueKey; }
set { _valueKey = value; }
}
public MySqlDbType Type
{
get { return _type; }
}
public bool AddParameterToCommand(MySqlCommand cmd)
{
if (_skipInsertOfPrimaryKey)
{
return true;
}
// If it is an output variable validity doesn't matter.
if (_direction != ParameterDirection.Input)
{
string IndexByNameValue = _storedProcedureParameterName;
cmd.Parameters.Add(new MySqlParameter(IndexByNameValue, _type));
cmd.Parameters[IndexByNameValue].Direction = _direction;
return true;
}
if (!IsValid)
{
return IsValid;
}
switch (_type)
{
case MySqlDbType.Byte:
case MySqlDbType.Int16:
case MySqlDbType.Int32:
cmd.Parameters.AddWithValue(_storedProcedureParameterName, _valueInt);
break;
case MySqlDbType.Double:
cmd.Parameters.AddWithValue(_storedProcedureParameterName, _valueDouble);
break;
case MySqlDbType.UInt32:
cmd.Parameters.AddWithValue(_storedProcedureParameterName, _valueKey);
break;
case MySqlDbType.String:
cmd.Parameters.AddWithValue(_storedProcedureParameterName, _value);
break;
}
return true;
}
protected void SetValue(string value)
{
if (string.IsNullOrEmpty(value))
{
return;
}
_value = value;
string eMsg = null;
switch (_type)
{
case MySqlDbType.Int16:
case MySqlDbType.Byte:
bool tmp = false;
if (!bool.TryParse(_value, out tmp))
{
eMsg = _publicName + ": Value is not True or False";
}
_valueInt = (tmp) ? 1 : 0;
break;
case MySqlDbType.Int32:
if (!int.TryParse(_value, out _valueInt))
{
eMsg = _publicName + ": Value is not in the proper format of an integer";
}
break;
case MySqlDbType.Double:
if (!double.TryParse(_value, out _valueDouble))
{
eMsg = _publicName + ": Value is not in the proper format of an floating point number";
}
break;
case MySqlDbType.UInt32:
_valueKey = Convert.ToUInt32(value);
if (!uint.TryParse(_value, out _valueKey))
{
eMsg = _publicName + ": Value is not in the proper format of an unsigned integer";
}
break;
case MySqlDbType.String:
default:
break;
}
if (eMsg != null)
{
MessageBox.Show(eMsg);
_isValueSet = false;
}
else
{
_isValueSet = true;
}
}
protected void SetValue(bool InVal)
{
_value = (InVal) ? "true" : "false";
if (_type == MySqlDbType.Int16 || _type == MySqlDbType.Byte)
{
_valueInt = (InVal) ? 1 : 0;
}
_isValueSet = true;
}
protected bool _dataIsValid()
{
bool dataIsValid = true;
if (_direction == ParameterDirection.Input && _isRequired && !_isValueSet)
{
dataIsValid = false;
}
return dataIsValid;
}
}
}
DBColParameterData.cs
using System.Data;
namespace ExperimentSimpleBkLibInvTool.ModelInMVC
{
public class DbColumnParameterData
{
public DbColumnParameterData(DataRow ColumnData)
{
bool parseWorked = true;
ColumnName = ColumnData[0].ToString();
parseWorked = int.TryParse(ColumnData[1].ToString(), out int ordinalPosition);
Ordinal_Posistion = ordinalPosition;
IsNullable = true;
}
public DbColumnParameterData(string columnName, int ordinal_Posistion, bool isNullable)
{
ColumnName = columnName;
Ordinal_Posistion = ordinal_Posistion;
IsNullable = isNullable;
}
public string ColumnName { get; private set; }
public int Ordinal_Posistion { get; private set; }
public bool IsNullable { get; private set; }
public int IndexBasedOnOrdinal { get { return Ordinal_Posistion - 1; } }
}
}
DataTableModel.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Windows;
using MySql.Data.MySqlClient;
using ExperimentSimpleBkLibInvTool.ModelInMVC.ItemBaseModel;
/*
*
* This file provides the database interface layer. All data retrieval and inserts
* are performed in this file. Information about each table is stored in the
* super classes that inherit from this class, but the data structures are located
* in this base class.
*
*/
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.DataTableModel
{
public abstract class CDataTableModel : ObservableModelObject
{
protected string _dbConnectionString;
protected string _getTableStoredProcedureName;
protected string _addItemStoredProcedureName;
protected string _tableName;
protected uint _newKeyValue;
protected MySqlParameterCollection _addItemStoredProcedureParameters;
protected List<DbColumnParameterData> _parameterProperties;
protected Dictionary<string, int> ParametersIndexedByPublicName;
protected Dictionary<string, int> ParametersIndexedByDatabaseTableName;
protected Dictionary<string, int> ParametersIndexedByParameterName;
private List<SqlCmdParameter> _sqlCmdParameters;
public uint NewKeyValue { get { return _newKeyValue; } }
public MySqlParameterCollection AddItemParameters { get { return _addItemStoredProcedureParameters; } }
public List<DbColumnParameterData> ColumnParameterData { get; private set; }
protected DataTable DataTable { get { return getDataTable(); } }
// The impementation of this function requires detailed knowlege of the columns in the table
// and the parameters of the stored procedure.
protected abstract void InitializeSqlCommandParameters();
public DbColumnParameterData GetDBColumnData(string columnName)
{
return ColumnParameterData.Find(x => x.ColumnName == columnName);
}
public List<SqlCmdParameter> SQLCommandParameters { get { return _sqlCmdParameters; } }
public Dictionary<string, int> PublicNameParameterIndex { get { return ParametersIndexedByPublicName; } }
public Dictionary<string, int> ParametersIndexByDbColumnName { get { return ParametersIndexedByDatabaseTableName; } }
public Dictionary<string, int> ParametersIndexByStoredProcedureName { get { return ParametersIndexedByParameterName; } }
protected CDataTableModel(string TableName, string GetTableStoredProcedureName, string AddItemToTableStoredProcedureName=null)
{
_newKeyValue = 0;
_tableName = TableName;
_getTableStoredProcedureName = GetTableStoredProcedureName;
_addItemStoredProcedureName = AddItemToTableStoredProcedureName;
_dbConnectionString = ConfigurationManager.ConnectionStrings["LibInvToolDBConnStr"].ConnectionString;
_sqlCmdParameters = new List<SqlCmdParameter>();
ParametersIndexedByPublicName = new Dictionary<string, int>();
ParametersIndexedByDatabaseTableName = new Dictionary<string, int>();
ParametersIndexedByParameterName = new Dictionary<string, int>();
// Not all datatable classes can add items, 2 examples are the status table and the condition table.
if (!string.IsNullOrEmpty(AddItemToTableStoredProcedureName))
{
GetParametersNamesFromAddCommand();
ColumnParameterData = GetColumnParameterProperties();
InitializeSqlCommandParameters();
ValidateParameterCount();
}
}
protected bool addItem(DataTableItemBaseModel NewDataItem)
{
bool canAddItemToTable = true;
canAddItemToTable = NewDataItem.IsValid;
if (canAddItemToTable)
{
canAddItemToTable = dbAddItem(NewDataItem);
}
return canAddItemToTable;
}
protected bool _addParametersInOrder(MySqlCommand cmd, DataTableItemBaseModel NewDataItem)
{
foreach (MySqlParameter parameter in _addItemStoredProcedureParameters)
{
if (!NewDataItem.AddParameterToCommand(cmd, parameter.ParameterName))
{
return false;
}
}
return true;
}
protected void _addSqlCommandParameter(string PublicName, DbColumnParameterData ColumnData, MySqlParameter parameter)
{
bool isRequired = false;
string DBColumnName = (ColumnData != null) ? ColumnData.ColumnName : "primaryKey";
if (!ParameterIsValid(PublicName, DBColumnName, parameter.ParameterName))
{
return;
}
if (ColumnData == null || ColumnData.IsNullable)
{
isRequired = false;
}
else
{
isRequired = true;
}
SqlCmdParameter NewParameter = new SqlCmdParameter(PublicName, DBColumnName, parameter.ParameterName, parameter.MySqlDbType, isRequired, parameter.Direction);
ParametersIndexedByPublicName.Add(PublicName, _sqlCmdParameters.Count);
ParametersIndexedByDatabaseTableName.Add(DBColumnName, _sqlCmdParameters.Count);
ParametersIndexedByParameterName.Add(parameter.ParameterName, _sqlCmdParameters.Count);
_sqlCmdParameters.Add(NewParameter);
}
private bool dbAddItem(DataTableItemBaseModel NewDataItem)
{
bool AddItemSuccess = true;
if (ReportProgrammerError(_addItemStoredProcedureName, "_addItemStoredProcedureName is not set!"))
{
return false;
}
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _addItemStoredProcedureName;
if (_addParametersInOrder(cmd, NewDataItem))
{
cmd.ExecuteNonQuery();
// Some of the stored procedures return the new key in the last parameter
// in those cases get the returned key so that the new row can be accessed.
int paramtercount = cmd.Parameters.Count - 1; // indexing starts at 0 ends at count - 1
if (cmd.Parameters[paramtercount].Direction != ParameterDirection.Input)
{
uint.TryParse(cmd.Parameters[paramtercount].Value.ToString(), out _newKeyValue);
}
OnPropertyChanged();
}
else
{
AddItemSuccess = false;
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
AddItemSuccess = false;
}
}
return AddItemSuccess;
}
private DataTable getDataTable()
{
int ResultCount = 0;
DataTable Dt = new DataTable();
if (!ReportProgrammerError(_getTableStoredProcedureName, "_getTableStoredProcedureName is not set!"))
{
try
{
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _getTableStoredProcedureName;
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
OnPropertyChanged();
}
}
}
catch (Exception ex)
{
string errorMsg = "Database Error: " + ex.Message;
MessageBox.Show(errorMsg);
}
}
return Dt;
}
private void GetParametersNamesFromAddCommand()
{
if (!string.IsNullOrEmpty(_addItemStoredProcedureName))
{
// Neither the status table or the condition table have stored procedures to
// add data to the tables, these tables are included in add book.
try
{
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _addItemStoredProcedureName;
cmd.Connection = conn;
MySqlCommandBuilder.DeriveParameters(cmd);
_addItemStoredProcedureParameters = cmd.Parameters;
}
}
}
catch (Exception ex)
{
string errorMsg = "Table: " + _tableName + " Stored Procedure: " + _addItemStoredProcedureName + "\nDatabase Error Initializing Command Parameter Properties: ";
errorMsg += ex.Message;
MessageBox.Show(errorMsg, "Database Error:", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
// Due to bugs/unimplemented features in MySQL MySqlCommandBuilder.DeriveParameters(Command)
// such as IsNullable will always be false this provides a workaround for getting additional
// information about each parameter
private List<DbColumnParameterData> GetColumnParameterProperties()
{
List<DbColumnParameterData> columnSchemaDetails = new List<DbColumnParameterData>();
DataTable Dt = new DataTable();
int ResultCount = 0;
if (!ReportProgrammerError(_tableName, "_tableName is not set!"))
{
try
{
using (MySqlConnection conn = new MySqlConnection(_dbConnectionString))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getTableColumnData";
cmd.Parameters.AddWithValue("tableName", _tableName);
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
ResultCount = sda.Fill(Dt);
}
}
foreach (DataRow dataRow in Dt.Rows)
{
columnSchemaDetails.Add(new DbColumnParameterData(dataRow));
}
}
catch (Exception ex)
{
string errorMsg = "Database Error Initializing Parameter Properties: " + ex.Message;
MessageBox.Show(errorMsg, "Database Error:", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
return columnSchemaDetails;
}
private bool ReportProgrammerError(string nameToCheck, string errorMessage)
{
if (string.IsNullOrEmpty(nameToCheck))
{
#if DEBUG
string errorMsg = "Programmer ERROR : " + errorMessage;
MessageBox.Show(errorMsg, "Programmer ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
#endif
return true;
}
return false;
}
private bool ParameterIsValid(string PublicName, string DataBaseColumnName, string StoredProcedureParamName)
{
bool isValid = true;
if (ReportProgrammerError(PublicName, "PublicName is null or empty in _addSqlCommandParameter"))
{
isValid = false;
}
if (ReportProgrammerError(DataBaseColumnName, "DataBaseColumnName is null or empty in _addSqlCommandParameter"))
{
isValid = false;
}
if (ReportProgrammerError(StoredProcedureParamName, "SBParamName is null or empty in _addSqlCommandParameter"))
{
isValid = false;
}
return isValid;
}
private bool ValidateParameterCount()
{
bool validCount = _sqlCmdParameters.Count == _addItemStoredProcedureParameters.Count;
#if DEBUG
if (!validCount)
{
string eMsg = "Stored Procedure: " + _addItemStoredProcedureName + " Expected parameter count is " + _addItemStoredProcedureParameters.Count.ToString() +
" Actual parameter count is " + _sqlCmdParameters.Count.ToString();
MessageBox.Show(eMsg, "Invalid Parameter Count", MessageBoxButton.OK, MessageBoxImage.Error);
}
#endif
return (validCount);
}
}
}
DataTableItemBaseModel.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows;
using MySql.Data.MySqlClient;
using ExperimentSimpleBkLibInvTool.ModelInMVC.DataTableModel;
/*
* There is a tight coupling between each model and the table it belongs to. This
* is due to the models ability to add parameters to the tables call to the stored
* procedure. This is only true when a model can be added to a table.
*
* This class represents a row of data in a data table. Generally it will be used
* to add a row to a database table.
*/
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.ItemBaseModel
{
public abstract class DataTableItemBaseModel
{
/*
* To save memory and correctly change the proper command parameter, only the
* _sqlCmdParameters list contains SqlCmdParameters and the dictionaries provide
* indexes into the command parameters list. To maintain good performance the
* dictionaries are used rather than using a find in the list.
*/
private List<SqlCmdParameter> _sqlCmdParameters;
private Dictionary<string, int> _parameterIndexByPublicName;
private Dictionary<string, int> _parameterIndexByDatabaseTableName;
private Dictionary<string, int> _parameterIndexByParameterName;
public bool IsValid { get { return _dataIsValid(); } }
public uint BookId
{
get { return GetParameterKValue("ID"); }
set { SetParameterValue("ID", value); }
}
public void setBookId(uint BookId)
{
SetParameterValue("ID", BookId);
}
public abstract bool AddToDb();
protected abstract bool _dataIsValid();
protected DataTableItemBaseModel(CDataTableModel DBInterfaceModel)
{
_sqlCmdParameters = new List<SqlCmdParameter>();
List<SqlCmdParameter> sqlCmdParameters = DBInterfaceModel.SQLCommandParameters;
foreach (SqlCmdParameter parameter in sqlCmdParameters)
{
SqlCmdParameter p = new SqlCmdParameter(parameter);
_sqlCmdParameters.Add(p);
}
_parameterIndexByPublicName = new Dictionary<string, int>(DBInterfaceModel.PublicNameParameterIndex);
_parameterIndexByParameterName = new Dictionary<string, int>(DBInterfaceModel.ParametersIndexByStoredProcedureName);
_parameterIndexByDatabaseTableName = new Dictionary<string, int>();
_parameterIndexByDatabaseTableName = new Dictionary<string, int>(DBInterfaceModel.ParametersIndexByDbColumnName);
}
/*
* Sometimes the number of parameters in the stored procedure count doesn't
* match the nummber of columns in the table. This function can be overriden
* in those cases. Two examples of this are the Series and Books.
*/
public bool AddParameterToCommand(MySqlCommand cmd, string ParameterName)
{
bool success = true;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
MySqlParameterCollection parameters = cmd.Parameters;
success = _sqlCmdParameters[tableIndex].AddParameterToCommand(cmd);
}
else
{
success = false;
}
return success;
}
public string GetParameterValue(string ParameterName)
{
string ParameterValue = "Failure";
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterValue = _sqlCmdParameters[tableIndex].Value;
}
return ParameterValue;
}
public uint GetParameterKValue(string ParameterName)
{
uint ParameterValue = 0;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterValue = _sqlCmdParameters[tableIndex].KeyValue;
}
return ParameterValue;
}
public int GetParameterIValue(string ParameterName)
{
int ParameterValue = -1;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterValue = Convert.ToInt32(_sqlCmdParameters[tableIndex].Value);
}
return ParameterValue;
}
protected ParameterDirection GetParameterDirection(string ParameterName)
{
ParameterDirection Direction = ParameterDirection.Input;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
Direction = _sqlCmdParameters[tableIndex].Direction;
}
return Direction;
}
protected MySqlDbType GetParameterType(string ParameterName)
{
MySqlDbType Type = MySqlDbType.String;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
Type = _sqlCmdParameters[tableIndex].Type;
}
return Type;
}
protected void SetParameterValue(string ParameterName, string value)
{
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
_sqlCmdParameters[tableIndex].Value = value;
}
}
protected void SetParameterValue(string ParameterName, uint value)
{
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
_sqlCmdParameters[tableIndex].Value = value.ToString();
_sqlCmdParameters[tableIndex].KeyValue = value;
}
}
protected void SetParameterValue(string ParameterName, int value)
{
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
_sqlCmdParameters[tableIndex].Value = value.ToString();
}
}
protected void SetParameterValue(string ParameterName, bool value)
{
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
_sqlCmdParameters[tableIndex].BValue = value;
}
}
protected bool GetParameterBValue(string ParameterName)
{
bool ParameterValue = false;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterValue = _sqlCmdParameters[tableIndex].BValue;
}
return ParameterValue;
}
protected uint GetKeyValue()
{
uint KeyValue = 0;
int tableIndex = getParameterIndex("ID");
if (tableIndex >= 0)
{
KeyValue = _sqlCmdParameters[tableIndex].KeyValue;
}
return KeyValue;
}
protected void SetKeyValue(uint KeyValue)
{
int tableIndex = getParameterIndex("ID");
if (tableIndex >= 0)
{
_sqlCmdParameters[tableIndex].KeyValue = KeyValue;
}
}
public bool GetParameterIsValid(string ParameterName)
{
bool ParameterIsValid = false;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterIsValid = _sqlCmdParameters[tableIndex].IsValid;
}
return ParameterIsValid;
}
protected bool GetParameterIsRequired(string ParameterName)
{
bool ParameterIsRequired = false;
int tableIndex = getParameterIndex(ParameterName);
if (tableIndex >= 0)
{
ParameterIsRequired = _sqlCmdParameters[tableIndex].IsRequired;
}
return ParameterIsRequired;
}
private int getParameterIndex(string parameterName)
{
int parameterIndex = -1;
int tableIndex;
if (_parameterIndexByParameterName.TryGetValue(parameterName, out tableIndex))
{
parameterIndex = tableIndex;
}
else if (_parameterIndexByPublicName.TryGetValue(parameterName, out tableIndex))
{
parameterIndex = tableIndex;
}
else if (_parameterIndexByDatabaseTableName.TryGetValue(parameterName, out tableIndex))
{
parameterIndex = tableIndex;
}
#if DEBUG
// ASSERT
else
{
string eMsg = "Programmer error in getParameterIndex(): Parameter not found: " + parameterName;
MessageBox.Show(eMsg, "Programmer Error:", MessageBoxButton.OK, MessageBoxImage.Error);
}
#endif
return parameterIndex;
}
protected bool _defaultIsValid()
{
bool isValid = true;
foreach (SqlCmdParameter parameter in _sqlCmdParameters)
{
isValid = parameter.IsValid;
if (parameter.Direction == ParameterDirection.Input && !isValid)
{
return isValid;
}
}
return isValid;
}
}
}
DictionaryTableModel.cs
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows;
using ExperimentSimpleBkLibInvTool.ModelInMVC.DataTableModel;
using ExperimentSimpleBkLibInvTool.ModelInMVC.ItemBaseModel;
namespace ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel
{
public abstract class DictionaryTableModel : CDataTableModel
{
private Dictionary<uint, string> _keyToTitle;
private Dictionary<string, uint> _titleToKey;
public DictionaryTableModel(string TableName, string GetTableStoredProcedureName, string AddItemToTableStoredProcedureName = null) :
base(TableName, GetTableStoredProcedureName, AddItemToTableStoredProcedureName)
{
_titleToKey = new Dictionary<string, uint>();
_keyToTitle = new Dictionary<uint, string>();
_titleToKey = DataTable.AsEnumerable().ToDictionary(row => row.Field<string>(0), row => row.Field<uint>(1));
_keyToTitle = DataTable.AsEnumerable().ToDictionary(row => row.Field<uint>(1), row => row.Field<string>(0));
}
public List<string> ListBoxSelectionList()
{
List<string> listBoxSelectionValues = _keyToTitle.Values.ToList<string>();
return listBoxSelectionValues;
}
protected string KeyToName(uint Key)
{
return _keyToTitle[Key];
}
protected uint NameToKey(string CategoryTitle)
{
return _titleToKey[CategoryTitle];
}
protected void AddItemToDictionary(DataTableItemBaseModel NewItem)
{
bool AddedSuccessfully = addItem(NewItem);
if (AddedSuccessfully && _newKeyValue > 0)
{
_keyToTitle.Add(_newKeyValue, NewItem.GetParameterValue("Name"));
_titleToKey.Add(NewItem.GetParameterValue("Name"), _newKeyValue);
}
else
{
string errorMsg = "Database Error: Failed to add item";
MessageBox.Show(errorMsg);
}
}
}
}
