Example #1
1
    public static void ReadExcel()
    {
        string sheetName = string.Empty;
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\test.xlsx;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        DataTable table = connection.GetSchema("Tables");

        if (table.Rows.Count > 0)
            sheetName = table.Rows[0]["TABLE_NAME"].ToString();

        if (!string.IsNullOrEmpty(sheetName))
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = string.Format("select * from [{0}]", sheetName);
            command.CommandType = CommandType.Text;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                StringBuilder strBuilder = new StringBuilder();
                for (int i = 0; i < reader.FieldCount; i++)
                    strBuilder.Append(string.Format("{0}\t", reader[i]));
                Console.WriteLine(strBuilder.ToString());
            }
            reader.Close();
        }

        connection.Close();

        Console.ReadKey();
    }
Example #2
1
        //public string ExcelFile {private get; set; }
        public static DataTable ReadData(string excelFile)
        {
            if (!System.IO.File.Exists(excelFile))
                return null;

            OleDbConnection excelConnection = new OleDbConnection();
            excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
            excelConnection.Open();
            DataTable dtSchema = excelConnection.GetSchema("Tables");
            if (dtSchema.Rows.Count == 0)
                return null;

            string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
            string strSQL = string.Format("select * from [{0}]", strTableName);
            OleDbCommand cmdSelect = excelConnection.CreateCommand();
            cmdSelect.CommandText = strSQL;
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
            DataTable dtResult=new DataTable();
            dbAdapter.Fill(dtResult);

            dbAdapter.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

            return dtResult;
        }
Example #3
0
 public DataTable GetTablesName()
 {
     OleDbConnection connection = new OleDbConnection();
     DataTable dtTables = new DataTable();
     connection.ConnectionString = this._DbConectionString;
     try
     {
         connection.Open();
         dtTables = connection.GetSchema("TABLES");
     }
     catch (OleDbException oledbex)
     {
         throw oledbex;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         if (connection.State == ConnectionState.Open)
         {
             connection.Close();
         }
     }
     return dtTables;
 }
Example #4
0
        /// <summary>
        /// Imports all sheets from an Excel file
        /// </summary>
        /// <param name="excelFile">The excel file path.</param>
        /// <returns></returns>
        public static DataSet ImportAll(string excelFile)
        {
            DataSet ds = new DataSet();

            string connectionString = GetExcelConnectionString(excelFile, false, true);

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                //csv doesn't have worksheets, and table name == file name
                if (Path.GetExtension(excelFile).Equals(".csv", StringComparison.OrdinalIgnoreCase))
                {
                    LoadTableIntoDataSet(connection, Path.GetFileName(excelFile), ds);
                    return ds;
                }

                //xls and xlsx have worksheets, so load each one as a datatable
                DataTable worksheets = connection.GetSchema("Tables");
                foreach (DataRow row in worksheets.Rows)
                {
                    //this can also return Excel named ranges
                    string tabName = (string)row["TABLE_NAME"];
                    //so look for sheets (excel puts $ after the name and may single-quote the name)
                    if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
                        LoadTableIntoDataSet(connection, tabName, ds);
                }
            }
            return ds;
        }
Example #5
0
        public string[] GetColumnsList(string worksheet)
        {
            string[] columns;

            try
            {
                OleDbConnection connection = new OleDbConnection(strConnection);
                connection.Open();
                DataTable tableColumns = connection.GetSchema("Columns", new string[] {null, null, worksheet + '$', null});
                connection.Close();

                columns = new string[tableColumns.Rows.Count];

                for (int i = 0; i < columns.Length; i++)
                {
                    columns[i] = (string)tableColumns.Rows[i]["COLUMN_NAME"];
                }
            }
            catch
            {
                throw;
            }

            return columns;
        }
