C# SqliteDataAdapter.Fill方法代码示例

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

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

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

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

        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 DataSet GetEmailByName(string name)
        {
            string sql = "select firstName, lastName, email from Employees where firstName like '" + name + "%' or lastName like '" + name + "%'";
            
            
            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;
            }
        }

        public DataSet GetCustomerDetails(string customerNumber)
        {
            string sql = "select Customers.customerNumber, Customers.customerName, Customers.logoFileName, Customers.contactLastName, Customers.contactFirstName, " +
                "Customers.phone, Customers.addressLine1, Customers.addressLine2, Customers.city, Customers.state, Customers.postalCode, Customers.country, " +
                "Customers.salesRepEmployeeNumber, Customers.creditLimit, CustomerLogin.email, CustomerLogin.password, CustomerLogin.question_id, CustomerLogin.answer " +
                "From Customers, CustomerLogin where Customers.customerNumber = CustomerLogin.customerNumber and Customers.customerNumber = " + customerNumber;

            DataSet ds = new DataSet();
            try
            {
            
                using (SqliteConnection connection = new SqliteConnection(_connectionString))
                {
                    connection.Open();

                    SqliteDataAdapter da = new SqliteDataAdapter(sql, connection);
                    da.Fill(ds);
                }

            }
            catch (Exception ex)
            {
                log.Error("Error getting customer details", ex);
                
                throw new ApplicationException("Error getting customer details", ex);
            }
            return ds;

        }