protected void Button1_Click(object sender, EventArgs e)
{
string fichpath = ConfigurationManager.AppSettings["ruta"].ToString();
string fich = "errores.xls";
string cols = "";
string colstype = "";
bool flg = false;
string nombretabla = "HS_IZ_CLIENTES";
string fecha = "20130820";
DataTable tabla = new DataTable("errores");
try
{
SqlConnection cnx = new SqlConnection();
cnx.ConnectionString = ConfigurationManager.ConnectionStrings["HSSIAAF"].ToString();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnx;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + nombretabla + "' AND COLUMN_NAME NOT IN ('LINEA','ERROR','CHECKS','ERRORS','LINEAC')";
cnx.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (File.Exists(fichpath + fich))
File.Delete(fichpath + fich);
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fichpath + fich + "';Extended Properties='Excel 8.0;HDR=Yes'"))
{
using (System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand("", con))
{
ocmd.Connection = con;
con.Open();
string sql = "CREATE TABLE [Errores] ( ";
bool flgcomma = true;
try
{
while (dr.Read())
{
switch (dr.GetString(1))
{
case "char":
{
sql += (flgcomma ? "" : ",") + dr.GetString(0) + " CHAR(1) ";
flg = true;
break;
}
case "varchar":
{
sql += (flgcomma ? "" : ",") + dr.GetString(0) + " VARCHAR(250) ";
flg = true;
break;
}
case "int":
{
sql += (flgcomma ? "" : ",") + dr.GetString(0) + " INT ";
flg = true;
break;
}
case "datetime":
{
sql += (flgcomma ? "" : ",") + dr.GetDateTime(0) + " DATETIME ";
flg = true;
break;
}
}
if (flg)
{
cols += (flgcomma ? "" : ",") + dr.GetString(0);
colstype += (flgcomma ? "" : ",") + dr.GetString(1);
flgcomma = false;
}
flg = false;
}
}
catch
{ }
sql += ")";
ocmd.CommandText = sql;
ocmd.CommandType = CommandType.Text;
ocmd.ExecuteNonQuery();
con.Close();
cnx.Close();
cmd.CommandText = "SELECT " + cols + " FROM " + nombretabla + " WHERE FECHA_CARGA='" + fecha + "'";
cnx.Open();
SqlDataReader dr2 = cmd.ExecuteReader();
string[] s = cols.Split(',');
string[] st = colstype.Split(',');
int cant = s.Length;
con.Open();
while (dr2.Read())
{
sql = "INSERT INTO [Errores] (" + cols + ") VALUES(";
flgcomma = true;
for (int i = 0; i <= cant - 1; i++)
{
switch (st[i])
{
case "char":
{
try
{
string sv = dr2.GetString(i);
sv = sv.Replace('\'', ' ').Replace('\"', ' ');
sql += (flgcomma ? "'" : ",'") + dr2.GetString(i) + "'";
}
catch
{
sql += (flgcomma ? "'" : ",'") + "'";
}
flgcomma = false;
break;
}
case "varchar":
{
try
{
string sv = dr2.GetString(i);
int y = 0;
sv = sv.Replace('\'', ' ').Replace('\"', ' ');
if (sv.Length > 250)
sv = sv.Substring(1, 247) + "...";
sql += (flgcomma ? "'" : ",'") + sv + "'";
}
catch
{
sql += (flgcomma ? "'" : ",'") + "'";
}
flgcomma = false;
break;
}
case "int":
{
try
{
sql += (flgcomma ? "" : ",") + dr2.GetInt32(i);
}
catch
{
sql += (flgcomma ? "" : ",") + "0";
}
flgcomma = false;
break;
}
case "datetime":
{
DateTime d = dr2.GetDateTime(i);
try
{
sql += (flgcomma ? "'" : ",'") + d.Year.ToString() + (d.Month < 10 ? "0" : "") + d.Month.ToString() + (d.Day < 10 ? "0" : "") + d.Day.ToString() + "'";
}
catch
{
sql += (flgcomma ? "'" : ",'") + "'";
}
flgcomma = false;
break;
}
}
}
sql += ")";
ocmd.CommandText = sql.Replace('\t',' ').Replace('\n',' ');
try
{
ocmd.ExecuteNonQuery();
}
catch
{
if (con.State == ConnectionState.Open)
{
ocmd.CommandText = "INSERT INTO [Errores] (" + cols.Substring(0, cols.IndexOf(',')) + ") VALUES('ERROR')";
ocmd.ExecuteNonQuery();
}
}
}
con.Close();
cnx.Close();
}
}
//Insert Terminado
}
catch //(Exception ex)
{
//ex.ToString();
}
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fich);
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/excel";
Response.TransmitFile(fichpath + fich);
Response.End();
}
