public IEnumerable <Suppliers> GetSuppliersByMaxCategory(ref int counted) //4 query
{
var connection = new MyDBConnection();
connection.OpenConnection();
var sqlExpression = "SELECT v.supplier, s.supplierId, counted " +
"FROM V5 v " +
"JOIN Suppliers s ON v.supplier = s.name " +
"ORDER BY counted DESC";
SqlDataAdapter adapter = new SqlDataAdapter(sqlExpression, connection.Connection);
var ds = new DataSet();
adapter.Fill(ds);
connection.CloseConnection();
counted = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[1]);
var list = new List <Suppliers>();
foreach (DataRow x in ds.Tables[0].Rows)
{
if ((int)x.ItemArray[2] != counted)
{
break;
}
var id = x.ItemArray[1];
var name = x.ItemArray[0];
list.Add(new Suppliers((int)id, (string)name));
}
return(list);
}
public IEnumerable <Suppliers> GetAll()
{
var sqlExpression = "SELECT * FROM Suppliers";
var connection = new MyDBConnection();
connection.OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter(sqlExpression, connection.Connection);
var ds = new DataSet();
adapter.Fill(ds);
connection.CloseConnection();
var list = new List <Suppliers>();
foreach (DataRow x in ds.Tables[0].Rows)
{
var id = x.ItemArray[0];
var name = x.ItemArray[1];
list.Add(new Suppliers((int)id, (string)name));
}
return(list);
}
public Suppliers GetById(int id)
{
var connection = new MyDBConnection();
connection.OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("SELECT * FROM Suppliers WHERE supplierId = @id",
connection.Connection);
var idParam = new SqlParameter("@id", id);
adapter.SelectCommand.Parameters.Add(idParam);
var ds = new DataSet();
adapter.Fill(ds);
connection.CloseConnection();
return(new Suppliers(id, (string)ds.Tables[0].Rows[0].ItemArray[1]));
}
public Categories GetById(int id)
{
var connection = new MyDBConnection();
connection.OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("SELECT * FROM Categories WHERE categoryId = @id",
connection.Connection);
var idParam = new SqlParameter("@id", id);
adapter.SelectCommand.Parameters.Add(idParam);
var ds = new DataSet();
adapter.Fill(ds);
connection.CloseConnection();
return(new Categories(id, ds.Tables[0].Rows[0].ItemArray[1].ToString()));
}