首页 > 学院 > 开发设计 > 正文

bulkcopy实现批量插入与更新

2019-11-14 13:42:44
字体:
来源:转载
供稿:网友
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;        }

 


发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表