public bool IsValidCustomerLogin(string email, string password)
{
//encode password
string encoded_password = Encoder.Encode(password);
//check email/password
string sql = "select * from CustomerLogin where email = '" + email + "' and password = '******';";
using (SqliteConnection connection = new SqliteConnection(_connectionString))
{
connection.Open();
SqliteDataAdapter da = new SqliteDataAdapter(sql, connection);
//TODO: User reader instead (for all calls)
DataSet ds = new DataSet();
da.Fill(ds);
try
{
return ds.Tables[0].Rows.Count == 0;
}
catch (Exception ex)
{
//Log this and pass the ball along.
log.Error("Error checking login", ex);
throw new Exception("Error checking login", ex);
}
}
}
public static DataTable GetDataTable(string query)
{
DataSet tempDataSet = new DataSet();
SqliteDataAdapter tempAdapter = null;
try
{
__connection.Open();
tempAdapter = new SqliteDataAdapter(query, __connection);
tempDataSet.Reset();
tempAdapter.Fill(tempDataSet);
}
catch (Exception ex)
{
throw new InvalidOperationException("Error in the DataBase", ex);
}
finally
{
if (tempAdapter != null)
tempAdapter.Dispose();
__connection.Close();
}
return tempDataSet.Tables[0];
}
/// <summary>
/// Sets the handler for receiving row updating events. Used by the DbCommandBuilder to autogenerate SQL
/// statements that may not have previously been generated.
/// </summary>
/// <param name="adapter">A data adapter to receive events on.</param>
protected override void SetRowUpdatingHandler(DbDataAdapter adapter)
{
SqliteDataAdapter adp = (SqliteDataAdapter)adapter;
_handler = new EventHandler <RowUpdatingEventArgs>(RowUpdatingEventHandler);
adp.RowUpdating += _handler;
}
public static DataSet ExecuteDataset(string connectionString, string commandText, params IDataParameter[] commandParameters)
{
using (SqliteConnection connection = new SqliteConnection(connectionString))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand())
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = CommandType.Text;
if (commandParameters != null)
{
foreach (IDataParameter p in commandParameters)
{
command.Parameters.Add(p);
}
}
SqliteDataAdapter adapter = new SqliteDataAdapter(command);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables.Count == 0)
{
adapter.FillSchema(dataSet, SchemaType.Source);
}
return dataSet;
}
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
DataTable dtArtists = new DataTable();
#region fetch data for artists
Mono.Data.Sqlite.SqliteConnection cn = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
Mono.Data.Sqlite.SqliteCommand comm = new Mono.Data.Sqlite.SqliteCommand(cn);
Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
comm.CommandText = @"
SELECT name, id, fetched
FROM artists
";
adapter.Fill(dtArtists);
#endregion
if (dtArtists.Rows.Count == 0)
{
List <SubsonicItem> artists = Subsonic.GetIndexes();
foreach (SubsonicItem artist in artists)
{
DataRow dr = dtArtists.NewRow();
dr["name"] = artist.name;
dr["id"] = artist.id;
dr["feteched"] = DateTime.Now.ToString();
dtArtists.Rows.Add(dr);
comm = new Mono.Data.Sqlite.SqliteCommand(cn);
comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
comm.Parameters.AddWithValue("@name", artist.name);
comm.Parameters.AddWithValue("@id", artist.id);
comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
comm.ExecuteNonQuery();
}
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
rptArtists.DataSource = dtArtists;
rptArtists.DataBind();
}
public DataSet Get(string command)
{
DataSet dataSet = new DataSet ();
using (SqliteConnection sql_connection=new SqliteConnection(connectionString)) {
SqliteDataAdapter slda=new SqliteDataAdapter(command,sql_connection);
sql_connection.Open();
slda.Fill(dataSet);
sql_connection.Close();
}
return dataSet;
}
public DataSet GetCatalogData()
{
using (SqliteConnection connection = new SqliteConnection(_connectionString))
{
SqliteDataAdapter da = new SqliteDataAdapter("select * from Products", connection);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad (e);
DataTable dtArtists = new DataTable();
#region fetch data for artists
Mono.Data.Sqlite.SqliteConnection cn = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
Mono.Data.Sqlite.SqliteCommand comm = new Mono.Data.Sqlite.SqliteCommand(cn);
Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
comm.CommandText = @"
SELECT name, id, fetched
FROM artists
";
adapter.Fill(dtArtists);
#endregion
if (dtArtists.Rows.Count == 0)
{
List<SubsonicItem> artists = Subsonic.GetIndexes();
foreach (SubsonicItem artist in artists)
{
DataRow dr = dtArtists.NewRow();
dr["name"] = artist.name;
dr["id"] = artist.id;
dr["feteched"] = DateTime.Now.ToString();
dtArtists.Rows.Add(dr);
comm = new Mono.Data.Sqlite.SqliteCommand(cn);
comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
comm.Parameters.AddWithValue("@name", artist.name);
comm.Parameters.AddWithValue("@id", artist.id);
comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());
if (cn.State != ConnectionState.Open)
cn.Open();
comm.ExecuteNonQuery();
}
if (cn.State != ConnectionState.Closed)
cn.Close();
}
rptArtists.DataSource = dtArtists;
rptArtists.DataBind();
}
public override DataTable ExecuteTable (IDbCommand command)
{
if (command == null)
throw new ArgumentException ("command");
DataTable table = new DataTable ();
using (command) {
using (SqliteDataAdapter adapter = new SqliteDataAdapter (command as SqliteCommand)) {
try {
adapter.Fill (table);
} catch (Exception e) {
QueryService.RaiseException (e);
}
}
}
return table;
}
static SqliteDataAdapter PrepareDataAdapter()
{
SqliteCommand select = new SqliteCommand("SELECT t, f, i, b FROM t1",_conn);
SqliteCommand update = new SqliteCommand("UPDATE t1 SET t = :textP, f = :floatP, i = :integerP, n=:blobP WHERE t = :textP ");
update.Connection=_conn;
SqliteCommand delete = new SqliteCommand("DELETE FROM t1 WHERE t = :textP");
delete.Connection=_conn;
SqliteCommand insert = new SqliteCommand("INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)");
insert.Connection=_conn;
SqliteDataAdapter custDA = new SqliteDataAdapter(select);
SqliteParameter textP = new SqliteParameter();
textP.ParameterName = "textP";
textP.SourceColumn = "t";
SqliteParameter floatP = new SqliteParameter();
floatP.ParameterName = "floatP";
floatP.SourceColumn = "f";
SqliteParameter integerP = new SqliteParameter();
integerP.ParameterName ="integerP";
integerP.SourceColumn = "i";
SqliteParameter blobP = new SqliteParameter();
blobP.ParameterName = "blobP";
blobP.SourceColumn = "b";
update.Parameters.Add(textP);
update.Parameters.Add(floatP);
update.Parameters.Add(integerP);
update.Parameters.Add(blobP);
delete.Parameters.Add(textP);
insert.Parameters.Add(textP);
insert.Parameters.Add(floatP);
insert.Parameters.Add(integerP);
insert.Parameters.Add(blobP);
custDA.UpdateCommand = update;
custDA.DeleteCommand = delete;
custDA.InsertCommand = insert;
return custDA;
}
/// <summary>
/// ���ݿ�ִ��(����DataSet)
/// </summary>
/// <param name="connection">���ݿ����Ӵ�</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SqliteConnection connection, CommandType commandType, string commandText, params SqliteParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
SqliteCommand cmd = new SqliteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqliteTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
using (SqliteDataAdapter da = new SqliteDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
public static DataSet LookupCable(string item)
{
string cs = "URI=file:test.db";
string stm = "SELECT * FROM Cable";
DataSet ds;
using(SqliteConnection con = new SqliteConnection(cs))
{
con.Open();
ds = new DataSet();
using(SqliteDataAdapter da
= new SqliteDataAdapter(stm, con))
{
da.Fill(ds, "Cable");
}
con.Close();
}
return ds;
}
/// <summary>
///
/// </summary>
/// <param name="da"></param>
/// <param name="conn"></param>
private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
{
lock (ds)
{
da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
da.InsertCommand.Connection = conn;
da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
da.UpdateCommand.Connection = conn;
SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
delete.Connection = conn;
da.DeleteCommand = delete;
}
}
/// <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();
}
}
/// <summary>
/// Closes the inventory interface
/// </summary>
public void Dispose()
{
if (conn != null)
{
conn.Close();
conn = null;
}
if (invItemsDa != null)
{
invItemsDa.Dispose();
invItemsDa = null;
}
if (invFoldersDa != null)
{
invFoldersDa.Dispose();
invFoldersDa = null;
}
if (ds != null)
{
ds.Dispose();
ds = null;
}
}
/// <summary>
///
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="queryText">
/// A <see cref="System.String"/>
/// </param>
/// <param name="queryParams">
/// A <see cref="System.String[]"/>
/// </param>
/// <returns>
/// A <see cref="IResultSet"/>
/// </returns>
public override IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams)
{
IResultSet resultSet = null;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string queryString = queryText;
try
{
if (queryParams != null)
{
queryString = String.Format(queryString, queryParams);
}
DataSet ds = new DataSet();
SqliteDataAdapter adapter = new SqliteDataAdapter(queryString, (SqliteConnection)connection);
adapter.Fill(ds);
resultSet = new ResultSet(ds);
}
catch (Exception e)
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
}
return resultSet;
}
private void setupLandCommands(SqliteDataAdapter da, SqliteConnection conn)
{
da.InsertCommand = createInsertCommand("land", ds.Tables["land"]);
da.InsertCommand.Connection = conn;
da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]);
da.UpdateCommand.Connection = conn;
SqliteCommand delete = new SqliteCommand("delete from land where UUID=:UUID");
delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
da.DeleteCommand = delete;
da.DeleteCommand.Connection = conn;
}
public void Dispose()
{
if (m_conn != null)
{
m_conn.Close();
m_conn = null;
}
if (ds != null)
{
ds = null;
}
if (terrainDa != null)
{
terrainDa = null;
}
if (landDa != null)
{
landDa = null;
}
if (landAccessListDa != null)
{
landAccessListDa = null;
}
if (regionSettingsDa != null)
{
regionSettingsDa = null;
}
}
/// <summary>
/// Initializes the command builder and associates it with the specified data adapter.
/// </summary>
/// <param name="adp"></param>
public SqliteCommandBuilder(SqliteDataAdapter adp)
{
QuotePrefix = "[";
QuoteSuffix = "]";
DataAdapter = adp;
}
public DataSet GetCustomerEmails(string email)
{
string sql = "select email from CustomerLogin where email like '" + email + "%'";
using (SqliteConnection connection = new SqliteConnection(_connectionString))
{
connection.Open();
SqliteDataAdapter da = new SqliteDataAdapter(sql, connection);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
return null;
else
return ds;
}
}
/// <summary>
/// Creates a DbDataAdapter for SQLite.
/// </summary>
/// <remarks>
/// <b>Important:</b> Since an object that derives from DbDataAdapter is returned you ought to
/// <em>call .Dispose()</em> on the returned object to release its resouces! (DbDataAdapter inherits
/// from DataAdapter which itself inherits from Component, which implements IDisposable!)
/// </remarks>
/// <returns>Instantiated SqliteDataAdapter.</returns>
public DbDataAdapter NewAdapter()
{
DbDataAdapter TheAdapter = new SqliteDataAdapter();
return TheAdapter;
}
/// <summary>
/// Executing SQL Statement
/// (using Mono/monotouch apis for SqliteConnection and SqliteDataAdapter classes).
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="statement">
/// A <see cref="System.String"/>
/// </param>
/// <param name="statementParams">
/// A <see cref="System.String[]"/>
/// </param>
/// <returns>
/// A <see cref="System.Boolean"/>
/// </returns>
public override bool ExecuteSQLStatement(Database db, string statement, string[] statementParams)
{
bool result = false;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string SQL = statement;
try
{
if (statementParams != null)
{
SQL = String.Format(SQL, statementParams);
}
SqliteDataAdapter adapter = new SqliteDataAdapter(SQL, (SqliteConnection)connection);
adapter.Fill(new DataSet());
result = true;
}
catch (Exception e)
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
}
return result;
}
private void setupRegionSettingsCommands(SqliteDataAdapter da, SqliteConnection conn)
{
da.InsertCommand = createInsertCommand("regionsettings", ds.Tables["regionsettings"]);
da.InsertCommand.Connection = conn;
da.UpdateCommand = createUpdateCommand("regionsettings", "regionUUID=:regionUUID", ds.Tables["regionsettings"]);
da.UpdateCommand.Connection = conn;
}
[Category ("NotWorking")] // Requires newer sqlite than is on wrench
public void UpdateResetRowErrorCorrectly ()
{
const string connectionString = "URI = file::memory:; Version = 3";
using (var dbConnection = new SqliteConnection (connectionString)) {
dbConnection.Open ();
using (var cmd = dbConnection.CreateCommand ()) {
cmd.CommandText = "CREATE TABLE data (id PRIMARY KEY, name TEXT)";
cmd.ExecuteNonQuery ();
}
var ts = dbConnection.BeginTransaction ();
var da = new SqliteDataAdapter ("SELECT * FROM data", dbConnection);
var builder = new SqliteCommandBuilder (da);
da.UpdateCommand = builder.GetUpdateCommand ();
da.UpdateCommand.Transaction = ts;
var ds1 = new DataSet ();
da.Fill (ds1, "data");
var table = ds1.Tables [0];
var row = table.NewRow ();
row ["id"] = 10;
row ["name"] = "Bart";
table.Rows.Add (row);
var ds2 = ds1.GetChanges ();
da.Update (ds2, "data");
Assert.IsFalse (ds2.HasErrors);
}
}
public void XimarinBugzillaBug853Test()
{
const string connectionString = "URI = file:./SqliteTest.db; Version = 3";//will be in System.Data directory
SqliteConnection dbConnection = new SqliteConnection(connectionString);
dbConnection.Open();
SqliteCommand ClearTableEntry=new SqliteCommand("DELETE FROM Primus;",dbConnection);
ClearTableEntry.ExecuteNonQuery();
SqliteDataAdapter sqliteDataAdapter = new SqliteDataAdapter("SELECT * FROM primus", dbConnection);
SqliteCommandBuilder builder = new SqliteCommandBuilder(sqliteDataAdapter);
sqliteDataAdapter.InsertCommand = builder.GetInsertCommand();
sqliteDataAdapter.DeleteCommand = builder.GetDeleteCommand();
DataSet dataSet = new DataSet();
sqliteDataAdapter.Fill(dataSet, "Primus");//reset
DataRow rowToBeAdded = dataSet.Tables["Primus"].NewRow();
rowToBeAdded["id"] = 123;
rowToBeAdded["name"] = "Name";//not null primary key
rowToBeAdded["value"] = 777;
dataSet.Tables["Primus"].Rows.Add(rowToBeAdded);
sqliteDataAdapter.Update (dataSet, "Primus");
//This would fail with NULL constraint violation in bug
//report. Because before the patch, it would create
//a new record with all fields being null-- if the
//exception rises, test fails
sqliteDataAdapter.Update (dataSet, "Primus");
dbConnection.Close();
dbConnection = null;
}
private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
{
da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
da.InsertCommand.Connection = conn;
}
public DataSet ExecuteDataSet(string sql)
{
using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString))
{
using (SqliteCommand cmd = new SqliteCommand(sql, conn))
{
try
{
conn.Open();
SqliteDataAdapter adapter = new SqliteDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
catch(Exception e)
{
conn.Close();
throw new Exception(e.Message + "<br>sql:" + sql + "<br>" + e.StackTrace.Replace("\n","<br>"));
}
finally
{
conn.Close();
}
}
}
}
private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn)
{
da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]);
da.InsertCommand.Connection = conn;
da.UpdateCommand = createUpdateCommand("landaccesslist", "LandUUID=:landUUID", "AccessUUID=:AccessUUID", ds.Tables["landaccesslist"]);
da.UpdateCommand.Connection = conn;
SqliteCommand delete = new SqliteCommand("delete from landaccesslist where LandUUID= :LandUUID and AccessUUID= :AccessUUID");
delete.Parameters.Add(createSqliteParameter("LandUUID", typeof(String)));
delete.Parameters.Add(createSqliteParameter("AccessUUID", typeof(String)));
da.DeleteCommand = delete;
da.DeleteCommand.Connection = conn;
}
/// <summary>
///
/// </summary>
/// <param name="da"></param>
/// <param name="conn"></param>
private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
{
da.InsertCommand = createInsertCommand("primitems", ds.Tables["primitems"]);
da.InsertCommand.Connection = conn;
da.UpdateCommand = createUpdateCommand("primitems", "itemID = :itemID", ds.Tables["primitems"]);
da.UpdateCommand.Connection = conn;
SqliteCommand delete = new SqliteCommand("delete from primitems where itemID = :itemID");
delete.Parameters.Add(createSqliteParameter("itemID", typeof (String)));
delete.Connection = conn;
da.DeleteCommand = delete;
}
/// <summary>
/// See IRegionDataStore
/// <list type="bullet">
/// <item>Initialises RegionData Interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// </list>
/// </summary>
/// <param name="connectionString">the connection string</param>
public void Initialise(string connectionString)
{
try
{
ds = new DataSet("Region");
m_log.Info("[SQLITE REGION DB]: Sqlite - connecting: " + connectionString);
m_conn = new SqliteConnection(connectionString);
m_conn.Open();
// SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
terrainDa = new SqliteDataAdapter(terrainSelectCmd);
SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
landDa = new SqliteDataAdapter(landSelectCmd);
SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);
// This actually does the roll forward assembly stuff
Migration m = new Migration(m_conn, GetType().Assembly, "RegionStore");
m.Update();
lock (ds)
{
ds.Tables.Add(createTerrainTable());
setupTerrainCommands(terrainDa, m_conn);
ds.Tables.Add(createLandTable());
setupLandCommands(landDa, m_conn);
ds.Tables.Add(createLandAccessListTable());
setupLandAccessCommands(landAccessListDa, m_conn);
ds.Tables.Add(createRegionSettingsTable());
setupRegionSettingsCommands(regionSettingsDa, m_conn);
try
{
terrainDa.Fill(ds.Tables["terrain"]);
}
catch (Exception)
{
m_log.Info("[SQLITE REGION DB]: Caught fill error on terrain table");
}
try
{
landDa.Fill(ds.Tables["land"]);
}
catch (Exception)
{
m_log.Info("[SQLITE REGION DB]: Caught fill error on land table");
}
try
{
landAccessListDa.Fill(ds.Tables["landaccesslist"]);
}
catch (Exception)
{
m_log.Info("[SQLITE REGION DB]: Caught fill error on landaccesslist table");
}
try
{
regionSettingsDa.Fill(ds.Tables["regionsettings"]);
}
catch (Exception)
{
m_log.Info("[SQLITE REGION DB]: Caught fill error on regionsettings table");
}
// We have to create a data set mapping for every table, otherwise the IDataAdaptor.Update() will not populate rows with values!
// Not sure exactly why this is - this kind of thing was not necessary before - justincc 20100409
// Possibly because we manually set up our own DataTables before connecting to the database
CreateDataSetMapping(terrainDa, "terrain");
CreateDataSetMapping(landDa, "land");
CreateDataSetMapping(landAccessListDa, "landaccesslist");
CreateDataSetMapping(regionSettingsDa, "regionsettings");
}
}
catch (Exception e)
{
m_log.Error(e);
//TODO: better error for users!
System.Threading.Thread.Sleep(10000); //Sleep so the user can see the error
Environment.Exit(23);
}
return;
}
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;
}
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
this.SetContentView(IslamicHadithAND.Resource.Layout.Book);
//ActionBar
ActionBar.NavigationMode = ActionBarNavigationMode.Standard;
progress = ProgressDialog.Show(this, "انتظر من فضلك", "يتم تحميل الأحاديث ...", true);
new Thread(new ThreadStart(() =>
{
Thread.Sleep(1);
this.RunOnUiThread(() =>
{
try
{
string content;
using (StreamReader streamReader = new StreamReader(Assets.Open("hadeeth.sqlite")))
{
content = streamReader.ReadToEnd();
}
string dbName = "hadeeth.sqlite";
string dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), dbName);
if (!File.Exists(dbPath))
{
using (Stream source = new StreamReader(Assets.Open("hadeeth.sqlite")).BaseStream)
{
using (var destination = System.IO.File.Create(dbPath))
{
source.CopyTo(destination);
}
}
}
DataTable dataTableBook = new DataTable();
var connectionString = string.Format("Data Source={0};Version=3;", dbPath);
using (var conn = new SqliteConnection((connectionString)))
{
using (var command = conn.CreateCommand())
{
conn.Open();
command.CommandText = @"SELECT hadeeth.*" +
"FROM Books INNER JOIN hadeeth ON Books.ID = hadeeth.BID" +
" where hadeeth.hadeeth like '%<%' and " +
"books.title like '%سنن الدارمي%'";
command.CommandType = CommandType.Text;
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTableBook);
}
}
var data = new List<string>();
for (int i = 0; i < dataTableBook.Rows.Count; i++)
{
data.Add(unBold((dataTableBook.Rows[i]["hadeeth"].ToString())));
if (dataTableBook.Rows.Count == 0)
{
new AlertDialog.Builder(this)
.SetTitle("خطأ")
.SetMessage("لا يوجد نتائج")
.SetPositiveButton("عودة", (senderaa, args) =>
{
// Back
})
.Show();
}
}
var listView = FindViewById<ListView>(IslamicHadithAND.Resource.Id.listBook);
listView.Adapter = new ArrayAdapter(this, Resource.Layout.ListViewContents, data);
listView.ItemClick += (sender, e) =>
{
var position = e.Position;
var HadithBrowser = new Intent(this, typeof(HadithBrowser));
HadithBrowser.PutExtra("Hadith", listView.GetItemAtPosition(position).ToString());
if (!listView.GetItemAtPosition(position + 1).Equals(null))
{
position++;
HadithBrowser.PutExtra("HadithNext1", listView.GetItemAtPosition(position).ToString());
}
if (!listView.GetItemAtPosition(position - 1).Equals(null))
{
position--;
HadithBrowser.PutExtra("HadithPrevious1", listView.GetItemAtPosition(position).ToString());
}
StartActivity(HadithBrowser);
};
}
catch (Exception ex)
{
new AlertDialog.Builder(this)
.SetPositiveButton("عودة", (sendera, args) =>
{
// Back
})
.SetTitle("خطأ")
.SetMessage("خطأ في قاعدة البيانات ( " + ex.ToString() + " )")
.Show();
}
progress.Dismiss();
});
})).Start();
}
// Temporary attribute while this is experimental
/***********************************************************************
*
* Public Interface Functions
*
**********************************************************************/
/// <summary>
/// See IRegionDataStore
/// <list type="bullet">
/// <item>Initialises RegionData Interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// </list>
/// </summary>
/// <param name="connectionString">the connection string</param>
public void Initialise(string connectionString)
{
try
{
m_connectionString = connectionString;
ds = new DataSet("Region");
m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString);
m_conn = new SqliteConnection(m_connectionString);
m_conn.Open();
SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
primDa = new SqliteDataAdapter(primSelectCmd);
SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
shapeDa = new SqliteDataAdapter(shapeSelectCmd);
// SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
itemsDa = new SqliteDataAdapter(itemsSelectCmd);
SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
terrainDa = new SqliteDataAdapter(terrainSelectCmd);
SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
landDa = new SqliteDataAdapter(landSelectCmd);
SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);
// This actually does the roll forward assembly stuff
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_conn, assem, "RegionStore");
m.Update();
lock (ds)
{
ds.Tables.Add(createPrimTable());
setupPrimCommands(primDa, m_conn);
ds.Tables.Add(createShapeTable());
setupShapeCommands(shapeDa, m_conn);
ds.Tables.Add(createItemsTable());
setupItemsCommands(itemsDa, m_conn);
ds.Tables.Add(createTerrainTable());
setupTerrainCommands(terrainDa, m_conn);
ds.Tables.Add(createLandTable());
setupLandCommands(landDa, m_conn);
ds.Tables.Add(createLandAccessListTable());
setupLandAccessCommands(landAccessListDa, m_conn);
ds.Tables.Add(createRegionSettingsTable());
setupRegionSettingsCommands(regionSettingsDa, m_conn);
// WORKAROUND: This is a work around for sqlite on
// windows, which gets really unhappy with blob columns
// that have no sample data in them. At some point we
// need to actually find a proper way to handle this.
try
{
primDa.Fill(ds.Tables["prims"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on prims table");
}
try
{
shapeDa.Fill(ds.Tables["primshapes"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on primshapes table");
}
try
{
terrainDa.Fill(ds.Tables["terrain"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on terrain table");
}
try
{
landDa.Fill(ds.Tables["land"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on land table");
}
try
{
landAccessListDa.Fill(ds.Tables["landaccesslist"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on landaccesslist table");
}
try
{
regionSettingsDa.Fill(ds.Tables["regionsettings"]);
}
catch (Exception)
{
m_log.Info("[REGION DB]: Caught fill error on regionsettings table");
}
// We have to create a data set mapping for every table, otherwise the IDataAdaptor.Update() will not populate rows with values!
// Not sure exactly why this is - this kind of thing was not necessary before - justincc 20100409
// Possibly because we manually set up our own DataTables before connecting to the database
CreateDataSetMapping(primDa, "prims");
CreateDataSetMapping(shapeDa, "primshapes");
CreateDataSetMapping(itemsDa, "primitems");
CreateDataSetMapping(terrainDa, "terrain");
CreateDataSetMapping(landDa, "land");
CreateDataSetMapping(landAccessListDa, "landaccesslist");
CreateDataSetMapping(regionSettingsDa, "regionsettings");
}
}
catch (Exception e)
{
m_log.Error(e);
Environment.Exit(23);
}
return;
}