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); } }