ASP.net manipulate data in dataset

Question

i have managed to connect to an interbase database and get data from the database and store it in a dataset, i need to manipulate some of the data in the dataset and do something like the following,

add a new column in the dataset called total, for each row i need to add the 2nd and 3rd column and place the result into the total column.

can some one shed some light on how i may do this, im using the following code to get the data into a data set.

    OdbcConnection conn = new OdbcConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;

    DataSet ds = new DataSet();

    OdbcDataAdapter da = new OdbcDataAdapter("SELECT * FROM MTD_FIGURE_VIEW1", conn);

    da.Fill(ds);

nemke · Accepted Answer · 2011-06-15 09:35:45Z

2

This is the simplest way to do it

 ds.Tables[0].Columns.Add("Sum", typeof(double), "Col1 + Col2");

answered Jun 15, 2011 at 9:35

nemke

2,4983 gold badges38 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Syeda · Accepted Answer · 2011-06-15 10:01:41Z

IF you are sure that your column values would never be null or empty then the best way is

ds.Tables[0].Columns.Add("Sum", typeof(double), "Col1 + Col2");

In case if your any column's value will be null then it wont give any exception but the total value would also be empty ...

If there can be any null value then try this

           dt.Columns.Add("Total", typeof(double));
        foreach (DataRow dr in dt.Rows)
        {
            if (String.IsNullOrEmpty(dr["col1"].ToString()))
                dr["col1"] = 0.0;
            if (String.IsNullOrEmpty(dr["col2"].ToString()))
                dr["col2"] = 0.0;

            dr["Total"] = Convert.ToDouble(dr["col1"]) + Convert.ToDouble(dr["col2"]);
        }

In this case if you never check for null or empty value then this will give you a run time exception...

Kamran Khan · Accepted Answer · 2011-06-15 09:44:14Z

1

Following is how to >manipulate some of the data in the dataset

ds.Tables[0].Columns.Add("Sum", typeof(string));
foreach (DataRow drRow in ds.Tables[0].Rows)
{
    drRow["Sum"] = (Convert.ToInt32(drRow["Col1"].ToString()) + Convert.ToInt32(drRow["Col2"].ToString())).ToString();
}

--UPDATE--

Something like following, using ternary operator:

drRow["Sum"] =
    (string.IsNullOrEmpty(drRow["Col1"].ToString()) ? 0.00 : Convert.ToDouble(drRow["Col1"].ToString())) +
    (string.IsNullOrEmpty(drRow["Col2"].ToString()) ? 0.00 : Convert.ToDouble(drRow["Col2"].ToString()));

edited Jun 15, 2011 at 9:44

answered Jun 15, 2011 at 8:52

Kamran Khan

9,9842 gold badges33 silver badges41 bronze badges

2 Comments

c11ada Over a year ago

thats perfect the only thing is all these values are of type double so can i just do something as follows Convert.ToDouble(drRow["Col1"].ToString() ?

Kamran Khan Over a year ago

Correct. I would also check for !(.IsNullOrEmpty) before .ToDouble()ing.

Pleun · Accepted Answer · 2011-06-15 10:54:59Z

1

Have you considered letting the database do the work

OdbcDataAdapter da = new OdbcDataAdapter("SELECT *, (col1+col2) as sum FROM MTD_FIGURE_VIEW1", conn);

PS: you might want to get rid of your select * and use columnames

answered Jun 15, 2011 at 10:54

Pleun

8,9102 gold badges32 silver badges51 bronze badges

2 Comments

Sorin Comanescu Over a year ago

Seems the right approach to me, with the only remark that maybe the summing should be guarded against nulls with isnull().

Pleun Over a year ago

@Sorin Comanescu yes, it depends on the definition of the database(assumuing the FROM ...view1 is indeed a view) => I would handle that already in the view

Collectives™ on Stack Overflow

ASP.net manipulate data in dataset

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related