.NET Development: marzo 2015

jueves, 26 de marzo de 2015

CheckListBox & List & Classes

public class Tax
{
public string Description { get; set; }
public bool Included { get; set; }
public Tax(string descrip, bool incl)
{
Description = descrip;
Included = incl;
}
}

private void btLoadCheckListBox_Click(object sender, EventArgs e)
{
List<Tax> lstTaxes = new List<Tax>();

lstTaxes.Add(new Tax("Uno", true));
lstTaxes.Add(new Tax("Dos", false));
lstTaxes.Add(new Tax("Tres", true));

int c = 0;

foreach (var tx in lstTaxes)
{

checkedListBox1.Items.Add(tx.Description);
checkedListBox1.SetItemChecked(c, tx.Included);
c++;
}
}

private void btLoadList_Click(object sender, EventArgs e)
{
List<Tax> lstTaxes = new List<Tax>();

for (int i = 0; i < checkedListBox1.Items.Count; i++)
{
if (checkedListBox1.GetItemChecked(i))
lstTaxes.Add(new Tax((string)checkedListBox1.Items[i], true));
else
lstTaxes.Add(new Tax((string)checkedListBox1.Items[i], false));
}
}

sábado, 21 de marzo de 2015

Extending TextBox component with money format

using System;
using System.Windows.Forms;
using System.ComponentModel;
using System.Drawing;
using System.Globalization;

namespace ClientCtrlComponents
{
public partial class EditMoney : TextBox
{
#region Member Variables

Color originalbgColor = Color.White;
Color lostfocusbgColor = Color.Ivory;
Color watermarkColor = Color.Gray;
Color forecolor;
Font font;

#endregion

#region Constructor

public EditMoney()
{
this.originalbgColor = this.FocusOnColor;
this.lostfocusbgColor = this.FocusLostColor;
this.forecolor = this.ForeColor;
this.ForeColor = this.watermarkColor;
this.font = this.Font;

this.KeyPress += new KeyPressEventHandler(EditMoney_KeyPress);
this.GotFocus += new EventHandler(EditMoney_GotFocus);
this.Leave += new EventHandler(EditMoney_Leave);
}

#endregion

#region Event Handler Methods

private void EditMoney_GotFocus(object sender, EventArgs e)
{
FocusColor(true);
}

private void EditMoney_Leave(object sender, EventArgs e)
{
decimal d;
FocusColor(false);
if (decimal.TryParse(((TextBox)sender).Text, out d))
((TextBox)sender).Text = d.ToString();
}

private void FocusColor(bool focus)
{
this.BackColor = (focus && this.Focus() ? this.originalbgColor : this.lostfocusbgColor);
}

void EditMoney_KeyPress(object sender, KeyPressEventArgs e)
{
NumberFormatInfo numberFormatInfo =
System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
string decimalSeparator = numberFormatInfo.NumberDecimalSeparator;
string groupSeparator = numberFormatInfo.NumberGroupSeparator;
string negativeSign = numberFormatInfo.NegativeSign;

e.Handled =
!(e.KeyChar.Equals((char)8)) &&
!Char.IsDigit(e.KeyChar) &&
!(e.KeyChar == (char)46 && (((TextBox)sender).Text.IndexOf(decimalSeparator) < 0));

try
{
if (((TextBox)sender).Text.Length == 0 && e.KeyChar.Equals((char)48)) //0
{
e.Handled = true;
}
else
if (((TextBox)sender).Text.Length == 0 && e.KeyChar.ToString().Equals(decimalSeparator)) //.
{
((TextBox)sender).Text = ((char)48).ToString() + decimalSeparator;
((TextBox)sender).Select(((TextBox)sender).Text.Length, 0);
e.Handled = true;
}
else
if (!(e.KeyChar.Equals((char)8)) &&
(((TextBox)sender).Text.Substring(((TextBox)sender).Text.IndexOf(decimalSeparator)).Length >= 3))
{
e.Handled = true;
}
}
catch { }
}

#endregion

#region User Defined Properties

/// <summary>
/// Property to set/get background color at focus on at design/runtime
/// </summary>
[Browsable(true)]
[Category("Extended Properties")]
[Description("Sets background color on focus")]
[DisplayName("FocusOnColor")]
public Color FocusOnColor
{
get
{
return this.originalbgColor;
}
set
{
this.originalbgColor = value;
base.OnGotFocus(new EventArgs());
}
}

/// <summary>
/// Property to set/get background color at focus lost at design/runtime
/// </summary>
[Browsable(true)]
[Category("Extended Properties")]
[Description("Sets background lost focus color")]
[DisplayName("FocusLostColor")]
public Color FocusLostColor
{
get
{
return this.lostfocusbgColor;
}
set
{
this.lostfocusbgColor = value;
base.OnLeave(new EventArgs());
}
}

#endregion
}
}

