/// <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>
///
/// </summary>
/// <param name="types"></param>
/// <returns></returns>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static string[] GetColumnNamesAndTypesSP(out string[] types)
{
string sqlString =
$"EXEC GetColumnNamesAndTypes;";
try
{
return(SQLControl.GetColumnNamesAndTypes(sqlString, out types));
}
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 Select all data stored procedure to collect all ware information in the database.
/// </summary>
/// <returns>Returns all information of all wares in the database as a list of lists of strings.</returns>
/// <exception cref="InvalidCastException"></exception>
/// <exception cref="SqlException"></exception>
/// <exception cref="InvalidOperationException"></exception>
/// <exception cref="System.IO.IOException"></exception>
/// <exception cref="ObjectDisposedException"></exception>
public static List <List <string> > GetAllInformation()
{
string sqlString =
"EXEC SelectAllData;";
try {
return(SQLControl.GetValuesMultiWares(sqlString));
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not get information: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
//return null;
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;
}
}
/// <summary>
/// Not Working as MSSQL thinks @Column is the column name. Uses the select part values from one ware stored procedure to retive the information(s) of <paramref name="columns"/> of the ware with <paramref name="id"/>.
/// </summary>
/// <param name="id">The ID of the ware to collect information from. </param>
/// <param name="columns">The column(s) to get information(s) from. </param>
/// <returns>Returns a list of strings, each string containg a column of data.</returns>
/// <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 List <string> GetPartlyInformationFromOneWareSP(string id, string columns)
{
if (id == null || columns == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC SelectPartValuesFromOneWare @WareID = {id}, @Columns = {columns};";
try {
return(SQLControl.GetValuesSingleWare(sqlString));
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not get information: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
//return null;
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");
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
/// <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 List <string> GetColumnNamesSP(string table)
{
if (table == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC GetColumnNames @Table = {table}";
try
{
return(SQLControl.GetColumnNames(sqlString));
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not retrive columns: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
//return null;
throw e;
}
}
/// <summary>
/// Not Working as MSSQL thinks @Column is the column name. Uses the Select partly data procedure to collect the data in <paramref name="columns"/> of all wares in the database.
/// </summary>
/// <param name="columns"></param>
/// <returns>Returns information from <paramref name="columns"/> of all wares in the database as a list of lists of strings.</returns>
/// <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 List <List <string> > GetPartlyInformation(string columns)
{
if (columns == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC SelectPartlyData @Columns = {columns};";
try
{
return(SQLControl.GetValuesMultiWares(sqlString));
}
catch (Exception e)
{
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not get information: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
//return null;
throw e;
}
}
/// <summary>
/// Uses the get type storage procedure to retrive the type of <paramref name="id"/>.
/// </summary>
/// <param name="id">The ware id to retrive the type of</param>
/// <returns>Returns the type of <paramref name="id"/> in a string list.</returns>
/// <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 List <string> GetTypeSP(string id)
{
if (id == null)
{
throw new NullReferenceException();
}
string sqlString =
$"EXEC FindWareType @WareID = {id};";
try
{
return(SQLControl.GetValuesSingleWare(sqlString));
}
catch (Exception e)
{
throw e;
//StorageSystemCore.Reporter.Report(e);
//Console.WriteLine($"Could not find the type: {e.Message}");
//StorageSystemCore.Support.WaitOnKeyInput();
//return null;
}
}
