LINQ DataTables & DataSet

Hello,

I'm new to LINQ and wanted to know if you can use a DataReader, DataTable, or even a DataSet when working with Data like ADO.NET Data Access. If so can someone please send me a link with tutorials that explains how to work/create DataTables, and DataSets withing LINQ.
LVL 4
asp_net2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
0
DhaestCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

asp_net2Author Commented:
wow, seems a lot different in LINQ using DataTable and DataSets than what it was like using just ADO.NET :(

Do you have any other easier examples/links to breaks everything down easier??

Also, below is a working example in LINQ that querys a DB against a value entered in a textbox control and returns one row of data. the names of the rows are bldg_id and bldg_name. Can you give me an example of what this would look like using DataTable??

    protected void SearchByIDBindToLabel()
    {
        DataClassesDataContext SearchByIDBindLabel = new DataClassesDataContext();

        // Value we are searching against from the Stored Procedure.
        int bldg_id = 0;

        // Check to see if value is an integer value if so then execute LINQ query.
        if (Int32.TryParse(txtSearchByID.Text, out bldg_id))
        {
            // Execute if value entered is a valid integer.
            var IDLabel = SearchByIDBindLabel.PrinterApp_ID(bldg_id);

            foreach (PrinterApp_IDResult printers in IDLabel)
            {
                lblBuildingName.Text = printers.bldg_name;
                lblBuildingID.Text = printers.bldg_id.ToString();
            }
        }

        else
        {
            lblError.Text = "Error: value in not an integer, please try again";
        }
    }

    protected void btnSearchByID_Click(object sender, EventArgs e)
    {
        SearchByIDBindToLabel();
    }
0
Fernando SotoRetiredCommented:
Hi asp_net2;

To your question, "wanted to know if you can use a DataReader, DataTable, or even a DataSet when working with Data like ADO.NET Data Access", Yes to DataSet and DataTable. But Linq to DataSet, DataTable will not query the database and return an object of DataTable. This linq type is used to query an existing DataTable. If you wish to query the database and create a DataTable object from its results Dhaest has posted a link re-posted here http://weblogs.asp.net/stevesloka/archive/2008/01/28/linq-to-datatable.aspx.

Fernando
0
Fernando SotoRetiredCommented:
You can try this tutorial on Linq to DataSet
http://scab.asthra.net/APP/post/NC01_W_004_01.aspx
0
asp_net2Author Commented:
That really didn't help much FernandoSoto. It's not a very clear explanation.
0
Fernando SotoRetiredCommented:
Lets start with what you don't understand with the post.
0
asp_net2Author Commented:
I'm use to the following below when using ADO.NET. Just an example that I"m used to. But not sure how to use this type of process correctly when working with LINQ to SQL.

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);

SqlCommand cmd = new SqlCommand();
cmd.SelectCommand = "SP";
cmd.CommandType = CommandType.SP;
cmd.Connection = conn;

DataTable dt = new DataTable();

DataAdapter adp = new DataAdapter();

adp.Fill(dt);

foreach (DataRow row in dt)
{
label.Text = dt{FieldName);
label2.Text = dt(FieldName);
}

0
Fernando SotoRetiredCommented:
Hi asp_net2;

I guess we need to figure out what you really want to do.

Do you want to query the database with Linq to SQL?
Or do you want to use standard ADO .Net to load a DataSet / DataTable?
What will you be doing with the results, like place it in a DataGridView?

Fernando
0
abhinayp86Commented:
To do the same,

u need to add an item "Linq to sql classes" to ur project. (rightclick on ur web site--> Add new item --> Linq to sql classes.

It creates a dbml file in ur project.

Now in view of ur visual studo, u will have this server explorer. Connect to ur database using this server explorer and drag and drop the tables u want on the .dbml file.

It will automatically create a class file. If, for instance, datacontext is the class it created, then u can use

DataContext db = new DataContext();
var q= from a in db.YourTableName
                           select a;

now q is ur returned Data.
u can directly bind it to any gridview.

but if u want to convert it to a data table,

u need to call the following method

DataTable dt = new DataTable();

dt = LINQToDataTable(q);




private DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
    {
        DataTable dtReturn = new DataTable();

        // column names 
        PropertyInfo[] oProps = null;

        if (varlist == null) return dtReturn;

        foreach (T rec in varlist)
        {
            // Use reflection to get property names, to create table, Only first time, others will follow 

            if (oProps == null)
            {
                oProps = ((Type)rec.GetType()).GetProperties();
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;

                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                    == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                }
            }

            DataRow dr = dtReturn.NewRow();

            foreach (PropertyInfo pi in oProps)
            {
                dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                (rec, null);
            }

            dtReturn.Rows.Add(dr);
        }
        return dtReturn;
    }

Open in new window

0
asp_net2Author Commented:
I plan on using LINQ to SQL Fernando, however i'm more custom to using ADO.NET. The reason why I need to learn LINQ to SQL is because I was involved in a project and they are using LINQ to SQL.
0
asp_net2Author Commented:
Hi Fernando,

I also plan on binding the data returned from LINQ to SQL using Label Controls and or TextBox. Most of what I'm going to be using is just displaying data to Label Controls and GridView's sparingly.
0
Fernando SotoRetiredCommented:
Hi asp_net2;

In that case to use Linw to SQL the first step is to create the data context that will be used to communicate with the MS SQL Server. The easiest way to do that is to use the OR/M design model as outlined in the following tutorial.

LINQ to SQL (Part 2 - Defining our Data Model Classes)
http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx

Once you have the model in place you can query the database through the model without any need to use the old ADO .Net techniques shown in your post ID: 32354668 and the results of that query can be used to update DataGridViews, TextBox and more.

You also may want to read his first article.

Using LINQ to SQL (Part 1)
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

If you have any question please ask.

Fernando
0
abhinayp86Commented:
Dude.. Chk my post.. It's exactly wat u want
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.