#c# #asp.net
#c# #asp.net
Вопрос:
Мне нужно прочитать файл Microsft Excel CSV и сохранить его в базе данных. Размер файла составляет около 500 МБ. Столбец содержит более 200 полей, а строка — около 400000. Я прочитал весь файл в Datatable? При отображении данных из Datatable в таблицу sql это занимает очень много времени?Есть ли способ сократить время?
public static DataTable GetRecordstoMigrate(string TableName, DataTable SourceDataRecords, List<DataMiddlewareMapping> list, string FileName)
{
DataTable DestinationRecords = new DataTable();
try
{
ExceptionLoggingInFile.SendSusssesToText("Start Data Mapping of " TableName);
Console.WriteLine("Data conversion started {0}", DateTime.Now);
list.OrderBy(o => o.Position).ToList().ForEach(c =>
{
DestinationRecords.Columns.Add(new DataColumn()
{
ColumnName = c.ColumnName,
DataType =
c.DataType == "nvarchar" ? System.Type.GetType("System.String") :
c.DataType == "int" ? System.Type.GetType("System.Int32") :
c.DataType == "bit" ? System.Type.GetType("System.Boolean") :
c.DataType == "decimal" ? System.Type.GetType("System.Decimal") :
c.DataType == "varbinary" ? System.Type.GetType("System.Byte[]") :
c.DataType == "varchar" ? System.Type.GetType("System.String") :
c.DataType == "datetime" ? System.Type.GetType("System.DateTime") :
c.DataType == "uniqueidentifier" ? System.Type.GetType("System.Guid") :
c.DataType == "float" ? System.Type.GetType("System.Float") :
c.DataType == "date" ? System.Type.GetType("System.DateTime") :
c.DataType == "bigint" ? System.Type.GetType("System.Int64") :
System.Type.GetType("System.Boolean")
});
});
foreach (DataRow SourceData in SourceDataRecords.Rows)
{
DataRow NewRecord = DestinationRecords.NewRow();
switch (TableName)
{
case "KYC_Individual_Details":
GetKYC_Individual_DetailsRecord(SourceData, list, ref NewRecord);
break;
case "KYC_Individual_Customer_Accounts":
GetKYC_Individual_Customer_AccountsRecord(SourceData, list, ref NewRecord);
break;
}
DestinationRecords.Rows.Add(NewRecord);
}
ExceptionLoggingInFile.SendSusssesToText("End Data Mapping of " TableName);
ImportDataFromFile(TableName, list, DestinationRecords).Wait();
Console.WriteLine("Data conversion ended {0}", DateTime.Now);
}
catch (Exception ex)
{
Console.WriteLine("Exception for Data conversion {0} {1}", DateTime.Now, ex.Message);
}
return DestinationRecords;
}
public static void GetKYC_Individual_DetailsRecord(DataRow SourceData, List<DataMiddlewareMapping> list, ref DataRow NewRecord)
{
try
{
object _ColumnValueForCustomerType = DBNull.Value;
var localRef = SourceData["LOCAL_REF"].ToString().Split('').ToList();
List<string> PositionListForFamily = new List<string>();
List<string> PositionValueListForFamily = new List<string>();
string CustomerTypeSectorNo = SourceData["SECTOR"].ToString();
bool resultssssss;
int ab;
resultssssss = int.TryParse(CustomerTypeSectorNo, out ab);
int CustomerType = 1;
if (resultssssss amp;amp; ab >= 2002)
{
CustomerType = 3;
}
if ((localRef.Count > 36))
{
PositionListForFamily = localRef[37].Split('').ToList();
PositionValueListForFamily = localRef[38].Split('').ToList();
}
foreach (var c in list.OrderBy(o => o.Position).ToList())
try
{
object _ColumnValue = DBNull.Value;
_ColumnValue = GetColumnValue(c.ColumnMapping, SourceData, c.ColumnName);
switch (c.ColumnName)
{
case "Id":
NewRecord[c.Position - 1] = Guid.NewGuid();
break;
case "CUSTOMER_NO":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "FIRST_NAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "MIDDLE_NAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "LAST_NAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "FULLNAME":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "COMPANY_NAME":
NewRecord[c.Position - 1] = CustomerType == 3 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "REG_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "DOB":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "BRANCH":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : _ColumnValue; ;
break;
case "GENDER":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString() == "MALE" ? "1" : _ColumnValue.ToString() == "FEMALE" ? "2" : "0" : "0";
break;
case "MaritialStatus":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString() == "SINGLE" ? "1" : _ColumnValue.ToString() == "MARRIED" ? "2" : _ColumnValue.ToString() == "DIVORCED" ? "5" : _ColumnValue.ToString() == "WIDOWED" ? "4" : "0" : "0";
break;
case "TEMPORARY_COUNTRY_CODE":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TEMPORARY_COUNTRY_DESCRIPTION":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_COUNTRY_CODE":
NewRecord[c.Position - 1] = _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_COUNTRY_DESCRIPTION":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TEMPORARY_ZONE":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TEMPORARY_DISTRICT":
int tintDistrict = 0;
if (string.IsNullOrEmpty(_ColumnValue.ToString()))
{
NewRecord[c.Position - 1] = DBNull.Value;
}
else
{
var _value = int.TryParse(_ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*"), out tintDistrict);
if (tintDistrict > 0)
NewRecord[c.Position - 1] = Enum.Parse((typeof(District)), tintDistrict.ToString()).ToString().ToUpper();
else
NewRecord[c.Position - 1] = _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*");
}
break;
case "TEMPORARY_VDC_M":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TEMPORARY_LOCAL":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TEMPORARY_WARD_NO":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_ZONE":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_DISTRICT":
int intDistrict = 0;
if (string.IsNullOrEmpty(_ColumnValue.ToString()))
{
NewRecord[c.Position - 1] = DBNull.Value;
}
else
{
var _value = int.TryParse(_ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*"), out intDistrict);
if (intDistrict > 0)
NewRecord[c.Position - 1] = Enum.Parse((typeof(District)), intDistrict.ToString()).ToString().ToUpper();
else
NewRecord[c.Position - 1] = _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*");
}
break;
case "PERMANENT_VDC_M":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_LOCAL":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PERMANENT_WARD_NO":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "FAXNO":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TELNO":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "REGNO":
NewRecord[c.Position - 1] = CustomerType == 2 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "PAN_NUMBER":
NewRecord[c.Position - 1] = CustomerType == 2 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "PAN_EXP_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "VATNO":
NewRecord[c.Position - 1] = CustomerType == 3 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "VAT_EXP_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "MOBILE_NUMBER":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "TELEPHONE":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "PASSPORT_NO":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "PASSPORT_RELEASE_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "PASSPORT_ISSUED_PLACE":
if (CustomerType == 1)
{
int PISSUED_PLACE_INT = 0;
if (string.IsNullOrEmpty(_ColumnValue.ToString()))
{
NewRecord[c.Position - 1] = DBNull.Value;
}
else
{
var _value = int.TryParse(_ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*"), out PISSUED_PLACE_INT);
if (PISSUED_PLACE_INT > 0)
NewRecord[c.Position - 1] = Enum.Parse((typeof(District)), PISSUED_PLACE_INT.ToString()).ToString().ToUpper();
else
NewRecord[c.Position - 1] = _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*");
}
}
else
{
NewRecord[c.Position - 1] = DBNull.Value;
}
break;
case "VISA_EXPIRY_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "CITIZENSHIP":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "CITIZENSHIP_RELEASE_DATE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "CITIZENSHIP_ISSUED_PLACE":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "IS_MINOR":
NewRecord[c.Position - 1] = (CustomerType == 1 amp;amp; _ColumnValue.ToString() == "1007") ? "1" : "0";
break;
case "MINOR_RELATIONSHIP":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue : DBNull.Value;
break;
case "FATHERNAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "GRANDDADNAME":
NewRecord[c.Position - 1] = DBNull.Value;
break;
case "MOTHERNAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "SPOUSE_NAME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "OCCUPATION":
NewRecord[c.Position - 1] = _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "CUSTOMER_TYPE":
NewRecord[c.Position - 1] = CustomerType.ToString();
break;
case "CUSTOMER_CATEGORY":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "ANNUAL_INCOME":
NewRecord[c.Position - 1] = _ColumnValue.ToString();
break;
case "FAMILY_SIZE":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "RISK_PROFILE":
NewRecord[c.Position - 1] = CustomerType == 1 ? _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object : DBNull.Value;
break;
case "EMAIL":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "SCORE":
NewRecord[c.Position - 1] = DBNull.Value;
break;
case "PROFILE":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? "1" : _ColumnValue.ToString().ToUpper() == "HIGH" ? "3" : _ColumnValue.ToString().ToUpper() == "MEDIUM" ? "2" : _ColumnValue.ToString().ToUpper() == "LOW" ? "1" : "1";
break;
case "CRITICALITY":
NewRecord[c.Position - 1] = DBNull.Value;
break;
case "PROFILE_PIC":
NewRecord[c.Position - 1] = DBNull.Value;
break;
case "VERIFIED_BY":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? DBNull.Value : _ColumnValue.ToString().Substring(0, (_ColumnValue.ToString().Length > c.Length ? c.Length : _ColumnValue.ToString().Length)).Replace("", "*") as object;
break;
case "KYCCompletd":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(_ColumnValue.ToString()) ? "0" : "1";
break;
case "STAFF_CUSTOMER":
NewRecord[c.Position - 1] = (CustomerType == 1 amp;amp; _ColumnValue.ToString() == "1002") ? "1" : "0";
break;
case "CreatedById":
NewRecord[c.Position - 1] = CreatedById;
break;
case "ModifiedById":
NewRecord[c.Position - 1] = CreatedById;
break;
case "AuthorisedById":
NewRecord[c.Position - 1] = CreatedById;
break;
case "CreatedDate":
NewRecord[c.Position - 1] = string.IsNullOrEmpty(SourceData[2].ToString()) ?
(DateTime)System.Data.SqlTypes.SqlDateTime.MinValue :
DateTime.ParseExact(SourceData[2].ToString(), "yyyyMMdd", CultureInfo.CurrentCulture) as object;
break;
case "ModifiedDate":
NewRecord[c.Position - 1] = DateTime.Now.Date;
break;
case "AuthorisedDate":
NewRecord[c.Position - 1] = DateTime.Now.Date;
break;
case "Record_Status":
NewRecord[c.Position - 1] = "2";
break;
case "Deleted_Status":
NewRecord[c.Position - 1] = DBNull.Value;
break;
case "DataEntry":
NewRecord[c.Position - 1] = "2";
break;
}
}
catch (Exception ex)
{
ExceptionLoggingInFile.SendErrorToText(ex, "Error from GetKYC_Individual_DetailsRecord method");
Console.WriteLine("Exception : {0} at {1}", ex.Message, c.ColumnName);
}
}
catch (Exception ex)
{
ExceptionLoggingInFile.SendErrorToText(ex, "Error from GetKYC_Individual_DetailsRecord method");
}
}
public static async Task ImportDataFromFile(string TableName, List<DataMiddlewareMapping> list, DataTable Records)
{
try
{
using (SqlConnection destinationConnection = new SqlConnection(_connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = string.Format("[dbo].[Temporary_{0}]", TableName);
bulkCopy.BatchSize = 20000;
bulkCopy.BulkCopyTimeout = 10000;
try
{
list.OrderBy(o => o.Position).ToList().ForEach(c =>
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping()
{
DestinationColumn = c.ColumnName,
SourceColumn = c.ColumnName
});
});
await bulkCopy.WriteToServerAsync(Records);
Console.WriteLine("Total Data imported", Records.Rows.Count);
}
catch (Exception ex)
{
ExceptionLoggingInFile.SendErrorToText(ex, "Error from ImportDataFromFile method");
Console.WriteLine(ex.Message);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception for Data import for sql {0} {1}", DateTime.Now, ex.Message);
}
}
Комментарии:
1. Что такое «слишком длинные» 10 минут? 10 часов?
2. в моем случае более 3 часов
3. Существует так много причин, по которым это может быть так медленно (чтение csv, компьютерное оборудование, типы данных, подключение к БД и т.д.). Разделите все это на отдельные блоки кода, такие как чтение csv, преобразование его в datatable, запись в БД и т.д., И оптимизируйте каждый шаг за шагом.
4. Глядя на ваш код, это может быть связано с производительностью при запуске SqlBulkCopy. Это можно оптимизировать, изменив размер пакета — для таблицы с 200 полями ваш текущий размер в 20 тысяч строк за раз вполне может быть (и, вероятно, есть) слишком большим за один раз, поэтому вы можете уменьшить его. Согласен с VDWWD, хотя я бы начал с отладки вашего кода и точного определения того, что занимает много времени. Есть много вещей, которые вы можете сделать, чтобы улучшить производительность приведенного выше кода
5. Спасибо, хорошо, я постараюсь, как вы говорите. Кроме того, при отладке я обнаружил, что сопоставление из sourceDatatable в DestinationDatatable занимает большую часть времени. Я не понимаю, нормально ли это или занимает больше времени, поскольку я просто использую цикл foreach в этой функции. У меня есть четыре файла, которые нужно делать ежедневно, поэтому мне нужно было сократить общее время.