SqlDataAdapter - can it be (re)used for 2 SELECT statements

[Matt Jensen]

Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification. Just
asking in the interests of trying to minimise code.

i.e. if the SqlDataAdapter uses a connection to the one database for a
select statement and I want to do a second select statement on exactly the
same database, can I reuse the SqlDataAdapter?

I ask because I want to put both 'select' results into the one dataset, just
thought I'd use the SqlDataAdapter if I could.

Thanks
Cheers
Matt

[Karl Seguin]

Just do it in 1 query.
select * from x; select * from y

it'll populate 2 tables in the 1 dataset that you Fill()

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:O3******** ******@tk2msftn gp13.phx.gbl...

Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification. Just
asking in the interests of trying to minimise code.

i.e. if the SqlDataAdapter uses a connection to the one database for a
select statement and I want to do a second select statement on exactly the
same database, can I reuse the SqlDataAdapter?

I ask because I want to put both 'select' results into the one dataset,
just thought I'd use the SqlDataAdapter if I could.

Thanks
Cheers
Matt

[Matt Jensen]

Nice, thanks :-)
Same for stored procedures with parameters though?
How about naming the tables in the dataset, or does one just refer to them
by table index number?
Cheers
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...

Just do it in 1 query.
select * from x; select * from y

it'll populate 2 tables in the 1 dataset that you Fill()

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:O3******** ******@tk2msftn gp13.phx.gbl...

Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification. Just
asking in the interests of trying to minimise code.

i.e. if the SqlDataAdapter uses a connection to the one database for a
select statement and I want to do a second select statement on exactly
the same database, can I reuse the SqlDataAdapter?

I ask because I want to put both 'select' results into the one dataset,
just thought I'd use the SqlDataAdapter if I could.

Thanks
Cheers
Matt

[Matt Jensen]

I guess I can just build up SqlCommand objects (including adding parameters
etc.) then just comma list them inside the SqlDataAdapter statement, can I?
How about naming the tables?
Thanks
Cheers
Matt

"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Nice, thanks :-)
Same for stored procedures with parameters though?
How about naming the tables in the dataset, or does one just refer to them
by table index number?
Cheers
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...

Just do it in 1 query.
select * from x; select * from y

it'll populate 2 tables in the 1 dataset that you Fill()

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:O3******** ******@tk2msftn gp13.phx.gbl...

Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification.
Just asking in the interests of trying to minimise code.

i.e. if the SqlDataAdapter uses a connection to the one database for a
select statement and I want to do a second select statement on exactly
the same database, can I reuse the SqlDataAdapter?

I ask because I want to put both 'select' results into the one dataset,
just thought I'd use the SqlDataAdapter if I could.

Thanks
Cheers
Matt

[Karl Seguin]

Not sure what you are asking about about the parameters.

If you are using an sproc (as you indicated) you'd pass in all the
parameters needed for both query to a single sproc and the queries can use
them as needed.

If you want to refer to the tables by name, simply name them after you've
done the fill based on the index (so use the index the first time, then the
name).

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:u7******** ******@TK2MSFTN GP12.phx.gbl...

I guess I can just build up SqlCommand objects (including adding parameters
etc.) then just comma list them inside the SqlDataAdapter statement, can I?
How about naming the tables?
Thanks
Cheers
Matt

"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Nice, thanks :-)
Same for stored procedures with parameters though?
How about naming the tables in the dataset, or does one just refer to
them by table index number?
Cheers
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...

Just do it in 1 query.
select * from x; select * from y

it'll populate 2 tables in the 1 dataset that you Fill()

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:O3******** ******@tk2msftn gp13.phx.gbl...
Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification.
Just asking in the interests of trying to minimise code.

i.e. if the SqlDataAdapter uses a connection to the one database for a
select statement and I want to do a second select statement on exactly
the same database, can I reuse the SqlDataAdapter?

I ask because I want to put both 'select' results into the one dataset,
just thought I'd use the SqlDataAdapter if I could.

