c# data manipulation prior to gridview binding

tgabe213 · Aug 26, 2009

What I'm trying to do is quite simple, I think. I just don't know how to go about it.

I'm hitting my SQL server database and retrieving only about 5-6 columns. The most amount of rows I'm going to have is MAYBE 20-30 tops.

Here's what I have so far. Just the connection, retrieve my data, bind it, and close the connection.

Code:

SqlConnection SQL_details = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr1"].ConnectionString);

        try
        {
            
            SQL_details.Open();
        }
        catch (Exception e)
        {
            //do something here?
        }

        SqlDataAdapter cmd_gv = new SqlDataAdapter("SELECT incID, priorityLevel, CONVERT(VARCHAR(8), openDate, 1) AS open_Date, locationNum, descr FROM incDetails", SQL_details);

        DataSet ds = new DataSet();
        cmd_gv.Fill(ds, "gv");

        gvMyinc.DataSource = ds.Tables["gv"].DefaultView;
        gvMyinc.DataBind();        

        try
        {
            SQL_details.Close();
        }
        catch (Exception e)
        {
            //do something here?
        }

This throws my data into the gridview just fine. However, what I'd like to do is be able to go to through each of these rows and evaluate them, and determine how to add them to the gridview.

Example: I'd like all of MY incidents to be at the top. Ownership comes from the currentOwner column from the table. Current owner will also be grabbed in the browser as the users current windows NT id. So for each row, I'd like to see if currentOwner == myNTid. if it is, add it to the gridview, and change the background color of it. Once all of mine have been added, then I'd like to add those that have currentOwner == NULL, color them, then add the rest of them beneath that.

Thanks!

Tytalus · Aug 26, 2009

Before binding the DataSet to the GridView I'd suggest hooking up an event handler to the RowDataBound event, which will be fired for each row that's handled.

