You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
892 lines
28 KiB
892 lines
28 KiB
using Microsoft.SqlServer.Management.Smo;
|
|
using Swift.DAL.Common;
|
|
using System;
|
|
using System.Collections;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Text;
|
|
using System.Web;
|
|
|
|
namespace Swift.DAL.SwiftDAL
|
|
{
|
|
public class RemittanceDao
|
|
{
|
|
private SqlConnection _connection;
|
|
|
|
public RemittanceDao()
|
|
{
|
|
Init();
|
|
}
|
|
|
|
private void Init()
|
|
{
|
|
_connection = new SqlConnection(GetConnectionString());
|
|
}
|
|
|
|
private void OpenConnection()
|
|
{
|
|
if (_connection.State == ConnectionState.Open)
|
|
_connection.Close();
|
|
_connection.Open();
|
|
}
|
|
|
|
private void CloseConnection()
|
|
{
|
|
if (_connection.State == ConnectionState.Open)
|
|
this._connection.Close();
|
|
}
|
|
|
|
private string GetConnectionString()
|
|
{
|
|
return ConfigurationSettings.AppSettings["RemittanceString"].ToString();
|
|
}
|
|
|
|
public DataSet ExecuteDataset(string sql)
|
|
{
|
|
var ds = new DataSet();
|
|
SqlDataAdapter da;
|
|
|
|
try
|
|
{
|
|
OpenConnection();
|
|
da = new SqlDataAdapter(sql, _connection);
|
|
da.SelectCommand.CommandTimeout = GetCommandTimeOut();
|
|
|
|
da.Fill(ds);
|
|
da.Dispose();
|
|
CloseConnection();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
da = null;
|
|
CloseConnection();
|
|
}
|
|
return ds;
|
|
}
|
|
|
|
public DataTable ExecuteDataTable(string sql)
|
|
{
|
|
using (var ds = ExecuteDataset(sql))
|
|
{
|
|
if (ds == null || ds.Tables.Count == 0)
|
|
return null;
|
|
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
|
|
public DataRow ExecuteDataRow(string sql)
|
|
{
|
|
using (var ds = ExecuteDataset(sql))
|
|
{
|
|
if (ds == null || ds.Tables.Count == 0)
|
|
return null;
|
|
|
|
if (ds.Tables[0].Rows.Count == 0)
|
|
return null;
|
|
|
|
return ds.Tables[0].Rows[0];
|
|
}
|
|
}
|
|
|
|
public String GetSingleResult(string sql)
|
|
{
|
|
try
|
|
{
|
|
var ds = ExecuteDataset(sql);
|
|
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
|
return "";
|
|
|
|
return ds.Tables[0].Rows[0][0].ToString();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
CloseConnection();
|
|
}
|
|
}
|
|
|
|
public String FilterStringForXml(string strVal)
|
|
{
|
|
var str = FilterQuote(strVal);
|
|
|
|
if (str.ToLower() == "null")
|
|
str = "";
|
|
|
|
str = "'" + str + "'";
|
|
|
|
return str;
|
|
}
|
|
|
|
public String FilterString(string strVal)
|
|
{
|
|
var str = FilterQuote(strVal);
|
|
|
|
if (str.ToLower() != "null")
|
|
str = "'" + str + "'";
|
|
|
|
return str;
|
|
}
|
|
|
|
public String FilterAmpersand(string strVal)
|
|
{
|
|
if (string.IsNullOrEmpty(strVal))
|
|
{
|
|
strVal = "";
|
|
}
|
|
var str = strVal/*.Trim()*/;
|
|
|
|
if (!string.IsNullOrEmpty(str))
|
|
{
|
|
str = str.Replace("&", "&");
|
|
}
|
|
return str;
|
|
}
|
|
|
|
|
|
public String FilterStringUnicode(string strVal)
|
|
{
|
|
var str = FilterQuote(strVal);
|
|
|
|
if (str.ToLower() != "null")
|
|
str = "N'" + str + "'";
|
|
|
|
return str;
|
|
}
|
|
|
|
public String FilterQuoteNative(string strVal)
|
|
{
|
|
if (string.IsNullOrEmpty(strVal))
|
|
{
|
|
strVal = "";
|
|
}
|
|
var str = Encode(strVal.Trim());
|
|
|
|
if (!string.IsNullOrEmpty(str))
|
|
{
|
|
str = str.Replace(";", "");
|
|
//str = str.Replace(",", "");
|
|
str = str.Replace("--", "");
|
|
str = str.Replace("'", "");
|
|
|
|
str = str.Replace("/*", "");
|
|
str = str.Replace("*/", "");
|
|
|
|
str = str.Replace(" select ", "");
|
|
str = str.Replace(" insert ", "");
|
|
str = str.Replace(" update ", "");
|
|
str = str.Replace(" delete ", "");
|
|
|
|
str = str.Replace(" drop ", "");
|
|
str = str.Replace(" truncate ", "");
|
|
str = str.Replace(" create ", "");
|
|
|
|
str = str.Replace(" begin ", "");
|
|
str = str.Replace(" end ", "");
|
|
str = str.Replace(" char(", "");
|
|
|
|
str = str.Replace(" exec ", "");
|
|
str = str.Replace(" xp_cmd ", "");
|
|
|
|
str = str.Replace("<script", "");
|
|
}
|
|
else
|
|
{
|
|
str = "null";
|
|
}
|
|
return str;
|
|
}
|
|
|
|
private string Encode(string strVal)
|
|
{
|
|
var sb = new StringBuilder(HttpUtility.HtmlEncode(HttpUtility.JavaScriptStringEncode(strVal)));
|
|
// Selectively allow <b> and <i>
|
|
sb.Replace("<b>", "<b>");
|
|
sb.Replace("</b>", "");
|
|
sb.Replace("<i>", "<i>");
|
|
sb.Replace("</i>", "");
|
|
return sb.ToString();
|
|
}
|
|
|
|
public String FilterStringNativeTrim(string strVal)
|
|
{
|
|
var str = FilterQuoteNative(strVal);
|
|
|
|
if (str.ToLower() != "null")
|
|
str = "'" + str + "'";
|
|
else
|
|
str = "";
|
|
|
|
return str;
|
|
}
|
|
|
|
public String FilterStringNative(string strVal)
|
|
{
|
|
var str = FilterQuoteNative(strVal);
|
|
|
|
if (str.ToLower() != "null")
|
|
str = "'" + str + "'";
|
|
|
|
return str;
|
|
}
|
|
|
|
public string SingleQuoteToDoubleQuote(string strVal)
|
|
{
|
|
strVal = strVal.Replace("\"", "");
|
|
return strVal.Replace("'", "\"");
|
|
}
|
|
|
|
public String FilterQuote(string strVal)
|
|
{
|
|
if (string.IsNullOrEmpty(strVal))
|
|
{
|
|
strVal = "";
|
|
}
|
|
var str = strVal/*.Trim()*/;
|
|
|
|
if (!string.IsNullOrEmpty(str))
|
|
{
|
|
str = str.Replace(";", "");
|
|
//str = str.Replace(",", "");
|
|
str = str.Replace("--", "");
|
|
str = str.Replace("'", "");
|
|
|
|
str = str.Replace("/*", "");
|
|
str = str.Replace("*/", "");
|
|
|
|
str = str.Replace(" select ", "");
|
|
str = str.Replace(" insert ", "");
|
|
str = str.Replace(" update ", "");
|
|
str = str.Replace(" delete ", "");
|
|
|
|
str = str.Replace(" drop ", "");
|
|
str = str.Replace(" truncate ", "");
|
|
str = str.Replace(" create ", "");
|
|
|
|
str = str.Replace(" begin ", "");
|
|
str = str.Replace(" end ", "");
|
|
str = str.Replace(" char(", "");
|
|
|
|
str = str.Replace(" exec ", "");
|
|
str = str.Replace(" xp_cmd ", "");
|
|
|
|
str = str.Replace("select ", "");
|
|
str = str.Replace("insert ", "");
|
|
str = str.Replace("update ", "");
|
|
str = str.Replace("delete ", "");
|
|
|
|
str = str.Replace("drop ", "");
|
|
str = str.Replace("truncate ", "");
|
|
str = str.Replace("create ", "");
|
|
|
|
str = str.Replace("begin ", "");
|
|
str = str.Replace("end ", "");
|
|
str = str.Replace("char(", "");
|
|
|
|
str = str.Replace("exec ", "");
|
|
str = str.Replace("xp_cmd ", "");
|
|
|
|
str = str.Replace("onmouseover ", "");
|
|
str = str.Replace("autofocus ", "");
|
|
str = str.Replace("onfocus ", "");
|
|
str = str.Replace("onerror ", "");
|
|
str = str.Replace("onload ", "");
|
|
|
|
str = str.Replace("<script", "");
|
|
str = str.Replace("</script>", "");
|
|
}
|
|
else
|
|
{
|
|
str = "null";
|
|
}
|
|
return str;
|
|
}
|
|
|
|
public DbResult ParseDbResultV2(string sql)
|
|
{
|
|
return ParseDbResult(ExecuteDatasetV2(sql).Tables[0]);
|
|
}
|
|
|
|
public DataSet ExecuteDatasetV2(string sql)
|
|
{
|
|
var ds = new DataSet();
|
|
using (var _connection = new SqlConnection(GetConnectionString()))
|
|
{
|
|
using (var cmd = new SqlCommand(sql, _connection))
|
|
{
|
|
cmd.CommandTimeout = GetCommandTimeOut();
|
|
SqlDataAdapter da;
|
|
try
|
|
{
|
|
//OpenConnection();
|
|
da = new SqlDataAdapter(cmd);
|
|
da.Fill(ds);
|
|
da.Dispose();
|
|
//CloseConnection();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
da = null;
|
|
//cmd.Dispose();
|
|
//CloseConnection();
|
|
}
|
|
}
|
|
}
|
|
return ds;
|
|
}
|
|
|
|
private int GetCommandTimeOut()
|
|
{
|
|
int cto = 0;
|
|
try
|
|
{
|
|
int.TryParse(ConfigurationSettings.AppSettings["eto"].ToString(), out cto);
|
|
if (cto == 0)
|
|
cto = 30;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
cto = 30;
|
|
}
|
|
return cto;
|
|
}
|
|
|
|
public DbResult ParseDbResult(DataTable dt)
|
|
{
|
|
var res = new DbResult();
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
res.ErrorCode = dt.Rows[0][0].ToString();
|
|
res.Msg = dt.Rows[0][1].ToString();
|
|
res.Id = dt.Rows[0][2].ToString();
|
|
}
|
|
if (dt.Columns.Count > 3)
|
|
{
|
|
res.Extra = dt.Rows[0][3].ToString();
|
|
}
|
|
if (dt.Columns.Count > 4)
|
|
{
|
|
res.Extra2 = dt.Rows[0][4].ToString();
|
|
}
|
|
if (dt.Columns.Count > 5)
|
|
{
|
|
res.Extra3 = dt.Rows[0][5].ToString();
|
|
}
|
|
return res;
|
|
}
|
|
|
|
public DbResult ParseDbResultNew(string sql)
|
|
{
|
|
return ParseDbResultNew(ExecuteDataset(sql).Tables[0]);
|
|
}
|
|
|
|
public DbResult ParseDbResultNew(DataTable dt)
|
|
{
|
|
var res = new DbResult();
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
res.ErrorCode = dt.Rows[0][0].ToString();
|
|
res.Msg = dt.Rows[0][1].ToString();
|
|
res.Id = dt.Rows[0][2].ToString();
|
|
}
|
|
if (dt.Columns.Count > 3)
|
|
{
|
|
res.Extra = dt.Rows[0][3].ToString();
|
|
}
|
|
if (dt.Columns.Count > 4)
|
|
{
|
|
res.Extra1 = dt.Rows[0][4].ToString();
|
|
}
|
|
if (dt.Columns.Count > 5)
|
|
{
|
|
res.Extra2 = dt.Rows[0][5].ToString();
|
|
}
|
|
return res;
|
|
}
|
|
|
|
public DbResult ParseDbResult(string sql)
|
|
{
|
|
return ParseDbResult(ExecuteDataset(sql).Tables[0]);
|
|
}
|
|
|
|
public DbResult ExecuteJob(string jobName)
|
|
{
|
|
Server server = new Server("10.90.77.61");
|
|
server.ConnectionContext.LoginSecure = false;
|
|
server.ConnectionContext.Login = "Arjun";
|
|
server.ConnectionContext.Password = "@RjUn@jM3rEm!TdB@J@pAn#$Jm3";
|
|
server.ConnectionContext.Connect();
|
|
//server.JobServer.Jobs["Txn Sync"]?.Start();
|
|
|
|
var job = server.JobServer.Jobs[jobName];
|
|
if (job.CurrentRunStatus.ToString() == "Idle")
|
|
{
|
|
job.Start();
|
|
}
|
|
DbResult _resp = new DbResult();
|
|
_resp.Msg = (job.CurrentRunStatus.ToString() == "Idle") ? "Job started successfully" : "Job is already running";
|
|
_resp.ErrorCode = (job.CurrentRunStatus.ToString() == "Idle") ? "0" : "1"; ;
|
|
|
|
//Console.WriteLine(job.CurrentRunStatus.ToString()); // Current running status
|
|
|
|
return _resp;
|
|
}
|
|
|
|
public UserDetails ParseLoginResult(DataTable dt, Location location = null)
|
|
{
|
|
var res = new UserDetails();
|
|
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
var row = dt.Rows[0];
|
|
res.ErrorCode = (row[0] ?? "").ToString();
|
|
res.Msg = (row[1] ?? "").ToString();
|
|
res.logId = (row["rowId"] ?? "").ToString();
|
|
|
|
if (res.ErrorCode == "0")
|
|
{
|
|
res.UserId = (row["UserId"] ?? "").ToString();
|
|
res.FullName = (row["fullName"] ?? "").ToString();
|
|
res.Address = (row["address"] ?? "").ToString();
|
|
res.LastLoginTs = (row["LastLoginTs"] ?? "").ToString();
|
|
res.UserAccessLevel = (row["userAccessLevel"] ?? "").ToString();
|
|
res.Branch = (row["branchId"] ?? "").ToString();
|
|
res.BranchName = (row["BRANCH_NAME"] ?? "").ToString();
|
|
res.UserType = (row["UserType"] ?? "").ToString();
|
|
res.isForcePwdChanged = (row["isForcePwdChanged"] ?? "").ToString();
|
|
res.sessionTimeOut = row["sessionTimeOutPeriod"].ToString();
|
|
res.UserUniqueKey = row["UserUniqueKey"].ToString();
|
|
res.LoggedInCountry = location.CountryName + " (" + location.CountryCode + ")";
|
|
res.LoginAddress = location.Region + ", " + location.City + ", " + location.ZipCode;
|
|
}
|
|
}
|
|
return res;
|
|
}
|
|
|
|
public UserDetails ParseAgentLoginResult(DataTable dt, Location location = null)
|
|
{
|
|
var res = new UserDetails();
|
|
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
var row = dt.Rows[0];
|
|
res.Id = (row["Id"] ?? "").ToString();
|
|
res.ErrorCode = (row["ErrorCode"] ?? "").ToString();
|
|
res.Msg = (row["Mes"] ?? "").ToString();
|
|
res.logId = (row["rowId"] ?? "").ToString();
|
|
|
|
if (res.ErrorCode == "2")
|
|
{
|
|
res.AttemptCount = Convert.ToInt16((row["ac"] ?? "0").ToString());
|
|
}
|
|
|
|
if (dt.Columns.Count > 5)
|
|
{
|
|
res.UserId = (row["UserId"] ?? "").ToString();
|
|
res.LastLoginTs = (row["lastLoginTs"] ?? "").ToString();
|
|
res.FullName = (row["fullName"] ?? "").ToString();
|
|
res.UserAccessLevel = (row["userAccessLevel"] ?? "").ToString();
|
|
res.sessionTimeOut = (row["sessionTimeOutPeriod"] ?? "").ToString();
|
|
res.Country = (row["country"] ?? "").ToString();
|
|
res.CountryId = (row["countryId"] ?? "").ToString();
|
|
res.Branch = (row["branch"] ?? "").ToString();
|
|
res.BranchName = (row["branchName"] ?? "").ToString();
|
|
res.Agent = (row["agent"] ?? "").ToString();
|
|
res.AgentName = (row["agentName"] ?? "").ToString();
|
|
res.SuperAgent = (row["superAgent"] ?? "").ToString();
|
|
res.SuperAgentName = (row["superAgentName"] ?? "").ToString();
|
|
res.SettlingAgent = (row["settlingAgent"] ?? "").ToString();
|
|
res.MapCodeInt = (row["mapCodeInt"] ?? "").ToString();
|
|
res.ParentMapCodeInt = (row["parentMapCodeInt"] ?? "").ToString();
|
|
res.MapCodeDom = (row["mapCodeDom"] ?? "").ToString();
|
|
res.AgentType = (row["agentType"] ?? "").ToString();
|
|
res.IsActAsBranch = (row["isActAsBranch"] ?? "").ToString();
|
|
res.FromSendTrnTime = (row["fromSendTrnTime"] ?? "").ToString();
|
|
res.ToSendTrnTime = (row["toSendTrnTime"] ?? "").ToString();
|
|
res.FromPayTrnTime = (row["fromSendTrnTime"] ?? "").ToString();
|
|
res.ToPayTrnTime = (row["toPayTrnTime"] ?? "").ToString();
|
|
res.UserType = (row["userType"] ?? "").ToString();
|
|
res.IsHeadOffice = (row["isHeadOffice"] ?? "").ToString();
|
|
res.AgentLocation = (row["agentLocation"] ?? "").ToString();
|
|
res.AgentGrp = (row["agentGrp"] ?? "").ToString();
|
|
res.AgentEmail = (row["agentEmail"] ?? "").ToString();
|
|
res.AgentPhone = (row["agentPhone"] ?? "").ToString();
|
|
res.isForcePwdChanged = (row["isForcePwdChanged"] ?? "").ToString();
|
|
res.UserUniqueKey = (row["UserUniqueKey"] ?? "").ToString();
|
|
|
|
res.LoggedInCountry = location.CountryName + " (" + location.CountryCode + ")";
|
|
res.LoginAddress = location.Region + ", " + location.City + ", " + location.ZipCode;
|
|
|
|
if (row.Table.Columns.Contains("newBranchId"))
|
|
{
|
|
res.newBranchId = (row["newBranchId"] ?? "").ToString();
|
|
}
|
|
}
|
|
}
|
|
return res;
|
|
}
|
|
|
|
#region ParseReportResult
|
|
|
|
public ReportResult ParseReportResult(DataSet ds, string sql)
|
|
{
|
|
var res = new ReportResult();
|
|
|
|
res.Sql = sql;
|
|
res.Result = ds;
|
|
|
|
if (ds == null || ds.Tables.Count == 0)
|
|
return res;
|
|
|
|
var tableCount = ds.Tables.Count;
|
|
|
|
if (tableCount > 3)
|
|
{
|
|
res.ReportHead = ds.Tables[tableCount - 1].Rows[0][0].ToString();
|
|
}
|
|
|
|
if (tableCount > 2)
|
|
{
|
|
var html = new StringBuilder("");
|
|
var hasFilters = false;
|
|
foreach (DataRow dr in ds.Tables[tableCount - 2].Rows)
|
|
{
|
|
if (dr[0] != null && !string.IsNullOrEmpty(dr[0].ToString()))
|
|
{
|
|
html.Append(" | " + dr[0] + "=" + dr[1]);
|
|
hasFilters = true;
|
|
}
|
|
}
|
|
|
|
res.Filters = hasFilters ? html.ToString().Substring(2) : "";
|
|
}
|
|
|
|
if (tableCount > 1)
|
|
{
|
|
var pos = tableCount - 3;
|
|
if (pos < 1)
|
|
pos = 1;
|
|
|
|
var dbresult = ParseDbResult(ds.Tables[pos]);
|
|
res.ErrorCode = dbresult.ErrorCode;
|
|
res.Msg = dbresult.Msg;
|
|
res.Id = dbresult.Id;
|
|
res.ResultSet = ds.Tables[0];
|
|
}
|
|
|
|
return res;
|
|
}
|
|
|
|
public ReportResult ParseReportResult(string sql)
|
|
{
|
|
var ds = ExecuteDataset(sql);
|
|
return ParseReportResult(ds, sql);
|
|
}
|
|
|
|
public ReportResult ParseReportResult(DataSet ds)
|
|
{
|
|
return ParseReportResult(ds, "");
|
|
}
|
|
|
|
#endregion ParseReportResult
|
|
|
|
public DataTable GetTable2(string sql)
|
|
{
|
|
return ExecuteDataset(sql).Tables[1];
|
|
}
|
|
|
|
protected string ParseData(string data)
|
|
{
|
|
return data.Replace("\"", "").Replace("'", "").Trim();
|
|
}
|
|
|
|
public string AutoSelect(string str1, string str2)
|
|
{
|
|
if (str1.ToLower() == str2.ToLower())
|
|
return "selected=\"selected\"";
|
|
|
|
return "";
|
|
}
|
|
|
|
protected string ParseDate(string data)
|
|
{
|
|
data = FilterString(data);
|
|
if (data.ToUpper() == "NULL")
|
|
return data;
|
|
data = data.Replace("'", "");
|
|
var dateParts = data.Split('/');
|
|
if (dateParts.Length < 3)
|
|
return "NULL";
|
|
var m = dateParts[0];
|
|
var d = dateParts[1];
|
|
var y = dateParts[2];
|
|
|
|
return "'" + y + "-" + (m.Length == 1 ? "0" + m : m) + "-" + (d.Length == 1 ? "0" + d : d) + "'";
|
|
}
|
|
|
|
public DataTable GetTable(string sql)
|
|
{
|
|
var ds = new DataSet();
|
|
SqlDataAdapter da;
|
|
|
|
try
|
|
{
|
|
OpenConnection();
|
|
da = new SqlDataAdapter(sql, _connection);
|
|
|
|
da.Fill(ds);
|
|
da.Dispose();
|
|
CloseConnection();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
da = null;
|
|
CloseConnection();
|
|
}
|
|
return ds.Tables[0];
|
|
}
|
|
|
|
public void ExecuteProcedure(string spName, SqlParameter[] param)
|
|
{
|
|
try
|
|
{
|
|
OpenConnection();
|
|
SqlCommand command = new SqlCommand(spName, _connection);
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
|
foreach (SqlParameter p in param)
|
|
{
|
|
command.Parameters.Add(p);
|
|
}
|
|
command.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
CloseConnection();
|
|
}
|
|
}
|
|
|
|
public string DataTableToText(ref DataTable dt, string delemeter, Boolean includeColHeader)
|
|
{
|
|
var sb = new StringBuilder();
|
|
var del = "";
|
|
var rowcnt = 0;
|
|
if (includeColHeader)
|
|
{
|
|
foreach (DataColumn col in dt.Columns)
|
|
{
|
|
sb.Append(del);
|
|
sb.Append(col.ColumnName);
|
|
del = delemeter;
|
|
}
|
|
rowcnt++;
|
|
}
|
|
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
if (rowcnt > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
del = "";
|
|
foreach (DataColumn col in dt.Columns)
|
|
{
|
|
sb.Append(del);
|
|
sb.Append(row[col.ColumnName].ToString());
|
|
del = delemeter;
|
|
}
|
|
rowcnt++;
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
public string DataTableToText(ref DataTable dt, string delemeter)
|
|
{
|
|
return DataTableToText(ref dt, delemeter, true);
|
|
}
|
|
|
|
public string DataTableToHTML(ref DataTable dt, Boolean includeColHeader)
|
|
{
|
|
var sb = new StringBuilder("<table>");
|
|
|
|
if (includeColHeader)
|
|
{
|
|
sb.AppendLine("<tr>");
|
|
foreach (DataColumn col in dt.Columns)
|
|
{
|
|
sb.Append("<th>" + col.ColumnName + "</th>");
|
|
}
|
|
sb.AppendLine("</tr>");
|
|
}
|
|
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
sb.AppendLine("<tr>");
|
|
foreach (DataColumn col in dt.Columns)
|
|
{
|
|
sb.Append("<td>" + row[col.ColumnName].ToString() + "</td>");
|
|
}
|
|
sb.AppendLine("</tr>");
|
|
}
|
|
sb.AppendLine("</table>");
|
|
return sb.ToString();
|
|
}
|
|
|
|
public string DataTableToHTML(ref DataTable dt)
|
|
{
|
|
return DataTableToHTML(ref dt, true);
|
|
}
|
|
|
|
public DbResult TryParseSQL(string sql)
|
|
{
|
|
var dr = new DbResult();
|
|
try
|
|
{
|
|
OpenConnection();
|
|
using (SqlCommand command = new SqlCommand())
|
|
{
|
|
command.Connection = _connection;
|
|
command.CommandType = CommandType.Text;
|
|
command.CommandText = "SET NOEXEC ON " + sql + " SET NOEXEC OFF"; ;
|
|
command.ExecuteNonQuery();
|
|
dr.ErrorCode = "0";
|
|
dr.Msg = "Success";
|
|
}
|
|
return dr;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
dr.ErrorCode = "1";
|
|
dr.Msg = FilterQuote(ex.Message);
|
|
return dr;
|
|
}
|
|
finally
|
|
{
|
|
CloseConnection();
|
|
}
|
|
}
|
|
|
|
public DataTable DecodeLogData(DataTable logTable)
|
|
{
|
|
var data = GetDataTemplete(logTable);
|
|
if (string.IsNullOrWhiteSpace(data))
|
|
{
|
|
return null;
|
|
}
|
|
|
|
var fieldList = new ArrayList();
|
|
fieldList.Add("Table");
|
|
fieldList.Add("ChangedDate");
|
|
fieldList.Add("ChangedBy");
|
|
fieldList.Add("ChangedType");
|
|
fieldList.Add("DataID");
|
|
|
|
var dt = CreateDataTableFromLogData(data, fieldList);
|
|
|
|
foreach (DataRow row in logTable.Rows)
|
|
{
|
|
DataRow newRow = dt.NewRow();
|
|
newRow["Table"] = row["tableName"].ToString();
|
|
newRow["ChangedDate"] = row["createdDate"].ToString();
|
|
newRow["ChangedBy"] = row["createdBy"].ToString();
|
|
newRow["ChangedType"] = row["logType"].ToString();
|
|
newRow["DataID"] = row["dataId"].ToString();
|
|
|
|
CreateDataRowFromLogData(ref newRow, row["newData"].ToString());
|
|
dt.Rows.Add(newRow);
|
|
}
|
|
|
|
return dt;
|
|
}
|
|
|
|
#region Helper
|
|
|
|
private string GetDataTemplete(DataTable dt)
|
|
{
|
|
var data = "";
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
data = row["OldData"].ToString();
|
|
if (string.IsNullOrWhiteSpace(data))
|
|
{
|
|
data = row["OldData"].ToString();
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(data))
|
|
{
|
|
return data;
|
|
}
|
|
}
|
|
return data;
|
|
}
|
|
|
|
private DataTable CreateDataTableFromLogData(string data, ArrayList defaultFields)
|
|
{
|
|
var dt = new DataTable();
|
|
|
|
foreach (var fld in defaultFields)
|
|
{
|
|
dt.Columns.Add(fld.ToString());
|
|
}
|
|
|
|
var stringSeparators = new[] { "-:::-" };
|
|
var dataList = data.Split(stringSeparators, StringSplitOptions.None);
|
|
const string seperator = "=";
|
|
foreach (var itm in dataList)
|
|
{
|
|
var seperatorPos = itm.IndexOf(seperator);
|
|
if (seperatorPos > -1)
|
|
{
|
|
var field = itm.Substring(0, seperatorPos - 1).Trim();
|
|
dt.Columns.Add(field);
|
|
}
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
private void CreateDataRowFromLogData(ref DataRow row, string data)
|
|
{
|
|
var stringSeparators = new[] { "-:::-" };
|
|
var dataList = data.Split(stringSeparators, StringSplitOptions.None);
|
|
|
|
const string seperator = "=";
|
|
foreach (var itm in dataList)
|
|
{
|
|
var seperatorPos = itm.IndexOf(seperator);
|
|
if (seperatorPos > -1)
|
|
{
|
|
var field = itm.Substring(0, seperatorPos - 1).Trim();
|
|
var value = itm.Substring(seperatorPos + 1).Trim();
|
|
|
|
row[field] = value;
|
|
}
|
|
}
|
|
}
|
|
|
|
public DataTable getTable(string sql)
|
|
{
|
|
return ExecuteDataTable(sql);
|
|
}
|
|
|
|
#endregion Helper
|
|
}
|
|
}
|