bulkcopy实现批量插入与更新

public static void UpdateData<T>(List<T> list, string TabelName)
        {
            DataTable dt = new DataTable("MyTable");
            clsBulkOperation blk = new clsBulkOperation();
            dt = ConvertToDataTable(list);
            ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["tcxxconnstring"].ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        command.CommandText = "CREATE TABLE #TmpTable(...)";
                        command.ExecuteNonQuery();
                        using (SqlBulkCopy  = new SqlBulkCopy(conn))
                        {
                            bulkcopy.BulkCopyTimeout = 660;
                            bulkcopy.DestinationTableName = TabelName;
                            bulkcopy.WriteToServer(dt);
                            bulkcopy.Close();
                        }
                        // Updating destination table, and dropping temp table
                        command.CommandTimeout = 300;
                        command.CommandText = "UPDATE T SET ... FROM " + TabelName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        // Handle exception properly
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
  public static void InsertData<T>(List<T> list,string TableName)
        {
                DataTable dt = new DataTable("MyTable");
                clsBulkOperation blk = new clsBulkOperation();
                dt = ConvertToDataTable(list);
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
                {
                    bulkcopy.BulkCopyTimeout = 660;
                    bulkcopy.DestinationTableName = TableName;
                    bulkcopy.WriteToServer(dt);
                }
        }    
public static DataTable ConvertToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }

 

转载于:https://www.cnblogs.com/airven/p/5015295.html