/// <summary>
/// Retrieves all the RSS urls to parse from database store.
/// </summary>
/// <returns>List of Urls to match</returns>
private List<RssUrlInfo> LoadRSSUrls() {
RssUrlInfo info = null;
List<RssUrlInfo> lst = new List<RssUrlInfo>();
HandleDatabase hdb = new HandleDatabase(_connection);
string sqlToUse =
"select orden, codObjetoSe, texto AS rss " +
"from objetosecontenido ,objetose " +
"where id_objetose = codobjetose AND nomobjetose = 'EC-Bloguer' AND atributo = 'rutaRssBlog' " +
"order by orden ";
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("loadRssUrls");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sqlToUse);
while (rdr.Read()) {
info = new RssUrlInfo();
string url = rdr["rss"].ToString();
if (url != null && url != "") {
info.Url = url;
info.Order = Convert.ToInt32(rdr["orden"].ToString());
info.ObjectSECode = Convert.ToInt32(rdr["codObjetoSe"].ToString());
lst.Add(info);
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
return lst;
}
/// <summary>
/// Puts in database all of collected information about RSS feeds.
/// </summary>
/// <param name="items">Information to process</param>
private void SaveToDb(List<RssUrlInfo> items) {
string sqlIns = "INSERT INTO publicacionBlog (orden, codobjetose, rutaRSS, titulo, link, linkcomentario, fechapublica, creador, descripcion, totalcomentarios, UltProcesado) VALUES " +
"(@orden, @objectsecode, @url, @titulo, @link, @linkcomentario, @fechapublica, @creador, @descripcion, @totalcomentarios, @ultprocesado)";
string sqlUpd = "update publicacionBlog " +
"set orden = @orden, " +
" titulo = @titulo, " +
" link = @link," +
" linkcomentario = @linkcomentario, " +
" fechapublica = @fechapublica, " +
" creador = @creador, " +
" descripcion = @descripcion, " +
" totalcomentarios = @totalcomentarios, " +
" UltProcesado = @ultprocesado " +
"where rutarss = @url and codobjetose = @objectsecode";
string sqlSel = "select count(*) as exist from publicacionblog where rutarss = @url and codobjetose = @objectsecode";
HandleDatabase hdb = new HandleDatabase(_connection);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("savetodb");
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
SqlParameter param4 = new SqlParameter();
SqlParameter param5 = new SqlParameter();
SqlParameter param6 = new SqlParameter();
SqlParameter param7 = new SqlParameter();
SqlParameter param8 = new SqlParameter();
SqlParameter param9 = new SqlParameter();
SqlParameter param10 = new SqlParameter();
SqlParameter param11 = new SqlParameter();
try {
foreach (var item in items) {
string s = "";
if (item.DocItemList == null) {
Console.WriteLine("RSS Not generated for [" + item.Url + "]");
continue;
}
// Let's find out if record already exists.
param1.ParameterName = "@url";
param1.Value = item.Url;
param1.SqlDbType = SqlDbType.VarChar;
param2.ParameterName = "@objectsecode";
param2.Value = item.ObjectSECode;
param2.SqlDbType = SqlDbType.Int;
param3.ParameterName = "@orden";
param3.Value = item.Order;
param3.SqlDbType = SqlDbType.Int;
param4.ParameterName = "@titulo";
s = item.DocItemList[0].Title;
if (s.Length > 253) {
s = s.Substring(1, 253);
}
param4.Value = s;
param4.SqlDbType = SqlDbType.VarChar;
param5.ParameterName = "@link";
param5.Value = item.DocItemList[0].Link;
param5.SqlDbType = SqlDbType.VarChar;
param6.ParameterName = "@linkcomentario";
param6.Value = item.DocItemList[0].LinkComments;
param6.SqlDbType = SqlDbType.VarChar;
param7.ParameterName = "@fechapublica";
param7.Value = item.DocItemList[0].PublishDate;
param7.SqlDbType = SqlDbType.DateTime;
param8.ParameterName = "@creador";
param8.Value = item.DocItemList[0].Creator;
param8.SqlDbType = SqlDbType.VarChar;
param9.ParameterName = "@descripcion";
s = item.DocItemList[0].Content;
if (s.Length > 498) {
s = s.Substring(1, 498);
}
param9.Value = s;
param9.SqlDbType = SqlDbType.VarChar;
param10.ParameterName = "@totalComentarios";
param10.Value = item.DocItemList[0].NumComments;
param10.SqlDbType = SqlDbType.Int;
param11.ParameterName = "@UltProcesado";
param11.Value = DateTime.Now;
param11.SqlDbType = SqlDbType.DateTime;
int cnt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sqlSel, param1, param2);
if (cnt == 0) {
// Create new record
hdb.ExecSQLStmt(transaction, sqlIns, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11);
}
else {
// Update existing record
hdb.ExecSQLStmt(transaction, sqlUpd, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11);
}
}
}
catch (Exception) {
transaction.Rollback();
throw;
}
finally {
transaction.Commit();
}
hdb.Close();
}