/// <summary>
/// Creates the table and columns.
/// </summary>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
/// <exception cref="IndexOutOfRangeException"></exception>
private static void CreateTableAndColumns()
{
if (SQLControl.DataBase == null)
{
throw new NullReferenceException();
}
string sqlString =
$"Use {SQLControl.DataBase}; " +
"Create Table Inventory " +
"(id NVARCHAR(16) Not null, " +
"idValue INT Not null Identity(1,1), " +
"name NVARCHAR(40) Not null , " +
"amount INT Not null , " +
"type NVARCHAR(40) Not null, " +
"dangerCategory int null, " +
"flashPoint float null, " +
"minTemp float null," +
"boilingPoint float null, " +
"volatile bit null," +
"information NVARCHAR(2048) null, " +
"Primary Key(id, idValue) );";
try
{
SQLControl.RunCommand(sqlString);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creates the database
/// </summary>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
/// <exception cref="IndexOutOfRangeException"></exception>
private static void CreateDatabase()
{
if (SQLControl.DataBase == null)
{
throw new NullReferenceException();
}
try
{
SQLControl.RunCommand($"Use Master; CREATE DATABASE {SQLControl.DataBase}");
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Uses the insert ware stored procedure to insert a new ware, into the database, with the ability to set any column.
/// However, <paramref name="id"/>, <paramref name="amount"/>, <paramref name="name"/> and <paramref name="type"/> is needed.
/// </summary>
/// <param name="id">The ID of the ware.</param>
/// <param name="name">The name of the ware.</param>
/// <param name="amount">The amount of the ware.</param>
/// <param name="type">The type of the ware.</param>
/// <param name="information">Information about the ware.</param>
/// <param name="dangerCategory">The danger category of the ware.</param>
/// <param name="flashPoint">The flash point of the ware.</param>
/// <param name="minTemp">The minimum temperature of the ware.</param>
/// <param name="boilingPoint">The boiling point of the ware.</param>
/// <param name="volatile">Whether the ware is volatile or not.</param>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static void InsertWareSP(string id, string name, int amount, string type, string information, string dangerCategory, string flashPoint, string minTemp, string boilingPoint, string @volatile)
{
string sqlString =
$"EXEC InsertWareFull @ID = {id}, @Name = {name}, @Amount = {amount}, @Type = {type}, " +
$"@Information = {information ?? "null"}, @DangerCategory = {dangerCategory ?? "null"}, @FlashPoint = {flashPoint ?? "null"}, @MinTemp = {minTemp ?? "null"}, @BoilingPoint = {boilingPoint ?? "null"}, @Volatile = {@volatile ?? "null"}";
try
{
SQLControl.RunCommand(sqlString);
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not insert: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
throw e;
}
}
/// <summary>
/// Uses the delete ware stored procedure to delete the ware with the <paramref name="id"/>.
/// </summary>
/// <param name="id">The ID of the ware to delete.</param>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static void RunDeleteWareSP(string id)
{
if (id == null)
{
throw new NullReferenceException();
}
string sqslString =
$"EXEC DeleteWare @WareToDelete = {id};";
try
{
SQLControl.RunCommand(sqslString);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Not Working as MSSQL thinks @Column is the column name. Uses the update stored procedure to update the ware <paramref name="id"/>'s <paramref name="column"/> with the new value of <paramref name="newValue"/>.
/// </summary>
/// <param name="id">Ware ID to update</param>
/// <param name="column">The Column to update</param>
/// <param name="newValue">The new value</param>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static void UpdateWareSP(string id, string column, string newValue)
{
if (id == null || column == null || newValue == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC UpdateWare @WareID = {id}, @Column = {column}, @NewValue = {newValue}";
try {
SQLControl.RunCommand(sqlString);
}
catch (Exception e)
{
StorageSystemCore.Reporter.Report(e);
Console.WriteLine($"Could not update: {e.Message}");
StorageSystemCore.Support.WaitOnKeyInput();
}
}
/// <summary>
/// Uses the insert ware stored procedure to insert a new ware, into the database, with the basic column requirements filled out.
/// </summary>
/// <param name="id">The ID of the ware.</param>
/// <param name="name">The name of the ware.</param>
/// <param name="amount">The amount of the ware.</param>
/// <param name="type">The type of the ware.</param>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static void InsertWareSP(string id, string name, int amount, string type)
{
if (id == null || name == null || type == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC InsertWareBasic @ID = {id}, @Name = {name}, @Amount = {amount}, @Type = {type}";
try {
SQLControl.RunCommand(sqlString);
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not insert: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
throw e;
}
}
/// <summary>
/// Removes <paramref name="amount"/> from the total amount of the ware with the ID <paramref name="id"/>.
/// </summary>
/// <param name="id">The ID of the ware.</param>
/// <param name="amount">The amount to remove</param>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static void RemoveFromWareAmountSP(string id, int amount)
{
if (id == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC RemoveFromWareAmount @WareID = {id}, @Value = {amount}";
try
{
SQLControl.RunCommand(sqlString);
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not add to ware: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
throw e;
}
}
{ //https://www.w3schools.com/sql/sql_stored_procedures.asp
/// <summary>
/// Creates all stored procedures in the database that the program will need.
/// </summary>
/// <exception cref="NullReferenceException"></exception>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="System.Data.SqlClient.SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
/// <exception cref="TargetException"></exception>
/// <exception cref="ArgumentException"></exception>
/// <exception cref="TargetInvocationException"></exception>
/// <exception cref="TargetParameterCountException"></exception>
/// <exception cref="MethodAccessException"></exception>
/// <exception cref="NotSupportedException"></exception>
public static void CreateAllStoredProcedures()
{
StorageSystemCore.Reporter.Log("Starting creation of stored procedures");
MethodInfo[] methods = Type.GetType(typeof(StoredProcedures).ToString()).GetMethods(BindingFlags.NonPublic | BindingFlags.Static); //the binding flags ensures only static non-public methods are found.
foreach (MethodInfo method in methods)
{
try
{
string sql = (string)method.Invoke(null, null); //unsafe, if a method does not have a return type that can be casted to a string or void return type, the program will crash.
StorageSystemCore.Reporter.Log($"Trying to run: {sql}");
SQLControl.RunCommand(sql); //first null is given since it is not invoked in an instance and the second null is because there is no parameters in the functions
StorageSystemCore.Reporter.Log("Succeded in running.");
}
catch (Exception e)
{
StorageSystemCore.Reporter.Log("Failed in running.");
throw e;
}
}
StorageSystemCore.Reporter.Log("Creation of stored procedures finalised");
}