I assume you can look online for proper documentation for this event (assuming it's what you need) and handle the message appropriately. Good luck!

tgabe213 · Aug 26, 2009

Tytalus said:
Before binding the DataSet to the GridView I'd suggest hooking up an event handler to the RowDataBound event, which will be fired for each row that's handled.

I assume you can look online for proper documentation for this event (assuming it's what you need) and handle the message appropriately. Good luck!

Thanks for the input, but I don't think that's what I'd really want, is it? It would solve 1/2 of my problem.

GridView..::.RowDataBound Event

Occurs when a data row is bound to data in a GridView control.

Maybe it's my interpretation, but wouldn't that give you the chance to do something to the row right before it binds?

For my situation, I would look at the data in the row, and if the currentOwner is me, then add it, if it isn't, I'll need to come back to it later.

Sgraffite · Aug 26, 2009

tgabe213 said:
For my situation, I would look at the data in the row, and if the currentOwner is me, then add it, if it isn't, I'll need to come back to it later.

Maybe I'm not understanding what you are looking for, but couldn't you eliminate anything that isn't your's via the sql query to begin with?

Or if you want all tickets, but your tickets first, you could use an ORDER BY in the sql query so that your tickets are at the top of the list.

calebb · Aug 26, 2009

How about something like this to get you started?

Code:

DataTable dtSorted = ds.Tables["gv"].Clone();
DataRow[] arrDrSorted = dt.Select("[incID] = 1234", "[priorityLevel] ASC, [openDate] DESC");  //Put the highest priority and most recently opened tasks at the top of the array

foreach(DataRow dr in arrDrSorted)
{
   dtSorted.Rows.Add(dr.ItemArray);
}

tgabe213 · Aug 27, 2009

Thanks calebb (as always..)!

-So you're creating a new datatable named dtSorted which is just a copy of the datatable gv
-Then you're creating an array named arrDrSorted. And based off of your comment, it's only grabbing incident 1234, and sorting by priorityLevel and openDate?
-Now you're just looping through the array and looking at each row. The only thing that throws me off is dr.ItemArray. Should ItemArray be arrDrSorted?

I think I see where you're leading me.

So, this will be broken up in to 3 arrays and 3 loops. The first one will be to grab only those that are assigned to me, look at them each, and add them to the new datatable. Then make another array that is equal only to those that are unassigned, loop them, then the last will be all remaining, loop thru, add them, then bind the new Datatable to the gridview.

Sounds like it is possible to me, but I'm wondering if there is a better way than 3 arrays and 3 loops.

tgabe213 · Aug 27, 2009

calebb said:

How about something like this to get you started?

Code:

DataTable dtSorted = ds.Tables["gv"].Clone();
DataRow[] arrDrSorted = dt.Select("[incID] = 1234", "[priorityLevel] ASC, [openDate] DESC");  //Put the highest priority and most recently opened tasks at the top of the array

foreach(DataRow dr in arrDrSorted)
{
   dtSorted.Rows.Add(dr.ItemArray);
}

Wouldn't I need a temp DataTable? You are creating dtSorted, and making it an identical copy of "gv".
Then we have an array, and that array is only equal to the data that is equal to the results of the dt.Select.
Now our loop looks at each element in the array, and adds it to the dtSorted? Isn't that added the rows right back into dtSorted?

Sgraffite · Aug 27, 2009

It looks like you are querying the data in an order you don't want, then using C# to reorder it the way you want. Why not just get it in the order you want first and save all that extra processing?

tgabe213 · Aug 27, 2009

Sgraffite said:
It looks like you are querying the data in an order you don't want, then using C# to reorder it the way you want. Why not just get it in the order you want first and save all that extra processing?

I see what you mean, and I was thinking about that, but I don't think it's possible.

So, you're talking about my SQL statement to get it initially? Yes, that would be a lot easier, but is it possible?

I need to select xcol, ycol, zcol FROM incDetails....

But sorted in the manner of my current windows ID on the top, then those unassigned, and everything else after.

I don't want an ORDER BY, because that's a column, and the current users incidents are always on top, so a simple sort on currentOwner won't work

So my output should be

Code:

100     8/23/2009     [B]myid   [/B]               description
103     8/25/2009     [B]myid   [/B]               description
104     8/25/2009     [B]myid  [/B]                description
102     8/24/2009     [B][I]null[/I]  [/B]            description
105     8/26/2009     [B][I]null[/I]     [/B]         description
106     8/27/2009     [B]someone else[/B]     description

I could easily break this up into 3 separate selects, but I don't think I want to do that. I could possibly have 3 selects into 3 datatables, and just populate a new datatable from these 3 selected datatables in the order I want.

Might as well do what I'm doing now. Only hitting the db once

Sgraffite · Aug 27, 2009

I'm fairly sure you can do it with one query, and get it in the order you want.

tgabe213 said:
I don't want an ORDER BY, because that's a column, and the current users incidents are always on top, so a simple sort on currentOwner won't work

I'm not sure what you mean by not wanting an ORDER BY, that would be how your sort it in the query

Here's a psuedo-query of what I mean. Say you are user_id 100, and you want your tickets on top, and then the rest of the tickets should be shown in order of most recent.

Code:

select incident_date,user_id,incident_description
from incidents
order by if(user_id = 100,1,0) desc,incident_date desc;

This will put all of your tickets first, ordered by their date newest to oldest, then everyone else's tickets ordered newest to oldest under your's.

tgabe213 · Aug 27, 2009

Sgraffite said:
I'm fairly sure you can do it with one query, and get it in the order you want.

I'm not sure what you mean by not wanting an ORDER BY, that would be how your sort it in the query

Here's a psuedo-query of what I mean. Say you are user_id 100, and you want your tickets on top, and then the rest of the tickets should be shown in order of most recent.

Code:

select incident_date,user_id,incident_description from incidents order by if(user_id = 100,1,0) desc,incident_date desc;

This will put all of your tickets first, ordered by their date newest to oldest, then everyone else's tickets ordered newest to oldest under your's.

I didn't know you could do any order by if...

Code:

select incID, currentOwner,descr
from incDetails
order by if(currentOwner = 'W855479',1,0) desc,openDate desc;

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

Google doesn't find anything either...But regardless...I want my tickets first, then those that are unassigned, then everyone else's after that. That's why I don't think it can be done with SQL

Sgraffite · Aug 27, 2009

tgabe213 said:
I didn't know you could do any order by if...

Code:

select incID, currentOwner,descr from incDetails order by if(currentOwner = 'W855479',1,0) desc,openDate desc;

Google doesn't find anything either...But regardless...I want my tickets first, then those that are unassigned, then everyone else's after that. That's why I don't think it can be done with SQL

I'm not sure what SQL you are using, but you can do it in MySQL. I'd imagine other versions of SQL would have a way as well, but different syntax. You can also write it in this format:

Code:

select incident_date,user_id,incident_description,if(user_id = 100,1,0) as isMe
from incidents
order by isMe desc,incident_date desc;

For unassigned tickets, assuming they have no user_id assigned, you can do a join and check for nulls where the user_id doesn't match the user table.

I still think it is possible to do via SQL since that is something that relational databases are good at *shrug*

Sgraffite · Aug 27, 2009

I'm assuming you are not using MySQL since you get a syntax error, here is what I think is the MSSQL version of an IF statement (I have no way to test it though)

Code:

select incID, currentOwner,descr
from incDetails
order by (select case currentOwner = 'W855479' then 1 else 0 end) desc,openDate desc;

Pwyl_The_Destroyer · Aug 27, 2009

have you considered using Union All, with one select per subset?

tgabe213 · Aug 27, 2009

Pwyl_The_Destroyer said:
have you considered using Union All, with one select per subset?

Excellent!

Just one of those things you don't know about until you use it...

Code:

SELECT incID, currentOwner, priorityLevel, CONVERT(VARCHAR(8), openDate, 1) AS openDate, locationNum, descr FROM incDetails WHERE currentOwner = @nt_id
UNION ALL
SELECT incID, currentOwner, priorityLevel, CONVERT(VARCHAR(8), openDate, 1) AS openDate, locationNum, descr FROM incDetails WHERE currentOwner IS NULL
UNION ALL
SELECT incID, currentOwner, priorityLevel, CONVERT(VARCHAR(8), openDate, 1) AS openDate, locationNum, descr FROM incDetails WHERE currentOwner <> @nt_id

Code:

100	xxxx479	0	08/21/09	90	Mount disk error.
103	xxxx479	5	08/24/09	94	lalala Upgrade.
104	xxxx479	2	08/24/09	32	PNS intercom over phone line
109	NULL	3	08/24/09	2	He got traded or something
105	xxxx973	2	08/24/09	15	Whatever I want
106	xxxx950	2	08/24/09	15	Whatever I want
107	xxxx973	2	08/24/09	33	Whatever I want
108	xxxx950	5	08/24/09	33	Whatever I want

Notice mine are on top (xxxx479), null is next, and the rest below it. Now I can use Tytalus' RowDataBound event, I think.

calebb · Aug 27, 2009

.clone() just copies the datatable schema without any data in it.

And, as far as the pseudo-code earlier using the IF in the WHERE clause, that's another great way to accomplish what you're trying to do... but on the SQL side.

The actual syntax would be: (I'll leave it in the select list so you can see what it's doing)

Code:

select 
	incident_date,
	user_id,
	incident_description,
	CASE WHEN user_id = 100 THEN 1 ELSE 0 END AS isItTGabe213OrNot
FROM incidents
ORDER BY 
	CASE WHEN user_id = 100 THEN 1 ELSE 0 END desc,
	incident_date desc

And re: it it expensive to box & unbox the data several times (take it on and off the stack multiple times). Yes. Yes it is. But it's all relative. You're talking 100's of microseconds on a modern processor.

edit: after reading over your actual requirements (yours, then unassigned, then everyone else)

Code:

select 
	incident_date,
	user_id,
	incident_description,
	CASE 
		WHEN user_id = 100 THEN 10 
		WHEN user_id IS NULL THEN 5
		ELSE 0 END AS isItTGabe213OrNot
FROM incidents
ORDER BY 
	CASE 
		WHEN user_id = 100 THEN 10 
		WHEN user_id IS NULL THEN 5
		ELSE 0 END desc,
	incident_date desc

Pwyl_The_Destroyer · Aug 27, 2009

tgabe213 said:
Notice mine are on top (xxxx479), null is next, and the rest below it. Now I can use Tytalus' RowDataBound event, I think.

What are you using the databound event for?

tgabe213 · Aug 27, 2009

Pwyl_The_Destroyer said:
What are you using the databound event for?

Coloring the background of the row based on specific criteria.

c# data manipulation prior to gridview binding

tgabe213

2[H]4U

Tytalus

Supreme [H]ardness

tgabe213

2[H]4U

Sgraffite

Supreme [H]ardness

calebb

[H]F Junkie

tgabe213

2[H]4U

tgabe213

2[H]4U

Sgraffite

Supreme [H]ardness

tgabe213

2[H]4U

Sgraffite

Supreme [H]ardness

tgabe213

2[H]4U

Sgraffite

Supreme [H]ardness

Sgraffite

Supreme [H]ardness

Pwyl_The_Destroyer

Limp Gawd

tgabe213

2[H]4U

calebb

[H]F Junkie

Pwyl_The_Destroyer

Limp Gawd

tgabe213

2[H]4U