public DataTable GetDataTable()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = "SELECT * FROM Products";
DataTable gridDT = aDB.ExecSQLQuery(sqlcmd);
return gridDT;
}
public void DeleteFromDB(int id)
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
sqlcmd = "DELETE FROM Hardwares" +
" WHERE [IdHardware] = " + id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public DataTable GetItemsTable()
{
DataTable _dt = null;
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
sqlcmd = "SELECT IdItem AS ValueMember, (ItemName & ' ' & OrderPrefix) AS DisplayMember FROM Items";
_dt = aDB.ExecSQLQuery(sqlcmd);
return _dt;
}
public DataTable GetHardwareTable()
{
DataTable _dt = null;
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
sqlcmd = "SELECT H.IdHardware AS ValueMember, (H.HardwareName & ' ' & I.ItemName) AS DisplayMember" +
" FROM Hardwares H " +
" LEFT JOIN Items I" +
" ON H.ItemId = I.IdItem" +
" WHERE NOT H.IdHardware = " + this.Id;
_dt = aDB.ExecSQLQuery(sqlcmd);
return _dt;
}
public DataTable GetDataTable()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = @"SELECT I.IdItem,
I.ItemName,
I.ProducerId,
I.OrderPrefix,
I.MachineBit,
I.ParentId,
P.ProducerName
FROM Items I
LEFT JOIN Producers P ON I.ProducerId = P.IdProducer";
DataTable gridDT = aDB.ExecSQLQuery(sqlcmd);
return gridDT;
}
public void SaveToDB()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
if (this.Id == 0)
{
sqlcmd = "SELECT TOP 1 IdStorage FROM Storages ORDER BY IdStorage DESC";
Id = aDB.ExecSQLQuery(sqlcmd).Select().First().Field<int>("IdStorage") + 1;
sqlcmd = "INSERT INTO Storages" +
" ([IdStorage]) VALUES (" + this.Id + ")";
aDB.ExecSQLNonQuery(sqlcmd);
}
sqlcmd = "UPDATE Storages" +
" SET [StorageName] = '" + this.Name + "'" +
" WHERE [IdStorage] = " + this.Id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public DataTable GetDataTable()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = @"SELECT H.IdHardware,
H.HardwareName,
H.ItemId,
H.ParentHardware,
Ph.HardwareName,
I.ItemName,
P.ProducerName
FROM (((Hardwares H)
LEFT JOIN Items I ON H.ItemId = I.IdItem)
LEFT JOIN Hardwares Ph ON H.ParentHardware = Ph.IdHardware)
LEFT JOIN Producers P ON I.ProducerId = P.IdProducer";
DataTable gridDT = aDB.ExecSQLQuery(sqlcmd);
return gridDT;
}
public void SaveToDB()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
if (this.Id == 0)
{
sqlcmd = "SELECT TOP 1 IdProducer FROM Producers ORDER BY IdProducer DESC";
this.Id = aDB.ExecSQLQuery(sqlcmd).Select().First().Field<int>("IdProducer")+1;
sqlcmd = "INSERT INTO Producers" +
" ([IdProducer]) VALUES (" + this.Id + ")";
aDB.ExecSQLNonQuery(sqlcmd);
}
sqlcmd = "UPDATE Producers" +
" SET [ProducerName] = '" + this.Name + "'," +
" [ProducerShortName] = '" + this.ShortName + "'" +
" WHERE [IdProducer] = " + this.Id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public void SaveToDB()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
if (this.Id == 0)
{
sqlcmd = "SELECT TOP 1 IdHardware FROM Hardwares ORDER BY IdHardware DESC";
this.Id = aDB.ExecSQLQuery(sqlcmd).Select().First().Field<int>("IdHardware") + 1;
sqlcmd = "INSERT INTO Hardwares" +
" ([IdHardware]) VALUES (" + this.Id + ")";
aDB.ExecSQLNonQuery(sqlcmd);
}
sqlcmd = "UPDATE Hardwares" +
" SET [HardwareName] = '" + this.Name + "'," +
" [ItemId] = " + ((this.ItemId != null) ? this.ItemId.ToString() : "NULL") + "," +
" [ParentHardware] = " + ((this.ParentHardware != null) ? this.ParentHardware.ToString() : "NULL") +
" WHERE [IdHardware] = " + this.Id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public void SaveToDB()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
if (this.Id == 0)
{
sqlcmd = "SELECT TOP 1 IdItem FROM Items ORDER BY IdItem DESC";
this.Id = aDB.ExecSQLQuery(sqlcmd).Select().First().Field<int>("IdItem") + 1;
sqlcmd = "INSERT INTO Items" +
" ([IdItem]) VALUES (" + this.Id + ")";
aDB.ExecSQLNonQuery(sqlcmd);
}
sqlcmd = "UPDATE Items" +
" SET [ItemName] = '" + this.Name + "'," +
" [OrderPrefix] = '" + this.OrderPrefix + "'," +
" [MachineBit] = " + this.MachineBit + "," +
" [ProducerId] = " + ((this.ProducerId != null) ? this.ProducerId.ToString() : "NULL") +
" WHERE [IdItem] = " + this.Id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public void SaveToDB()
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = null;
if (this.Id == 0)
{
sqlcmd = "SELECT TOP 1 IdProduct FROM Products ORDER BY IdProduct DESC";
this.Id = aDB.ExecSQLQuery(sqlcmd).Select().First().Field<int>("IdProduct") + 1;
sqlcmd = "INSERT INTO Products" +
" ([IdProduct]) VALUES (" + this.Id + ")";
aDB.ExecSQLNonQuery(sqlcmd);
}
sqlcmd = "UPDATE Products" +
" SET [ProductName] = '" + this.Name + "'," +
" [ItemId] = " + ((this.ItemId != null) ? this.ItemId.ToString() : "NULL") + "," +
" [SerialNumber] = '" + this.SerialNumber + "'," +
" [InventoryNumber] = '" + this.InventoryNumber + "'," +
" [ProductionDate] = " + ((this.ProductionDate != null) ? "'"+this.ProductionDate.Value.ToString()+"'" : "NULL") + "," +
" [PurchaseDate] = " + ((this.PurchaseDate != null) ? "'"+this.PurchaseDate.Value.ToString()+ "'" : "NULL") +
" WHERE [IdProduct] = " + this.Id;
aDB.ExecSQLNonQuery(sqlcmd);
}
public static void SetDGridView(DataGridView _dGW, string _tbl)
{
{
CAccessDB aDB = new CAccessDB();
string sqlcmd = @"SELECT P.TTableFieldName,
P.TTableFieldHeaderText,
P.TTableFieldVisible,
P.TTableFieldWidth,
P.TTableFieldPriority
FROM PGrids P, TTables T
WHERE P.TTableID = T.IdTTable
AND T.TTableName = '" + _tbl + "'";
DataTable gridDT = aDB.ExecSQLQuery(sqlcmd);
var query = from tbl in gridDT.AsEnumerable()
orderby tbl.Field<int?>("TTableFieldPriority")
select new CGridField()
{
FieldHeaderText = tbl.Field<string>("TTableFieldHeaderText"),
FieldName = tbl.Field<string>("TTableFieldName"),
FieldVisible = tbl.Field<bool>("TTableFieldVisible"),
FieldWidth = tbl.Field<int>("TTableFieldWidth"),
FieldPriority = tbl.Field<int?>("TTableFieldPriority")
};
Fields = query.ToList();
}
int _index = 0;
foreach ( CGridField _el in Fields)
{
_dGW.Columns[_el.FieldName].Visible = _el.FieldVisible;
if (_el.FieldVisible == true)
{
_dGW.Columns[_el.FieldName].HeaderText = _el.FieldHeaderText;
_dGW.Columns[_el.FieldName].Width = _el.FieldWidth;
_dGW.Columns[_el.FieldName].DisplayIndex = _index++;
}
}
}