viernes, 20 de marzo de 2015

Textbox Only Money Values

private void EditMoney_Leave(object sender, EventArgs e)
{
  decimal d;
if (decimal.TryParse(((TextBox)sender).Text, out d))
      ((TextBox)sender).Text = d.ToString();
}

private void textBox_KeyPress(object sender, KeyPressEventArgs e)
{
  //Only basckspace, numbers and decimal dot
  NumberFormatInfo numberFormatInfo = System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
string decimalSeparator = numberFormatInfo.NumberDecimalSeparator;
string groupSeparator = numberFormatInfo.NumberGroupSeparator;
  string negativeSign = numberFormatInfo.NegativeSign;

e.Handled =
!(e.KeyChar.Equals((char)8)) &&
!Char.IsDigit(e.KeyChar) &&
!(e.KeyChar == (char)46 && (((TextBox)sender).Text.IndexOf(decimalSeparator) < 0));

try
{
if (((TextBox)sender).Text.Length == 0 && e.KeyChar.Equals((char)48)) //0
{
e.Handled = true;
}
  else
if (((TextBox)sender).Text.Length == 0 && e.KeyChar.ToString().Equals(decimalSeparator)) //.
{
((TextBox)sender).Text = ((char)48).ToString() + decimalSeparator;
((TextBox)sender).Select(((TextBox)sender).Text.Length, 0);
e.Handled = true;
}
  else
if (!(e.KeyChar.Equals((char)8)) &&
(((TextBox)sender).Text.Substring(((TextBox)sender).Text.IndexOf(decimalSeparator)).Length >= 3))
{
e.Handled = true;
}
}

catch { }
}

INTERFACE:

private void InitializeComponent()
{
this.textBox1 = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// textBox1
//
this.textBox1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.textBox1.Font = new System.Drawing.Font("Microsoft Sans Serif", 20F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.textBox1.Location = new System.Drawing.Point(47, 45);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(251, 38);
this.textBox1.TabIndex = 0;
this.textBox1.TextAlign = System.Windows.Forms.HorizontalAlignment.Right;
this.textBox1.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.textBox1_KeyPress);
//
// label1
//
this.label1.Location = new System.Drawing.Point(49, 47);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(26, 34);
this.label1.TabIndex = 1;
this.label1.Text = "$";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(11F, 24F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(347, 145);
this.Controls.Add(this.label1);
this.Controls.Add(this.textBox1);
this.Font = new System.Drawing.Font("Microsoft Sans Serif", 14F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Margin = new System.Windows.Forms.Padding(6);
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Form1";
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
this.Load += new System.EventHandler(this.Form1_Load);
this.Shown += new System.EventHandler(this.Form1_Shown);
this.ResizeEnd += new System.EventHandler(this.Form1_ResizeEnd);
this.ResumeLayout(false);
this.PerformLayout();
}

martes, 10 de marzo de 2015

Upload Txt file to SQL DataTable, Convert to HTML file & download it.

IMPORTING PROCESS

Original file:

Table:

Import file method:

public static String[] SaveDataFromFileEPA(string row, string filename)
{
String[] lstInfo = new String[Common.Values.len];
DateTime ini = DateTime.Now;
lstInfo[0] = ini.ToString(Common.Values.date);
DateTime end = DateTime.Now;

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[Common.StringsValues.connection].ConnectionString.ToString()))
{
try
{
SqlCommand cmd = new SqlCommand(Common.StringsValues.storedprocedure, conn);
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;

string s = string.Empty;

cmd.Parameters.Add(Common.ColumnName.loadid, SqlDbType.VarChar);
cmd.Parameters[Common.ColumnName.loadid].Value = filename;

cmd.Parameters.Add(Common.ColumnName.c001, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c001].Value = s;
cmd.Parameters.Add(Common.ColumnName.c002, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c002].Value = s;
cmd.Parameters.Add(Common.ColumnName.c003, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c003].Value = s;
cmd.Parameters.Add(Common.ColumnName.c004, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c004].Value = s;
cmd.Parameters.Add(Common.ColumnName.c005, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c005].Value = s;
cmd.Parameters.Add(Common.ColumnName.c006, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c006].Value = s;
cmd.Parameters.Add(Common.ColumnName.c007, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c007].Value = s;
cmd.Parameters.Add(Common.ColumnName.c008, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c008].Value = s;
cmd.Parameters.Add(Common.ColumnName.c009, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c009].Value = s;
cmd.Parameters.Add(Common.ColumnName.c010, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c010].Value = s;
cmd.Parameters.Add(Common.ColumnName.c011, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c011].Value = s;
cmd.Parameters.Add(Common.ColumnName.c012, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c012].Value = s;
cmd.Parameters.Add(Common.ColumnName.c013, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c013].Value = s;
cmd.Parameters.Add(Common.ColumnName.c014, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c014].Value = s;
cmd.Parameters.Add(Common.ColumnName.c015, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c015].Value = s;
cmd.Parameters.Add(Common.ColumnName.c016, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c016].Value = s;
cmd.Parameters.Add(Common.ColumnName.c017, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c017].Value = s;
cmd.Parameters.Add(Common.ColumnName.c018, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c018].Value = s;
cmd.Parameters.Add(Common.ColumnName.c019, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c019].Value = s;
cmd.Parameters.Add(Common.ColumnName.c020, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c020].Value = s;
cmd.Parameters.Add(Common.ColumnName.c021, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c021].Value = s;
cmd.Parameters.Add(Common.ColumnName.c022, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c022].Value = s;
cmd.Parameters.Add(Common.ColumnName.c023, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c023].Value = s;
cmd.Parameters.Add(Common.ColumnName.c024, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c024].Value = s;
cmd.Parameters.Add(Common.ColumnName.c025, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c025].Value = s;
cmd.Parameters.Add(Common.ColumnName.c026, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c026].Value = s;
cmd.Parameters.Add(Common.ColumnName.c027, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c027].Value = s;
cmd.Parameters.Add(Common.ColumnName.c028, SqlDbType.VarChar); cmd.Parameters[Common.ColumnName.c028].Value = s;

string[] records = row.Split(Common.Values.separator);
int c = 1;

foreach (string str in records)
{
switch (c)
{
case 1: cmd.Parameters[Common.ColumnName.c001].Value = str; break;
case 2: cmd.Parameters[Common.ColumnName.c002].Value = str; break;
case 3: cmd.Parameters[Common.ColumnName.c003].Value = str; break;
case 4: cmd.Parameters[Common.ColumnName.c004].Value = str; break;
case 5: cmd.Parameters[Common.ColumnName.c005].Value = str; break;
case 6: cmd.Parameters[Common.ColumnName.c006].Value = str; break;
case 7: cmd.Parameters[Common.ColumnName.c007].Value = str; break;
case 8: cmd.Parameters[Common.ColumnName.c008].Value = str; break;
case 9: cmd.Parameters[Common.ColumnName.c009].Value = str; break;
case 10: cmd.Parameters[Common.ColumnName.c010].Value = str; break;
case 11: cmd.Parameters[Common.ColumnName.c011].Value = str; break;
case 12: cmd.Parameters[Common.ColumnName.c012].Value = str; break;
case 13: cmd.Parameters[Common.ColumnName.c013].Value = str; break;
case 14: cmd.Parameters[Common.ColumnName.c014].Value = str; break;
case 15: cmd.Parameters[Common.ColumnName.c015].Value = str; break;
case 16: cmd.Parameters[Common.ColumnName.c016].Value = str; break;
case 17: cmd.Parameters[Common.ColumnName.c017].Value = str; break;
case 18: cmd.Parameters[Common.ColumnName.c018].Value = str; break;
case 19: cmd.Parameters[Common.ColumnName.c019].Value = str; break;
case 20: cmd.Parameters[Common.ColumnName.c020].Value = str; break;
case 21: cmd.Parameters[Common.ColumnName.c021].Value = str; break;
case 22: cmd.Parameters[Common.ColumnName.c022].Value = str; break;
case 23: cmd.Parameters[Common.ColumnName.c023].Value = str; break;
case 24: cmd.Parameters[Common.ColumnName.c024].Value = str; break;
case 25: cmd.Parameters[Common.ColumnName.c025].Value = str; break;
case 26: cmd.Parameters[Common.ColumnName.c026].Value = str; break;
case 27: cmd.Parameters[Common.ColumnName.c027].Value = str; break;
case 28: cmd.Parameters[Common.ColumnName.c028].Value = str; break;
}

c++;
}

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

}
catch (SqlException ex)
{
Utils Mon = new Utils();
Mon.InsertEdenredMXMonitor(ex, Utils.LogMessageIN.DataAccess);
lstInfo[4] = ex.Message;
conn.Close();
}
}

lstInfo[1] = end.ToString(Common.Values.date);
lstInfo[2] = new TimeSpan(end.Ticks - ini.Ticks).ToString();
lstInfo[3] = Common.StringsValues.message;
return lstInfo;

}