Example #6
0
 public DataTable GetTablesName(string FileName)
 {
     OleDbConnection connection = new OleDbConnection();
     DataTable dtTables = new DataTable();
     connection.ConnectionString = ConectionStringManagerAccess(FileName);
     try
     {
         connection.Open();
         dtTables = connection.GetSchema("TABLES");
         for (int i = 0; i < dtTables.Rows.Count; i++)
         {
             if (dtTables.Rows[i]["TABLE_TYPE"].ToString() != "TABLE")
             {
                 dtTables.Rows.RemoveAt(i);
                 i--;
             }
         }
     }
     catch (OleDbException oleex)
     {
         throw oleex;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         if (connection.State == ConnectionState.Open)
         {
             connection.Close();
         }
     }
     return dtTables;
 }
Example #7
0
        public DataTable GetSchema(string collectionName)
        {
            DataTable results;

            conn = new OleDbConnection(connection);
            conn.Open();
            results =  conn.GetSchema(collectionName);
            conn.Close();
            return results;
        }
 public void load_sheet_names()
 {
     connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_PathtextBox1.Text + ";Extended Properties=Excel 12.0;";
     OleDbConnection connection = new OleDbConnection(connectionstring);
     connection.Open();
     connection.GetSchema();
     connection.GetSchema("Tables");
     List<string> tables = new List<string>();
     foreach (DataRow r in connection.GetSchema("Tables").Select("TABLE_TYPE = 'TABLE'"))
     {
         tables.Add(r["TABLE_NAME"].ToString());
     }
     foreach (var item in tables)
     {
         Excel_Sheet_Name_comboBox.Items.Add(item);
     }
     if (connection.State == ConnectionState.Open)
     {
         connection.Close();
     }
 }
Example #9
0
    public IEnumerable<NFLEPMarkov> GetMarkovData(string FileName)
    {
        string sSheetName = null;
        string sConnection = null;
        DataTable dtTablesList = default(DataTable);
        OleDbCommand oleExcelCommand = default(OleDbCommand);
        OleDbDataReader oleExcelReader = default(OleDbDataReader);
        OleDbConnection oleExcelConnection = default(OleDbConnection);

        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
        oleExcelConnection = new OleDbConnection(sConnection);
        oleExcelConnection.Open();

        dtTablesList = oleExcelConnection.GetSchema("Tables");

        if (dtTablesList.Rows.Count > 0)
        {
            sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
        }

        dtTablesList.Clear();
        dtTablesList.Dispose();


        if (!string.IsNullOrEmpty(sSheetName))
        {
            oleExcelCommand = oleExcelConnection.CreateCommand();
            oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
            oleExcelCommand.CommandType = CommandType.Text;
            oleExcelReader = oleExcelCommand.ExecuteReader();

            //nOutputRow = 0;
            //var sheets = oleExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //oleExcelCommand.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

            //var adapter = new OleDbDataAdapter(oleExcelCommand);
            //var ds = new DataSet();
            //adapter.Fill(ds);

            //object[] dataFill = new object[17];

            using (var reader = oleExcelReader) { //will this work?
            //using (var reader = oleExcelCommand.ExecuteReader())
            //I think using gets rid of the reader, hopefully.
                while (reader.Read()) {
                     yield return NFLEPMarkov.Create(reader);
                }
            }
        }
        oleExcelConnection.Close();
    }
Example #10
0
 //对下拉列表进行数据绑定
 private void CBoxBind()
 {
     cbox_SheetName.Items.Clear();//清空下拉列表项
     //连接Excel数据库
     OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Path.Text + ";Extended Properties=Excel 8.0");
     olecon.Open();//打开数据库连接
     System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
     DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
     while (DTReader.Read())//循环读取
     {
         cbox_SheetName.Items.Add(DTReader["Table_Name"].ToString().Replace('$',' ').Trim());//将工作表名添加到下拉列表中
     }
     DTable = null;//清空表对象
     DTReader = null;//清空表读取对象
     olecon.Close();//关闭数据库连接
     cbox_SheetName.SelectedIndex = 0;//设置下拉列表默认选项为第一项
 }
