private void LoadDgvUsers() { List <User> list = new List <User>(); /// Set the datatable based on the SelectedIndex of <see cref="cboUsersViews"/>. switch (cboUsersViews.SelectedIndex) { case 0: list = User.ActiveUsers(); break; case 1: list = User.AllUsers().Where(x => x.StateCode == 1).ToList(); break; default: return; } // convert list to datatable DataTable dataTable = new DataTable(); using (var reader = ObjectReader.Create(list)) { dataTable.Load(reader); } dgvUsers.DataSource = dataTable; // Display/order the columns. dgvUsers.Columns["UserId"].Visible = false; dgvUsers.Columns["FirstName"].Visible = false; dgvUsers.Columns["LastName"].Visible = false; dgvUsers.Columns["EmailAddress"].Visible = false; dgvUsers.Columns["MainPhone"].Visible = false; dgvUsers.Columns["CreatedBy"].Visible = false; dgvUsers.Columns["CreatedOn"].Visible = false; dgvUsers.Columns["ModifiedBy"].Visible = false; dgvUsers.Columns["ModifiedOn"].Visible = false; dgvUsers.Columns["StateCode"].Visible = false; dgvUsers.Columns["DomainName"].DisplayIndex = 0; dgvUsers.Columns["FullName"].DisplayIndex = 1; dgvUsers.Columns["Title"].DisplayIndex = 2; }
private void SaveSubcollection(ConcurrentBag <EToDoProgram> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("MongoToDoId", "MongoToDoId"); bcc.ColumnMappings.Add("MongoProgramId", "MongoProgramId"); bcc.DestinationTableName = "RPT_ToDoProgram"; bcc.WriteToServer(objRdr); } catch (Exception ex) { if (ex.Message.Contains("Received an invalid column length from the bcp client for colid")) { var pattern = @"\d+"; Match match = Regex.Match(ex.Message.ToString(), pattern); var index = Convert.ToInt32(match.Value) - 1; FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance); var sortedColumns = fi.GetValue(bcc); var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns); FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance); var metadata = itemdata.GetValue(items[index]); var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata); var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata); OnDocColEvent(new ETLEventArgs { Message = "[" + Contract + "] ToDo():SqlBulkCopy process failure: " + ex.Message + String.Format("Column: {0} contains data with a length greater than: {1}", column, length) + " : " + ex.InnerException, IsError = true }); } } } }
public void TestReaderSpecifiedColumns() { var source = new[] { new ObjectReaderType { A = 123, B = "abc", C = 1, D = 123 }, new ObjectReaderType { A = 456, B = "def", C = 2, D = null }, new ObjectReaderType { A = 789, B = "ghi", C = 3, D = 789 } }; var table = new DataTable(); using (var reader = ObjectReader.Create(source, "B", "A", "D")) { table.Load(reader); } Assert.Equal(3, table.Columns.Count); Assert.Equal("B", table.Columns[0].ColumnName); Assert.Equal("A", table.Columns[1].ColumnName); Assert.Equal("D", table.Columns[2].ColumnName); Assert.Same(typeof(string), table.Columns[0].DataType); Assert.Same(typeof(int), table.Columns[1].DataType); Assert.Same(typeof(int), table.Columns[2].DataType); Assert.True(table.Columns[0].AllowDBNull, "B/null"); Assert.False(table.Columns[1].AllowDBNull, "A/null"); Assert.True(table.Columns[2].AllowDBNull, "D/null"); Assert.Equal(3, table.Rows.Count); Assert.Equal("abc", table.Rows[0][0]); Assert.Equal(123, table.Rows[0][1]); Assert.Equal(123, table.Rows[0][2]); Assert.Equal("def", table.Rows[1][0]); Assert.Equal(456, table.Rows[1][1]); Assert.Equal(DBNull.Value, table.Rows[1][2]); Assert.Equal("ghi", table.Rows[2][0]); Assert.Equal(789, table.Rows[2][1]); Assert.Equal(789, table.Rows[2][2]); }
private void Products_Load(object sender, EventArgs e) { ShowProductList(); SaleOfficeService saleOfficeService = new SaleOfficeService(); var list = saleOfficeService.GetSaleOffice(); //tao 1 bang DataTable dataTable = new DataTable(); using (var reader = ObjectReader.Create(list)) { dataTable.Load(reader); } cbSaleOf.DataSource = dataTable; //hthi tren giao dien cbSaleOf.DisplayMember = "SOName"; //gtri thuc cbSaleOf.ValueMember = "SOID"; }
public string AktarPdf <T>(List <T> list) { DataTable dataTable = new DataTable(); dataTable.Load(ObjectReader.Create(list)); var fileName = Guid.NewGuid() + ".pdf"; var returnPath = "/documents/" + fileName; var path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot/documents/" + fileName); var stream = new FileStream(path, FileMode.Create); string arialTtf = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Fonts), "arial.ttf"); BaseFont baseFont = BaseFont.CreateFont(arialTtf, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED); Font font = new Font(baseFont, 12, Font.NORMAL); Document document = new Document(PageSize.A4, 25f, 25f, 25f, 25f); PdfWriter.GetInstance(document, stream); document.Open(); PdfPTable pdfpTable = new PdfPTable(dataTable.Columns.Count); for (int i = 0; i < dataTable.Columns.Count; i++) { pdfpTable.AddCell(new Phrase(dataTable.Columns[i].ColumnName, font)); } for (int i = 0; i < dataTable.Rows.Count; i++) { for (int j = 0; j < dataTable.Columns.Count; j++) { pdfpTable.AddCell(new Phrase(dataTable.Rows[i][j].ToString(), font)); } } document.Add(pdfpTable); document.Close(); return(returnPath); }
public void Load <T>(IEnumerable <T> source) where T : class, new() { var worksheet = this._workbook.AddWorksheet(typeof(T).Name); var props = GetExportableProperties(typeof(T)); var table = new DataTable(typeof(T).Name); using (var reader = ObjectReader.Create(source, props.Select(p => p.Name).ToArray())) { table.Load(reader); } worksheet.FirstRow().FirstCell().InsertTable(table, typeof(T).Name); }
internal void ShowTop10AccordingtoDifficulty(Dificuldade dificuldade) { List <Top10Resultado> listaTop10 = Program.M_Status.top10Resultados; IEnumerable <Top10Resultado> data = listaTop10.Where(e => (e.dificuldade == dificuldade.ToString())); DataTable table = new DataTable(); using (var reader = ObjectReader.Create(data)) { table.Load(reader); } table.Columns["Nome"].SetOrdinal(0); table.Columns["Tempo"].SetOrdinal(1); table.Columns["Quando"].SetOrdinal(2); table.Columns["dificuldade"].SetOrdinal(3); dataGrid.RowDetailsTemplate = (DataTemplate)this.Resources["RowDetailsTemplate"]; FillDataGrid(table, dataGrid); }
public DataTable CustomersToDataTable(List <Customer> customers) { var ct = new DataTable("CustomerType"); var r = ObjectReader.Create(customers, "CustomerID", "Title", "FirstName", "LastName", "MiddleName", "CompanyName", "SalesPerson", "EmailAddress", "Phone", "ModifiedDate" ); ct.Load(r); return(ct); }
private void FastMemberDemoForm_Load(object sender, EventArgs e) { #if false var students = new Student[] { new Student() { Name = "Tom", Age = 12, Address = "NYC" }, new Student() { Name = "Jerry", Age = 8, Address = "NYC" }, new Student() { Name = "Lily", Age = 14, Address = "LA" }, new Student() { Name = "Rick", Age = 66, Address = "LA" }, }; #else var students = new List <Student>(); for (int i = 0; i < 100; ++i) { var student = new Student(); student.Name = Faker.Name.First(); student.Address = Faker.Address.City(); student.Age = Faker.RandomNumber.Next(10, 100); students.Add(student); } #endif var dataTable = new DataTable(); using (var reader = ObjectReader.Create(students, "Name", "Age", "Address")) { dataTable.Load(reader); } dataGridView.DataSource = dataTable; }
private void SaveAddress(List <EAddress> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("ContactId", "ContactId"); bcc.ColumnMappings.Add("MongoContactId", "MongoContactId"); bcc.ColumnMappings.Add("MongoId", "MongoId"); bcc.ColumnMappings.Add("TypeId", "TypeId"); bcc.ColumnMappings.Add("MongoCommTypeId", "MongoCommTypeId"); bcc.ColumnMappings.Add("StateId", "StateId"); bcc.ColumnMappings.Add("MongoStateId", "MongoStateId"); bcc.ColumnMappings.Add("Line1", "Line1"); bcc.ColumnMappings.Add("Line2", "Line2"); bcc.ColumnMappings.Add("Line3", "Line3"); bcc.ColumnMappings.Add("City", "City"); bcc.ColumnMappings.Add("PostalCode", "PostalCode"); bcc.ColumnMappings.Add("Preferred", "Preferred"); bcc.ColumnMappings.Add("OptOut", "OptOut"); bcc.ColumnMappings.Add("Delete", "Delete"); bcc.ColumnMappings.Add("Version", "Version"); bcc.ColumnMappings.Add("MongoUpdatedBy", "MongoUpdatedBy"); bcc.ColumnMappings.Add("UpdatedById", "UpdatedById"); bcc.ColumnMappings.Add("LastUpdatedOn", "LastUpdatedOn"); bcc.ColumnMappings.Add("MongoRecordCreatedBy", "MongoRecordCreatedBy"); bcc.ColumnMappings.Add("RecordCreatedById", "RecordCreatedById"); bcc.ColumnMappings.Add("RecordCreatedOn", "RecordCreatedOn"); bcc.ColumnMappings.Add("TTLDate", "TTLDate"); bcc.DestinationTableName = "RPT_ContactAddress"; bcc.WriteToServer(objRdr); } catch (Exception ex) { FormatError(ex, bcc); } } }
static void Main(string[] args) { var data = GetItems(); var connectionString = "data source=localhost;initial catalog=test;Connect Timeout=60;Integrated Security=true"; using (var sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); using (var bcp = new SqlBulkCopy(sqlConnection)) using (var reader = ObjectReader.Create(data)) { bcp.DestinationTableName = "misc"; bcp.AddMappings(data); bcp.WriteToServer(reader); } } Console.WriteLine("Hello World!"); }
private void RefreshGrid(List <T> entities, params Tuple <string, int>[] fields) { _isDataLoaded = true; _fields = fields; _dataTable.Clear(); using (var reader = ObjectReader.Create(entities, fields.Select(x => x.Item1).ToArray())) { _dataTable.Load(reader); } bindingSource.DataMember = _dataTable.TableName; SetTotalCount(entities.Count); grid.ClearSelection(); }
private void SaveSubcollection(ConcurrentBag <EMedication> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("Id", "MongoId"); bcc.ColumnMappings.Add("ProductId", "ProductId"); bcc.ColumnMappings.Add("NDC", "NDC"); bcc.ColumnMappings.Add("FullName", "FullName"); bcc.ColumnMappings.Add("ProprietaryName", "ProprietaryName"); bcc.ColumnMappings.Add("ProprietaryNameSuffix", "ProprietaryNameSuffix"); bcc.ColumnMappings.Add("StartDate", "StartDate"); bcc.ColumnMappings.Add("EndDate", "EndDate"); bcc.ColumnMappings.Add("SubstanceName", "SubstanceName"); bcc.ColumnMappings.Add("Route", "Route"); bcc.ColumnMappings.Add("Form", "Form"); bcc.ColumnMappings.Add("FamilyId", "FamilyId"); bcc.ColumnMappings.Add("Unit", "Unit"); bcc.ColumnMappings.Add("Strength", "Strength"); bcc.ColumnMappings.Add("Version", "Version"); bcc.ColumnMappings.Add("DeleteFlag", "DeleteFlag"); bcc.ColumnMappings.Add("TTLDate", "TTLDate"); bcc.ColumnMappings.Add("LastUpdatedOn", "LastUpdatedOn"); bcc.ColumnMappings.Add("RecordCreatedBy", "MongoRecordCreatedBy"); bcc.ColumnMappings.Add("RecordCreatedOn", "RecordCreatedOn"); bcc.ColumnMappings.Add("MongoUpdatedBy", "MongoUpdatedBy"); bcc.DestinationTableName = "RPT_Medication"; bcc.WriteToServer(objRdr); } catch (Exception ex) { FormatError(ex, bcc); } } }
private void SaveSubcollection(ConcurrentBag <ECareTeam> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("MongoCareTeamId", "MongoCareTeamId"); bcc.ColumnMappings.Add("MongoContactIdForPatient", "MongoContactIdForPatient"); bcc.ColumnMappings.Add("MongoCareMemberId", "MongoCareMemberId"); bcc.ColumnMappings.Add("MongoContactIdForCareMember", "MongoContactIdForCareMember"); bcc.ColumnMappings.Add("RoleId", "RoleId"); bcc.ColumnMappings.Add("CustomRoleName", "CustomRoleName"); bcc.ColumnMappings.Add("StartDate", "StartDate"); bcc.ColumnMappings.Add("EndDate", "EndDate"); bcc.ColumnMappings.Add("Core", "Core"); bcc.ColumnMappings.Add("Notes", "Notes"); bcc.ColumnMappings.Add("FrequencyId", "FrequencyId"); bcc.ColumnMappings.Add("Distance", "Distance"); bcc.ColumnMappings.Add("DistanceUnit", "DistanceUnit"); bcc.ColumnMappings.Add("DataSource", "DataSource"); bcc.ColumnMappings.Add("ExternalRecordId", "ExternalRecordId"); bcc.ColumnMappings.Add("Status", "Status"); bcc.ColumnMappings.Add("Version", "Version"); bcc.ColumnMappings.Add("DeleteFlag", "DeleteFlag"); bcc.ColumnMappings.Add("TTLDate", "TTLDate"); bcc.ColumnMappings.Add("UpdatedBy", "UpdatedBy"); bcc.ColumnMappings.Add("LastUpdatedOn", "LastUpdatedOn"); bcc.ColumnMappings.Add("RecordCreatedBy", "RecordCreatedBy"); bcc.ColumnMappings.Add("RecordCreatedOn", "RecordCreatedOn"); bcc.DestinationTableName = "RPT_CareTeam"; bcc.WriteToServer(objRdr); } catch (Exception ex) { FormatError(ex, bcc); } } }
/// <summary> /// Bulk Inserts Items to DB /// </summary> /// <param name="rows">rows to be bulk inserted</param> /// <param name="tableName">table name to bulk insert into it</param> public void BulkInsert(List <T> rows, string tableName) { if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException(); } using (var c = new SqlBulkCopy(_context.Database.GetDbConnection().ConnectionString)) { c.DestinationTableName = tableName; c.BulkCopyTimeout = 60; //c.BatchSize = 50 string[] props; using (var sqlCmd = new SqlCommand()) { sqlCmd.CommandText = $"SELECT TOP(1) * FROM {tableName}"; sqlCmd.Connection = new SqlConnection(_context.Database.GetDbConnection().ConnectionString); var da = new SqlDataAdapter { SelectCommand = sqlCmd }; var ds = new DataSet(); da.FillSchema(ds, SchemaType.Source); props = ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); } var table = new DataTable(); //var row = rows.FirstOrDefault(); //_context.Model.FindEntityType(typeof(T)).N using (var reader = ObjectReader.Create(rows, props)) { table.Load(reader); } c.WriteToServer(table.CreateDataReader()); } }
private void SaveContact(List <EContact> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("ContactId", "ContactId"); bcc.ColumnMappings.Add("PatientId", "PatientId"); bcc.ColumnMappings.Add("MongoPatientId", "MongoPatientId"); bcc.ColumnMappings.Add("MongoId", "MongoId"); bcc.ColumnMappings.Add("Version", "Version"); bcc.ColumnMappings.Add("MongoUpdatedBy", "MongoUpdatedBy"); bcc.ColumnMappings.Add("UpdatedBy", "UpdatedById"); bcc.ColumnMappings.Add("LastUpdatedOn", "LastUpdatedOn"); bcc.ColumnMappings.Add("MongoRecordCreatedBy", "MongoRecordCreatedBy"); bcc.ColumnMappings.Add("RecordCreatedById", "RecordCreatedById"); bcc.ColumnMappings.Add("RecordCreatedOn", "RecordCreatedOn"); bcc.ColumnMappings.Add("ResourceId", "ResourceId"); bcc.ColumnMappings.Add("FirstName", "FirstName"); bcc.ColumnMappings.Add("MiddleName", "MiddleName"); bcc.ColumnMappings.Add("LastName", "LastName"); bcc.ColumnMappings.Add("PreferredName", "PreferredName"); bcc.ColumnMappings.Add("Gender", "Gender"); bcc.ColumnMappings.Add("MongoTimeZone", "MongoTimeZone"); bcc.ColumnMappings.Add("TimeZone", "TimeZone"); bcc.ColumnMappings.Add("TTLDate", "TTLDate"); bcc.ColumnMappings.Add("Delete", "Delete"); bcc.ColumnMappings.Add("ExtraElements", "ExtraElements"); bcc.DestinationTableName = "RPT_Contact"; bcc.WriteToServer(objRdr); } catch (Exception ex) { FormatError(ex, bcc); } } }
/// <summary> /// Convert an IEnumerable into a Sql Server table-valued parameter. /// </summary> /// <typeparam name="T">Type of the data.</typeparam> /// <param name="paramName">Name of the parameter.</param> /// <param name="data">Data values for the table.</param> /// <param name="columnNames">List of columns to add to the data. Allows you to not include all of the classes columns.</param> /// <returns>IDbDataParameter</returns> public static IDbDataParameter MakeTableValuedParameter <T>(string paramName, IEnumerable <T> data, params string[] columnNames) { var prm = new SqlParameter(paramName, SqlDbType.Structured); using (var table = new DataTable()) { // For simple types, create the data table ourselves, otherwise use FastMember. if (typeof(T).IsValueType || typeof(T).Equals(typeof(string))) { var colName = (columnNames.Length > 0) ? columnNames[0] : "Value"; var dc = new DataColumn(colName); dc.DataType = typeof(T); table.Columns.Add(dc); foreach (var item in data) { var dr = table.NewRow(); dr[0] = item; table.Rows.Add(dr); } } else { // If the columns aren't specified then grab the list of columns from the model // and pass them into FastMember. If you don't specify the columns then FastMember // will always put them in alpha order and we don't want that. if (columnNames.Length == 0) { columnNames = ReflectionHelper.GetPropertyNames <T>().ToArray(); } using (var reader = ObjectReader.Create(data, columnNames)) { table.Load(reader); } } prm.Value = table; } return(prm); }
private void CargaDatos() { DataTable dt = new DataTable(); using (var reader = ObjectReader.Create(DataFinal)) { dt.Load(reader); } Grafico.Titles.Clear(); Grafico.Series.Clear(); Grafico.ChartAreas.Clear(); Grafico.Palette = ChartColorPalette.Pastel; ChartArea areagrafica = new ChartArea(); areagrafica.Area3DStyle.Enable3D = true; areagrafica.Area3DStyle.Inclination = 25; Grafico.ChartAreas.Add(areagrafica); Title titulo = new Title("Grafica de Pie (Frecuencia Relativa)"); titulo.Font = new Font("Arial", 15, FontStyle.Bold); Grafico.Titles.Add(titulo); string nombreserie = "GraficoPie"; Grafico.Series.Add(nombreserie); Grafico.Series[nombreserie].ChartType = SeriesChartType.Pie; Grafico.Series[nombreserie].XValueMember = "Meses"; Grafico.Series[nombreserie].YValueMembers = "Meses"; Grafico.Series[nombreserie].IsValueShownAsLabel = true; Grafico.Series[nombreserie].LabelFormat = "#.##%"; foreach (DataRow dr in dt.Rows) { string strserie = string.IsNullOrEmpty(dr.ItemArray[4].ToString()) ? dr.ItemArray[5].ToString() : dr.ItemArray[4].ToString(); Grafico.Series[nombreserie].Points.AddXY(strserie, dr.ItemArray[2]); } }
public void Upload(List <UmiItem> euroMadeData, Action <string> updateState) { updateState("Инициализация загрузчика."); using (SqlConnection connection = ConnectionBuilder.ParserDB as SqlConnection) { connection.Open(); updateState("Подготовка транзакции."); SqlTransaction transaction = connection.BeginTransaction(); using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepNulls, transaction)) { bulkCopy.BatchSize = 500; bulkCopy.DestinationTableName = "dbo.EuroItems"; bulkCopy.ColumnMappings.Add(nameof(UmiItem.ObjectID), nameof(UmiItem.ObjectID)); bulkCopy.ColumnMappings.Add(nameof(UmiItem.FieldID), nameof(UmiItem.FieldID)); bulkCopy.ColumnMappings.Add(nameof(UmiItem.EuroMadePrice), nameof(UmiItem.EuroMadePrice)); bulkCopy.ColumnMappings.Add(nameof(UmiItem.UploadToMarket), nameof(UmiItem.UploadToMarket)); bulkCopy.ColumnMappings.Add(nameof(UmiItem.UriOrName), nameof(UmiItem.UriOrName)); try { using (var reader = ObjectReader.Create(euroMadeData)) { updateState("Запись объектов транзакции."); bulkCopy.WriteToServer(reader); } } catch (Exception e) { updateState("Отмена транзакции."); transaction.Rollback(); connection.Close(); } } transaction.Commit(); } updateState("Все объекты загружены"); }
public async Task AdicionarItens(IEnumerable <PrefixoModel> t, int c, int?u) { using (var conn = new SqlConnection(Util.ConnString)) { await conn.OpenAsync(); var tran = conn.BeginTransaction(); try { await conn.ExecuteAsync("TRUNCATE TABLE HELPER.dbo.PREFIXO", transaction : tran); using (var bcp = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) using (var reader = ObjectReader.Create(t.Select(a => new { a.Prefixo, OperadoraID = (byte)a.Operadora }), "Prefixo", "OperadoraID")) { bcp.DestinationTableName = "HELPER.dbo.PREFIXO"; bcp.ColumnMappings.Add("Prefixo", "PREFIXO"); bcp.ColumnMappings.Add("OperadoraID", "OPERADORAID"); await bcp.WriteToServerAsync(reader); } tran.Commit(); } catch (Exception err) { tran.Rollback(); throw err; } finally { tran.Dispose(); conn.Close(); } } }
static void Main(string[] args) { IList <MyClass> ls = new List <MyClass>(); ls.Add(new MyClass { MyColumn1 = "The" }); ls.Add(new MyClass { MyColumn1 = "Big" }); ls.Add(new MyClass { MyColumn1 = "Ant" }); DataTable dt = new DataTable(); using (var reader = ObjectReader.Create(ls)) { dt.Load(reader); } }
public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <double> progress = null, int batchSize = 2000) { var sqlBulkCopy = new SqlBulkCopy(context.Database.GetDbConnection().ConnectionString) { DestinationTableName = tableInfo.InsertToTempTable ? tableInfo.FullTempTableName : tableInfo.FullTableName, BatchSize = batchSize, NotifyAfter = batchSize }; sqlBulkCopy.SqlRowsCopied += (sender, e) => { progress?.Invoke(e.RowsCopied / entities.Count); }; foreach (var element in tableInfo.PropertyColumnNamesDict) { sqlBulkCopy.ColumnMappings.Add(element.Key, element.Value); } using (var reader = ObjectReader.Create(entities, tableInfo.PropertyColumnNamesDict.Keys.ToArray())) { sqlBulkCopy.WriteToServer(reader); } }
private void DeleteProductBtn_Click(object sender, RoutedEventArgs e) { object item = dgProduct.SelectedItem; int id = Int32.Parse((dgProduct.SelectedCells[0].Column.GetCellContent(item) as TextBlock).Text); if (Database.DeleteItem(id) == HttpStatusCode.OK) { MessageBox.Show("Delete Succesfully!"); var items = Database.GetCart(); DataTable dataTable = new DataTable(); using (var reader = ObjectReader.Create(items, "id", "productId", "quantity", "productName", "quantity", "totalPrice")) { dataTable.Load(reader); } dgProduct.ItemsSource = dataTable.DefaultView; } }
public async Task ImportToStagingTable(IEnumerable <CharityImport> charityImports) { using (var connection = new SqlConnection(_connectionString)) using (var bulkCopy = new SqlBulkCopy(connection)) { await connection.OpenAsync().ConfigureAwait(false); bulkCopy.BatchSize = 1000; bulkCopy.BulkCopyTimeout = 3600; bulkCopy.DestinationTableName = "[CharityImport].[extract_charity_import]"; bulkCopy.ColumnMappings.Clear(); PopulateBulkCopy(bulkCopy, typeof(CharityImport)); using (var reader = ObjectReader.Create(charityImports)) { await bulkCopy.WriteToServerAsync(reader).ConfigureAwait(false); } } }
private void SavePatientUtilizationCollection(ConcurrentBag <EPatientUtilization> dic) { var rSeries = dic.AsEnumerable(); using (var bcc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.Default)) using (var objRdr = ObjectReader.Create(rSeries)) { try { bcc.BulkCopyTimeout = 580; bcc.ColumnMappings.Add("Id", "MongoId"); bcc.ColumnMappings.Add("PatientId", "MongoPatientId"); bcc.ColumnMappings.Add("NoteTypeId", "MongoNoteTypeId"); bcc.ColumnMappings.Add("Reason", "Reason"); bcc.ColumnMappings.Add("VisitTypeId", "MongoVisitTypeId"); bcc.ColumnMappings.Add("OtherVisitType", "OtherVisitType"); bcc.ColumnMappings.Add("AdmitDate", "AdmitDate"); bcc.ColumnMappings.Add("Admitted", "Admitted"); bcc.ColumnMappings.Add("DischargeDate", "DischargeDate"); bcc.ColumnMappings.Add("LocationId", "MongoLocationId"); bcc.ColumnMappings.Add("OtherLocation", "OtherLocation"); bcc.ColumnMappings.Add("DispositionId", "MongoDispositionId"); bcc.ColumnMappings.Add("OtherDisposition", "OtherDisposition"); bcc.ColumnMappings.Add("UtilizationSourceId", "MongoUtilizationSourceId"); bcc.ColumnMappings.Add("DataSource", "DataSource"); bcc.ColumnMappings.Add("UpdatedBy", "MongoUpdatedBy"); bcc.ColumnMappings.Add("LastUpdatedOn", "LastUpdatedOn"); bcc.ColumnMappings.Add("RecordCreatedBy", "MongoRecordCreatedBy"); bcc.ColumnMappings.Add("RecordCreatedOn", "RecordCreatedOn"); bcc.ColumnMappings.Add("Version", "Version"); bcc.ColumnMappings.Add("DeleteFlag", "Delete"); bcc.DestinationTableName = "RPT_PatientUtilization"; bcc.WriteToServer(objRdr); } catch (Exception ex) { FormatError(ex, bcc); } } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { IIoCManager ioCManager = (IIoCManager)HttpContext.Current.Application["managerIoC"]; IRecommendationGroupService recommendationGroupService = ioCManager.Resolve <IRecommendationGroupService>(); HttpCookie cookie = Request.Cookies["loginName"]; List <DTOGroupsUser> groupsUsers = recommendationGroupService.ShowUserGroups(cookie.Value); if (groupsUsers.Count == 0) { lblNoGroups.Visible = true; lblGroups.Visible = false; return; } lblGroups.Visible = false; #region //Forma normal //this.gvGroups.DataSource = groupsUsers; //this.DataBind(); #endregion //Forma normal #region //Forma opcional : Utilizando paquete Nuget: FastMember DataTable table = new DataTable(); using (var reader = ObjectReader.Create(groupsUsers, "group_usersId", "gr_name")) { table.Load(reader); } ViewState["groups"] = table; this.gvGroups.DataSource = (DataTable)ViewState["groups"];; this.DataBind(); this.gvGroups.Columns[0].Visible = false; #endregion //Forma opcional : Utilizando paquete Nuget: FastMember } }
private void button1_Click(object sender, EventArgs e) { OpenFileDialog of = new OpenFileDialog(); of.Filter = Variables.excelFilter; if (of.ShowDialog() == DialogResult.OK) { loadForm.ShowSplashScreen(); DataSet ds = Util.openExcel(of.FileName); DataTable dt = ds.Tables[0]; DataRow[] rowsToDelete = dt.Select("Column0 is NULL"); foreach (var row in rowsToDelete) { dt.Rows.Remove(row); } dt.Rows.RemoveAt(0); int COLINDEX_TANGGAL = 'A' - 'A', COLINDEX_WSID = 'B' - 'A', COLINDEX_NOMINALSetoran = 'C' - 'A'; List <TabelTarikanSetoranCRM> dataExcel = new List <TabelTarikanSetoranCRM>(); foreach (DataRow row in dt.Rows) { dataExcel.Add(new TabelTarikanSetoranCRM() { tanggal = DateTime.Parse(row[COLINDEX_TANGGAL].ToString()), wsid = row[COLINDEX_WSID].ToString(), nominal = Int64.Parse(row[COLINDEX_NOMINALSetoran].ToString()), jenis = "Setoran" }); } using (SqlBulkCopy sbq = new SqlBulkCopy(Variables.connectionString)) { using (var reader = ObjectReader.Create(dataExcel, "tanggal", "wsid", "nominal", "jenis")) { sbq.DestinationTableName = "dbo.TabelTarikanSetoranCRM"; sbq.WriteToServer(reader); } } loadForm.CloseForm(); } }
private void AddBillBtn_Click(object sender, RoutedEventArgs e) { if (Database.Checkout() == HttpStatusCode.OK) { MessageBox.Show("Checkout successfully"); var items = Database.GetCart(); DataTable dataTable = new DataTable(); using (var reader = ObjectReader.Create(items, "id", "productId", "quantity", "productName", "quantity", "totalPrice")) { dataTable.Load(reader); } dgProduct.ItemsSource = dataTable.DefaultView; } else { MessageBox.Show("Invalid input"); } }
public async Task BulkInsertRequiredPayments(List <ItRequiredPayment> requiredPayments) { using (var conn = DatabaseConnection.Connection()) { await conn.OpenAsync().ConfigureAwait(false); using (var bcp = new SqlBulkCopy(conn)) using (var reader = ObjectReader.Create(requiredPayments, "Id", "CommitmentId", "CommitmentVersionId", "AccountId", "AccountVersionId", "Uln", "LearnRefNumber", "AimSeqNumber", "Ukprn", "IlrSubmissionDateTime", "PriceEpisodeIdentifier", "StandardCode", "ProgrammeType", "FrameworkCode", "PathwayCode", "ApprenticeshipContractType", "DeliveryMonth", "DeliveryYear", "CollectionPeriodName", "CollectionPeriodMonth", "CollectionPeriodYear", "TransactionType", "AmountDue", "SfaContributionPercentage", "FundingLineType", "UseLevyBalance", "LearnAimRef", "LearningStartDate")) { bcp.DestinationTableName = "[PaymentsDue].[RequiredPayments]"; await bcp.WriteToServerAsync(reader).ConfigureAwait(false); } } }
public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { var sqlConnection = (SqlConnection)context.Database.GetDbConnection(); try { sqlConnection.Open(); using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection)) { tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, progress); using (var reader = ObjectReader.Create(entities, tableInfo.PropertyColumnNamesDict.Keys.ToArray())) { sqlBulkCopy.WriteToServer(reader); } } } finally { sqlConnection.Close(); } }