Method 2:

public static class StringsValues
{
public static string connection = "Sql";
public static string storedprocedure = "InsertRowFileEPA";
public static string message = "Guardardo de archivo EPA";

}

Stored procedure:

IF OBJECT_ID ('[dbo].[InsertRowFileEPA]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[InsertRowFileEPA];
GO

CREATE PROCEDURE [dbo].[InsertRowFileEPA]
(
@LoadId NVARCHAR(15),
@c001 NVARCHAR(50),
@c002 NVARCHAR(50),
@c003 NVARCHAR(50),
@c004 NVARCHAR(50),
@c005 NVARCHAR(50),
@c006 NVARCHAR(50),
@c007 NVARCHAR(50),
@c008 NVARCHAR(50),
@c009 NVARCHAR(50),
@c010 NVARCHAR(50),
@c011 NVARCHAR(50),
@c012 NVARCHAR(50),
@c013 NVARCHAR(50),
@c014 NVARCHAR(50),
@c015 NVARCHAR(50),
@c016 NVARCHAR(50),
@c017 NVARCHAR(50),
@c018 NVARCHAR(50),
@c019 NVARCHAR(50),
@c020 NVARCHAR(50),
@c021 NVARCHAR(50),
@c022 NVARCHAR(50),
@c023 NVARCHAR(50),
@c024 NVARCHAR(50),
@c025 NVARCHAR(50),
@c026 NVARCHAR(50),
@c027 NVARCHAR(50),
@c028 NVARCHAR(50)
)
AS
BEGIN

SET NOCOUNT ON
BEGIN TRY

BEGIN TRANSACTION cargaRegistroAmex;

INSERT INTO [dbo].[FileEPA] (
LoadId,
c001,
c002,
c003,
c004,
c005,
c006,
c007,
c008,
c009,
c010,
c011,
c012,
c013,
c014,
c015,
c016,
c017,
c018,
c019,
c020,
c021,
c022,
c023,
c024,
c025,
c026,
c027,
c028
)
VALUES (
@LoadId,
@c001,
@c002,
@c003,
@c004,
@c005,
@c006,
@c007,
@c008,
@c009,
@c010,
@c011,
@c012,
@c013,
@c014,
@c015,
@c016,
@c017,
@c018,
@c019,
@c020,
@c021,
@c022,
@c023,
@c024,
@c025,
@c026,
@c027,
@c028
)

COMMIT TRANSACTION cargaRegistroAmex;
RETURN 0;
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN;
ROLLBACK TRANSACTION cargaRegistroAmex;
END;
RAISERROR ('Error guardando registro de archivo AMEX.', -- Mensaje
18, -- Severidad.
1 -- Estado.
);
END CATCH;
SET NOCOUNT OFF

END

GENERATION AND DOWNLOAD PROCESS

Call 1:

CreateVisorEpaArchive(NameFile, date);

//DOWNLOAD FILE PROCESS//

  if (NameFile != string.Empty & NameFile != "No")
  {
//string strURL = ConfigurationManager.AppSettings["PathDownLoad"].ToString() + NameFile;
  string strURL = ConfigurationManager.AppSettings["PathCreateFiles"].ToString() + NameFile;

  WebClient req = new WebClient();
  HttpResponse response = HttpContext.Current.Response;
  response.Clear();
  response.ClearContent();
  response.ClearHeaders();
  response.Buffer = true;
  response.AddHeader("Content-Disposition", "attachment;filename=\"" + NameFile + "\"");

byte[] data = req.DownloadData(strURL);
response.BinaryWrite(data);
response.End();
}

Call 2:

  ProcessFile(_FileName, date);

Method 1:

  private void ProcessFile(string filename, string date)
{
try
{
DirectoryInfo DirInfo = new DirectoryInfo(ConfigurationManager.AppSettings["PathCreateFiles"].ToString());
string[] files = Directory.GetFiles(DirInfo.FullName);

for (int i = 0; i <= files.Count()-1; i++)
{
try
{
if (File.Exists(files[i]) && files[i].Replace(DirInfo.FullName, "").Contains("FILEEPA"))
File.Delete(files[i]);
}
catch (Exception)
{
throw;
}
}

string FileToGenerate = ConfigurationManager.AppSettings["PathCreateFiles"].ToString() + filename.ToString();

CreateDocHTML(FileToGenerate, date);
}
catch { }

}

Method 2:

public void CreateDocHTML(string file, string date)
{
int count = 0;

try
{
count++;

ERMX.MO.LEDGE.SSRS.Data.Data data = new ERMX.MO.LEDGE.SSRS.Data.Data();
List<string> lstRecords = data.GetDataTable("DRP_LEDGEConnectionString", "dbo.GetDataFileEPA", "@InsertionDate|" + date);

System.Text.StringBuilder filecontent = new System.Text.StringBuilder();
System.Text.StringBuilder tmp = new System.Text.StringBuilder();

filecontent.Append("<html><head><title>VISOR EPA - DIA ");
filecontent.Append(DateTime.Now.ToShortDateString());
filecontent.Append("</title></head><body><table id=\"table\" border=1 cellspacing=0 cellpadding=3 bgcolor=#bfdfff bordercolor=#ffffff><thead><tr bgcolor=#dddddd><th title=\"Field #1\">Fila</th><th title=\"Field #2\">Cargado</th><th title=\"Field #3\">Identificador</th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th></tr></thead><tbody>");

foreach (string row in lstRecords)
{
tmp.Clear();
string[] columns = row.Split('|');

int value = 0;

for (int col = 0; col <= columns.Count() - 1; col++)
{
if (col == 8)
int.TryParse(columns[col].Trim(), out value);

tmp.Append("<td nowrap>");
tmp.Append(columns[col].Trim());
tmp.Append("</td>");
}

tmp.Append("</tr>");

switch (value)
{
case 0: tmp.Insert(0, "<tr bgcolor=#aaaaaa>"); break; //gray
case 1: tmp.Insert(0, "<tr bgcolor=#ffaaaa>"); break; //red
case 3: tmp.Insert(0, "<tr bgcolor=#ffffaa>"); break; //yellow
case 4: tmp.Insert(0, "<tr bgcolor=#aaffaa>"); break; //green
case 5: tmp.Insert(0, "<tr bgcolor=#aaffff>"); break; //blue
case 9: tmp.Insert(0, "<tr bgcolor=#aaaaaa>"); break; //gray
}

filecontent.Append(tmp.ToString());
}

filecontent.Append("</table></body></html>");

using (StreamWriter fileReportVisorEpa = new StreamWriter(file, true))
{
fileReportVisorEpa.WriteLine(filecontent);
}
}
catch { }
}

Method 3:

public List<string> GetDataTable(string DataBaseName, string StoredProcedureName, string Params)
{
SqlConnection cnx = new SqlConnection();
cnx.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[DataBaseName].ConnectionString;
SqlCommand cmd = new SqlCommand(StoredProcedureName, cnx);
cmd.CommandType = CommandType.StoredProcedure;
string[] param = Params.Split('|');
for (int i = 0; i < param.Count(); i += 2) cmd.Parameters.Add(new SqlParameter(param[i], param[i + 1]));

try
{
List<string> lstRows = new List<string>();
cnx.Open();
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
lstRows.Add(dr[0].ToString());
}

cnx.Close();

return lstRows;
}
catch (Exception ex)
{

}

return null;
}

