protected override System.Data.DataTable ObtenerContenido(System.Data.DataTable contenido, FuenteInformacion fuenteInformacion, string rutaArchivo)
{
try
{
DataSet dsMsExcel = new DataSet();
using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + rutaArchivo + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"))
//using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + rutaArchivo + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"))
{
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
objOleConnection.Open();
DataTable worksheets = objOleConnection.GetSchema("Tables");
string hoja = worksheets.Rows[0][2].ToString();
System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection);
select.CommandType = CommandType.Text;
adapter.SelectCommand = select;
dsMsExcel.Tables.Clear();
adapter.Fill(dsMsExcel);
if (dsMsExcel.Tables.Count > 0)
{
foreach (DataRow filaEstadoCuenta in dsMsExcel.Tables[0].Rows)
{
contenido.Rows.Add(filaEstadoCuenta.ItemArray);
}
contenido.Rows.Remove(contenido.Rows[0]);
return(contenido);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public override System.Data.DataTable GetTables()
{
System.Data.DataTable dt = null;
using (System.Data.OleDb.OleDbConnection dbcon = new System.Data.OleDb.OleDbConnection(this.m_ConnectionString.ConnectionString))
{
lock (dbcon)
{
try
{
if (dbcon.State != System.Data.ConnectionState.Open)
{
dbcon.Open();
}
//string[] tblrestrictions = new string[] {null, null, null, "TABLE"};
//System.Data.DataTable dt1 = dbcon.GetSchema("tables", tblrestrictions);
// Note:
// Restrictions are a string array in the following format:
// {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.
// http://forums.asp.net/t/976267.aspx/1?Record+s+cannot+be+read+no+read+permission+on+MSysObjects+
// http://www.devart.com/dotconnect/mysql/docs/MetaData.html
// http://msdn.microsoft.com/en-us/library/ms254934(v=vs.80).aspx
// dt = dbcon.GetSchema("MetaDataCollections");
dt = dbcon.GetSchema("tables");
//dt = dbcon.GetSchema("columns");
//dt = dbcon.GetSchema("views");
//dt = dbcon.GetSchema("restrictions");
//dt = dbcon.GetSchema("procedures");
//dt = dbcon.GetSchema("DataTypes");
//dt = dbcon.GetSchema("Indexes");
//dt = dbcon.GetSchema("ReservedWords");
//dt = dbcon.GetSchema("DataSourceInformation");
}
catch (System.Exception ex)
{
if (Log("cOleDB_schema.GetTables", ex, "dbcon.GetSchema(\"tables\")"))
{
throw;
}
}
finally
{
if (dbcon.State != System.Data.ConnectionState.Closed)
{
dbcon.Close();
}
}
} // End Lock dbcon
} // End Using dbcon
return(dt);
} // End Function GetTables
public DataBase(string fileName)
{
this.FileName = fileName;
string connStr = BuildExcel2007ConnectionString(fileName, true);
conn = new System.Data.OleDb.OleDbConnection(connStr);
conn.Open();
DataTable tableschema = conn.GetSchema(OdbcMetaDataCollectionNames.Tables);
DataSet set = tableschema.DataSet;
TableName = tableschema.Rows[0]["Table_name"].ToString();
}
public override System.Data.DataColumn[] ObtenerColumnas(FuenteInformacion fuenteInformacion, string rutaArchivo)
{
try
{
DataSet dsMsExcel = new DataSet();
using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + rutaArchivo + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"))
{
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
objOleConnection.Open();
DataTable worksheets = objOleConnection.GetSchema("Tables");
string hoja = worksheets.Rows[0][2].ToString();
System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection);
select.CommandType = CommandType.Text;
adapter.SelectCommand = select;
dsMsExcel.Tables.Clear();
adapter.Fill(dsMsExcel);
if (dsMsExcel.Tables.Count > 0)
{
DataRow col = dsMsExcel.Tables[0].Rows[0];
DataColumn[] columnas = new DataColumn[col.ItemArray.Length];
int index = 0;
string nombre;
foreach (object campo in col.ItemArray)
{
if (!string.IsNullOrEmpty(campo.ToString()))
{
nombre = campo.ToString().Trim();
}
else
{
nombre = "NULL" + index.ToString();
}
columnas[index] = new DataColumn(nombre);
index++;
}
return(columnas);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
private void btnUpsize_Click(object sender, EventArgs e)
{
try
{
#region "GetAccessTables"
string[] path = txtAccessFile.Text.Split('\\');
int i = 0;
string theSystemDB = "";
for (i = 0; i < path.Length - 1; i++)
{
if (theSystemDB == "")
{
theSystemDB = path[i].ToString();
}
else
{
theSystemDB = theSystemDB + "\\" + path[i].ToString();
}
}
theSystemDB = theSystemDB + "\\System.mdw";
System.Data.OleDb.OleDbConnection theAccessCon = new System.Data.OleDb.OleDbConnection();
if (rbtCareWare.Checked == true)
{
theAccessCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtAccessFile.Text.Trim() + ";User ID=" + txtAccessUid.Text.Trim() + ";Password="******";Jet OLEDB:System database=" + theSystemDB;
}
else
{
theAccessCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtAccessFile.Text.Trim() + ";Jet OLEDB:Database Password="******";Jet OLEDB:System database=" + theSystemDB;
}
theAccessCon.Open();
DataTable theAccessTables = theAccessCon.GetSchema("TABLES");
DataTable theColumns = theAccessCon.GetSchema("COLUMNS");
#endregion
pgbar1.Maximum = theAccessTables.Rows.Count;
System.Data.OleDb.OleDbCommand theAccessCmd = new System.Data.OleDb.OleDbCommand();
pgbar1.Value = 0;
foreach (DataRow theDR in theAccessTables.Rows)
{
if (theDR["TABLE_TYPE"].ToString() == "TABLE")
{
theAccessCmd.CommandText = "Select * from " + theDR["TABLE_NAME"].ToString();
theAccessCmd.Connection = theAccessCon;
System.Data.OleDb.OleDbDataAdapter theAccessAdpt = new System.Data.OleDb.OleDbDataAdapter(theAccessCmd);
DataTable theResult = new DataTable();
theAccessAdpt.Fill(theResult);
//////Migration UpsizeManager = new Migration();
IMigration UpsizeManager;
UpsizeManager = (IMigration)ObjectFactory.CreateInstance("BusinessProcess.Service.BMigration, BusinessProcess.Service");
#region "ExporttoSQL"
string theConstr = string.Format("data source={0};uid={1};pwd={2};initial catalog={3}", txtSqlServer.Text.Trim(), txtSqlUid.Text.Trim(), txtSqlPwd.Text.Trim(), txtSqlDbName.Text.Trim());
UpsizeManager.UpsizeData(theResult, theColumns, txtSqlDbName.Text, theDR["TABLE_NAME"].ToString());
#endregion
}
pgbar1.Value = pgbar1.Value + 1;
}
MessageBox.Show("Upsizing Completed.", "IQCare", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception err)
{
MessageBox.Show(err.Message.ToString(), "IQCare", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
} // End Function GetColumnNamesForTable
public override bool TableExists(string strTableName)
{
strTableName = strTableName.Replace("'", "''");
bool bReturnValue = false;
using (System.Data.OleDb.OleDbConnection dbcon = new System.Data.OleDb.OleDbConnection(this.m_ConnectionString.ConnectionString))
{
lock (dbcon)
{
try
{
if (dbcon.State != System.Data.ConnectionState.Open)
{
dbcon.Open();
}
//string[] tblrestrictions = new string[] {null, null, null, "TABLE"};
//System.Data.DataTable dt1 = dbcon.GetSchema("tables", tblrestrictions);
// Note:
// Restrictions are a string array in the following format:
// {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.
// http://forums.asp.net/t/976267.aspx/1?Record+s+cannot+be+read+no+read+permission+on+MSysObjects+
using (System.Data.DataTable dt = dbcon.GetSchema("tables"))
{
int i = dt.Select("TABLE_NAME like '" + strTableName + "'").Length;
if (i > 0)
{
bReturnValue = true;
}
dt.Clear();
dt.Dispose();
/*
* System.Collections.Generic.List<string> ls = new System.Collections.Generic.List<string>();
* foreach(System.Data.DataColumn dc in dt.Columns)
* {
* ls.Add(dc.ColumnName);
* }
* MsgBox(string.Join(" ", ls.ToArray()));
*/
}
}
catch (System.Exception ex)
{
if (Log("cOleDB_schema.TableExists", ex, "GetSchema(\"ExportTables\")"))
{
throw;
}
}
finally
{
if (dbcon.State != System.Data.ConnectionState.Closed)
{
dbcon.Close();
}
}
} // End Lock dbcon
} // End Using dbcon
/*
* strTableName = strTableName.Replace("'", "''");
*
* string strSQL = @"
* SELECT COUNT(*)
* FROM MSysObjects
* WHERE (((Left([Name],1))<> ""~"")
* AND ((Left([Name],4))<>""MSys"")
* AND ((MSysObjects.Type) In (1,4,6)))
* AND Name = '" + strTableName + "' "
* ;
*
* return ExecuteScalar<bool>(strSQL);
*/
return(bReturnValue);
} // End Function TableExists
public System.Data.DataSet GetDataSet_Excel(HttpPostedFileBase file, out string ErrorMessage)
{
using (iGst_Svc.GSTServiceClient iGstSvc = new iGst_Svc.GSTServiceClient())
{
System.Data.DataSet ds = null;
ErrorMessage = "";
try
{
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand cmd = null;
System.Data.OleDb.OleDbDataAdapter da = null;
string TableName = "", ConnectionString = "";
var fileName = System.IO.Path.GetFileName(file.FileName);
var path = System.IO.Path.Combine(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["UploadDataPath"].ToString()), fileName);
switch (System.IO.Path.GetExtension(path).Trim().ToUpper())
{
case ".XLS":
ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path);
break;
case ".XLSX":
ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path);
break;
default:
ErrorMessage = "Please select a valid excel file";
break;
}
if (ErrorMessage.Trim().Length == 0)
{
try
{
try
{
file.SaveAs(path);
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Saveas", "Error");
ErrorMessage = "Problem in saving file:" + ex.Message;
return(null);
}
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel15ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel14ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel13ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path));
conn.Open();
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Connection", "Error");
ErrorMessage = "Problem in saving file:" + ex.Message;
return(null);
}
}
}
}
}
try
{
TableName = conn.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
cmd = new System.Data.OleDb.OleDbCommand(@"SELECT * FROM [" + TableName + "]", conn);
da = new System.Data.OleDb.OleDbDataAdapter(cmd);
ds = new System.Data.DataSet();
da.Fill(ds);
da.Dispose();
cmd.Dispose();
conn.Dispose();
return(ds);
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error");
ErrorMessage = "Inner Problem in reading file";
return(null);
}
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error");
ErrorMessage = "Problem in reading file";
return(null);
}
}
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
ErrorMessage = "Problem in reading file" + ex.Message;
return(null);
}
return(null);
}
}