public DataTable LoadCategorys() { connection = new ConnectionClass(); string query = @"SELECT * FROM Categories"; cmd = new SqlCommand(query, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); connection.GetClose(); return(dataTable); }
public int UpdateGridviewRow(Item item) { connection = new ConnectionClass(); int isExecuted = 0; try { commandString = @"UPDATE Items SET Name = '" + item.ItemName + "' WHERE ID = " + item.Id + ""; sqlCommand = new SqlCommand(commandString, connection.GetConnection()); isExecuted = sqlCommand.ExecuteNonQuery(); connection.GetClose(); } catch (Exception exception) { MessageBox.Show(exception.Message); } return(isExecuted); }
public int AvaiLavelQty(StockInModel stockinModel) { connection = new ConnectionClass(); string query = @"SELECT sum(StockIn.Quantity) AS totalStockInQty, sum(StockOut.Quantity) AS totalStockOutQty FROM StockIn LEFT OUTER JOIN StockOut ON StockIn.ItemId = StockOut.ItemId WHERE StockIn.ItemId = '" + stockinModel.ItemId + "' GROUP BY StockIn.ItemId"; cmd = new SqlCommand(query, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); //int avaiLavelQty = 0; int totalInStock = 0; int totalOutStock = 0; foreach (DataRow row in dataTable.Rows) { if (!row.IsNull("totalStockInQty")) { totalInStock = Convert.ToInt32(row["totalStockInQty"]); } if (!row.IsNull("totalStockOutQty")) { totalOutStock = Convert.ToInt32(row["totalStockOutQty"]); } } int avilavelStock; if (dataTable.Rows.Count > 0) { avilavelStock = totalInStock - totalOutStock; } else { avilavelStock = 0; } connection.GetClose(); return(avilavelStock); }
public int InsertStock(StockInModel stockinModel) { connection = new ConnectionClass(); if (!string.IsNullOrEmpty(stockinModel.Id)) { string query = @"Update StockIn set ItemId = '" + stockinModel.ItemId + "', Quantity = '" + stockinModel.StockInQty + "' WHERE Id='" + stockinModel.Id + "'"; cmd = new SqlCommand(query, connection.GetConnection()); } else { string query = @"INSERT INTO StockIn (ItemId, Quantity, CreatedDate) VALUES ( " + stockinModel.ItemId + ", '" + stockinModel.StockInQty + "', '" + stockinModel.CreatedDate + "')"; cmd = new SqlCommand(query, connection.GetConnection()); } int isExecuted; isExecuted = cmd.ExecuteNonQuery(); connection.GetClose(); return(isExecuted); }
public int InsertItem(Item item) { connection = new ConnectionClass(); int isExecuted = 0; try { commandString = @"INSERT INTO Items VALUES('" + item.ItemName + "','" + item.ReorderLevel + "'," + item.CategoryId + "," + item.CompanyId + " ,'" + item.UserId + "','" + item.CreatedDate + "')"; sqlCommand = new SqlCommand(commandString, connection.GetConnection()); isExecuted = sqlCommand.ExecuteNonQuery(); } catch (Exception exception) { MessageBox.Show(exception.Message); } finally { connection.GetClose(); } return(isExecuted); }
public DataTable GetCompanysInCompanyCombobox() { DataTable dataTable = new DataTable(); try { connection = new ConnectionClass(); commandString = @"SELECT * FROM Companys"; sqlCommand = new SqlCommand(commandString, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand); dataAdapter.Fill(dataTable); } catch (Exception exception) { MessageBox.Show(exception.Message); } finally { connection.GetClose(); } return(dataTable); }
public DataTable ShowItemsInDatagridview() { connection = new ConnectionClass(); DataTable dataTable = new DataTable(); try { commandString = @"SELECT i.Id as SL,i.Name,ReOrderLevel,c.Name as Category, a.Name as Company From Items as i INNER JOIN Categories as c ON c.Id=i.CategoryId INNER JOIN Companys as a ON a.Id=i.CompanyId"; sqlCommand = new SqlCommand(commandString, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand); dataAdapter.Fill(dataTable); } catch (Exception exception) { MessageBox.Show(exception.Message); } finally { connection.GetClose(); } return(dataTable); }
public DataTable CatWiseItems(StockInModel stockInModel) { connection = new ConnectionClass(); string query = @"SELECT * FROM Items where CategoryId = '" + stockInModel.CatId + "' AND CompanyId='" + stockInModel.ComId + "'"; cmd = new SqlCommand(query, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); if (dataTable.Rows.Count > 0) { DataRow row = dataTable.NewRow(); row["Id"] = 0; row["Name"] = "Please Select Item"; dataTable.Rows.InsertAt(row, 0); } connection.GetClose(); return(dataTable); }
public DataTable LoadCompanys() { connection = new ConnectionClass(); string query = @"SELECT * FROM Companys"; cmd = new SqlCommand(query, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); DataRow row = dataTable.NewRow(); row[0] = 0; row[1] = "Please Select Company"; dataTable.Rows.InsertAt(row, 0); connection.GetClose(); return(dataTable); }
public DataTable ComWiseCat(StockInModel stockInModel) { connection = new ConnectionClass(); string query = @"select c.Id, c.Name from Items as i left join Categories as c on c.Id = i.CategoryId where i.CompanyId = '" + stockInModel.ComId + "' group by c.Id, c.Name"; cmd = new SqlCommand(query, connection.GetConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); if (dataTable.Rows.Count > 0) { DataRow row = dataTable.NewRow(); row["Id"] = 0; row["Name"] = "Please Select Cartegory"; dataTable.Rows.InsertAt(row, 0); } connection.GetClose(); return(dataTable); }
//Extra method added by group manager in below public List <Item> GetItems() { List <Item> items = new List <Item>(); connection = new ConnectionClass(); string query = "Select i.Id AS ItemId,i.Name AS ItemName,i.ReOrderLevel,i.CreatedDate,i.UserId,co.Id as CompanyId,co.Name as CompanyName,ca.Id as CategoryId,ca.Name as CategoryName From Items AS i INNER JOIN Companys as co ON i.CompanyId=co.Id INNER JOIN Categories as ca ON i.CategoryId=ca.Id"; try { sqlCommand = new SqlCommand(query, connection.GetConnection()); reader = sqlCommand.ExecuteReader(); while (reader.Read()) { Item item = new Item(); item.Id = (int)(reader["ItemId"]); item.ItemName = reader["ItemName"].ToString(); item.ReorderLevel = Convert.ToInt32(reader["ReOrderLevel"]); item.CategoryId = Convert.ToInt32(reader["CategoryId"]); item.CompanyId = Convert.ToInt32(reader["CompanyId"]); item.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]); item.CategoryName = reader["CategoryName"].ToString(); item.CompanyName = reader["CompanyName"].ToString(); item.UserId = Convert.ToInt32(reader["UserId"]); items.Add(item); } } catch (Exception exception) { items = null; } finally { connection.GetClose(); } return(items); }