public SqliteDataReader Delete(SQLStatement sqlStatement)
{
if (sqlStatement == null)
{
return(null);
}
string sql = string.Format(
"DELETE FROM {0} WHERE {1}"
, sqlStatement.tableName, sqlStatement.GetConditions());
return(Execute(sql));
}
public SqliteDataReader Update(SQLStatement sqlStatement)
{
if (sqlStatement == null)
{
return(null);
}
string sql = string.Format(
"UPDATE {0} SET {1} WHERE {2}"
, sqlStatement.tableName, sqlStatement.GetUpdateFields(), sqlStatement.GetConditions());
return(Execute(sql));
}
/// <param name="fields">要更新的字段</param>
/// <param name="union">条件语句中的unoin关键字 比如 OR 或 AND</param>
/// <param name="operation">条件语句中的操作符 比如 = 或 like</param>
/// <param name="conditionFields">条件字段</param>
public void Update(SQLField[] fields, SQLUnion union, SQLOperation operation, params SQLField[] conditionFields)
{
if ((fields == null || fields.Length <= 0) || IsCountAndValueEmpty(conditionFields))
{
return;
}
SQLStatement statement = new SQLStatement(GetTableName());
statement.UpateFields(fields).AddConditions(operation, union, conditionFields);
Upate(statement);
}
/// <summary>
/// 创建数据表
/// </summary>
public SqliteDataReader CreateTable(SQLStatement sqlStatement)
{
if (sqlStatement == null)
{
return(null);
}
string sql = string.Format(
"CREATE TABLE {0} ( {1} ) ",
sqlStatement.tableName, sqlStatement.GetCreateFields());
return(Execute(sql));
}
public void Upate(SQLStatement statement)
{
if (statement == null)
{
return;
}
OpenOrCreateIfNeed();
SqliteDataReader reader = sqlite.Update(statement);
sqlite.CloseReader(reader);
}
/// <param name="union">条件语句中的unoin关键字 比如 OR 或 AND</param>
public void Delete(SQLField[] fields, SQLUnion union, SQLOperation operation = SQLOperation.EQUAL)
{
if (fields == null)
{
return;
}
SQLStatement statement = new SQLStatement(GetTableName());
statement.AddConditions(operation, union, fields);
Delete(statement);
}
public SqliteDataReader Query(SQLStatement sqlStatement)
{
if (sqlStatement == null)
{
return(null);
}
StringBuilder sql = new StringBuilder();
sql.Append(string.Format(
"SELECT {0} FROM {1}"
, sqlStatement.GetSelectFields(), sqlStatement.tableName));
string conditions = sqlStatement.GetConditions();
if (!string.IsNullOrEmpty(conditions))
{
sql.Append(" WHERE ");
sql.Append(conditions);
}
string groupBy = sqlStatement.GetGroupByFields();
if (!string.IsNullOrEmpty(groupBy))
{
sql.Append(" GROUP BY ");
sql.Append(groupBy);
}
string orderBy = sqlStatement.GetOrderByFields();
if (!string.IsNullOrEmpty(orderBy))
{
sql.Append(" ORDER BY ");
sql.Append(orderBy);
}
string limit = sqlStatement.GetLimit();
if (!string.IsNullOrEmpty(limit))
{
sql.Append(" LIMIT ");
sql.Append(limit);
}
return(Execute(sql.ToString()));
}
public bool Query(string tableName, SQLField field)
{
if (string.IsNullOrEmpty(tableName) || field == null)
{
return(false);
}
SQLStatement statement = new SQLStatement(tableName);
statement.SelectFields("*").AddConditions(SQLOperation.EQUAL, SQLUnion.NONE, field);
SqliteDataReader reader = Query(statement);
bool hasRows = reader.HasRows;
CloseReader(reader);
return(hasRows);
}
/// <returns>返回多条查询结果 每条结果包含多个字段的值</returns>
/// <param name="fieldNames">要查询的字段名</param>
/// <param name="union">条件语句中的unoin关键字 比如 OR 或 AND</param>
/// <param name="operation">条件语句中的操作符 比如 = 或 like</param>
/// <param name="conditionFields">条件字段</param>
public List <SQLField[]> Query(string[] fieldNames, SQLUnion union, SQLOperation operation, params SQLField[] conditionFields)
{
if (fieldNames == null || fieldNames.Length <= 0)
{
return(null);
}
SQLStatement statement = new SQLStatement(GetTableName());
statement.SelectFields(fieldNames);
if (!IsCountAndValueEmpty(conditionFields))
{
statement.AddConditions(operation, union, conditionFields);
}
return(Query(statement));
}
//打开或创建数据库及数据表
void OpenOrCreateIfNeed()
{
if (sqlite == null)
{
sqlite = new SQLiteHelper(GetDatabaseName());
tableName = GetTableName();
SQLField[] fields = GetAllFields();
bool isExistIdField = false;
for (int i = 0; i < fields.Length; i++)
{
if (string.Equals(fields[i].name, COL_ID))
{
isExistIdField = true;
}
allFields.Add(fields[i].name, fields[i]);
}
if (!isExistIdField)
{
//默认创建integer字段_id 并作为主键
allFields.Add(COL_ID, new SQLField(COL_ID).SetType(SQLFieldType.INT).SetPrimaryKey(true));
}
}
if (!sqlite.IsTableExist(tableName))
{
SQLStatement statement = new SQLStatement(tableName);
foreach (KeyValuePair <string, SQLField> kvp in allFields)
{
statement.CreateFields(kvp.Value);
}
SqliteDataReader reader = sqlite.CreateTable(statement);
sqlite.CloseReader(reader);
}
}
public List <SQLField[]> Query(SQLStatement statement)
{
if (statement == null)
{
return(null);
}
string[] fieldNames = statement.GetSelectFieldArray();
if ((fieldNames == null || fieldNames.Length <= 0))
{
return(null);
}
OpenOrCreateIfNeed();
List <SQLField[]> result = new List <SQLField[]>();
SqliteDataReader reader = sqlite.Query(statement);
if (reader.HasRows)
{
while (reader.Read())
{
SQLField[] resultFields = new SQLField[fieldNames.Length];
for (int i = 0; i < fieldNames.Length; i++)
{
string fieldName = fieldNames[i].Trim();
int ordinal = reader.GetOrdinal(fieldName);
if (reader.IsDBNull(ordinal))
{
resultFields[i] = new SQLField(fieldName);
}
else
{
SQLFieldType fieldType = allFields[fieldName].type;
switch (fieldType)
{
case SQLFieldType.BINARY:
resultFields[i] = new SQLField(fieldName).SetValue(Convert.ToString((byte[])reader.GetValue(ordinal)));
break;
case SQLFieldType.FLOAT:
resultFields[i] = new SQLField(fieldName).SetValue(Convert.ToString(reader.GetFloat(ordinal)));
break;
case SQLFieldType.INT:
resultFields[i] = new SQLField(fieldName).SetValue(Convert.ToString(reader.GetInt32(ordinal)));
break;
case SQLFieldType.LONG:
resultFields[i] = new SQLField(fieldName).SetValue(Convert.ToString(reader.GetInt64(ordinal)));
break;
case SQLFieldType.TEXT:
resultFields[i] = new SQLField(fieldName).SetValue(reader.GetString(ordinal));
break;
default:
resultFields[i] = new SQLField(fieldName);
break;
}
resultFields[i].SetType(fieldType);
}
}
result.Add(resultFields);
}
}
sqlite.CloseReader(reader);
return(result);
}
