12 Replies - 3605 Views - Last Post: 18 April 2011 - 09:00 AM Rate Topic: -----

#1 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Create new datatable from existing datatable

Posted 15 April 2011 - 06:29 AM

Hi everyone. I am pulling data from an informix database and displaying it in a webpage. I need to do calculations to the data, and it would be much much easier to pull the raw data from the database, do my calculations in c#, then populate my gridview with the new data. I'm guessing I have to push the new data into a new datatable first? Does that make sense? I'll be doing mathematical functions on a lot of the data, that's why I'd like to do it after I've already gotten the raw data. Any suggestions? I'm very new to C# so please be gentle.. :online2long:

The code that creates the datatable is:

            da1 = new OdbcDataAdapter(sql2, con);

            da1.Fill(ds1, "DailySplit");

            int results = 0;
            DataRow[] returnedRows;

            returnedRows = ds1.Tables["DailySplit"].Select("row_date='" + myDte + "'");

            results = returnedRows.Length;

            if (results > 0)
            {
                DataRow dr1;

                dr1 = returnedRows[0];

                GridView1.AutoGenerateColumns = false;
                GridView1.DataSource = ds1.Tables["DailySplit"];
                GridView1.DataBind();

                con.Close();
                con.Dispose();
            }
            else
            {
                con.Close();
                con.Dispose();
            }


Thanks!!

Dave

Is This A Good Question/Topic? 0
  • +

Replies To: Create new datatable from existing datatable

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Create new datatable from existing datatable

Posted 15 April 2011 - 06:57 AM

Rule #1: only get as much data from the database as you need.
Rule #2: make the database do as much of data manipulation as it can.

So, why not have a WHERE clause attached to sql2 that filters by row_data?
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8371
  • View blog
  • Posts: 31,100
  • Joined: 12-June 08

Re: Create new datatable from existing datatable

Posted 15 April 2011 - 07:06 AM

Yes that seems logical. You pull all your data back into a dataset (which holds a datatable). You can have a predefined dataset with a table that matches. Either way your data is back.
Right click on your project -> add new -> data -> dataset. Right click on the blank area and 'add table'.

Go manipulate it all you want! You don't really need to make a new datatable or anything (assuming you are not making new columns).

It's also noted if you are doing some basic to moderate math you can add an "expression" to a column in the dataset that would do some of the more routine math. It's tricky and might be a bit outside your skill set but it's there.
Was This Post Helpful? 0
  • +
  • -

#4 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Re: Create new datatable from existing datatable

Posted 15 April 2011 - 07:17 AM

Thanks for the responses guys! I appreciate it. baavgi, I am limiting the data I return by date. I use a range, beginning of the month to yesterday. Bear in mind I'm querying an informix database, not a sql database. The sql query I would need to duplicate is:

