Saturday 10 May 2014

how to convert datatable to list in C# AND sum of column values 

DataTable table;
table = dataSet.Tables["YourTableName"];
 
// Declare an object variable.
object sumObject;
sumObject = table.Compute("Sum(Amount)", "");
Display the result in your Total Amount Label like so:
lblTotalAmount.Text = sumObject.ToString();


this.LabelControl.Text = datatable.AsEnumerable()
    .Sum( x => x.Field<int>( "Amount" ) )
    .ToString();
If you want to filter the results:
 this.LabelControl.Text = datatable.AsEnumerable()
    .Where( y => y.Field<string>( "SomeCol" ) != "foo" )
    .Sum( x => x.Field<int>( "MyColumn" ) )
    .ToString();
………………………………………………………………………………….
If you have a ADO.Net DataTable you could do
int sum = 0;
foreach(DataRow dr in dataTable.Rows)
{
   sum += Convert.ToInt32(dr["Amount"]);
}
If you want to query the database table, you could use
Select Sum(Amount) From DataTable

public decimal Total()
    {
      decimal decTotal=(datagridview1.DataSource as DataTable).Compute("Sum(FieldName)","");
      return decTotal;
    }

How can I get a sum for all the columns in a datatable? Say I had the following table. How can I calculate the "total" row? It should be easy to add total row to a datatable.
         Columns    hits     uniques    sigups, etc...
Rows                  
1                      12         1         23
2                       1         0          5
3                       6         2          9
 
 
total                  19          3        37
 
DataTable dt = new DataTable();
            int sum = 0;
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    sum += (int)dr[dc];
                }
            } 

private void ComputeBySalesSalesID(DataSet dataSet)
{
    // Presumes a DataTable named "Orders" that has a column named "Total."
    DataTable table;
    table = dataSet.Tables["Orders"];
 
    // Declare an object variable. 
    object sumObject;
    sumObject = table.Compute("Sum(Total)", "EmpID = 5");
}

int sum = (int)table.Compute("Sum(pieces)", "article = 'milk' AND artno='15'");

var results = dataTable.AsEnumerable()
                       .Where(row => row.Field<string>("article") == "milk" && 
                                     row.Field<int>("artno") == 15)
                       .Select(row => row.Field<int>("pieces"))
                       .Sum();

List<int> totalPrice = new List<int>();
var SumOftotalPrice = (from s in totalPrice
                       select s).Sum();
In C# do the following to conver the string list into a int list and the sum it.
List<string> list_str = new List<string>();
...
var list_int = list_str.Select( (x)=>int.Parse(x) );
int sum = list_int.Sum();

Use Sum()
 List<string> foo = new List<string>();
 foo.Add("1");
 foo.Add("2");
 foo.Add("3");
 foo.Add("4");
 
 Console.Write(foo.Sum(x => Convert.ToInt32(x)));
Prints:
10
If you want show sum of all columns you need 2 loops.
I think bellow code can be helpful for you:
 

 Collapse | Copy Code
 
int totalSum= 0;
foreach (ListViewItem item in [YourListView])
         for(int i = 0; i < [NumberOfColumns]; i++)
                 totalSum += int.Parse(item.Subitems[i].Text);


// DataSet ds = new DataSet();
DataTable dt = new DataTable();
adap.Fill(dt);

if (dt.Rows.Count &gt; 0)
{
 
Label1.Text = dt.Rows[0]["Total"].ToString();
}

if (e.ColumnIndex == 4)//4 is the column that you want to add up in real time
{
double sum = 0;
foreach (DataGridViewRow item in dataGridView1.Rows)
{
if (!item.IsNewRow)
{
sum += Convert.ToDouble(item.Cells[4].Value.ToString());
label1.Text = "" + sum;
}
}
 
}
private void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        if (e.ColumnIndex == 5)
            textBox9.Text = CellSum().ToString();
    }
 
    private double CellSum()
    {
        double sum = 0;
        for (int i = 0; i < dataGridView2.Rows.Count; ++i)
        {
            double d = 0;
            Double.TryParse(dataGridView2.Rows[i].Cells[5].Value.ToString(), out d);
            sum += d;
        }
        return sum;
    }

private void dataGridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
    {
        if (e.ColumnIndex == 5)
        {
            summition();
        }
    }
    void summition() 
    { 
        double sum = 0;
        foreach (DataGridViewRow row in dataGridView2.Rows)
        {
            if(!row .IsNewRow )
                sum += Convert.ToDouble(row.Cells [5].Value .ToString () );
        }
 
 
        textBox9.Text = sum.ToString();
    }
var value = dataGridView1.Rows.Cast<DataGridViewRow>().Sum(x =>
                                                                {
                                                                  double val = 0;
                                                                  if (double.TryParse(x.Cells["Column"], out val))
                                                                    return val;
                                                                  else
                                                                    return 0;
                                                                });

