|
|
using Common.Model;using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Text.RegularExpressions;
namespace Repository{ public class Dao { private string GetConnectionString() { return ConfigurationManager.ConnectionStrings["RemittanceDB"].ConnectionString; }
/// <summary>
/// Get Command TimeOut
/// </summary>
/// <returns></returns>
private int GetCommandTimeOut() { int cto = 0; try { int.TryParse(ConfigurationManager.AppSettings["cto"].ToString(), out cto); if (cto == 0) cto = 30; } catch { cto = 30; } return cto; }
/// <summary>
/// Execute Data set
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet ExecuteDataset(string sql) { var ds = new DataSet();
var conStr = GetConnectionString(); using (var con = new SqlConnection(conStr)) { var cmd = new SqlCommand(sql, con); cmd.CommandTimeout = GetCommandTimeOut(); SqlDataAdapter da; try { da = new SqlDataAdapter(cmd); da.Fill(ds); da.Dispose(); } catch (Exception ex) { throw ex; } finally { da = null; cmd.Dispose(); } return ds; } }
/// <summary>
/// Filter String to check Sql Injection
/// </summary>
/// <param name="strVal"></param>
/// <returns></returns>
public String FilterString(string strVal) { var str = FilterQuote(strVal);
if (str.ToLower() != "null") str = "'" + str + "'";
return str; }
/// <summary>
/// Filter String to check Sql Injection
/// </summary>
/// <param name="strVal"></param>
/// <returns></returns>
public String FilterStringUnicode(string strVal) { var str = FilterQuote(strVal);
if (str.ToLower() != "null") str = "N'" + str + "'";
return str; }
/// <summary>
/// Filter Xml Node String to check Sql injection
/// </summary>
/// <param name="strVal"></param>
/// <returns></returns>
public String FilterXmlNodeString(string strVal) { var str = FilterQuote(strVal);
return str; }
/// <summary>
/// Filter Quote to check sql Injection
/// </summary>
/// <param name="strVal"></param>
/// <returns></returns>
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 = Regex.Replace(str, " select ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " insert ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " update ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " delete ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " drop ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " truncate ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " create ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " begin ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " end ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " char ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " exec ", string.Empty, RegexOptions.IgnoreCase); str = Regex.Replace(str, " xp_cmd ", string.Empty, RegexOptions.IgnoreCase);
str = Regex.Replace(str, @"<.*?>", string.Empty); } else { str = "null"; } return str; }
/// <summary>
/// Parse DbResult
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public DbResult ParseDbResult(DataTable dt) { var res = new DbResult(); if (dt.Rows.Count > 0) { res.ResponseCode = 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 > 4) { res.Extra2 = dt.Rows[0][4].ToString(); } if (dt.Columns.Count > 5) { res.Extra3 = dt.Rows[0][5].ToString(); } } return res; }
/// <summary>
/// Parse DbResult
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DbResult ParseDbResult(string sql) { return ParseDbResult(ExecuteDataset(sql).Tables[0]); }
/// <summary>
/// Execute sql and return Data Table
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql) { using (var ds = ExecuteDataset(sql)) { if (ds == null || ds.Tables.Count == 0) return null;
return ds.Tables[0]; } }
/// <summary>
/// Execute sql and return Data Row
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
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]; } }
/// <summary>
/// Convert data row To String
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public string ConvertDrToString(object dr) { if (dr != DBNull.Value) { return dr.ToString(); } return string.Empty; }
/// <summary>
/// Convert Datarow To Decimal
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public Decimal ConvertDrToDecimal(object dr) { if (dr != DBNull.Value) { return Convert.ToDecimal(dr.ToString()); } return 0; }
/// <summary>
/// Convert Datarow ToInt64
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public Int64 ConvertDrToInt64(object dr) { if (dr != DBNull.Value) { return Convert.ToInt64(dr.ToString()); } return 0; }
/// <summary>
/// Convert Datarow To Date
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public DateTime ConvertDrToDate(object dr) { if (dr != DBNull.Value) { return Convert.ToDateTime(dr.ToString()); } return DateTime.MinValue; }
/// <summary>
/// Convert Datarow To Int
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public Int16 ConvertDrToInt(object dr) { if (dr != DBNull.Value) { return Convert.ToInt16(dr.ToString()); } return 0; }
/// <summary>
/// Parse Quote to check sql Injection
/// </summary>
/// <param name="val"></param>
/// <returns></returns>
public string ParseQuote(string val) { return "\"" + val + "\""; }
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; } } internal T ParseDbResult<T>(string sql) { throw new NotImplementedException(); } }}
|
