/// <summary> /// Build a Select query command /// </summary> /// <param name="condition">Condition</param> /// <param name="listOfParameters">List of record parameters</param> protected void BuildSelectCommand(DbTransaction transaction, Condition condition, List <RecordParameter> listOfParameters, params RecordOrderBy[] pOrderBy) { if (RecordConnection == null) { RecordConnection = ProviderFactory.CreateConnection(); RecordConnection.ConnectionString = ConnectionString; //RecordConnection = CreateConnection(null, this.ConnectionString); } String SelectFormat = "SELECT {0} FROM {1} {2}"; String Attrs = String.Empty; String Where = String.Empty; foreach (String filed in Current[typeof(TRECORD)].m_Members) //foreach (MemberInfo member in Current[this.GetType()].Attributes) { MemberInfo member = typeof(TRECORD).GetMember(filed)[0]; object field = member.GetCustomAttributes(typeof(Field), true)[0]; if (!String.IsNullOrEmpty(Attrs)) { Attrs += ", "; } Attrs += String.Format("[{0}]", (field as Field).Name); if (field is Field & (field as Field).IsPrimaryKey) { if (!String.IsNullOrEmpty(Where)) { Where += " AND "; } Where += String.Format("[{0}]={1}", (field as Field).Name, InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name) ); } } if (condition != null) { Where = String.Format("WHERE {0}", condition.Build(listOfParameters)).Replace(" ", " "); } else { Where = String.Format("WHERE ({0})", Where); } if (pOrderBy != null && pOrderBy.Length > 0) { SelectFormat += " ORDER BY "; foreach (RecordOrderBy orderBy in pOrderBy) { SelectFormat += orderBy.ToString() + ","; } SelectFormat = SelectFormat.Substring(0, SelectFormat.Length - 1); } _SelectCommandText = String.Format(SelectFormat, Attrs, TableName, Where); }
/// <summary> /// Build delete query /// </summary> /// <param name="pParams">List of parameters</param> internal void BuildDeleteCommand(DbTransaction transaction, params RecordParameter[] pParams) { if (RecordConnection == null) { RecordConnection = CreateConnection(transaction, this.ChooseCurrentConnectionString()); } String DeleteFormat = "DELETE FROM {0} WHERE {1}"; String Where = String.Empty; foreach (String memberName in Current[this.GetType()].m_Members) { MemberInfo member = GetType().GetMember(memberName)[0]; object field = member.GetCustomAttributes(typeof(Field), true)[0]; if (field is Field & (field as Field).IsPrimaryKey) { if (!String.IsNullOrEmpty(Where)) { Where += " AND "; } Where += String.Format("[{0}]={1}", (field as Field).Name, InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name)); } } _DeleteCommandText = String.Format(DeleteFormat, TableName, Where); }
/// <summary> /// Converter from CompareKind to String /// </summary> /// <param name="connection">Connection</param> /// <param name="parameter">Parameter</param> /// <param name="CompareKind">Compare Type</param> /// <returns>CompareKind converted into string with format</returns> public static String SignWithFormat(DbConnection connection, RecordParameter parameter, CompareKind CompareKind) { //if (connection == null) // connection = Record.SharedProviderFactory.CreateConnection(); String pName = InternalConvert.ConvertQueryArgument(connection, parameter.ParameterName); switch (CompareKind) { case CompareKind.None: return(String.Format(" ([{0}] = {1}) ", parameter.ParameterField, pName)); case CompareKind.Equal: return(String.Format(" ([{0}] = {1}) ", parameter.ParameterField, pName)); case CompareKind.IsNULL: return(String.Format(" ([{0}] IS NULL) ", parameter.ParameterField)); case CompareKind.Major: return(String.Format(" ([{0}] > {1}) ", parameter.ParameterField, pName)); case CompareKind.Minor: return(String.Format(" ([{0}] < {1}) ", parameter.ParameterField, pName)); case CompareKind.IsNotNULL: return(String.Format(" ([{0}] IS NOT NULL) ", parameter.ParameterField)); case CompareKind.NotEqual: return(String.Format(" ([{0}] <> {1}) ", parameter.ParameterField, pName)); case CompareKind.Like: return(String.Format(" ([{0}] LIKE '%{1}%') ", parameter.ParameterField, parameter.Value)); case CompareKind.In: if (parameter.Value is IList) { String listFieldValue = ""; foreach (var item in (IList)parameter.Value) { listFieldValue += "," + item; } return(String.Format(" ([{0}] IN ({1})) ", parameter.ParameterField, listFieldValue.Substring(1))); } else { return(String.Format(" ([{0}] IN ({1})) ", parameter.ParameterField, parameter.Value)); } case CompareKind.MajorAndEqual: return(String.Format(" ([{0}] >= {1}) ", parameter.ParameterField, pName)); case CompareKind.MinorAndEqual: return(String.Format(" ([{0}] <= {1}) ", parameter.ParameterField, pName)); case CompareKind.IsNullOrEmpty: return(String.Format(" (([{0}] IS NULL) OR ([{0}] = '')) ", parameter.ParameterField)); default: return(String.Format(" ([{0}] = {1}) ", parameter.ParameterField, pName)); } }
private void BuildSelectCommand(DbTransaction transaction, Condition condition, List <RecordParameter> listOfParameters) { if (RecordConnection == null) { RecordConnection = CreateConnection(transaction, this.ChooseCurrentConnectionString()); } String SelectFormat = "SELECT {0} FROM {1} {2}"; String Attrs = String.Empty; String Where = String.Empty; foreach (String filed in Current[GetType()].m_Members) //foreach (MemberInfo member in Current[this.GetType()].Attributes) { MemberInfo member = GetType().GetMember(filed)[0]; object field = member.GetCustomAttributes(typeof(Field), true)[0]; if (!String.IsNullOrEmpty(Attrs)) { Attrs += ", "; } Attrs += String.Format("[{0}]", (field as Field).Name); if (field is Field & (field as Field).IsPrimaryKey) { if (!String.IsNullOrEmpty(Where)) { Where += " AND "; } Where += String.Format("[{0}]={1}", (field as Field).Name, InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name) ); } } if (condition != null) { Where = String.Format("WHERE {0}", condition.Build(listOfParameters)).Replace(" ", " "); } else { Where = String.Format("WHERE ({0})", Where); } _SelectCommandText = String.Format(SelectFormat, Attrs, TableName, Where); }
/// <summary> /// Build update query /// </summary> /// <param name="pParams">List of parameters</param> internal void BuildUpdateCommand(DbTransaction transaction, params RecordParameter[] pParams) { if (RecordConnection == null) { RecordConnection = CreateConnection(transaction, this.ChooseCurrentConnectionString()); } String UpdateFormat = "UPDATE {0} SET {1} WHERE {2}"; String Sets = String.Empty; String Where = String.Empty; foreach (String memberName in Current[GetType()].m_Members) { MemberInfo member = GetType().GetMember(memberName)[0]; object field = member.GetCustomAttributes(typeof(Field), true)[0]; if (field is Field) { if (!((field as Field).IsIdentity | (field as Field).IsPrimaryKey)) { if (!String.IsNullOrEmpty(Sets)) { Sets += ", "; } Sets += String.Format("[{0}]={1}", (field as Field).Name, InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name)); } if ((field as Field).IsPrimaryKey) { if (!String.IsNullOrEmpty(Where)) { Where += " AND "; } Where += String.Format("[{0}]={1}", (field as Field).Name, InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name)); } } } _UpdateCommandText = String.Format(UpdateFormat, TableName, Sets, Where); }
/// <summary> /// Build insert query /// </summary> /// <param name="pParams">List of parameters</param> internal void BuildInsertCommand(DbTransaction transaction, params RecordParameter[] pParams) { if (RecordConnection == null) { RecordConnection = CreateConnection(transaction, this.ChooseCurrentConnectionString()); } String InsertFormat = "INSERT INTO {0}({1}) VALUES ({2})"; String Attrs = String.Empty; String Values = String.Empty; foreach (String memberName in Current[GetType()].m_Members) { MemberInfo member = GetType().GetMember(memberName)[0]; object field = member.GetCustomAttributes(typeof(Field), true)[0]; if (field is Field) { if (!(field as Field).IsIdentity) { if (!String.IsNullOrEmpty(Attrs)) { Attrs += ", "; } Attrs += String.Format("[{0}]", (field as Field).Name); if (!String.IsNullOrEmpty(Values)) { Values += ", "; } Values += String.Format("{0}", InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name) ); } } } _InsertCommandText = String.Format(InsertFormat, TableName, Attrs, Values); }