| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2006-09-18 : 14:59:12
|
| Hello all! In my seemingly neverending quest to rewrite a ColdFusion website, I have encountered another issue that has got me a little stumped.I am working with a query that returns about 60 columns. In the query, each of the items in the resultset are coming back like "CHILD.CHILD_GUID". I am still very new to .NET, but what I am doing is calling this proc using a SqlCommand object, putting the resulset into a SqlDataReader and then passing the SqlDataReader set into a function that reads the results into a DataTable, which gets passed back to my code-behind page. I am doing that so that my database connection can get closed, but I can continue to work with a dataset. (I am open to constructive criticism on my methods!). I am running into a problem with the DataTable because this 60 column query has got a column "CHILD.CHILD_GUID" and it also has a "CASE.CHILD_GUID". Two columns with different aliases, but the same name. The DataTable does not like this. Do you have any work arounds? Or better suggestions on how to work with a disconnected set of data?Thanks!AjHey, it compiles. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-18 : 15:05:48
|
| Give the columns an alias too. SELECT CHILD.CHILD_GUID AS ColumnAlias1, CASE.CHILD_GUID AS ColumnAlias2...Tara Kizer |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-18 : 15:24:16
|
| I suspect you are doing something like this:select a.*, b.* from a inner join b ..... Explicitly list out the columns to retrieve, and only include matching columns (i.e., your GUID column) once.- Jeff |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-18 : 16:47:57
|
| Tara, based on your reply my assumption is that there is no way for me to use .NET to concatenate the alias name with the column name. If that is the case, I will add " AS Blah_blah_blah" to the columns so that I can reference them.Jeff, the query does explicitly list the colums that are being returned. But .NET does not appear to recognize the join alias as a column name. So my sql code shows CHILD.CHILD_GUID, but .NET shows it as just CHILD_GUID.Thanks!AjHey, it compiles. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-18 : 17:18:33
|
| I wouldn't assume anything from my post. I am giving you an alternative solution so that your names are unique. I'll leave it up to someone else with more experience with .NET to answer if there is a solution in .NET.Tara Kizer |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-18 : 17:28:49
|
| Well, your solution certainly works Tara. It will be a pain to go back and add the column aliases, but hey, call it job security!Hey, it compiles. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-18 : 18:53:46
|
quote:
...putting the resulset into a SqlDataReader and then passing the SqlDataReader set into a function that reads the results into a DataTable
This is an absolutely terrible architecture!DataReaders are marshalled by reference and should NOT be used across boundaries.Just get the data as a DataTable in the first place!DavidMProduction is just another testing cycle |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-18 : 19:47:54
|
quote:
Originally posted by byrmol
quote:
...putting the resulset into a SqlDataReader and then passing the SqlDataReader set into a function that reads the results into a DataTable
This is an absolutely terrible architecture!DataReaders are marshalled by reference and should NOT be used across boundaries.Just get the data as a DataTable in the first place!DavidMProduction is just another testing cycle
how did I miss that part? Another thing I see too often is people using DataSets when all they need is DataTables ....As David says, just fill up a DataTable using a SqlDataAdapter after you have your command all set. No need for the DataReader step.- Jeff |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-19 : 05:28:14
|
It depends on what you're doing with the data.If you only want to diplay it without modifying it, open reader with CloseConnection option in the constructor,bind the datareader to the repeater and after databind just close the datareader.that the fastes way of displaying data.if you're datagrid-ing the data then what byrmol said is best.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-19 : 17:03:08
|
| >>It depends on what you're doing with the data.No it does not.It depends on the architecture.DataTable and DataSets are marshalled by value.DataReaders are marshalled by reference.The consequences of this is painfully obvious if you try to use a DataReader as the returning object from a webservice call...DavidMProduction is just another testing cycle |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-20 : 02:40:23
|
| true.I haven't thought about web services at all.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-20 : 16:29:44
|
| Here is a rundown of what I am working with.A page "child_detail.aspx.vb" calls a function to populate an "asp:literal" control on my aspx page. The function that is called is on SiteClass.vb page in my App_Code folder (a class page with most of my query calls, it also contains my database connection subroutine). This function runs a stored procedure and then takes the resultset and puts it into a SqlDataReader object. That object is passed into a function that turns it into a DataTable (just a table, no keys or relationships). That DataTable is then passed back to the child_detail.aspx.vb page so that I can use the information in it to populate asp:literal(s) throughout my page. This was my first attempt at understanding the SqlDataXXX objects and the DataTable objects. I still have too much old school VB6 in me. I assumed I could pass a SqlDataReader back to my aspx.vb page and read it. Well, I can't since my connection is dropped shortly after the query that populates it is ran. So, I threw it into a DataTable instead and passed that back.Again, I am open to constructive criticism since I am trying to learn the best methods to use in .NET. I do know that I don't want to make my aspx.vb pages close my database connection. I want the SiteClass.vb page to handle all of that on it's own, so I don't forget to close a connection. So, I am not binding any of this data to datagrids. I use it for display purposes only. I am not modifying any of it, or needing to pass it back to the database. Again, I am open to other suggestions. Thanks!AjHey, it compiles. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-21 : 02:56:48
|
ok here's what i do for simple apps that look like yours and don't need a middle tier.i put a ProjectNameDb.cs in app_code which holds all functions for data access.All of these functions are static and each returns a SqlDataReader object like this:/* in ProjectNameDb.cs */public static SqlDataReader GetAllData(){ try { _sqlCommandObject.Connection.Open(); // tells the datareader to close the connection when we close the reader SqlDataReader rdr = sqlCommandObject.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } catch { if(_sqlCommandObject.Connection.State == ConnectionState.Open) _sqlCommandObject.Connection.Close(); return null; }}/* in aspx.cs page */SqlDataReader rdr = ProjectNameDb.GetAllData();yourRepeaterCtrl.DataSource = rdr;yourRepeaterCtrl.DataBind();rdr.Close();and that's it. fast neat and short.you should also know that populating a datatable also uses a datareader in the code behind.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-09-21 : 03:56:57
|
| Not sure if this will help as my understanding of this is very simplistic but here goes (note also that this is ASP 1/1.1) - Datareader - this is a read only forward only stream of data from the source and so is relatively fast. These are based on a single SQL statement from a single database (though presumably that can be a sproc that calls other databases). These can only be bound to a single controlDatatable - this is (as the name suggests) a set of data from a unique data sourceDataset - this is a disconnected set of datatables and can contain relationships (datarelation's) etc. (Have also been led to believe that creating these in code is somehow less efficient than doing it at design time - not sure how that works! (and quite frankly I find it much simpler to write in code) I presume it is to do with early and late binding but perhaps someone more knowledgeable can explain).Dataview - not sure this belongs here but is similar to a SQL view in this case on a datatableSteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-21 : 04:10:04
|
| It's not about being less efficient it's about type safety.you can have a typed datased that you can simply do:myDataSetOfPersonsRow.PersonNameinstead of myDataSetOfPersonsRow["PersonName"]myDataSetOfPersonsRow["PersonName"] is faster becase a typed dataset is derived from a general dataset.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-21 : 09:50:20
|
| Now this is what I have been searching for! Thanks spirit! The problem that I had before had to do with the fact that I was manually closing down all connections. This is great! Thanks again!AjHey, it compiles. |
|
|
|
|
I can't be the first to have this problem