CODEWITHSUNDEEP
c#asp.netdatatabledataset
FunFly WhiteGuy
FunFly WhiteGuy

Reputation: 173

Assign Variable To Data Table

I am returning 2 results from a SQL Stored Proc -

Select SUM(abcd) from rstuv
Select SUM(lmnop) from zxy

Then in my C# I am assigning those to Data Tables like so

DataTable firstreturnedresult = ds.Tables[0];
DataTable secondreturnedresult = ds.Tables[1];

Question being how can I assign a decimal variable to the SUM() contained in the Data Set?

Upvotes: 1

Views: 1049

Answers (2)

Steve
Steve

Reputation: 216293

The queries shown are using the scalar function SUM so the result is a datatable consisting of a single row with a single column. To get that value and assign it to a decimal variable you could write:

decimal sum1 = Convert.ToDecimal(firstreturnedresult.Rows[0][0]);

This works correctly only if your database table contains at least one row to SUM on. If the database table is empty then the first column of the returning table contains the value DbNull.Value and you need to watch for that and avoid a conversion that would trigger an exception

decimal sum1 = 0;
if(firstreturnedresult.Rows[0][0] != DBNull.Value)
   sum1 = Convert.ToDecimal(firstreturnedresult.Rows[0][0]);

Consider also to change how do you retrieve your values. Instead of building all the infrastructure required by an SqlDataAdapter you could opt for the simplicity of an SqlCommand and a call to ExecuteScalar

Something like this

 SqlCommand cmd = new SqlCommand("Select SUM(abcd) from rstuv", connection);
 object result = cmd.ExecuteScalar();
 decimal sum1 = result == null ? 0 : Convert.ToDecimal(result);

Upvotes: 2

Habib
Habib

Reputation: 223247

You are probably using ExecuteQuery against your SQL Command. Use ExecuteScalar method on your command. This will give you one single value back. 

decimal sum = (decimal) cmd.ExecuteScalar();

Upvotes: 1

Related Questions