/// <summary>
/// Load directory into temporary table in order to check if new files exist
/// (files are sync'ed in database.
/// </summary>
private void LoadToonsIntoTemp() {
String folderPath = _se4MediaSourceFolder + @"\Caricaturas2";
if (log.IsDebugEnabled) {
log.Debug("LoadToonsIntoTemp Start");
}
string sql = "insert into toondirtmp(fromdir, filename, dateProcessed, CreateDate, UpdateDate, processed, idArticle) values(@fromdir, @filename, @dateProcessed, @CreateDate, @UpdateDate, @processed, @idArticle)";
var files = from file in Directory.GetFiles(folderPath, "caric*_G.jpg")
orderby file descending
select file;
long sequence = 6000000L;
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@fromdir";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@filename";
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@dateProcessed";
param3.SqlDbType = SqlDbType.DateTime;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@CreateDate";
param4.SqlDbType = SqlDbType.DateTime;
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@UpdateDate";
param5.SqlDbType = SqlDbType.DateTime;
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@processed";
param6.SqlDbType = SqlDbType.Int;
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@idArticle";
param7.SqlDbType = SqlDbType.VarChar;
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("LoadToonsIntoTemp");
hdb.ExecSQLStmt(transaction, "delete from toondirtmp");
foreach (var file in files) {
param1.Value = folderPath;
param2.Value = Path.GetFileName(file);
param3.Value = DateTime.Now;
param4.Value = param5.Value = File.GetLastWriteTime(file);
param6.Value = 0;
param7.Value = "EC_" + sequence++;
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3, param4, param5, param6, param7);
}
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("LoadToonsIntoTemp End");
}
}
/// <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>
/// Used to generate value for field UrlTitle in sitemap table.
/// </summary>
private void ComputeUrlTitle() {
if (log.IsDebugEnabled) {
log.Debug("ComputeUrlTitle Start");
}
long counter = 0;
string line = "ComputeUrlTitle Init";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
Dictionary<long, String> urlTitleList = new Dictionary<long, String>();
string sql = "select idSitemap, urlPath from sitemap where urlTitle is null";
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ComputeUrlTitle");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read()) {
counter++;
Console.Write(counter + "\r");
var urlPath = rdr["urlPath"].ToString();
var id = Convert.ToInt32(rdr["idSitemap"].ToString());
if (urlPath != "") {
urlPath = urlPath.ExtractUrlTitle();
urlTitleList.Add(id, urlPath);
}
}
Console.WriteLine();
rdr.Close();
transaction.Commit();
line = "ComputeUrlTitle Updating database";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
long counter1 = 0;
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@urltitle";
param1.SqlDbType = SqlDbType.VarChar;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@id";
param2.SqlDbType = SqlDbType.Int;
transaction = hdb.BeginTransaction("ComputeUrlTitleUpdate");
foreach (var pair in urlTitleList) {
counter1++;
Console.Write(counter1 + "\r");
sql = "update sitemap set urltitle = @urltitle where idsitemap = @id";
param1.Value = pair.Value;
param2.Value = pair.Key;
hdb.ExecSQLStmt(transaction, sql, param1, param2);
}
transaction.Commit();
hdb.Close();
Console.WriteLine();
line = "ComputeUrlTitle processed " + counter + " items";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
if (log.IsDebugEnabled) {
log.Debug("ComputeUrlTitle End");
}
}
/// <summary>
/// Check if table 'toondirtmp' has newer records to insert into 'toondir'
/// </summary>
private void SyncToonInfo() {
string sql = "";
if (log.IsDebugEnabled) {
log.Debug("SyncToonInfo Start");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SyncToonInfo");
sql += "insert into toondir(idArticle, fromdir, filename, dateprocessed, createdate, updatedate, processed) ";
sql += "select idArticle, fromdir, filename, dateprocessed, createdate, updatedate, processed ";
sql += "from toondirtmp a ";
sql += "where filename not in (select filename from toondir) ";
hdb.ExecSQLStmt(transaction, sql);
sql = "update toondir set idArticle = 'EC_' + cast((id + 6000000) as varchar(255))";
hdb.ExecSQLStmt(transaction, sql);
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("SyncToonInfo End");
}
}
/// <summary>
/// Gets all useful records and validates that their URLs don't contain non-ascii characters, if one is found
/// it is reported to both screen and log.
/// </summary>
private void ValidateURLChars() {
if (log.IsDebugEnabled) {
log.Debug("ValidateURLChars Start");
}
long numInvalidSE4Docs = 0;
long numInvalidOldDocs = 0;
string msg = "";
string loadOldDocuments = "";
string loadSE4Documents = "";
string se4LayoutList = _se4LayoutToFilter;
string layoutFilter = "";
loadOldDocuments = "select idSitemap, url, urlPath from sitemap " +
"where idOld is not null and idSE4 is null and oldDocStatus = 2 and url is not null";
loadSE4Documents = "select idSitemap, url, urlPath from sitemap " +
"where idSE4 is not null and url is not null @layoutfilter@ and urlParameters = '' and processed = 0 ";
if (se4LayoutList == "") {
layoutFilter = "";
}
else {
layoutFilter = "and layout in (@se4layoutlist@) ";
layoutFilter = layoutFilter.Replace("@se4layoutlist@", se4LayoutList);
}
loadSE4Documents = loadSE4Documents.Replace("@layoutfilter@", layoutFilter);
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("DeleteInvalidUrls");
hdb.ExecSQLStmt(transaction, "delete from invalidurls");
transaction.Commit();
transaction = hdb.BeginTransaction("ValidateURLChars");
// 1. Loads valid SE4Documents to validate their URLs
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, loadSE4Documents);
numInvalidSE4Docs = 0;
while (rdr.Read()) {
string url = rdr["url"].ToString();
string urlPath = rdr["urlPath"].ToString();
long idSitemap = Convert.ToInt32(rdr["idSitemap"].ToString());
if (!url.IsUrlWithValidChars()) {
msg = "SE4Doc Url at idSitemap=[" + idSitemap + "] is not ASCII conformant. URL=[" + url + "]";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
numInvalidSE4Docs++;
SaveToInvalidUrlTable(idSitemap, url, urlPath, 1);
}
}
rdr.Close();
// 2. Loads Old documents to validate their URLs.
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, loadOldDocuments);
numInvalidOldDocs = 0;
while (rdr.Read()) {
string url = rdr["url"].ToString();
string urlPath = rdr["urlPath"].ToString();
long idSitemap = Convert.ToInt32(rdr["idSitemap"].ToString());
if (!url.IsUrlWithValidChars()) {
msg = "Old DOC Url at idSitemap=[" + idSitemap + "] is not ASCII conformant. URL=[" + url + "]";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
numInvalidOldDocs++;
SaveToInvalidUrlTable(idSitemap, url, urlPath, 2);
}
}
msg = "There are [" + numInvalidSE4Docs + "] invalid URls in SE4 Docs";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
msg = "There are [" + numInvalidOldDocs + "] invalid URls in Old Docs";
Console.WriteLine(msg);
if (log.IsInfoEnabled) {
log.Info(msg);
}
rdr.Close();
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("ValidateURLChars End");
}
}
/// <summary>
/// Invokes sp 'sp_syncmigrationdb' to update index table used as the migration-processing-control data.
/// </summary>
private void SynchronizeDBData() {
if (log.IsDebugEnabled) {
log.Debug("SynchronizeDBData Start");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SynchronizeDBData");
hdb.ExecSQLStmt(transaction, "execute sp_syncmigrationdb");
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("SynchronizeDBData End");
}
}
/// <summary>
/// Exec an SQL statement in batch.
/// NOTE: Reads a file from 'SQLBatchFile' configuration parameter and assumes a true SQL statement
/// (which ends with semicolon) is read and executed accordingly and Batch file must be UTF-8 encoded.
/// SQL must end with semi-colon. SQL msut not contain any commentary in it.
/// </summary>
private void ExecuteSqlStmtBatch() {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch Start");
}
String[] readSQLStmts = null;
String line = "";
line = "Executing SQL Commands from file [" + _sqlBatchFile + "]";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
if (!File.Exists(_sqlBatchFile)) {
line = "Batch file [" + _sqlBatchFile + "] does not exist";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
}
}
else {
readSQLStmts = File.ReadAllLines(_sqlBatchFile, Encoding.UTF8);
if (readSQLStmts.Length == 0) {
line = "SQL Batch File is empty";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
}
}
else {
line = "Executing statements";
if (log.IsInfoEnabled) {
log.Info(line);
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmtBatch");
try {
bool trueSqlStmt = false;
String sqlExec = "";
foreach (string sql in readSQLStmts) {
if (sql != "") {
if (sql.Contains(';')) {
trueSqlStmt = true;
}
sqlExec += sql + " ";
if (trueSqlStmt) {
if (log.IsWarnEnabled) {
log.Warn("Executing SQL=[" + sqlExec + "]");
}
hdb.ExecSQLStmt(transaction, sqlExec);
trueSqlStmt = false;
sqlExec = "";
}
}
}
transaction.Commit();
}
catch (Exception e) {
Console.WriteLine("Exception caught (ExecuteSqlStmtBatch). See log for details");
line = "Processing aborted by Exception raised ";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
log.Error(e.Message);
log.Error(e.StackTrace);
}
transaction.Rollback();
}
hdb.Close();
}
}
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch End");
}
}
/// <summary>
/// Stores in 'InvalidUrls' database table as reference.
/// </summary>
/// <param name="idSitemap">Sitemap record to use as reference</param>
/// <param name="url">Invalid url</param>
/// <param name="p">kind of record, 1: SE4 doc, 2: Old doc</param>
private void SaveToInvalidUrlTable(long idSitemap, string url, string urlPath, int p) {
if (log.IsDebugEnabled) log.Debug("SaveToInvalidUrlTable Start");
string sqlToUse = "insert into invalidurls(id, url, urlencoded, kind) values (@sitemap, @url, @urlencoded, @kind) ";
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@sitemap";
param1.Value = idSitemap;
param1.SqlDbType = SqlDbType.Int;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@url";
param2.Value = url;
param2.SqlDbType = SqlDbType.VarChar;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@kind";
param3.Value = p;
param3.SqlDbType = SqlDbType.Int;
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@urlencoded";
param4.Value = HttpUtility.UrlEncode(urlPath);
param4.SqlDbType = SqlDbType.VarChar;
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SaveToInvalidUrlTable");
hdb.ExecSQLStmt(transaction, sqlToUse, param1, param2, param3, param4);
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) log.Debug("SaveToInvalidUrlTable End");
}
/// <summary>
/// Exec an SQL statement in batch supplied by sql parameter. SQL must end with semi-colon. SQL msut not contain any commentary in it.
/// </summary>
/// <param name="sqlBatchProvided">In memory SQL to act upon.</param>
private void ExecuteSqlStmtBatch(string sqlBatchSupplied) {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch(string) Start");
}
String[] readSQLStmts = null;
String line = "";
line = "Executing SQL Commands from [" + sqlBatchSupplied + "]";
Console.WriteLine(line);
if (log.IsInfoEnabled) {
log.Info(line);
}
line = "Executing statements";
if (log.IsInfoEnabled) {
log.Info(line);
}
readSQLStmts = sqlBatchSupplied.Split(';');
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmtBatch1");
try {
String sqlExec = "";
foreach (string sql in readSQLStmts) {
if (sql != "") {
sqlExec = sql;
if (log.IsWarnEnabled) {
log.Warn("Executing SQL=[" + sqlExec + "]");
}
hdb.ExecSQLStmt(transaction, sqlExec);
sqlExec = "";
}
}
transaction.Commit();
}
catch (Exception e) {
Console.WriteLine("Exception caught (ExecuteSqlStmtBatch). See log for details");
line = "Processing aborted by Exception raised ";
Console.WriteLine(line);
if (log.IsErrorEnabled) {
log.Error(line);
log.Error(e.Message);
log.Error(e.StackTrace);
}
transaction.Rollback();
}
hdb.Close();
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmtBatch(string) End");
}
}
/// <summary>
/// Exec an SQL statement.
/// </summary>
private void ExecuteSqlStmt() {
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmt Start");
}
if (_sqlExecStmt != "") {
if (log.IsWarnEnabled) {
log.Warn("Using SQL=[" + _sqlExecStmt + "]");
}
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("ExecuteSqlStmt");
hdb.ExecSQLStmt(transaction, _sqlExecStmt);
transaction.Commit();
hdb.Close();
}
else {
var s = "No SQL statement to execute";
Console.WriteLine(s);
if (log.IsInfoEnabled) {
log.Info(s);
}
}
if (log.IsDebugEnabled) {
log.Debug("ExecuteSqlStmt End");
}
}
/// <summary>
/// Marks records in list as being processed. Further iterations should not use that record again.
/// </summary>
/// <param name="processedIds">List of Documents to update.</param>
/// <param name="zipFileName">Indicates all these 'processedIds' are stored in this zip file.</param>
private void MarkToonDirRecordsAsProcessed(List<ToonDirInfo> processedIds, string zipFileName) {
if (log.IsDebugEnabled) log.Debug("MarkToonDirRecordsAsProcessed start");
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("MarkToonDirRecordsAsProcessed");
String sql = "update toondir set processed = @processed, zipFilename = @zipFileName where id = @id";
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
param1.ParameterName = "@id";
param1.SqlDbType = SqlDbType.Int;
param2.ParameterName = "@zipFileName";
param2.SqlDbType = SqlDbType.VarChar;
param3.ParameterName = "@processed";
param3.SqlDbType = SqlDbType.Int;
foreach (var it in processedIds) {
param1.Value = it.Id;
param2.Value = zipFileName;
param3.Value = (it.Processed == 0 ? 1 : it.Processed);
hdb.ExecSQLStmt(transaction, sql, param1, param2, param3);
}
transaction.Commit();
hdb.Close();
if (log.IsDebugEnabled) log.Debug("MarkToonDirRecordsAsProcessed end");
}
/// <summary>
/// Updates old document Template Type based in the jSonContent.
/// NOTE: Used only once.
/// </summary>
protected void OldDocUpdateTemplateType() {
HandleDatabase hdb = new HandleDatabase(_sitemapConnStr);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("SiteMapUpdate");
foreach (var item in _se4DocList) {
Doc examinedDoc = JsonConvert.DeserializeObject<Doc>(item.JsonContent);
if (examinedDoc != null) {
int templateType = examinedDoc.TemplateType;
string sql = "Update sitemap set oldDocTemplateType = @templateType where idOld = @id";
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@templateType";
param4.Value = examinedDoc.TemplateType;
param4.SqlDbType = SqlDbType.Int;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@id";
param3.Value = item.IDOldDoc;
param3.SqlDbType = SqlDbType.VarChar;
hdb.ExecSQLStmt(transaction, sql, param3, param4);
}
}
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();
}
}
