Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am working on re-writing my code to use SqlBulkCopy Class to test the insert record performance. I am running into errors converting List to a DataTable.


C#
public string toTbl(IList < string > records) {
    const string connectionString = @ "Data Source=sqlserver;Initial Catalog=dbname;User Id=user;Password=password;";    
    try {
        var studentData = from record in records
        let srec = record.Split(',')
        select new Student {
            ID = srec[0],
            Student = srec[1],
            Grade = srec[2]
        };

        foreach(var i in studentData) {
            using(SqlConnection sqlConnection = new SqlConnection(connectionString)) {
                sqlConnection.Open();    
                using(SqlCommand cmd = new SqlCommand("INSERT INTO [Student] ([ID], [Student], [Grade]) VALUES (@ID, @Student, @Grade)", sqlConnection)) {
                    cmd.Parameters.AddWithValue("@ID", i.ID);
                    cmd.Parameters.AddWithValue("@Student", i.Student);
                    cmd.Parameters.AddWithValue("@Grade", i.Grade);    
                    cmd.ExecuteNonQuery();
                }
                sqlConnection.Close();
            }
        }
    } catch (Exception ex) {
        message = ex.Message;
    }
}


What I have tried:

I am writing this below code and run into errors. Could you please guide me.
Thanks

private string toTbl(IList < string > records)
        {
            const string connectionString = @ "Data Source=sqlserver;Initial Catalog=dbname;User Id=user;Password=password;"; 
            try
            {               
                ListtoDataTable lsttodt = new ListtoDataTable();
                DataTable dt = lsttodt.ToDataTable(records);

                using (var conn = new SqlConnection(connectionString))
                {
                    await conn.OpenAsync();
                    using (var bulkCopy = new SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = "Student";

                        try
                        {
                            // Write from the source to the destination.
                            await bulkCopy.WriteToServerAsync(dt);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return (null);
        }

        public class ListtoDataTable
        {
            public DataTable ToDataTable<T>(List<T> items)
            {
                DataTable dataTable = new DataTable(typeof(T).Name);
                //Get all the properties by using reflection   
                PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                foreach (PropertyInfo prop in Props)
                {
                    //Setting column names as Property names  
                    dataTable.Columns.Add(prop.Name);
                }
                foreach (T item in items)
                {
                    var values = new object[Props.Length];
                    for (int i = 0; i < Props.Length; i++)
                    {

                        values[i] = Props[i].GetValue(item, null);
                    }
                    dataTable.Rows.Add(values);
                }

                return dataTable;
            }
        }
Posted
Updated 14-Feb-18 9:07am
v2
Comments
j snooze 13-Feb-18 17:08pm    
you may want to post the error message, and the line it errors on when you debug it.
Member 12586110 13-Feb-18 17:16pm    
Hi, I get two errors one at DataTable dt = lsttodt.ToDataTable(records); and others at await (await conn.OpenAsync();, await bulkCopy.WriteToServerAsync(dt);)

Error: CS0411, The type arguments for method 'ListtoDataTable.ToDataTable<t>(List<t>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.
Error: CS4032, The 'await' operator can only be used within an async method. Consider marking this method with the 'async' modifier and changing its return type to 'Task<string>'.

Thanks
Maciej Los 14-Feb-18 2:14am    
As to me ListToDataTable class have to be static and its method ToDataTable too. On the other hand, your data is stored in List<string>, which means you can simply rewrite it into DataTable:
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Record", typeof(string)));
foreach(string item in records)
{
dt.Rows.Add(new object[]{item});
}

1 solution

First of all: You should work on data, not on string!
What it means?
Note: student data should be stored in a List<Student> instead of List<string>! You have to pass it into your method to bulk copy data.

Second of all: ListToDataTable class should be static and its method ToDataTable too!

public static class ListToDataTable
{
    public static DataTable ToDataTable<T>(List<T> items)
    {
     //body of method
    }
}


Third of all: If you would like to use async method, you'll need to create async method, which accept list of students converted into datatable. More at: c# - Async for Bulk copy - Stack Overflow[^]
 
Share this answer
 
Comments
Member 12586110 14-Feb-18 15:07pm    
Hi, I wrote the below per your inputs but I have 2 issues.

1) How to split my string as the data is comma-separated the code below writes all the records into the ID field.
2) Could you please guide me on how to make the table inserts asynchronous, I will be calling the toTbl() from a different method.


	public string toTbl(IList < string > records)
        {
            const string connectionString = @"Data Source=dbsvr;Initial Catalog=dbname;User Id=usr;Password=pwd;";

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID", typeof(string)));
            dt.Columns.Add(new DataColumn("Student", typeof(string)));
            dt.Columns.Add(new DataColumn("Grade", typeof(string)));
            foreach (string item in records.Skip(1))
            {
                dt.Rows.Add(new object[] { item });
            }

            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(conn))
                {
                    s.DestinationTableName = "Student";
                    s.BatchSize = dt.Rows.Count;
                    s.BulkCopyTimeout = 0;
                    s.ColumnMappings.Add("ID", "ID");
                    s.ColumnMappings.Add("Student", "Student");
                    s.ColumnMappings.Add("Grade", "Grade");
                    s.WriteToServer(dt);
                    s.Close();
                }
                conn.Close();
            }
        }
			
    


Thank you
Maciej Los 14-Feb-18 15:34pm    
Seems, you know how to split string into Student data:
var studentData = from record in records
let srec = record.Split(',')
select new Student {
ID = srec[0],
Student = srec[1],
Grade = srec[2]

As to the question no. 2: please, read my answer again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900