Split Function in Sql Server to break Comma-Separated Strings into Table

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.


CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))

as

begin
declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1 or @String is null return

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break
end
return

end

split function can be Used as
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
would return

Above Split function can be used to pass parameter to IN clause in sql server.

Convert DataTable to List and List to DataTable in Asp.net C# Use this Class

internal static class MapUtil


{

public static List ToList(this DataTable table)

{

List list = new List();
T item;

Type listItemType = typeof(T);
for (int i = 0; i < table.Rows.Count; i++)

{

item = (T)Activator.CreateInstance(listItemType);

MapRow(item, table, listItemType, i);

list.Add(item);

}

return list;

}



private static void MapRow(object vOb, DataTable table, Type type, int row)

{

for (int col = 0; col < table.Columns.Count; col++)

{

var columnName = table.Columns[col].ColumnName;



var prop = type.GetProperty(columnName);

object data ;

if (prop == null)

{

data = table.Rows[row][col].ToString();

}

else

{ data = GetData(prop, table.Rows[row][col]); }

prop.SetValue(vOb, data, null);

}

}



private static object GetData(PropertyInfo prop, object value)

{



if (prop.PropertyType.Name.Equals("Int32"))

return Convert.ToInt32(value);



if (prop.PropertyType.Name.Equals("Double"))

return Convert.ToDouble(value);



if (prop.PropertyType.Name.Equals("DateTime"))

return Convert.ToDateTime(value);



if (prop.PropertyType.Name.Equals("Guid"))

return new Guid(value.ToString());





return Convert.ToString(value).Trim();

}



public static DataTable ToDataTable(this IList list)

{



DataTable dt = null;

Type listType = list.GetType();



if (listType.IsGenericType)

{

Type elementType = listType.GetGenericArguments()[0];

dt = new DataTable(elementType.Name + "List");

MemberInfo[] miArray = elementType.GetMembers(BindingFlags.Public
BindingFlags.Instance);

foreach (MemberInfo mi in miArray)

{

if (mi.MemberType == MemberTypes.Property)

{

PropertyInfo pi = mi as PropertyInfo;

dt.Columns.Add(pi.Name, pi.PropertyType);

}

else if (mi.MemberType == MemberTypes.Field)

{

FieldInfo fi = mi as FieldInfo;

dt.Columns.Add(fi.Name, fi.FieldType);

}

}

IList il = list;

foreach (object record in il)

{

int i = 0;

object[] fieldValues = new object[dt.Columns.Count];

foreach (DataColumn c in dt.Columns)

{

MemberInfo mi = elementType.GetMember(c.ColumnName)[0];

if (mi.MemberType == MemberTypes.Property)

{

PropertyInfo pi = mi as PropertyInfo;

fieldValues[i] = pi.GetValue(record, null);

}

else if (mi.MemberType == MemberTypes.Field)

{

FieldInfo fi = mi as FieldInfo;

fieldValues[i] = fi.GetValue(record);

}

i++;

}

dt.Rows.Add(fieldValues);

}

}

return dt;

}

}
 
write this class in your app_code
 
after implimenting this class you will get youDataTable.ToList<> property and to yourList<>.ToDataTable() property they will return respective objects
 
Happy coding Guys