public static List <string> GetColumnNames(string tableName)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var dataModel = connect.Mapping;
List <string> listOfColumns = new List <string>();
foreach (var result in dataModel.GetTables())
{
if (result.TableName.Equals("dbo." + tableName))
{
foreach (var tableDetails in result.RowType.DataMembers)
{
// Using this if to remove columns with "_" because they belong to foreing keys. This should not be displayed to user.
if (!tableDetails.MappedName.Contains("_"))
{
listOfColumns.Add(tableDetails.MappedName);
}
}
return(listOfColumns);
}
}
return(null);
}
public IEnumerable <TInStock> GetStock()
{
DataLinqToSQLDataContext conn = new DataLinqToSQLDataContext();
string sql = "SELECT StockID, BarcodeID, LocationID, Quantity FROM TInStock";
return(conn.ExecuteQuery <TInStock>(sql));
}
public void InsertNewProduct(string name, decimal price, decimal tax, decimal discount, string description, int FKcategory, int FKbrand, string fullName, string shortName)
{
// Using 'using' to dispose connection after the new product
// gets inserted into the database.
using (DataLinqToSQLDataContext product = new DataLinqToSQLDataContext())
{
// instantiating a new object that will be inserted into database
TProductGroup newProduct = new TProductGroup
{
Name = name,
Price = price,
Tax = tax,
Discount = discount,
DESCRIPTION = description,
CategoryID = FKcategory,
BrandID = FKbrand,
FullProductName = fullName,
ShortProductName = shortName
};
product.TProductGroups.InsertOnSubmit(newProduct);
product.SubmitChanges();
}
}
private void BtnLoggin_Click(object sender, EventArgs e)
{
string userManager = emailTxt.Text;
string passwordmanager = passwordTxt.Text;
using (DataLinqToSQLDataContext user = new DataLinqToSQLDataContext())
{
// Getting managers table
var managerTable = user.GetTable <TManagerInventory>();
var authManager = managerTable.Where(x => x.ManagerEmail == userManager && x.Password == passwordmanager).ToList();
// Checking if user is valid
if (authManager.Count > 0)
{
// This information is going to be sent to the main form
SetManager = emailTxt.Text;
MessageBox.Show("Welcome " + userManager, "Inventory Management", MessageBoxButtons.OK, MessageBoxIcon.None);
MainInventoryForm authForm = new MainInventoryForm();
authForm.Show();
this.Hide();
}
else
{
MessageBox.Show("You enter wrong credentials!", "Inventory Management", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public IQueryable GetProductList()
{
DataLinqToSQLDataContext db = new DataLinqToSQLDataContext();
var query = from prodcuts in db.TProductGroups select prodcuts.Name;
return(query);
}
public List <int> GetBarcode()
{
DataLinqToSQLDataContext db = new DataLinqToSQLDataContext();
var query = from products in db.TProductGroups select products.Barcode;
return(query.ToList());
}
public static IEnumerable <TProductGroup> AllProducts()
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var query = connect.ExecuteQuery <TProductGroup>("SELECT * FROM TProductGroup");
return(query);
}
// Get primary key to display in a combo box
public List <int> GetListBarcode()
{
DataLinqToSQLDataContext products = new DataLinqToSQLDataContext();
var query = from barcode in products.TProductGroups
select barcode.Barcode;
return(query.ToList());
}
public List <int> GetBrandId()
{
DataLinqToSQLDataContext brand = new DataLinqToSQLDataContext();
var query = from brandId in brand.TBrands
select brandId.BrandID;
return(query.ToList());
}
public List <int> GetCategories()
{
DataLinqToSQLDataContext category = new DataLinqToSQLDataContext();
var result = from cat in category.TCategories
select cat.CategoryID;
return(result.ToList());
}
public static IEnumerable <TProductGroup> GetProductGroup(string column, string parameter, string value)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
string sqlStatement = "SELECT * FROM TProductGroup WHERE TProductGroup." + column + " " + parameter + " " + "'" + value + "'";
var query = connect.ExecuteQuery <TProductGroup>(sqlStatement);
return(query);
}
public object PopulateComboBarcode(string param)
{
DataLinqToSQLDataContext products = new DataLinqToSQLDataContext();
string query = "SELECT Barcode, " + param + " FROM TProductGroup";
var result = products.ExecuteQuery <TProductGroup>(query);
return(result.ToList());
}
public void UpdateCategory(int categoryId, string cateName)
{
using (DataLinqToSQLDataContext category = new DataLinqToSQLDataContext())
{
TCategory editCategory = category.TCategories.SingleOrDefault(x => x.CategoryID == categoryId);
editCategory.Name = cateName;
category.SubmitChanges();
}
}
public object ShowCategories()
{
DataLinqToSQLDataContext category = new DataLinqToSQLDataContext();
var query = from categories in category.TCategories
select new
{
categories.Name
};
return(query.ToList());
}
public object ShowComboBrand()
{
DataLinqToSQLDataContext brand = new DataLinqToSQLDataContext();
var result = from brands in brand.TBrands
select new
{
brands.Name
};
return(result.ToList());
}
public object ShowComboCategory()
{
DataLinqToSQLDataContext category = new DataLinqToSQLDataContext();
var result = from categories in category.TCategories
select new
{
categories.Name
};
return(result.ToList());
}
public void InsertCategory(string categoryName)
{
using (DataLinqToSQLDataContext category = new DataLinqToSQLDataContext())
{
TCategory newCategory = new TCategory
{
Name = categoryName
};
category.TCategories.InsertOnSubmit(newCategory);
category.SubmitChanges();
}
}
public void UpdateBrand(int brandId, string name)
{
// Updating product information
using (DataLinqToSQLDataContext brands = new DataLinqToSQLDataContext())
{
TBrand editBrand = brands.TBrands.SingleOrDefault(x => x.BrandID == brandId);
editBrand.Name = name;
brands.SubmitChanges();
}
}
public object ShowBrands()
{
DataLinqToSQLDataContext brands = new DataLinqToSQLDataContext();
var result = from brand in brands.TBrands
select new
{
brand.BrandID,
brand.Name
};
return(result.ToList());
}
public static object GetAllProducts()
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var result = from product in connect.TProductGroups
select new
{
ID = product.Barcode, // If this tag is modified it will impact on the string splitting (Substring) that happens in TrimID located at SearchBL
product.Name,
};
return(result.ToList());
}
public void InsertNewBrand(string brandName)
{
using (DataLinqToSQLDataContext brands = new DataLinqToSQLDataContext())
{
TBrand newBrand = new TBrand
{
Name = brandName
};
brands.TBrands.InsertOnSubmit(newBrand);
brands.SubmitChanges();
}
}
public static object GetUnitCost(int barcode, DateTime startDate, DateTime endDate, int location)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
if (location != 0)
{
var result = from pl in connect.TPurchaseLogs
join lo in connect.TLocations
on pl.LocationID equals lo.LocationID
join pg in connect.TProductGroups
on pl.BarcodeID equals pg.Barcode
where pl.BarcodeID == barcode &&
pl.Date >= startDate &&
pl.Date <= endDate &&
pl.LocationID == location
select new JoinResultGetUnitCostMethod
{
TrNmb = pl.TransactionID,
ProductName = pg.FullProductName,
Location = lo.Name,
Quantity = pl.Quantity,
TotalCost = pl.TotalCost
};
return(result.ToList());
}
else
{
var result = from pl in connect.TPurchaseLogs
join lo in connect.TLocations
on pl.LocationID equals lo.LocationID
join pg in connect.TProductGroups
on pl.BarcodeID equals pg.Barcode
where pl.BarcodeID == barcode &&
pl.Date >= startDate &&
pl.Date <= endDate
select new JoinResultGetUnitCostMethod
{
TrNmb = pl.TransactionID,
ProductName = pg.FullProductName,
Location = lo.Name,
Quantity = pl.Quantity,
TotalCost = pl.TotalCost
};
return(result.ToList());
}
}
private void ShowProduct()
{
using (DataLinqToSQLDataContext products = new DataLinqToSQLDataContext())
{
var query = from product in products.TProductGroups
select product;
IQueryable <TProductGroup> prodQuery =
from prod in products.TProductGroups
orderby prod.BrandID
select prod;
var lambaQuery = products.TProductGroups.OrderBy(x => x.DESCRIPTION).ThenByDescending(x => x.CategoryID);
}
}
protected static MetaTable GetTableType(string tableName)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var dataModel = connect.Mapping;
foreach (var result in dataModel.GetTables())
{
if (result.TableName.Equals(tableName))
{
return(result);
}
}
return(null);
}
public static IEnumerable <T> GetResultSetWithWhereClauses <T>(string table, string column, string parameter, string value, List <string[]> list)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
StringBuilder sb = new StringBuilder();
foreach (string[] clause in list)
{
string whereClause = clause[0] + " " + table + "." + clause[1] + " " + clause[2] + "'" + clause[3] + "' ";
sb.Append(whereClause);
}
string sqlStatement = "SELECT * FROM dbo." + table + " WHERE " + table + "." + column + " " + parameter + " " + "'" + value + "' " + sb.ToString();
var query = connect.ExecuteQuery <T>(sqlStatement);
return(query.ToList());
}
public static List <string> GetTableNames()
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var dataModel = connect.Mapping;
List <string> listOfTables = new List <string>();
foreach (var result in dataModel.GetTables())
{
// Using this if to remove a sensitive table that was added later on the development process. This should not be displayed to the users.
if (!result.RowType.ToString().Contains("TManagerInventory"))
{
listOfTables.Add(result.RowType.ToString());
}
}
return(listOfTables);
}
public void UpdateProduct(int barcode, string name, decimal price, decimal tax, decimal discount, string description, int category, int brand, string fullName, string shortName)
{
// Updating product information
using (DataLinqToSQLDataContext product = new DataLinqToSQLDataContext())
{
TProductGroup editProduct = product.TProductGroups.SingleOrDefault(x => x.Barcode == barcode);
editProduct.Name = name;
editProduct.Price = price;
editProduct.Tax = tax;
editProduct.Discount = discount;
editProduct.DESCRIPTION = description;
editProduct.CategoryID = category;
editProduct.BrandID = brand;
editProduct.FullProductName = fullName;
editProduct.ShortProductName = shortName;
product.SubmitChanges();
}
}
public object ShowInventoryProduct()
{
DataLinqToSQLDataContext products = new DataLinqToSQLDataContext();
var result = from product in products.TProductGroups
select new
{
product.Barcode,
product.Name,
product.Price,
product.Tax,
product.Discount,
product.DESCRIPTION,
product.CategoryID,
product.BrandID,
product.FullProductName,
product.ShortProductName
};
return(result.ToList());
}
public static Type CheckColumnType(string tableName, string column)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var dataModel = connect.Mapping;
foreach (var result in dataModel.GetTables())
{
if (result.TableName.Equals(tableName))
{
foreach (var tableDetails in result.RowType.DataMembers)
{
if (tableDetails.MappedName == column)
{
return(tableDetails.Type);
}
}
}
}
return(null);
}
public static object GetLocationsWithProductId(int value)
{
DataLinqToSQLDataContext connect = new DataLinqToSQLDataContext();
var result = from product in connect.TPurchaseLogs
join location in connect.TLocations
on product.LocationID equals location.LocationID
where product.BarcodeID == value
select new
{
LocID = product.LocationID,
Base = location.Name,
};
var distinctResult = result.Distinct().ToList();
// Adding extra line in the result so we can query independently from product's location;
var emptyObj = new { LocID = (int?)0, Base = "All Locations" };
distinctResult.Add(emptyObj);
return(distinctResult.ToList());
}
