/// <summary> /// 批量插入用户配置 /// </summary> /// <param name="connectionString">目标连接字符</param> /// <param name="TableName">目标表</param> /// <param name="dt">源数据</param> public void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt) { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlbulkcopy.DestinationTableName = TableName; for (int i = 0; i < dt.Columns.Count; i++) { sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } sqlbulkcopy.WriteToServer(dt); } catch (System.Exception ex) { throw ex; } } } }
View Code
#region 将List转换到DataTable DataTable dt = new DataTable(); dt.Columns.Add("RecordNum", typeof(string)); dt.Columns.Add("UserName", typeof(string)); dt.Columns.Add("ThemeID", typeof(int)); dt.Columns.Add("ThemeName", typeof(string)); dt.Columns.Add("TempID", typeof(int)); dt.Columns.Add("TempRecordNumberID", typeof(string)); dt.Columns.Add("TempUrl", typeof(string)); dt.Columns.Add("ConfigDetail", typeof(string)); dt.Columns.Add("Remark1", typeof(string)); dt.Columns.Add("IsDelete", typeof(bool)); dt.Columns.Add("CreateBy", typeof(string)); dt.Columns.Add("CreateTime", typeof(DateTime)); foreach (var p in ucList) { DataRow row = dt.NewRow(); row["RecordNum"] = p.RecordNum; row["UserName"] = p.UserName; row["ThemeID"] = p.ThemeID; row["ThemeName"] = p.ThemeName; row["TempID"] = p.TempID; row["TempRecordNumberID"] = p.TempRecordNumberID; row["TempUrl"] = p.TempUrl; row["ConfigDetail"] = p.ConfigDetail; row["Remark1"] = p.Remark1; row["IsDelete"] = p.IsDelete; row["CreateBy"] = p.CreateBy; row["CreateTime"] = p.CreateTime; dt.Rows.Add(row); } #endregion //批量保存用户配置 string myConn = ConfigurationManager.AppSettings["PortalConnString"].ToString(); T_COM_UserConfigBll.Instance.SqlBulkCopyByDatatable(myConn, "T_COM_UserConfig", dt);
View Code
<add key="PortalConnString" value="initial catalog=HXPortal;data source=xx.xxx.xx.xx;password=xxxx;User id=xxx;" />
View Code
时间: 2024-09-21 03:25:30