By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,972 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,972 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.com> wrote in message
news:O3**************@tk2msftngp13.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

Nov 19 '05 #2

P: n/a
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*************@TK2MSFTNGP15.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.com> wrote in message
news:O3**************@tk2msftngp13.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


Nov 19 '05 #3

P: n/a
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.com> wrote in message
news:%2****************@TK2MSFTNGP12.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*************@TK2MSFTNGP15.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.com> wrote in message
news:O3**************@tk2msftngp13.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



Nov 19 '05 #4

P: n/a
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.com> wrote in message
news:u7**************@TK2MSFTNGP12.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.com> wrote in message
news:%2****************@TK2MSFTNGP12.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*************@TK2MSFTNGP15.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.com> wrote in message
news:O3**************@tk2msftngp13.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



Nov 19 '05 #5

P: n/a
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(ConfigurationSettings.AppSettings["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTypes");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spTFL_RequestJob_JobTypes",
objConn);
objCmdWebJobs.CommandType = CommandType.StoredProcedure;
objCmdWebJobs.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebJobs.Parameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter(objCmdWebJobs);
objDAWebJobs.Fill(objDSJobTypes,"WebJobs");
//bind to repeater
Repeater1.DataSource=objDSJobTypes.Tables["WebJobs"].DefaultView;
Repeater1.DataBind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjects = new SqlCommand("spTFL_RequestJob_JobTypes",
objConn);
objCmdWebProjects.CommandType = CommandType.StoredProcedure;
objCmdWebProjects.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebProjects.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProjects = new SqlDataAdapter(objCmdWebProjects);
objDAWebProjects.Fill(objDSJobTypes,"WebProjects") ;
//bind to repeater
Repeater2.DataSource=objDSJobTypes.Tables["WebProjects"].DefaultView;
Repeater2.DataTextField = "JobTypeDesc";
Repeater2.DataValueField = "JobTypeID";
Repeater2.DataBind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es****************@TK2MSFTNGP14.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.com> wrote in message
news:u7**************@TK2MSFTNGP12.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.com> wrote in message
news:%2****************@TK2MSFTNGP12.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*************@TK2MSFTNGP15.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.com> wrote in message
news:O3**************@tk2msftngp13.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
>



Nov 19 '05 #6

P: n/a
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.DataSource = WebJobs.GetJobs(1);
Repeater1.DataBind();
Repeater2.DataSource = WebJobs.GetJobs(2);
Repeater2.DataBind();
Or,

DataSet ds = WebJobs.GetJobs(new int[]{1,2});
Repeater1.DataSource = ds.tables[0];
Repeater2.DataSource = ds.tables[1];
Repeater1.DataBind();
Repeater2.DataBind();

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.com> wrote in message
news:OU*************@TK2MSFTNGP15.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(ConfigurationSettings.AppSettings["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTypes");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spTFL_RequestJob_JobTypes",
objConn);
objCmdWebJobs.CommandType = CommandType.StoredProcedure;
objCmdWebJobs.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebJobs.Parameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter(objCmdWebJobs);
objDAWebJobs.Fill(objDSJobTypes,"WebJobs");
//bind to repeater
Repeater1.DataSource=objDSJobTypes.Tables["WebJobs"].DefaultView;
Repeater1.DataBind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjects = new
SqlCommand("spTFL_RequestJob_JobTypes", objConn);
objCmdWebProjects.CommandType = CommandType.StoredProcedure;
objCmdWebProjects.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebProjects.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProjects = new SqlDataAdapter(objCmdWebProjects);
objDAWebProjects.Fill(objDSJobTypes,"WebProjects") ;
//bind to repeater
Repeater2.DataSource=objDSJobTypes.Tables["WebProjects"].DefaultView;
Repeater2.DataTextField = "JobTypeDesc";
Repeater2.DataValueField = "JobTypeID";
Repeater2.DataBind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es****************@TK2MSFTNGP14.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.com> wrote in message
news:u7**************@TK2MSFTNGP12.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.com> wrote in message
news:%2****************@TK2MSFTNGP12.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*************@TK2MSFTNGP15.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.com> wrote in message
> news:O3**************@tk2msftngp13.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
>>
>
>



Nov 19 '05 #7

P: n/a
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**************@TK2MSFTNGP15.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.DataSource = WebJobs.GetJobs(1);
Repeater1.DataBind();
Repeater2.DataSource = WebJobs.GetJobs(2);
Repeater2.DataBind();
Or,

DataSet ds = WebJobs.GetJobs(new int[]{1,2});
Repeater1.DataSource = ds.tables[0];
Repeater2.DataSource = ds.tables[1];
Repeater1.DataBind();
Repeater2.DataBind();

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.com> wrote in message
news:OU*************@TK2MSFTNGP15.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(ConfigurationSettings.AppSettings["connJBS"]);
//create dataset to hold all table rows
DataSet objDSJobTypes = new DataSet("JobTypes");

//Web Jobs
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebJobs = new SqlCommand("spTFL_RequestJob_JobTypes",
objConn);
objCmdWebJobs.CommandType = CommandType.StoredProcedure;
objCmdWebJobs.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebJobs.Parameters["@JobCatID"].Value = 1;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebJobs = new SqlDataAdapter(objCmdWebJobs);
objDAWebJobs.Fill(objDSJobTypes,"WebJobs");
//bind to repeater
Repeater1.DataSource=objDSJobTypes.Tables["WebJobs"].DefaultView;
Repeater1.DataBind();

//Web Projects
//Create the stored procedure command object & add parameter objects for
the stored procedure parameter
SqlCommand objCmdWebProjects = new
SqlCommand("spTFL_RequestJob_JobTypes", objConn);
objCmdWebProjects.CommandType = CommandType.StoredProcedure;
objCmdWebProjects.Parameters.Add("@JobCatID", SqlDbType.Int);
objCmdWebProjects.Parameters["@JobCatID"].Value = 2;
//create our DataAdapter object and use it to fill the dataset object
SqlDataAdapter objDAWebProjects = new SqlDataAdapter(objCmdWebProjects);
objDAWebProjects.Fill(objDSJobTypes,"WebProjects") ;
//bind to repeater
Repeater2.DataSource=objDSJobTypes.Tables["WebProjects"].DefaultView;
Repeater2.DataTextField = "JobTypeDesc";
Repeater2.DataValueField = "JobTypeID";
Repeater2.DataBind();

Any advice greatly appreciated.
Thanks
Matt

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:es****************@TK2MSFTNGP14.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.com> wrote in message
news:u7**************@TK2MSFTNGP12.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.com> wrote in message
news:%2****************@TK2MSFTNGP12.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*************@TK2MSFTNGP15.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.com> wrote in message
>> news:O3**************@tk2msftngp13.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
>>>
>>
>>
>
>



Nov 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.