/// <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"); }