Example #11
0
 //获取所有工作表名称
 private List<string> GetSheetName(string P_str_Excel)
 {
     List<string> P_list_SheetName = new List<string>();//实例化泛型集合对象
     //连接Excel数据库
     OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Excel + ";Extended Properties=Excel 8.0");
     olecon.Open();//打开数据库连接
     System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
     DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
     while (DTReader.Read())//循环读取
     {
         string P_str_Name = DTReader["Table_Name"].ToString().Replace('$', ' ').Trim();//记录工作表名称
         if (!P_list_SheetName.Contains(P_str_Name))//判断泛型集合中是否已经存在该工作表名称
             P_list_SheetName.Add(P_str_Name);//将工作表名添加到泛型集合中
     }
     DTable = null;//清空表对象
     DTReader = null;//清空表读取对象
     olecon.Close();//关闭数据库连接
     return P_list_SheetName;//返回得到的泛型集合
 }
 private void button1_Click(object sender, EventArgs e)
 {
     try
     {
         comboBox1.Items.Clear();
         OleDbConnection dbCon = new OleDbConnection(@"Provider=SQLNCLI10;Server=(local);Database=McDac;Trusted_Connection=yes");
         dbCon.Open();
         DataTable tables = dbCon.GetSchema("Tables", new string[] { null, null, null, "TABLE" }); //список всех таблиц
         foreach (DataRow row in tables.Rows)
         {
             string TableName = row["TABLE_NAME"].ToString();
             comboBox1.Items.Add(TableName);
         }
         dbCon.Close();
         MessageBox.Show("Ready!");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }
Example #13
0
        public string[] GetWorksheetList()
        {
            string[] worksheets;

            try
            {
                OleDbConnection connection = new OleDbConnection(strConnection);
                connection.Open();
                DataTable tableWorksheets = connection.GetSchema("Tables");
                connection.Close();

                worksheets = new string[tableWorksheets.Rows.Count];

                for (int i = 0; i < worksheets.Length; i++)
                {
                    worksheets[i] = (string)tableWorksheets.Rows[i]["TABLE_NAME"];
                    worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
                    // removes the trailing $ and other characters appended in the table name
                    while (worksheets[i].EndsWith("$"))
                        worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
                }
            }
            catch 
            {
                /* 
                    for (int i = 0; i < ex.Errors.Count; i++)
                    {
                        MessageBox.Show("Index #" + i + "\n" +
                        "Message: " + myException.Errors[i].Message + "\n" +
                        "Native: " +
                            myException.Errors[i].NativeError.ToString() + "\n" +
                        "Source: " + myException.Errors[i].Source + "\n" +
                        "SQL: " + myException.Errors[i].SQLState + "\n");
                    }
                 */
                throw;
            }

            return worksheets;
        }
Example #14
0
 public ActionResult Excel(HttpPostedFileBase uploadFile)
 {
     StringBuilder strValidations = new StringBuilder(string.Empty);
     try
     {
         string filePath = Path.Combine(HttpContext.Server.MapPath("../ufile"), Path.GetFileName(uploadFile.FileName));
         if (uploadFile.ContentLength > 0)
         {
             Path.GetFileName(uploadFile.FileName);
             uploadFile.SaveAs(filePath);
             string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("../files/") + uploadFile.FileName.Split('\\')[3].ToString() + ";Extended Properties=Excel 12.0;";
             using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
             {
                 conn.Open();
                 using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
                 {
                     string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                     string query = "SELECT * FROM [" + sheetName + "]";
                     OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
                     DataSet ds = new DataSet();
                     adapter.Fill(ds, "Items");
                     if (ds.Tables.Count > 0)
                     {
                         if (ds.Tables[0].Rows.Count > 0)
                         {
                             for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                             {
                                 //这里添加到数据库操作
                             }
                         }
                     }
                 }
             }
         }
     }
     catch
     { }
     return View();
 }
Example #15
0
        private static void OleDbReadCollectionName(string name)
        {
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                StringBuilder sb = new StringBuilder();

                DataTable dataTable = conn.GetSchema(name);

                foreach (DataColumn dataColumn in dataTable.Columns)
                {
                    sb.Append(dataColumn.ColumnName.PadRight(40));
                }

                sb.AppendLine();

                foreach (DataRow row in dataTable.Rows)
                {
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        sb.Append(row[column].ToString().PadRight(40));
                    }

                    sb.AppendLine();
                }

                using (StreamWriter writer = new StreamWriter(String.Format("oledb-{0}-schema.txt", name.ToLowerInvariant())))
                {
                    writer.Write(sb.ToString());
                    writer.Flush();
                }

            }
        }
