Non-Entity framework database interaction modelScience Fiction Wall of Fame (Shame?)Closures in C#, necessary...
Violin - Can double stops be played when the strings are not next to each other?
How can I wire 7 outdoor posts correctly?
In what order does sftp fetch files when using "get -r folder"?
Why does the degree of dissociation change when we dilute a weak acid even though the equilibrium constant K is constant?
What is the relationship between relativity and the Doppler effect?
How to get the n-th line after a grepped one?
Constant Current LED Circuit
Why is a polar cone a closed set?
Am I eligible for the Eurail Youth pass? I am 27.5 years old
What is the plural TO OF sth
Using Leaflet inside Bootstrap container?
Why is the President allowed to veto a cancellation of emergency powers?
How are passwords stolen from companies if they only store hashes?
What is the significance behind "40 days" that often appears in the Bible?
What exactly is this small puffer fish doing and how did it manage to accomplish such a feat?
A Ri-diddley-iley Riddle
Four married couples attend a party. Each person shakes hands with every other person, except their own spouse, exactly once. How many handshakes?
Does .bashrc contain syntax errors?
I am trying to parse json data using jq
This word with a lot of past tenses
HP P840 HDD RAID 5 many strange drive failures
Why is there so much iron?
Matrix using tikz package
Could this Scherzo by Beethoven be considered to be a fugue?
Non-Entity framework database interaction model
Science Fiction Wall of Fame (Shame?)Closures in C#, necessary or not?Framework for comparing database objectsCRUD commands to SQL databaseEntity Framework Code First Softball Stats Database SchemaSales order domain model with Entity FrameworkEntity Framework configurationMVVM - ObservableCollection & Entity Framework DbContextStoring Enum values as Strings in DBUpdate SQL database, similar-but-different queries for each monthScience Fiction Wall of Fame (Shame?)
4
$begingroup$
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 exaample 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);
}
}
}
}
c# .net mysql database wpf
edited 2 days ago
t3chb0t
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
$endgroup$
add a comment |
4
$begingroup$
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 exaample 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);
}
}
}
}
c# .net mysql database wpf
edited 2 days ago
t3chb0t
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
$endgroup$
add a comment |
4
4
4
$begingroup$
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 exaample 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);
}
}
}
}
c# .net mysql database wpf
edited 2 days ago
t3chb0t
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
$endgroup$
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 exaample 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);
}
}
}
}
c# .net mysql database wpf
c# .net mysql database wpf
edited 2 days ago
t3chb0t
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
edited 2 days ago
t3chb0t
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
edited 2 days ago
t3chb0t
35k752124
edited 2 days ago
t3chb0t
35k752124
edited 2 days ago
t3chb0t
35k752124
35k752124
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
asked Mar 14 at 16:35
pacmaninbwpacmaninbw
5,25321537
5,25321537
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
$begingroup$
I won't read this code beginning to end. Some basic advice:
Namespaces
ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel doesn't follow naming conventions.
Naming convention is Company.App.Module.
It should be something like: Pacmaninbw.PersonalLibrary.Model.
Naming
Don't tack on crap affixes to the names.
ExperimentSimple...
It's ok to write experimentally low quality code.
But stating the experimentality of the code belongs to README.md.
Putting it in the name makes it even lower quality.
If you are worried people will use your code to operate Nuclear Reactors and Airliners put legal discalimers in the LICENCE.txt....Tool
It's either a "library" or a "book inventory", but never a "book library inventory tool", much less "bk lib inv tool".
You can say "screwdriver" or "hammer", but never "nail driver hammer tool".
Don't repeat pattern artifacts in names (
ModelInMVC)
If you are following a well-known pattern no need to mangle names to advertise it.
When you are following patterns follow the naming conventions in the examples.
If you, for some reason, are writing a singleton, add an Instance property.
Don't name the property TheSingletonInstance.
If you put Models, Views, Controllers folders people will understand you are following a pattern, especially if it is the IDE default.
If you are following an obscure pattern, document it with the sources where others can learn about it and the reasons you chose it in the README.
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
$endgroup$
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f215431%2fnon-entity-framework-database-interaction-model%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
$begingroup$
I won't read this code beginning to end. Some basic advice:
Namespaces
ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel doesn't follow naming conventions.
Naming convention is Company.App.Module.
It should be something like: Pacmaninbw.PersonalLibrary.Model.
Naming
Don't tack on crap affixes to the names.
ExperimentSimple...
It's ok to write experimentally low quality code.
But stating the experimentality of the code belongs to README.md.
Putting it in the name makes it even lower quality.
If you are worried people will use your code to operate Nuclear Reactors and Airliners put legal discalimers in the LICENCE.txt....Tool
It's either a "library" or a "book inventory", but never a "book library inventory tool", much less "bk lib inv tool".
You can say "screwdriver" or "hammer", but never "nail driver hammer tool".
Don't repeat pattern artifacts in names (
ModelInMVC)
If you are following a well-known pattern no need to mangle names to advertise it.
When you are following patterns follow the naming conventions in the examples.
If you, for some reason, are writing a singleton, add an Instance property.
Don't name the property TheSingletonInstance.
If you put Models, Views, Controllers folders people will understand you are following a pattern, especially if it is the IDE default.
If you are following an obscure pattern, document it with the sources where others can learn about it and the reasons you chose it in the README.
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
$endgroup$
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
add a comment |
1
$begingroup$
I won't read this code beginning to end. Some basic advice:
Namespaces
ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel doesn't follow naming conventions.
Naming convention is Company.App.Module.
It should be something like: Pacmaninbw.PersonalLibrary.Model.
Naming
Don't tack on crap affixes to the names.
ExperimentSimple...
It's ok to write experimentally low quality code.
But stating the experimentality of the code belongs to README.md.
Putting it in the name makes it even lower quality.
If you are worried people will use your code to operate Nuclear Reactors and Airliners put legal discalimers in the LICENCE.txt....Tool
It's either a "library" or a "book inventory", but never a "book library inventory tool", much less "bk lib inv tool".
You can say "screwdriver" or "hammer", but never "nail driver hammer tool".
Don't repeat pattern artifacts in names (
ModelInMVC)
If you are following a well-known pattern no need to mangle names to advertise it.
When you are following patterns follow the naming conventions in the examples.
If you, for some reason, are writing a singleton, add an Instance property.
Don't name the property TheSingletonInstance.
If you put Models, Views, Controllers folders people will understand you are following a pattern, especially if it is the IDE default.
If you are following an obscure pattern, document it with the sources where others can learn about it and the reasons you chose it in the README.
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
$endgroup$
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
add a comment |
1
1
1
$begingroup$
I won't read this code beginning to end. Some basic advice:
Namespaces
ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel doesn't follow naming conventions.
Naming convention is Company.App.Module.
It should be something like: Pacmaninbw.PersonalLibrary.Model.
Naming
Don't tack on crap affixes to the names.
ExperimentSimple...
It's ok to write experimentally low quality code.
But stating the experimentality of the code belongs to README.md.
Putting it in the name makes it even lower quality.
If you are worried people will use your code to operate Nuclear Reactors and Airliners put legal discalimers in the LICENCE.txt....Tool
It's either a "library" or a "book inventory", but never a "book library inventory tool", much less "bk lib inv tool".
You can say "screwdriver" or "hammer", but never "nail driver hammer tool".
Don't repeat pattern artifacts in names (
ModelInMVC)
If you are following a well-known pattern no need to mangle names to advertise it.
When you are following patterns follow the naming conventions in the examples.
If you, for some reason, are writing a singleton, add an Instance property.
Don't name the property TheSingletonInstance.
If you put Models, Views, Controllers folders people will understand you are following a pattern, especially if it is the IDE default.
If you are following an obscure pattern, document it with the sources where others can learn about it and the reasons you chose it in the README.
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
$endgroup$
I won't read this code beginning to end. Some basic advice:
Namespaces
ExperimentSimpleBkLibInvTool.ModelInMVC.DictionaryTabelBaseModel doesn't follow naming conventions.
Naming convention is Company.App.Module.
It should be something like: Pacmaninbw.PersonalLibrary.Model.
Naming
Don't tack on crap affixes to the names.
ExperimentSimple...
It's ok to write experimentally low quality code.
But stating the experimentality of the code belongs to README.md.
Putting it in the name makes it even lower quality.
If you are worried people will use your code to operate Nuclear Reactors and Airliners put legal discalimers in the LICENCE.txt....Tool
It's either a "library" or a "book inventory", but never a "book library inventory tool", much less "bk lib inv tool".
You can say "screwdriver" or "hammer", but never "nail driver hammer tool".
Don't repeat pattern artifacts in names (
ModelInMVC)
If you are following a well-known pattern no need to mangle names to advertise it.
When you are following patterns follow the naming conventions in the examples.
If you, for some reason, are writing a singleton, add an Instance property.
Don't name the property TheSingletonInstance.
If you put Models, Views, Controllers folders people will understand you are following a pattern, especially if it is the IDE default.
If you are following an obscure pattern, document it with the sources where others can learn about it and the reasons you chose it in the README.
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
answered 2 days ago
abuzittin gillifircaabuzittin gillifirca
5,982924
5,982924
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
add a comment |
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
$begingroup$
I really want a nail driver hammer tool now. +1 Good notes on naming.
$endgroup$
– Shelby115
2 days ago
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f215431%2fnon-entity-framework-database-interaction-model%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
