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

M

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
 
K

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
 
M

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
 
M

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
 
K

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
 
M

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(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
 
K

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.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 said:
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 said:
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!)
 
M

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 said:
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 said:
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 said:
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!)


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

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 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!)


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top