………………………………………………………………………………………………………………………………………….
decimal Total = 0;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
Total+= Convert.ToDecimal(dataGridView1.Rows[i].Cells["ColumnName"].Value);
}
labelName.Text = Total.ToString(); - See more at: http://codingresolved.com/discussion/248/get-total-column-datagridview-c/p1#sthash.2ZV8Ur7t.dpuf
private void btn_sum_Click(object sender, EventArgs e)
        {
            int sum = 0;
            for (int i = 0; i < dataGridView1.Rows.Count; ++i)
            {
                sum += Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value);
            }
            label1.Text = "Total sum is:"+sum.ToString();





List<Employee> emp = new List<Employee>();
 
//Maintaining DataTable on ViewState
//For Demo only
 
DataTable dt = ViewState["CurrentEmp"] as DataTable;
 
//read data from DataTable 
//using lamdaexpression
 
 
emp = (from DataRow row in dt.Rows
 
   select new Employee
   {
       _FirstName = row["FirstName"].ToString(),
       _LastName = row["Last_Name"].ToString()
 
   }).ToList();

using System.Data;
 
 
var myEnumerable = myDataTable.AsEnumerable();
 
List<MyClass> myClassList =
    (from item in myEnumerable
     select new MyClass{
         MyClassProperty1 = item.Field<string>("DataTableColumnName1"),
         MyClassProperty2 = item.Field<string>("DataTableColumnName2")
    }).ToList();

Try this code and This is easiest way to convert datatable to list
List<DataRow> listtablename = dataTablename.AsEnumerable().ToList();


This example retrieves a value from a text box on a Windows Form and displays it in a text box on another form.

Example

In Form1.cs:
private Form2 otherForm;
private void GetOtherFormTextBox()
{
    textBox1.Text = otherForm.TextBox1.Text;
}
In Form2.cs:
public TextBox TextBox1
{
    get
    {
        return textBox1;
    }
}

This example displays a second form from another Windows Form.
Example
private void button1_Click(object sender, System.EventArgs e)
{
    Form2 frm = new Form2();
    frm.Show();
}

DataTabledt = CreateDataTableInSomeWay();

List<DataRow> list = new List<DataRow>();
foreach (DataRowdr in dt.Rows)
{
list.Add(dr);
}


DataTable table = GetTable();
        //
        // Use DataTable here with SQL.
        //
    }

/// <summary>
    /// This example method generates a DataTable.
    /// </summary>
staticDataTableGetTable()
    {
        //
        // Here we create a DataTable with four columns.
        //
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
    }
}



Example-2 // convert list to datatable
using System;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Windows.Forms;
 
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
    {
        public Form1()
        {
        InitializeComponent();
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
        // Example list.
            List<string[]> list = new List<string[]>();
        list.Add(new string[] { "Column 1", "Column 2", "Column 3" });
        list.Add(new string[] { "Row 2", "Row 2" });
        list.Add(new string[] { "Row 3" });
 
        // Convert to DataTable.
        DataTable table = ConvertListToDataTable(list);
            dataGridView1.DataSource = table;
        }
 
        staticDataTableConvertListToDataTable(List<string[]> list)
        {
        // New table.
        DataTable table = new DataTable();
 
        // Get max columns.
        int columns = 0;
        foreach (var array in list)
            {
               if (array.Length> columns)
               {
               columns = array.Length;
               }
            }
 
        // Add columns.
        for (int i = 0; i < columns; i++)
            {
               table.Columns.Add();
            }
 
        // Add rows.
        foreach (var array in list)
            {
               table.Rows.Add(array);
            }
 
        return table;
        }
    }
}



DataTabledt=YourList.ToDataTable();
 
publicstaticDataTableToDataTable<T>(thisList<T>iList)
{
DataTabledataTable=newDataTable();
PropertyDescriptorCollectionpropertyDescriptorCollection=
TypeDescriptor.GetProperties(typeof(T));
for(int i =0; i <propertyDescriptorCollection.Count; i++)
{
PropertyDescriptorpropertyDescriptor=propertyDescriptorCollection[i];
Typetype=propertyDescriptor.PropertyType;
 
if(type.IsGenericType&&type.GetGenericTypeDefinition()==typeof(Nullable<>))
type=Nullable.GetUnderlyingType(type);
 
 
dataTable.Columns.Add(propertyDescriptor.Name, type);
}
object[] values =newobject[propertyDescriptorCollection.Count];
foreach(T iListIteminiList)
{
for(int i =0; i <values.Length; i++)
{
values[i]=propertyDescriptorCollection[i].GetValue(iListItem);
}
dataTable.Rows.Add(values);
}
returndataTable;

}
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)