Example #16
0
        public void dobivanjeTablica()
        {
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\EvidencijaOtpremnica.mdb";
            OleDbConnection conn = new OleDbConnection(connString);
            conn.Open();

            //dobivanje imena svih tablica

            if (Broj_otpremnice == "")
            {
                string[] restrictions = new string[4];
                restrictions[3] = "Table";

                DataTable dt = conn.GetSchema("Tables", restrictions);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    tableNames.Add(dt.Rows[i][2].ToString());
                }

            }

            conn.Close();
        }
Example #17
0
        public DataTable GetCumasFileData()
        {
            // http://www.connectionstrings.com/dbf-foxpro/
            // http://stackoverflow.com/questions/22361457/c-sharp-read-from-dbf-files-into-a-datatable
            // http://www.codeproject.com/Articles/24247/Load-a-DBF-into-a-DataTable
            // https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.110).aspx
            // http://stackoverflow.com/questions/11356878/get-data-in-a-dbf-file-using-c-sharp

            DataTable resultData = new DataTable();

            using (OleDbConnection connectionHandler = new OleDbConnection(
            @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\diogo.marques\Downloads\Newest version\Newest version\Newest version\SALONMAN CURRENT\;Extended Properties=dBASE IV"))
            {
                // Open the connection, and if open successfully, you can try to query it
                connectionHandler.Open();

                if (connectionHandler.State == ConnectionState.Open)
                {
                    string mySQL = "select * from Cumas";  // dbf table name

                    OleDbCommand MyQuery = new OleDbCommand(mySQL, connectionHandler);
                    OleDbDataAdapter DA  = new OleDbDataAdapter(MyQuery);

                    DA.Fill(resultData);

                    var databaseName  = connectionHandler.Database;
                    var serverVersion = connectionHandler.ServerVersion;
                    var site          = connectionHandler.Site;
                    var schema        = connectionHandler.GetSchema();
                    var container     = connectionHandler.Container;

                    connectionHandler.Close();
                }

                return resultData;
            }
        }
Example #18
0
        public static DataTable GetOledbFirstTableData(this string dbFile)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + dbFile + "; Extended Properties=Excel 8.0;";

            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();

                var schema = conn.GetSchema("Tables");
                string strSql = string.Format("select * from [{0}]", schema.Rows[0]["TABLE_NAME"]);

                DataTable dt = null;

                if (schema.Rows.Count > 0)
                {
                    var ds = new DataSet();
                    var da = new OleDbDataAdapter(strSql, conn);
                    da.Fill(ds); // 填充DataSet   
                    dt = ds.Tables[0];

                }
                return dt;
            }
        }
Example #19
0
        private static void ReadData(OleDbConnection connection,
            OleDbCommand selectCommand, OleDbDataAdapter adapter)
        {
            DataTable tableInfo = connection.GetSchema("Tables");
            List<string> sheetsName = new List<string>();
            foreach (DataRow row in tableInfo.Rows)
            {
                sheetsName.Add(row["TABLE_NAME"].ToString());
            }

            for (int t = 0; t < sheetsName.Count; t++)
            {
                selectCommand.CommandText = "SELECT * FROM [" + sheetsName[t] + "]";
                selectCommand.Connection = connection;
                adapter.SelectCommand = selectCommand;
                DataTable Sheet = new DataTable();
                Sheet.TableName = sheetsName[t].Replace("$", "").Replace("'", "");
                adapter.Fill(Sheet);

                Console.WriteLine("NAME -> SCORE");
                Console.WriteLine("--------------------------");

                for (int i = 0; i < Sheet.Rows.Count; i++)
                {
                    for (int j = 0; j < Sheet.Columns.Count; j++)
                    {
                        Console.Write(Sheet.Rows[i][j]);
                        if (j < Sheet.Columns.Count - 1)
                        {
                            Console.Write(" -> ");
                        }
                    }
                    Console.WriteLine();
                }
            }
        }
