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();