Click or drag to resize

Performing Searches

This walkthrough will show you how retrieve data via the MySQL protocol from Sphinx with the SphinxQLDataReader and the SphinxQLDataAdapter.

Retrieving Data via the SphinxQLDataReader

The SphinxQLDataReader class can be used to retrieve records from Sphinx in a sequential manner. In the following example we query the Sakila index from the tutorial and output the document ids to the console:

C#
using (SphinxQLConnection connection = new SphinxQLConnection("Data Source=localhost;Port=9306"))
{
    SphinxQLCommand command = new SphinxQLCommand("SELECT * from sakila WHERE MATCH(@match)", connection);
    command.Parameters.Add("@match", "room");

    connection.Open();

    using (SphinxQLDataReader dataReader = command.ExecuteReader())
    {
        while (dataReader.Read())
        {
            Console.WriteLine(dataReader.GetInt64("id"));
        }
    }
}

The next example shows how to retrieve more than result set via the SphinxQLDataReader and the NextResult method:

C#
using (SphinxQLConnection connection = new SphinxQLConnection("Data Source=localhost;Port=9306"))
{
    SphinxQLCommand command = new SphinxQLCommand(connection);
    command.CommandText = @"SELECT * from sakila WHERE MATCH(@match1);
                            SELECT * from sakila WHERE MATCH(@match2)";

    command.Parameters.Add("@match1", "room");
    command.Parameters.Add("@match2", "amazing");

    connection.Open();

    using (SphinxQLDataReader dataReader = command.ExecuteReader())
    {
        while (dataReader.Read())
        {
            Console.WriteLine(dataReader.GetInt64("id"));
        }

        dataReader.NextResult();

        while (dataReader.Read())
        {
            Console.WriteLine(dataReader.GetInt64("id"));
        }
    }
}
Retrieving Data via the SphinxQLDataAdapter

The SphinxQLDataAdapter class can be used to fill a DataTable or a DataSet with with records from Sphinx. The following code shows how to fill a DataTable with data from the Sakila index:

C#
DataTable dataTable = new DataTable();

using (SphinxQLConnection connection = new SphinxQLConnection("Data Source=localhost;Port=9306"))
{
    SphinxQLCommand command = new SphinxQLCommand("SELECT * from sakila;", connection);

    SphinxQLDataAdapter dataAdapter = new SphinxQLDataAdapter();
    dataAdapter.SelectCommand = command;
    dataAdapter.Fill(dataTable);
}

The SphinxQLDataAdapter can also be used to fill more than one DataTable by using a DataSet:

C#
DataSet dataSet = new DataSet();

using (SphinxQLConnection connection = new SphinxQLConnection("Data Source=localhost;Port=9306"))
{
    SphinxQLCommand selectCommand = new SphinxQLCommand(connection);               
    selectCommand.CommandText = @"SELECT * from sakila WHERE MATCH(@match1);
                                  SELECT * from sakila WHERE MATCH(@match2)";

    selectCommand.Parameters.Add("@match1", "room");
    selectCommand.Parameters.Add("@match2", "amazing");

    SphinxQLDataAdapter dataAdapter = new SphinxQLDataAdapter();
    dataAdapter.SelectCommand = selectCommand;

    dataAdapter.Fill(dataSet);
}