Example #20
0
        /// <summary>
        ///   Gets the list of worksheets in the spreadsheet.
        /// </summary>
        /// 
        public string[] GetWorksheetList()
        {
            string[] worksheets;

            OleDbConnection connection = new OleDbConnection(strConnection);
            connection.Open();
            DataTable tableWorksheets = connection.GetSchema("Tables");
            connection.Close();

            worksheets = new string[tableWorksheets.Rows.Count];

            for (int i = 0; i < worksheets.Length; i++)
            {
                worksheets[i] = (string)tableWorksheets.Rows[i]["TABLE_NAME"];
                worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');

                // removes the trailing $ and other characters appended in the table name
                while (worksheets[i].EndsWith("$", StringComparison.Ordinal))
                    worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
            }


            return worksheets;
        }
		private static string GetFirstSheet(OleDbConnection conn)
		{
#if NET_2_0
			DataTable dt = conn.GetSchema("Tables");
#else
			DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
#endif
	
			if (dt != null && dt.Rows.Count > 0)
				return dt.Rows[0]["TABLE_NAME"].ToString();
			else
				throw new BadUsageException("A Valid sheet was not found in the workbook.");
		}
Example #22
0
		public void GetSchema_Connection_Closed ()
		{
			OleDbConnection cn = new OleDbConnection ();

			try {
				cn.GetSchema ();
				Assert.Fail ("#A1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
				Assert.IsNull (ex.InnerException, "#B3");
				Assert.IsNotNull (ex.Message, "#B4");
			}

			try {
				cn.GetSchema ("Tables");
				Assert.Fail ("#B1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
				Assert.IsNull (ex.InnerException, "#B3");
				Assert.IsNotNull (ex.Message, "#B4");
			}

			try {
				cn.GetSchema ((string) null);
				Assert.Fail ("#C1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
				Assert.IsNull (ex.InnerException, "#C3");
				Assert.IsNotNull (ex.Message, "#C4");
			}

			try {
				cn.GetSchema ("Tables", new string [] { "master" });
				Assert.Fail ("#D1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#D2");
				Assert.IsNull (ex.InnerException, "#D3");
				Assert.IsNotNull (ex.Message, "#D4");
			}

			try {
				cn.GetSchema ((string) null, new string [] { "master" });
				Assert.Fail ("#E1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#E2");
				Assert.IsNull (ex.InnerException, "#E3");
				Assert.IsNotNull (ex.Message, "#E4");
			}

			try {
				cn.GetSchema ("Tables", (string []) null);
				Assert.Fail ("#F1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#F2");
				Assert.IsNull (ex.InnerException, "#F3");
				Assert.IsNotNull (ex.Message, "#F4");
			}

			try {
				cn.GetSchema ((string) null, (string []) null);
				Assert.Fail ("#G1");
			} catch (InvalidOperationException ex) {
				// Invalid operation. The connection is closed
				Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#G2");
				Assert.IsNull (ex.InnerException, "#G3");
				Assert.IsNotNull (ex.Message, "#G4");
			}
		}
Example #23
0
        public static bool TableExists(string connString, string table)
        {
            bool functionReturnValue = false;
            //// Open connection to the database
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                //// Specify restriction to get table definition schema
                //// For reference on GetSchema see:
                //// http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx

                string[] restrictions = new string[4];
                restrictions[2] = table;
                DataTable dbTbl = conn.GetSchema("Tables", restrictions);

                functionReturnValue = dbTbl.Rows.Count != 0;

                dbTbl.Dispose();
            }
            return functionReturnValue;
        }
        private DataTable GetTableColumns(string tName)
        {
            DataTable tc = new DataTable();

            try
            {

                if (this.raExportAccess.Checked == true)
                {
                    OleDbConnection conn = new OleDbConnection();
                    conn.ConnectionString = this.txtDataSource.Text;

                    conn.Open();

                    string[] Restrictions = new string[4];
                    Restrictions[2] = tName;

                    tc = conn.GetSchema("Columns", Restrictions);

                    return tc;

                }
                else if (this.raExportMSSQL.Checked == true)
                {
                    SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = this.txtDataSource.Text;

                    conn.Open();

                    string[] Restrictions = new string[4];
                    Restrictions[2] = tName;

                    tc = conn.GetSchema("Columns", Restrictions);

                    return tc;
                }
                else if (this.raExportMySql.Checked == true)
                {
                    MySqlConnection conn = new MySqlConnection();
                    conn.ConnectionString = this.txtDataSource.Text;

                    conn.Open();

                    string[] Restrictions = new string[4];
                    Restrictions[2] = tName;

                    tc = conn.GetSchema("Columns", Restrictions);

                    return tc;
                }

                return tc;

            }
            catch (System.Exception)
            {
                return null;
            }
        }
        private void FillAccessTable()
        {
            if (this.comTableName.Items.Count != 0)
                return;

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = this.txtDataSource.Text;

            try
            {
                conn.Open();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(rm.GetString("Error12") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            DataTable tb = conn.GetSchema("Tables");

            foreach (DataRow r in tb.Rows)
            {
                if (r[3].ToString() == "TABLE")
                {
                    this.comTableName.Items.Add(r[2].ToString());
                }

            }
        }
        /// <summary>
        /// Creates a Boral CMC specific CSV file from an excel file with DDW information
        /// </summary>
        /// <param name="sourceXlsFile">Source excel file</param>
        /// <param name="outputFolder">Destination folder for the resulting CSV file</param>
        /// <returns></returns>
        internal static string ApplyCsvHack(string sourceXlsFile, string outputFolder)
        {
            var finalFile = sourceXlsFile;

            const string xlsConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
            var fileName = Path.GetFileNameWithoutExtension(sourceXlsFile);

            if (!string.IsNullOrEmpty(fileName))
            {
                // the final file that will be returned is the hacked CSV file
                finalFile = Path.Combine(outputFolder, fileName + ".csv");

                // delete the CSV file if it already exists
                if (File.Exists(finalFile)) File.Delete(finalFile);

                using (var conn = new OleDbConnection(string.Format(xlsConnString, sourceXlsFile)))
                {
                    conn.Open();

                    string[] sheetNames = conn.GetSchema("Tables").AsEnumerable().Select(a => a["TABLE_NAME"].ToString()).ToArray();

                    foreach (string sheetName in sheetNames)
                    {
                        using (var sw = new StreamWriter(File.Create(finalFile), Encoding.Unicode))
                        {
                            using (var ds = new DataSet())
                            {
                                using (var adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), conn))
                                {
                                    adapter.Fill(ds);

                                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                                    {
                                        var dr = ds.Tables[0].Rows[i];

                                        string[] cells = dr.ItemArray.Select(a => a.ToString()).ToArray();

                                        // ensure this is not an empty row before we write it out to the file
                                        if (string.Join(",", cells).Replace(",","").Trim() == string.Empty)
                                        {
                                            continue;
                                        }

                                        // check whether or not the current line is for a DDW header
                                        if (cells[0].ToLower() == "name" && cells[1].ToLower() == "payrollnumber")
                                        {
                                            // use only the required DDW header columns (some columns will be blank)
                                            sw.WriteLine("{0}", string.Join(",", cells.Take(12)));

                                            // also write the DDW header values ignoring trailing commas
                                            i++;
                                            dr = ds.Tables[0].Rows[i];
                                            cells = dr.ItemArray.Select(a => a.ToString()).ToArray();
                                            sw.WriteLine("{0}", string.Join(",", cells.Take(12)));
                                        }
                                        else
                                        {
                                            sw.WriteLine("{0}", string.Join(",", cells));
                                        }
                                    }
                                }
                            }
                        }

                        var strQ = File.ReadAllText(finalFile);
                        File.WriteAllText(finalFile, strQ);
                    }
                }
            }

            return finalFile;
        }
Example #27
0
 /// <summary>
 /// 取所有表名
 /// </summary>
 /// <returns></returns>
 public List<string> GetTableNameList()
 {
     List<string> list = new List<string>();
     OleDbConnection Conn = new OleDbConnection(connectionString);
     try
     {
         if (Conn.State == ConnectionState.Closed)
             Conn.Open();
         DataTable dt = Conn.GetSchema("Tables");
         foreach (DataRow row in dt.Rows)
         {
             if (row[3].ToString() == "TABLE")
                 list.Add(row[2].ToString());
         }
         return list;
     }
     catch (Exception e)
     { throw e; }
     finally { if (Conn.State == ConnectionState.Open) Conn.Close(); Conn.Dispose(); }
 }
Example #28
0
        public override bool process(string url, string filename, string source)
        {
            bool ret = true;

            OleDbConnection con = null;

            LibSys.StatusBar.Trace("IP: processing MDB file: " + filename);

            try
            {
                con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;data source=" + filename + "");
                con.Open(); //connection must be opened

                DataTable dt = con.GetSchema("Tables");

                DataRow row = dt.Select("TABLE_TYPE='TABLE'")[0];

                string tableName = row["TABLE_NAME"].ToString();

                OleDbCommand cmd = new OleDbCommand("SELECT * from [" + tableName + "]", con); // creating query command
                OleDbDataReader reader = cmd.ExecuteReader(); // executes query

                int i = 0;
                int errCnt = 0;
                while (reader.Read()) // if can read row from database
                {
                    try
                    {
                        SectionRow sr = new SectionRow()
                        {
                            RECRD = (int)reader.GetValue(0),
                            VESSLTERMS = reader.GetValue(1).ToString(),
                            CHART = reader.GetValue(2).ToString(),
                            AREA = reader.GetValue(3).ToString(),
                            CARTOCODE = reader.GetValue(4).ToString(),
                            SNDINGCODE = reader.GetValue(5).ToString(),
                            DEPTH = reader.GetValue(6).ToString(),
                            NATIVLAT = reader.GetValue(7).ToString(),
                            NATIVLON = reader.GetValue(8).ToString(),
                            LAT83 = reader.GetValue(9).ToString(),
                            LONG83 = reader.GetValue(10).ToString(),
                            LATDEC = (double)reader.GetValue(11),
                            LONDEC = -(double)reader.GetValue(12),
                            NATIVDATUM = reader.GetValue(13).ToString(),
                            CONVERT83 = reader.GetValue(14).ToString(),
                            GPACCURACY = reader.GetValue(15).ToString(),
                            GPQUALITY = reader.GetValue(16).ToString(),
                            GPSOURCE = reader.GetValue(17).ToString(),
                            QUADRANT = reader.GetValue(18).ToString(),
                            History = reader.GetValue(19).ToString(),
                            REFERENCE = reader.GetValue(20).ToString(),
                            YEARSUNK = reader.GetValue(21).ToString()
                        };

                        CreateInfo createInfo = new CreateInfo();	// we will recycle it in place, filling every time.

                        createInfo.init("wpt");
                        createInfo.name = (sr.VESSLTERMS + " " + sr.DEPTH).Trim();
                        createInfo.desc = sr.YEARSUNK;
                        createInfo.lat = sr.LATDEC;
                        createInfo.lng = sr.LONDEC;
                        createInfo.typeExtra = "unknown";	// type: ppl, school, park, locale, airport, reservoir, dam, civil, cemetery, valley, building
                        createInfo.source = source;
                        createInfo.comment = sr.History.Replace(". ", ".\r\n") + ";\r\n" + sr.REFERENCE;

                        m_insertWaypoint(createInfo);
                    }
                    catch (Exception excr)
                    {
                        errCnt++;
                    }

                    i++;
                }
                LibSys.StatusBar.Trace("OK: MDB file: '" + filename + "' processed; found table[" + tableName + "] records/waypoints count=" + i + "  errors count=" + errCnt);
            }
            catch (Exception e)
            {
                LibSys.StatusBar.Error("FileAwoisMdb process() " + e.Message);
                ret = false;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }

            return ret;
        }
Example #29
0
        public void LoadRepository()
        {
            //1. recursive delete \extracted [catching NonExisting exception]
            string extractPath = @"..\..\extracted";
            if (Directory.Exists(extractPath))
            {
                Directory.Delete(extractPath, true);
            }

            //2. create \extracted
            Directory.CreateDirectory(extractPath);

            //3. extract from sales_reports.zip into extracted
            string zipPath = @"..\..\sales_reports.zip";
            using (var zip1 = ZipFile.Read(zipPath))
            {
                foreach (var entry in zip1)
                {
                    entry.Extract(extractPath, ExtractExistingFileAction.OverwriteSilently);
                }
            }

            //4. get \extracted directories
            var subfolders = Directory.GetDirectories(extractPath);

            //5. foreach the directories 
            foreach (var dateFolderPath in subfolders)
            {
                string dateFolder = dateFolderPath.Split('\\').Last();

                //    parsing the date (parse fail = skip)
                DateTime saleDate;
                if (!DateTime.TryParse(dateFolder, out saleDate))
                {
                    continue;
                }

                //    get filenames
                var saleReportsByDealer = Directory.GetFiles(dateFolderPath);

                //    foreach the filenames
                foreach (var reportPath in saleReportsByDealer)
                {
                    // check if .xls extension (fail = skip)
                    var fileInfo = new FileInfo(reportPath);
                    string extension = fileInfo.Extension;
                    if (extension != ".xls")
                    {
                        continue;
                    }
                    // parse int for DealerId (fail = skip)
                    string filename = fileInfo.Name.Split('.').First();
                    int dealerId;
                    if (!int.TryParse(filename, out dealerId))
                    {
                        continue;
                    }

                    // connect via OleDb + pull the rows + close connection
                    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + reportPath + ";"
                        + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

                    OleDbConnection db_Con = new OleDbConnection(connectionString);

                    #region Connection
                    db_Con.Open();
                    using (db_Con)
                    {

                        // this code is for discovering sheet names if they are not available
                        DataTable tables = db_Con.GetSchema("Tables");
                        var sheetNameList = new List<string>();
                        for (int i = 0; i < tables.Rows.Count; i++)
                        {
                            var name = tables.Rows[i][2].ToString();
                            sheetNameList.Add(name);
                        }

                        foreach (var sheetName in sheetNameList)
                        {
                            Sale sale = new Sale() { DealerId = dealerId, SaleDate = saleDate };
                            this.repo.Add<Sale>(sale);

                            string sqlString = "select * from [" + sheetName + "];";

                            OleDbCommand cmdGetRows = new OleDbCommand(sqlString, db_Con);
                            //cmdGetRows.Parameters.AddWithValue("@sheetName", sheetName);

                            OleDbDataReader reader = cmdGetRows.ExecuteReader();
                            using (reader)
                            {
                                SaleDetails details;
                                while (reader.Read())
                                {
                                    details = this.DetailsRow(this.repo, reader, sale);
                                    if (details != null)
                                    {
                                        this.repo.Add<SaleDetails>(details);
                                    }
                                }
                            }
                        }
                    }//using db_Con
                    #endregion
                }
            }
        }
Example #30
0
    public List<table> getTables(string cadconexion, string database)
    {
        OleDbConnection conexion = null;
        try
        {
            List<table> lista = new List<table>();

            conexion = new OleDbConnection(cadconexion);
            miComando = new OleDbCommand("");
            miComando.Connection = conexion;
            conexion.ConnectionString = cadconexion;
            conexion.Open();

            System.Data.DataTable dt = new System.Data.DataTable();
            dt = conexion.GetSchema(OleDbMetaDataCollectionNames.Tables, new String[] { null, null, null, null });

            foreach (System.Data.DataRow rowDatabase in dt.Rows)
            {
                // exclude system tables...
                if (rowDatabase["table_name"].ToString().IndexOf("TMP") == -1 && rowDatabase["table_name"].ToString().IndexOf("MSys") == -1 && rowDatabase.ItemArray[3].Equals("TABLE"))
                {
                    string tableName = rowDatabase["table_name"].ToString();

                    table tab = new table();
                    tab.Name = tableName;
                    tab.TargetName = tab.Name;
                    lista.Add(tab);

                }

            }

            return lista;

        }
        catch (Exception ep)
        {
            //  lo.tratarError(ep, "Error en dbClass.new", "");
            return null;
        }
        finally
        {
            conexion.Close();
        }
    }