.NET C# Question about efficiently performing multiple SQL queries

[dalearyous]

right now i am basically doing this:

con1.Open();
cmdSelectLabs = new SqlCommand("my sql command", con1);
dataReaderLabs = cmdSelectLabs.ExecuteReader();
labView.DataSource = dataReaderLabs;
labView.DataBind();
dataReaderLabs.Close();

i need to create several different sqlcommands and run them all on the same page. do i have to create a new connection, execute reader, and bind data every time? i know i can reuse the connection.

 

[KIAman]

First, the neat thing about ADO.NET is that it will take care of a lot of the overhead of managing your SQL connections for you. By default, your connections will automatically pool and utilize the same resource even if you explicitly call open and close all over your code.

You can be a little more transactionally efficient, it depends on what is your strongest or weakest link is in your system. You have several options but if you must build SQL in your .NET code (inline SQL), then you can combine all the SQL into a single command and return a dataset which holds multiple tables per each result set.

Also, depends on your concurrency requirements, you might want the connection object be static.

Everything depends on the situation.

This obviously won't work if you build your SQL based on user events

 

[dalearyous]

i don't mind having all of my SQL in one command held in a dataset as long as it is easy to reference what i need in the dataset.

pointers or examples?

 

[KIAman]

[COLOR=blue]string[/COLOR] queryString = 
  [COLOR=#a31515]"SELECT * FROM TABLE1; SELECT * FROM TABLE2"[/COLOR];
SqlDataAdapter daStuff= [COLOR=blue]new[/COLOR] SqlDataAdapter(queryString, connection);
 
DataSet dsStuff= [COLOR=blue]new[/COLOR] DataSet();
daStuff.Fill(dsStuff);
 
myBindObject.DataSource = dsStuff.Tables[0];
myOtherBindObject.DataSource = dsStuff.Tables[1];

Quick and dirty example which should point you in that direction.

 

[dalearyous]

great starting point! thanks

so, in your example does dsStuff.Tables[0] refer to the first query and dsStuff.Tables[1] refer to second query?

and do i have to declare myBindObject and myOtherBindObject

 

[BigDH01]

I'm telling you man...

http://msdn.microsoft.com/en-us/library/bb399398.aspx

Take the short amount of time it would take to get this setup and you will thank me forever.

 

[LokutusofBorg]

Even better, learn Entity Framework 4.1 (Code First). It's so easy it isn't even funny. Looking at your ADO.NET code above just makes me cringe. Writing raw ADO.NET like that is completely out-dated at this point. If you're doing it for the sake of learning then fine. But learn EF4.1 or Linq2Sql or some other ORM and set yourself free.

 

[Bulldog13]

I'm telling you man...

http://msdn.microsoft.com/en-us/library/bb399398.aspx

Take the short amount of time it would take to get this setup and you will thank me forever.

Amen.

 

[dalearyous]

will do!

thanks again for pointers. i am always looking to do things the "right" way

*edit*
ok yeah, wow, this is awesome!

**edit*
ok wait, how would i do this using SQL server? it says: // Northwnd inherits from System.Data.Linq.DataContext.

How would I mimic that in my situation?

I understand this: // Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");

but Northwnd is referring to a namespace that i obviously won't have ...

 

[Markbnj]

Even better, learn Entity Framework 4.1 (Code First). It's so easy it isn't even funny. Looking at your ADO.NET code above just makes me cringe. Writing raw ADO.NET like that is completely out-dated at this point. If you're doing it for the sake of learning then fine. But learn EF4.1 or Linq2Sql or some other ORM and set yourself free.

Subsonic FTW.

 

[dalearyous]

i edited my post ... slightly confused

i have the data connection setup for the database i need to access in my server explorer but there is no .MDF file there

 

[MSCoder610]

http://msdn.microsoft.com/en-us/library/bb384428.aspx

Basically if you already have your SQL data connection set up such that you see it as a data source in the Server Explorer window, you just do:
- Add New Item
- LINQ to SQL classes (*.dbml)
- Drag the tables you care about over onto the designer surface, and you should end up with your own [DatabaseName]Context class to use.

Not in front of Visual Studio right now but those should be the general steps.

 

[BigDH01]

http://msdn.microsoft.com/en-us/library/bb384428.aspx

Basically if you already have your SQL data connection set up such that you see it as a data source in the Server Explorer window, you just do:
- Add New Item
- LINQ to SQL classes (*.dbml)
- Drag the tables you care about over onto the designer surface, and you should end up with your own [DatabaseName]Context class to use.

Not in front of Visual Studio right now but those should be the general steps.

This is basically it. If you haven't already connected to the database with server explorer, you'll just need to right click on data connections and add a new connection. From there, just select the server name, authentication, and database and then just drag your tables over.

BTW, Entity Framework is even a bit easier. Just add the Entity data model and the wizard will walk you through the rest.

 

[dalearyous]

so after dragging over the tables i am currently using over it created a DataClasses.dbml file and if i open the *.designer.cs file i see now that it is defining a lot of things like DataClassesDataContext etc ...

is this something i need to include now in my code behind file?

 

[BigDH01]

so after dragging over the tables i am currently using over it created a DataClasses.dbml file and if i open the *.designer.cs file i see now that it is defining a lot of things like DataClassesDataContext etc ...

is this something i need to include now in my code behind file?

You'll have to include the namespace if it's not in the same namespace as your code behind. To define the datacontext now, all it takes is...

using (var dc = new DataClassesDataContext())
{
   labView.DataSource = dc.Labs.Where(d => d.whatever == whatever);
   labView.DataBind();
}

I guess webforms also have a linqdatasource control that you can look into. Don't know much about it, moved on to MVC.

 

[dalearyous]

this is what i currently have in my code behind file: (for testing purposes)

    protected void Page_Load(object sender, EventArgs e)
    {        
        DataClassesDataContext db = new DataClassesDataContext();

        var groupNameQuery = from Table in db.Database1 where Table.PROJECT_NUMBER == "136068" select Table.AWARD_NUMBER;

            foreach (var awardNumber in groupNameQuery)
            {
                Console.WriteLine(awardNumber);
            }
    }

*edit*
error: DataClassesDataContext does not contain a definition for 'Database1' (are you missing a using directive or an assembly reference?)

 

[Markbnj]

error: DataClassesDataContext does not contain a definition for 'Database1' (are you missing a using directive or an assembly reference?)

If you're missing a using directive you can hover over the missing name and click ctrl + . (period) and VS will suggest the appropriate inclusion.

 

[dalearyous]

can't seem to make that happen ... it is underlining db.Database1

 

[dalearyous]

        var groupNameQuery = from tb1 in db.Expenses where tb1.AWARD_NUMBER == "444" select tb1.AWARD_NUMBER;
        GridView1.DataSource = groupNameQuery;
        GridView1.DataBind();

this worked!

 

[dalearyous]

one last question ...

if this is my query:

var gnQuery = (from gn in db.Project where gn.GroupNumber == 1 select gn).Distinct();

this query only returns 1 entity: a group name. since the result is just 1 value ... how do i store it and then call it in my .aspx file.

i would want to call it like this:

<%=groupName%>

or whatever.

 

[BigDH01]

one last question ...

if this is my query:

var gnQuery = (from gn in db.Project where gn.GroupNumber == 1 select gn).Distinct();

this query only returns 1 entity: a group name. since the result is just 1 value ... how do i store it and then call it in my .aspx file.

i would want to call it like this:

<%=groupName%>

or whatever.

I think most people here would just tell you to use a literal. If you'll only have one group name and not need a repeater or something of that nature. Literal.Text = groupname;

 

[Oyster]

I disagree with a lot of posts in this thread that blindly pointed the OP to LINQ to SQL. I don't mean to offend anyone, but have any of you actually stepped through the debugger to look at the SQL that is generated behind the scenes for some of the complex LINQ queries. Have you looked at the metadata-related queries that ADO.NET sends to SQL Server at runtime using the SQL Server Profiler before each DB call is made?

Good luck querying tables with more than a million rows, populating data warehouses, or hosting database-heavy Websites. LINQ to SQL does make things a lot easier, but those things are extremely unoptimized. In general, any decent programmer must learn the basics of optimally querying a database and understand concepts like database indexes. You can put a "where" LINQ query which searches for 10 different things... if the query is not covered by an index, you'll be royally screwed in production.

To the OP: please take the time to learn the basics of ADO.NET: DataTable/DataView, DataSet, disposing connection objects, utilizing "using" blocks, etc. And stay away from SqlDataReader objects . In general, unless you're dealing with Web Services, DataTable is usually the fastest since all the data is "binary." DataSet is OK for multiple tables, but it is extremely verbose because it is XML-based.

Once you have your data in memory, you can do some wonderful things via LINQ... for now, LINQ to SQL has a lot to improve.

 

[LokutusofBorg]

LINQ to SQL is a dead end. It is certainly usable but Entity Framework is what has the weight behind it for future evolution from Microsoft.

EF had considerable optimization effort put into the latest version. The only way to get better optimized code is to hand-tailor every single query your application will throw at the database. But that is completely counter to the idea of an ORM. Telling someone to continue to use DataSet in this day and age is akin to suggesting someone develop using VI.

 

[dalearyous]

really? after i spent a day stumbling through linq to sql

so would i use queries to populate a DataTable?

 

[Crusty]

really? after i spent a day stumbling through linq to sql

so would i use queries to populate a DataTable?

Welcome to the .NET Framework.