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);
}
