DataGridView sample for filtering and browsing through data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

olympus_mons@gmx.de - 29 Jan 2007 18:59 GMT

Hi,

I'm sorry if this is obvious, but as I'm just starting my C# adventure
with VS2005 I dare to ask:

Can somebody point me to a C#-sample showing what are the best
techniques to:
- present a window with e.g. a combobox with data from a lookup-tabe
and a simple text field and an empty DataGridView
- the user selects an item from combobox and/or enters the first
letters of a name in the text field.
- the data grid gets populated with the data corresponding to the
search criteria once the use clicks the "search" button

What I found on msdn was just a DataGridView showing all data but with
no search capability. And the data always was selected from one table,
there were no joins to lookup tables included.

I tried to modify a simple sample but ran into problems as soon as I
used the query editor of VS2005 and included more that one table in
the query although the underlying data set contains the data and
lookup table, connected by a PK/FK relationship...

I'm looking for either a online reference to a good intro into
DataGridView or a reference to a good "cook book" to get me started.

Thanks,
Stefan

Reply to this Message

RobinS - 30 Jan 2007 00:18 GMT

I found a lot of cool stuff about the DataGridView and data binding it in
Brian Noyes' book on Data Binding.

What you really need is to figure out how to use parameters and stored
procedures, if you're the guy who was posting about doing something like
"nome + '*'" in the adonet newsgroup.

You need to do a parameterized query:
select * from whatever where nome like ?
And then pass it the value for the parameter = "value*".

Someone here can probably tell you exactly how to do that; I use stored
procedures, and SQLServer rather than Access, so I'd have to look it up.

You could also create the query dynamically. If you *are* using Access
instead of SQLServer, replace SqlConnection with OLEDBCOnnection, and
SqlDataAdapter with OLEDBDataAdapter. I'm a VB.Net programmer, but here's
my best stab at how to do it in C#. Please forgive (and/or correct) any
syntax errors.

String SQLString =
"select * from myTable Where Nome Like '" + myTextBox.Text + "*'"
SqlConnection cn = New SqlConnection(connString)
SQLString = "SELECT * FROM Customers"
SqlDataAdapter daCustomers = New SqlDataAdapter(SQLString, cn)
Dataset ds = New DataSet()
cn.Open()
daCustomers.Fill(ds, "Customers")
cn.Close()

Then you need to bind your data to the grid. I would use a BindingSource as
an intermediary because it automatically reflects any changes in your data
source to your controls, and gives you some other options for later
(sorting and filtering).

BindingSource myBindingSource = New BindingSource()
myBindingSource.DataSource = ds
myBindingSource.DataMember = "Customers"
myDataGridView.DataSource = myBindingSource

This should work like magic. You should be able to get it to work using a
dynamic query, but be aware that this isn't the most secure way in the
world to write it. Sometimes you do what you have to do, but you might want
to consider changing over to parameterized queries after you get this to
work. Check out Dave Sceppa's ADO.Net Core Reference is you're looking for
a good book to help you with data access.

Hope this helps!

Robin S.

> Hi,
>
[quoted text clipped - 24 lines]
> Thanks,
> Stefan

Reply to this Message

olympus_mons@gmx.de - 30 Jan 2007 08:24 GMT

Robin,

thanks for pointing me to Brian's book. I hope I can find it in a
local book store (I'm a bit old fashioned and like to take the book in
my hands and browse through it before I buy it). ADO.Net Core
Reference is already ordered but not yet on my desk. BTW I'm not that
"name*" guy and my backend db is oracle.

Thanks again,
Stefan

> I found a lot of cool stuff about the DataGridView and data binding it in
> Brian Noyes' book on Data Binding.
[quoted text clipped - 77 lines]
> > Thanks,
> > Stefan- Hide quoted text -- Show quoted text -

Reply to this Message

ClayB - 30 Jan 2007 10:57 GMT

If you want to work disconnected from the database (ie, load a
DataTable and then dynamically apply different filters to that
DataTable), you might consider using a DataView and its RowFilter
property. Here is some code. Drop a TextBox and a DataGridView on a
form, and add a Form.Load event handler. Then replace the handler with
the code below. As you type into the textbox, the DataGridView is
filtered by the values in Col0 matching what you type.

private DataView dv;
private string[] strings = new string[] {"this", "that",
"or" , "the", "other", "will", "do", "now"};
private void Form1_Load(object sender, EventArgs e)
{
#region Get the DataSource
DataTable dt = new DataTable("MyTable");

int nCols = 4;
int nRows = 2000;
Random r = new Random(123345345);

for (int i = 0; i < nCols; i++)
dt.Columns.Add(new DataColumn(string.Format("Col{0}",
i)));

for (int i = 0; i < nRows; ++i)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < nCols; j++)
dr[j] = strings[r.Next(8)];
dt.Rows.Add(dr);
}
#endregion

this.dv = new DataView(dt);

this.dataGridView1.DataSource = dv;

this.textBox1.TextChanged += new
EventHandler(textBox1_TextChanged);
}

void textBox1_TextChanged(object sender, EventArgs e)
{
dv.RowFilter = string.Format("[Col0] like '{0}*'",
this.textBox1.Text);
}

=======================
Clay Burch
Syncfusion, Inc.

Reply to this Message

RobinS - 31 Jan 2007 08:15 GMT

I don't blame you; I like to look at books before I buy them, too. But I
couldn't find Brian's anywhere, so I ordered it blind. It's about the only
book on data binding on the market. There is some stuff in Chris Sells'
Windows Forms 2.0 book, but not enough, and not as thorough.

Sorry about confusing you with the other guy; the questions are almost
identical.

if you want to load a dataset, and then let the user do filtering and
sorting against it, bind the dataset to the DataGridView using a
BindingSource.

Then put your text box or whatever on the screen, and when they apply the
filter, change the filter property of the Binding Source.

If you are putting a list of business objects in the grid, you need Brian
Noyes's book. Chris Sells, in his Windows Forms 2.0 book, addresses
business objects in the grid and updating them, but when it comes to
sorting and filtering, he points the reader to Brian's book.

If you are binding data that you can easily get from a database, if there's
not gazillions of rows, you can do it the way I've recommended above.
Otherwise, I would requery the database when they put in their selection
criteria, and set the data source of the BindingSource, which will
immediately change the screen.

If you have gazillions of rows of data in the database, you don't want to
load them into a dataset, it will take forever to sort/filter. You can do
it a lot quicker by requerying.

If you're always going to use the same field, or set of fields, you can use
a set of stored procedures and pass the filter value in a parameter. Or you
can try the dynamic query method I recommended in my earlier post. As far
as I know, you can not pass a field name to a stored procedure, just a
value.

I have several apps that let my clients put in several fields for selection
criteria, and even pick the fields they want to output, and I use dynamic
queries to pull the data and display it to them.

Robin S.
----------------------------------------------------

> Robin,
>
[quoted text clipped - 96 lines]
>> > Thanks,
>> > Stefan- Hide quoted text -- Show quoted text -

Reply to this Message

Tim Van Wassenhove - 30 Jan 2007 16:10 GMT

olympus_mons@gmx.de schreef:

> - the data grid gets populated with the data corresponding to the
> search criteria once the use clicks the "search" button
>
> What I found on msdn was just a DataGridView showing all data but with
> no search capability. And the data always was selected from one table,
> there were no joins to lookup tables included.

You may want to have a look at: http://blw.sourceforge.net/ (An
IBindingListView implementation)

Signature

Tim Van Wassenhove <url:http://www.timvw.be/>

Reply to this Message

Rate this thread:

DataGridView sample for filtering and browsing through data

Free Magazines