public static void deleteMainTbl2(BusLicense busLic) { string connStr = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString; string deleteQry = "DELETE FROM [LRW_V8].[LR_BusinessLicenses] WHERE license_number = @license_number AND license_type = @license_type AND license_classification = @license_classification " + "AND license_category = @license_category AND contact_name = @contact_name AND account_start_date = @account_start_date AND street_address = @street_address " + "AND city = @city AND state = @state AND zip_code = @zip_code"; using (var conn = new SqlConnection(connStr)) { using (SqlCommand command = new SqlCommand(deleteQry, conn)) { command.Parameters.AddWithValue("@license_number", busLic.license_number); command.Parameters.AddWithValue("@license_type", busLic.license_type); command.Parameters.AddWithValue("@license_classification", busLic.license_classification); command.Parameters.AddWithValue("@license_category", busLic.license_category); command.Parameters.AddWithValue("@contact_name", busLic.contact_name); command.Parameters.AddWithValue("@account_start_date", busLic.account_start_date); command.Parameters.AddWithValue("@street_address", busLic.street_address); command.Parameters.AddWithValue("@city", busLic.city); command.Parameters.AddWithValue("@state", busLic.state); command.Parameters.AddWithValue("@zip_code", busLic.zip_code); conn.Open(); int result = command.ExecuteNonQuery(); if (result < 1) { Debug.WriteLine("No data inserted"); } conn.Close(); } } }
public static void InsertChangesTbl(BusLicense busLic, string action) { string connStr = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString; string insertChangesQry = "INSERT INTO [LRW_V8].[LR_BusinessLicenses_Changes] (RUNDATE, ACTION, license_number, license_type, license_classification, license_category, business_name, contact_name, account_start_date, street_address, city, state, zip_code) " + "VALUES (@RUNDATE, @ACTION, @license_number, @license_type, @license_classification, @license_category, @business_name, @contact_name, @account_start_date, @street_address, @city, @state, @zip_code)"; using (var conn = new SqlConnection(connStr)) { using (SqlCommand command2 = new SqlCommand(insertChangesQry, conn)) { command2.Parameters.AddWithValue("@RUNDATE", DateTime.Now); command2.Parameters.AddWithValue("@ACTION", action); command2.Parameters.AddWithValue("@license_number", (string)busLic.license_number); command2.Parameters.AddWithValue("@license_type", (string)busLic.license_type); command2.Parameters.AddWithValue("@license_classification", (string)busLic.license_classification); command2.Parameters.AddWithValue("@license_category", (string)busLic.license_category); command2.Parameters.AddWithValue("@business_name", (string)busLic.business_name); command2.Parameters.AddWithValue("@contact_name", (string)busLic.contact_name); command2.Parameters.AddWithValue("@account_start_date", busLic.account_start_date); command2.Parameters.AddWithValue("@street_address", (string)busLic.street_address); command2.Parameters.AddWithValue("@city", (string)busLic.city); command2.Parameters.AddWithValue("@state", (string)busLic.state); command2.Parameters.AddWithValue("@zip_code", (string)busLic.zip_code); //command2.Parameters.AddWithValue("@x_coord", Convert.ToDouble(busLic.geocoded_column.longitude)); //command2.Parameters.AddWithValue("@y_coord", Convert.ToDouble(busLic.geocoded_column.latitude)); conn.Open(); int result = command2.ExecuteNonQuery(); if (result < 1) { Debug.WriteLine("No data inserted"); } conn.Close(); } } }
static void Main(string[] args) { /* * First create a dictionary using business license number as keys and List<BusLicense> as values. Use the city of LR REST service to build this. * Loop thru each business object key in the dictionary: * query records for that license number in main table of database (this I call a rec set or "recs") * If there is one business obj and <2 recs for that license number * Check if there are any recs for that busness license number, * if not add to main tbl and changes tbl as ADD * if there is one, check if the records all match * If not, update rec in main table and add 2 recs to changes table as UPDATEOLD and UPDATENEW * If there are >=2 business objs OR >=2 recs * Loop through each business object(s) and each rec for given license number and compare every field. * If match is found for given business object, then skip * If no match is found for given business object, add that business object to main table and to changes table (as ADD). * * Loop through each rec in record set - this part is to determine the recs to delete. * See if there is a key in the dictionary with the license number in the record set. * If there is no key, the license number must have been dropped. Delete this record from main db table and add to changes tbl as DELETE. * If there is a key, loop through the business object(s) in list and compare all fields. * If there is no match, delete this record from main db table and add to changes tbl as DELETE. * * The Program2 Main method is for creating a new LR_BusinessLicenses table. This should only be done once except for during testing phase. * To toggle between the programs, go to Properties --> Application tab --> startup object */ string connStr = ConfigurationManager.ConnectionStrings["IMSReader"].ConnectionString; // Get the current business license data from the LR city website as list of business license objects. // Had to add this because it was not working on the server. System.Net.ServicePointManager.Expect100Continue = false; System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; //HttpWebRequest WebReq = (HttpWebRequest)WebRequest.Create(string.Format("https://data.littlerock.gov/resource/vthq-dt7e.json?license_type=RESTAURANT")); HttpWebRequest WebReq = (HttpWebRequest)WebRequest.Create(string.Format("https://data.littlerock.gov/resource/vthq-dt7e.json?$limit=50000")); WebReq.Method = "GET"; HttpWebResponse WebResp = (HttpWebResponse)WebReq.GetResponse(); int busLicCount = 0; int busLicDupCount = 0; int changedRecCount = 0; int addCount = 0; int deletedRecs = 0; Boolean matchFound = false; string jsonString; using (Stream stream = WebResp.GetResponseStream()) { // Create a list of business objects from the LR city REST service. StreamReader reader = new StreamReader(stream, System.Text.Encoding.UTF8); jsonString = reader.ReadToEnd(); List <BusLicense> busLicenses = JsonConvert.DeserializeObject <List <BusLicense> >(jsonString); System.Diagnostics.Debug.WriteLine("busLicenses.Count= " + busLicenses.Count.ToString()); if (busLicenses.Count < 1) { log.Debug("no business license objects returned from LR REST"); return; } log.Debug("Total number of business licenses on LR site = " + busLicenses.Count); // Create a dictionary from list of BusLicense objects: license number --> list of busLicense objects // Note: have to do this because there are duplicate records in LR City data with same business license number. // Only add the license numbers that start with BL Dictionary <string, List <BusLicense> > busLicenseDict = new Dictionary <string, List <BusLicense> >(); foreach (var itm in busLicenses) { busLicCount = busLicCount + 1; if (itm.license_number.ToString().Substring(0, 2) == "BL") { if (busLicenseDict.ContainsKey(itm.license_number)) { busLicDupCount = busLicDupCount + 1; busLicenseDict[itm.license_number].Add(itm); } else { busLicenseDict.Add(itm.license_number, new List <BusLicense>()); busLicenseDict[itm.license_number].Add(itm); } } } log.Debug("Total business licenses with BL prefix = " + busLicenseDict.Count().ToString()); // Loop through the business license objects in the dictionary and use the license number to query the db table. // If there is only one business object in the list and one or less recs, deal with these first. foreach (var dictEntry in busLicenseDict) { string mainTblQry = "SELECT [license_number], [license_type], [license_classification], [license_category], [business_name], [contact_name], " + "[account_start_date], [street_address], [city], [state], [zip_code], [x_coord], [y_coord] " + "FROM [V8].[LRW_V8].[LR_BusinessLicenses] " + "WHERE license_number = '" + dictEntry.Key + "'"; BusinessLics_console.DataAccess da1 = new BusinessLics_console.DataAccess(); DataSet ds2 = da1.GetData(mainTblQry); List <BusLicense> busLicenseList = busLicenseDict[dictEntry.Key]; if (busLicenseList.Count == 1 && ds2.Tables[0].Rows.Count < 2) { if (ds2.Tables[0].Rows.Count < 1) { // There is no rec in main table, so add business license object to records in main and changes tables. addCount = addCount + 1; InsertMainTbl(busLicenseList[0]); InsertChangesTbl(busLicenseList[0], "ADD"); } else { DataRow dr = ds2.Tables[0].Rows[0]; if (busLicenseList[0].license_type == (string)dr["license_type"] && busLicenseList[0].license_classification == (string)dr["license_classification"] && busLicenseList[0].license_category == (string)dr["license_category"] && busLicenseList[0].business_name == (string)dr["business_name"] && busLicenseList[0].contact_name == (string)dr["contact_name"] && busLicenseList[0].account_start_date == (DateTime)dr["account_start_date"] && busLicenseList[0].street_address == (string)dr["street_address"] && busLicenseList[0].city == (string)dr["city"] && busLicenseList[0].state == (string)dr["state"] && busLicenseList[0].zip_code == (string)dr["zip_code"]) { // Do nothing because there is a match. } else { // One or more field do not match, and there is only one business object and only one rec so must be an update. changedRecCount = changedRecCount + 1; deleteMainTbl(dictEntry.Key); InsertMainTbl(busLicenseList[0]); InsertChangesTbl(busLicenseList[0], "UPDATENEW"); BusLicense blTemp = new BusLicense(); blTemp.license_number = (string)dr["license_number"]; blTemp.license_type = (string)dr["license_type"]; blTemp.license_classification = (string)dr["license_classification"]; blTemp.license_category = (string)dr["license_category"]; blTemp.business_name = (string)dr["business_name"]; blTemp.contact_name = (string)dr["contact_name"]; blTemp.account_start_date = (DateTime)dr["account_start_date"]; blTemp.street_address = (string)dr["street_address"]; blTemp.city = (string)dr["city"]; blTemp.state = (string)dr["state"]; blTemp.zip_code = (string)dr["zip_code"]; InsertChangesTbl(blTemp, "UPDATEOLD"); blTemp = null; } } } else // There is either >1 business objects or >1 recs in main table. { foreach (var listEntry in dictEntry.Value) { BusLicense bl = new BusLicense(); matchFound = false; foreach (DataRow dr in ds2.Tables[0].Rows) { bl.license_number = (string)dr["license_number"]; bl.license_type = (string)dr["license_type"]; bl.license_classification = (string)dr["license_classification"]; bl.license_category = (string)dr["license_category"]; bl.business_name = (string)dr["business_name"]; bl.contact_name = (string)dr["contact_name"]; bl.account_start_date = (DateTime)dr["account_start_date"]; bl.street_address = (string)dr["street_address"]; bl.city = (string)dr["city"]; bl.state = (string)dr["state"]; bl.zip_code = (string)dr["zip_code"]; if (bl.license_type == listEntry.license_type && bl.license_classification == listEntry.license_classification && bl.license_category == listEntry.license_category && bl.business_name == listEntry.business_name && bl.account_start_date == listEntry.account_start_date && bl.contact_name == listEntry.contact_name && bl.street_address == listEntry.street_address && bl.city == listEntry.city && bl.state == listEntry.state && bl.zip_code == listEntry.zip_code) { // There is a match, so flip the switch matchFound = true; } else { // This was not a match so continue checking for matches } } if (matchFound == false) // No matching recs found for this business object, so add it to main as ADD { InsertMainTbl(listEntry); InsertChangesTbl(listEntry, "ADD"); } bl = null; } } } // Ends foreach loop for business object dictionary // Loop through the main table in database to see what recs should be deleted. If a rec in main table does not have matching license number // in the dictionary, then delete it from main table and add a rec to changes table as DELETE. string allRecsQry = "SELECT [license_number], [license_type], [license_classification], [license_category], [business_name], [contact_name], " + "[account_start_date], [street_address], [city], [state], [zip_code], [x_coord], [y_coord] " + "FROM [V8].[LRW_V8].[LR_BusinessLicenses] "; BusinessLics_console.DataAccess daAllRecs = new BusinessLics_console.DataAccess(); DataSet dsAllRecs = daAllRecs.GetData(allRecsQry); Boolean keyExists = true; Boolean matchExists = false; BusLicense bl_rec = new BusLicense(); foreach (DataRow dr in dsAllRecs.Tables[0].Rows) { bl_rec.license_number = (string)dr["license_number"]; bl_rec.license_type = (string)dr["license_type"]; bl_rec.license_classification = (string)dr["license_classification"]; bl_rec.license_category = (string)dr["license_category"]; bl_rec.business_name = (string)dr["business_name"]; bl_rec.contact_name = (string)dr["contact_name"]; bl_rec.account_start_date = (DateTime)dr["account_start_date"]; bl_rec.street_address = (string)dr["street_address"]; bl_rec.city = (string)dr["city"]; bl_rec.state = (string)dr["state"]; bl_rec.zip_code = (string)dr["zip_code"]; keyExists = busLicenseDict.ContainsKey((string)dr["license_number"]); if (keyExists == false) { InsertChangesTbl(bl_rec, "DELETE"); deleteMainTbl((string)dr["license_number"]); } else { matchExists = false; foreach (BusLicense blo in busLicenseDict[(string)dr["license_number"]]) { if (blo.license_type == bl_rec.license_type && blo.license_classification == bl_rec.license_classification && blo.license_category == bl_rec.license_category && blo.business_name == bl_rec.business_name && blo.contact_name == bl_rec.contact_name && blo.account_start_date == bl_rec.account_start_date && blo.street_address == bl_rec.street_address && blo.city == bl_rec.city && blo.state == bl_rec.state && blo.zip_code == bl_rec.zip_code) { matchExists = true; } } if (matchExists == false) { deletedRecs = deletedRecs + 1; InsertChangesTbl(bl_rec, "DELETE"); deleteMainTbl2(bl_rec); } } } } log.Debug("Number of business licenses with duplicate entries = " + busLicDupCount.ToString()); log.Debug("Number of records added = " + addCount.ToString()); log.Debug("Number of updated records = " + changedRecCount.ToString()); log.Debug("Number of deleted records = " + deletedRecs.ToString()); log.Debug("program complete"); }