事务，表截断和参数化SQL查询

我必须清除表中的所有内容并重新加载新数据。有超过1500行，因此出于性能原因，有必要使用TRUNCATE TABLE .

我的方法流程是:

1. 开始交易
截断表
2. 获取新数据
3. 使用SQL参数化值插入所有行
4. 获取数据&&插入成功?Commit Transaction: Rollback

我的问题是:我可以使用SqlConnection成功执行上述操作吗?因为我正在截断表并执行> 1500个insert，所以我有点犹豫是否要运行代码来找出答案。我试着在网上看了看，虽然我发现了类似的问题，但我觉得它们不适合我的情况。

下面是我开发的执行所需操作的方法:

        public static void InsertCourseLookup(List<Course> courses)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    SqlTransaction transaction = connection.BeginTransaction("CourseLookupTransaction");
                    command.Connection = connection;
                    command.Transaction = transaction;
                    command.CommandTimeout = 300;
                    try
                    {
                        command.CommandText = "TRUNCATE TABLE course_info";
                        command.ExecuteNonQuery();
                        foreach (Course course in courses)
                        {
                            if (course == null)
                            {
                                throw new ArgumentException("course cannot be null");
                            }
                            ContentData courseData = GlobalHelper.GetContentData(course.ContentId);
                            if (courseData == null)
                            {
                                throw new Exception(string.Format("Missng ContentData for course '{0}'", course.Title));
                            }
                            // checks if row is already in table, if it is check.ContentId will be greater than 0
                            CourseLookup check = CourseHelper.GetCourseLookup(course.DatabaseId);
                            string sql = string.Empty;
                            if (check.ContentID > 0)
                            {
                                sql = @"
                                    UPDATE  course_info
                                    SET     content_id = @content_id,
                                            name = @name,
                                            code = @code,
                                            description = @description,
                                            url = @url,
                                            meta_keywords = @meta_keywords,
                                            meta_description = @meta_description
                                    WHERE   course_id = @course_id";
                            }
                            else
                            {
                                sql = @"
                                    INSERT INTO course_info(course_id, content_id, name, code, description, url, meta_keywords, meta_description)
                                    VALUES(@course_id, @content_id, @name, @code, @description, @url, @meta_keywords, @meta_description)";
                            }
                            string metaKeywords = string.Empty;
                            string metaDescription = string.Empty;
                            if (courseData.MetaData != null)
                            {
                                for (int i = 0; i < courseData.MetaData.Length; i++)
                                {
                                    if (courseData.MetaData[i].Id == ConfigData.Metadata.MetaKeywordsID)
                                    {
                                        metaKeywords = DataHelper.TruncateString(courseData.MetaData[i].Text, 500);
                                    }
                                    else if (courseData.MetaData[i].Id == ConfigData.Metadata.MetaDescriptionID)
                                    {
                                        metaDescription = DataHelper.TruncateString(courseData.MetaData[i].Text, 500);
                                    }
                                }
                            }
                            command.CommandText = sql;
                            command.Parameters.AddRange(
                                new SqlParameter[] {
                                    new SqlParameter("@course_id", course.DatabaseId),
                                    new SqlParameter("@content_id", course.ContentId),
                                    new SqlParameter("@name", course.Title),
                                    new SqlParameter("@code", course.Code),
                                    new SqlParameter("@description", course.Description),
                                    new SqlParameter("@url", courseData.Quicklink),
                                    new SqlParameter("@meta_keywords", metaKeywords),
                                    new SqlParameter("@meta_description", metaDescription)
                                }
                            );
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Log.Error(string.Format("Unable to reload course lookup table: {0}", ex.Message));
                    }
                }
            }
        }

catch (Exception ex)
{
    transaction.Rollback();
    Log.Error(string.Format("Unable to reload course lookup table: {0}", ex.Message));
}