Stored Procedure:

  ALTER PROCEDURE [dbo].[GetDataFileEPA]
  @InsertionDate DATETIME
  AS
  BEGIN

  SELECT STR(IdRow) +'|'+ CONVERT(VARCHAR,InsertionDate) +'|'+ LoadId +'|'+ c001 +'|'+ c002 +'|'+ c003 +'|'+ c004 +'|'+ c005 +'|'+ c006 +'|'+ c007 +'|'+ c008 +'|'+ c009 +'|'+ c010 +'|'+ c011 +'|'+ c012 +'|'+ c013 +'|'+ c014 +'|'+ c015 +'|'+ c016 +'|'+ c017 +'|'+ c018 +'|'+ c019 +'|'+ c020 +'|'+ c021 +'|'+ c022 +'|'+ c023 +'|'+ c024 +'|'+ c025 +'|'+ c026 +'|'+ c027 +'|'+ c028 AS records
  FROM [dbo].[FileEPA] WITH (NOLOCK)
  WHERE InsertionDate=@InsertionDate

  END

Result:

HTML:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><META content="IE=5.0000" http-equiv="X-UA-Compatible">
<TITLE>VISOR EPA - DIA 26/02/2015</TITLE>
<META http-equiv="Content-Type" content="text/html; charset=windows-1252">
<META name="GENERATOR" content="MSHTML 10.00.9200.17183"></HEAD>
<BODY>
<TABLE id="table" bordercolor="#ffffff" bgcolor="#bfdfff" border="1"
cellspacing="0" cellpadding="3">
<THEAD>
<TR bgcolor="#dddddd">
<TH title="Field #1">Fila</TH>
<TH title="Field #2">Cargado</TH>
<TH title="Field #3">Identificador</TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH>
<TH></TH></TR></THEAD>
<TBODY>
<TR bgcolor="#aaaaaa">
<TD nowrap="">303</TD>
<TD nowrap="">2015-01-22</TD>
<TD nowrap="">20150122125859</TD>
<TD nowrap="">9352330873</TD>
<TD nowrap="">99991231</TD>
<TD nowrap="">999999</TD>
<TD nowrap="">9999999999</TD>
<TD nowrap="">99999</TD>
<TD nowrap="">9</TD>
<TD nowrap="">0</TD>
<TD nowrap="">20141229</TD>
<TD nowrap="">005434</TD>
<TD nowrap="">000332</TD>
<TD nowrap="">...</TD>
<TD nowrap="">61</TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD>
<TD nowrap=""></TD></TR></TBODY></TABLE></BODY></HTML>

Converting DateTime to ID string in SQL

SELECT TOP 10 CONVERT(VARCHAR, Registration_date, 112) + REPLACE(SUBSTRING(CONVERT(VARCHAR, Registration_date, 114),1,8),':','')
FROM [TableName]

Result format: - yyyyMMddHHmmss -

20150414131724
20150414152927
20150414174656