public void ImportCSV(string strFilePath, DateTime fechaSorteo)
{
try
{
log.Debug("Subiendo Archivo CSV");
DataTable dt = new DataTable();
using (StreamReader sr = new StreamReader(strFilePath))
{
string[] headers = sr.ReadLine().Split(';');
foreach (string header in headers)
{
dt.Columns.Add(header);
}
while (!sr.EndOfStream)
{
string[] rows = sr.ReadLine().Split(';');
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
dt.Rows.Add(dr);
}
}
//Dejar solo los ultimos 3 sorteos
//using (var ctx = new ControlBingosChiriDb()) // assuming IDisposable
//{
// log.Debug("Limpiando Datos viejos");
// ctx.Database.ExecuteSqlCommand("TRUNCATE TABLE [NumerosSorteos]");
// ctx.SaveChanges();
//}
using (var ctx = new ControlBingosChiriDb()) // assuming IDisposable
{
log.Debug("Limpiando Datos viejos");
ctx.Database.ExecuteSqlCommand("Delete From [NumerosSorteos] Where DATEADD(month, 2, FechaCreacion) < getdate()");
ctx.SaveChanges();
}
// Where DATEADD(month, 2, FechaCreacion) < getdate()
using (var ctx2 = new ControlBingosChiriDb()) // assuming IDisposable
{
log.Debug("Preparando Registros a Grabar");
foreach (DataRow item in dt.Rows)
{
NumerosSorteo n = new NumerosSorteo();
n.NumVal = Convert.ToInt32(item["numven"]);
n.FechaSorteo = fechaSorteo;
n.FechaCreacion = DateTime.Now;
//Console.Write(item["numven"]);
ctx2.NumerosSorteo.Add(n);
}
log.Debug("Grabando");
ctx2.SaveChanges();
}
}
catch (Exception ex)
{
log.Debug("Error en la importación: ");
log.Debug("Exception: " + ex.GetType() + ex.InnerException);
log.Debug("Message: " + ex.Message + "$$" + ex);
if (ex.GetType() != typeof(InvalidOperationException))
{
ViewBag.Message = "Error";
}
return;
}
//return dt;
}
public void ImportDBF(string filename)
{
log.Debug("Importando archivo...");
try
{
string FileName = filename;
string Path = FileName.Substring(0);
int Separator = Path.LastIndexOf("\\");
string DataSource = FileName.Substring(0, Separator) + "\\";
string File = FileName.Substring(Separator);
int Separator2 = File.LastIndexOf("\\");
string DBF = File.Remove(Separator2, 1);
int Separator3 = DBF.LastIndexOf(".");
string DBF_Extension = DBF.Substring(Separator3);
string DBF_FileName = DBF.Remove(Separator3, 4);
log.Debug("Iniciando Conexion Con DBF...");
//define the connections to the .dbf file
OleDbConnection conn = new OleDbConnection(@"Provider=vfpoledb;Data Source=" + DataSource + ";Collating Sequence=machine;");
log.Debug("Preparando Comando");
OleDbCommand command = new OleDbCommand("select * from " + DBF_FileName, conn);
//open the connection and read in all the data from .dbf file into a datatable
conn.Open();
DataTable dt = new DataTable();
log.Debug("Ejecutando Lectura del DBF");
dt.Load(command.ExecuteReader());
log.Debug("Cerrando Conexión");
conn.Close(); //close connection to the .dbf file
using (var ctx = new ControlBingosChiriDb()) // assuming IDisposable
{
log.Debug("Limpiando Datos viejos");
ctx.Database.ExecuteSqlCommand("TRUNCATE TABLE [NumerosSorteos]");
ctx.SaveChanges();
}
using (var ctx2 = new ControlBingosChiriDb()) // assuming IDisposable
{
log.Debug("Preparando Registros a Grabar");
foreach (DataRow item in dt.Rows)
{
NumerosSorteo n = new NumerosSorteo();
n.NumVal = Convert.ToInt32(item["numven"]);
//Console.Write(item["numven"]);
ctx2.NumerosSorteo.Add(n);
}
log.Debug("Grabando");
ctx2.SaveChanges();
}
//string SQLTableName = "Control";
////create a reader for the datatable
//DataTableReader reader = dt.CreateDataReader();
//myConnection.Open(); ///this is my connection to the sql server
//SqlBulkCopy sqlcpy = new SqlBulkCopy(myConnection);
//sqlcpy.DestinationTableName = SQLTableName; //copy the datatable to the sql table
//sqlcpy.WriteToServer(dt);
//myConnection.Close();
//reader.Close();
//MessageBox.Show("Congratulations, your .dbf file has been transferred.", "Success");
}
catch (Exception ex)
{
log.Debug("Error en la importación: ");
log.Debug("Exception: " + ex.GetType() + ex.InnerException);
log.Debug("Message: " + ex.Message + "$$" + ex);
if (ex.GetType() != typeof(InvalidOperationException))
{
ViewBag.Message = "Error";
}
return;
//LogEntry(ex.ToString());
//MessageBox.Show("Sorry, your .dbf file transfer failed. " +
//"Please make sure that you entered a valid SQL table name. " +
//"It must contain the correct column names and lengths that correspond to the .dbf.", "Failure");
//this.Refresh();
}
}
