/// <inheritdoc/>
protected override DataSet CreateDataSet()
{
DataSet dataset = base.CreateDataSet();
CsvConnectionStringBuilder builder = new CsvConnectionStringBuilder(ConnectionString);
List <string> rawLines = CsvUtils.ReadLines(builder);
DataTable table = CsvUtils.CreateDataTable(builder, rawLines);
if (table != null)
{
dataset.Tables.Add(table);
}
return(dataset);
}
internal static List <string> ReadLines(CsvConnectionStringBuilder builder, int maxLines = 0)
{
if (String.IsNullOrEmpty(builder.CsvFile) || String.IsNullOrEmpty(builder.Separator))
{
return(null);
}
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
WebRequest request;
WebResponse response = null;
try
{
Uri uri = new Uri(builder.CsvFile);
if (uri.IsFile)
{
if (Config.ForbidLocalData)
{
throw new Exception(Res.Get("ConnectionEditors,Common,OnlyUrlException"));
}
request = (FileWebRequest)WebRequest.Create(uri);
request.Timeout = 5000;
response = (FileWebResponse)request.GetResponse();
}
else if (uri.OriginalString.StartsWith("http"))
{
request = (HttpWebRequest)WebRequest.Create(uri);
request.Timeout = 5000;
response = (HttpWebResponse)request.GetResponse();
}
else if (uri.OriginalString.StartsWith("ftp"))
{
request = (FtpWebRequest)WebRequest.Create(uri);
request.Timeout = 5000;
response = (FtpWebResponse)request.GetResponse();
}
}
catch (Exception e)
{
throw e;
}
if (response == null)
{
return(null);
}
List <string> lines = new List <string>();
if (maxLines == 0)
{
maxLines = int.MaxValue;
}
// read lines
using (StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding(builder.Codepage)))
{
for (int i = 0; i < maxLines; i++)
{
string line = reader.ReadLine();
// end of stream reached
if (line == null)
{
break;
}
// skip empty lines
if (!String.IsNullOrEmpty(line))
{
lines.Add(line);
}
}
}
return(lines);
}
internal static DataTable CreateDataTable(CsvConnectionStringBuilder builder, List <string> rawLines)
{
if (rawLines == null)
{
return(null);
}
// split each line to array of values
List <string[]> lines = new List <string[]>();
for (int i = 0; i < rawLines.Count; i++)
{
string line = rawLines[i];
string[] values = line.Split(builder.Separator.ToCharArray());
if (builder.RemoveQuotationMarks)
{
for (int j = 0; j < values.Length; j++)
{
values[j] = values[j].Trim("\"".ToCharArray());
}
}
lines.Add(values);
}
if (lines.Count == 0)
{
return(null);
}
NumberFormatInfo numberInfo = CultureInfo.GetCultureInfo(builder.NumberFormat)?.NumberFormat ?? CultureInfo.CurrentCulture.NumberFormat;
NumberFormatInfo currencyInfo = CultureInfo.GetCultureInfo(builder.CurrencyFormat)?.NumberFormat ?? CultureInfo.CurrentCulture.NumberFormat;
DateTimeFormatInfo dateTimeInfo = CultureInfo.GetCultureInfo(builder.DateTimeFormat)?.DateTimeFormat ?? CultureInfo.CurrentCulture.DateTimeFormat;
// get table name from file name
string tableName = Path.GetFileNameWithoutExtension(builder.CsvFile).Replace(".", "_");
if (String.IsNullOrEmpty(tableName))
{
tableName = "Table";
}
DataTable table = new DataTable(tableName);
string[] fields = lines[0];
// create table columns
for (int i = 0; i < fields.Length; i++)
{
DataColumn column = new DataColumn();
column.DataType = typeof(string);
// get field names from first string if needed
string fieldName = fields[i].Replace("\t", "");
if (builder.FieldNamesInFirstString && !table.Columns.Contains(fieldName))
{
column.ColumnName = fieldName;
column.Caption = column.ColumnName;
}
else
{
column.ColumnName = DEFAULT_FIELD_NAME + i.ToString();
column.Caption = column.ColumnName;
}
table.Columns.Add(column);
}
int startIndex = builder.FieldNamesInFirstString ? 1 : 0;
// cast types of fields if needed
if (builder.ConvertFieldTypes)
{
int number = lines.Count - startIndex;
DetermineTypes(lines.GetRange(startIndex, number), table, numberInfo, currencyInfo, dateTimeInfo);
}
// add table rows
for (int i = startIndex; i < lines.Count; i++)
{
if (lines[i].Length > 0)
{
// get values from the string
fields = lines[i];
// add a new row
DataRow row = table.NewRow();
int valuesCount = fields.Length < table.Columns.Count ? fields.Length : table.Columns.Count;
for (int j = 0; j < valuesCount; j++)
{
string value = fields[j];
if (!String.IsNullOrEmpty(value))
{
if (table.Columns[j].DataType == typeof(String))
{
row[j] = value;
}
else if (table.Columns[j].DataType == typeof(Int32))
{
row[j] = Int32.Parse(value);
}
else if (table.Columns[j].DataType == typeof(Decimal))
{
row[j] = Decimal.Parse(value, NumberStyles.Currency, currencyInfo);
}
else if (table.Columns[j].DataType == typeof(Double))
{
row[j] = Double.Parse(value, NumberStyles.Number, numberInfo);
}
else if (table.Columns[j].DataType == typeof(DateTime))
{
row[j] = DateTime.Parse(value, dateTimeInfo);
}
}
}
table.Rows.Add(row);
}
}
return(table);
}
