c# winforms - binding DataGridView to sdf database _

Question

I create a small sdf database and put it into project folder.
Trying to bind a DataGridView with that database, I wrote:

using System.Data.SqlClient;
. . .
Form1_Load:
string connString = "Provider=.NET Framework Data Provider for Microsoft SQL Server Compact 3.5;Data Source=|DataDirectory|\db01.sdf";
  string query = "SELECT * FROM table01";
  SqlDataAdapter dA = new SqlDataAdapter(query, connString);
  SqlCommandBuilder cBuilder = new SqlCommandBuilder(dA);
  DataTable dT = new DataTable();
  dA.Fill(dT);
  BindingSource bS = new BindingSource();
  bS.DataSource = dT;
  dgv01.DataSource = bS;

Error: Keyword not supported: 'provider'.
Could someone explain, what is wrong, please.

Answer 1

try the following

SqlCeConnection yourConnection = new SqlCeConnection("Data Source=|DataDirectory|\\YourDatabase.sdf") 

You have to use System.Data.SqlServerCe for you to program compact databases.

---

Good is not good enough when best is expected !!!

Answer 2

I just tested this code and its working fine.

using System.Data.SqlServerCe;
SqlCeConnection con = new SqlCeConnection("Data Source=C:\\Users\\username\\Documents\\Test.sdf;Password='password'");
            con.Open();
            WebMsgBox.Show("Connection established successfully");
            con.Close();
            WebMsgBox.Show("Connection closed successfully");
            

My database in Documents folder. Modify above code to suit yours.

---

Good is not good enough when best is expected !!!

Answer 3

Thanks, but in that case what about SqlDataAdapter and SqlCommandBuilder - they becomes outside of assembly. What more assembly should i use? I need to bind sdf database and DataGridView on the Form.

Answer 4

Thanks, but in that case what about SqlDataAdapter and SqlCommandBuilder - they becomes outside of assembly. What more assembly should i use? I need to bind sdf database and DataGridView on the Form.

chirochino's code is not placed outside the class, he just cut out the rest and kept only the essential parts. 

Since you will be working with SQL Server Compact database file, you should use classes in the SqlServerCe namespace which were designed specially for this purpose. 

In your sample code you create an instance of the DataAdapter by calling one of its four constructors, the one that accepts a select command string and a connection string as arguments. chirochino showed you how to create the same instance of the SqlCeDataAdapter using another constructor, the one that accepts a select command string and a SqlCeConnection instance. You will have the same result. 

DataTable dT;
BindingSource bS;

using (SqlCeConnection yourConnection = new SqlCeConnection("Data Source=|DataDirectory|\\YourDatabase.sdf"))
{
    dT = new DataTable();
    bS = new BindingSource();    

    string query = "SELECT * FROM table01";
    SqlCeDataAdapter dA = new SqlCeDataAdapter(query, yourConnection);
    SqlCeCommandBuilder cBuilder = new SqlCeCommandBuilder(dA);
    dA.Fill(dT);
    
    bS.DataSource = dT;
    dgv01.DataSource = bS;
}

---

Captain Solo never finishes his battles. He seems to enjoy watching us going down to crash without actually doing so. I cannot tell you the number of times that we have been statistically doomed, only to escape at the last mo--