public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Console.WriteLine("Exemplo SQLite usando Command");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand(
//"insert into estados values (60, 'WI', 'William Ivanski')",
"delete from estados where codigo = 60",
v_con
);
// 4) executando Command
v_cmd.ExecuteNonQuery();
Console.WriteLine("Ok.");
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 5) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 6) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public void CreateStandardTables()
{
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_container` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `name` text NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL,";
command.CommandText += " `opened` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_items` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `iteminstance` text NOT NULL,";
command.CommandText += " `amount` INTEGER NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_mobinteract` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `vobType` INTEGER NOT NULL,";
command.CommandText += " `name` text NOT NULL,";
command.CommandText += " `posx` REAL NOT NULL,";
command.CommandText += " `posy` REAL NOT NULL,";
command.CommandText += " `posz` REAL NOT NULL,";
command.CommandText += " `world` text NOT NULL,";
command.CommandText += " `triggered` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `world_container_items` (";
command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
command.CommandText += " `containerid` INTEGER NOT NULL,";
command.CommandText += " `item` text NOT NULL,";
command.CommandText += " `amount` INTEGER NOT NULL";
command.CommandText += ")";
command.ExecuteNonQuery();
command.Dispose();
}
public void Dispose()
{
__command.Dispose();
}
public static void Main(string[] args)
{
Mono.Data.Sqlite.SqliteConnection v_con = null;
Mono.Data.Sqlite.SqliteCommand v_cmd = null;
Mono.Data.Sqlite.SqliteDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo SQLite usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Mono.Data.Sqlite.SqliteConnection(
"Data Source=../../../databases/lugares.db;Version=3;Synchronous=Full;Journal Mode=Off;"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Mono.Data.Sqlite.SqliteCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Mono.Data.Sqlite.SqliteException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
private IEnumerable <object> Select(string entityName, Type objectType, IEnumerable <FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences)
{
Debug.WriteLineIf(TracingEnabled, "+Select");
if (entityName == null)
{
throw new EntityNotFoundException(objectType);
}
if (!Entities.Contains(entityName))
{
if (DiscoverDynamicEntity(entityName) == null)
{
yield return(null);
}
}
UpdateIndexCacheForType(entityName);
var items = new List <object>();
var connection = GetConnection(false);
SQLiteCommand command = null;
try
{
CheckOrdinals(entityName);
bool tableDirect;
command = GetSelectCommand <SQLiteCommand, SQLiteParameter>(entityName, filters, out tableDirect);
command.Connection = connection as SQLiteConnection;
command.Transaction = CurrentTransaction as SQLiteTransaction;
int searchOrdinal = -1;
// ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
// TODO: we need to ensure that the search value does not exceed the length of the indexed
// field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (results.HasRows)
{
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = results.GetOrdinal(matchField);
}
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
bool fieldsSet;
object item = CreateEntityInstance(entityName, objectType, Entities[entityName].Fields, results, out fieldsSet);
object rowPK = null;
if (!fieldsSet)
{
foreach (var field in Entities[entityName].Fields)
{
MethodInfo mi = null;
if (!field.PropertyInfo.CanWrite)
{
// get a private accessor?
mi = field.PropertyInfo.GetSetMethod(true);
// not settable, so skip
if (mi == null)
{
continue;
}
}
var value = results[field.Ordinal];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (fillReferences)
{
// get serializer
var itemType = item.GetType();
var deserializer = GetDeserializer(itemType);
if (deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = deserializer.Invoke(item, new object[] { field.FieldName, value });
field.PropertyInfo.SetValue(item, @object, null);
}
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.IsTimespan)
{
// SQLite doesn't support "timespan" - and date/time must be stored as text, real or 64-bit integer (see the SQLite docs for more details)
// here we'll pull TimeSpans (since they can be negative) as an offset from a base date
var storeDate = (DateTime)value;
var storeTime = storeDate - new DateTime(1980, 1, 1);
field.PropertyInfo.SetValue(item, storeTime, null);
}
else if (field.DataType == DbType.DateTime)
{
field.PropertyInfo.SetValue(item, Convert.ToDateTime(value), null);
}
else if ((field.IsPrimaryKey) && (value is Int64))
{
if (field.PropertyInfo.PropertyType.Equals(typeof(int)))
{
// SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else
{
field.PropertyInfo.SetValue(item, Convert.ToInt64(value), null);
}
}
else if ((value is Int64) || (value is double))
{
// SQLite is "interesting" in that its 'integer' has a strong affinity toward 64-bit, so int and uint properties
// end up as 64-bit fields. Decimals have a strong affinity toward 'double', so float properties
// end up as 'double'. Even more fun is that a decimal value '0' will come back as an int64
// When we query those back, we must convert to put them into the property or we crash hard
object setval;
if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
{
setval = Convert.ToUInt32(value);
}
else if ((field.PropertyInfo.PropertyType.Equals(typeof(Int32))) || (field.PropertyInfo.PropertyType.Equals(typeof(Int32?))))
{
setval = Convert.ToInt32(value);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
{
setval = Convert.ToDecimal(value);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
{
setval = Convert.ToSingle(value);
}
else if (field.PropertyInfo.PropertyType.IsEnum)
{
setval = Enum.ToObject(field.PropertyInfo.PropertyType, value);
}
else
{
setval = value;
}
if (mi == null)
{
field.PropertyInfo.SetValue(item, setval, null);
}
else
{
mi.Invoke(setval, null);
}
}
else
{
var t = value.GetType();
field.PropertyInfo.SetValue(item, value, null);
}
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false);
}
// changed from
// items.Add(item);
yield return(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
Debug.WriteLineIf(TracingEnabled, "-Select");
}
protected override object[] Select(string entityName, IEnumerable <FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences, bool filterReferences, IDbConnection connection)
{
if (!m_entities.HasEntity(entityName))
{
throw new EntityNotFoundException(entityName);
}
UpdateIndexCacheForType(entityName);
//var genType = typeof(List<>).MakeGenericType(objectType);
//var items = (System.Collections.IList)Activator.CreateInstance(genType);
var items = new List <object>();
SqlEntityInfo entity = m_entities[entityName];
var isDynamicEntity = entity is DynamicEntityInfo;
if (connection == null)
{
connection = GetConnection(false);
}
SQLiteCommand command = null;
try
{
//Deprecated
//CheckOrdinals(entityName);
OnBeforeSelect(m_entities[entityName], filters, fillReferences);
var start = DateTime.Now;
bool tableDirect;
command = GetSelectCommand <SQLiteCommand, SQLiteParameter>(entityName, filters, firstRowOffset, fetchCount, out tableDirect);
command.Connection = connection as SQLiteConnection;
int searchOrdinal = -1;
// ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
// TODO: we need to ensure that the search value does not exceed the length of the indexed
// field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (results.HasRows)
{
var ordinals = GetOrdinals(entityName, results);
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = ordinals[matchField];
}
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
object item = null;
object rowPK = null;
if (isDynamicEntity)
{
var dynamic = new DynamicEntity(entity as DynamicEntityInfo);
foreach (var pair in ordinals)
{
if (entity.Fields[pair.Key].DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
pair.Key, entityName));
}
dynamic[pair.Key] = entity.Deserializer(dynamic, pair.Key, results[pair.Value]);
}
else
{
dynamic[pair.Key] = results[pair.Value];
}
}
item = dynamic;
}
else if (entity.CreateProxy == null)
{
if (entity.DefaultConstructor == null)
{
item = Activator.CreateInstance(entity.EntityType);
}
else
{
item = entity.DefaultConstructor.Invoke(null);
}
foreach (var field in Entities[entityName].Fields)
{
var value = results[ordinals[field.FieldName]];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = entity.Deserializer.Invoke(item, field.FieldName, value);
field.PropertyInfo.SetValue(item, @object, null);
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs <TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var valueAsTimeSpan = new TimeSpan((long)value);
field.PropertyInfo.SetValue(item, valueAsTimeSpan, null);
}
else if ((field.IsPrimaryKey) && (value is Int64) && (field.PropertyInfo.PropertyType.Equals(typeof(Int32))))
{
// SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if ((value is Int64) || (value is double))
{
// Work Around SQLite underlying storage type
if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
{
field.PropertyInfo.SetValue(item, Convert.ToUInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(Int32)))
{
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
{
field.PropertyInfo.SetValue(item, Convert.ToDecimal(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
{
field.PropertyInfo.SetValue(item, Convert.ToSingle(value), null);
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
//Check if it is reference key to set, not primary.
ReferenceAttribute attr = referenceFields.Where(
x => x.ReferenceField == field.FieldName).FirstOrDefault();
if (attr != null)
{
rowPK = value;
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
else
{
item = entity.CreateProxy(results, ordinals);
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false, filterReferences, connection);
}
items.Add(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
OnAfterSelect(m_entities[entityName], filters, fillReferences, DateTime.Now.Subtract(start), command.CommandText);
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
return(items.ToArray());
}
public void Dispose() => Command.Dispose();