Thanks
Cheers
Matt

[Matt Jensen]

Might be best if I give an example. I'm wondering if I can use the one
dataadapter for 2 different calls to the same sproc, and/or how I can use
less code:

// Open a connection to database
SqlConnection objConn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTyp es");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spT FL_RequestJob_J obTypes",
objConn);
objCmdWebJobs.C ommandType = CommandType.Sto redProcedure;
objCmdWebJobs.P arameters.Add(" @JobCatID", SqlDbType.Int);
objCmdWebJobs.P arameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter( objCmdWebJobs);
objDAWebJobs.Fi ll(objDSJobType s,"WebJobs");
//bind to repeater
Repeater1.DataS ource=objDSJobT ypes.Tables["WebJobs"].DefaultView;
Repeater1.DataB ind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjec ts = new SqlCommand("spT FL_RequestJob_J obTypes",
objConn);
objCmdWebProjec ts.CommandType = CommandType.Sto redProcedure;
objCmdWebProjec ts.Parameters.A dd("@JobCatID" , SqlDbType.Int);
objCmdWebProjec ts.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProject s = new SqlDataAdapter( objCmdWebProjec ts);
objDAWebProject s.Fill(objDSJob Types,"WebProje cts");
//bind to repeater
Repeater2.DataS ource=objDSJobT ypes.Tables["WebProject s"].DefaultView;
Repeater2.DataT extField = "JobTypeDes c";
Repeater2.DataV alueField = "JobTypeID" ;
Repeater2.DataB ind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es******** ********@TK2MSF TNGP14.phx.gbl. ..

Not sure what you are asking about about the parameters.

If you are using an sproc (as you indicated) you'd pass in all the
parameters needed for both query to a single sproc and the queries can use
them as needed.

If you want to refer to the tables by name, simply name them after you've
done the fill based on the index (so use the index the first time, then
the name).

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:u7******** ******@TK2MSFTN GP12.phx.gbl...

I guess I can just build up SqlCommand objects (including adding
parameters etc.) then just comma list them inside the SqlDataAdapter
statement, can I?
How about naming the tables?
Thanks
Cheers
Matt

"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Nice, thanks :-)
Same for stored procedures with parameters though?
How about naming the tables in the dataset, or does one just refer to
them by table index number?
Cheers
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...
Just do it in 1 query.
select * from x; select * from y

it'll populate 2 tables in the 1 dataset that you Fill()

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:O3******** ******@tk2msftn gp13.phx.gbl...
> Howdy
> Fairly simple question I think, I presume the answer is no it can't be
> reused for 2 *SELECT* statements, but just hoping for clarification.
> Just asking in the interests of trying to minimise code.
>
> i.e. if the SqlDataAdapter uses a connection to the one database for a
> select statement and I want to do a second select statement on exactly
> the same database, can I reuse the SqlDataAdapter?
>
> I ask because I want to put both 'select' results into the one
> dataset, just thought I'd use the SqlDataAdapter if I could.
>
> Thanks
> Cheers
> Matt
>

[Karl Seguin]

Seems like you don't have any tiers. I say that because it's pretty obvious
that your presentation logic layer (codebehind) has all the data access
layer embedded into it. Your code should look like:

Repeater1.DataS ource = WebJobs.GetJobs (1);
Repeater1.DataB ind();
Repeater2.DataS ource = WebJobs.GetJobs (2);
Repeater2.DataB ind();
Or,

DataSet ds = WebJobs.GetJobs (new int[]{1,2});
Repeater1.DataS ource = ds.tables[0];
Repeater2.DataS ource = ds.tables[1];
Repeater1.DataB ind();
Repeater2.DataB ind();

or some other variant...

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:OU******** *****@TK2MSFTNG P15.phx.gbl...

Might be best if I give an example. I'm wondering if I can use the one
dataadapter for 2 different calls to the same sproc, and/or how I can use
less code:

