BigfootSQL by Mitch Labrador

<PackageReference Include="BigfootSQL" Version="1.0.2" />

 SqlHelper

public class SqlHelper
using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.Common; using System.Reflection; using System.Text; using System.Text.RegularExpressions; namespace BigfootSQL { public class SqlHelper { [AttributeUsage(AttributeTargets.All, Inherited = false, AllowMultiple = false)] private sealed class IgnoreAttribute : Attribute { } [AttributeUsage(AttributeTargets.All, Inherited = false, AllowMultiple = false)] private sealed class DbNameAttribute : Attribute { public string DBName { get; set; } public DbNameAttribute(string dbName) { DBName = dbName; } } private static class Cache { private static Dictionary<string, object> _cache = new Dictionary<string, object>(); public static bool Contains(string key) { return _cache.ContainsKey(key); } public static object GetValue(string key) { if (!_cache.ContainsKey(key)) return null; return _cache[key]; } public static void Add(string key, object data) { if (_cache.ContainsKey(key)) _cache[key] = data; else _cache.Add(key, data); } public static void Remove(string key) { if (_cache.ContainsKey(key)) _cache.Remove(key); } } public interface ITypeConverter { object GetValue(Type fieldType, object value); } [Serializable] public class SqlRecord { private Dictionary<string, object> Data; public bool HasValue => Data != null; public SqlRecord() { Data = new Dictionary<string, object>(); } public void AddFieldValue(string fieldname, object value) { Data.Add(fieldname, value); } public SqlRecord(Dictionary<string, object> data) { Data = data; } public string GetString(string key) { if (!HasValue) return ""; object obj = Data[key]; if (obj == null || obj == DBNull.Value) return ""; return obj.ToString(); } public int GetInt(string key) { int result = 0; int.TryParse(GetString(key), out result); return result; } public decimal GetDecimal(string key) { decimal result = 0; decimal.TryParse(GetString(key), out result); return result; } public DateTime GetDate(string key) { DateTime result = DateTime.MinValue; DateTime.TryParse(GetString(key), out result); return result; } public bool GetBool(string key) { bool result = false; string text = GetString(key).ToLowerInvariant(); switch (text) { case "1": case "on": case "true": case "yes": result = true; break; default: bool.TryParse(text, out result); break; } return result; } } private StringBuilder _sql = new StringBuilder(); private List<DbParameter> _params = new List<DbParameter>(); public string ConnectionString; private int _pageSize; private readonly List<string> _insertColumns = new List<string>(); public string Owner; public string Qualifier; public string ModuleQualifier; public string ProviderName = "System.Data.SqlClient"; public List<DbParameter> Params => _params; private bool HasParams => _params.Count > 0; public StringBuilder RawBuilder => _sql; public string DebugSql { get { string text = "====NEW QUERY====\n"; foreach (DbParameter param in _params) { string text2 = ""; bool flag = param.DbType == DbType.String || param.DbType == DbType.DateTime; text2 = ((param.Value == null) ? "NULL" : EscapeApostrophe(param.Value.ToString())); if (flag && text2 != "NULL") text2 = "'" + text2 + "'"; if (param.DbType == DbType.Boolean) text2 = ((text2 == "False") ? "0" : "1"); if (param.DbType == DbType.DateTime && text2 != "NULL" && (DateTime)param.Value == DateTime.MinValue) text2 = "NULL"; string text3 = param.DbType.ToString(); if (param.DbType == DbType.String) text3 = "nvarchar(max)"; else if (param.DbType == DbType.Int32) { text3 = "int"; } else if (param.DbType == DbType.DateTime) { text3 = "DateTime"; } else if (param.DbType == DbType.Boolean) { text3 = "bit"; } string text4 = text; text = text4 + "DECLARE @" + param.ParameterName + " " + text3; string text5 = text; text = text5 + " SET @" + param.ParameterName + " = " + text2; text += "\n"; } text = text + _sql + "\n"; string[] array = new string[13] { "SELECT", "FROM", "WHERE", "INNER JOIN", "LEFT JOIN", "ORDER BY", "GROUP BY", "DECLARE", "SET", "VALUES", "INSERT INTO", "DELETE FROM", "UPDATE" }; string[] array2 = array; foreach (string text6 in array2) { text = text.Replace(text6, "\r\n" + text6); } return text; } } public SqlHelper() { } public SqlHelper(string connectionString) { ConnectionString = connectionString; } public string AddTempParam(object value) { return AddTempParam(value, ""); } public string AddTempParam(object value, string prefix) { prefix = Regex.Replace(prefix, "[^a-zA-Z0-9]", ""); string text = "TP" + _params.Count + "_" + prefix + "_" + Guid.NewGuid().ToString().Substring(24); AddParam(text, value); return "@" + text; } public SqlHelper MergeSqlHelper(SqlHelper helperToMergeIn) { Params.AddRange(helperToMergeIn.Params); return Add(helperToMergeIn.ToString()); } public SqlHelper AddProcParam(string name, object value, bool addcomma = false) { if (!name.StartsWith("@")) name = "@" + name; AddParam(name, value); Add((addcomma ? ", " : "") + name + " = " + name); return this; } public SqlHelper AddParam(string name, object value) { if (name.StartsWith("@")) name = name.Substring(1); DbParameter dbParameter = DbProviderFactory().CreateParameter(); dbParameter.ParameterName = name; bool flag = false; if (value != null && value is DateTime && (DateTime)value == DateTime.MinValue) flag = true; if (flag) dbParameter.Value = DBNull.Value; else dbParameter.Value = value; _params.Add(dbParameter); return this; } public SqlHelper Add(string sql) { return Append(sql); } public SqlHelper Add(SqlHelper sql) { Params.AddRange(sql.Params); return Append(sql.ToString()); } private SqlHelper Append(string sql) { if (_sql.Length > 0) sql = " " + sql + " "; _sql.Append(Translate(sql)); return this; } public SqlHelper Add(string wherecolumn, object value) { return Add(wherecolumn, "=", value, false); } public SqlHelper AddIf(bool condition, string wherecolumn, string operator, object value) { if (condition) Add(wherecolumn, operator, value); return this; } public SqlHelper Add(string wherecolumn, object value, bool isSet) { return Add(wherecolumn, "=", value, isSet); } public SqlHelper Add(string wherecolumn, string operator, object value) { return Add(wherecolumn, operator, value, false); } public SqlHelper Add(string wherecolumn, string operator, object value, bool isSet) { if (value == null) { Add(wherecolumn); if (!isSet) return Add("IS NULL"); return Add("= NULL"); } return Add(wherecolumn).Add(operator).Add(AddTempParam(value, wherecolumn)); } public SqlHelper SET_IDENTITY_INSERT_ON(string tablename) { return Add("SET IDENTITY_INSERT " + tablename + " ON"); } public SqlHelper SET_IDENTITY_INSERT_OFF(string tablename) { return Add("SET IDENTITY_INSERT " + tablename + " OFF"); } public SqlHelper GO() { return Add("GO"); } public SqlHelper LIKE(string wherecolumn, string value) { return Add(wherecolumn).Add("LIKE").Add("'%" + EscapeForLike(value, true) + "%'"); } public SqlHelper StartsWith(string wherecolumn, string value) { return Add(wherecolumn).Add("LIKE").Add("'" + EscapeForLike(value, true) + "%'"); } public SqlHelper EndsWith(string wherecolumn, string value) { return Add(wherecolumn).Add("LIKE").Add("'%" + EscapeForLike(value, true) + "'"); } public SqlHelper Contains(string wherecolumn, string value) { return LIKE(wherecolumn, value); } public SqlHelper LIKE(string wherecolumn, string value, bool fullTextSearch) { if (!string.IsNullOrEmpty(value) && fullTextSearch) { bool flag = true; OpenParenthesis(); string[] array = value.Split(new char[1] { ' ' }); foreach (string value2 in array) { if (flag) flag = false; else AND(); LIKE(wherecolumn, value2); } CloseParenthesis(); return this; } return LIKE(wherecolumn, value); } public SqlHelper SELECT(string sql) { return Add("SELECT " + sql); } public SqlHelper CASE() { return Add("CASE"); } public SqlHelper CASE(string condition, string trueResult, string falseResult) { return CASE().WHEN(condition, trueResult).ELSE(falseResult).END(); } public SqlHelper AS(string columnName) { return Add("AS " + columnName); } public SqlHelper WHEN(string condition, string trueResult) { return Add($"""{condition}""{trueResult}"); } public SqlHelper ELSE(string elseResult) { return Add("ELSE " + elseResult); } public SqlHelper END() { return Add("END"); } public SqlHelper SELECTPAGED(string columns, string pageorderby, int pageSize) { _pageSize = pageSize; return Add("SELECT " + columns + ", ROW_NUMBER() OVER(ORDER BY " + pageorderby + " ) AS RowNumber"); } public SqlHelper PAGE(int currentpage) { int num = _pageSize * currentpage; int num2 = _pageSize * currentpage + _pageSize + 1; _sql.Insert(0, "SELECT * FROM ("); _sql.AppendFormat(") PagedResult WHERE RowNumber > " + num + " AND RowNumber < " + num2); return this; } public SqlHelper SELECT_ALL_FROM(string tablename) { return Add("SELECT * FROM").Add(Translate(tablename)); } public SqlHelper SELECT_ALL_FROM(string tablename, int topCount) { string str = (topCount == 0) ? "" : (" TOP " + topCount + " "); return Add("SELECT" + str + " * FROM").Add(Translate(tablename)); } public SqlHelper SELECT_TOP(int max, string sql) { return Add("SELECT TOP " + max + " " + sql); } public SqlHelper SELECT_COUNT_ALL_FROM(string tablename) { return Add("SELECT COUNT(*) FROM").Add(Translate(tablename)); } public SqlHelper SELECT_COUNT_ALL_FROM(SqlHelper query, string alias) { return Add("SELECT COUNT(*) FROM (").Add(query).Add(")").Add(alias); } public SqlHelper SELECT(params string[] columns) { string text = "SELECT "; bool flag = true; foreach (string str in columns) { if (flag) { text += columns; flag = false; } else text = text + ", " + str; } return Add(text); } public SqlHelper SELECT_IDENTITY() { return Add("SELECT @@IDENTITY"); } public SqlHelper INNERJOIN(string sql) { return Add("INNER JOIN " + sql); } public SqlHelper INNERJOIN(SqlHelper sql, string alias) { _params.AddRange(sql.Params); return Add("INNER JOIN (" + sql + ") " + alias); } public SqlHelper LEFTJOIN(string sql) { return Add("LEFT JOIN " + sql); } public SqlHelper LEFTJOIN(SqlHelper sql, string alias) { Params.AddRange(sql.Params); return Add("LEFT JOIN (" + sql + ") " + alias); } public SqlHelper OUTERJOIN(string sql) { return Add("FULL JOIN " + sql); } public SqlHelper UNIONALL(string sql) { return Add("UNION ALL " + sql); } public SqlHelper UNIONALL(SqlHelper sql) { Params.AddRange(sql.Params); return Add("UNION ALL " + sql); } public SqlHelper OUTERJOIN(SqlHelper sql, string alias) { Params.AddRange(sql.Params); return Add("FULL JOIN (" + sql + ") " + alias); } public SqlHelper ON(string sql) { return Add("ON " + sql); } public SqlHelper ON(string leftcolumn, string rightcolumn) { return Add("ON " + leftcolumn + " = " + rightcolumn); } public SqlHelper FROM(string sql) { return Add("FROM " + sql); } public SqlHelper FROM(SqlHelper sql, string alias) { Params.AddRange(sql.Params); return Add("FROM (" + sql + ") " + alias); } public SqlHelper WHERE() { return Add("WHERE"); } public SqlHelper WHERE(string sql) { return Add("WHERE " + sql); } public SqlHelper WHERE(SqlHelper sql) { return Add("WHERE ").Add(sql); } public SqlHelper WHERE(string columnname, object value) { return Add("WHERE").Add(columnname, value); } public SqlHelper WHERE(string columnname, string operator, object value) { return Add("WHERE").Add(columnname, operator, value); } public SqlHelper ISNOTNULL(string columnName) { return Add(columnName + " IS NOT NULL"); } public SqlHelper ANDISNOTNULL(string columnName) { return AND().ISNOTNULL(columnName); } public SqlHelper ORISNOTNULL(string columnName) { return OR().ISNOTNULL(columnName); } public SqlHelper ISNULL(string columnName) { return Add(columnName + " IS NULL"); } public SqlHelper ANDISNULL(string columnName) { return AND().ISNULL(columnName); } public SqlHelper ORISNULL(string columnName) { return OR().ISNULL(columnName); } public SqlHelper IN(string columnname, int value) { return Add(columnname + " IN (" + value + ")"); } public SqlHelper IN(string columnname, string value) { return Add(columnname + " IN ('" + EscapeApostrophe(value) + "')"); } public SqlHelper WHEREAND() { if (!ToString().Contains("WHERE")) return Add("WHERE"); return Add("AND"); } public SqlHelper WHEREOR() { if (!ToString().Contains("WHERE")) return Add("WHERE"); return Add("OR"); } public SqlHelper IN(string columnname, params string[] values) { return Add(columnname + " IN (" + ArrayToINstring(values) + ")"); } public SqlHelper IN(string columnname, bool searchAsIntArray, params string[] values) { return Add(columnname + " IN (" + ArrayToINint(values) + ")"); } public SqlHelper IN(string columnname, params int[] values) { return Add(columnname + " IN (" + ArrayToINint(values) + ")"); } public SqlHelper IN(string columnname, SqlHelper sql) { return Add(columnname + " IN (").MergeSqlHelper(sql).Add(")"); } public SqlHelper ORDERBY(string sql) { return Add("ORDER BY " + sql); } public SqlHelper GROUPBY(string sql) { return Add("GROUP BY " + sql); } public SqlHelper HAVING(string sql) { return Add("HAVING " + sql); } public SqlHelper INSERTINTO(string tablename, string columns) { _insertColumns.AddRange(columns.Split(new char[1] { ',' })); return Add("INSERT INTO " + tablename + "(" + columns + ")"); } public SqlHelper VALUES(string sql) { return Add("VALUES ( " + sql + " ) "); } public SqlHelper VALUES(params object[] ps) { Add("VALUES ("); for (int i = 0; i < ps.Length; i++) { string prefix = (i < _insertColumns.Count) ? _insertColumns[i] : ""; object value = ps[i]; if (i > 0) Add(","); Add(AddTempParam(value, prefix)); } return Add(")"); } public SqlHelper OP() { return OpenParenthesis(); } public SqlHelper OpenParenthesis() { return Add("("); } public SqlHelper OP(string wherecolumn, object value) { return OpenParenthesis(wherecolumn, value); } public SqlHelper OpenParenthesis(string wherecolumn, object value) { return Add("(").Add(wherecolumn, value); } public SqlHelper CP() { return CloseParenthesis(); } public SqlHelper CloseParenthesis() { return Add(")"); } public SqlHelper UPDATE(string sql) { return Add("UPDATE " + sql); } public SqlHelper SET() { return Add("SET"); } public SqlHelper SET(string columnname, object value) { int num = _sql.ToString().LastIndexOf(" UPDATE ", StringComparison.InvariantCultureIgnoreCase); if (num == -1) num = 0; string text = _sql.ToString().Substring(num).TrimEnd(new char[0]); if (text.IndexOf(" SET", StringComparison.InvariantCultureIgnoreCase) == -1) Add("SET"); else if (!text.TrimEnd(new char[0]).EndsWith(",")) { Add(","); } Add(columnname, value, true); return this; } public SqlHelper DELETEFROM(string sql) { return Add("DELETE FROM " + sql); } public SqlHelper AND() { return Add("AND"); } public SqlHelper AND(string sql) { return Add("AND " + sql); } public SqlHelper AND(string column, object value) { return Add("AND").Add(column, value); } public SqlHelper AND(string column, string operator, object value) { return Add("AND").Add(column, operator, value); } public SqlHelper AND_BETWEEN(string column, DateTime startValue, DateTime endValue) { return AND().BETWEEN(column, startValue, endValue); } public SqlHelper BETWEEN(string column, object startValue, object endValue) { return Add(column + " BETWEEN " + AddTempParam(startValue, column) + " AND " + AddTempParam(endValue, column)); } public SqlHelper BETWEEN(string value, string startColumn, string endColumn) { return Add(value + " BETWEEN " + startColumn + " AND " + endColumn); } public SqlHelper OR() { return Add("OR"); } public SqlHelper OR(string sql) { return Add("OR " + sql); } public SqlHelper OR(string column, object value) { return Add("OR").Add(column, value); } public SqlHelper OR(string column, string operator, object value) { return Add("OR").Add(column, operator, value); } public SqlHelper OR_BETWEEN(string column, DateTime startValue, DateTime endValue) { return OR().BETWEEN(column, startValue, endValue); } public SqlHelper DECLARE(string varname, string vartype) { if (!varname.StartsWith("@")) varname = "@" + varname; return Add("DECLARE " + varname + ((vartype.ToLower() == "table") ? " AS " : " ") + vartype); } public SqlHelper DECLARE(string varname, string vartype, object value) { if (!varname.StartsWith("@")) varname = "@" + varname; return DECLARE(varname, vartype).Add("SET " + varname + " = " + AddTempParam(value, varname.Substring(1))); } public SqlHelper DECLARE_TABLE(string varname, string columns) { return DECLARE(varname, "TABLE").Add("( " + columns + " )"); } public SqlHelper EXECUTE(string spname) { return Add("EXECUTE " + spname); } public SqlHelper EXECUTE(string spname, params object[] ps) { Add("EXECUTE " + spname); bool flag = true; foreach (object value in ps) { if (flag) flag = false; else Add(","); Add(AddTempParam(value)); } return this; } public SqlHelper SELECT_SCALARFUNCTION(string fname, params object[] ps) { Add("SELECT " + fname + "("); bool flag = true; foreach (object value in ps) { if (flag) flag = false; else Add(","); Add(AddTempParam(value)); } Add(")"); return this; } private void AddIfNotFound(string statement) { if (_sql.ToString().IndexOf(statement) == -1) Add(statement); } public void Clear() { _sql = new StringBuilder(); _params = new List<DbParameter>(); } public override string ToString() { return _sql.ToString(); } public SqlHelper(string owner, string qualifier, string moduleQualifier, string connectionString) { if (!string.IsNullOrEmpty(owner) && !owner.EndsWith(".")) owner += "."; if (!string.IsNullOrEmpty(qualifier) && !qualifier.EndsWith("_")) qualifier += "_"; if (!string.IsNullOrEmpty(moduleQualifier) && !moduleQualifier.EndsWith("_")) moduleQualifier += "_"; Owner = owner; Qualifier = qualifier; ModuleQualifier = moduleQualifier; ConnectionString = connectionString; } public SqlHelper New() { return new SqlHelper(Owner, Qualifier, ModuleQualifier, ConnectionString); } public string Translate(string sql) { sql = sql.Replace("{databaseOwner}", Owner); sql = sql.Replace("{objectQualifier}", Qualifier); Dictionary<string, string> tokens = GetTokens(sql, "{{{", "}}}"); GetTokens(sql, tokens, "{{", "}}"); if (tokens.Count > 0) { foreach (string key in tokens.Keys) { sql = sql.Replace(key, key.StartsWith("{{{") ? GetName(tokens[key], false) : GetName(tokens[key], true)); } return sql; } return sql; } private string GetName(string name, bool addModuleQualifier) { string str = Owner + Qualifier; if (!string.IsNullOrEmpty(ModuleQualifier) && addModuleQualifier) str += ModuleQualifier; return str + name; } public static string ArrayToINint(params int[] ps) { string text = ""; for (int i = 0; i < ps.Length; i++) { int num = ps[i]; if (text.Length > 0) text += ", "; text += num.ToString(); } return text; } public static string ArrayToINint(params string[] ps) { string text = ""; foreach (string s in ps) { if (text.Length > 0) text += ", "; text += int.Parse(s).ToString(); } return text; } public static string ArrayToINstring(params string[] ps) { string text = ""; foreach (string sql in ps) { if (text.Length > 0) text += ", "; text = text + "'" + EscapeApostrophe(sql) + "'"; } return text; } public static string EscapeApostrophe(string sql) { sql = sql.Replace("'", "''"); return sql; } public static string EscapeForLike(string value, bool escapeApostrophe) { if (string.IsNullOrEmpty(value)) return value; string[] array = new string[4] { "%", "_", "-", "^" }; string text = value.Replace("[", "[[]"); string[] array2 = array; foreach (string text2 in array2) { text = text.Replace(text2, "[" + text2 + "]"); } if (escapeApostrophe) text = EscapeApostrophe(text); return text; } public static Dictionary<string, string> GetTokens(string s, string startDelimiter, string endDelimiter) { return GetTokens(s, new Dictionary<string, string>(), startDelimiter, endDelimiter); } public static Dictionary<string, string> GetTokens(string s, Dictionary<string, string> tokens, string startDelimiter, string endDelimiter) { int num = 0; do { int num2 = s.IndexOf(startDelimiter, num); if (num2 <= -1) break; int num3 = s.IndexOf(endDelimiter, num2); if (num3 > -1) { string text = s.Substring(num2, num3 + endDelimiter.Length - num2); string value = text.Substring(startDelimiter.Length, text.Length - (startDelimiter.Length + endDelimiter.Length)); tokens.Add(text, value); num = num3 + endDelimiter.Length; } } while (num < s.Length); return tokens; } public SqlHelper INSERTOBJECT(string tableName, object data) { return INSERTOBJECT(tableName, data, null, null); } public SqlHelper INSERTOBJECT(string tableName, object data, string includeOnlyTheseColumns, string excludeTheseColumns) { Dictionary<string, object> objectProperties = GetObjectProperties(data, includeOnlyTheseColumns, excludeTheseColumns); if (objectProperties.Count == 0) throw new ApplicationException("There are no properties to insert!"); return INSERTOBJECT(tableName, objectProperties); } public SqlHelper INSERTDICTIONARY(string tableName, Dictionary<string, object> insertData) { string text = ""; List<object> list = new List<object>(); foreach (string key in insertData.Keys) { if (!string.IsNullOrEmpty(text)) text += ", "; text += key; list.Add(insertData[key]); } return INSERTINTO(tableName, text).VALUES(list.ToArray()); } public SqlHelper UPDATEOBJECT(string tableName, object data) { return UPDATEOBJECT(tableName, data, null, null); } public SqlHelper UPDATEOBJECT(string tableName, object data, string includeOnlyTheseColumns, string excludeTheseColumns) { Dictionary<string, object> objectProperties = GetObjectProperties(data, includeOnlyTheseColumns, excludeTheseColumns); if (objectProperties.Count == 0) throw new ApplicationException("There are no properties to update!"); return UPDATEOBJECT(tableName, objectProperties); } public SqlHelper UPDATEDICTIONARY(string tableName, Dictionary<string, object> updateData) { UPDATE(tableName); foreach (string key in updateData.Keys) { SET(key, updateData[key]); } return this; } public Dictionary<string, object> GetObjectProperties(object data, string includeOnlyTheseProperties, string excludeTheseProperties) { string key = "getObjectProperties:" + data.GetType().FullName; if (Cache.Contains(key)) return (Dictionary<string, object>)Cache.GetValue(key); Dictionary<string, object> dictionary = new Dictionary<string, object>(); List<string> stringArray = GetStringArray(includeOnlyTheseProperties); List<string> stringArray2 = GetStringArray(excludeTheseProperties); PropertyInfo[] properties = data.GetType().GetProperties(); foreach (PropertyInfo propertyInfo in properties) { if (propertyInfo.CanRead && (stringArray.Count <= 0 || stringArray.Contains(propertyInfo.Name)) && (stringArray2.Count <= 0 || !stringArray2.Contains(propertyInfo.Name))) { IgnoreAttribute[] array = propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false) as IgnoreAttribute[]; if (array == null || array.Length <= 0) { string key2 = propertyInfo.Name; DbNameAttribute[] array2 = propertyInfo.GetCustomAttributes(typeof(DbNameAttribute), false) as DbNameAttribute[]; if (array2 != null && array2.Length > 0) key2 = array2[0].DBName; dictionary.Add(key2, propertyInfo.GetValue(data, null)); } } } Cache.Add(key, dictionary); return dictionary; } private List<string> GetStringArray(string data) { List<string> list = new List<string>(); if (string.IsNullOrEmpty(data)) return list; string[] array = data.Split(new char[1] { ',' }); foreach (string text in array) { list.Add(text.Trim()); } return list; } public object ExecuteScalar() { object obj = DbExecuteScalar(CommandType.Text, ToString(), _params.ToArray()); if (obj == DBNull.Value) obj = null; return obj; } public T ExecuteScalar<T>() { object obj = ExecuteScalar(); if (obj != null) { TypeConverter converter = TypeDescriptor.GetConverter(typeof(T)); return (T)converter.ConvertFromInvariantString(obj.ToString()); } return default(T); } public int ExecuteScalarIdentity() { SELECT_IDENTITY(); return ExecuteScalarInt(); } public int ExecuteScalarInt() { return ExecuteScalar<int>(); } public decimal ExecuteScalarDecimal() { return ExecuteScalar<decimal>(); } public bool ExecuteScalarBool() { return ExecuteScalar<bool>(); } public string ExecuteScalarString() { return ExecuteScalar<string>(); } public SqlRecord ExecuteSqlRecord() { return new SqlRecord(ExecuteValues()); } public List<SqlRecord> ExecuteSqlRecordCollection() { using (DbDataReader dbDataReader = ExecuteReader()) { List<SqlRecord> list = new List<SqlRecord>(); while (dbDataReader.Read()) { Dictionary<string, object> dictionary = new Dictionary<string, object>(); for (int i = 0; i < dbDataReader.FieldCount; i++) { try { string name = dbDataReader.GetName(i); object value = null; if (!dbDataReader.IsDBNull(i)) value = dbDataReader[i]; dictionary.Add(name, value); } catch (IndexOutOfRangeException) { } } list.Add(new SqlRecord(dictionary)); } return list; } } public int ExecuteNonquery() { return DbExecuteNonQuery(CommandType.Text, ToString(), _params.ToArray()); } public T ExecuteObject<T>() { using (DbDataReader dr = ExecuteReader()) return FillObject<T>(dr); } public Dictionary<string, object> ExecuteValues() { using (DbDataReader dbDataReader = ExecuteReader()) { Dictionary<string, object> result = null; if (dbDataReader.Read()) { result = new Dictionary<string, object>(); for (int i = 0; i < 100; i++) { try { string name = dbDataReader.GetName(i); object value = null; if (!dbDataReader.IsDBNull(i)) value = dbDataReader[i]; result.Add(name, value); } catch (IndexOutOfRangeException) { return result; } } return result; } return result; } } public List<string> ExecuteStringCollection() { using (DbDataReader dbDataReader = ExecuteReader()) { List<string> list = null; while (dbDataReader.Read()) { if (!dbDataReader.IsDBNull(0)) { if (list == null) list = new List<string>(); list.Add(dbDataReader[0].ToString()); } } return list; } } public List<T> ExecuteCollection<T>() { using (DbDataReader reader = ExecuteReader()) return FillCollection<T>(reader); } public DbDataReader ExecuteReader() { return DbExecuteReader(CommandType.Text, ToString(), _params.ToArray()); } public DataSet ExecuteDataset() { return DbExecuteDataset(CommandType.Text, ToString(), _params.ToArray()); } public DataSet ExecuteDataset(DataSet data) { DataSet dataSet = ExecuteDataset(); if (data.Tables.Count == 1 && dataSet.Tables.Count == 1) dataSet.Tables[0].TableName = data.Tables[0].TableName; data.Merge(dataSet, false, MissingSchemaAction.Ignore); return data; } public DbProviderFactory DbProviderFactory() { return DbProviderFactories.GetFactory(ProviderName); } public DbConnection CreateDbConnection(bool openConnection) { DbConnection dbConnection = DbProviderFactory().CreateConnection(); dbConnection.ConnectionString = ConnectionString; if (openConnection) dbConnection.Open(); return dbConnection; } private DbCommand CreateDbCommand(CommandType commandType, string commandText, IEnumerable<DbParameter> commandParameters) { DbCommand dbCommand = DbProviderFactory().CreateCommand(); dbCommand.CommandText = commandText; dbCommand.CommandType = commandType; if (commandParameters != null) { foreach (DbParameter commandParameter in commandParameters) { if (commandParameter != null) { if ((commandParameter.Direction == ParameterDirection.InputOutput || commandParameter.Direction == ParameterDirection.Input) && commandParameter.Value == null) commandParameter.Value = DBNull.Value; dbCommand.Parameters.Add(commandParameter); } } } dbCommand.Connection = CreateDbConnection(true); return dbCommand; } private void DisposeDbCommand(DbCommand cmd) { if (cmd != null) { if (cmd.Connection != null) cmd.Connection.Dispose(); cmd.Dispose(); } } public int DbExecuteNonQuery(CommandType commandType, string commandText, params DbParameter[] commandParameters) { DbCommand dbCommand = CreateDbCommand(commandType, commandText, commandParameters); try { int result = dbCommand.ExecuteNonQuery(); dbCommand.Parameters.Clear(); return result; } finally { DisposeDbCommand(dbCommand); } } public DataSet DbExecuteDataset(CommandType commandType, string commandText, params DbParameter[] commandParameters) { DbCommand dbCommand = CreateDbCommand(commandType, commandText, commandParameters); try { DataSet dataSet = new DataSet(); DbDataAdapter dbDataAdapter = DbProviderFactory().CreateDataAdapter(); dbDataAdapter.SelectCommand = dbCommand; dbDataAdapter.Fill(dataSet); dbCommand.Parameters.Clear(); return dataSet; } finally { DisposeDbCommand(dbCommand); } } public object DbExecuteScalar(CommandType commandType, string commandText, params DbParameter[] commandParameters) { DbCommand dbCommand = CreateDbCommand(commandType, commandText, commandParameters); try { object result = dbCommand.ExecuteScalar(); dbCommand.Parameters.Clear(); return result; } finally { DisposeDbCommand(dbCommand); } } public DbDataReader DbExecuteReader(CommandType commandType, string commandText, IEnumerable<DbParameter> commandParameters) { DbCommand dbCommand = CreateDbCommand(commandType, commandText, commandParameters); try { DbDataReader result = dbCommand.ExecuteReader(CommandBehavior.CloseConnection); bool flag = true; foreach (DbParameter parameter in dbCommand.Parameters) { if (parameter.Direction != ParameterDirection.Input) flag = false; } if (flag) dbCommand.Parameters.Clear(); return result; } catch { DisposeDbCommand(dbCommand); throw; } } public static void AddTypeConverter(string typeName, ITypeConverter converter) { typeName = typeName.ToLower(); Dictionary<string, ITypeConverter> allConverters = GetAllConverters(); if (allConverters.ContainsKey(typeName)) allConverters[typeName] = converter; else allConverters.Add(typeName, converter); } private static Dictionary<string, ITypeConverter> GetAllConverters() { if (!Cache.Contains("TypeConverters")) { Dictionary<string, ITypeConverter> data = new Dictionary<string, ITypeConverter>(); Cache.Add("TypeConverters", data); } return (Dictionary<string, ITypeConverter>)Cache.GetValue("TypeConverters"); } private static ITypeConverter GetTypeConverter(string typeName) { typeName = typeName.ToLower(); Dictionary<string, ITypeConverter> allConverters = GetAllConverters(); if (!allConverters.ContainsKey(typeName)) return null; return allConverters[typeName]; } public static object GetValue(Type fieldType, object value) { string name = fieldType.Name; object result = null; Type baseType = fieldType.BaseType; if (value == null || value.ToString() == string.Empty) return result; if (fieldType.Equals(value.GetType())) result = value; else if (name == "Boolean") { result = ((value.ToString() == "1" || value.ToString().ToLower() == "on" || value.ToString().ToLower() == "true" || value.ToString().ToLower() == "yes") ? true : false); } else if (name.StartsWith("Nullable")) { string fullName = fieldType.FullName; if (fullName.Contains("DateTime")) result = Convert.ToDateTime(value); else if (fullName.Contains("Boolean")) { result = Convert.ToBoolean(value); } else if (fullName.Contains("Int16")) { result = Convert.ToInt16(value); } else if (fullName.Contains("Int32")) { result = Convert.ToInt32(value); } else if (fullName.Contains("Integer")) { result = Convert.ToInt32(value); } else if (fieldType.FullName.Contains("Int64")) { result = Convert.ToInt64(value); } else if (fieldType.FullName.Contains("Decimal")) { result = Convert.ToDecimal(value); } else if (fullName.Contains("Double")) { result = Convert.ToDouble(value); } else if (fullName.Contains("Single")) { result = Convert.ToSingle(value); } else if (fullName.Contains("UInt16")) { result = Convert.ToUInt16(value); } else if (fullName.Contains("UInt32")) { result = Convert.ToUInt32(value); } else if (fullName.Contains("UInt64")) { result = Convert.ToUInt64(value); } else if (fullName.Contains("SByte")) { result = Convert.ToSByte(value); } } else if (fieldType.FullName.Equals("System.Guid")) { result = new Guid(value.ToString()); } else if (baseType != (Type)null && fieldType.BaseType == typeof(Enum)) { if (!int.TryParse(value.ToString(), out int result2)) try { return Enum.Parse(fieldType, value.ToString()); } catch (Exception) { return Enum.ToObject(fieldType, value); } result = result2; } else if (fieldType.FullName.Equals("System.Guid")) { result = new Guid(value.ToString()); } else { ITypeConverter typeConverter = GetTypeConverter(fieldType.Name); if (typeConverter == null) try { result = Convert.ChangeType(value, fieldType); return result; } catch (Exception) { return result; } result = typeConverter.GetValue(fieldType, value); } return result; } public static T FillObject<T>(DbDataReader dr) { return FillObject<T>(dr, true); } public static List<T> FillCollection<T>(DbDataReader reader) { List<T> list = new List<T>(); try { while (reader.Read()) { list.Add(CreateObject<T>(reader)); } return list; } finally { DisposeReader(reader); } } private static void DisposeReader(DbDataReader reader) { if (reader != null) { if (!reader.IsClosed) reader.Close(); reader.Dispose(); } } private static T FillObject<T>(DbDataReader dr, bool manageDataReader) { try { T result = default(T); if (manageDataReader && !dr.Read()) return result; return CreateObject<T>(dr); } finally { if (manageDataReader) DisposeReader(dr); } } private static T CreateObject<T>(DbDataReader dr) { bool flag = false; T val; if (typeof(T) == typeof(string)) { val = (T)(object)string.Empty; flag = true; } else if (typeof(T).IsValueType) { val = default(T); flag = true; } else { val = Activator.CreateInstance<T>(); } if (flag) { object value = GetValue(val.GetType(), dr.GetValue(0)); if (value != null) val = (T)value; return val; } List<object> fields = GetFields(val); int[] ordinals = GetOrdinals(fields, dr); for (int i = 0; i < fields.Count; i++) { object obj = fields[i]; if (ordinals[i] != -1) { object value2 = dr.GetValue(ordinals[i]); if (value2 != DBNull.Value) { if (obj is FieldInfo) (obj as FieldInfo).SetValue(val, GetValue(((FieldInfo)obj).FieldType, value2), BindingFlags.Default, null, null); else if (obj is PropertyInfo) { (obj as PropertyInfo).SetValue(val, GetValue(((PropertyInfo)obj).PropertyType, value2), BindingFlags.Default, null, null, null); } } } } return val; } private static int[] GetOrdinals(List<object> fields, DbDataReader dr) { int[] array = new int[fields.Count]; Hashtable hashtable = new Hashtable(); for (int i = 0; i < dr.FieldCount; i++) { hashtable[dr.GetName(i).ToUpperInvariant()] = ""; } for (int j = 0; j < fields.Count; j++) { string text = (fields[j] is FieldInfo) ? ((FieldInfo)fields[j]).Name.ToUpperInvariant() : ((PropertyInfo)fields[j]).Name.ToUpperInvariant(); if (hashtable.ContainsKey(text)) array[j] = dr.GetOrdinal(text); else array[j] = -1; } return array; } private static List<object> GetFields(object obj) { string key = "reflectioncache_" + obj.GetType().FullName; List<object> list; if (Cache.Contains(key)) list = (Cache.GetValue(key) as List<object>); else { list = new List<object>(); PropertyInfo[] properties = obj.GetType().GetProperties(); foreach (PropertyInfo propertyInfo in properties) { if (propertyInfo.CanWrite) list.Add(propertyInfo); } FieldInfo[] fields = obj.GetType().GetFields(); foreach (FieldInfo fieldInfo in fields) { if (fieldInfo.IsPublic && !fieldInfo.IsStatic) list.Add(fieldInfo); } list.AddRange(obj.GetType().GetFields()); Cache.Add(key, list); } return list; } } }