Conversion Between DataTable and List in C#

Conversion Between DataTable and List in C#

Introduction

Sometimes in projects we may face a situation where we have to convert:

•     A IList of user defined type to a DataTable or

•     A DataTable to a List of user defined type

Now rather than making custom conversion mechanism of each different user defined type, let’s make generic converters to save code and time.

List to DataTable

Here is the extension method to convert a List to a DataTable:

/// 

/// Convert Genric List  to DataTable
/// 
public static DataTable ToDataTable(this IList listObject)
{
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
    DataTable myTable = new DataTable();
    foreach (PropertyDescriptor prop in properties)
    {
        myTable.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[properties.Count];
    foreach (T item in listObject)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = properties[i].GetValue(item);
        }
        myTable.Rows.Add(values);
    }
    return myTable;
}

DataTable to List<TSource>

/// 

/// Convert DataTable to Genric List
/// 
public static List DataTableMapToList(DataTable dtSource)
{
    string propName = string.Empty;
    List entityList = new List();
    foreach (DataRow dr in dtSource.Rows)
    {
        // Create Instance of the Type T
        T entity = Activator.CreateInstance();
        // Get all properties of the Type T
        System.Reflection.PropertyInfo[] entityProperties = typeof(T).GetProperties();
        // Loop through the properties defined in the
        // entityList entity object and mapped the value
        foreach (System.Reflection.PropertyInfo item in entityProperties)
        {
            propName = string.Empty;
            if (propName.Equals(string.Empty))
                propName = item.Name;
            if (dtSource.Columns.Contains(propName))
            {
                // Assign value to the property
                item.SetValue
                (
                    entity,
                    dr[propName].GetType().Name.Equals(typeof(DBNull).Name) ? null : dr[propName],
                    null
                );
            }
        }
        entityList.Add(entity);
    }
    return entityList;
}

SQL Server– Concatenate Rows using FOR XML PATH()

SQL Server– Concatenate Rows using FOR XML PATH()

This is probably one of the most frequently asked question – How to concatenate rows? And, the answer is to use XML PATH.

For example, if you have the following data:

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],

            SUB.Name AS [Sub Category]

FROM        Production.ProductCategory CAT

INNER JOIN  Production.ProductSubcategory SUB

            ON CAT.ProductCategoryID = SUB.ProductCategoryID

=====================================================================


The desired output here is to concatenate the subcategories in a single row as:

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

=============================================================================




USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],

            STUFF((    SELECT ',' + SUB.Name AS [text()]

                        – Add a comma (,) before each value

                        FROM Production.ProductSubcategory SUB

                        WHERE

                        SUB.ProductCategoryID = CAT.ProductCategoryID

                        FOR XML PATH('') – Select it as XML

                        ), 1, 1, '' )

                        – This is done to remove the first character (,)

                        – from the result

            AS [Sub Categories]

FROM  Production.ProductCategory CAT

Executing this query will generate the required concatenated values as depicted in above screen shot.

SQL Server : Execute Dynamic Query with out parameter : specify output parameters in sp_executesql stored procedure

The sp_executesql system stored procedure is used to execute a T-SQL statement which can be reused multiple times, or to execute a dynamically built T-SQL statement. It takes parameters as inputs in order to process the T-SQL statements or batches. It also allows output parameters to be specified so that any output generated from the T-SQL statements can be stored .

Two scenarios in which the output parameters will be useful with sp_executesql are:

• If sp_executesql generates output that will be useful, storing this output to an output parameter allows the calling batch to use the parameter for later queries.
• If sp_executesql is executing a stored procedure that is defined using output parameters, the output parameters for sp_executesql can be used to hold the outputs generated from the stored procedure.

The following two examples demonstrate the use of output parameters with sp_executesql.

Example 1

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
    

Example 2

CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go

Firebug - get absolute coordinates of Selected Element in firebug

I was searching is there any way to find the left top position of element in page fast.
without each time writing the jquery code  to fetch offset. and refesh page and debug.

i just wanted to know the position of element which i click in firebug.

and i found a great solution.

FindPostion Function :

var findpos = function findPos(obj) {
   var curleft = curtop = 0;
   if (obj.offsetParent) {
      do {
      curleft += obj.offsetLeft;
      curtop += obj.offsetTop;
      } while (obj = obj.offsetParent);
      return [curleft,curtop];
   }
}

(paste it in Firebug, and then you have a findpos function available in the console).
For majority of simplest cases, you will have offsetParent = <body> which has zero offsets (like in the screenshots) so you don't need to add anything to offsetLeft and offsetTop. However if relative positionings take place, then you must traverse the parents.

When you select an element in Firebug, then it's available as $0 in Firebug console.
So, after mixing the two things, you can issue the commands like in the screenshot:

JAVASCRIPT : Add st, nd, rd and th (ordinal) suffix to a number

I would like to dynamically generate a string of text based on a current day. 
So, for example, if it is day 1 then I would like my code to generate  =  "1st".

2 = "2nd"
3 = "3rd"

I Searched And Found Good function to get ordinal suffix for number

function ordinal_suffix_of(i) {

    var j = i % 10,

        k = i % 100;

    if (j == 1 && k != 11) {

        return i + "st";

    }

    if (j == 2 && k != 12) {

        return i + "nd";

    }

    if (j == 3 && k != 13) {

        return i + "rd";

    }

    return i + "th";

}