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];
        }

        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 = '" + 
                         encoded_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 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;
                }
            }
        }

 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;
    }

 private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
 {
     da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
     da.InsertCommand.Connection = conn;
 }

        /// <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;
        }

        /// <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;
        }

		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 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;
 }

        // 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
//.........这里部分代码省略.........

        /// <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;
        }

 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();
             }
         }
     }
 }

		[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);
			}
		}

        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;
        }