/// <summary> /// For every sitemap XML file, it is stored to the sitemap table onto the database. /// </summary> /// <param name="urlList">The list of sitemap XML files</param> public void SaveToDatabase(List <string> urlList) { if (urlList.Count > 0) { string sql = "insert into sitemap(url, urlHost, urlPath, urlParameters, source, filename, dateProcessed, sitemapType, filter) values(@url, @urlHost, @urlPath, @urlParameters, @source, @filename, @dateProcessed, @sitemapType, @filter)"; string sqlDel = "delete from sitemap where filename = @filename"; string sqlSel = "select count(1) cnt from sitemap with (index(ix_url)) where url = @url"; HandleDatabase hdb = new HandleDatabase(); SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@url"; param1.Value = ""; param1.SqlDbType = SqlDbType.VarChar; SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@urlHost"; param2.Value = ""; param2.SqlDbType = SqlDbType.VarChar; SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@urlPath"; param3.Value = ""; param3.SqlDbType = SqlDbType.VarChar; SqlParameter param4 = new SqlParameter(); param4.ParameterName = "@urlParameters"; param4.Value = ""; param4.SqlDbType = SqlDbType.VarChar; SqlParameter param5 = new SqlParameter(); param5.ParameterName = "@source"; param5.Value = ""; param5.SqlDbType = SqlDbType.VarChar; SqlParameter param6 = new SqlParameter(); param6.ParameterName = "@filename"; param6.Value = ""; param6.SqlDbType = SqlDbType.VarChar; SqlParameter param7 = new SqlParameter(); param7.ParameterName = "@dateProcessed"; param7.Value = DateTime.Now; param7.SqlDbType = SqlDbType.DateTime; SqlParameter param8 = new SqlParameter(); param8.ParameterName = "@sitemapType"; param8.Value = 0; param8.SqlDbType = SqlDbType.Int; SqlParameter param9 = new SqlParameter(); param9.ParameterName = "@filter"; param9.Value = 0; param9.SqlDbType = SqlDbType.VarChar; hdb.Open(); SqlTransaction transaction = hdb.BeginTransaction("sitemap"); //SqlTransaction trnDelete = hdb.BeginTransaction("sitemapDelete"); param6.Value = FileName; hdb.ExecSQLStmt(transaction, sqlDel, param6); //trnDelete.Commit(); foreach (var url in urlList) { Uri uri = new Uri(url); param1.Value = url; param2.Value = uri.Host; param3.Value = uri.LocalPath; param4.Value = uri.Query; param5.Value = Source; param6.Value = FileName; param7.Value = DateTime.Now; param8.Value = SitemapType; param9.Value = Filter; //SqlTransaction trnSel = hdb.BeginTransaction("sitemapSel"); int cnt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sqlSel, param1); //trnSel.Commit(); if (cnt == 0) { hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7, param8, param9); } } transaction.Commit(); hdb.Close(); } }
/// <summary> /// For every sitemap XML file, it is stored to the sitemap table onto the database. /// </summary> /// <param name="urlList">The list of sitemap XML files</param> public void SaveToDatabase(List<string> urlList) { if (urlList.Count > 0) { string sql = "insert into sitemap(url, urlHost, urlPath, urlParameters, source, filename, dateProcessed, sitemapType, filter) values(@url, @urlHost, @urlPath, @urlParameters, @source, @filename, @dateProcessed, @sitemapType, @filter)"; string sqlDel = "delete from sitemap where filename = @filename"; string sqlSel = "select count(1) cnt from sitemap with (index(ix_url)) where url = @url"; HandleDatabase hdb = new HandleDatabase(); SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@url"; param1.Value = ""; param1.SqlDbType = SqlDbType.VarChar; SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@urlHost"; param2.Value = ""; param2.SqlDbType = SqlDbType.VarChar; SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@urlPath"; param3.Value = ""; param3.SqlDbType = SqlDbType.VarChar; SqlParameter param4 = new SqlParameter(); param4.ParameterName = "@urlParameters"; param4.Value = ""; param4.SqlDbType = SqlDbType.VarChar; SqlParameter param5 = new SqlParameter(); param5.ParameterName = "@source"; param5.Value = ""; param5.SqlDbType = SqlDbType.VarChar; SqlParameter param6 = new SqlParameter(); param6.ParameterName = "@filename"; param6.Value = ""; param6.SqlDbType = SqlDbType.VarChar; SqlParameter param7 = new SqlParameter(); param7.ParameterName = "@dateProcessed"; param7.Value = DateTime.Now; param7.SqlDbType = SqlDbType.DateTime; SqlParameter param8 = new SqlParameter(); param8.ParameterName = "@sitemapType"; param8.Value = 0; param8.SqlDbType = SqlDbType.Int; SqlParameter param9 = new SqlParameter(); param9.ParameterName = "@filter"; param9.Value = 0; param9.SqlDbType = SqlDbType.VarChar; hdb.Open(); SqlTransaction transaction = hdb.BeginTransaction("sitemap"); //SqlTransaction trnDelete = hdb.BeginTransaction("sitemapDelete"); param6.Value = FileName; hdb.ExecSQLStmt(transaction, sqlDel, param6); //trnDelete.Commit(); foreach (var url in urlList) { Uri uri = new Uri(url); param1.Value = url; param2.Value = uri.Host; param3.Value = uri.LocalPath; param4.Value = uri.Query; param5.Value = Source; param6.Value = FileName; param7.Value = DateTime.Now; param8.Value = SitemapType; param9.Value = Filter; //SqlTransaction trnSel = hdb.BeginTransaction("sitemapSel"); int cnt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sqlSel, param1); //trnSel.Commit(); if (cnt == 0) { hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7, param8, param9); } } transaction.Commit(); hdb.Close(); } }