public static List <ManufacturerModel> getManufacturerList()
{
List <ManufacturerModel> manufacturerModels = new List <ManufacturerModel>();
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Manufacturers Order by name asc";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ManufacturerModel model = new ManufacturerModel()
{
ManufacturerID = Convert.ToInt32(reader[0]),
Name = reader[1].ToString(),
ClientNumber = reader[2].ToString(),
WebAddress = reader[3].ToString()
};
manufacturerModels.Add(model);
}
con.Close();
return(manufacturerModels);
}
}
public static void ImportFromExcel(string path)
{
List <ArticleModel> ArticlesFromExcel = ExcelEditor.ArticlesFromExcel(path);
CheckAndCreateReferences(ArticlesFromExcel);
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Articles (ArticleID, Description, ID_Category, Pricing, Quantity, ID_Project, SupplierPartNumber, ManufacturerPartNumber, ID_Manufacturer, ID_Supplier, ID_Status, Created, LastUpdate, ID_Location) VALUES (@ArticleID, @Description, @ID_Category, @Pricing, @Quantity, @ID_Project, @SupplierPartNumber, @ManufacturerPartNumber, @ID_Manufacturer, @ID_Supplier, @ID_Status, @Created, @LastUpdate, @ID_Location)";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
foreach (ArticleModel article in ArticlesFromExcel)
{
if (ArticleController.checkSupplierAndManufacturerPartNumber(article.ManufacturerPartNumber, article.SupplierPartNumber).Count == 0)
{
cmd.Parameters.AddWithValue("@ArticleID", article.ArticleID);
cmd.Parameters.AddWithValue("@Description", article.Description);
cmd.Parameters.AddWithValue("@ID_Category", getThreeDigits(article.ArticleID.ToString()));
cmd.Parameters.AddWithValue("@Pricing", article.Pricing);
cmd.Parameters.AddWithValue("@Quantity", article.Quantity);
cmd.Parameters.AddWithValue("@ID_Project", article.ID_Project);
cmd.Parameters.AddWithValue("@SupplierPartNumber", article.SupplierPartNumber);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", article.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@ID_Manufacturer", ManufacturerController.getManufacturerID(article.Manufacturer));
cmd.Parameters.AddWithValue("@ID_Supplier", SupplierController.getSupplierID(article.Supplier));
cmd.Parameters.AddWithValue("@ID_Status", StatusController.getStatusID(article.Status));
cmd.Parameters.AddWithValue("@Created", article.Created);
cmd.Parameters.AddWithValue("@LastUpdate", DateTime.Now.ToString("dd/MM/yyyy"));
cmd.Parameters.AddWithValue("@ID_Location", LocationController.getLocationID(article.Location));
cmd.ExecuteNonQuery();
}
}
con.Close();
MessageBox.Show("Import done succesfully, Please restart the Application");
//MessageBox.Show(article.ArticleID + ": " + article.Description + " was added successfully");
}
public static List <string> searchCategory(string word)
{
List <string> result = new List <string>();
List <CategoryModel> models = new List <CategoryModel>();
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Categories Where Description like '%" + word + "%' ";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
/* CategoryModel model = new CategoryModel()
* {
* CategoryID = reader[0].ToString(),
* Description = reader[1].ToString()
* };
* models.Add(model);*/
result.Add(reader[0].ToString() + " - " + reader[1].ToString());
}
}
con.Close();
return(result);
}
public static List <ArticleModel> checkSupplierAndManufacturerPartNumber(string manufacturerPartNumber, string supplierPartNumber)
{
List <ArticleModel> duplicateArticles = new List <ArticleModel>();
using (SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
con.Open();
string query = "SELECT ArticleID, ManufacturerPartNumber, SupplierPartNumber FROM Articles WHERE ManufacturerPartNumber IS @ManufacturerPartNumber OR SupplierPartNumber IS @SupplierPartNumber";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", manufacturerPartNumber);
cmd.Parameters.AddWithValue("@SupplierPartNumber", supplierPartNumber);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ArticleModel article = new ArticleModel()
{
ArticleID = Convert.ToInt32(reader[0].ToString()),
ManufacturerPartNumber = reader[1].ToString(),
SupplierPartNumber = reader[2].ToString()
};
duplicateArticles.Add(article);
}
}
con.Close();
return(duplicateArticles);
}
}
public static string getProjects(string articleID)
{
string projects = "";
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT proj.ProjectName FROM Article_Project AS artproj" +
" JOIN Projects AS proj ON proj.ProjectID = artproj.ID_Project" +
" WHERE artproj.ID_Article = '" + articleID + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
projects += "," + reader[0].ToString();
}
return(projects.Remove(0, 1));
}
else
{
return("");
}
}
}
public static List <ArticleModel> GetAdvancedSearchList(CheckBox[] cbxs, string input)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>(ExpertSmartSearch.advancedQuery(cbxs, input), new DynamicParameters());
cnn.Close();
addHyperLinkAndProjects(output.ToList());
return(output.ToList());
}
}
internal static void addStatus(StatusModel model)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Status (Status) VALUES(@Status);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@Status", model.Status);
cmd.ExecuteNonQuery();
con.Close();
}
public static List <ArticleModel> myFoundArticles(string columnName, string attr)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>("SELECT Article.ArticleID, Article.Description , Manufacturer.name AS Manufacturer,Article.ManufacturerPartNumber, Supplier.name AS Supplier, Article.SupplierPartNumber , Location.Location AS Location, Article.Quantity, Article.Created, Article.LastUpdate, Article.Pricing FROM Articles AS Article"
+ " left JOIN Manufacturers AS Manufacturer ON Article.ID_Manufacturer = Manufacturer.ManufacturerID "
+ " left JOIN Suppliers AS Supplier ON Article.ID_Supplier = Supplier.SupplierID"
+ " left JOIN Locations AS Location ON Article.ID_Location = Location.LocationID WHERE " + columnName + " Like '%" + attr + "%'", new DynamicParameters());
cnn.Close();
return(output.ToList());
}
}
public static void addLocation(LocationModel location)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Locations (Location) VALUES(@Location);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@Location", location.Location);
cmd.ExecuteNonQuery();
con.Close();
}
public static void deleteArticle(ArticleModel article)
{
archivInsert(article);
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "DELETE FROM Articles WHERE ArticleID = @ArticleID ";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@ArticleID", article.ArticleID);
cmd.ExecuteNonQuery();
con.Close();
}
public static void addManufacturer(ManufacturerModel manufacturer)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
cnn.Open();
SQLiteCommand cmd = new SQLiteCommand(cnn);
cmd.CommandText = "INSERT INTO Manufacturers (Name, ClientNumber, WebAddress) VALUES (@Name, @ClientNumber, @WebAddress)";
cmd.Parameters.AddWithValue("@Name", manufacturer.Name);
cmd.Parameters.AddWithValue("@ClientNumber", manufacturer.ClientNumber);
cmd.Parameters.AddWithValue("@WebAddress", manufacturer.WebAddress);
cmd.ExecuteNonQuery();
}
}
public static void addSupplier(SupplierModel supplier)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Suppliers (Name, ClientNumber, WebAddress) VALUES(@Name,@ClientNumber, @WebAddress);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@Name", supplier.Name);
cmd.Parameters.AddWithValue("@ClientNumber", supplier.webAddress);
cmd.Parameters.AddWithValue("@WebAddress", supplier.clientNumber);
cmd.ExecuteNonQuery();
con.Close();
}
public static void addProject(ProjectModel project)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Projects (ProjectName) VALUES(@ProjectName);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@ProjectName", project.Name);
cmd.ExecuteNonQuery();
con.Close();
}
public static List <ArticleModel> GetInventoryList()
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
var output = cnn.Query <ArticleModel>("SELECT Article.ArticleID, Article.Description , Manufacturer.name AS Manufacturer,Article.ManufacturerPartNumber, Supplier.name AS Supplier, Article.SupplierPartNumber , Location.Location AS Location, Article.Quantity, Article.Created, Article.LastUpdate, Article.Pricing FROM Articles AS Article"
+ " left JOIN Manufacturers AS Manufacturer ON Article.ID_Manufacturer = Manufacturer.ManufacturerID"
+ " left JOIN Suppliers AS Supplier ON Article.ID_Supplier = Supplier.SupplierID"
+ " left JOIN Locations AS Location ON Article.ID_Location = Location.LocationID; ", new DynamicParameters());
cnn.Close();
addHyperLinkAndProjects(output.ToList());
return(output.ToList());
}
}
public static bool addCategory(CategoryModel category)
{
using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
{
cnn.Open();
SQLiteCommand cmd = new SQLiteCommand(cnn);
try
{
cmd.CommandText = "INSERT INTO Categories (CategoryID, Description) VALUES (@CategoryID, @Description)";
cmd.Parameters.AddWithValue("@CategoryID", category.CategoryID);
cmd.Parameters.AddWithValue("@Description", category.Description);
cmd.ExecuteNonQuery();
cnn.Close();
return(true);
}
catch (SQLiteException e)
{
MessageBox.Show(e.Message);
return(false);
}
}
/* public static void copyList()
* {
*
* List < KategorieSimpleModel > diesehier = CSVEditor.kategorienSimpleListe();
*
* using (SQLiteConnection cnn = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString()))
* {
* cnn.Open();
* SQLiteCommand cmd = new SQLiteCommand(cnn);
* foreach (KategorieSimpleModel dies in diesehier) {
* cmd.CommandText = "INSERT INTO Categories1 (CategoryID, Description) VALUES (@CategoryID, @Description)";
* cmd.Parameters.AddWithValue("@CategoryID", dies.id);
* cmd.Parameters.AddWithValue("@Description", dies.bezeichnung);
*
* cmd.ExecuteNonQuery();
* }
* cnn.Close();
*
* }
*
* }*/
}
public static void archivInsert(ArticleModel article)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "INSERT INTO Archiv_Article( " +
"old_ArticleID, old_Description, " +
"old_Category, old_Pricing, " +
"old_Projects, " +
"old_SupplierPartNumber, " +
"old_ManufacturerPartNumber, " +
"old_Manufacturer, " +
"old_Supplier, " +
"old_Status," +
"old_Created, " +
"old_Deleted) " +
"VALUES(" +
"@old_ArticleID, @old_Description, " +
"@old_Category, @old_Pricing, " +
"@old_Projects, " +
"@old_SupplierPartNumber, " +
"@old_ManufacturerPartNumber, " +
"@old_Manufacturer, " +
"@old_Supplier, " +
"@old_Status," +
"@old_Created, " +
"@old_Deleted )";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@old_ArticleID", article.ArticleID);
cmd.Parameters.AddWithValue("@old_Description", article.Description);
cmd.Parameters.AddWithValue("@old_Category", article.Category);
cmd.Parameters.AddWithValue("@old_Pricing", article.Pricing);
cmd.Parameters.AddWithValue("@old_Projects", Article_ProjectController.getProjects(article.ArticleID.ToString()));
cmd.Parameters.AddWithValue("@old_SupplierPartNumber", article.SupplierPartNumber);
cmd.Parameters.AddWithValue("@old_ManufacturerPartNumber", article.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@old_Manufacturer", article.Manufacturer);
cmd.Parameters.AddWithValue("@old_Supplier", article.Supplier);
cmd.Parameters.AddWithValue("@old_Status", article.Status);
cmd.Parameters.AddWithValue("@old_Created", article.Created);
cmd.Parameters.AddWithValue("@old_Created", article.Created);
cmd.Parameters.AddWithValue("@old_Deleted", DateTime.Now.ToString("dd/MM/yyyy"));
cmd.ExecuteNonQuery();
con.Close();
}
public static int getLocationID(string Location)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT LocationID FROM Locations WHERE Location = '" + Location + "';";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var locationID = cmd.ExecuteScalar();
con.Close();
if (locationID != null)
{
return(Convert.ToInt32(locationID));
}
else
{
return(0);
}
}
public static int getManufacturerID(string manufacturerName)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT ManufacturerID FROM Manufacturers WHERE name = '" + manufacturerName + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var manufacturerID = cmd.ExecuteScalar();
con.Close();
if (manufacturerID != null)
{
return(Convert.ToInt32(manufacturerID));
}
else
{
return(0);
}
}
public static void addNewArticle(ArticleModel article)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Articles (ArticleID, Description, ID_Category, Pricing, Quantity, ID_Project, SupplierPartNumber, ManufacturerPartNumber, ID_Manufacturer, ID_Supplier, ID_Status, Created, LastUpdate, ID_Location) VALUES (@ArticleID, @Description, @ID_Category, @Pricing, @Quantity, @ID_Project, @SupplierPartNumber, @ManufacturerPartNumber, @ID_Manufacturer, @ID_Supplier, @ID_Status, @Created, @LastUpdate, @ID_Location)";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
cmd.Parameters.AddWithValue("@ArticleID", article.ArticleID);
cmd.Parameters.AddWithValue("@Description", article.Description);
cmd.Parameters.AddWithValue("@ID_Category", article.ID_Category);
cmd.Parameters.AddWithValue("@Pricing", article.Pricing);
cmd.Parameters.AddWithValue("@Quantity", article.Quantity);
cmd.Parameters.AddWithValue("@ID_Project", article.ID_Project);
cmd.Parameters.AddWithValue("@SupplierPartNumber", article.SupplierPartNumber);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", article.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@ID_Manufacturer", article.ID_Manufacturer);
cmd.Parameters.AddWithValue("@ID_Supplier", article.ID_Supplier);
cmd.Parameters.AddWithValue("@ID_Status", article.ID_Status);
cmd.Parameters.AddWithValue("@Created", article.Created);
cmd.Parameters.AddWithValue("@LastUpdate", article.LastUpdate);
cmd.Parameters.AddWithValue("@ID_Location", article.ID_Location);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(article.ArticleID + ": " + article.Description + " was added successfully");
/*cmd.CommandText = query;
* cmd.Parameters.AddWithValue("@ArticleID", 9000);
* cmd.Parameters.AddWithValue("@Description", "LALALALA");
* cmd.Parameters.AddWithValue("@ID_Category", 121);
* cmd.Parameters.AddWithValue("@Pricing", 12.2);
* cmd.Parameters.AddWithValue("@Quantity", 12);
* cmd.Parameters.AddWithValue("@ID_Projects", 1);
* cmd.Parameters.AddWithValue("@SupplierPartNumber", "asdasdasdas");
* cmd.Parameters.AddWithValue("@ManufacturerPartNumber", "asdasdasd");
* cmd.Parameters.AddWithValue("@ID_Manufacturer", 1);
* cmd.Parameters.AddWithValue("@ID_Supplier", "asdasdasdasd");
* cmd.Parameters.AddWithValue("@ID_Status", 1);
* cmd.Parameters.AddWithValue("@Created", "12/12/2018");
* cmd.Parameters.AddWithValue("@LastUpdate", "12/12/2018");
* cmd.Parameters.AddWithValue("@ID_Location", 1);*/
}
public static int getProjectID(string projectname)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT ProjectID FROM Projects WHERE ProjectName = '" + projectname + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var projectID = cmd.ExecuteScalar();
con.Close();
if (projectID != null)
{
return(Convert.ToInt32(projectID));
}
else
{
return(0);
}
}
public static int getStatusID(string status)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT StatusID FROM Status WHERE Status = '" + status + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var statusID = cmd.ExecuteScalar();
con.Close();
if (statusID != null)
{
return(Convert.ToInt32(statusID));
}
else
{
return(0);
}
}
public static int getCategoryID(string description)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT CategoryID FROM Categories WHERE Description = '" + description + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var categoryID = cmd.ExecuteScalar();
con.Close();
if (categoryID != null)
{
return(Convert.ToInt32(categoryID));
}
else
{
return(0);
}
}
public static bool categoryExist(string categoryID)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT CategoryID FROM Categories WHERE CategoryID = '" + categoryID + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var result = cmd.ExecuteScalar();
con.Close();
if (result != null)
{
return(true);
}
else
{
return(false);
}
}
public static string getProjectNameByID(int projectID)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT ProjectName FROM Projects WHERE ProjectID = '" + projectID + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
var projectname = cmd.ExecuteScalar();
con.Close();
if (projectname != null)
{
return(projectname.ToString());
}
else
{
return("none");
}
}
public static bool articleprojectExists(Article_ProjectModel proj)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Article_Project WHERE ID_Project = '" + proj.ID_Project + "' AND ID_Article = '" + proj.ID_Article + "'";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
}
public static void addArticleProjects(List <Article_ProjectModel> article_projects)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
string query = "INSERT INTO Article_Project (ID_Article, ID_Project) VALUES(@ID_Article, @ID_Project);";
SQLiteCommand cmd = new SQLiteCommand(query, con);
con.Open();
foreach (Article_ProjectModel project in article_projects)
{
if (!articleprojectExists(project))
{
cmd.Parameters.AddWithValue("@ID_Article", project.ID_Article);
cmd.Parameters.AddWithValue("@ID_Project", project.ID_Project);
cmd.ExecuteNonQuery();
}
else
{
}
}
con.Close();
}
public static void updateArticle(ArticleModel am)
{
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "UPDATE Articles SET Description = @Description"
+ ", Pricing = @Pricing"
+ ", ID_Project = @ID_Project"
+ ", SupplierPartNumber = @SupplierPartNumber "
+ ", ManufacturerPartNumber = @ManufacturerPartNumber"
+ ", ID_Manufacturer = @ID_Manufacturer"
+ ", ID_Supplier = @ID_Supplier"
+ ", ID_Status = @ID_Status"
+ ", LastUpdate = @LastUpdate"
+ ", ID_Location = @ID_Location"
+ ", Quantity = @Quantity"
+ " WHERE ArticleID = @ArticleID";
SQLiteCommand cmd = new SQLiteCommand(query, con);
cmd.Parameters.AddWithValue("@Description", am.Description);
cmd.Parameters.AddWithValue("@Pricing", am.Pricing);
cmd.Parameters.AddWithValue("@ID_Project", am.ID_Project);
cmd.Parameters.AddWithValue("@SupplierPartNumber", am.SupplierPartNumber);
cmd.Parameters.AddWithValue("@ManufacturerPartNumber", am.ManufacturerPartNumber);
cmd.Parameters.AddWithValue("@ID_Manufacturer", am.ID_Manufacturer);
cmd.Parameters.AddWithValue("@ID_Supplier", am.ID_Supplier);
cmd.Parameters.AddWithValue("@ID_Status", am.ID_Status);
cmd.Parameters.AddWithValue("@LastUpdate", am.LastUpdate);
cmd.Parameters.AddWithValue("@ID_Location", am.ID_Location);
cmd.Parameters.AddWithValue("@Quantity", am.Quantity);
cmd.Parameters.AddWithValue("@ArticleID", am.ArticleID);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(am.ArticleID + ": " + am.Description + " was successfully updated");
}
public static List <CategoryModel> getCategoryList()
{
List <CategoryModel> models = new List <CategoryModel>();
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Categories";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
CategoryModel model = new CategoryModel()
{
CategoryID = reader[0].ToString(),
Description = reader[1].ToString()
};
models.Add(model);
}
}
con.Close();
return(models);
}
public static List <ProjectModel> getProjectList()
{
List <ProjectModel> projectModels = new List <ProjectModel>();
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Projects";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ProjectModel model = new ProjectModel()
{
ProjectID = Convert.ToInt32(reader[0]),
Name = reader[1].ToString()
};
projectModels.Add(model);
}
con.Close();
return(projectModels);
}
}
public static List <StatusModel> StatusList()
{
List <StatusModel> statusModels = new List <StatusModel>();
SQLiteConnection con = new SQLiteConnection(InventoryDBSqliteConnection.LoadConnectionString());
con.Open();
string query = "SELECT * FROM Status";
SQLiteCommand cmd = new SQLiteCommand(query, con);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
StatusModel model = new StatusModel()
{
StatusID = Convert.ToInt32(reader[0]),
Status = reader[1].ToString()
};
statusModels.Add(model);
}
con.Close();
return(statusModels);
}
}