public void ToDataTableNullTable() { DataTable dt = null; Assert.ThrowsArgumentNullException("table", () => m_TestObjects.ToDataTable(dt)); }
public static DataSet ToDataSet <T>(this IList <T> list) { var ds = new DataSet(); ds.Tables.Add(list.ToDataTable()); return(ds); }
public void PutDataIntoDB(IList <T> data, string tableDestination, int timeout = 120) { if (Mapping is null) { throw new Exception("Mapping is null"); } if (SQLConex is null) { throw new Exception("SQLConex is null"); } DataTable dt = data.ToDataTable <T>(); if (SQLConex.State != ConnectionState.Open) { SQLConex.Open(); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLConex)) { bulkCopy.DestinationTableName = tableDestination; bulkCopy.BulkCopyTimeout = timeout; for (int i = 0; i < Mapping.Count; i++) { bulkCopy.ColumnMappings.Add(Mapping[i].Origen, Mapping[i].Destino); } bulkCopy.WriteToServer(dt); } }
/// <summary> /// 将嵌套的IList<T>转化为主从的DataTable后,追加到DataSet中,并追加主从关联关系 /// </summary> /// <typeparam name="Parent">父实体类型</typeparam> /// <typeparam name="Children">子实体类型</typeparam> /// <param name="ds">DataSet</param> /// <param name="parentlist">父实体列表</param> /// <param name="childlist">子实体列表</param> /// <param name="parentColumnName">获取父列名的表达式树</param> /// <param name="childColumnName">获取子列名的表达式树</param> /// <param name="parentTableName">可选,父DataTable的名称</param> /// <param name="childTableName">可选,子DataTable的名称</param> /// <returns></returns> public static DataSet AddNestedList <Parent, Children>(this DataSet ds, IList <Parent> parentlist, IList <Children> childlist, Expression <Func <Parent, object> > parentColumnName, Expression <Func <Children, object> > childColumnName, string parentTableName = null, string childTableName = null) where Parent : class where Children : class { if (ds == null) { throw new ArgumentNullException("ds", "DataSet不能为Null"); } parentTableName = parentTableName ?? typeof(Parent).Name; childTableName = childTableName ?? typeof(Children).Name; if (!ds.Tables.Contains(parentTableName)) { var table = parentlist.ToDataTable(parentTableName); ds.Tables.Add(table); } if (!ds.Tables.Contains(childTableName)) { var table = childlist.ToDataTable(childTableName); ds.Tables.Add(table); } ds.AddRelation(GetColumnName(parentColumnName), GetColumnName(childColumnName), parentTableName, childTableName); return(ds); }
public static void BulkUpdate <T>(this SqlConnection connection, IList <T> data, string tableName, List <string> idColumns, params string[] columnNames) { var temptableName = "#" + Guid.NewGuid(); var propertyNamesIncludeId = columnNames.Select(RemoveOperator).ToList(); propertyNamesIncludeId.AddRange(idColumns); var dataTable = data.ToDataTable(propertyNamesIncludeId); var sqlCreateTemptable = dataTable.GenerateTableDefinition(temptableName, idColumns); var updateStatementBuilder = new StringBuilder(); updateStatementBuilder.AppendLine("update a set"); updateStatementBuilder.AppendLine(string.Join("," + Environment.NewLine, columnNames.Select(x => CreateSetStatement(x, "a", "b")))); updateStatementBuilder.AppendLine($"from {tableName } a join [{ temptableName}] b on " + string.Join(" and ", idColumns.Select(x => $"a.[{x}] = b.[{x}]"))); connection.Open(); using (var createTemptableCommand = connection.CreateCommand()) { createTemptableCommand.CommandText = sqlCreateTemptable; createTemptableCommand.ExecuteNonQuery(); } dataTable.SqlBulkCopy(temptableName, connection); using (var updateCommand = connection.CreateCommand()) { updateCommand.CommandText = updateStatementBuilder.ToString(); var affectedRows = updateCommand.ExecuteNonQuery(); } connection.Close(); }
private void SqlBulkInsert <T, TKey>(IList <T> entities, string destinationTableName = null) where T : BaseModel <TKey> { using (var dt = entities.ToDataTable()) { dt.TableName = destinationTableName; using (var conn = Database.GetDbConnection() as SqlConnection ?? new SqlConnection(_option.ConnectionString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var tran = conn.BeginTransaction()) { try { var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { BatchSize = entities.Count, DestinationTableName = dt.TableName, }; GenerateColumnMappings <T, TKey>(bulk.ColumnMappings); bulk.WriteToServerAsync(dt); tran.Commit(); } catch (Exception) { tran.Rollback(); throw; } } conn.Close(); } } }
/// <summary> /// 导出文件 /// </summary> /// <param name="filePath">文件路径</param> /// <param name="companyId">公司ID</param> /// <param name="rlist">要导出的列表</param> public void ExportExcel(string filePath, IList<RouteInformationItem> rlist) { DataTable rdt = rlist.ToDataTable(); MemoryStream ms = NPOIHelper.RenderToExcel(rdt); NPOIHelper.SaveToFile(ms, filePath); }
public static void BulkDelete <T>(this SqlConnection connection, IList <T> data, string tableName, List <string> idColumns) { var temptableName = "#" + Guid.NewGuid(); var dataTable = data.ToDataTable(idColumns); var sqlCreateTemptable = dataTable.GenerateTableDefinition(temptableName, idColumns); var deleteStatement = $"delete a from {tableName} a join [{temptableName}] b on " + string.Join(" and ", idColumns.Select(x => $"a.[{x}] = b.[{x}]")); connection.Open(); using (var createTemptableCommand = connection.CreateCommand()) { createTemptableCommand.CommandText = sqlCreateTemptable; createTemptableCommand.ExecuteNonQuery(); } dataTable.SqlBulkCopy(temptableName, connection); using (var deleteCommand = connection.CreateCommand()) { deleteCommand.CommandText = deleteStatement.ToString(); var affectedRows = deleteCommand.ExecuteNonQuery(); } connection.Close(); }
/// <summary> /// /// </summary> /// <param name="dataList"></param> /// <see cref="http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code"/> public void UpdateUserRight(IList <Right_Data> datalist) { const string SQL = @" MERGE [Rights] AS T USING @Table AS S ON (T.r_id = S.r_id) WHEN NOT MATCHED BY TARGET THEN INSERT([r_us_id], [r_ho_iso_id], [r_ho_id], [r_br_id], [r_active], [r_granted_by], [r_granthed_at]) VALUES(S.r_us_id, S.r_ho_iso_id, S.r_ho_id, S.r_br_id, S.r_active, S.r_granted_by, S.r_granthed_at) WHEN MATCHED THEN UPDATE SET r_us_id = S.r_us_id, r_br_ho_id = S.r_br_ho_id, r_br_ho = S.r_ho_id, r_br_id = S.r_br_id, r_active = S.r_active, r_granted_by = S.r_granted_by, r_granthed_at = S.r_granthed_at WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, inserted.*, deleted.*;"; using (var conn = new SqlConnection(ImagesConnectionString)) { conn.Open(); using (var comm = new SqlCommand(SQL, conn)) { comm.Parameters.AddWithValue("@Table", datalist.ToDataTable()); comm.ExecuteNonQuery(); } } }
/// <summary> /// Service method to get all patients /// </summary> /// <returns>Data table</returns> public DataTable GetAll() { using (var context = new FHIRDbContext()) { IList <PatientEntity> patients = context.Patients.Where(i => i.SoftDelete == false).ToList(); return(patients.ToDataTable <PatientEntity>()); } }
public DataTable GetAll() { using (var context = new ProductContext()) { IList <Product> products = context.Products.ToList(); return(products.ToDataTable <Product>()); } }
public static void BulkInsert <T>(this SqlConnection connection, IList <T> data, string tableName, params string[] columnNames) { var dataTable = data.ToDataTable(columnNames.ToList()); connection.Open(); dataTable.SqlBulkCopy(tableName, connection); connection.Close(); }
/// <summary> /// Service method to get all club members /// </summary> /// <returns>Data table</returns> public DataTable GetAll() { using (var context = new SocialClubDbContext()) { IList <ClubMember> members = context.ClubMembers.ToList(); return(members.ToDataTable <ClubMember>()); } }
private void AddReserveInfoToTable(IList <ApiReserveTime> infos) { var table = infos.ToDataTable(); DataView dv = table.DefaultView; dv.Sort = "IsValid desc, StartTime asc"; table = dv.ToTable(); grdReserveInfo.DataSource = table; ResourceManagerUtil.ChangeDatagridViewTitle(grdReserveInfo); }
public async Task<IList<Record>> SaveRecordsAsync(IList<Record> records) { return await GetByCommandAsync(cmd => { cmd.UseProcedure("dbo.SaveRecords"); cmd.AddTableParam("@Records", records.ToDataTable()); return cmd.ReadToListAsync<Record>(); }); }
private void Fill <T>(IList <T> data) { grd.DataSource = data.ToDataTable(); if (grd.ColumnCount > 0) { foreach (DataGridViewColumn grdColumn in grd.Columns) { grdColumn.SortMode = DataGridViewColumnSortMode.Automatic; } } }
public void BulkInsertList <T>(string tableName, IList <T> list) where T : class { using (var bulkCopy = new SqlBulkCopy(sqlconnection)) { DataTable table = new DataTable(); table = list.ToDataTable(); bulkCopy.DestinationTableName = tableName; bulkCopy.BatchSize = table.Rows.Count; bulkCopy.WriteToServer(table); } }
public static void BulkMerge <T>(this SqlConnection connection, IList <T> data, string tableName, List <string> idColumns, string[] updateColumnNames, string[] insertColumnNames) { var temptableName = "#" + Guid.NewGuid(); var propertyNames = updateColumnNames.Select(RemoveOperator).ToList(); propertyNames.AddRange(idColumns); propertyNames.AddRange(insertColumnNames); propertyNames = propertyNames.Distinct().ToList(); var dataTable = data.ToDataTable(propertyNames); var sqlCreateTemptable = dataTable.GenerateTableDefinition(temptableName, idColumns); string mergeStatement = string.Empty; mergeStatement += $"MERGE {tableName} t"; mergeStatement += Environment.NewLine; mergeStatement += $" USING [{temptableName}] s"; mergeStatement += Environment.NewLine; mergeStatement += $"ON ({string.Join(" and ", idColumns.Select(x => $"s.[{x}] = t.[{x}]"))})"; mergeStatement += Environment.NewLine; mergeStatement += $"WHEN MATCHED"; mergeStatement += Environment.NewLine; mergeStatement += $" THEN UPDATE SET"; mergeStatement += Environment.NewLine; mergeStatement += string.Join("," + Environment.NewLine, updateColumnNames.Select(x => " " + CreateSetStatement(x, "t", "s"))); mergeStatement += Environment.NewLine; mergeStatement += $"WHEN NOT MATCHED BY TARGET"; mergeStatement += Environment.NewLine; mergeStatement += $" THEN INSERT ({string.Join(", ", insertColumnNames)})"; mergeStatement += Environment.NewLine; mergeStatement += $" VALUES ({string.Join(", ", insertColumnNames.Select(x => $"s.{x}"))})"; mergeStatement += ";"; connection.Open(); using (var createTemptableCommand = connection.CreateCommand()) { createTemptableCommand.CommandText = sqlCreateTemptable; createTemptableCommand.ExecuteNonQuery(); } dataTable.SqlBulkCopy(temptableName, connection); using (var updateCommand = connection.CreateCommand()) { updateCommand.CommandText = mergeStatement; var affectedRows = updateCommand.ExecuteNonQuery(); } connection.Close(); }
private void BindData(IList<Car> vcars) { this.label2.Text = string.Format("{0} Automobiles", vcars.Count); DataTable dt = vcars.ToDataTable(); dataGridView1.AutoGenerateColumns = false; dataGridView1.DataSource = dt; this.devAgeTextBox1.DataBindings.Clear(); this.devAgeTextBox1.DataBindings.Add("Text", dataGridView1.DataSource, "Description"); this.linkLabel1.DataBindings.Clear(); this.linkLabel1.DataBindings.Add("Text", dataGridView1.DataSource, "Link"); if (vcars.Count <= 0) return; dataGridView1.Focus(); dataGridView1.CurrentCell = dataGridView1.Rows[0].Cells[2]; }
public void BulkInsertChain <T>(IList <T> Data, string Name) where T : class { var tbl = Data.ToDataTable(Name); if (_connection.State != ConnectionState.Open) { _connection.Open(); } using (SqlBulkCopy bulk = new SqlBulkCopy((SqlConnection)_connection, SqlBulkCopyOptions.Default, (SqlTransaction)Transaction)) { bulk.DestinationTableName = Name; bulk.WriteToServer(tbl); } }
/// <summary> /// 将IList<T>转化为DataTable后,追加到DataSet中。 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="ds">DataSet</param> /// <param name="list">实体列表</param> /// <param name="tableName">可选,DataTable的名称</param> /// <returns></returns> public static DataSet AddList <T>(this DataSet ds, IList <T> list, string tableName = null) where T : class { if (ds == null) { throw new ArgumentNullException("ds", "DataSet不能为Null"); } tableName = tableName ?? typeof(T).Name; if (!ds.Tables.Contains(tableName)) { var table = list.ToDataTable(tableName); ds.Tables.Add(table); } return(ds); }
private void ShowReport() { ReportUIModel.IsBusyIndicatorHidden = false; try { Utils.EnsureCulture(); var flowDocument = (FlowDocument) Application.LoadComponent( new Uri(string.Format("Templates/{0}.{1}.xaml", _reportFileName, Settings.Default.Culture), UriKind.Relative)); var temp = new WindowBase(false, false, false); flowDocument.FontFamily = temp.FontFamily; flowDocument.FlowDirection = temp.FlowDirection; var reportDocument = new ReportDocument { XamlData = XamlWriter.Save(flowDocument), XpsCompressionOption = CompressionOption.SuperFast }; var data = new ReportData(); data.ReportDocumentValues.Add("ReportedOn", string.Format(ReportPreviewResources.ReportGeneratingDate, DateTime.Now.ToLocalized())); data.ReportDocumentValues.Add("ReportedBy", string.Format(ReportPreviewResources.ReporterFormat, AppContext.Instanse.User.UserName, AppContext.Instanse.User.Name ?? ReportPreviewResources.NoName)); data.ReportDocumentValues.Add("Slogan", ReportPreviewResources.Slogan); data.ReportDocumentValues.Add("ReportTitle", Settings.Default.ReportTitle); data.ReportDocumentValues.Add("ReportDescription", Settings.Default.ReportDescription); data.DataTables.Add(_items.ToDataTable()); var xps = reportDocument.CreateXpsDocument(data); DispatcherHelper.CheckBeginInvokeOnUI(() => ReportUIModel.Document = xps.GetFixedDocumentSequence()); /*} catch (Exception) {*/ } finally { ReportUIModel.IsBusyIndicatorHidden = true; } }
/// <summary> /// 批量新增 /// TODO:暂未实现(bulk insert by sqlbulkcopy, and with transaction.) /// </summary> /// <param name="entities"></param> /// <param name="destinationTableName"></param> public virtual void BulkInsert(IList <T> entities, string destinationTableName = null) { if (!_db.Database.IsSqlServer()) // && !_db.Database.IsMySql() { throw new NotSupportedException("This method only supports for SQL Server."); // or MySql } using (SqlConnection connect = new SqlConnection()) { connect.ConnectionString = _db.Database.GetDbConnection().ConnectionString; if (connect.State != System.Data.ConnectionState.Open) { connect.Open(); } string tableName = string.Empty; var tType = typeof(T); var tableAttribute = tType.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault(); if (tableAttribute != null) { tableName = ((TableAttribute)tableAttribute).Name; } else { tableName = tType.Name; } using (var transaction = connect.BeginTransaction()) { try { var bulkCopy = new SqlBulkCopy(connect, SqlBulkCopyOptions.Default, transaction) { BatchSize = entities.Count, DestinationTableName = tableName }; GenerateColumnMappings <T>(bulkCopy.ColumnMappings); bulkCopy.WriteToServer(entities.ToDataTable()); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } } }
private async Task RefreshGridView(DateTime?since, DateTime?to) { Data = new List <RentDetailVM>(); var rents = await _rents.GetAll(nameof(Employee), nameof(Vehicle), nameof(Customer), nameof(Vehicle) + "." + nameof(Vehicle.VehicleModel)); if (since != null && to != null) { rents = rents.Where(r => r.RentDate >= since && r.RentDate <= to).ToList(); } foreach (var rent in rents) { Data.Add(new RentDetailVM(rent)); } GridRents.DataSource = Data.ToDataTable(); }
private void BindData(IList <Car> vcars) { this.label2.Text = string.Format("{0} Automobiles", vcars.Count); DataTable dt = vcars.ToDataTable(); dataGridView1.AutoGenerateColumns = false; dataGridView1.DataSource = dt; this.devAgeTextBox1.DataBindings.Clear(); this.devAgeTextBox1.DataBindings.Add("Text", dataGridView1.DataSource, "Description"); this.linkLabel1.DataBindings.Clear(); this.linkLabel1.DataBindings.Add("Text", dataGridView1.DataSource, "Link"); if (vcars.Count <= 0) { return; } dataGridView1.Focus(); dataGridView1.CurrentCell = dataGridView1.Rows[0].Cells[2]; }
private void BindarGrid() { try { DateTime DataInicial = DateTime.TryParse(txtDataInicialFiltro.Text, out DataInicial) ? DataInicial : DateTime.MinValue; DateTime DataFinal = DateTime.TryParse(txtDataFinalFiltro.Text, out DataFinal) ? DataFinal : DateTime.MinValue; StatusPedido StatusPedido = (StatusPedido)int.Parse(ddlCliente.SelectedValue); PedidoBll PedidoBll = new PedidoBll(); IList <Pedido> Pedidos = PedidoBll.BuscarPedidosUsuario(this.User.Identity.Name, txtDocumentoFiltro.Text, DataInicial, DataFinal, StatusPedido); gvPedidos.DataSource = Pedidos; gvPedidos.DataBind(); ViewState["Produtos"] = Pedidos.ToDataTable(); } catch (Exception ex) { Msg.Erro(Resource.ContateAdminstrador, this); } }
public static void BulkInsert <T>(this DbContext context, IList <T> entityList, int batchSize = 1000) where T : class { string connectionString = context.Database.Connection.ConnectionString; DataTable dataTable = entityList.ToDataTable(); string tableName = entityList.First().GetType().GetAttributeValue((TableAttribute dna) => dna.Name); if (string.IsNullOrEmpty(tableName)) { tableName = entityList.First().GetType().Name; } using (var sqlBulk = new SqlBulkCopy(connectionString)) { sqlBulk.BatchSize = batchSize; sqlBulk.DestinationTableName = tableName; sqlBulk.WriteToServer(dataTable); } }
public static void BulkInsert <T>(this IDbConnection conn, IList <T> dataList, string destinationTableName, Func <long, int> sqlRowsCopied = null) { using (var sqlDestination = new SqlConnection(conn.ConnectionString)) { if (sqlDestination.State != ConnectionState.Open) { sqlDestination.Open(); } using (var sqlBulkCopy = new SqlBulkCopy(sqlDestination)) { sqlBulkCopy.BatchSize = dataList.Count; sqlBulkCopy.DestinationTableName = destinationTableName; var dataTable = dataList.ToDataTable(); sqlBulkCopy.WriteToServer(dataTable); } } }
private static DataTable PrintTable(IList <Customer> customers) { Console.WriteLine("Printing an IList to a DataTable"); Console.WriteLine("---------------------------"); DataTable table = customers.ToDataTable(); foreach (DataRow row in table.Rows) { Console.WriteLine("Customer"); Console.WriteLine("---------------"); foreach (DataColumn column in table.Columns) { object value = row[column.ColumnName]; Console.WriteLine("{0}: {1}", column.ColumnName, value); } } Console.WriteLine(); return(table); }
// GET: Search Movies through API public ActionResult Search(string movie) { string json = string.Empty; string matrix = movie; string url = String.Format(@"http://www.omdbapi.com/?s={0}&r=json&apikey=5ec4811a", matrix); HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); using (HttpWebResponse response = (HttpWebResponse)request.GetResponse()) using (Stream stream = response.GetResponseStream()) using (StreamReader reader = new StreamReader(stream)) { json = reader.ReadToEnd(); } ViewBag.Response = json; IList <MovieRest> MovieList = (IList <MovieRest>)JsonConvert.DeserializeObject(json); // MovieRest MovieList =(MovieRest) JsonConvert.DeserializeObject(json); ViewBag.Response = MovieList.ToDataTable <MovieRest>(); return(View()); }
/// <summary> /// bulk insert by sqlbulkcopy. /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TKey"></typeparam> /// <param name="entities"></param> /// <param name="destinationTableName"></param> public void BulkInsert <T>(IList <T> entities, string destinationTableName = null) where T : class { if (entities == null || !entities.Any()) { return; } if (string.IsNullOrEmpty(destinationTableName)) { var mappingTableName = typeof(T).GetCustomAttribute <TableAttribute>()?.Name; destinationTableName = string.IsNullOrEmpty(mappingTableName) ? typeof(T).Name : mappingTableName; } using (var dt = entities.ToDataTable()) { using (var conn = new SqlConnection(_option.ConnectionString)) { conn.Open(); using (var tran = conn.BeginTransaction()) { try { using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) { bulk.BatchSize = entities.Count; bulk.DestinationTableName = destinationTableName; bulk.EnableStreaming = true; bulk.WriteToServerAsync(dt); tran.Commit(); } } catch (Exception) { tran.Rollback(); throw; } } conn.Close(); } } }
private static DataTable PrintTable(IList<Customer> customers) { Console.WriteLine("Printing an IList to a DataTable"); Console.WriteLine("---------------------------"); DataTable table = customers.ToDataTable(); foreach (DataRow row in table.Rows) { Console.WriteLine("Customer"); Console.WriteLine("---------------"); foreach (DataColumn column in table.Columns) { object value = row[column.ColumnName]; Console.WriteLine("{0}: {1}", column.ColumnName, value); } } Console.WriteLine(); return table; }
public void BulkInsert <T>(IList <T> Data, string Name) where T : class { var tbl = Data.ToDataTable(Name); using (SqlConnection Con = (SqlConnection)Connection) { using (SqlBulkCopy bulk = new SqlBulkCopy(Con)) { Con.Open(); bulk.DestinationTableName = Name; try { bulk.WriteToServer(tbl); } catch (Exception ex) { throw; } Con.Close(); } } }