public List<Item> ItemsGridview(string name)
{
var griditems = new List<Item>();
Item item = null;
using (var db = new Database())
{
db.OpenConnection();
db.CreateCommand(
"SELECT Item.*, Quality.Name AS Quality FROM Item, Quality WHERE Item.Quality_ID=Quality.Quality_ID AND Item.Name LIKE @name");
db.AddParameter("@name", "%" + name + "%");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
item = new Item();
item.Name = (string)dr["name"];
item.Level = (int)dr["level"];
item.ReqLevel = (int)dr["Reqlevel"];
item.Quality = (string)dr["Quality"];
griditems.Add(item);
}
}
return griditems;
}
/// <summary>
/// The get ships from database.
/// </summary>
/// <returns>
/// The <see cref="List" />.
/// </returns>
public List<Ship> GetShips()
{
var shiplist = new List<Ship>();
using (var db = new Database())
{
Ship ships = null;
db.OpenConnection();
db.CreateCommand("Select * FROM ship");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
ships = new Ship();
ships.type = (string)dr["type"];
ships.height = (int)dr["height"];
ships.length = (int)dr["Length"];
ships.width = (int)dr["width"];
ships.power = (int)dr["poweroutlet"];
shiplist.Add(ships);
}
}
return shiplist;
}
/// <summary>
/// The get containers.
/// </summary>
/// <param name="ship">
/// The ship.
/// </param>
/// <param name="port">
/// The port.
/// </param>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
public List<Container> GetContainers(string ship, string port)
{
var containerlist = new List<Container>();
using (var db = new Database())
{
Container containers = null;
db.OpenConnection();
db.CreateCommand(
"Select Container.*,Company.Name AS name,Ship.type AS shiptype from Container,Ship,Port,Company WHERE Ship.Type LIKE @ship AND Port.Name LIKE @port AND Company.ID = Container.Company_ID AND Ship.ID = Container.Ship_ID");
db.AddParameter("@ship", ship);
db.AddParameter("@port", port);
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
containers = new Container();
containers.id = (int)dr["id"];
containers.type = (string)dr["type"];
containers.weight = (int)dr["weight"];
containers.onship = (string)dr["shiptype"];
containers.companyname = (string)dr["name"];
containerlist.Add(containers);
}
}
return containerlist;
}
public Item ItemGridview(string name)
{
Item item = null;
using (Database db = new Database())
{
db.OpenConnection();
db.CreateCommand("SELECT * FROM Item");
DataTable datatableitems = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter();
da.Fill(datatableitems);
}
return item;
}
public Item GetItemById(int id)
{
Item item = null;
using (Database db = new Database())
{
db.OpenConnection();
db.CreateCommand("SELECT * FROM Item WHERE Item_ID = " + id.ToString());
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
item = new Item();
item.Name = dr["name"].ToString();
item.Level = dr["level"].ToString();
}
}
return item;
}
/// <summary>
/// The get ports from Database.
/// </summary>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
public List<Port> GetPorts()
{
var Portlist = new List<Port>();
using (var db = new Database())
{
Port ports = null;
db.OpenConnection();
db.CreateCommand("Select Name from Port");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
ports = new Port();
ports.name = (string)dr["name"];
Portlist.Add(ports);
}
}
return Portlist;
}
/// <summary>
/// The get companies from database.
/// </summary>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
public List<Company> GetCompanies()
{
var companylist = new List<Company>();
using (var db = new Database())
{
Company companies = null;
db.OpenConnection();
db.CreateCommand("Select * from company");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
companies = new Company();
companies.id = (int)dr["id"];
companies.name = (string)dr["name"];
companylist.Add(companies);
}
}
return companylist;
}
public List<Classes> ClassesGridview(string name)
{
var gridclasses = new List<Classes>();
Classes classes = null;
using (var db = new Database())
{
db.OpenConnection();
db.CreateCommand(
"SELECT Class.*, Spec.Name AS Spec FROM Class, Spec WHERE Class.Class_ID=Spec.Class_ID AND Class.Name LIKE @name");
db.AddParameter("@name", "%" + name + "%");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
classes = new Classes();
classes.Name = (string)dr["name"];
classes.Specs = (string)dr["spec"];
gridclasses.Add(classes);
}
}
return gridclasses;
}
public List<Guide> GuideGridview(string name)
{
var gridguide = new List<Guide>();
Guide guide = null;
using (var db = new Database())
{
db.OpenConnection();
db.CreateCommand(
"SELECT Guide.Name, Author.Name AS Author FROM Guide, Author WHERE Guide.Author_ID=Author.Author_ID And Guide.Name LIKE @name");
db.AddParameter("@name", "%" + name + "%");
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
guide = new Guide();
guide.Name = (string)dr["name"];
guide.Author = (string)dr["author"];
gridguide.Add(guide);
}
}
return gridguide;
}
public Item GetItemByName(string name)
{
Item item = null;
using (Database db = new Database())
{
db.OpenConnection();
//db.CreateCommand("SELECT * FROM Item WHERE Name = @name");
//db.AddParameter("@name", name);
db.CreateCommand("SELECT * FROM Item WHERE Name LIKE @name");
db.AddParameter("@name", "%" + name + "%"); // Part of the string
MySqlDataReader dr = db.Command.ExecuteReader();
while (dr.Read())
{
item = new Item();
item.Name = dr["name"].ToString();
item.Level = dr["level"].ToString();
}
}
return item;
}