public void ContactsAddedBetweenDateRange_ReturnTwo_ForGivenDateRange() { //Act List <DataRow> data = SqlServerOperation.ContactsAddedBetweenDateRange(new DateTime(2020, 11, 01), DateTime.Today); //Assert Assert.AreEqual(2, data.Count); }
/// <summary> /// Gets a <see cref="DbCommand" /> instance for executing a stored procedure. /// </summary> /// <param name="operation"> /// The operation that is performed by the stored procedure. /// </param> /// <param name="parameters"> /// The name of the parameters that are required by the stored procedure. /// </param> /// <returns> /// A new <see cref="DbCommand" /> instance, pre-initialised for executing a stored procedure that performs the specified <paramref name="operation" />. /// </returns> protected DbCommand GetStoredProcedure(SqlServerOperation operation, params String[] parameters) { String procedureNameSuffix = (parameters.Length == 0 ? String.Empty : "By" + String.Join("And", parameters)); String procedureNameFormat = SqlServerRepository.StoredProcedureNamingConvention.Replace("{typeNamespace}", "{0}").Replace("{typeName}", "{1}").Replace("{operation}", "{2}").Replace("{parameters}", "{3}"); String procedureName = String.Format(procedureNameFormat, this.TypeNamespace, this.TypeName, operation, procedureNameSuffix); return(this.Provider.GetStoredProcedure(procedureName)); }
public void CountByState_ReturnOne_ForGivenState() { //Arrange string state = "Ods"; //Act Dictionary <string, int> countByState = SqlServerOperation.CountByState(); //Assert Assert.AreEqual(1, countByState[state]); }
public void CountByCity_ReturnOne_ForGivenCity() { //Arrange string city = "Ranchi"; //Act Dictionary <string, int> countByCity = SqlServerOperation.CountByCity(); //Assert Assert.AreEqual(1, countByCity[city]); }
public void ReadFromSqlServer_ShouldReturnAllFiles_AsAddressBookObject() { //Arrange AddressBooks addressBooks = new AddressBooks(); //Act SqlServerOperation.ReadFromSqlServer(ref addressBooks); //Assert Assert.AreEqual(2, addressBooks._multiAddressBooks["General"].AddressBook.Count); }
private void Form1_Load(object sender, EventArgs e) { SqlServerOperation operation = SqlServerOperation.Instance; dbWithTables = operation.GetDataSets(); dataSets.DataSource = dbWithTables.Keys.ToList(); dataSets.SelectedIndex = 0; tblName.DataSource = dbWithTables[dataSets.SelectedItem.ToString()]; tblName.SelectedIndex = 0; }
private void btnScrapp_Click(object sender, EventArgs e) { if (productInfoList.Count == 0) { MessageBox.Show("Selected Sheet is empty"); return; } SqlServerOperation operation = SqlServerOperation.Instance; operation.SetDataSets(dataSets.SelectedItem.ToString(), tblName.SelectedItem.ToString()); bool updateFlag = updCheck.Checked; Browser browser = new Browser(Browser.Type.CHROME, false); Puller dataPuller = new Puller(); foreach (var info in productInfoList) { try { if (!templates.ContainsKey(info.Template)) { if (!ReadTemplate(info.Template)) { Log.Exception("Scrapping failed for " + info.Url); continue; } } DataModel dataModel = templates[info.Template]; if (staticRb.Checked) { StaticSite target = new StaticSite(browser, dataModel.name, info.Url, dataModel, 1, info.LastPageNo, info.Name, dataModel.nextPageSelectorElement, int.Parse(siteLoad.Text), int.Parse(pageLoad.Text), dataModel.siteActions, dataModel.pageActions); dynamic sitena = dataPuller.PullData(target, updateFlag); } else { DynamicSite target = new DynamicSite(browser, dataModel.name, info.Url, dataModel, info.Url, urlSplitterTb.Text, int.Parse(incTb.Text), info.Name, int.Parse(siteLoad.Text), int.Parse(pageLoad.Text), dataModel.siteActions, dataModel.pageActions, 1, info.LastPageNo); dynamic sitena = dataPuller.PullData(target, updateFlag); } } catch (Exception ex) { Log.Exception("Scrapping failed for " + info.Url); } } MessageBox.Show("Scrapping has been completed !!"); }
private void updSchema_Click(object sender, EventArgs e) { var dbName = dataSets.SelectedItem; var tbl = tblName.SelectedItem; SqlServerOperation operation = SqlServerOperation.Instance; operation.SetDataSets(dbName.ToString(), tbl.ToString()); if (siteDataModel == null) { GetDataFromControls(); } if (operation.CreateTable(siteDataModel)) { MessageBox.Show("Schema Updated"); } }
/// <summary> /// Read contact details from XML/ CSV / JSON file /// and store in AddressBooks class instance /// </summary> /// <param name="addressBooksCollection">reference to addressbooks instance to store contact details</param> private static void FileReadingOperation(ref AddressBooks addressBooksCollection) { LogDetails logDetails = new LogDetails(); try { //IoOperations.DeserializeAddressBooks(ref addressBooksCollection); //CsvOperations.ReadFromCsv(ref addressBooksCollection); //JsonOperation.ReadFromJson(ref addressBooksCollection); SqlServerOperation.ReadFromSqlServer(ref addressBooksCollection); } catch (Exception e) { logDetails.LogDebug("IO Error in Reading operation"); logDetails.LogError(e.Message); } }
public void CVCrawler() { SqlServerOperation SqlOperation = new SqlServerOperation(); while (true) { DataTable dt_sa = new DataTable(); initset(dt_sa); string query = "select top 50 * from careers where flag = 0"; DataTable dt = new DataTable(); dt = SqlServerOperation.GetDataTable(query); int idbegin = -1, id = 1; foreach (DataRow row in dt.Rows) { if (idbegin == -1) { idbegin = Convert.ToInt32(row[0]); } id = Convert.ToInt32(row[0]); string name = row[1].ToString(); name = name.Trim(); string link = row[3].ToString(); link = link.Trim(); try { DataRow r = dt_sa.NewRow(); r["SOFID"] = -1; r["Name"] = name; r["Url"] = link; Console.WriteLine(id + name); work(id, name, link, dt_sa, r); } catch (Exception e) { Output write = new Output(); write.writetofile(e); //throw; } } SqlServerOperation.ExecuteSqlBulkCopy(dt_sa, "dbo.social_account"); string temp2 = "update careers set flag = 1 where SOFID <= " + id + " and SOFID >= " + idbegin; SqlServerOperation.ExecuteNonQuery(temp2); } }
public static void LinqOperations() { //1 List <DataRow> data = SqlServerOperation.ContactsAddedBetweenDateRange(new DateTime(2020, 11, 01), DateTime.Today); Console.WriteLine("Contacts added between 01/11/2020 and Today "); foreach (DataRow row in data) { Console.WriteLine(row.Field <string>("ContactName")); } Console.WriteLine(); //2 Dictionary <string, int> countByCity = SqlServerOperation.CountByCity(); Console.WriteLine("City\tCount"); foreach (var item in countByCity) { Console.WriteLine(item.Key + "\t" + item.Value); } Console.WriteLine(); //3 Dictionary <string, int> countByState = SqlServerOperation.CountByState(); Console.WriteLine("State\tCount"); foreach (var item in countByState) { Console.WriteLine(item.Key + "\t" + item.Value); } Console.WriteLine(); //4 //ContactDetails contact = new ContactDetails("Shyam", "Kumar", "Barauni", "Patna", "710923", "9309165207", "*****@*****.**"); //SqlServerOperation.AddContactDetail("College", "ShyamBmep", contact); //5 SqlServerOperation.WriteFromSqlServerToJson(); }
internal virtual dynamic GetData(bool updateFlag) { SqlServerOperation sqlOp = SqlServerOperation.Instance; int threshold = int.Parse(ConfigurationManager.AppSettings["DBThreshold"]); data = new List <dynamic>(); // navigate to site url browser.NavigateTo(url); Thread.Sleep(SiteTimeout * 1000); // process all actions on site main url ProcessActions(siteActionList, PageTimeout); if (lastPage == -1) { lastPage = GetLastPageFromSiteAction(); } for (int pageNo = firstPage; pageNo <= lastPage; pageNo++) { // wait between navigation //Thread.Sleep(Helper.GetNavigationWait()); // process actions on page dynamic pageData = this.GetPageData(pageNo); if (pageData != null) { // add page data count to site data count dataCount += pageData.Count; // add page data to site data data.Add(pageData); //for updating scrapped data if (updateFlag) { if (!sqlOp.IsAllAreLatest(pageData, url)) { sqlOp.RemoveInvalid(pageData, url); sqlOp.SaveData(data, dataModel, url, productName); data.Clear(); break; } } //if data exceeds from threshold then store in DB else if (pageNo % threshold == 0) { sqlOp.SaveData(data, dataModel, url, productName); data.Clear(); } } else { Console.WriteLine("No Data Found on Site:{0} \n PageNumber:{1}", name, pageNo); string customMessage = string.Format("Site:{0} \n PageNumber:{1} \n Method:{2} \n ", name, pageNo, "GetData returned Empty Parent Elements"); Log.Exception(customMessage); } Console.WriteLine("Page:" + pageNo); ProcessActions(pageActionList, PageTimeout); } if (data.Count != 0) { sqlOp.SaveData(data, dataModel, url, productName); data.Clear(); } // return null if no data fetched if (dataCount == 0) { Console.WriteLine("No Data Found on Site:{0}", name); return(null); } return(data); }