C＃从列表中创建数据表 [英] C# create datatable from a list

问题描述

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.

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;
            }
        }

推荐答案

首先： 你应该处理数据，而不是字符串！

这意味着什么？

注意：学生数据应存储在列表< Student> 中，而不是 List< string> ！你必须将它传递给你的方法来批量复制数据。



其次： ListToDataTable class应该是静态的，它的方法也是 ToDataTable ！

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
    }
}

第三：如果你想使用异步方法，你会需要创建异步方法，它接受转换成数据表的学生列表。更多信息： c＃ - 批量复制的异步 - 堆栈溢出 [ ^ ]

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[^]

这篇关于C＃从列表中创建数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！