How to Convert Dataset or DataTable to List collection

Some time we need to convert Dataset or DataTable values to List items for easy filter or any search in the data. List collection have more feature compare then Dataset. Like we can select top 3 users or role is admin or etc. So we can write own LINQ query in the list and get relevant information without using any loop. This will help to improve you application performance as well.

The given below example I have one userdetails class have properties of user information and another one is data set to list conversion method. If you pass dataset to the function then the function will return collection of userdetails list.

Sample Code:


Convert DataSet to List Function:
        public IList ConvertToList(DataSet ds)
        {
            List lst = new List();
            try
            {
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var myData = ds.Tables[0].AsEnumerable().Select(r => new UserDetails
                    {
                        UserID = r.Field("UserID"),
                        UserName = r.Field("Username"),
                        EmailID = r.Field("EmailID"),
                        Name = r.Field("DisplayName"),
                        RoleID = r.Field("RoleID").ToString(),
                        Sex = r.Field("Sex"),
                        DOB = r.Field("DOB"),
                        Mobile = r.Field("Mobile"),
                        IsActive = r.Field("IsActive")
                    });
                    lst = myData.ToList();
                }
            }
            catch (Exception ex)
            {
                Comman.ErrorLog(ex.Message);
            }
            return lst;
        }







    
UserDetails.cs    
//User detail Class information
        public class UserDetails
        {
            public string UserID { get; set; }
            public string UserName { get; set; }
            public string EmailID { get; set; }
            public string Name { get; set; }
            public string RoleID { get; set; }
            public string Sex { get; set; }
            public DateTime? DOB { get; set; }
            public string Mobile { get; set; }
            public bool IsActive { get; set; }
        }

How to Generate SQL Query in excel sheet two column

Today I have faced one problem in excel sheet. I have a situation need to update user email id details from excel to my client database. The users email list is more than 1000 users.

For this update to excel we can do line by line update to the database or import into database and write the join query for update. But the simple way is we can generate INSERT/UPDATE SQL query and execute in to the database. For my situation I have already user data in the database. So I want to update the emailID based on the employeeID contition.

For that the below Excel comment will help to generate the SQL UPDATE query with simple steps and I can update with in 5mintus.

=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Excel Sheet:

Emp ID	Name	EMail id	Project
1110	Karthik	Karthik@gmail.com	Sprots Domain
1111	Kumar	Kumar@gmail.com	Sprots Domain
1112	Vijay	Vijay@gmail.com	Sprots Domain
1113	Yuvan	Yuvan@gmail.com	Education Domain

Excel Sheet Query Generator:
=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Output SQL Query :

SQL Query

UPDATE tb_employee set EMPL_EMAILID= ('Karthik@gmail.com') WHERE EMPL_ID= (1110);

UPDATE tb_employee set EMPL_EMAILID= ('Kumar@gmail.com') WHERE EMPL_ID= (1111);

UPDATE tb_employee set EMPL_EMAILID= ('Vijay@gmail.com') WHERE EMPL_ID= (1112);

UPDATE tb_employee set EMPL_EMAILID= ('Yuvan@gmail.com') WHERE EMPL_ID= (1113);

Finlay copy all the sql query from excel sheet and execute into the database. Its take 5 mints to complete your task.

Try this and let me know your feedback.