📄 collecteddataprovider.cs
字号:
{
DateTime date;
bool isDate = DateTime.TryParse(searchFieldValues[i].ToString(), out date);
if (isDate) //Value is a date
{
if (searchFieldItemTypes[i].Equals("Time"))
{
if (string.IsNullOrEmpty(searchFieldValues[i].ToString()))
{
sb.Append(searchFields[i] + " is null");
}
else
{
sb.Append(searchFields[i] + " like " + "'%" + date.ToLongTimeString() + "%'");
}
}
else
{
if (string.IsNullOrEmpty(searchFieldValues[i].ToString()))
{
sb.Append(searchFields[i] + " is null");
}
else
{
sb.Append(searchFields[i] + " = #" + searchFieldValues[i] + "#");
}
}
}
else
{
if (string.IsNullOrEmpty(searchFieldValues[i].ToString()))
{
sb.Append(searchFields[i] + " is null");
}
}
}
else
{
if (string.IsNullOrEmpty(searchFieldValues[i].ToString())) //Value is null or an empty string)
{
sb.Append(searchFields[i] + " is null");
}
//for wild card searches - EJ
else if (searchFieldValues[i].ToString().StartsWith("%") && searchFieldValues[i].ToString().EndsWith("%") ||
searchFieldValues[i].ToString().StartsWith("*") && searchFieldValues[i].ToString().EndsWith("*") ||
searchFieldValues[i].ToString().StartsWith("*") && searchFieldValues[i].ToString().EndsWith("%") ||
searchFieldValues[i].ToString().StartsWith("%") && searchFieldValues[i].ToString().EndsWith("*")) //allows for wild card searches (i.e. "%T%")
{
sb.Append(searchFields[i] + " like '%" + searchFieldValues[i].ToString().Substring(1, searchFieldValues[i].ToString().Length - 2) + "%'");
}
else if (searchFieldValues[i].ToString().EndsWith("%") || searchFieldValues[i].ToString().EndsWith("*")) //allows for wild card searches (i.e. "T%")
{
sb.Append(searchFields[i] + " like '" + searchFieldValues[i].ToString().Substring(0, searchFieldValues[i].ToString().Length - 1) + "%'");
}
else
{
sb.Append(searchFields[i] + " = '" + searchFieldValues[i] + "'");
}
}
}
sb.Append(" AND ");
}
sb.Remove(sb.Length - 5, 5);
sb.Append(" AND RecStatus > 0");
Query selectQuery = db.CreateQuery(sb.ToString());
return (DataTable)db.Select(selectQuery);
}
catch (Exception ex)
{
throw new GeneralException("Could not retrieve search records", ex);
}
finally
{
}
}
#endregion Select Statements
#region Insert Statements
/// <summary>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <param name="reader">Forward-only stream of result sets obtained by executing a command.</param>
public void SaveRecord(View view, IDataReader reader)
{
try
{
bool hasData = false;
WordBuilder fieldNames = new WordBuilder();
WordBuilder fieldValues = new WordBuilder();
fieldNames.Delimitter = StringLiterals.COMMA;
fieldValues.Delimitter = StringLiterals.COMMA;
foreach (Field field in view.Fields.TableColumnFields)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField))
{
if (!Util.IsEmpty(reader[field.Name]))
{
hasData = true;
fieldNames.Append(Util.InsertInSquareBrackets(field.Name));
string fieldValue = reader[field.Name].ToString();
// DEFECT #247 Insert in single quotes in textfield when needed.
if ((field is TextField))
{
fieldValue = Util.InsertInSingleQuotes(fieldValue);
}
if ((field is DateField) || (field is DateTimeField) || (field is TimeField) || (field is PhoneNumberField) || (field is TableBasedDropDownField))
{
fieldValue = Util.InsertInSingleQuotes(fieldValue);
}
if (field is CheckBoxField)
{
bool checkboxValue = bool.Parse(fieldValue);
if (checkboxValue)
{
fieldValue = "1";
}
else
{
fieldValue = "0";
}
}
fieldValues.Append(fieldValue);
}
}
}
string queryString = "INSERT INTO ";
queryString += Util.InsertInSquareBrackets(view.TableName) + StringLiterals.SPACE;
queryString += Util.InsertInParantheses(fieldNames.ToString());
queryString += " VALUES ";
queryString += Util.InsertInParantheses(fieldValues.ToString());
Query insertQuery = db.CreateQuery(queryString);
if (hasData)
{
db.ExecuteNonQuery(insertQuery);
}
}
//catch (Exception ex)
//{
// throw new GeneralException("Could not insert record.", ex);
//}
finally
{
}
}
/// <summary>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <returns>Unique key of the record.</returns>
public int SaveRecord(View view)
{
Configuration config = Configuration.GetNewInstance();
try
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [" + view.TableName + "] (");
foreach (IDataField dataField in view.Fields.DataFields)
{
// if (!(dataField is UniqueKeyField) && !(dataField is RecStatusField) && !(dataField is MirrorField))
if (!(dataField is UniqueKeyField) && !(dataField is MirrorField))
{
sb.Append(Util.InsertInSquareBrackets(dataField.Name));
sb.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(") VALUES (");
foreach (IDataField dataField in view.Fields.DataFields)
{
// if (!(dataField is UniqueKeyField) && !(dataField is RecStatusField) && !(dataField is MirrorField))
if (!(dataField is UniqueKeyField) && !(dataField is MirrorField))
{
if (dataField is RecStatusField)
{
if (dataField.CurrentRecordValue == null) //saving a new record
{
sb.Append("1, ");
}
else
{
sb.Append(int.Parse(dataField.CurrentRecordValue.ToString())).Append(", ");
}
}
else if (dataField is DateField || dataField is DateTimeField || dataField is TimeField)
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
sb.Append(Util.InsertInSingleQuotes(DateTime.Parse(dataField.CurrentRecordValue).ToString()));
sb.Append(", ");
}
else
{
sb.Append("null, ");
}
}
else if (dataField is ImageField)
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
sb.Append(Util.InsertInSingleQuotes(dataField.CurrentRecordValue.ToString()));
sb.Append(", ");
}
else
{
sb.Append("null, ");
}
}
else if (dataField is CheckBoxField || dataField is YesNoField)
{
if (dataField is YesNoField)
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
if (dataField.CurrentRecordValue.Equals("1") || dataField.CurrentRecordValue.Equals(config.Settings.RepresentationOfYes))
{
sb.Append("1, ");
}
else if (dataField.CurrentRecordValue.Equals("0") || dataField.CurrentRecordValue.Equals(config.Settings.RepresentationOfNo))
{
sb.Append("0, ");
}
}
else
{
sb.Append("null, ");
}
}
else if (dataField is CheckBoxField)
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
if (dataField.CurrentRecordValue.Equals("1") || dataField.CurrentRecordValue.Equals("True"))
{
sb.Append("1, ");
}
else if (dataField.CurrentRecordValue.Equals("0") || dataField.CurrentRecordValue.Equals("False"))
{
sb.Append("0, ");
}
}
else
{
sb.Append("null, ");
}
}
}
else if (dataField is NumberField)
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
sb.Append(double.Parse(dataField.CurrentRecordValue.ToString())).Append(", ");
}
else
{
sb.Append("null, ");
}
}
else
{
if (!string.IsNullOrEmpty(dataField.CurrentRecordValue))
{
sb.Append(Util.InsertInSingleQuotes(dataField.CurrentRecordValue));
sb.Append(", ");
}
else
{
sb.Append("null, ");
}
}
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
Query insertQuery = db.CreateQuery(sb.ToString());
db.ExecuteNonQuery(insertQuery);
Query selectQuery = db.CreateQuery("SELECT MAX(UniqueKey) FROM [" + view.TableName + "]");
// DataTable result = db.Select(selectQuery);
// return (int)result.Rows[0][0];
int recordID = (int)db.ExecuteScalar(selectQuery);
//SaveGridRecord(view, recordID);
return recordID;
}
catch (Exception ex)
{
throw new GeneralException("Could not insert record.", ex);
}
finally
{
}
}
/// <summary>
/// Inserts table name and Id into SysDataTables.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="viewId">Id of view.</param>
public void InsertSysDataTableRow(string tableName, int viewId)
{
#region Input Validation
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("Table Name");
}
if (viewId < 0)
{
throw new ArgumentOutOfRangeException("View Id");
}
#endregion
try
{
Query query = db.CreateQuery("INSERT INTO sysDataTables([TableName], [ViewId]) VALUES (@tableName, @viewId)");
query.Parameters.Add(new QueryParameter("@tableName", DbType.String, tableName));
query.Parameters.Add(new QueryParameter("@viewId", DbType.Int16, viewId));
db.ExecuteNonQuery(query);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -