public List <ZipList> GetZipListInZip(ref Zip z)
{
string SelectZipListInZipToolString = @"SELECT * FROM ZipList WHERE ([ZIP] = @ZId)";
using (var cmd = new SqlCommand(SelectZipListInZipToolString, OpenConnection))
{
SqlDataReader reader = null;
cmd.Parameters.AddWithValue("@ZId", z.ZipId);
reader = cmd.ExecuteReader();
List <ZipList> eachZipList = new List <ZipList>();
while (reader.Read())
{
var zipList = new ZipList
{
City = (string)reader["City"],
Country = (string)reader["Country"],
ZipCode = (string)reader["ZipCode"]
};
eachZipList.Add(zipList);
}
return(eachZipList);
}
}
public void DeleteZipListDb(ref ZipList zl)
{
string deleteString = @"DELETE FROM ZipList WHERE (ZipListId = @ZipListId)";
using (SqlCommand cmd = new SqlCommand(deleteString, OpenConnection))
{
cmd.Parameters.AddWithValue("@ZipListId", zl.ZipListId);
var id = (int)cmd.ExecuteNonQuery();
zl = null;
}
}
public void UpdateZipListDb(ref ZipList zl)
{
string updateString = @"UPDATE ZipList
SET City = @City, Country = @Country, ZipCode = @ZipCode
WHERE ZipListId = @ZipListId";
using (SqlCommand cmd = new SqlCommand(updateString, OpenConnection))
{
cmd.Parameters.AddWithValue("@City", zl.City);
cmd.Parameters.AddWithValue("@Country", zl.Country);
cmd.Parameters.AddWithValue("@ZipCode", zl.ZipCode);
var id = (int)cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Test query to display a list of all zipcodes in the DB
/// </summary>
public void ReadAllZips()
{
// Initialize our database context
using (var context = new Pharm2UEntities())
{
// clear the previous results
ZipList.Clear();
// Add the zip for each record found
foreach (var record in context.P2U_ZipCodes)
{
Console.WriteLine(record.Zip);
ZipList.Add(record.Zip);
}
}
}
public void AddZipListDb(ref ZipList zl)
{
string insertStringParam = @"INSERT INTO [ZipList] (City, Country, ZipCode)
OUTPUT INSERTED.ZipListId
VALUES (@City, @Country, @ZipCode)";
using (SqlCommand cmd = new SqlCommand(insertStringParam, OpenConnection))
{
cmd.Parameters.AddWithValue("@City", zl.City);
cmd.Parameters.AddWithValue("@Country", zl.Country);
cmd.Parameters.AddWithValue("@ZipCode", zl.ZipCode);
//zl.Zip = (int) cmd.ExecuteScalar();
zl.ZipListId = (int)cmd.ExecuteScalar(); // Returns the identity of the new record
}
}
// A simple clipped list of changed items.
private void DrawChangedItemsTab()
{
using var tab = DrawTab(ChangedItemsTabHeader, Tabs.ChangedItems);
if (!tab)
{
return;
}
using var list = ImRaii.ListBox("##changedItems", -Vector2.One);
if (!list)
{
return;
}
var zipList = ZipList.FromSortedList(_mod.ChangedItems);
var height = ImGui.GetTextLineHeight();
ImGuiClip.ClippedDraw(zipList, kvp => _window.DrawChangedItem(kvp.Item1, kvp.Item2, true), height);
}
public void GetZipListById(ref ZipList zl)
{
string sqlcmd = @"SELECT City, Country, ZipCode
FROM ZipList WHERE (ZipListId = @ZipListId)";
using (var cmd = new SqlCommand(sqlcmd, OpenConnection))
{
cmd.Parameters.AddWithValue("@ZipListId", zl.ZipListId);
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.Read())
{
zl.ZipListId = (int)reader["ZipListId"];
zl.City = (string)reader["City"];
zl.Country = (string)reader["Country"];
zl.ZipCode = (string)reader["ZipCode"];
}
}
}
private void LoadSpecification()
{
ServiceZipList.Clear();
EmptyZipsList.Clear();
NewZipList.Clear();
_reportsController.ShowDialogWindow();
foreach (var serviceZip in _excelSpecificationLoader.GetServiceZips(SelectedCompany, _specFileName, SelectedMonth, SelectedYear).Result)
{
ServiceZipList.Add(serviceZip);
}
foreach (var serviceZip in ServiceZipList)
{
if (!ZipList.Any(z => z.ZipName.ToLower() == serviceZip.ZipName.ToLower()))
{
if (!NewZipList.Any(e => e.ZipName.ToLower() == serviceZip.ZipName.ToLower()))
{
NewZipList.Add(new ZipSet {
ZipName = serviceZip.ZipName
});
}
}
}
}
