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; }
新闻热点
疑难解答