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();
}
}