SELECT root_dsplit.acd, root_dsplit.row_date, Format([row_date],"ddd") AS myDay, root_dsplit.split, qrySplitInfo.item_name, Sum([acdcalls]+[abncalls]+[busycalls]+[disccalls]+[outflowcalls]+[dequecalls]) AS NCO, root_dsplit.acdcalls AS NCH, root_dsplit.abncalls AS NCA, Format(IIf(Sum([abncalls])=0,0,Sum([abntime])/Sum([abncalls])),0) AS AAT, IIf(Sum([acdcalls])=0,0,(Sum([I_ACDTIME]+[I_ACWTIME]+[I_OTHERTIME]+[AUXOUTTIME]+[ACWOUTOFFTIME])/Sum(([I_ACDTIME]+[I_ACWTIME]+[I_OTHERTIME]+[AUXOUTTIME]+[ACWOUTOFFTIME])+([I_AVAILTIME])))) AS OCC, IIf(Sum([acdcalls])=0,0,Sum([anstime])/Sum([acdcalls])) AS ASA, Format(IIf(Sum([acdtime])=0,0,Sum([acdtime])/Sum([acdcalls])),0) AS ATT, Format(IIf(Sum([acdcalls])=0,0,Sum([i_acdothertime]+[i_acdaux_outtime]+[i_acdauxintime])/Sum([acdcalls])),0) AS AOHT, Format(IIf(Sum([acdcalls])=0,0,Sum([i_acwtime])/Sum([acdcalls])),0) AS AACW, Format(IIf(Sum([acdtime])=0,0,Sum([acdtime]+[i_acdothertime]+[i_acdaux_outtime]+[i_acdauxintime]+[i_acwtime])/Sum([acdcalls])),0) AS AHT, (Sum([AUXOUTOFFCALLS])+Sum([ACWOUTOFFCALLS])) AS NOC, IIf(Sum([AUXOUTOFFTIME])+Sum([ACWOUTOFFTIME])=0,0,((Sum([AUXOUTOFFTIME])+Sum([ACWOUTOFFTIME]))/(Sum([AUXOUTOFFCALLS])+Sum([ACWOUTOFFCALLS])))) AS AOCT, IIf(Sum([acdcalls])=0,0,Sum([acdcalls])/Sum([acdcalls]+[abncalls]+[busycalls]+[disccalls]+[outflowcalls]+[dequecalls])) AS ACC, Sum([ACCEPTABLE])+Sum([SLVLABNS]) AS ACPT, IIf(Sum([acdcalls])=0,0,(Sum([ACCEPTABLE]))/Sum([acdcalls]+[abncalls]+[busycalls]+[disccalls]+[outflowcalls]+[dequecalls])) AS SVLVL
FROM root_dsplit INNER JOIN qrySplitInfo ON root_dsplit.split = qrySplitInfo.int_split
GROUP BY root_dsplit.acd, root_dsplit.row_date, Format([row_date],"ddd"), root_dsplit.split, qrySplitInfo.item_name, root_dsplit.acdcalls, root_dsplit.abncalls, Format([row_date],"dd"), root_dsplit.acdtime, root_dsplit.i_acwtime, root_dsplit.abntime
HAVING (((root_dsplit.acd)=1) AND ((root_dsplit.row_date) Between #4/1/2011# And #4/14/2011#) AND ((root_dsplit.split)=170));


Informix doesn't use if statements. You need to use case statements and clever use of the IFNULL statement in your query. I would need roughly 8 different case statements in the above query, and currently I can't get 1 simple case statement to return any data. I think my best bet is to return the raw data I need and do the calculations in C#. Thing is, I don't know how to push the data I've done the calculations on back into a datatable. Can anyone point me in the right direction for that?

Thanks again!

Dave
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Create new datatable from existing datatable

Posted 15 April 2011 - 04:44 PM

Wait, you're working from an Access query? The sound you here is me pounding my head against the table...

The having is wrong; it doesn't apply to any group by results. Most disturbing is "GROUP BY root_dsplit.row_date, Format([row_date],"ddd"),Format([row_date],"dd")..." Really? Are you sure? All that is kind of suspect.

For the C# code...

That's pretty easy. You just delete the rows you don't want to be there.
e.g.
foreach (DataRow row in ds1.Tables["DailySplit"].Select("row_date<>'" + myDte + "'")) {
	row.Delete();
}



That should do it.
Was This Post Helpful? 0
  • +
  • -

#6 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 06:09 AM

Hey baavgai! Thanks for taking the time to answer, I appreciate it. Originally I was working from an Access database. That was my query in that. Maybe I should just tell you what the problem is, and see if anyone has a better solution? Remember, I've only been working with Visual Studio and C# for a couple of weeks now. I'm querying an old informix database that can't handle if statements. I have multiple columns I need to do division against, however, sometimes 1 of the numbers can be a zero. If I do the division, I get a divide by zero error and my webpage fails. I need to put an if statement somewhere to catch the zero. Something like:

IF(abncalls = 0),0,(abntime/abncalls)

if abncalls = zero then zero otherwise divide abntime by abncalls.

This is just one example, I have quite of few of these to do for different columns. If I can't do the if statements in the query, I don't know where to do it and get the data into the datagrid after the fact.

Thanks for any help! I really appreciate it!!!

Dave
Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4309
  • View blog
  • Posts: 7,463
  • Joined: 08-June 10

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 06:29 AM

Well, does the division have to be done as part of the query? Why not just select the necessary columns, and then perform the division in a loop on the client side?
Was This Post Helpful? 0
  • +
  • -

#8 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 06:54 AM

Hey Curtis! YES!!! That's what I've been trying to do!! But I don't know how to get my results into the datagrid! Whew! :helpsmilie:

So, to make this easier on me, here is the code I have so far:

            con.ConnectionString = conCMS;
            con.Open();

            string mySplit = DropDownList1.SelectedValue.ToString().Trim();
            string myDte = tbDate.Text;
            string myDte2 = DateTime.Now.AddDays(-30).ToShortDateString();

            string sql2 = "SELECT row_date, split, abncalls, abntime " +
                          "FROM dsplit " +
                          "WHERE row_date Between '4/1/2011' AND '4/17/2011' AND split = '170'";

            da1 = new OdbcDataAdapter(sql2, con);

            da1.Fill(ds1, "DailySplit");

            GridView3.AutoGenerateColumns = true;
            GridView3.DataSource = ds1.Tables["DailySplit"];
            GridView3.DataBind();


This returns 1 row for each date in the range in my query. I need to divide abntime by abncalls unless abncalls = 0. Then I just want to display a 0, or null, it doesn't really matter. I know how to get the different elements out of the dataset. I would use something like:

            DataRow dRow1 = ds1.Tables["DailySplit"].Rows[0];

            string sDate = dRow1.ItemArray.GetValue(0).ToString();
            string sSplit = dRow1.ItemArray.GetValue(1).ToString();
            string sABN = dRow1.ItemArray.GetValue(2).ToString();
            string sABNt = dRow1.ItemArray.GetValue(3).ToString();


Then I can convert to int or double or date as the case may be, do the math on stuff, then "somehow" get the results into a datagrid. How could I do that?

Thanks guys, I appreciate all your help!

Dave
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4309
  • View blog
  • Posts: 7,463
  • Joined: 08-June 10

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 07:28 AM

Here's how I would do it. I'd construct a class to hold your values:

public class MyDataRow{
  public DateTime RowDate {get;set;}
  public string Split {get;set;}
  public int AbnCalls {get;set;}
  public int AbnTime {get;set;}
}


This should hold the data from one row. Now you can add a property to it that will do what you want (inside the class):

public double MyValue {
  get {
    if(AbnCalls == 0)
      return 0;
    else
      return (double)AbnTime / (double)AbnCalls;
  }
}


You can create a list of these classes and fill them after you select your data:

da1.Fill(ds1, "DailySplit");
List<MyDataRow> myRows = new List<MyDataRows>();
foreach(DataRow row in ds1.Tables["DailySplit"].Rows){
  MyRow myRow = new MyRow();
  myRow.RowDate = DateTime.Parse(row[0].ToString());
  myRow.Split = row[1].ToString();
  myRow.AbnCalls = int.Parse(row[2].ToString());
  myRow.AbnTime = int.Parse(row[3].ToString());
  myRows.Add(myRow);
}
//bind to myRows instead of the data set.


Was This Post Helpful? 1
  • +
  • -

#10 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 08:10 AM

Hey again Curtis!! Thanx!! That's it!! I really really appreciate it!

One question though, I've tested the above and it works perfectly! But what if I don't want to display AbnCalls and AbnTime, just the result of the division?

Thanks again!

Dave
Was This Post Helpful? 0
  • +
  • -

#11 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4309
  • View blog
  • Posts: 7,463
  • Joined: 08-June 10

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 08:19 AM

Don't use AutoGenerateColumns. From what I can see, is this an ASP.NET GridView we're discussing here? You should be able to use the smart dialog box for it and create columns yourself, instead of relying on auto generated ones.

Another option could be to make those fields private, and instead of manually setting each individual property, make a constructor on the class that will take all four values.
Was This Post Helpful? 1
  • +
  • -

#12 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 59
  • Joined: 07-April 11

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 08:58 AM

Cool beans!!! Thanks again Curtis! You rock! :bananaman:

Dave
Was This Post Helpful? 0
  • +
  • -

#13 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4309
  • View blog
  • Posts: 7,463
  • Joined: 08-June 10

Re: Create new datatable from existing datatable

Posted 18 April 2011 - 09:00 AM

Glad you got what you needed, and glad I was able to help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1