public void GenericExportCSV()
{
string sql = ConfigurationManager.ConnectionStrings["odbc"].ConnectionString;
OdbcConnection cn = null;
try
{
using (cn = new OdbcConnection(sql))
{
cn.Open();
DataSet CustomersDataSet = new DataSet();
using (OdbcDataAdapter da_local = new OdbcDataAdapter(String.Format("SELECT * FROM RREV.{0}", Table), cn))
{
using (OdbcCommandBuilder cmdBuilder = new OdbcCommandBuilder(da_local))
{
OdbcCommand schemaCommand = new OdbcCommand(String.Format("SELECT * FROM RREV.{0} WHERE 1=0", Table), cn);
OdbcDataReader reader = schemaCommand.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();
FixedLengthClassBuilder cb = new FixedLengthClassBuilder(Table);
//cb.IgnoreFirstLines = 1;
cb.IgnoreEmptyLines = true;
//// populate the fields based on the columns
foreach (DataRow row in schemaTable.Rows)
{
string fieldName = row.Field<string>("ColumnName");
Type fieldType = row.Field<Type>("DataType");
int fieldLength = row.Field<int>("ColumnSize");
FixedFieldBuilder fb = cb.AddField(fieldName, fieldLength, fieldType);
fb.FieldOptional = row.Field<bool>("AllowDBNull");
int NumericPrecision = row.Field<int>("NumericPrecision");
//Converterb
//ConverterBuilder cb = new ConverterBuilder();
//fb.Converter = new MoneyConverter(NumericPrecision);
//fb.FieldIndex = row.Field<int>("ColumnOrdinal");
//fb.Converter =
//- Results View Expanding the Results View will enumerate the IEnumerable
//+ [0] {ColumnName} object {System.Data.DataColumn}
//+ [1] {ColumnOrdinal} object {System.Data.DataColumn}
//+ [2] {ColumnSize} object {System.Data.DataColumn}
//+ [3] {NumericPrecision} object {System.Data.DataColumn}
//+ [4] {NumericScale} object {System.Data.DataColumn}
//+ [5] {DataType} object {System.Data.DataColumn}
//+ [6] {ProviderType} object {System.Data.DataColumn}
//+ [7] {IsLong} object {System.Data.DataColumn}
//+ [8] {AllowDBNull} object {System.Data.DataColumn}
//+ [9] {IsReadOnly} object {System.Data.DataColumn}
//+ [10] {IsRowVersion} object {System.Data.DataColumn}
//+ [11] {IsUnique} object {System.Data.DataColumn}
//+ [12] {IsKey} object {System.Data.DataColumn}
//+ [13] {IsAutoIncrement} object {System.Data.DataColumn}
//+ [14] {BaseSchemaName} object {System.Data.DataColumn}
//+ [15] {BaseCatalogName} object {System.Data.DataColumn}
//+ [16] {BaseTableName} object {System.Data.DataColumn}
//+ [17] {BaseColumnName} object {System.Data.DataColumn}
//+ Constraints {System.Data.ConstraintCollection} System.Data.ConstraintCollection
}
reader.Close();
string classFileName = String.Format("e:\\temp\\db_dump\\{0}.xml", Table);
cb.SaveToXml(classFileName);
da_local.Fill(CustomersDataSet, Table);
DataTable dt = CustomersDataSet.Tables[Table];
//http://stackoverflow.com/questions/8725225/how-to-use-a-dynamic-csv-delimiter-with-filehelpers
log4net.LogManager.GetLogger(this.GetType()).InfoFormat("{0} num rows: {1}", Table, dt.Rows.Count);
CsvEngine.DataTableToCsv(dt, Path.Combine("e:\\temp\\db_dump\\", Table));
//DataTable schemaTable = reader.GetSchemaTable();
cn.Close();
//// create the FileHelpers record class
//// alternatively there is a 'FixedClassBuilder'
//DelimitedClassBuilder cb = new DelimitedClassBuilder("UnitTask", ",");
//cb.IgnoreFirstLines = 1;
//cb.IgnoreEmptyLines = true;
//// populate the fields based on the columns
//foreach (DataRow row in schemaTable.Rows)
//{
// cb.AddField(row.Field<string>("ColumnName"), row.Field<Type>("DataType"));
// cb.LastField.TrimMode = TrimMode.Both;
//}
//reader.Close();
//// load the dynamically created class into a FileHelpers engine
//FileHelperEngine engine = new FileHelperEngine(cb.CreateRecordClass());
}
}
}
}
catch (Exception ex)
{
}
finally
{
if (cn != null && cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
static void RunTimeRecordsFixed()
{
Console.WriteLine("Run Time Records now =) ...");
Console.WriteLine();
FixedLengthClassBuilder cb = new FixedLengthClassBuilder("Customer", FixedMode.ExactLength);
cb.AddField("CustId", 5, typeof(Int32));
cb.AddField("Name", 20, typeof(string));
cb.AddField("Balance", 8, typeof(Decimal));
cb.AddField("AddedDate", 8, typeof(DateTime));
cb.LastField.Converter.Kind = ConverterKind.Date;
cb.LastField.Converter.Arg1 = "ddMMyyyy";
// Estas dos lineas son el uso de la librería
FileHelperEngine engine = new FileHelperEngine(cb.CreateRecordClass());
DataTable dt = engine.ReadFileAsDT(@"..\Data\CustomersFixedLength.txt");
// Aqui es donde ustedes agregan su código
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("Customer: " + dr[0].ToString() + " - " + dr[1].ToString());
}
}
/// <summary>
/// Loads the XML representation of a a ClassBuilder inheritor and return it. (for XML saved with SaveToXml method)
/// </summary>
/// <remarks>
/// ClassBuilder inheritors: <see cref="DelimitedClassBuilder"/> or <see cref="FixedLengthClassBuilder"/>
/// </remarks>
/// <param name="document">The XML document with the representation of the record class.</param>
/// <returns>A new instance of a ClassBuilder inheritor: <see cref="DelimitedClassBuilder"/> or <see cref="FixedLengthClassBuilder"/> </returns>
public static ClassBuilder LoadFromXml(XmlDocument document)
{
ClassBuilder res = null;
string classtype = document.DocumentElement.LocalName;
if (classtype == "DelimitedClass")
{
res = DelimitedClassBuilder.LoadXmlInternal(document);
}
else
{
res = FixedLengthClassBuilder.LoadXmlInternal(document);
}
XmlNode node = document.DocumentElement["IgnoreLastLines"];
if (node != null)
{
res.IgnoreLastLines = int.Parse(node.InnerText);
}
node = document.DocumentElement["IgnoreFirstLines"];
if (node != null)
{
res.IgnoreFirstLines = int.Parse(node.InnerText);
}
node = document.DocumentElement["IgnoreEmptyLines"];
if (node != null)
{
res.IgnoreEmptyLines = true;
}
node = document.DocumentElement["CommentMarker"];
if (node != null)
{
res.IgnoreCommentedLines.CommentMarker = node.InnerText;
}
node = document.DocumentElement["CommentInAnyPlace"];
if (node != null)
{
res.IgnoreCommentedLines.InAnyPlace = bool.Parse(node.InnerText.ToLower());
}
node = document.DocumentElement["SealedClass"];
res.SealedClass = node != null;
node = document.DocumentElement["Namespace"];
if (node != null)
{
res.Namespace = node.InnerText;
}
node = document.DocumentElement["Visibility"];
if (node != null)
{
res.Visibility = (NetVisibility)Enum.Parse(typeof(NetVisibility), node.InnerText);
}
;
node = document.DocumentElement["RecordCondition"];
if (node != null)
{
res.RecordCondition.Condition = (RecordCondition)Enum.Parse(typeof(RecordCondition), node.InnerText);
}
;
node = document.DocumentElement["RecordConditionSelector"];
if (node != null)
{
res.RecordCondition.Selector = node.InnerText;
}
res.ReadClassElements(document);
node = document.DocumentElement["Fields"];
XmlNodeList nodes;
if (classtype == "DelimitedClass")
{
nodes = node.SelectNodes("/DelimitedClass/Fields/Field");
}
else
{
nodes = node.SelectNodes("/FixedLengthClass/Fields/Field");
}
foreach (XmlNode n in nodes)
{
res.ReadField(n);
}
return(res);
}
public void SaveLoadXmlFileFixed2()
{
FixedLengthClassBuilder cb = new FixedLengthClassBuilder("Customers");
cb.AddField("Field1", 8, typeof(DateTime));
cb.LastField.Converter.Kind = ConverterKind.Date;
cb.LastField.Converter.Arg1 = "ddMMyyyy";
cb.LastField.FieldNullValue = DateTime.Now;
cb.AddField("FieldSecond", 3, typeof(string));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = ' ';
cb.AddField("Field33", 3, typeof(int));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = '0';
cb.LastField.TrimMode = TrimMode.Both;
cb.SaveToXml(@"runtime.xml");
engine = new FileHelperEngine(ClassBuilder.ClassFromXmlFile("runtime.xml"));
Assert.AreEqual("Customers", engine.RecordType.Name);
Assert.AreEqual(3, engine.RecordType.GetFields().Length);
Assert.AreEqual("Field1", engine.RecordType.GetFields()[0].Name);
}
public void SaveLoadXmlFileFixed()
{
FixedLengthClassBuilder cb = new FixedLengthClassBuilder("Customers");
cb.FixedMode = FixedMode.ExactLength;
cb.AddField("Field1", 8, typeof(DateTime));
cb.LastField.Converter.Kind = ConverterKind.Date;
cb.LastField.Converter.Arg1 = "ddMMyyyy";
cb.LastField.FieldNullValue = DateTime.Now;
cb.AddField("FieldSecond", 3, typeof(string));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = ' ';
cb.AddField("Field33", 3, typeof(int));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = '0';
cb.LastField.TrimMode = TrimMode.Both;
cb.SaveToXml(@"runtime.xml");
FixedLengthClassBuilder loaded = (FixedLengthClassBuilder) ClassBuilder.LoadFromXml(@"runtime.xml");
Assert.AreEqual("Field1", loaded.FieldByIndex(0).FieldName);
Assert.AreEqual("FieldSecond", loaded.FieldByIndex(1).FieldName);
Assert.AreEqual("Field33", loaded.FieldByIndex(2).FieldName);
Assert.AreEqual("System.DateTime", loaded.FieldByIndex(0).FieldType);
Assert.AreEqual("System.String", loaded.FieldByIndex(1).FieldType);
Assert.AreEqual("System.Int32", loaded.FieldByIndex(2).FieldType);
Assert.AreEqual(ConverterKind.Date, loaded.FieldByIndex(0).Converter.Kind);
Assert.AreEqual("ddMMyyyy", loaded.FieldByIndex(0).Converter.Arg1);
Assert.AreEqual(AlignMode.Right, loaded.FieldByIndex(1).AlignMode);
Assert.AreEqual(' ', loaded.FieldByIndex(1).AlignChar);
Assert.AreEqual(FixedMode.ExactLength, loaded.FixedMode);
}
public ClassBuilder CommonCreate()
{
FixedLengthClassBuilder cb = new FixedLengthClassBuilder("Customers");
cb.AddField("Field1", 8, typeof(DateTime));
cb.LastField.Converter.Kind = ConverterKind.Date;
cb.LastField.Converter.Arg1 = "ddMMyyyy";
cb.LastField.FieldNullValue = DateTime.Now;
cb.AddField("Field2", 3, typeof(string));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = ' ';
cb.AddField("Field3", 3, typeof(int));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = '0';
cb.LastField.TrimMode = TrimMode.Both;
return cb;
}
public void FullClassBuildingFixed()
{
FixedLengthClassBuilder cb = new FixedLengthClassBuilder("Customers");
cb.AddField("Field1", 8, typeof(DateTime));
cb.LastField.Converter.Kind = ConverterKind.Date;
cb.LastField.Converter.Arg1 = "ddMMyyyy";
cb.LastField.FieldNullValue = DateTime.Now;
cb.AddField("Field2", 3, typeof(string));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = ' ';
cb.AddField("Field3", 3, typeof(int));
cb.LastField.AlignMode = AlignMode.Right;
cb.LastField.AlignChar = '0';
cb.LastField.TrimMode = TrimMode.Both;
engine = new FileHelperEngine(cb.CreateRecordClass());
DataTable dt = engine.ReadFileAsDT(Common.TestPath(@"Good\test1.txt"));
Assert.AreEqual(4, dt.Rows.Count);
Assert.AreEqual(4, engine.TotalRecords);
Assert.AreEqual(0, engine.ErrorManager.ErrorCount);
}
