locked
Extracting data from SQL Server Dataset into a class RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I’ve already read a tone of articles, but I don’t actually understand how to extract data from a SQL server table into a class where I need to perform some calculations and display calculation results into a separate form

    I am just looking for some directions on how to achieve that.

    Thanks
    Tuesday, June 25, 2013 9:23 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    I don’t suggest creating classes and adding objects to them manually. Instead, try using some ORM software that can do this automatically.

    Here is the list of different options for .NET. Try those that are built in into .NET framework, but using with any of these is not a mistake.
    • Proposed as answer by Fanny Liu Saturday, June 29, 2013 9:39 AM
    • Marked as answer by Fanny Liu Monday, July 1, 2013 10:27 AM
    Tuesday, June 25, 2013 9:27 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Below is a simple example using an ADO.NET DataTable.  For more robust requirements, consider an ORM solution like David suggested (e.g. Entity Framework).

    SqlDataAdapter dataAdapter = new SqlDataAdapter(
    "SELECT"
    + " SalesPersonID, SUM(TotalDue) AS CommissionableSales"
    + " FROM AdventureWorks2012.Sales.SalesOrderHeader"
    + " WHERE SalesPersonID IS NOT NULL"
    + " GROUP BY SalesPersonID;"
    , connectionString);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);

//add calculated field SalesCommission and calculate 5% commission for each sales person
dataTable.Columns.Add("SalesCommission", typeof(Decimal));
foreach(DataRow row in dataTable.Rows)
{
    Decimal commissionableSales = ((Decimal)row["CommissionableSales"]) * .05m;
    Decimal salesCommission = commissionableSales * .05m;
    row["SalesCommission"] = salesCommission;
}

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Fanny Liu Saturday, June 29, 2013 9:39 AM
    • Marked as answer by Fanny Liu Monday, July 1, 2013 10:27 AM
    Tuesday, June 25, 2013 11:36 AM
    Answerer

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    I don’t suggest creating classes and adding objects to them manually. Instead, try using some ORM software that can do this automatically.

    Here is the list of different options for .NET. Try those that are built in into .NET framework, but using with any of these is not a mistake.
    • Proposed as answer by Fanny Liu Saturday, June 29, 2013 9:39 AM
    • Marked as answer by Fanny Liu Monday, July 1, 2013 10:27 AM
    Tuesday, June 25, 2013 9:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Do you mean a data set  of .NET?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, June 25, 2013 10:50 AM
    Answerer
  • Question
    Sign in to vote
    1
    Sign in to vote

    Below is a simple example using an ADO.NET DataTable.  For more robust requirements, consider an ORM solution like David suggested (e.g. Entity Framework).

    SqlDataAdapter dataAdapter = new SqlDataAdapter(
    "SELECT"
    + " SalesPersonID, SUM(TotalDue) AS CommissionableSales"
    + " FROM AdventureWorks2012.Sales.SalesOrderHeader"
    + " WHERE SalesPersonID IS NOT NULL"
    + " GROUP BY SalesPersonID;"
    , connectionString);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);

//add calculated field SalesCommission and calculate 5% commission for each sales person
dataTable.Columns.Add("SalesCommission", typeof(Decimal));
foreach(DataRow row in dataTable.Rows)
{
    Decimal commissionableSales = ((Decimal)row["CommissionableSales"]) * .05m;
    Decimal salesCommission = commissionableSales * .05m;
    row["SalesCommission"] = salesCommission;
}

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Fanny Liu Saturday, June 29, 2013 9:39 AM
    • Marked as answer by Fanny Liu Monday, July 1, 2013 10:27 AM
    Tuesday, June 25, 2013 11:36 AM
    Answerer