/// <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);
}
