.NET Development: 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>

martes, 10 de marzo de 2015

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