[RESOLVED] Help needed about converting OLEDB to SQLite

[nikel]

Hello, I created an OLEDB database with relations, it works fine. I tried converting it to SQLite however I'm getting empty datagridview.

private void Form1_Load(object sender, EventArgs e)
{
    // Compose the connection string.
    string connect_string =
        @"Data Source=Contacts.sqlite; Version=3; FailIfMissing=True; Foreign Keys=True;";
 
    // Create a DataAdapter to load the Addresses table.
    DaAddresses = new SQLiteDataAdapter(
        "SELECT * FROM Addresses", connect_string);
 
    // Create a DataAdapter to load the Addresses table.
    DaTestScores = new SQLiteDataAdapter(
        "SELECT * FROM TestScores", connect_string);
 
    // Create and fill the DataSet.
    StudentDataSet = new DataSet();
    DataTable dt = new DataTable("Addresses");
    DataTable dt2 = new DataTable("TestScores");
 
    StudentDataSet.Tables.Add(dt);
    StudentDataSet.Tables.Add(dt2);
 
    DaAddresses.Fill(dt);
    DaTestScores.Fill(dt2);
 
    // Define the relationship between the tables.
    DataRelation data_relation = new DataRelation(
        "Addresses_TestScores",
        StudentDataSet.Tables["Addresses"].Columns["ContactID"],
        StudentDataSet.Tables["TestScores"].Columns["ContactID"]);
    StudentDataSet.Relations.Add(data_relation);
 
    // Bind the DataGrid to the DataSet.
    dgv.AutoGenerateColumns = true;
    dgv.DataMember = StudentDataSet.Tables[0].TableName;
    dgv.Update();
    dgv.DataSource = StudentDataSet;
}

How can I show data on Datagriview?

[techgnome]

Immediately after the fill statements, check the .Rows.Count property of the datatables... see what it is...
I'd also fill them first before adding them to the dataset, but I think that's more of a personal choice than anything wrong with what you're doing.
Also dgv.Update shouldn't be necessary.
And lastly, are you sure there's matching data in both tables? Or better still, is there data in the database?

-tg

[nikel]

How stupid of me, theres no data in the database! Thanks a lot.

[jmcilhinney]

The only part of that code that is really relevant to your question is the Fill calls. Either they are retrieving data or they aren't. You can determine that as tg suggested or get the value returned by Fill itself. If that's zero then that means that there's no data in the database that matches your query.

By the way, there are a couple of obvious improvements you could make to that code. Firstly, by passing a connection string to the data adapter constructor you will end up with two separate connection objects that each get opened and closed. If you're query the same database twice then you should be creating one connection object, opening it, performing both queries and then closing it.

Secondly, there's no need to create the DataTables and add them to the DataSet. You can call Fill and pass the DataSet and a table name and a DataTable will be created implicitly.

var data = new DataSet();
 
using (var connection = new SQLiteConnection("connection string here"))
using (var addressAdapter = new SQLiteDataAdapter("SELECT * FROM Addresses", connection))
using (var testScoreAdapter = new SQLiteDataAdapter("SELECT * FROM TestScores", connection))
{
    connection.Open();
 
    addressAdapter.Fill(data, "Addresses");
    testScoreAdapter.Fill(data, "TestScores");
}

[nikel]

Yes, I've fixed that connection problem. Thanks for the tips.