c# winforms - binding DataGridView to sdf database

Memento

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
If your database requirements are fairly modest, then SQL Server Compact is fine.

If you've created an .sdf file, then I assume you must have used Sql Server Compact anyway as SQL Server Express creates .mdf files.

However, it's just dawned on me that you should be using the classes in the System.Data.SqlServerCe namespace rather than the System.Data.SqlClient namespace which may be the reason for the latest problem.

So, I'd try:

using System.Data.SqlServerCe;

// ....

string connString = "Data Source=|DataDirectory|\db01.sdf";
string query = "SELECT * FROM table01";
SqlCeDataAdapter dA = new SqlCeDataAdapter(query, connString);
SqlCeCommandBuilder cBuilder = new SqlCeCommandBuilder(dA);
DataTable dT = new DataTable();
dA.Fill(dT);
BindingSource bS = new BindingSource();
bS.DataSource = dT;
dgv01.DataSource = bS;

You'll also need to add a reference to System.Data.SqlServerCe.dll if you haven't added one already.

0
Vulpes14y ago
Well, there's no escaping the fact that you have to:

1. Set your own connection string - ADO.NET has providers for a large number of databases, all of which have their own connection string format and options.

2. Write your own SELECT queries as these can be arbitrarily complex.

Once you've done that there's not all that much code left.

The CommandBuilder builds your INSERT, UPDATE and DELETE commands and the DataAdapter deals with filling your DataSet and writing updates back to the database. The BindingSource does what is says on the tin.

I suppose you could do these latter operations behind the scenes but, if you did so, you'd loose the tremendous flexibility the present system gives you.

Incidentally, MS have made attempts to make all of this easier with LINQ to SQL and the Entity Framework but whether they have succeeded or not is an open question as these technologies have always seemed to me to introduce more problems than they solve!
0
Memento14y ago
Hi, Vulpes,
ThankYou for yourEfforts, it seems your code works...but... but... but...
I must ask someone this question:
Is it possible that databinding is so unnecessary complicated ?
Is it possible that highRangeDevelopers (like Microsoft) cannot produce a simple DataGridView which will hold the data.
If I put some value in a cell, iti is normally to assume that i want that value in that cell, and why all these TimeConsumingAnd Complicated Toys like Connections, DataSet, DataTable, BindingSource, TableAdapters...
Why all these things don't work in the background.
So - drag the DataGridView from the toolbox - and voila - it's redy for entering and hold entered data.
I don't believe that today we have any hardware limitations for such developing approach.
Excuse me if I spend you time, maybe you see all of that -so normally.
Regards.
0
Memento14y ago
In that case error is:
A network-related or instance-specific error occurred while establishing a connection to SQL Server... error: 40 - Could not open a connection to SQL Server)

I installed SQL Express during C# express instalation. Or maybe I need SQL Compact Edition ? I have no fully SQL Server. What is your advice?
0
Vulpes14y ago
I'd see whether it will work if you remove the 'Provider' part of the connection string which doesn't appear to be supported by SQL Server Compact 3.5:

string connString = "Data Source=|DataDirectory|\db01.sdf";
0