// Open a connection to database
SqlConnection objConn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTyp es");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spT FL_RequestJob_J obTypes",
objConn);
objCmdWebJobs.C ommandType = CommandType.Sto redProcedure;
objCmdWebJobs.P arameters.Add(" @JobCatID", SqlDbType.Int);
objCmdWebJobs.P arameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter( objCmdWebJobs);
objDAWebJobs.Fi ll(objDSJobType s,"WebJobs");
//bind to repeater
Repeater1.DataS ource=objDSJobT ypes.Tables["WebJobs"].DefaultView;
Repeater1.DataB ind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjec ts = new
SqlCommand("spT FL_RequestJob_J obTypes", objConn);
objCmdWebProjec ts.CommandType = CommandType.Sto redProcedure;
objCmdWebProjec ts.Parameters.A dd("@JobCatID" , SqlDbType.Int);
objCmdWebProjec ts.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProject s = new SqlDataAdapter( objCmdWebProjec ts);
objDAWebProject s.Fill(objDSJob Types,"WebProje cts");
//bind to repeater
Repeater2.DataS ource=objDSJobT ypes.Tables["WebProject s"].DefaultView;
Repeater2.DataT extField = "JobTypeDes c";
Repeater2.DataV alueField = "JobTypeID" ;
Repeater2.DataB ind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es******** ********@TK2MSF TNGP14.phx.gbl. ..

Not sure what you are asking about about the parameters.

If you are using an sproc (as you indicated) you'd pass in all the
parameters needed for both query to a single sproc and the queries can
use them as needed.

If you want to refer to the tables by name, simply name them after you've
done the fill based on the index (so use the index the first time, then
the name).

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:u7******** ******@TK2MSFTN GP12.phx.gbl...

I guess I can just build up SqlCommand objects (including adding
parameters etc.) then just comma list them inside the SqlDataAdapter
statement, can I?
How about naming the tables?
Thanks
Cheers
Matt

"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..
Nice, thanks :-)
Same for stored procedures with parameters though?
How about naming the tables in the dataset, or does one just refer to
them by table index number?
Cheers
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...
> Just do it in 1 query.
>
>
> select * from x; select * from y
>
> it'll populate 2 tables in the 1 dataset that you Fill()
>
> Karl
>
> --
> MY ASP.Net tutorials
> http://www.openmymind.net/ - New and Improved (yes, the popup is
> annoying)
> http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
> come!)
>
>
> "Matt Jensen" <re************ ***@microsoft.c om> wrote in message
> news:O3******** ******@tk2msftn gp13.phx.gbl...
>> Howdy
>> Fairly simple question I think, I presume the answer is no it can't
>> be reused for 2 *SELECT* statements, but just hoping for
>> clarification. Just asking in the interests of trying to minimise
>> code.
>>
>> i.e. if the SqlDataAdapter uses a connection to the one database for
>> a select statement and I want to do a second select statement on
>> exactly the same database, can I reuse the SqlDataAdapter?
>>
>> I ask because I want to put both 'select' results into the one
>> dataset, just thought I'd use the SqlDataAdapter if I could.
>>
>> Thanks
>> Cheers
>> Matt
>>
>
>

[Matt Jensen]

Nice, thanks.
Yeah I'm just starting, way in over my head, just trying to get things
working, haven't got Visual Studio either (yet)!
Thanks again
Matt
"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:O5******** ******@TK2MSFTN GP15.phx.gbl...

Seems like you don't have any tiers. I say that because it's pretty
obvious that your presentation logic layer (codebehind) has all the data
access layer embedded into it. Your code should look like:

Repeater1.DataS ource = WebJobs.GetJobs (1);
Repeater1.DataB ind();
Repeater2.DataS ource = WebJobs.GetJobs (2);
Repeater2.DataB ind();
Or,

