コード例 #1
0
ファイル: QB.cs プロジェクト: ricoisme/SecurityDriven.TinyORM
		const int AVERAGE_PROPERTY_LENGTH = 14 + 2; // assumed average property length, plus 2 characters for "@" and ("," or "=").

		#region Insert<T>()

		public static QueryInfo Insert<T>(T obj, Predicate<string> excludedProperties = null, string tableName = null) where T : class
		{
			if (tableName == null) tableName = obj.AsSqlName();
			var dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
			var dictCount = dict.Count;
			var dictNew = new Dictionary<string, (object, Type)>(dictCount, Util.FastStringComparer.Instance);

			int i = 0;
			string currentKey, paramName;

			var sb = new StringBuilder("INSERT ", AVERAGE_PROPERTY_LENGTH * dictCount * 2 + 25).Append(tableName).Append(" (");
			var sbParams = new StringBuilder(AVERAGE_PROPERTY_LENGTH * dictCount);
			foreach (var kvp in dict)
			{
				currentKey = kvp.Key;
				if (excludedProperties != null && excludedProperties(currentKey))
					continue;

				if (i++ != 0)
				{
					sb.Append(',');
					sbParams.Append(',');
				}
				sb.Append('[').Append(currentKey).Append(']');
				paramName = "@#" + currentKey;
				sbParams.Append(paramName);
				dictNew.Add(paramName, kvp.Value);
			}//foreach

			sb.Append(") VALUES (").Append(sbParams.ToString()).Append(')');
			var result = QueryInfo.Create(sql: sb.ToString(), parameterMap: dictNew);
			return result;
		}// Insert<T>()
コード例 #2
0
ファイル: QB.cs プロジェクト: okusnadi/SecurityDriven.TinyORM
		}// Update<T, TParamType>()
		#endregion

		#region UpdateRaw<T>()
		internal static QueryInfo UpdateRaw<T>(T obj, Predicate<string> excludedProperties = null, string tableName = null, Dictionary<string, object> dict = null) where T : class
		{
			if (tableName == null) tableName = obj.AsSqlName();
			if (dict == null) dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
			var dictCount = dict.Count;
			var dictNew = new Dictionary<string, object>(dictCount, Util.FastStringComparer.Instance);

			int i = 0;
			string currentKey, paramName;

			var sb = new StringBuilder("UPDATE ", AVERAGE_PROPERTY_LENGTH * dictCount * 2 + 12).Append(tableName).Append(" SET ");
			foreach (var kvp in dict)
			{
				currentKey = kvp.Key;
				if (excludedProperties != null && !excludedProperties(currentKey))
					continue;

				if (i++ != 0)
				{
					sb.Append(',');
				}
				paramName = "@@" + currentKey;
				sb.Append('[').Append(currentKey).Append("]=").Append(paramName);
				dictNew.Add(paramName, kvp.Value);
			}//foreach

			if (i == 0) throw new ArgumentException($"{nameof(excludedProperties)} predicate is false for all property names.", nameof(excludedProperties));
			var result = new QueryInfo { SQL = sb.ToString(), ParameterMap = dictNew };
			return result;
		}// UpdateRaw<T>()
コード例 #3
0
		}// CreateQueryInfo()

		public static QueryInfo CreateQueryInfo<TParamType>(string sql, TParamType param) where TParamType : class
		{
			var queryInfo = new QueryInfo() { SQL = sql };
			if (param == null)
			{
				queryInfo.ParameterMap = new Dictionary<string, object>(0);
				return queryInfo;
			}

			queryInfo.ParameterMap = ReflectionHelper_Shared.ObjectToDictionary<TParamType>(param, ReflectionHelper_Shared.PARAM_PREFIX);
			return queryInfo;
		}// CreateQueryInfo<TParamType>()
コード例 #4
0
		static Dictionary<string, object> GetObjectPropertiesAsDictionary<T>(T obj) where T : class
		{
			Dictionary<string, object> propertyMap;
			var rowStore = obj as RowStore;
			if (rowStore != null)
			{
				propertyMap = new Dictionary<string, object>(rowStore.RowValues.Length, Util.FastStringComparer.Instance);
				foreach (var kvp in rowStore.Schema.FieldMap)
				{
					propertyMap.Add(kvp.Key, rowStore.RowValues[kvp.Value]);
				}
			}
			else propertyMap = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);

			return propertyMap;
		}//GetObjectPropertiesAsDictionary<T>()
