private Theme FillTheme(SqliteDataReader reader)
{
Theme value = new Theme();
int index = reader.GetOrdinal("Id");
if (!reader.IsDBNull(index))
{
value.Id = reader.GetInt32(index);
}
index = reader.GetOrdinal("Title");
if (!reader.IsDBNull(index))
{
value.Title = reader.GetString(index);
}
index = reader.GetOrdinal("Description");
if (!reader.IsDBNull(index))
{
value.Description = reader.GetString(index);
}
index = reader.GetOrdinal("ParentId");
if (!reader.IsDBNull(index))
{
value.ParentId = reader.GetInt32(index);
}
index = reader.GetOrdinal("ParentTitle");
if (!reader.IsDBNull(index))
{
value.ParentTitle = reader.GetString(index);
}
index = reader.GetOrdinal("WithText");
if(!reader.IsDBNull(index))
{
value.WithText = reader.GetBoolean(index);
}
return value;
}
private Painting FillPainting(SqliteDataReader reader)
{
Painting value = new Painting();
int index = reader.GetOrdinal("Id");
if (!reader.IsDBNull(index))
{
value.Id = reader.GetInt32(index);
}
index = reader.GetOrdinal("Title");
if (!reader.IsDBNull(index))
{
value.Title = reader.GetString(index);
}
index = reader.GetOrdinal("ThemeId");
if (!reader.IsDBNull(index))
{
value.ThemeId = reader.GetInt32(index);
}
index = reader.GetOrdinal("ThemeTitle");
if (!reader.IsDBNull(index))
{
value.ThemeTitle = reader.GetString(index);
}
index = reader.GetOrdinal("Description");
if (!reader.IsDBNull(index))
{
value.Description = reader.GetString(index);
}
index = reader.GetOrdinal("Filename");
if (!reader.IsDBNull(index))
{
value.Filename = reader.GetString(index);
}
index = reader.GetOrdinal("OnSlider");
if (!reader.IsDBNull(index))
{
value.OnSlider = reader.GetBoolean(index);
}
index = reader.GetOrdinal("Price");
if (!reader.IsDBNull(index))
{
value.Price = reader.GetInt32(index);
}
index = reader.GetOrdinal("Available");
if (!reader.IsDBNull(index))
{
value.Available = reader.GetBoolean(index);
}
return value;
}
private Event FillEvent(SqliteDataReader reader)
{
Event value = new Event();
int index = reader.GetOrdinal("Id");
if (!reader.IsDBNull(index))
{
value.Id = reader.GetInt32(index);
}
index = reader.GetOrdinal("Title");
if (!reader.IsDBNull(index))
{
value.Title = reader.GetString(index);
}
index = reader.GetOrdinal("Description");
if (!reader.IsDBNull(index))
{
value.Description = reader.GetString(index);
}
index = reader.GetOrdinal("Modification");
if (!reader.IsDBNull(index))
{
string modification = reader.GetString(index);
value.ModificationDate = DateTime.ParseExact(modification, DateTimeFormat, CultureInfo.InvariantCulture);
}
index = reader.GetOrdinal("Expiration");
if (!reader.IsDBNull(index))
{
string expiration = reader.GetString(index);
value.ExpirationDate = DateTime.ParseExact(expiration, DateTimeFormat, CultureInfo.InvariantCulture);
}
return value;
}
protected override void OnCreate(Bundle bundle) {
base.OnCreate(bundle);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
// Get our button from the layout resource,
// and attach an event to it
Button button = FindViewById<Button>(Resource.Id.MyButton);
button.Click += delegate { button.Text = string.Format("{0} clicks!", count++); };
#region ADDED TO SAMPLE TO DEMONSTRATE Portable.Data.Sqlite
string myTableName = "TestTable1";
string sql;
//Instantiate my "crypt engine"
this.AppCryptEngine = this.AppCryptEngine ?? new FakeCryptEngine(this.AppPassword);
#region Part 1 - ADO - Create a table, add a record, add a column, add encrypted data, read back data
using (var dbConn = new SqliteAdoConnection(this.SqliteConnection, this.AppCryptEngine)) {
Console.WriteLine("PART 1 - Doing ADO stuff");
//Create the table if it doesn't exist
sql = "CREATE TABLE IF NOT EXISTS " + myTableName + " (IdColumn INTEGER PRIMARY KEY AUTOINCREMENT, DateTimeColumn DATETIME, TextColumn TEXT);";
using (var cmd = new SqliteCommand(sql, dbConn)) {
dbConn.SafeOpen();
cmd.ExecuteNonQuery();
Console.WriteLine("Table [" + myTableName + "] created (if it didn't exist).");
}
//Add a record
sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn) VALUES (@date, @text);";
int newRowId;
using (var cmd = new SqliteCommand(sql, dbConn)) {
cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
cmd.Parameters.Add(new SqliteParameter("@text", "Hello SQLite."));
dbConn.SafeOpen();
newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId()); //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
Console.WriteLine("A record with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
}
//Read the datetime column on the oldest record
sql = "SELECT [DateTimeColumn] FROM " + myTableName + " ORDER BY [DateTimeColumn] LIMIT 1;";
using (var cmd = new SqliteCommand(sql, dbConn)) {
dbConn.SafeOpen();
DateTime oldest = Convert.ToDateTime(cmd.ExecuteScalar());
Console.WriteLine("The oldest record in table [" + myTableName + "] has timestamp: " + oldest.ToString());
}
//Add an encrypted column to the table
//NOTE: There is no benefit to creating the column as SQLite datatype ENCRYPTED vs. TEXT
// It is actually a TEXT column - but I think it is nice to set it to type ENCRYPTED for future purposes.
// Hopefully a future version of SQLitePCL will make it easy to figure out if a column is defined as ENCRYPTED or TEXT
// (right now, it identifies both as TEXT)
sql = "ALTER TABLE " + myTableName + " ADD COLUMN EncryptedColumn ENCRYPTED;";
//Note: This column shouldn't exist until the above sql is run, since I just created the table above. But if this application has been run multiple times,
// the column may already exist in the table - so I need to check for it.
bool columnAlreadyExists = false;
#region Check for column
using (var checkCmd = new SqliteCommand(dbConn)) {
checkCmd.CommandText = "PRAGMA table_info (" + myTableName + ");";
dbConn.SafeOpen();
using (var checkDr = new SqliteDataReader(checkCmd)) {
while (checkDr.Read()) {
if (checkDr.GetString("NAME") == "EncryptedColumn") {
Console.WriteLine("The [EncryptedColumn] column already exists.");
columnAlreadyExists = true;
break;
}
}
}
}
#endregion
if (!columnAlreadyExists) {
using (var cmd = new SqliteCommand(sql, dbConn)) {
dbConn.SafeOpen();
cmd.ExecuteNonQuery();
Console.WriteLine("The [EncryptedColumn] column was created in table [" + myTableName + "].");
}
}
//Add a record with an encrypted column value
sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn, EncryptedColumn) VALUES (@date, @text, @encrypted);";
using (var cmd = new SqliteCommand(sql, dbConn)) {
cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
cmd.Parameters.Add(new SqliteParameter("@text", "Hello data."));
cmd.AddEncryptedParameter(new SqliteParameter("@encrypted",
Tuple.Create<string, string, string>("Hello", "encrypted", "data")));
dbConn.SafeOpen();
newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId()); //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
Console.WriteLine("A record featuring encrypted data with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
}
//Get the value of the encrypted column
sql = "SELECT [EncryptedColumn] FROM " + myTableName + " WHERE [IdColumn] = @id;";
using (var cmd = new SqliteCommand(sql, dbConn)) {
cmd.Parameters.Add(new SqliteParameter("@id", newRowId));
dbConn.SafeOpen();
string encryptedColumnValue = cmd.ExecuteScalar().ToString();
var decryptedValue = this.AppCryptEngine.DecryptObject<Tuple<string, string, string>>(encryptedColumnValue);
Console.WriteLine("The actual (encrypted) value of the [EncryptedColumn] column of record ID " + newRowId.ToString() + " is: " + encryptedColumnValue);
Console.WriteLine("The decrypted value of the [EncryptedColumn] column of record ID " + newRowId.ToString() + " is: " +
decryptedValue.Item1 + " " + decryptedValue.Item2 + " " + decryptedValue.Item3);
}
//Using a SqliteDataReader and GetDecrypted<T> to get all of the encrypted values
sql = "SELECT [IdColumn], [DateTimeColumn], [EncryptedColumn] FROM " + myTableName + ";";
using (var cmd = new SqliteCommand(sql, dbConn)) {
dbConn.SafeOpen();
using (var dr = new SqliteDataReader(cmd)) {
while (dr.Read()) {
var sb = new StringBuilder();
sb.Append("ID: " + dr.GetInt32("IdColumn").ToString());
sb.Append(" - Timestamp: " + dr.GetDateTime("DateTimeColumn").ToString());
//IMPORTANT: By default, GetDecrypted<T> will throw an exception on a NULL column value. You can specify DbNullHandling.ReturnTypeDefaultValue
// to return the default value of the specified type - as in default(T) - when a NULL column value is encountered, if you choose.
var decryptedValue = dr.GetDecrypted<Tuple<string, string, string>>("EncryptedColumn", DbNullHandling.ReturnTypeDefaultValue);
sb.Append(" - Value: " + ((decryptedValue == null) ? "NULL" :
decryptedValue.Item1 + " " + decryptedValue.Item2 + " " + decryptedValue.Item3));
Console.WriteLine(sb.ToString());
}
}
}
}
#endregion
#region Part 2 - EncryptedTable - Create an encrypted table to hold SampleDataItem objects, and read and write data
long numRecords;
using (var dbConn = new SqliteAdoConnection(this.SqliteConnection, this.AppCryptEngine)) {
Console.WriteLine(" ");
Console.WriteLine("PART 2 - Doing EncryptedTable stuff");
//Creating the encrypted table, adding some items/records
using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {
//Shouldn't need to call CheckDbTable manually, but I am going to check to see if there are
// records in the table, so I need to make sure the table exists
//This will check the table and create the table and/or any missing columns if needed.
encTable.CheckDbTable();
//Check to see how many records are in the table now
numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
Console.WriteLine("(1) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);
foreach (var item in ExampleData.GetData().Where(i => i.LastName != "Johnson")) {
encTable.AddItem(item);
}
Console.WriteLine("(2) There are currently {0} items to be written to the encrypted table: {1}",
encTable.TempItems.Where(i => i.IsDirty).Count(), encTable.TableName);
//Note that at this point in the code, nothing new has been written to the table yet.
// The table will be updated on encTable.Dispose (in this case, that happens automatically at the end of this using() code
// block) or we could force it now with encTable.WriteItemChanges()
//encTable.WriteItemChanges();
}
//Adding a couple more records...
using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {
//Because encTable was disposed above, we should now see records in the table
numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
Console.WriteLine("(3) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);
//Here is one way to add an item to the table - immediately
// (no need to type out 'immediateWriteToTable:' - but just wanted to show what the 'true' was for)
encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Bob").Single(), immediateWriteToTable: true);
//Another way to add items to the table - wait until WriteItemChanges() or WriteChangesAndFlush() or encTable.Dispose()
// is called
encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Joan").Single(), immediateWriteToTable: false);
encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Ned").Single());
//Should only see one more record - Joan and Ned haven't been written yet
numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
Console.WriteLine("(4) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);
//Let's see which items we have in memory right now
foreach (var item in encTable.TempItems) {
Console.WriteLine("In memory: ID#{0} {1} {2} - Status: {3}", item.Id, item.FirstName, item.LastName, item.SyncStatus);
}
//We can use WriteItemChanges() - writes any in-memory item changes to the table
//encTable.WriteItemChanges();
//OR WriteChangesAndFlush() writes any in-memory items to the table, and then drops any in-memory items and/or in-memory index of the table
//Normally, only items that are out-of-sync with the table are written, forceWriteAll causes all items (whether they have changed or not)
// to be written
encTable.WriteChangesAndFlush(forceWriteAll: true);
//How many items in memory now?
Console.WriteLine("After WriteChangesAndFlush() there are now {0} items in memory.", encTable.TempItems.Count());
//How many records in the table?
numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
Console.WriteLine("After WriteChangesAndFlush() there are now {0} records in the table.", numRecords.ToString());
}
//Reading and searching for items/records
using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {
//Doing a GetItems() with an empty TableSearch (like the line below) will get all items
List<SampleDataItem> allItems = encTable.GetItems(new TableSearch());
foreach (var item in allItems) {
Console.WriteLine("In table: ID#{0} {1} {2}", item.Id, item.FirstName, item.LastName);
}
//Let's just get one item - exceptionOnMissingItem: true will throw an exception if the item wasn't found
// in the table; with exceptionOnMissingItem: false, we will just get a null
SampleDataItem singleItem = encTable.GetItem(allItems.First().Id, exceptionOnMissingItem: true);
Console.WriteLine("Found via ID: ID#{0} {1} {2}", singleItem.Id, singleItem.FirstName, singleItem.LastName);
//Because we did a full table GetItems() above, we should have a nice, searchable index of all of the
// items in the table. But let's check it and re-build if necessary
encTable.CheckFullTableIndex(rebuildIfExpired: true);
//Otherwise, we could just force a rebuild of the searchable index
// encTable.BuildFullTableIndex();
//So, the easy way to find matching items, based on the full table index is to pass in a TableSearch
List<SampleDataItem> matchingItems = encTable.GetItems(new TableSearch {
SearchType = TableSearchType.MatchAll, //Items must match all search criteria
MatchItems = {
new TableSearchItem("LastName", "Johnson", SearchItemMatchType.IsEqualTo),
new TableSearchItem("FirstName", "Ned", SearchItemMatchType.DoesNotContain)
}
});
foreach (var item in matchingItems) {
Console.WriteLine("Found via search: ID#{0} {1} {2}", item.Id, item.FirstName, item.LastName);
}
//Let's see what is in this "full table index" anyway
foreach (var item in encTable.FullTableIndex.Index) {
Console.WriteLine("Indexed item ID: " + item.Key.ToString());
foreach (var value in item.Value) {
Console.WriteLine(" - Searchable value: {0} = {1}", value.Key ?? "", value.Value ?? "");
}
}
//Let's remove/delete a record from the table (with immediate removal)
Console.WriteLine("Deleting record: ID#{0} {1} {2}", singleItem.Id, singleItem.FirstName, singleItem.LastName);
encTable.RemoveItem(singleItem.Id, immediateWriteToTable: true);
//Let's see what is actually in the table
Console.WriteLine("Records in the table " + encTable.TableName + " - ");
sql = "select * from " + encTable.TableName;
using (var cmd = new SqliteCommand(sql, dbConn)) {
using (SqliteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
var record = new StringBuilder();
foreach (Portable.Data.Sqlite.TableColumn column in encTable.TableColumns.Values.OrderBy(tc => tc.ColumnOrder)) {
if (column.ColumnName == "Id") { record.Append(column.ColumnName + ": " + reader[column.ColumnName].ToString()); }
else {
record.Append(" - " + column.ColumnName + ": " + (reader[column.ColumnName].ToString() ?? ""));
}
}
Console.WriteLine(record.ToString());
}
}
}
}
}
#endregion
Console.WriteLine("Done.");
//Pop up a message saying we are done
var dialog = new AlertDialog.Builder(this);
dialog.SetMessage("If you can see this message, then the sample Portable.Data.Sqlite code ran correctly. " +
"Take a look at the code in the 'MainActivity.cs' file, and compare it to what you are seeing in the IDE Output window. " +
"And have a nice day...");
dialog.SetCancelable(false);
dialog.SetPositiveButton("OK", delegate { });
dialog.Create().Show();
#endregion
}
public static List <String> GetData()
{
List <String> entries = new List <string>();
using (SqliteConnection db =
new SqliteConnection("Filename=sqliteSample.db"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand
("SELECT Text_Entry from MyTable", db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
db.Close();
}
return(entries);
}
/// <summary>
/// Get a list of courses with the student's enrollment status.
/// </summary>
/// <returns>A list of courses with the student's enrollment status.</returns>
public List <StudentCourse> GetEnrollment()
{
List <StudentCourse> courses = new List <StudentCourse>();
SqliteConnection conn = DatabaseConnection.GetConnection();
conn.Open();
SqliteCommand command = conn.CreateCommand();
const string enrollmentSqlFormat =
"SELECT c.ID, c.Name, c.StartDate, c.EndDate, " +
"CASE WHEN sc.StudentID IS NOT NULL THEN 1 ELSE 0 END " +
"FROM Courses c " +
"LEFT JOIN (SELECT * FROM StudentCourses WHERE StudentID = {0}) sc " +
"ON c.ID = sc.CourseID";
command.CommandText = String.Format(enrollmentSqlFormat, Id);
SqliteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime startDate = reader.GetDateTime(2);
DateTime endDate = reader.GetDateTime(3);
bool isEnrolled = reader.GetBoolean(4);
courses.Add(new StudentCourse(id, name, startDate, endDate, Id, isEnrolled));
}
// clean up
reader.Close();
conn.Close();
return(courses);
}
//关闭数据库
public void Disconnect()
{
if (command != null)
{
command.Dispose();
}
command = null;
if (dataReader != null)
{
dataReader.Dispose();
}
dataReader = null;
if (connection != null)
{
connection.Close();
Debug.Log("Disconnected db.");
}
connection = null;
}
public static List <Vozilo> DohvatiSvaVozila()
{
var lista = new List <Vozilo>();
SqliteCommand c = BazaPodataka.con.CreateCommand();
c.CommandText = string.Format(@"SELECT id, marka, model, godiste, registracija, tip
FROM Vozila" );
SqliteDataReader reader = c.ExecuteReader();
while (reader.Read())
{
Vozilo auto = new Vozilo();
auto.id = (long)reader["id"];
auto.Marka = (string)reader["marka"];
auto.Model = (string)reader["model"];
auto.godiste = (long)reader["godiste"];
auto.Registracija = (string)reader["registracija"];
auto.Tip = Vozilo.GetTipVrsta((long)reader["tip"]);
lista.Add(auto);
}
c.Dispose();
return(lista);
}
public DateTime?GetLastUpdatedAt(int companyId)
{
using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
{
db.Open();
try
{
SqliteCommand selectCommand = new SqliteCommand("SELECT COUNT(*) from BusinessPartnerTypes WHERE CompanyId = @CompanyId AND IsSynced = 1", db);
selectCommand.Parameters.AddWithValue("@CompanyId", companyId);
SqliteDataReader query = selectCommand.ExecuteReader();
int count = query.Read() ? query.GetInt32(0) : 0;
if (count == 0)
{
return(null);
}
else
{
selectCommand = new SqliteCommand("SELECT MAX(UpdatedAt) from BusinessPartnerTypes WHERE CompanyId = @CompanyId AND IsSynced = 1", db);
selectCommand.Parameters.AddWithValue("@CompanyId", companyId);
query = selectCommand.ExecuteReader();
if (query.Read())
{
int counter = 0;
return(SQLiteHelper.GetDateTimeNullable(query, ref counter));
}
}
}
catch (Exception ex)
{
MainWindow.ErrorMessage = ex.Message;
}
db.Close();
}
return(null);
}
private Equipo ParseEquipo(SqliteDataReader reader)
{
var Equipo = new Equipo();
var i = 0;
unchecked
{
Equipo.ID = (int)reader.GetInt64(i++);
Equipo.NOMBRE = reader.GetString(i++);
Equipo.AULA = reader.GetString(i++);
Equipo.AREA = reader.GetString(i++);
Equipo.Fecha_adquisicion_hw = reader.GetString(i++);
Equipo.garantia = reader.GetString(i++);
Equipo.ficha_tecnica_hd = reader.GetString(i++);
Equipo.num_provee = (int)reader.GetInt64(i++);
Equipo.conectividad_red = reader.GetString(i++);
Equipo.licencia_hw = reader.GetString(i++);
Equipo.TIPO = reader.GetString(i++);
return(Equipo);
}
}
public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
{
//No era necesario pero bueno.. por si acaso
int titulodevolver = 0;
SqliteConnection conexion = new SqliteConnection("Data Source=peliculasbase.db");
conexion.Open();
SqliteCommand comando = conexion.CreateCommand();
comando.CommandText = "SELECT idPelicula FROM peliculas where titulo=@titulo";
comando.Parameters.Add("@titulo", SqliteType.Text);
comando.Parameters["@titulo"].Value = value.ToString();
SqliteDataReader lector = comando.ExecuteReader();
if (lector.HasRows)
{
while (lector.Read())
{
titulodevolver = int.Parse(lector["idPelicula"].ToString());
}
}
conexion.Close();
return(titulodevolver);
}
public String GetAlias(Int32 threadId, String bolded)
{
String rc = String.Empty;
// Check our thread.
String sql =
@"
SELECT poster.postername FROM Poster
JOIN Alias ON (Poster.posterid = Alias.posterid)
WHERE (threadid = @p1) AND (bolded = @p2)
LIMIT 1;
";
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
cmd.Parameters.Add(new SqliteParameter("@p2", bolded));
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
rc = r.GetString(0);
}
}
}
}
watch.Stop();
//Trace.TraceInformation("after GetAliasDB {0}", watch.Elapsed.ToString());
// Check other threads.
return(rc);
}
public IEnumerable <String> GetLivePlayers(Int32 threadId, Int32 postNumber)
{
List <String> players = new List <string>();
String sql =
@"SELECT Poster.postername
FROM Poster
INNER JOIN Player ON (Poster.posterid = Player.posterid)
INNER JOIN GameRole ON (Player.roleid = GameRole.roleid)
WHERE (GameRole.threadid = @p1)
AND ((Player.endtime IS NULL)
OR (Player.endtime > @p2))
ORDER BY Poster.postername ASC;";
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqliteConnection dbRead = new SqliteConnection(_connect))
{
dbRead.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
{
cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
cmd.Parameters.Add(new SqliteParameter("@p2", postNumber));
using (SqliteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
String player = r.GetString(0);
players.Add(player);
}
}
}
}
watch.Stop();
//Trace.TraceInformation("after GetPlayerList {0}", watch.Elapsed.ToString());
return(players);
}
GetSQliteData getSQliteData;//连接数据库类的引用
/// <summary>
/// 给小怪模型类赋值
/// </summary>
/// <param name="conditon">小怪等级</param>
/// <param name="value">值</param>
/// <returns></returns>
public LowMonsterModel LowMonster(string conditon, string value)
{
getSQliteData.OpenDB("Data Source=" + Path.Combine(Application.persistentDataPath, ConstData.dataBase)); //根据路径打开数据库
LowMonsterModel lowMonster = new LowMonsterModel(); //创建小怪模型类对象
SqliteDataReader reader = getSQliteData.GetDataReader(ConstData.T_lowMonster, conditon, value);
//读取数据
while (reader.Read())
{
lowMonster.Level = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.level)));
lowMonster.EmpiricValue = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.empiricValue)));
lowMonster.Name = reader.GetString(reader.GetOrdinal(ConstMonsterData.name));
lowMonster.Hp = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.hp)));
lowMonster.PhysicsAttack = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.physicsAttack)));
lowMonster.MagicAttack = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.magicAttack)));
lowMonster.Armor = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.armor)));
lowMonster.MagicResist = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.magicResist)));
lowMonster.AttackRate = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.attackRate)));
lowMonster.RecoverRate = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.recoverRate)));
lowMonster.Speed = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.speed)));
}
getSQliteData.CloseDB();//关闭数据库
return(lowMonster);
}
public static Dictionary <string, Dictionary <string, string> > GetData()
{
Dictionary <string, Dictionary <string, string> > allData;
Dictionary <string, string> hashDict = new Dictionary <string, string>()
{
{ "DestinyInventoryItemDefinition", "itemHash" }
};
using (SqliteConnection db =
new SqliteConnection("Filename=Manifest.content"))
{
db.Open();
allData = new Dictionary <string, Dictionary <string, string> >();
foreach (string tableName in hashDict.Keys)
{
SqliteCommand selectCommand = new SqliteCommand("SELECT json from " + tableName, db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
if (tableName.Equals("DestinyInventoryItemDefinition"))
{
if (!allData.Keys.Contains("DestinyInventoryItemDefinition"))
{
allData.Add("DestinyInventoryItemDefinition", new Dictionary <string, string>());
}
string json = query.GetString(0);
allData["DestinyInventoryItemDefinition"].Add(JsonConvert.DeserializeObject <DestinyInventoryItemRootObject>(json).hash + "", json);
}
}
}
db.Close();
}
return(allData);
}
/// <summary>Selects all projects.</summary>
/// <returns>All projects.</returns>
public List <Project> SelectProjects()
{
List <Project> Projects = new List <Project>();
string sql = "SELECT * FROM Projects ORDER BY ProjectId ASC; ";
using (SqliteCommand cmd = new SqliteCommand(sql, Connection))
{
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Project p = new Project();
p.ProjectId = (long)rdr["ProjectId"];
p.Name = rdr["Name"].ToString();
p.Description = rdr["Description"].ToString();
p.Created = DateTime.Parse((string)rdr["Created"]);
Projects.Add(p);
}
}
}
return(Projects);
}
/// <summary>Selects project by the name.</summary>
/// <returns>Project with given name.</returns>
/// <param name="name">Name of the project.</param>
public Project[] SelectProjectByName(string name)
{
Project[] Ret = new Project[1];
string sql = "SELECT * FROM Projects WHERE Name = '" + name + "' LIMIT 1; ";
using (SqliteCommand cmd = new SqliteCommand(sql, Connection))
{
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Project p = new Project();
p.ProjectId = (long)rdr["ProjectId"];
p.Name = rdr["Name"].ToString();
p.Description = rdr["Description"].ToString();
p.Created = DateTime.Parse((string)rdr["Created"]);
Ret[0] = p;
}
}
}
return(Ret);
}
public static List <LeaderBoard> GetUsersNotOnLeaderBoard(string dataSource)
{
List <LeaderBoard> leaderBoard = new List <LeaderBoard>();
using (SqliteConnection conn = new SqliteConnection(dataSource))
{
conn.Open();
SqliteCommand cmd = new SqliteCommand("SELECT * FROM Checkin WHERE UserName NOT IN (SELECT DISTINCT Username FROM LeaderBoard)", conn);
SqliteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var leaderBoardRow = new LeaderBoard
{
Username = rdr["Username"] == DBNull.Value ? "" : (string)rdr["Username"]
};
leaderBoard.Add(leaderBoardRow);
}
}
return(leaderBoard);
}
private List <Reservation> GetAllRows(SqliteDataReader reader)
{
List <Reservation> result = new List <Reservation>();
while (reader.HasRows)
{
while (reader.Read())
{
result.Add(new Reservation
{
reservation_id = reader.GetInt32(0),
reservation_date = DateTime.Parse(reader.GetString(1)),
reservation_time = TimeSpan.Parse(reader.GetString(2)),
client_id = reader.GetInt32(3),
services_id = reader.GetInt32(4),
employee_id = reader.GetInt32(5)
});
}
reader.NextResult();
}
return(result);
}
protected override void ParseData(SqliteDataReader reader)
{
mInfos.Clear();
DBFightEfffectLayoutItem info;
if (reader != null)
{
if (reader.HasRows == true)
{
while (reader.Read())
{
info = new DBFightEfffectLayoutItem();
info.FightTypeStr = GetReaderString(reader, "type");
info.Level = DBTextResource.ParseI_s(GetReaderString(reader, "level"), 0);
if (info.FightTypeStr != null && mInfos.ContainsKey(info.FightTypeStr) == false)
{
mInfos[info.FightTypeStr] = info;
}
}
}
}
}
// Funkcja dodaje zeskanowany produkt do listy produktów
public bool add_product_to_list(string RFID, bool storno)
{
// Wyszukanie w bazie danych produktu oznaczonego zeskanowanym tagiem RFID
// Zapytanie SQL:
string sql = "select * from Produkty where RFID like '%" + RFID + "%'";
// Umieszczenie zapytania SQL w komedzie SQLite
SqliteCommand command = new SqliteCommand(sql, db);
// Wykonanie zapytania na bazie danych
SqliteDataReader reader = command.ExecuteReader();
// Odczyt rezultatu zapytania z bazy danych
if (reader.Read())
{
string prefix = "";
if (storno)
{
prefix = "-";
}
// Utworzenie obiektu pojedynczego produktu na podstawie dancy z bazy danych
Produkt p = new Produkt(reader["Name"].ToString(), prefix + reader["Price"].ToString(), reader["RFID"].ToString(), reader["VAT"].ToString());
// Dodanie produktu do listy zakupów
zakupy.Add(p);
// Zwraca true jeżeli się powiodło
return(true);
}
else
{
// Zwraca false, jeśli się nie powiodło
return(false);
}
}
private void OnGUI()
{
if (GUILayout.Button("Insert 10W"))
{
StrayFogSQLiteHelper helper = new StrayFogSQLiteHelper(mDbPath);
while (mInsertNum > 0)
{
helper.ExecuteQuery("INSERT INTO AsmdefMap VALUES (1,'AAAAAAAA','BBBBBBBB','CCCCCCC','DDDDDD','EEEEEE',0)");
mInsertNum--;
}
}
GUILayout.Label("Insert Num =>" + mInsertNum);
if (GUILayout.Button("Read SQLite"))
{
watch.Reset();
watch.Start();
StrayFogSQLiteHelper helper = new StrayFogSQLiteHelper(mDbPath);
SqliteDataReader reader = helper.ReadFullTable("AsmdefMap");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
sbTableColumn.Append(reader.GetName(i) + ",");
sbTableValue.Append(reader.GetValue(i).ToString());
}
}
reader.Close();
watch.Stop();
}
GUILayout.Label("Time=>" + watch.ElapsedMilliseconds);
mScrollViewPosition = GUILayout.BeginScrollView(mScrollViewPosition);
GUILayout.Label("Connect String=>" + mDbPath);
GUILayout.Label("Table Columns=>" + sbTableColumn.ToString());
GUILayout.Label("Table Values=>" + sbTableValue.ToString());
GUILayout.EndScrollView();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseConnection()
{
//销毁Command
if (dbCommand != null)
{
dbCommand.Cancel();
}
dbCommand = null;
//销毁Reader
if (dataReader != null)
{
dataReader.Close();
}
dataReader = null;
//销毁Connection
if (dbConnection != null)
{
dbConnection.Close();
dbConnection.Dispose();
}
dbConnection = null;
}
public IActionResult Query(string name)
{
if (String.IsNullOrEmpty(name))
{
ViewData["Error"] = "Name can't be empty";
return(View());
}
ViewData["Query"] = name;
List <Employee> employees = new List <Employee>();
// string queryString = "SELECT firstName, lastName, email FROM Employees WHERE firstName like '" + name + "%' or lastName like '" + name + "%' AND employeeNumber < 10000 LIMIT 5";
var queryString = "SELECT firstName, lastName, email FROM Employees WHERE firstName like '@name%' or lastName like '@name%' AND employeeNumber < 10000 LIMIT 5";
SqliteConnection connection = new SqliteConnection("Data Source=db.sqlite3");
using (SqliteCommand cmd = new SqliteCommand(queryString, connection))
{
connection.Open();
var sqlLiteParam = new SqliteParameter("@name", SqliteType.Text)
{
Value = name
};
cmd.Parameters.Add(sqlLiteParam);
using (SqliteDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
employees.Add(new Employee(dr.GetString(0), dr.GetString(1), dr.GetString(2)));
}
}
}
return(View(employees));
}
public UploadEntry GetNextUploadEntryToUpload()
{
using (SqliteConnection conn = new SqliteConnection(_connectionString))
{
conn.Open();
using (SqliteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT ROWID, * from UploadEntries
WHERE UploadedAt IS NULL
AND Cancelled = 0
ORDER BY PriorityNum DESC, CreatedAt ASC
LIMIT 1";
using (SqliteDataReader reader = cmd.ExecuteReader())
{
UploadEntry uploadEntry = null;
if (reader.Read())
{
uploadEntry = GetUploadEntryFromReader(reader);
}
return(uploadEntry);
}
}
}
}
void OnClick()
{
Debug.Log("Button Clicked!");
// 数据库文件路径
//string appDBPath = Application.persistentDataPath + "/test.db";
string appDBPath = Application.dataPath + "/test.db";
DbAccess db = new DbAccess(@"Data Source=" + appDBPath);
path = appDBPath;
// 创建test表
db.CreateTable("test", new string[] { "name", "qq", "mail", "www" }, new string[] { "text", "text", "text", "text" });
// 插入数据,插入字符串加上'' 不然会报错
db.InsertInto("test", new string[] { "'哈哈哈'", "'1213213213'", "'*****@*****.**'", "'www.jianshu.com'" });
db.InsertInto("test", new string[] { "'嘿嘿嘿'", "'1241124214'", "'*****@*****.**'", "'www.csdn.com'" });
db.InsertInto("test", new string[] { "'嘻嘻嘻'", "'1235667767'", "'*****@*****.**'", "'www.manew.com'" });
// 删掉数据
db.Delete("test", new string[] { "mail", "mail" }, new string[] { "'*****@*****.**'", "'*****@*****.**'" });
// 查询数据
using (SqliteDataReader sqReader = db.SelectWhere("test", new string[] { "name", "mail" }, new string[] { "qq" }, new string[] { "=" }, new string[] { "1235667767" })) {
while (sqReader.Read())
{
//目前中文无法显示
Debug.Log("name:" + sqReader.GetString(sqReader.GetOrdinal("name")));
Debug.Log("mail:" + sqReader.GetString(sqReader.GetOrdinal("mail")));
name = sqReader.GetString(sqReader.GetOrdinal("name"));
mail = sqReader.GetString(sqReader.GetOrdinal("mail"));
}
sqReader.Close();
}
db.CloseSqlConnection();
}
public static Vozilo DohvatiPoRegistraciji(string registracija)
{
SqliteCommand c = BazaPodataka.con.CreateCommand();
c.CommandText = string.Format(@"SELECT id, marka, model, godiste, registracija, tip
FROM Vozila
WHERE registracija = '{0}'" , registracija);
SqliteDataReader reader = c.ExecuteReader();
Vozilo auto = new Vozilo();
while (reader.Read())
{
auto.id = (long)reader["id"];
auto.Marka = (string)reader["marka"];
auto.Model = (string)reader["model"];
auto.godiste = (long)reader["godiste"];
auto.Registracija = (string)reader["registracija"];
auto.Tip = Vozilo.GetTipVrsta((long)reader["tip"]);
}
c.Dispose();
return(auto);
}
/// <summary>
/// This will grab all the urdf types that is known in the database
/// </summary>
/// <returns>A list of type of urdf in the database.</returns>
public List <UrdfTypeModel> GetUrdfTypes()
{
List <UrdfTypeModel> list = new List <UrdfTypeModel>();
string sql = "SELECT `uid`, `name` FROM `tblUrdfType`;";
SqliteCommand cmd = _engine.conn.CreateCommand();
cmd.CommandText = sql;
SqliteDataReader reader = cmd.ExecuteReader();
UrdfTypeModel item;
try
{
while (reader.Read())
{
item = new UrdfTypeModel();
item.uid = reader.GetInt32(0);
item.name = reader.GetString(1);
list.Add(item);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally
{
reader.Close();
reader = null;
cmd.Dispose();
cmd = null;
}
return(list);
}
//public static List<String> GetData()
public static ArrayList GetData() // Return List to ArrayList Using Collection for show MessageBox
{
//List<String> entries = new List<string>();
ArrayList entries = new ArrayList();
using (SqliteConnection db =
new SqliteConnection($"Filename=sqliteSample.db"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand
("SELECT CustID, first_Name, last_Name, email from MyCustomers", db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
entries.Add("Name " + query.GetString(1) + "Last Name " + query.GetString(2) + "Email " + query.GetString(3));
}
db.Close();
}
return(entries);
}
// for saving the schedule to user //
public void ReturnLatestSchedule()
{
Schedules.Clear();
using (SqliteConnection con = new SqliteConnection(cs))
{
con.Open();
string sql = "SELECT * FROM CameraSchedule WHERE GhostID ='" + GhostID + "' ORDER BY CameraScheduleID desc LIMIT 1";
using (SqliteCommand cmd = new SqliteCommand(sql, con))
{
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Schedules.Add(Convert.ToString(reader.GetValue(i)));
}
}
reader.Close();
}
con.Close();
UpdateSchedules();
}
}
protected override void ParseData(SqliteDataReader reader)
{
if (reader == null || !reader.HasRows)
{
return;
}
m_WidgetInfos.Clear();
while (reader.Read())
{
var id = DBTextResource.ParseUI(GetReaderString(reader, "id"));
var window = GetReaderString(reader, "window");
var path = GetReaderString(reader, "path");
var is_audit = DBTextResource.ParseUI(GetReaderString(reader, "is_audit"));
if (m_WidgetInfos.ContainsKey(window))
{
var list = m_WidgetInfos[window];
WidgetInfo info = new WidgetInfo();
info.path = path;
info.is_audit = is_audit == 1;
info.id = id;
list.Add(info);
}
else
{
var list = new List <WidgetInfo>();
WidgetInfo info = new WidgetInfo();
info.path = path;
info.is_audit = is_audit == 1;
info.id = id;
list.Add(info);
m_WidgetInfos[window] = list;
}
}
}
public List <Zone> GetPartitionsZones(int PartitionId)
{
List <Zone> result = new List <Zone>();
try{
connection.Open();
using (var c = connection.CreateCommand()) {
c.CommandText = "Select * from Zone where PartitionId=@PartitionId";
c.CommandType = CommandType.Text;
c.Parameters.AddWithValue("@PartitionId", PartitionId);
SqliteDataReader reader = c.ExecuteReader();
while (reader.Read())
{
result.Add(MapZoneFromReader(reader));
}
}
}
catch (Exception) {}
finally{
connection.Close();
}
return(result);
}
public static string SQLiteGet(string sql)
{
string outstr = "";
using (SqliteConnection con = new SqliteConnection("DataSource=" + JustTokenClass.SQLiteDBPatch + ";"))
{
con.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, con))
{
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
outstr += rdr.GetString(0);
}
}
}
con.Close();
}
return(outstr);
}
public List <string> ReturnGhostNames()
{
using (SqliteConnection con = new SqliteConnection(cs))
{
con.Open();
string sql = "SELECT gt.Name FROM GhostType as gt";
using (SqliteCommand cmd = new SqliteCommand(sql, con))
{
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
GhostNames.Add(Convert.ToString(reader.GetValue(i)));
}
}
reader.Close();
}
con.Close();
return(GhostNames);
}
}
