C# Sqlite.SqliteDataAdapter类代码示例

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