How to fill a DataTable with the result of a LINQ?

I have a method that calls a stored procedure. It uses the employee number as a parameter to retrieve the data of a particular employee and then fills the data table with the result.

protected DataTable CreateDT(string empNo)
        {
            DataTable dataTable = null;
            try
            {
                SqlCommand cmd = new SqlCommand("FIND_EMPLOYEE_BY_EMPNO", pl.ConnOpen());
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@EMPNO", (object)empNo));
                SqlDataAdapter da = new SqlDataAdapter(pl.cmd);
                dataTable = new DataTable("dt");
                da.Fill(dt);
            }
            catch (Exception x)
            {
                MessageBox.Show(x.GetBaseException().ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                pl.MySQLConn.Close();
            }
            return dt;
        }

What I'm trying to accomplish is convert this code to LINQ, but I don't know how get the result and fill it to my data table. See below:

alt text http://a.imageshack.us/img706/9017/testki.jpg

protected DataTable CreateDT(string empNo)
        {
            DataTable dataTable = null;
            try
            {
                DataClasses1D开发者_如何学PythonataContext dataClass1 = new DataClasses1DataContext();
                // I tried to cast it to DataTable, but it doesn't work...
                dataTable = (DataTable)dataClass1.findEmployeeByID(empNo);
            }
            catch (Exception x)
            {
                MessageBox.Show(x.GetBaseException().ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                pl.MySQLConn.Close();
            }
            return dt;
        }

Please guide me how to properly do this... Thanks in advance..

---

Why exactly do you need to fill a data table? Most bindable controls that use a data table accept any ienumerable-based object, which the collection result of standard LINQ produces.

You're having to refactor the code anyways to use the LINQ objects, so you might as well go ahead and change it all the way. You'll be happier in the long run as LINQ is much easier to use than ado.net.

But to answer the question, you would have to iterate through the list and insert each element into the datatable. Something like thus (code sample found at this article):

public 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;
}

---

findEmployeeByID will most likely return IEnumerable<Employee>. Considering that you are switching to using LINQ, you should actually take advantage of strongly typed data and use it across you application. So, change the return type of CreateDT function and adjust the rest of the code accordingly(I assume that the stored procedure returns at most one result):

protected Employee CreateDT(string empNo)
    {

        try
        {
            DataClasses1DataContext dataClass1 = new DataClasses1DataContext();
            // I tried to cast it to DataTable, but it doesn't work...
            return dataClass1.findEmployeeByID(empNo).FirstOrDefault();
        }
        catch (Exception x)
        {
            MessageBox.Show(x.GetBaseException().ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
           //might need to dispose the context here
        }
        return null;
    }

Usage:

 var employee = CreateDT(1234);
 //You can now access members of employee in a typesafe manner
 string name = employee.Name;

EDIT Updated code - this is how you can rewrite the old DataTable code:

 protected void RetrieveEmployee(string empNo) { 
     Employee emp = CreateDT(empNo);// <---- Here 
     txtEmployeeNo.Text = emp.EmployeeNo;
     txtLastName.Text = emp.LastName;
     //....
 }

Note the absence of array indices and late bound column specifiers - ie dt[0]["EmployeeNo"] became emp.EmployeeNo - must safer, faster and easier to read.

---

Why does it need to return a DataTable? One of the big advantages of LINQ is that you can work with strong-typed collections, rather than string-keyed DataTables.

protected IEnumerable<Employee> GetEmployee(string empNo)
{
    try
    {
        DataClasses1DataContext dataClass1 = new DataClasses1DataContext();
        // I tried to cast it to DataTable, but it doesn't work...
        return dataClass1.findEmployeeByID(empNo);
    }
    catch (Exception x)
    {
        MessageBox.Show(x.GetBaseException().ToString(), "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
        return null;
    }
    finally
    {
        pl.MySQLConn.Close();
    }
}

Some other points:

1. I would remove the exception handling from this method, do it a higher-level place. If you're using WinForms (which you seem to be) I would just let the exception bubble all the way to the default WinForms exception handler (add a handler to the Application.ThreadException)
2. I would also make the DataContext class a member variable, rather than creating and destroying one each call. The advantage here is that if you do multiple updates on the same instance, you can call one Save() and apply them all - which results in a single call to the server, rather than one call to the server for each one.