/// <summary> /// Разбирает CSV строку /// </summary> /// <param name="text">Текст в формате CSV</param> /// <param name="quotesType">Типы ковычек</param> /// <returns>Массив значений</returns> public static string[] Parse(string text, CsvQuotesType quotesType = CsvQuotesType.DoubleQuotes) { if (string.IsNullOrEmpty(text)) { throw new ArgumentNullException(); } List <string> str = new List <string>(); Match matchResults; switch (quotesType) { case CsvQuotesType.DoubleQuotes: matchResults = parserDoubleQuotes.Match(text); break; case CsvQuotesType.SingleQuotes: matchResults = parserSingleQuotes.Match(text); break; default: throw new ArgumentOutOfRangeException("quotesType"); } while (matchResults.Success) { if (!string.IsNullOrEmpty(matchResults.Value)) { if (quotesType == CsvQuotesType.DoubleQuotes && matchResults.Groups[1].Value.StartsWith("\"")) { str.Add(matchResults.Groups[1].Value.Substring(1, matchResults.Groups[1].Value.Length - 2).Replace("\"\"", "\"")); } else if (quotesType == CsvQuotesType.SingleQuotes && matchResults.Groups[1].Value.StartsWith("'")) { str.Add(matchResults.Groups[1].Value.Substring(1, matchResults.Groups[1].Value.Length - 2).Replace("''", "'")); } else { str.Add(matchResults.Groups[1].Value); } } matchResults = matchResults.NextMatch(); } return(str.ToArray()); }
/// <summary> /// Разбирает CSV строку /// </summary> /// <param name="text">Текст в формате CSV</param> /// <param name="quotesType">Типы ковычек</param> /// <returns>Массив значений</returns> public static string[] Parse(string text, CsvQuotesType quotesType = CsvQuotesType.DoubleQuotes) { if (string.IsNullOrEmpty(text)) { throw new ArgumentNullException(); } List<string> str = new List<string>(); Match matchResults; switch (quotesType) { case CsvQuotesType.DoubleQuotes: matchResults = parserDoubleQuotes.Match(text); break; case CsvQuotesType.SingleQuotes: matchResults = parserSingleQuotes.Match(text); break; default: throw new ArgumentOutOfRangeException("quotesType"); } while (matchResults.Success) { if (!string.IsNullOrEmpty(matchResults.Value)) { if (quotesType == CsvQuotesType.DoubleQuotes && matchResults.Groups[1].Value.StartsWith("\"")) { str.Add(matchResults.Groups[1].Value.Substring(1, matchResults.Groups[1].Value.Length - 2).Replace("\"\"", "\"")); } else if (quotesType == CsvQuotesType.SingleQuotes && matchResults.Groups[1].Value.StartsWith("'")) { str.Add(matchResults.Groups[1].Value.Substring(1, matchResults.Groups[1].Value.Length - 2).Replace("''", "'")); } else { str.Add(matchResults.Groups[1].Value); } } matchResults = matchResults.NextMatch(); } return str.ToArray(); }
/// <summary> /// Remove from the table each row, using the equality condition /// </summary> /// <param name="table">Table name</param> /// <param name="stream">Stream with CSV data</param> /// <param name="keyColumn">Equality column in CSV file</param> /// <param name="modulator">Converting keyColumn value to some types</param> /// <param name="csvQuotesType">Quotes type</param> /// <example>UnloadCsv("someTable", stream, "ID", x => (int)x)</example> public void UnloadCsv(string table, StreamReader stream, string keyColumn, Func <string, object> modulator = null, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { throw new NotImplementedException(); }
/// <summary> /// Загружает поток в базу. Данные в формате CSV /// </summary> /// <param name="tableName">Таблица для загрузки</param> /// <param name="stream">Входной поток</param> /// <param name="modulator">Преобразования</param> /// <param name="csvQuotesType">Тип кавычек CSV</param> /// <param name="identity">true - отключать идентити спецификацию</param> public void LoadCsv(string tableName, StreamReader stream, Dictionary <string, Func <string, object> > modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes, bool identity = false) { DateTime begin = DateTime.Now; try { log.Info("Страт импорта данных в таблицу {0}", tableName); var firstLine = stream.ReadLine(); if (string.IsNullOrEmpty(firstLine)) { throw new InvalidDataException(); } connection.Open(); using (IDbCommand cmd = connection.CreateCommand()) { if (identity) { EnableIdentityInsert(tableName, cmd); } var headers = CsvParser.Parse(firstLine, csvQuotesType); cmd.CommandText = razor.Parse( TemplateManager.GetInsertRowsStreamTemplate(), new object[] { tableName, headers }, "insert rows stream").Replace("\r", string.Empty).Replace("\n", string.Empty); var dataParams = new IDbDataParameter[headers.Length]; for (int i = 0; i < dataParams.Length; i++) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = GetParameterName(headers, i); parameter.DbType = DbType.String; dataParams[i] = parameter; cmd.Parameters.Add(parameter); } for (var line = stream.ReadLine(); line != null; line = stream.ReadLine()) { var lineValues = CsvParser.Parse(line, csvQuotesType); if (lineValues.Length != dataParams.Length) { throw new InvalidDataException(); } for (int i = 0; i < lineValues.Length; i++) { if (modulator != null && modulator.ContainsKey(headers[i])) { dataParams[i].Value = modulator[headers[i]](lineValues[i]); } else { dataParams[i].Value = lineValues[i]; } } cmd.ExecuteNonQuery(); } if (identity) { DisableIdentityInsert(tableName, cmd); } } log.Info("Импорт завершен за {0}", DateTime.Now - begin); } finally { if (connection != null) { connection.Close(); } } }
/// <summary> /// Remove from the table each row, using the equality condition /// </summary> /// <param name="table">Table name</param> /// <param name="stream">Stream with CSV data</param> /// <param name="keyColumn">Equality column in CSV file</param> /// <param name="modulator">Converting keyColumn value to some types</param> /// <param name="csvQuotesType">Quotes type</param> /// <example>UnloadCsv("someTable", stream, "ID", x => (int)x)</example> public void UnloadCsv(string table, StreamReader stream, string keyColumn, Func<string, object> modulator = null, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { throw new NotImplementedException(); }
/// <summary> /// ��������� ����� � ����. ������ � ������� CSV /// </summary> /// <param name="tableName">������� ��� ��������</param> /// <param name="stream">������� �����</param> /// <param name="modulator">��������������</param> /// <param name="csvQuotesType">��� ������� CSV</param> /// <param name="identity">true - ��������� �������� ������������</param> public void LoadCsv(string tableName, StreamReader stream, Dictionary<string, Func<string, object>> modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes, bool identity = false) { DateTime begin = DateTime.Now; try { log.Info("����� ������� ������ � ������� {0}", tableName); var firstLine = stream.ReadLine(); if (string.IsNullOrEmpty(firstLine)) { throw new InvalidDataException(); } connection.Open(); using (IDbCommand cmd = connection.CreateCommand()) { if (identity) { EnableIdentityInsert(tableName, cmd); } var headers = CsvParser.Parse(firstLine, csvQuotesType); cmd.CommandText = razor.Parse( TemplateManager.GetInsertRowsStreamTemplate(), new object[] { tableName, headers }, "insert rows stream").Replace("\r", string.Empty).Replace("\n", string.Empty); var dataParams = new IDbDataParameter[headers.Length]; for (int i = 0; i < dataParams.Length; i++) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = GetParameterName(headers, i); parameter.DbType = DbType.String; dataParams[i] = parameter; cmd.Parameters.Add(parameter); } for (var line = stream.ReadLine(); line != null; line = stream.ReadLine()) { var lineValues = CsvParser.Parse(line, csvQuotesType); if (lineValues.Length != dataParams.Length) { throw new InvalidDataException(); } for (int i = 0; i < lineValues.Length; i++) { if (modulator != null && modulator.ContainsKey(headers[i])) { dataParams[i].Value = modulator[headers[i]](lineValues[i]); } else { dataParams[i].Value = lineValues[i]; } } cmd.ExecuteNonQuery(); } if (identity) { DisableIdentityInsert(tableName, cmd); } } log.Info("������ �������� �� {0}", DateTime.Now - begin); } finally { if (connection != null) { connection.Close(); } } }
/// <summary> /// Загружает поток в базу. Данные в формате CSV /// </summary> /// <param name="tableName">Таблица для загрузки</param> /// <param name="resource">Имя ресурса</param> /// <param name="modulator">Преобразования</param> /// <param name="csvQuotesType">Тип кавычек CSV</param> public void LoadCsv(string tableName, string resource, Dictionary <string, Func <string, object> > modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { using (var stream = GetType().Assembly.GetManifestResourceStream(resource)) { LoadCsv(tableName, new StreamReader(stream, Encoding.Default), modulator, csvQuotesType); } }
/// <summary> /// Remove from the table each row, using the equality condition /// </summary> /// <param name="table">Table name</param> /// <param name="stream">Stream with CSV data</param> /// <param name="keyColumn">Equality column in CSV file</param> /// <param name="modulator">Converting keyColumn value to some types</param> /// <param name="csvQuotesType">Quotes type</param> /// <example>UnloadCsv("someTable", stream, "ID", x => (int)x)</example> public void UnloadCsv(string table, StreamReader stream, string keyColumn, Func <string, object> modulator = null, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { ObjectFactory.GetInstance <IDatabaseStrategy>().UnloadCsv(table, stream, keyColumn, modulator, csvQuotesType); }
/// <summary> /// Загружает поток в базу. Данные в формате CSV /// </summary> /// <param name="tableName">Таблица для загрузки</param> /// <param name="stream">Входной поток</param> /// <param name="modulator">Преобразования</param> /// <param name="csvQuotesType">Тип кавычек CSV</param> /// <param name="identity">true - отключать идентити спецификацию</param> public void LoadCsv(string tableName, StreamReader stream, Dictionary <string, Func <string, object> > modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes, bool identity = false) { ObjectFactory.GetInstance <IDatabaseStrategy>().LoadCsv(tableName, stream, modulator, csvQuotesType, identity); }
/// <summary> /// Remove from the table each row, using the equality condition /// </summary> /// <param name="table">Table name</param> /// <param name="stream">Stream with CSV data</param> /// <param name="keyColumn">Equality column in CSV file</param> /// <param name="modulator">Converting keyColumn value to some types</param> /// <param name="csvQuotesType">Quotes type</param> /// <example>UnloadCsv("someTable", stream, "ID", x => (int)x)</example> public void UnloadCsv(string table, StreamReader stream, string keyColumn, Func<string, object> modulator = null, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { try { strategy.UnloadCsv(table, stream, keyColumn, modulator, csvQuotesType); } catch { } }
/// <summary> /// Загружает поток в базу. Данные в формате CSV /// </summary> /// <param name="tableName">Таблица для загрузки</param> /// <param name="stream">Входной поток</param> /// <param name="modulator">Преобразования</param> /// <param name="csvQuotesType">Тип кавычек CSV</param> /// <param name="identity">true - отключать идентити спецификацию</param> public void LoadCsv(string tableName, StreamReader stream, Dictionary<string, Func<string, object>> modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes, bool identity = false) { try { strategy.LoadCsv(tableName, stream, modulator, csvQuotesType); } catch { } }
/// <summary> /// Remove from the table each row, using the equality condition /// </summary> /// <param name="table">Table name</param> /// <param name="stream">Stream with CSV data</param> /// <param name="keyColumn">Equality column in CSV file</param> /// <param name="modulator">Converting keyColumn value to some types</param> /// <param name="csvQuotesType">Quotes type</param> /// <example>UnloadCsv("someTable", stream, "ID", x => (int)x)</example> public void UnloadCsv(string table, StreamReader stream, string keyColumn, Func <string, object> modulator = null, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes) { try { strategy.UnloadCsv(table, stream, keyColumn, modulator, csvQuotesType); } catch { } }
/// <summary> /// Загружает поток в базу. Данные в формате CSV /// </summary> /// <param name="tableName">Таблица для загрузки</param> /// <param name="stream">Входной поток</param> /// <param name="modulator">Преобразования</param> /// <param name="csvQuotesType">Тип кавычек CSV</param> /// <param name="identity">true - отключать идентити спецификацию</param> public void LoadCsv(string tableName, StreamReader stream, Dictionary <string, Func <string, object> > modulator, CsvQuotesType csvQuotesType = CsvQuotesType.DoubleQuotes, bool identity = false) { try { strategy.LoadCsv(tableName, stream, modulator, csvQuotesType); } catch { } }