Convert Linq to DataTable

-
         var query = (from employees in _dsSecurity.Employees.AsEnumerable()
                                 join empSites in _dtEmployeesSite
                                     on employees.EmployeeId equals empSites.EmployeeId
                                 join empFilter in _dtScheduleProviders.AsEnumerable()
                                     on employees.EmployeeId equals (Guid)empFilter["EmployeeId"]
                                 where empSites.Field("SiteId") == (siteId) && empSites.Field("TheWeekDaysId") == dayNumber
                                     && empSites.StartTime.ToLocalTime().TimeOfDay <= StartTime
                                     && empSites.EndTime.ToLocalTime().TimeOfDay >= EndTime
                                 select new
                                 {
                                     employees.EmployeeId,
                                     EmployeeCode = employees.Field("EmployeeCode"),
                                     EmployeeTypeId = Guid.Empty,
                                     FirstName = employees.Field("FirstName"),
                                     LastName = employees.Field("LastName"),
                                     MiddleName = employees.Field("MiddleName"),
                                     Title = employees.Field("Title"),
                                     Prefix = employees.Field("Prefix"),
                                     Suffix = employees.Field("Suffix"),
                                     EmployeeImage = employees["EmployeeImage"],
                                     Credentials = employees.Field("Credentials"),
                                     Salutation = employees.Field("Salutation"),
                                     IsInternal = employees.Field("IsInternal"),
                                     IsClinician = employees.Field("IsClinician"),
                                     employees.SchedulerTypeId,
                                     HomePhone = employees.Field("HomePhone"),
                                     CellPhone = employees.Field("CellPhone"),
                                     IsActive = employees.Field("IsActive"),
                                     EmployeeName =
                                     employees.Field("LastName") + ", " + employees.Field("FirstName"),
                                     employees.CreatedOn,
                                     employees.CreatedBy,
                                     employees.LastModifiedOn,
                                     LastModifiedBy = employees.Field("LastModifiedBy"),
                                     IsShowOnSchedule = employees.Field("IsShowOnSchedule")
                                 }).Distinct();

                    DataTable dtEmp = _dsSecurity.Employees.Clone();
                    dtEmp = ToADOTable(query);


---------Convert Linq To Data Table----------------
 private DataTable ToADOTable(IEnumerable varlist)
        {
            DataTable dtReturn = new DataTable();
            try
            {
                // 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 = (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);
                }
            }
            catch (ExceptionBase exceptionBase)
            {
                Common.Generic.ShowSendToSupportMessage(exceptionBase.Message, exceptionBase.ExceptionDetails);
            }
            catch (System.Exception ex)
            {
                UILayerException uiLayerException = new UILayerException(ex, ExceptionSeverity.Error);
                Common.Generic.ShowSendToSupportMessage(uiLayerException.Message, uiLayerException);
            }
            return dtReturn;
        }

Comments

Post a Comment

Popular posts from this blog

Querying a Data Table Using Select Method and Lambda Expressions in Dot Net

-
Image
.Net Questions and Ans.. Q. 1 Querying a Data Table Using Select Method and Lambda Expressions in Dot Net Suppose we have a DataTable object having four fields: SSN, NAME, ADDRESS and AGE. We could create a data table and add columns in the following way: DataTable  dt =  new   DataTable (); dt.Columns.Add( "SSN" ,  typeof ( string )); dt.Columns.Add( "NAME" ,  typeof ( string )); dt.Columns.Add( "ADDR" ,  typeof ( string )); dt.Columns.Add( "AGE" ,  typeof ( int )); // Showing how to set Primary Key(s) in a Data table (Although it's not compulsory to have one) DataColumn [] keys =  new   DataColumn [1]; keys[0] = dt.Columns[0]; dt.PrimaryKey = keys; Now we store some data in our data table "dt" to show how we can perform several queries on the DataTable object like filtering some data, finding a person's record...
Read more

StringToTable

-
ALTER FUNCTION [dbo].[udf_StringToTable] (       @list NVARCHAR(MAX) ,       @delimiter NCHAR(1) = ',' --Defaults to CSV ) RETURNS @tableList TABLE ( value nvarchar(MAX) ) AS BEGIN     DECLARE @value NVARCHAR(MAX)     DECLARE @position INT     SET @list = LTRIM(RTRIM(@list))+ ','     SET @position = CHARINDEX(@delimiter, @list, 1)     IF REPLACE(@list, @delimiter, '') <> ''     BEGIN         WHILE @position > 0         BEGIN             SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))             IF @value <> ''             BEGIN                 INSERT INTO @tableList (value)   ...
Read more

GetPhoneSSNFormat

-
ALTER FUNCTION [dbo].[udf_GetPhoneSSNFormat] (     -- Add the parameters for the function here     @TypeId        INT,     @DataValue    NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN     DECLARE @FormattedValue    NVARCHAR(MAX)     SET @FormattedValue = ''     -- Will Remove all white spaces, brackets, hyphens     DECLARE @OriginalValue    NVARCHAR(MAX)     SET @OriginalValue = ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(@DataValue,'(',''),')',''),'-',''),' ',''),'')         -- Type Id : 1 For Phone #     -- Type Id : 2 For SSN #     IF @TypeId = 1         BEGIN             SELECT @FormattedValue =              ...
Read more