/* * Method to return n URLs from DB. */ public static IEnumerable <URL> getURLSFromDB(int n, bool initialLoad) { IEnumerable <URL> myUrlEnumerable = null; String dbConfig = new MyConfigurationHelper().getDBConnectionConfig(); using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){//get connection if (db != null) { if (!initialLoad) { //if not initial load, we need to get new urls in status INITIAL myUrlEnumerable = db.Query <URL>("Select Id, Url, Urltype, Property from URL where status = @status limit @k", new { status = URL.URLStatus.INITIAL, k = n }); } else { //if initial load, we need to get URLs in RUNNING status as well as they were not parseds last time myUrlEnumerable = db.Query <URL>("Select Id, Url, Urltype, Property from URL where status = ANY(@status) limit @k", new { status = new [] { (int)URL.URLStatus.INITIAL, (int)URL.URLStatus.RUNNING }, k = n }); } } } return(myUrlEnumerable); }
/* * This method updates the status of url passed to it to DONE. */ public static void markURLDone(URL url) { String dbConfig = new MyConfigurationHelper().getDBConnectionConfig(); using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){ if (db != null) { db.Execute("update url set status = @status where id=@id", new { status = (int)URL.URLStatus.DONE, id = url.id }); } } }
/* * This method simply merges whatever data is passed to it into DB */ public static void updateURLs(Queue <URL> myUrlQueue) { String dbConfig = new MyConfigurationHelper().getDBConnectionConfig(); using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){ if (db != null) { db.BulkMerge(myUrlQueue); } } }
/* * Method to insert parsed properties into DB */ public static void insertParsedProperties(PropertyData propData) { if (propData == null) { return; } List <PropertyType> propertyTypeList = propData.urlList; if (propertyTypeList != null && propertyTypeList.Count > 0) { String dbConfig = new MyConfigurationHelper().getDBConnectionConfig(); using (IDbConnection db = DBConnectionHelper.getConnection(dbConfig)){ //get connection db.BulkMerge(propertyTypeList) //insert the list of property types .ThenForEach(x => x.properties .ForEach(y => y.propertytype = x.id)) //set property type id for properties .ThenBulkMerge(x => x.properties) //insert properties .ThenForEach(x => x.url.property = x.id) //set property id for urls .ThenBulkMerge(x => x.url); //insert urls } } }
public static IDbConnection getConnection(String location) { if (location.SequenceEqual("aws")) { String secretString = AwsSecretManager.Get("PostgresSecret"); JObject secret = JObject.Parse(secretString); String host = secret.GetValue("host").ToObject(typeof(String)).ToString(); String username = secret.GetValue("username").ToObject(typeof(String)).ToString(); String password = secret.GetValue("password").ToObject(typeof(String)).ToString(); String dbName = secret.GetValue("dbname").ToObject(typeof(String)).ToString(); String connectionString = new StringBuilder() .Append("Host=").Append(host) .Append(";Username="******";Password="******";Database=").Append(dbName).ToString(); return(new NpgsqlConnection(connectionString)); } else { MyConfigurationHelper myConfigurationManager = new MyConfigurationHelper(); return(new SqlConnection(myConfigurationManager.getDBConnectionString())); } }
public static IDbConnection getConnection() { MyConfigurationHelper myConfigurationManager = new MyConfigurationHelper(); return(new NpgsqlConnection(myConfigurationManager.getDBConnectionString())); }