protected override void ValidateTable(IDbConnection connection, IEntityInfo entity)
{
// first make sure the table exists
if (!TableExists(entity.EntityAttribute.NameInStore))
{
CreateTable(connection, entity);
return;
}
var fieldData = new List <object[]>();
using (var command = new SQLiteCommand())
{
command.Connection = connection as SQLiteConnection;
command.CommandText = string.Format("PRAGMA table_info({0})", entity.EntityName);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var values = new object[6];
reader.GetValues(values);
fieldData.Add(values);
}
}
}
foreach (var field in entity.Fields)
{
// 0 = cid (column id)
// 1 = name
// 2 = type
// 3 = notnull
// 4 = dflt_value
// 5 = pk
var existing = fieldData.FirstOrDefault(f => string.Compare(f[1].ToString(), field.FieldName, true) == 0);
if (existing == null)
{
// field doesn't exist - we must create it
var alter = new StringBuilder(string.Format("ALTER TABLE {0} ", entity.EntityAttribute.NameInStore));
alter.Append(string.Format("ADD [{0}] {1} {2}",
field.FieldName,
GetFieldDataTypeString(entity.EntityName, field),
GetFieldCreationAttributes(entity.EntityAttribute, field)));
using (var command = new SQLiteCommand(alter.ToString(), connection as SQLiteConnection))
{
command.ExecuteNonQuery();
}
}
else
{
// TODO: verify field length, etc.
}
}
return;
}
public static DbDataReader ExecuteSelect(this DbConnection gdb, string sql, params object[] args)
{
MySqlConnection db = (MySqlConnection)gdb;
MySqlCommand cmd = db.CreateCommand();
GenerateSqlCommand(cmd, sql, args);
return(cmd.ExecuteReader());
}
public static void InsertObject <T> (this DbConnection gdb, T obj)
{
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(obj.GetType());
StringBuilder sql = new StringBuilder("INSERT INTO `");
sql.Append(map.Table).Append("` (");
foreach (string f in map.Keys)
{
if (f != map.IdentityField)
{
sql.Append(f).Append(',');
}
}
sql[sql.Length - 1] = ')';
sql.Append(" VALUES (");
foreach (var f in map)
{
if (f.Key == map.IdentityField)
{
continue;
}
string fp = "@_" + f.Key;
sql.Append(fp).Append(',');
cmd.Parameters.AddWithValue(fp, f.Value.GetValue(obj, null));
}
sql[sql.Length - 1] = ')';
if (map.IdentityField != null)
{
sql.Append("; SELECT @@IDENTITY");
}
cmd.CommandText = sql.ToString();
if (map.IdentityField == null)
{
cmd.ExecuteNonQuery();
}
else
{
using (DbDataReader dr = cmd.ExecuteReader()) {
if (dr.Read())
{
PropertyInfo prop = map[map.IdentityField];
object val = Convert.ChangeType(dr[0], prop.PropertyType);
prop.SetValue(obj, val, null);
}
else
{
throw new Exception("Insertion failed");
}
}
}
}
}
public override ChunkData Pull(ChunkDescriptor Chunk)
{
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand("SELECT PATH FROM CHUNKS WHERE ID = @p0", Base)) {
Comm.Parameters.AddWithValue("p0", Chunk.Hash);
Mono.Data.Sqlite.SqliteDataReader DR = Comm.ExecuteReader();
if (DR.Read())
{
return(new File(Chunk, this, DR.GetString(0)));
}
return(null);
}
}
protected void fillCbFlagWithAllFlagTypes()
{
dbConn.Open ();
String getAllFlagTypesQ = "select * from FlagTypes;";
try {
SQLiteCommand cmd = new SQLiteCommand(getAllFlagTypesQ, dbConn);
SQLiteDataReader reader = cmd.ExecuteReader();
while(reader.Read()){
cbFlag.AppendText (reader["title"].ToString());
}
} catch (SQLiteException e){
Console.Write (e.ToString());
}
dbConn.Close ();
}
//dbPathFile --> path file do banco de dados
//tablename --> nome da tabela
//resultCol --> coluna da tabela que sera retornada Ex."Nome"
//refCol --> coluna de referencia para comparação Ex."Senha"
//refData --> dado que sera comparado com a coluna de referencia Ex."123"
public String dbGetCommand(String dbPathFile, String tableName, String resultCol, String refCol, String refData)
{
int i = 0;
String resultModel = "";
String strQuery = "SELECT * FROM " + tableName;
ArrayList readArray = new ArrayList();
try
{
Mono.Data.Sqlite.SqliteConnection connection =
new Mono.Data.Sqlite.SqliteConnection("Data Source=" + dbPathFile);
connection.Open();
Mono.Data.Sqlite.SqliteCommand dbcmd = connection.CreateCommand();
dbcmd.CommandText = strQuery;
//SqliteDataReader rdr = cmd.ExecuteReader()
Mono.Data.Sqlite.SqliteDataReader rdr = dbcmd.ExecuteReader();
while (rdr.Read())
{
i++;
//if(rdr[refCol].ToString() == refData){
// resultModel = rdr[resultCol].ToString();
//}
if (rdr[refCol].ToString() == refData)
{
resultModel = rdr[resultCol].ToString();
}
}
connection.Close();
}
catch (Exception ex)
{
showMessage("Erro ao executar SqliteDataReader: " + ex.Message);
}
return(resultModel);
}
public static T ReadSettings <T> (this DbConnection gdb) where T : new ()
{
T obj = new T();
DbMap map = GetMap(typeof(T));
MySqlConnection db = (MySqlConnection)gdb;
Dictionary <string, PropertyInfo> readProps = new Dictionary <string, PropertyInfo> (map);
using (MySqlCommand cmd = db.CreateCommand()) {
cmd.CommandText = "SELECT * FROM `" + map.Table + "`";
using (DbDataReader dr = cmd.ExecuteReader()) {
while (dr.Read())
{
string fname = (string)dr["Key"];
string val = dr["Value"] as string;
PropertyInfo prop;
if (map.TryGetValue(fname, out prop))
{
object cval;
if (prop.PropertyType.IsEnum)
{
cval = Enum.Parse(prop.PropertyType, val);
}
else
{
cval = Convert.ChangeType(val, prop.PropertyType);
}
prop.SetValue(obj, cval, null);
readProps.Remove(fname);
}
}
}
}
foreach (PropertyInfo prop in readProps.Values)
{
DataMemberAttribute att = (DataMemberAttribute)Attribute.GetCustomAttribute(prop, typeof(DataMemberAttribute), true);
if (att != null && att.DefaultValue != null)
{
prop.SetValue(obj, att.DefaultValue, null);
}
}
return(obj);
}
public static IEnumerable <T> SelectObjects <T> (this DbConnection gdb, string sql, params object[] args) where T : new ()
{
List <T> res = new List <T> ();
MySqlConnection db = (MySqlConnection)gdb;
using (MySqlCommand cmd = db.CreateCommand()) {
DbMap map = GetMap(typeof(T));
if (sql == "*")
{
// Select all
sql = "SELECT * FROM `" + map.Table + "`";
}
else if (sql.StartsWith("*"))
{
// Select with a where
sql = "SELECT * FROM `" + map.Table + "` WHERE " + sql.Substring(1);
}
else if (sql == ".")
{
// Select by id
if (map.KeyFields.Length != 1)
{
throw new NotSupportedException();
}
sql = "SELECT * FROM `" + map.Table + "` WHERE " + map.KeyFields[0] + " = {0}";
}
GenerateSqlCommand(cmd, sql, args);
using (DbDataReader dr = cmd.ExecuteReader()) {
while (dr.Read())
{
res.Add(ReadObject <T> (map, dr));
}
}
}
return(res);
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if(!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if(ret)
{
using(SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if(cn1.State == System.Data.ConnectionState.Open)
{
using(SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if(cn2.State == System.Data.ConnectionState.Open)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
using(SQLiteTransaction tr = cn2.BeginTransaction())
{
try
{
List<long> add = new List<long>();
using(SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using(SQLiteDataReader rd = cmd.ExecuteReader())
{
while(rd.Read())
{
long id = rd.GetInt64(0);
using(SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using(SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if(!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach(long id in add)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch(Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using(SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch(Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return ret;
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if(File.Exists(file))
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
bool? NoCacheTimeColumn = null;
try
{
using(DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using(DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch(Exception ex)
{
if(ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if(NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return ret;
}
public SQLiteDataReader ExecuteReader()
{
return(new SQLiteDataReader(__command.ExecuteReader()));
}
public override ChunkData Pull(ChunkDescriptor Chunk)
{
using (Mono.Data.Sqlite.SqliteCommand Comm = new Mono.Data.Sqlite.SqliteCommand ("SELECT PATH FROM CHUNKS WHERE ID = @p0", Base)) {
Comm.Parameters.AddWithValue("p0", Chunk.Hash);
Mono.Data.Sqlite.SqliteDataReader DR = Comm.ExecuteReader ();
if(DR.Read())
{
return new File(Chunk, this, DR.GetString(0));
}
return null;
}
}
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();
}
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());
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if (File.Exists(file))
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
bool?NoCacheTimeColumn = null;
try
{
using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using (DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch (Exception ex)
{
if (ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return(ret);
}
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");
}
private bool _checkIfInputDataIsInDb()
{
string link = entryLink.Text.Trim();
if (link.Length > 0) {
string lookupCheckQ = "select count(*) as numberOfRows from Links where link='" + link + "';";
try {
SQLiteCommand cmd = new SQLiteCommand (lookupCheckQ, dbConn);
dbConn.Open ();
SQLiteDataReader reader = cmd.ExecuteReader ();
if (reader.Read ()) {
Console.WriteLine (reader ["numberOfRows"]);
if(Convert.ToInt32( reader["numberOfRows"]) >0){
dbConn.Close ();
return true;
} else {
dbConn.Close ();
return false;
}
} else {
dbConn.Close ();
return false;
}
} catch (Exception e) {
dbConn.Close ();
Console.WriteLine (e.ToString ());
return false;
}
} else {
return false;
}
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if (!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if (ret)
{
using (SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if (cn1.State == System.Data.ConnectionState.Open)
{
using (SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if (cn2.State == System.Data.ConnectionState.Open)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
using (SQLiteTransaction tr = cn2.BeginTransaction())
{
try
{
List <long> add = new List <long>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
long id = rd.GetInt64(0);
using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using (SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if (!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach (long id in add)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch (Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public void Execute() => Reader = Command.ExecuteReader();
