dataset and where clause

[Altman]

I am playing around with making a dataset with the designer in vb.net
2005. I realize you can add parameters to select statement and then
filter off of those parameters. What I would like to do is to take the
dataset that the designer made and add the whole where clause to it at
run time. Is this possible?

 

[Cor Ligthert [MVP]]

Altman,

I don't think so, the designer builds beside the Select as well the Update,
the Insert and the Deletecommands.

Those are more complex than the standards, because for concurrencychecking
are the old rows first checked against the last in the database.

If you want to do things like you ask, than you should in my opinion forget
the designer and do everything in code.

Just my idea.

Cor

 

[Altman]

Thanks for the reply, the reason I would like to do this with the
designer is I plan to use the rdlc. In order to make the rdlc I need a
dataset to design it off of. But as I said before I would like to set
the where clause programatically. I know I can filter the dataset
table but I would like to filter it on the select statement to speed
things up.

 

[Guest]

I think you are confusing dataset with dataAdapter. The dataAdapter is the
component that contains/uses the sql statements. You have the 4 basic sql
commands "Select", "Insert", "Update", "Delete" that are part of the
dataAdapter. Note: it is way better/easier to write out the
dataAdapters/datasets than to use the designer. Here is a sample:

Imports System.Data.SqlClient

sub something()
Dim da As sqlDataAdapter, ds As Dataset, conn As Sql Connection
conn = New SqlConnection
conn.ConnectionString = "Data
Source=yourserver;UID=yourID;PWD=yourpassword;DATABASE=someDB"
da = New sqlDataAdapter
ds = New Dataset
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * from tbl1 Where RecordID = @ID"
da.SelectCommand.Parameters.Add(New SqlParameter("@ID", SqlDBType.Int, 4,
"RecordID")

da.SelectCommand.Parameter("@ID").Value = txtRecordID.Text

da.Fill(ds, "tbl1")
Datagridview1.Datasource = ds.Tables("tbl1")
End Sub

Now your dataset contains a table called "tbl1" which is a copy of the table
from the database -- except in memory and gets displayed in the datagridview.

HTH,
Rich

 

[Altman]

I do understand the difference between a dataadatper and a dataset. I
am trying to make a rdlc report and as far as I can tell, I cannot use
a dataset that is created programatically. Therefore I was using the
designer to make a dataset. Obviously the dataset that is created with
the designer must create it's own dataadapter to pull the data in. I
basically just need to access this adapter, and either change the sql
select or at the very least obtain what the sql select is. Unless
someone knows of an easier way that I can design a rdlc and be able to
set the where clause programatically.

 

[Jim Wooley]

You can use the CommandBuilder object to build the update, insert and delete
commands as necessary based on a select command. Naturally, you will suffer
some performance implications in doing this. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp.
The CommandBuilder is used by the VS designer to create the commands. I would
recommend NOT using it in a production application, but rather have more
fine-grained control of your environment.

Per the original question, you should be able to check the DataAdapter.SelectCommand.CommandText
to manipulate the SQL being sent. One thing to remember: use parameterized
queries not string concatenation to avoid SQL Injection.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

 

[Bob]

When you create a bound datagridview on a form you will see that doing that
creates three objects a dataset a binding source and a table adatapter. In
the binding source you will find a property called filter. This is basically
where you can enter a value that is everything you would find in a where
clause except the Where keyword
In your code you can write a sequence that does the following
1- clears the dataset or maybe just the datatable you want to use
2- Sets the filter on the bindingsource
3- Fill the tabledapter(s)
Lets say you have dataset1, that would give you Bindingsource1 and
datatable1 and your sql table itself is named Table1

You would do something like
dataset1.clear 'This clears all the data from all the rows in the dataset
If you want to be able to refill just a single table one at a time, you can
set the tableadapter's ClearBeforeFill property to true.
When you do that each time you call for a fill on a tableadapter it will
automatically clear that table adapter's content.

BindingSource1.filter = "Myfield1 = 'a string' and Myfield2 = 2"
TableAdapter1.fill(dataset1.table1)

You can put this in the form load event (in which case you don't need the
dataset1.clear statement) or you can do this in any event like a button
click. I spoke about the datagridview but basically any bound control on
your form will need to use the same three objects, dataset bindingsource and
table adapter.

HTH

Bob