你的位置:首页 > ASP.net教程

[ASP.net教程]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;    }