DataSet ds = WebJobs.GetJobs (new int[]{1,2});
Repeater1.DataS ource = ds.tables[0];
Repeater2.DataS ource = ds.tables[1];
Repeater1.DataB ind();
Repeater2.DataB ind();

or some other variant...

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:OU******** *****@TK2MSFTNG P15.phx.gbl...

Might be best if I give an example. I'm wondering if I can use the one
dataadapter for 2 different calls to the same sproc, and/or how I can use
less code:

// Open a connection to database
SqlConnection objConn = new
SqlConnection(C onfigurationSet tings.AppSettin gs["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTyp es");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spT FL_RequestJob_J obTypes",
objConn);
objCmdWebJobs.C ommandType = CommandType.Sto redProcedure;
objCmdWebJobs.P arameters.Add(" @JobCatID", SqlDbType.Int);
objCmdWebJobs.P arameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter( objCmdWebJobs);
objDAWebJobs.Fi ll(objDSJobType s,"WebJobs");
//bind to repeater
Repeater1.DataS ource=objDSJobT ypes.Tables["WebJobs"].DefaultView;
Repeater1.DataB ind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjec ts = new
SqlCommand("spT FL_RequestJob_J obTypes", objConn);
objCmdWebProjec ts.CommandType = CommandType.Sto redProcedure;
objCmdWebProjec ts.Parameters.A dd("@JobCatID" , SqlDbType.Int);
objCmdWebProjec ts.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProject s = new SqlDataAdapter( objCmdWebProjec ts);
objDAWebProject s.Fill(objDSJob Types,"WebProje cts");
//bind to repeater
Repeater2.DataS ource=objDSJobT ypes.Tables["WebProject s"].DefaultView;
Repeater2.DataT extField = "JobTypeDes c";
Repeater2.DataV alueField = "JobTypeID" ;
Repeater2.DataB ind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es******** ********@TK2MSF TNGP14.phx.gbl. ..

Not sure what you are asking about about the parameters.

If you are using an sproc (as you indicated) you'd pass in all the
parameters needed for both query to a single sproc and the queries can
use them as needed.

If you want to refer to the tables by name, simply name them after
you've done the fill based on the index (so use the index the first
time, then the name).

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:u7******** ******@TK2MSFTN GP12.phx.gbl...
I guess I can just build up SqlCommand objects (including adding
parameter s etc.) then just comma list them inside the SqlDataAdapter
statement , can I?
How about naming the tables?
Thanks
Cheers
Matt

"Matt Jensen" <re************ ***@microsoft.c om> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..
> Nice, thanks :-)
> Same for stored procedures with parameters though?
> How about naming the tables in the dataset, or does one just refer to
> them by table index number?
> Cheers
> Matt
>
> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
> net> wrote in message news:Oi******** *****@TK2MSFTNG P15.phx.gbl...
>> Just do it in 1 query.
>>
>>
>> select * from x; select * from y
>>
>> it'll populate 2 tables in the 1 dataset that you Fill()
>>
>> Karl
>>
>> --
>> MY ASP.Net tutorials
>> http://www.openmymind.net/ - New and Improved (yes, the popup is
>> annoying)
>> http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more
>> to come!)
>>
>>
>> "Matt Jensen" <re************ ***@microsoft.c om> wrote in message
>> news:O3******** ******@tk2msftn gp13.phx.gbl...
>>> Howdy
>>> Fairly simple question I think, I presume the answer is no it can't
>>> be reused for 2 *SELECT* statements, but just hoping for
>>> clarification. Just asking in the interests of trying to minimise
>>> code.
>>>
>>> i.e. if the SqlDataAdapter uses a connection to the one database for
>>> a select statement and I want to do a second select statement on
>>> exactly the same database, can I reuse the SqlDataAdapter?
>>>
>>> I ask because I want to put both 'select' results into the one
>>> dataset, just thought I'd use the SqlDataAdapter if I could.
>>>
>>> Thanks
>>> Cheers
>>> Matt
>>>
>>
>>
>
>