コード例 #5
0
ファイル: QB.cs プロジェクト: okusnadi/SecurityDriven.TinyORM
		}// Update<T>

		public static QueryInfo Update<T, TParamType>(T obj, string whereSql = null, TParamType whereParam = null, Predicate<string> excludedProperties = null, string tableName = null, Dictionary<string, object> dict = null) where T : class where TParamType : class
		{
			if (dict == null) dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
			QueryInfo queryInfo = UpdateRaw<T>(obj, excludedProperties, tableName, dict);
			var paramDictAlias = queryInfo.ParameterMap;
			var whereParamMap = default(Dictionary<string, object>);

			if (string.IsNullOrEmpty(whereSql))
			{
				if (!dict.TryGetValue("Id", out var id)) throw new ArgumentException(@"""whereSql"" is empty and object does not contain ""Id"" property.");

				whereSql = "Id=@w@Id";
				paramDictAlias.Add("@w@Id", id);
			}
			else if (whereParam != null)
			{
				whereParamMap = whereParam as Dictionary<string, object> ?? ReflectionHelper_Shared.ObjectToDictionary<TParamType>(whereParam, ReflectionHelper_Shared.PARAM_PREFIX);
				foreach (var kvp in whereParamMap)
					paramDictAlias.Add(kvp.Key, kvp.Value);
			}
			whereSql = queryInfo.SQL + " WHERE " + whereSql;
			queryInfo = new QueryInfo { SQL = whereSql, ParameterMap = paramDictAlias };
			return queryInfo;
		}// Update<T, TParamType>()
コード例 #6
0
ファイル: QB.cs プロジェクト: okusnadi/SecurityDriven.TinyORM
		}// Delete<TParamType>()
		#endregion

		#region Upsert<T>
		public static QueryInfo Upsert<T>(T obj, string tableName = null, Predicate<string> excludedInsertProperties = null, Predicate<string> includedUpdateProperties = null, string mergeOnSql = null) where T : class
		{
			if (tableName == null) tableName = obj.AsSqlName();
			if (string.IsNullOrEmpty(mergeOnSql)) mergeOnSql = "S.Id=T.Id"; // "S" is source; "T" is target
			var dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
			var dictCount = dict.Count;
			var dictParams = new Dictionary<string, object>(dictCount, Util.FastStringComparer.Instance);

			string currentKey, currentKeyBracketed, paramName;

			var sbSql = new StringBuilder(";WITH S(");
			var sbParams = new StringBuilder();
			var sbInsertColumns = new StringBuilder();
			var sbInsertValues = new StringBuilder();
			var sbUpdateSql = new StringBuilder();

			int i = 0, insertColumnCount = 0, updateColumnCount = 0;

			foreach (var kvp in dict)
			{
				currentKey = kvp.Key;
				currentKeyBracketed = "[" + currentKey + "]";
				if (i++ != 0)
				{
					sbSql.Append(',');
					sbParams.Append(',');
				}

				sbSql.Append(currentKeyBracketed);
				paramName = "@@" + currentKey;
				sbParams.Append(paramName);
				dictParams.Add(paramName, kvp.Value);

				if (excludedInsertProperties == null || !excludedInsertProperties(currentKey))
				{
					if (insertColumnCount++ > 0)
					{
						sbInsertColumns.Append(',');
						sbInsertValues.Append(',');
					}
					sbInsertColumns.Append(currentKeyBracketed);
					sbInsertValues.Append(paramName);
				}

				if (includedUpdateProperties == null || includedUpdateProperties(currentKey))
				{
					if (updateColumnCount++ > 0)
					{
						sbUpdateSql.Append(',');
					}
					sbUpdateSql.Append(currentKeyBracketed).Append('=').Append(paramName);
				}
			}//foreach

			sbSql
				.Append(") AS (SELECT ")
				.Append(sbParams.ToString())
				.Append(")\nMERGE ")
				.Append(tableName)
				.Append(" WITH (HOLDLOCK) T USING S ON ")
				.Append(mergeOnSql)
				.Append("\nWHEN NOT MATCHED THEN\n\tINSERT (")
				.Append(sbInsertColumns.ToString()).Append(") VALUES (")
				.Append(sbInsertValues);

			if (updateColumnCount > 0)
			{
				sbSql
					.Append(")\nWHEN MATCHED THEN\n\tUPDATE SET ")
					.Append(sbUpdateSql.ToString());
			}
			else
			{
				sbSql.Append(")\n");
			}

			var result = new QueryInfo { SQL = sbSql.ToString(), ParameterMap = dictParams };
			return result;
		}//Upsert()
コード例 #7
0
ファイル: QB.cs プロジェクト: okusnadi/SecurityDriven.TinyORM
		}// Delete()

		public static QueryInfo Delete<TParamType>(string tableName = null, string whereSql = null, TParamType whereParam = null) where TParamType : class
		{
			if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName");
			if (string.IsNullOrEmpty(whereSql)) throw new ArgumentNullException("whereSql");

			string sql = "DELETE FROM " + tableName + " WHERE " + whereSql;
			var whereParamMap = default(Dictionary<string, object>);

			if (whereParam != null) whereParamMap = whereParam as Dictionary<string, object> ?? ReflectionHelper_Shared.ObjectToDictionary<TParamType>(whereParam, ReflectionHelper_Shared.PARAM_PREFIX);
			var result = new QueryInfo { SQL = sql, ParameterMap = whereParamMap };
			return result;
		}// Delete<TParamType>()
コード例 #8
0
		static void SetParameters<T>(this SqlCommand command, T obj) where T : class
		{
			var objPropertyValueDictionary = ReflectionHelper_Shared.ObjectToDictionary<T>(obj, ReflectionHelper_Shared.PARAM_PREFIX);
			SetParameters(command, objPropertyValueDictionary);
		}// SetParameters<T>()