private static IList<Supplier> GetAllUsingADONET()
{
var daftarSupplier = new List<Supplier>();
using (var conn = GetOpenConnection())
{
var sql = @"SELECT SupplierID, NamaSupplier, Jalan, Kota
FROM Supplier
ORDER BY NamaSupplier";
using (var cmd = new SqlCommand(sql, conn))
{
using (var dtr = cmd.ExecuteReader())
{
while (dtr.Read())
{
var supplier = new Supplier
{
SupplierID = dtr["SupplierID"] is DBNull ? 0 : (int)dtr["SupplierID"],
NamaSupplier = dtr["NamaSupplier"] is DBNull ? string.Empty : (string)dtr["NamaSupplier"],
Jalan = dtr["Jalan"] is DBNull ? string.Empty : (string)dtr["Jalan"],
Kota = dtr["Kota"] is DBNull ? string.Empty : (string)dtr["Kota"]
};
daftarSupplier.Add(supplier);
}
}
}
}
return daftarSupplier;
}
private static int DeleteUsingEF(Supplier supplier)
{
var result = 0;
using (var db = new RetailContext())
{
db.Entry(supplier).State = EntityState.Deleted;
db.SaveChanges();
result = 1;
}
return result;
}
private static int DeleteUsingADONET(Supplier supplier)
{
var result = 0;
using (var conn = GetOpenConnection())
{
var sql = @"DELETE FROM Supplier
WHERE SupplierID = @1";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", supplier.SupplierID);
result = cmd.ExecuteNonQuery();
}
}
return result;
}
private static Beli GetPembelianUsingADONET(string nota)
{
Beli beli = null;
using (var conn = GetOpenConnection())
{
// ambil data header
var sql = @"SELECT Beli.Nota, Beli.Tanggal, Supplier.SupplierID, Supplier.NamaSupplier
FROM Supplier INNER JOIN Beli ON Supplier.SupplierID = Beli.SupplierID
WHERE Beli.Nota = @1";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", nota);
using (var dtr = cmd.ExecuteReader())
{
if (dtr.Read())
{
// mapping record ke objek supplier
var supplier = new Supplier
{
SupplierID = dtr["SupplierID"] is DBNull ? 0 : (int)dtr["SupplierID"],
NamaSupplier = dtr["NamaSupplier"] is DBNull ? string.Empty : (string)dtr["NamaSupplier"]
};
// mapping record ke objek beli
beli = new Beli();
beli.Nota = dtr["Nota"] is DBNull ? string.Empty : (string)dtr["Nota"];
beli.Tanggal = dtr["Tanggal"] is DBNull ? DateTime.MinValue : (DateTime)dtr["Tanggal"];
beli.Supplier = supplier; // hubungkan objek supplier dan beli
}
}
}
if (beli != null)
{
// ambil data detail
var daftarItemBeli = GetDetailPembelian(nota, conn);
beli.ItemBelis = daftarItemBeli; // hubungkan objek beli dg detail beli
}
}
return beli;
}
private static Supplier GetByIDUsingADONET(int supplierID)
{
Supplier supplier = null;
using (var conn = GetOpenConnection())
{
var sql = @"SELECT SupplierID, NamaSupplier, Jalan, Kota
FROM Supplier
WHERE SupplierID = @1";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", supplierID);
using (var dtr = cmd.ExecuteReader())
{
if (dtr.Read())
{
supplier = new Supplier();
supplier.SupplierID = dtr["SupplierID"] is DBNull ? 0 : (int)dtr["SupplierID"];
supplier.NamaSupplier = dtr["NamaSupplier"] is DBNull ? string.Empty : (string)dtr["NamaSupplier"];
supplier.Jalan = dtr["Jalan"] is DBNull ? string.Empty : (string)dtr["Jalan"];
supplier.Kota = dtr["Kota"] is DBNull ? string.Empty : (string)dtr["Kota"];
}
}
}
}
return supplier;
}
private static int UpdateUsingADONET(Supplier supplier)
{
var result = 0;
using (var conn = GetOpenConnection())
{
var sql = @"UPDATE Supplier SET NamaSupplier = @1, Jalan = @2, Kota = @3
WHERE SupplierID = @4";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", supplier.NamaSupplier);
cmd.Parameters.AddWithValue("@2", supplier.Jalan);
cmd.Parameters.AddWithValue("@3", supplier.Kota);
cmd.Parameters.AddWithValue("@4", supplier.SupplierID);
result = cmd.ExecuteNonQuery();
}
}
return result;
}
private static int SaveUsingADONET(Supplier supplier)
{
var result = 0;
using (var conn = GetOpenConnection())
{
var sql = @"INSERT INTO Supplier (NamaSupplier, Jalan, Kota)
VALUES (@1, @2, @3)";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", supplier.NamaSupplier);
cmd.Parameters.AddWithValue("@2", supplier.Jalan);
cmd.Parameters.AddWithValue("@3", supplier.Kota);
result = cmd.ExecuteNonQuery();
}
}
return result;
}
private static IList<Supplier> GetByNameUsingADONET(string namaSupplier)
{
var daftarSupplier = new List<Supplier>();
using (var conn = GetOpenConnection())
{
var sql = @"SELECT SupplierID, NamaSupplier, Jalan, Kota
FROM Supplier
WHERE NamaSupplier LIKE @1
ORDER BY NamaSupplier";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@1", "%" + namaSupplier + "%");
using (var dtr = cmd.ExecuteReader())
{
while (dtr.Read())
{
var supplier = new Supplier
{
SupplierID = dtr["SupplierID"] is DBNull ? 0 : (int)dtr["SupplierID"],
NamaSupplier = dtr["NamaSupplier"] is DBNull ? string.Empty : (string)dtr["NamaSupplier"],
Jalan = dtr["Jalan"] is DBNull ? string.Empty : (string)dtr["Jalan"],
Kota = dtr["Kota"] is DBNull ? string.Empty : (string)dtr["Kota"]
};
daftarSupplier.Add(supplier);
}
}
}
}
return daftarSupplier;
}