public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings)
{
_database = database;
_settings = settings;
_connection = _database.ConnectionPool.GetConnection();
_transaction = _connection.BeginTransaction(level);
}
public static Rider LoadAny(string userId)
{
Rider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select r.UserId as UserId, r.Username as Username, r.BikeWheelSizeMm as BikeWheelSizeMm, r.Turbo as Turbo, " +
"r.TurboIsCalibrated as TurboIsCalibrated, r.EstimatedPower as EstimatedPower " +
"From cycli_riders r " +
"where UserId=@u1 and AccountStatus='Active'" +
"union " +
"select r.UserId as UserId, r.Username as Username, 700 as BikeWheelSizeMm, null as Turbo, " +
"'False' as TurboIsCalibrated, 'False' as EstimatedPower " +
"From cycli_virtual_riders r " +
"where UserId=@u2 and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql, "@u1", userId, "@u2",userId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new Rider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId= dr.Field<string>("UserId");
thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
thisRider.CurrentTurbo = dr.Field<string>("Turbo");
thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);
}
db.Close();
return thisRider;
}
public itempage1()
{
InitializeComponent();
try
{
var db = new SQLiteDatabase();
DataTable recipe;
String query = "select ID \"id\", NAME \"Description\",";
query += "CLIP \"Text\"";
query += "from CLIPBOARD;";
recipe = db.GetDataTable(query);
// The/ results can be directly applied to a DataGridView control
//dataGrid.DataContext = recipe;
/*
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
MessageBox.Show(r["Name"].ToString());
MessageBox.Show(r["Description"].ToString());
MessageBox.Show(r["Prep Time"].ToString());
MessageBox.Show(r["Cooking Time"].ToString());
}
*/
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public static RiderMessage[] GetMessages(string userId,string raceId)
{
List<RiderMessage> messages = new List<RiderMessage>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select m.RaceId, u.userid,u.username, m.message, m.SendTime " +
"from cycli_rider_messages m, cycli_riders u " +
"where m.SenderId = u.UserId and " +
"m.RaceId = @r " +
"order by m.SendTime";
DataTable dt = db.GetDataTable(sql,"@r",raceId, "@t", "fromTime");
db.Close();
foreach (DataRow dr in dt.Rows)
{
RiderMessage message = new RiderMessage();
message.SenderId = (string)dr["userid"];
message.Sender = (string)dr["username"];
message.RaceId = (string)dr["RaceId"];
message.Message = (string)dr["Message"];
message.SendTime = (long)(int)dr["SendTime"];
messages.Add(message);
}
return messages.ToArray();
}
static void DumpSqlite(string filename)
{
Serializer s = new Serializer();
try
{
var db = new SQLiteDatabase(filename);
List <String> tableList = db.GetTables();
List<SerializableDictionary<string, string>> dictList = new List<SerializableDictionary<string, string>>();
foreach (string table in tableList)
{
String query = string.Format("select * from {0};", table);
DataTable recipe = db.GetDataTable(query);
foreach (DataRow r in recipe.Rows)
{
SerializableDictionary<string, string> item = new SerializableDictionary<string, string>();
foreach (DataColumn c in recipe.Columns)
{
item[c.ToString()] = r[c.ToString()].ToString();
}
dictList.Add(item);
}
s.Serialize(string.Format("{0}.xml", table), dictList, table);
dictList.Clear();
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message + "\n\n";
}
}
/// <summary>
/// Creates new instance of SQLiteVdbe class by compiling a statement
/// </summary>
/// <param name="query"></param>
/// <returns>Vdbe</returns>
public SQLiteVdbe(SQLiteDatabase db, String query)
{
vm = null;
// prepare and compile
Sqlite3.sqlite3_prepare_v2(db.Connection(), query, query.Length, ref vm, 0);
}
public static Friend[] Load(string userId)
{
List<Friend> friends = new List<Friend>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select * from (select r.UserId as UserId, r.UserName as UserName, " +
"r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
"f.Status as Status " +
"from cycli_riders r, cycli_friends f, cycli_turbos t " +
"where f.UserId = '" + userId + "' and r.UserId=f.FriendId " +
"and r.Turbo = t.Type " +
"union " +
"select r.UserId as UserId, r.UserName as UserName, "+
"r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
"f.Status as Status " +
"from cycli_riders r, cycli_friends f, cycli_turbos t " +
"where f.FriendId= '" + userId + "' and r.UserId=f.UserId "+
"and r.Turbo = t.Type " +
") order by UserName";
DataTable dtFriends = db.GetDataTable(sql);
foreach (DataRow dr in dtFriends.Rows)
{
Friend f = new Friend()
{
UserId = dr.Field<string>("UserId"),
UserName = dr.Field<string>("UserName"),
Turbo = dr.Field<string>("Turbo"),
Coefficients = new double[]{dr.Field<double>("Power_Model_C1"),dr.Field<double>("Power_Model_C2"),dr.Field<double>("Power_Model_C3")},
Status = (string)dr["Status"]
};
friends.Add(f);
}
return friends.ToArray();
}
public static bool EmployeeExists(string employeeID, SQLiteDatabase sql)
{
// notify user if card wasn't found
if (sql.GetDataTable("select * from employees where employeeID=" + employeeID.Trim() + ";").Rows.Count == 1)
return true;
else
return false;
}
public FormWarmup(SQLiteDatabase db)
{
InitializeComponent();
dbsqlite = db;
loadconfig();
//load from DB
LoadPhysical();
}
public frm_Main()
{
connect = new ConnectProlog();
InitializeComponent();
m_resources = new Resources();
m_database = new SQLiteDatabase("laptop.s3db");
}
private void InitializeTables(SQLiteDatabase db)
{
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
for(int i = 0; i < CREATE_Commands.Length; i++)
{
db.ExecuteNonQuery(CREATE_Commands[i]);
}
}
public static SQLiteDatabase Instance()
{
if (instance == null)
{
instance = new SQLiteDatabase();
}
return instance;
}
public DataAccess(string filename)
{
this.filename = filename;
database = new SQLiteDatabase(filename);
//Create database structure for new files
if (!File.Exists(filename))
{
CreateNewRunFile(filename);
}
}
public void ResetDatabases()
{
string password = Settings.Default.Password;
if (password.Length > 0)
password = SecurityExtensions.DecryptString(password, Encoding.Unicode.GetBytes(Settings.Default.Entropy)).ToInsecureString();
worldDatabase = new WorldDatabase(Settings.Default.Host, Settings.Default.Port, Settings.Default.User, password, Settings.Default.Database);
sqliteDatabase = new SQLiteDatabase("Resources/sqlite_database.db");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string sql = "select * from userInfo order by id desc limit 200";
SQLiteDatabase db = new SQLiteDatabase();
var datatable = db.GetDataTable(sql);
rptUserInfo.DataSource = datatable;
rptUserInfo.DataBind();
}
}
/// <summary>
/// Checks whether a specified account exists and whether the password supplied
/// is correct
/// </summary>
/// <param name="id">The ID of the student/teacher - Corresponds to the id field
/// in the DB</param>
/// <param name="pass">The password to check as a string</param>
/// <param name="stuteach">A string containing "students" or "teachers"</param>
/// <returns>true when account is correct, false otherwise</returns>
public static bool checkAccount(int id, string pass, string stuteach)
{
SQLiteDatabase db = new SQLiteDatabase();
string dbPass = db.ExecuteScalar(string.Format("SELECT hash FROM {0} WHERE id={1};", stuteach, id));
string passHash = Password.hashAsString(pass);
if (dbPass == passHash)
return true;
return false;
}
static void Main()
{
//Needed for log4net to read from the app.config
XmlConfigurator.Configure();
var db = new SQLiteDatabase(@"D:\Fun\Code\GitHub\Logger\Database\MyLogs.db;");
//Clearing previous test data
db.ClearTable("Log");
AddFakeData();
RetrieveAndDisplayData(db);
}
public Form1(int selectedMail, int action)
{
InitializeComponent();
// action:
// 0 = new mail
// 1 = reply
// 2 = forward
// 3 = send public key
string selectedSender = "unknown";
string selectedSubject = "unknown";
string selectedBody = "unknown";
if (action == 1 || action == 2)
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mail;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", Timestamp \"Timestamp\"";
query += "from Mails ";
query += "where ID = " + selectedMail + ";";
Mail = db.GetDataTable(query);
foreach (DataRow r in Mail.Rows)
{
selectedSender = r["Sender"].ToString();
selectedSubject = r["Subject"].ToString();
selectedBody = r["Body"].ToString();
}
}
switch (action)
{
case 1: // Reply
textBoxTo.Text = selectedSender;
textBoxSub.Text = "RE: " + selectedSubject;
break;
case 2: // Forward
textBoxTo.Text = "";
textBoxSub.Text = "FW: " + selectedSubject;
break;
case 3:
textBoxSub.Text = "My public key";
textBoxBody.Text = Properties.Settings.Default.RSAPublic;
textBoxPublicKey.Visible = false;
label1.Visible = false;
checkBoxEncrypt.Visible = false;
checkBoxRSA.Visible = false;
break;
default:
textBoxTo.Text = "";
textBoxSub.Text = "";
break;
}
}
//
// PRIVATE and INTERNAL
//
internal Vault()
{
databaseFileName = PLUGIN_FOLDER + Path.DirectorySeparatorChar + "vault.db";
debug("database file location:" + databaseFileName);
Dictionary<string, string > options = new Dictionary<string, string>();
options["Data Source"] = databaseFileName;
options["New"] = "True";
database = new SQLiteDatabase(options);
setup();
}
public static string ChangeFriendCode(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
string g = Guid.NewGuid().ToString();
string sqlFriendCode = "update cycli_riders set friendCode=@g where UserId = @u";
db.ExecuteNonQuery(sqlFriendCode, "@g", g, "@u", userId);
// This query checks that the user has not already been invited
db.Close();
return g;
}
private static void RetrieveAndDisplayData(SQLiteDatabase db)
{
var results = db.GetDataTable("SELECT * From Log");
//TODO: Use generics instead. Please don't go out there kicking a cute puppy because of this code :(
foreach (DataRow item in results.Rows)
{
Console.WriteLine("Level: {0} - Location: {1} - Date and Time: {2}",
item["Level"].ToString().ToUpper(), item["Location"], item["TimeStamp"]);
Console.WriteLine("Message: \n{0} \n", item["Message"]);
}
Console.ReadLine();
}
public Resources()
{
if (System.IO.File.Exists("laptop.s3db"))
m_database = new SQLiteDatabase("laptop.s3db");
else
{
OpenFileDialog op = new OpenFileDialog();
op.Filter = "Database File|*.s3db";
op.ShowDialog();
String FilePath = op.FileName;
m_database = new SQLiteDatabase(FilePath);
}
Insert();
}
private void button_Click(object sender, RoutedEventArgs e)
{
var db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("NAME", nameTextBox.Text);
data.Add("CLIP", clipBoardTB.Text);
try
{
db.Insert("clipboard", data);
}
catch (Exception crap)
{
MessageBox.Show(crap.Message);
}
}
/// <summary>
/// Class Constructor
/// </summary>
public Form_Login()
{
InitializeComponent();
// Check if the databas exists
if (System.IO.File.Exists("whatsapp_db.s3db"))
{
Database = new SQLiteDatabase("whatsapp_db.s3db");
}
else
{
MessageBox.Show("The database is missing (whatsapp_db.s3db), make sure it's in the same folder as the application.", "Error: Database missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public void Start()
{
_lastupdate = DateTime.Now;
_db = new SQLiteDatabase("|DataDirectory|app_data.sqlite;Version=3;");
TransportInfo localTransport = CreateTransport(Helpers.GetLocalIP(), 7202);
_app = new SIPApp(localTransport);
_app.RequestRecvEvent += AppRequestRecvEvent;
_app.ResponseRecvEvent += AppResponseRecvEvent;
const string scscfIP = "scscf.open-ims.test";
const int scscfPort = 6060;
SIPStack stack = CreateStack(_app, scscfIP, scscfPort);
stack.Uri = new SIPURI("*****@*****.**");
_localparty = new Address("<sip:[email protected]>");
StartTimer();
}
public Form1(int selectedMail, int action)
{
InitializeComponent();
// Actions:
// 0 = new mail
// 1 = reply
// 2 = forward
string selectedSender = "unknown";
string selectedSubject = "unknown";
string selectedBody = "unknown";
if (action == 1 || action == 2)
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mail;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", Timestamp \"Timestamp\"";
query += "from Mails ";
query += "where ID = " + selectedMail + ";";
Mail = db.GetDataTable(query);
foreach (DataRow r in Mail.Rows)
{
selectedSender = r["Sender"].ToString();
selectedSubject = r["Subject"].ToString();
selectedBody = r["Body"].ToString();
}
}
switch (action)
{
case 1: // Reply
textBoxTo.Text = selectedSender;
textBoxSub.Text = "RE: " + selectedSubject;
break;
case 2: // Forward
textBoxTo.Text = "";
textBoxSub.Text = "FW: " + selectedSubject;
break;
default:
textBoxTo.Text = "";
textBoxFrom.Text = "";
textBoxSub.Text = "";
break;
}
//MessageBox.Show(selectedMail.ToString() + " - " + action.ToString());
}
protected void Page_Init(object sender, EventArgs e)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select nationality from cycli_nationalities";
DataTable dtNationality = db.GetDataTable(sql);
foreach (DataRow dr in dtNationality.Rows)
{
nationality.Items.Add((string)(dr[0]));
}
sql = @"select type from cycli_turbos order by type";
DataTable dtTurbos = db.GetDataTable(sql);
foreach (DataRow dr in dtTurbos.Rows)
{
turbo.Items.Add((string)(dr[0]));
}
db.Close();
}
public ADMIN_PANEL_WINDOW(Szyfrator main)
{
InitializeComponent();
Form = main;
try
{
db = new SQLiteDatabase("C:\\Szyfrator\\Szyfrator_db");
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public int get_default_settings()
{
DataTable recipe = new DataTable();
try
{
db = new SQLiteDatabase();
String query = "select default_settings \"Default_settings\"";
query += " from default_set WHERE extra='239';";
recipe = db.GetDataTable(query);
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
return 0;
}
DataRow dr = recipe.Rows[0];
return Int32.Parse(dr[0].ToString());
}
public static bool ReturnResults(string SQLStatement, string DatabaseLocation, ref DataSet ds, out string message)
{
//Add a call here to a parser that will
//ensure the SQLStatement is properly formed
if (SQLStatement.ToLower().StartsWith("select") || SQLStatement.ToLower().StartsWith("pragma"))
{
SQLiteDatabase db = new SQLiteDatabase(DatabaseLocation);
ds = db.ExecuteQuery(SQLStatement);
message = string.Format( ds != null ? "ExecuteQuery: ok" : "ExecuteQuery Failed");
return ds == null;
}
else
{
int result = SQLiteDatabase.ExecuteNonQuery(SQLStatement);
ds = null;
message = string.Format("ExecuteNonQuery: Records Modified {0}", result);
return result > -1;
}
}
public TestQuery(SQLiteDatabase database) : base(database)
{
}
public virtual void ClearBindings() => SQLiteDatabase._sqlite3_clear_bindings(CheckDisposed());
public User(SQLiteDatabase db)
: base(db)
{
}
public static void Run(Query query, SQLiteDatabase database, Action <QueryResult> onCompleteAction)
{
_runner.Run(query, database, onCompleteAction);
}
public StaffingDb(Context context) : base(context, name: _DatabaseName, factory: null, version: 1) //Step 2;
{
myContext = context;
myDBObj = WritableDatabase;
}
public UserWithBlob(SQLiteDatabase db)
: base(db)
{
Photo = new SQLiteBlobObject(this, nameof(Photo));
}
public static async Task <IDictionary <TaxonRankType, ITaxon> > GetTaxaAsync(this SQLiteDatabase database, ISpecies species)
{
IDictionary <TaxonRankType, ITaxon> result = new Dictionary <TaxonRankType, ITaxon>();
result.Add(TaxonRankType.Species, species);
if (species.Genus != null)
{
result.Add(TaxonRankType.Genus, species.Genus);
}
if (result.ContainsKey(TaxonRankType.Genus))
{
ITaxon family = await database.GetTaxonAsync(result[TaxonRankType.Genus].ParentId, TaxonRankType.Family);
if (family != null)
{
result.Add(TaxonRankType.Family, family);
}
}
if (result.ContainsKey(TaxonRankType.Family))
{
ITaxon order = await database.GetTaxonAsync(result[TaxonRankType.Family].ParentId, TaxonRankType.Order);
if (order != null)
{
result.Add(TaxonRankType.Order, order);
}
}
if (result.ContainsKey(TaxonRankType.Order))
{
ITaxon @class = await database.GetTaxonAsync(result[TaxonRankType.Order].ParentId, TaxonRankType.Class);
if (@class != null)
{
result.Add(TaxonRankType.Class, @class);
}
}
if (result.ContainsKey(TaxonRankType.Class))
{
ITaxon phylum = await database.GetTaxonAsync(result[TaxonRankType.Class].ParentId, TaxonRankType.Phylum);
if (phylum != null)
{
result.Add(TaxonRankType.Phylum, phylum);
}
}
if (result.ContainsKey(TaxonRankType.Phylum))
{
ITaxon kingdom = await database.GetTaxonAsync(result[TaxonRankType.Phylum].ParentId, TaxonRankType.Kingdom);
if (kingdom != null)
{
result.Add(TaxonRankType.Kingdom, kingdom);
}
}
if (result.ContainsKey(TaxonRankType.Kingdom))
{
ITaxon domain = await database.GetTaxonAsync(result[TaxonRankType.Kingdom].ParentId, TaxonRankType.Domain);
if (domain != null)
{
result.Add(TaxonRankType.Domain, domain);
}
}
return(result);
}
public SQLiteColumnType GetColumnType(int index) => SQLiteDatabase._sqlite3_column_type(CheckDisposed(), index);
public override void OnCreate(SQLiteDatabase db)
{
throw new NotImplementedException();
}
public double GetColumnDouble(int index) => SQLiteDatabase._sqlite3_column_double(CheckDisposed(), index);
public string GetColumnName(int index)
{
var ptr = SQLiteDatabase._sqlite3_column_name16(CheckDisposed(), index);
return(ptr == IntPtr.Zero ? null : Marshal.PtrToStringUni(ptr));
}
public int GetColumnInt32(int index) => SQLiteDatabase._sqlite3_column_int(CheckDisposed(), index);
public long GetColumnInt64(int index) => SQLiteDatabase._sqlite3_column_int64(CheckDisposed(), index);
public virtual void Reset() => SQLiteDatabase._sqlite3_reset(CheckDisposed());
public static void Ensure(SQLiteDatabase db)
{
var tq = new TestQuery(db);
tq.Name = "bill";
tq.Age = 21;
tq.Department = "Accounting";
tq.IsAbsent = false;
tq.Options = UserOptions.HasDrivingLicense;
tq.MonthlySalary = 1200;
tq.OfficeLatitude = 49.310230;
tq.OfficeLongitude = 24.0923;
tq.StartDateUtc = new DateTime(1990, 1, 25);
tq.EndDateUtc = new DateTime(2019, 11, 5);
tq.UniqueId = new Guid("00000000-0000-0000-0000-000000000001");
tq.Save();
tq = new TestQuery(db);
tq.Name = "samantha";
tq.Age = 51;
tq.Department = "HR";
tq.IsAbsent = true;
tq.Options = UserOptions.HasDrivingLicense | UserOptions.IsAdmin;
tq.MonthlySalary = 2120;
tq.OfficeLatitude = 48.310230;
tq.OfficeLongitude = 23.0923;
tq.StartDateUtc = new DateTime(2000, 4, 13);
tq.UniqueId = new Guid("00000000-0000-0000-1000-000000000002");
tq.Save();
tq = new TestQuery(db);
tq.Name = "joe";
tq.Age = 32;
tq.Department = "HR";
tq.IsAbsent = true;
tq.Options = UserOptions.HasTruckDrivingLicense | UserOptions.HasDrivingLicense;
tq.MonthlySalary = 1532;
tq.OfficeLatitude = 47.310230;
tq.OfficeLongitude = 22.0923;
tq.StartDateUtc = new DateTime(2014, 10, 11);
tq.UniqueId = new Guid("00000000-0000-1000-1000-000000000003");
tq.Save();
tq = new TestQuery(db);
tq.Name = "will";
tq.Age = 34;
tq.Department = "HR";
tq.IsAbsent = true;
tq.Options = UserOptions.None;
tq.MonthlySalary = 1370;
tq.OfficeLatitude = 46.110230;
tq.OfficeLongitude = 25.1923;
tq.StartDateUtc = new DateTime(2005, 1, 4);
tq.EndDateUtc = new DateTime(2011, 4, 2);
tq.UniqueId = new Guid("00000000-5000-1000-1000-000000000004");
tq.Save();
tq = new TestQuery(db);
tq.Name = "leslie";
tq.Age = 44;
tq.Department = "Accounting";
tq.IsAbsent = true;
tq.Options = UserOptions.None;
tq.MonthlySalary = 2098;
tq.OfficeLatitude = 47.110230;
tq.OfficeLongitude = 26.1923;
tq.StartDateUtc = new DateTime(2001, 7, 20);
tq.UniqueId = new Guid("A0000000-5000-1000-1000-000000000005");
tq.Save();
tq = new TestQuery(db);
tq.Name = "bob";
tq.Age = 36;
tq.Department = "Accounting";
tq.IsAbsent = false;
tq.Options = UserOptions.HasDrivingLicense;
tq.MonthlySalary = 2138;
tq.OfficeLatitude = 48.109630;
tq.OfficeLongitude = 25.1923;
tq.StartDateUtc = new DateTime(2001, 5, 10);
tq.UniqueId = new Guid("A0000000-5000-1000-1000-000000000006");
tq.Save();
tq = new TestQuery(db);
tq.Name = "tom";
tq.MonthlySalary = decimal.MaxValue;
tq.Save();
}
public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
throw new NotImplementedException();
}
static void SafeMain(string[] args)
{
//DocProgram.Starter();
//return;
if (File.Exists("test.db"))
{
File.Delete("test.db");
}
using (var db = new SQLiteDatabase(""))
{
//db.Logger = new ConsoleLogger(true);
db.EnableStatementsCache = true;
db.CollationNeeded += OnCollationNeeded;
db.DefaultColumnCollation = nameof(StringComparer.OrdinalIgnoreCase);
var user = new User(db);
user.Name = "bob";
user.Email = "*****@*****.**";
db.Save(user);
//for (int i = 0; i < 10; i++)
//{
// ThreadPool.QueueUserWorkItem((state) =>
// {
// int ii = (int)state;
// db.SynchronizeSchema<SimpleUser>();
// TableStringExtensions.ToTableString(db.GetTable<SimpleUser>(), Console.Out);
// var su = new SimpleUser();
// su.Name = "toto";
// su.Email = "*****@*****.**";
// db.Save(su);
// db.GetTableRows<SimpleUser>().ToTableString(Console.Out);
// db.LoadAll<SimpleUser>().ToTableString(Console.Out);
// }, i);
//}
Console.ReadLine();
db.GetStatementsCacheEntries().ToTableString(Console.Out);
return;
//db.DeleteTable<UserWithBlob>();
//db.DeleteTable<Product>();
//db.Vacuum();
//db.SynchronizeSchema<TestQuery>();
//TestQuery.Ensure(db);
db.LoadAll <TestQuery>().ToTableString(Console.Out);
//db.BeginTransaction();
//for (int i = 0; i < 10; i++)
//{
// var c = db.CreateObjectInstance<UserWithBlob>();
// c.Email = "bob" + i + "." + Environment.TickCount + "@mail.com";
// c.Name = "Name" + i + DateTime.Now;
// c.Options = UserOptions.Super;
// db.Save(c);
// //c.Photo = File.ReadAllBytes(@"d:\temp\IMG_0803.JPG");
// //c.Photo.Save(@"d:\temp\IMG_0803.JPG");
// //var p = db.CreateObjectInstance<Product>();
// //p.Id = Guid.NewGuid();
// //p.User = c;
// //db.Save(p);
//}
//db.Commit();
//var table = db.GetTable<UserWithBlob>();
//if (table != null)
//{
// TableStringExtensions.ToTableString(table, Console.Out);
// //TableStringExtensions.ToTableString(table.GetRows(), Console.Out);
// var one = db.LoadAll<UserWithBlob>().FirstOrDefault();
// one.Photo.Load("test.jpg");
// TableStringExtensions.ToTableString(one, Console.Out);
//}
//var table2 = db.GetTable<Product>();
//if (table2 != null)
//{
// TableStringExtensions.ToTableString(table2, Console.Out);
// TableStringExtensions.ToTableString(table2.GetRows(), Console.Out);
//}
db.SetScalarFunction("toto", 1, true, (c) =>
{
c.SetResult("héllo world");
});
//db.Query<TestQuery>().Where(u => u.Department.Contains("h") || u.Department == "accounting").ToTableString(Console.Out);
//db.Query<TestQuery>().Where(u => u.Department.Substring(1) == "R" || u.Department.Substring(1) == "r").
// Select(u => new { D = u.Department }).ToTableString(Console.Out);
//db.Query<TestQuery>().Where(u => u.Department.Contains("r")).ToTableString(Console.Out);
//db.Query<TestQuery>().Where(u => u.StartDateUtc > DateTime.UtcNow).ToTableString(Console.Out);
var sc = StringComparison.CurrentCultureIgnoreCase;
var eq = EqualityComparer <string> .Default;
db.Query <TestQuery>().Where(u => u.Department.Contains("h", sc)).ToTableString(Console.Out);
db.Query <TestQuery>().Where(u => u.Department.Contains("h", sc)).OrderBy(u => u.Name).ThenByDescending(u => u.MonthlySalary).ToTableString(Console.Out);
string h = "h";
string r = "r";
//TableStringExtensions.ToTableString(db.GetTable<TestQuery>(), Console.Out);
db.GetTable <TestQuery>().Columns.ToTableString(Console.Out);
}
}
/// <summary>
/// Se crea el evento para el boton siguiente
/// Se valida el formulario de la pantalla de Impresión
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
///
private void BtnGuardar_Click(object sender, EventArgs e)
{
//verificando si hay un resultado para la notificacion
if (!string.IsNullOrEmpty(diligenciada))
{
//verificando codigo de resultado
if (listview.CheckedItemPosition >= 0)
{
if (diligenciada.Equals("true", StringComparison.Ordinal))
{
ManejoBaseDatos.Abrir();
ICursor mCursor = ManejoBaseDatos.Seleccionar("SELECT * FROM Imagenes WHERE Nombre='" + guidNotificando + "'");
//verificando si existe firma
if (mCursor.Count > 0)
{
//ManejoBaseDatos.Cerrar();
//verificando sin hay observaciones
if (!string.IsNullOrEmpty(observaciones))
{
Console.WriteLine("Guardando datos");
try
{
Console.WriteLine("Guardando datos para NotificacionCompletada");
Console.WriteLine("Codigo notificacion: " + codNotificacion.ToString());
db1 = SQLiteDatabase.OpenDatabase(dbPath, null, DatabaseOpenFlags.OpenReadwrite);
db1.ExecSQL("UPDATE Notificaciones SET Estado='NotificacionCompletada' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET PendienteSubir='S' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET FirmaNotificando='" + guidNotificando + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoCodigo=" + listview.CheckedItemPosition.ToString() + " WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoDescripcion='" + listview.GetItemAtPosition(listview.CheckedItemPosition).ToString() + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoDiligenciada='" + diligenciada + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET Observaciones='" + observaciones + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET FechaNotificacion='" + "2015-07-13T19:33:38.034Z" + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
//Console.WriteLine("Guid notificando: " + guidNotificando);
//Console.WriteLine("Resultado Codigo: " + listview.CheckedItemPosition.ToString());
//Console.WriteLine("Resultado Descripcion: " + listview.GetItemAtPosition(listview.CheckedItemPosition).ToString());
//Console.WriteLine("Resultado diligenciada: " + diligenciada);
//Console.WriteLine("observaciones: " + observaciones);
//Console.WriteLine("Fecha de notificacion: " + "2015-07-13T19:33:38.034Z");
//Console.WriteLine("Nombre de testigo: " + nombreTestigo);
if (!string.IsNullOrEmpty(nombreTestigo))
{
db1.ExecSQL("UPDATE Notificaciones SET FirmaTestigo='" + guidTestigo + "' WHERE CodigoNotificacion='" + codNotificacion + "';");
}
db1.Close();
Toast.MakeText(this.Activity, "Datos Guardados de forma exitosa", ToastLength.Short).Show();
}
catch (Exception ex) { Console.WriteLine("ERROR REGISTRANDO DATOS: " + ex.ToString()); }
}
else
{
Toast.MakeText(this.Activity, "Por favor agregue observaciones para poder registrar datos.", ToastLength.Long).Show();
}
}
else
{
//ManejoBaseDatos.Cerrar();
mCursor.Close();
Toast.MakeText(this.Activity, "Por favor solicite al notificando firmar el acta.", ToastLength.Long).Show();
}
mCursor.Close();
//
}
if (diligenciada.Equals("false", StringComparison.Ordinal))
{
if (listview.CheckedItemPosition >= 0)
{
if (!string.IsNullOrEmpty(observaciones))
{
//Guardando datos
db1 = SQLiteDatabase.OpenDatabase(dbPath, null, DatabaseOpenFlags.OpenReadwrite);
db1.ExecSQL("UPDATE Notificaciones SET Estado='NotificacionCompletada' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET PendienteSubir='S' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoCodigo=" + listview.CheckedItemPosition.ToString() + " WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoDescripcion='" + listview.GetItemAtPosition(listview.CheckedItemPosition).ToString() + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET ResultadoDiligenciada='" + diligenciada + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET Observaciones='" + observaciones + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.ExecSQL("UPDATE Notificaciones SET FechaNotificacion='" + "2015-07-13T19:33:38.034Z" + "' WHERE CodigoNotificacion=" + codNotificacion + ";");
db1.Close();
}
else
{
Toast.MakeText(this.Activity, "Por favor agregue observaciones para poder registrar datos.", ToastLength.Long).Show();
}
//
}
else
{
Toast.MakeText(this.Activity, "Por favor seleccione la razón del resultado seleccionado.", ToastLength.Long).Show();
}
}
}
else
{
Toast.MakeText(this.Activity, "Por favor seleccione la razón del resultado seleccionado.", ToastLength.Long).Show();
}
}
else
{
Toast.MakeText(this.Activity, "Por favor seleccione un resultado para la notificación.", ToastLength.Long).Show();
}
}
private void SetupDatabase()
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
db = new SQLiteDatabase(this.dataPath);
string query = "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='" + TABLE_DATASETS + "'";
stmt = db.Prepare(query);
if (stmt.Read() && stmt.Fields["count"].INTEGER == 0)
{
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - running create dataset");
db.Exec(
"CREATE TABLE " + TABLE_DATASETS + "("
+ DatasetColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ DatasetColumns.DATASET_NAME + " TEXT NOT NULL,"
+ DatasetColumns.CREATION_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_BY + " TEXT,"
+ DatasetColumns.STORAGE_SIZE_BYTES + " INTEGER DEFAULT 0,"
+ DatasetColumns.RECORD_COUNT + " INTEGER DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_TIMESTAMP + " INTEGER DEFAULT '0',"
+ DatasetColumns.LAST_SYNC_RESULT + " TEXT,"
+ "UNIQUE (" + DatasetColumns.IDENTITY_ID + ", "
+ DatasetColumns.DATASET_NAME + ")"
+ ")");
}
stmt.FinalizeStm();
query = "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='" + TABLE_RECORDS + "'";
stmt = db.Prepare(query);
if (stmt.Read() && stmt.Fields["count"].INTEGER == 0)
{
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - running create dataset");
db.Exec(
"CREATE TABLE " + TABLE_RECORDS + "("
+ RecordColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ RecordColumns.DATASET_NAME + " TEXT NOT NULL,"
+ RecordColumns.KEY + " TEXT NOT NULL,"
+ RecordColumns.VALUE + " TEXT,"
+ RecordColumns.SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.LAST_MODIFIED_BY + " TEXT,"
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.MODIFIED + " INTEGER NOT NULL DEFAULT 1,"
+ "UNIQUE (" + RecordColumns.IDENTITY_ID + ", " + RecordColumns.DATASET_NAME
+ ", " + RecordColumns.KEY + ")"
+ ")");
}
}
finally
{
if (stmt != null)
{
stmt.FinalizeStm();
}
}
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - completed setupdatabase");
}
}
/// <summary>
/// 创建一个数据提供程序实例
/// </summary>
/// <param name="connectionStringName"></param>
/// <returns></returns>
public static Database Create(string connectionStringName)
{
CheckUtil.ArgumentNotNullOrEmpty(connectionStringName, "connectionStringName");
DbConnDAL dal = new DbConnDAL();
var model = dal.FindOne(connectionStringName);
if (model == null)
{
throw new Exception(string.Format(Resources.Data.ConnectionStringNameNotFound, connectionStringName));
}
string connectionString = model.ConnectionString;
string providerName = model.ProviderName;
Database db = new SqlServerDatabase(connectionString);
DbProviderFactory providerFactory = null;
if (string.IsNullOrEmpty(providerName))
{
return(db);
}
//if (css.ProviderName == "System.Data.OleDb")
//{
// providerFactory = OleDbFactory.Instance;
//}
//else
//{
// providerFactory = DbProviderFactories.GetFactory(css.ProviderName);
//}
//if (providerFactory == null) throw new Exception(string.Format(Resources.Data.DataProviderNotFound, css.ProviderName));
switch (providerName)
{
//case "System.Data.SqlClient":
// break;
case "System.Data.Odbc":
db = new OdbcDatabase(connectionString);
break;
case "System.Data.OleDb":
db = new OleDbDatabase(connectionString);
break;
case "System.Data.OracleClient":
db = new OracleDatabase(connectionString);
break;
case "Oracle.ManagedDataAccess.Client":
db = new OracleDatabase(connectionString);
break;
case "Devart.Data.Oracle": //http://evget.com/zh-CN/product/954/feature.aspx http://www.devart.com/
case "DDTek.Oracle": //http://www.datadirect.com/index.html 由于删除了版权DLL，导致该功能可能无法使用。可在QQ群：122161138中下载source_lib.zip
providerFactory = DbProviderFactories.GetFactory(providerName);
db = new OracleDatabase(connectionString, providerFactory);
break;
case "System.Data.SQLite":
providerFactory = DbProviderFactories.GetFactory(providerName);
db = new SQLiteDatabase(connectionString, providerFactory);
break;
case "MySql.Data.MySqlClient":
providerFactory = DbProviderFactories.GetFactory(providerName);
db = new MySqlDatabase(connectionString, providerFactory);
break;
case "IBM.Data.DB2":
providerFactory = DbProviderFactories.GetFactory(providerName);
db = new DB2Database(connectionString, providerFactory);
break;
case "FirebirdSql.Data.FirebirdClient":
providerFactory = DbProviderFactories.GetFactory(providerName);
db = new FirebirdDatabase(connectionString, providerFactory);
break;
default:
break;
}
return(db);
}
public static async Task <ITaxon> GetTaxonAsync(this SQLiteDatabase database, string name, TaxonRankType rank)
{
return((await database.GetTaxaAsync(name, rank)).FirstOrDefault());
}
public SQLiteErrorCode BindParameter(int index, double value) => SQLiteDatabase._sqlite3_bind_double(CheckDisposed(), index, value);
public override void OnCreate(SQLiteDatabase db)
{
db.ExecSQL(CreateUserTableQuery);
}
public SQLiteErrorCode BindParameter(int index, long value) => SQLiteDatabase._sqlite3_bind_int64(CheckDisposed(), index, value);
public static void RegisterCustomCollators(SQLiteDatabase database)
{
NativeRegisterCustomCollators(database, Build.VERSION.SdkInt);
}
public SQLiteErrorCode BindParameterNull(int index) => SQLiteDatabase._sqlite3_bind_null(CheckDisposed(), index);
private static void NativeRegisterCustomCollators(SQLiteDatabase database, int sdkVersion
)
{
}
protected internal SqlFsNode(SQLiteDatabase db, SqlFsLocker fsLocker, FsID id)
{
this.db = db;
this.fsLocker = fsLocker;
this.id = id;
}
public SQLiteErrorCode BindParameterZeroBlob(int index, int size) => SQLiteDatabase._sqlite3_bind_zeroblob(CheckDisposed(), index, size);
public SQLiteErrorCode BindParameter(int index, bool value) => SQLiteDatabase._sqlite3_bind_int(CheckDisposed(), index, value ? 1 : 0);
