C# OleDbConnectionStringBuilder.ToString方法代码示例

 /// <summary>
 /// Initializes a new instance of the <see cref="SqlServerDataProvider"/> class.
 /// </summary>
 /// <param name="connectionString">The connection string.</param>
 internal SqlServerDataProvider(string connectionString)
     : base(connectionString)
 {
     var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(this.ConnectionString);
     var oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder();
     oleDbConnectionStringBuilder.DataSource = sqlConnectionStringBuilder.DataSource;
     oleDbConnectionStringBuilder.Provider = "SQLOLEDB";
     oleDbConnectionStringBuilder["database"] = sqlConnectionStringBuilder.InitialCatalog;
     oleDbConnectionStringBuilder["trusted_connection"] = sqlConnectionStringBuilder.IntegratedSecurity ? "yes" : "no";
     this.oleDbConnectionString = oleDbConnectionStringBuilder.ToString();
 }

 public static OleDbConnection getCon()
 {
     if (conexion == null)
     {
         OleDbConnectionStringBuilder b = new OleDbConnectionStringBuilder();
         b.Provider = "Microsoft.ACE.OLEDB.12.0";
         b.DataSource = "cyberman.accdb";
         conexion = new OleDbConnection(b.ToString());
         conexion.Open();
     }
     return conexion;
 }

        public ExcelReader(string path, bool hasHeaders, bool hasMixedData)
        {
            this.path = path;
            OleDbConnectionStringBuilder strBuilder = new OleDbConnectionStringBuilder();
            strBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
            strBuilder.DataSource = path;
            strBuilder.Add("Extended Properties", "Excel 8.0;"+
                "HDR=" + (hasHeaders ? "Yes" : "No") + ';' +
                "Imex=" + (hasMixedData ? "2" : "0") + ';' +
              "");

            strConnection = strBuilder.ToString();
        }

        /// <summary>
        /// ���� DataSource ���\���f�[�^�\�[�X�ւ̐ڑ�����݂܂��B
        /// </summary>
        /// <returns></returns>
        public override DbConnection getConnection()
        {
            OleDbConnectionStringBuilder connection_builder = new OleDbConnectionStringBuilder();

            //connection_builder.ConnectTimeout = base.loginTimeout;

            connection_builder["Provider"] = OLEDBProviderFactory.provider;
            connection_builder["Persist Security Info"] = base.integrated_security_;
            connection_builder["Data Source"] = base.data_source_;

            OleDbConnection result_connection = new OleDbConnection( connection_builder.ToString() );
            result_connection.InfoMessage += onInfoMessage;

            return result_connection;
        }

 private static void AddTable(OleDbConnectionStringBuilder csb)
 {
     // check if table exists
     var con = new ADODB.Connection();
     con.Open(csb.ToString());
     var db = new ADOX.Catalog();
     db.ActiveConnection = con;
     try
     {
         var table = db.Tables[_logFileProcessor.TableName];
     }
     catch (COMException)
     {
         db.Tables.Append(_logFileProcessor.GetTable());
     }
     finally
     {
         con.Close();
     }
 }

        /*
         * Obtaining the name of the excel sheets and placing them on a list.
         * This method was obtained from: http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order
         */
        public List<string> ListSheetInExcel(string filePath)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;
            sbConnection.DataSource = filePath;

            List<string> listSheet = new List<string>();

            if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
            {
                sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
            }
            else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
            {
                sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
            }

            sbConnection.Add("Extended Properties", strExtendedProperties);

            using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
            {
                conn.Open();
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheet.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            return listSheet;
        }

 /// <summary>
 /// Method: ListSheetInExcel
 /// This method will return a list of worksheets of the Excel file selected by name
 /// </summary>
 /// <returns>List of Worksheets of a single excel file</returns>
 public static List<string> ListSheetInExcel()
 {
     // List<T> for Excel Worksheets
     List<string> listSheet = new List<string>();
     // OleDbConnectionStringBuilder
     OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
     // String variable for ExtendedProperties
     String strExtendedProperties = String.Empty;
     // Set Datasource for Excel Connection
     sbConnection.DataSource = WinPath;
     // Evaluate which version is the current excel file
     // Excel 2003
     if (Version().Equals(ExcelVersion.ExcelVersion2003))
     {
         // Set provider for 2003
         sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
         strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=0;ReadOnly=true";
     }
     // Excel 2007+
     else if (Version().Equals(ExcelVersion.ExcelVersion2007))
     {
         // Set provider for 2007+
         sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
         strExtendedProperties = "Excel 12.0 Xml;HDR=Yes;IMEX=0";
     }
     // Add Extended Properties to Connection String
     sbConnection.Add("Extended Properties", strExtendedProperties);
     // Asign Connection string to static ExcelConnection variable
     ExcelConnection = sbConnection.ToString();
     // Use the current connection
     using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
     {
         // Open current connection
         conn.Open();
         // Get Tables Schema
         DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         // Loop through each row and get the table name
         foreach (DataRow drSheet in dtSheet.Rows)
         {
             // Check wheter row contains '_xlnm#_Filte rDatabase'
             // or sheet name(i.e. sheet name always ends with $ sign
             if (drSheet["TABLE_NAME"].ToString().Contains("$"))
             {
                 if (!drSheet["TABLE_NAME"].ToString().Contains("xlnm"))
                 {
                     // Add table name to List<T>
                     listSheet.Add(drSheet["TABLE_NAME"].ToString());
                 }
             }
         }
     }
     // Return List<T>
     return listSheet;
 }

        private string GetStringConnection(string fullFileName)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;
            sbConnection.DataSource = fullFileName;
            if (Path.GetExtension(fullFileName).Equals(".xls"))//for 97-03 Excel file
            {
                sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
            }
            else if (Path.GetExtension(fullFileName).Equals(".xlsx"))  //for 2007 Excel file
            {
                sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
            }
            sbConnection.Add("Extended Properties", strExtendedProperties);

            return sbConnection.ToString();

        }

        private string GetCSVStringConnection(string fullFileName)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;
            sbConnection.DataSource = fullFileName;
            sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
            strExtendedProperties = "Text;HDR=YES;FMT=Delimited";

            sbConnection.Add("Extended Properties", strExtendedProperties);

            return sbConnection.ToString();

        }

        protected override OleDbConnection GetNativeConnection(string connectionString)
        {
            OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString);
            oleDBCnnStrBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";

            return new OleDbConnection(oleDBCnnStrBuilder.ToString());
        }

        static void Main(string[] args)
        {
            // read in arguments, parse them for easy consumption
            ParseCommandlineArguments(args);

            // setup log processor
            _logFileProcessor = LogFileProcessor.Create(_type);
            _logFileProcessor.LogFiles = _logs.GetFiles(_logFilter);

            // set up logger
            Logger.DiskLog = _executionLog;

            // Print welcome message
            Logger.Info();
            Logger.Info(" ##################################################");
            Logger.Info(" # Apatche Log Files to Access Database Converter #");
            Logger.Info(" #                                                #");
            Logger.Info(" # Version 0.3                                    #");
            Logger.Info(" # By: Egil Hansen (http://egilhansen.com)        #");
            Logger.Info(" ##################################################");
            Logger.Info();
            Logger.Info("Runtime settings:");
            Logger.Info();
            Console.ForegroundColor = ConsoleColor.DarkBlue;
            Logger.Info("  Database File (db) ................... : {0}", _dbFileName.FullName);
            Logger.Info("  Overwrite Existing DB File (overwrite) : {0}", _overwrite.ToString());
            Logger.Info("  Logs Directory (logs) ................ : {0}", _logs.FullName);
            Logger.Info("  Log filter (filter) .................. : {0}", _logFilter);
            Logger.Info("  Log File Type (type) ................. : {0}", _logFileProcessor.Name);
            Logger.Info("  Runetime Log (runtimelog) ............ : {0}", _executionLog != null ? _executionLog.FullName : "n/a");
            Logger.Info();
            Console.ResetColor();

            // create target database based on log file type
            var csb = new OleDbConnectionStringBuilder { DataSource = _dbFileName.FullName, Provider = "Microsoft.Jet.OLEDB.4.0" };

            // remove existing database file
            if (_dbFileName.Exists && _overwrite)
            {
                _dbFileName.Delete();
            }

            // create database
            if (_dbFileName.Exists)
            {
                AddTable(csb);
            }
            else
            {
                CreateAccessDatabase(csb.ToString());
            }

            // parse each log file, add each log entry to database
            Logger.Info("Starting log file processing . . .");
            Logger.LogOffscreen(string.Empty);
            Logger.LogOffscreen("Start time: {0}", DateTime.Now);
            Logger.Info();

            _logFileProcessor.Process(csb.ToString());
        }

        /// <summary>
        /// Gets table schema information about an OLE database
        /// </summary>
        /// <returns>DataTable with schema information</returns>
        //This function has been fixed in parent class, It should work for all database type  //  zack 1/30/08
        //public override DataTable GetTableSchema()
        //{
        //DataTable schema = base.GetSchema("Tables");
        //foreach (DataRow row in schema.Rows)
        //{
        //    // TODO: This isn't asbolute, should be replaced with search on...
        //    // exact system table names
        //    if (row[ColumnNames.SCHEMA_TABLE_NAME].ToString().StartsWith("MSys", true, CultureInfo.InvariantCulture))
        //    {
        //        row.Delete();
        //    }
        //}
        //schema.AcceptChanges();
        //    return schema;
        //}
        public static string BuildConnectionString(string filePath, string password)
        {
            StringBuilder builder = new StringBuilder();

            if (filePath.EndsWith(".accdb", true, System.Globalization.CultureInfo.InvariantCulture))
            {
                builder.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=");
            }

            builder.Append(EncodeOleDbConnectionStringValue(filePath));
            builder.Append(";");

            builder.Append("User Id=admin;Password=;");

            if (!string.IsNullOrEmpty(password))
            {
                builder.Append("Jet OLEDB:Database Password=");
                builder.Append(EncodeOleDbConnectionStringValue(password));
                builder.Append(";");
            }

            // sanity check
            OleDbConnectionStringBuilder connectionBuilder = new OleDbConnectionStringBuilder(builder.ToString());
            return connectionBuilder.ToString();
        }

        /// <summary>
        /// Extracts file location and replaces relative file paths with full file paths by updating ConnectionString value
        /// </summary>
        protected void ProcessConnectionString()
        {
            this.location = null;
            if (this.connectionString == null) return;

            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(this.connectionString);

            string filename = builder.DataSource;
            if (File.Exists(filename))
            {
                if (!Path.IsPathRooted(filename))
                {
                    filename = Path.GetFullPath(filename);
                }
                this.location = filename;
                this.DbName = Path.GetFileNameWithoutExtension(this.location);
                this.connectionString = builder.ToString();
            }
        }

        private DataTable loadExcelFile(string fileName, string sheetName)
        {
            OleDbConnection objConn = null;
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            sbConnection.DataSource = fileName;
            String strExtendedProperties = Path.GetExtension(fileName);
            try
            {
               DataTable table = new DataTable();
               if (Path.GetExtension(fileName).Equals(".xls"))//for 97-03 Excel file
                  {
                      sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                      strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
                  }
                  else if (Path.GetExtension(fileName).Equals(".xlsx"))  //for 2007 Excel file
                  {
                      sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                      strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                  }
                  sbConnection.Add("Extended Properties", strExtendedProperties);
                  objConn = new OleDbConnection(sbConnection.ToString());
                  objConn.Open();
                  using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConn)) //rename sheet if required!
                      dbAdapter.Fill(table);

                if (table == null)
                {
                    return null;
                }
                creator.loadData(table);
                return table;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        private String[] SheetNames(string fileName)
        {
            String[] Sheets = null;
            OleDbConnection objConn = null;
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            sbConnection.DataSource = fileName;
            String strExtendedProperties = Path.GetExtension(fileName);
            try
            {
                if (Path.GetExtension(fileName).Equals(".xls") || Path.GetExtension(fileName).Equals(".xlsx"))//any excel sheet
                {
                    if (Path.GetExtension(fileName).Equals(".xls"))//for 97-03 Excel file
                    {
                        sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                        strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
                    }
                    else if (Path.GetExtension(fileName).Equals(".xlsx"))  //for 2007 Excel file
                    {
                        sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                        strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                    }
                    sbConnection.Add("Extended Properties", strExtendedProperties);
                    objConn = new OleDbConnection(sbConnection.ToString());
                    objConn.Open();
                    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    if (dt == null)
                    {
                        return null;
                    }

                    Sheets = new String[dt.Rows.Count];
                    int i = 0;

                    // Add the sheet name to the string array.
                    foreach (DataRow row in dt.Rows)
                    {
                        if (row["TABLE_NAME"].ToString().Contains("$")
                            && !row["TABLE_NAME"].ToString().Contains("$Print")
                            && !row["TABLE_NAME"].ToString().Contains("$'Print")
                             && !row["TABLE_NAME"].ToString().Contains("$_"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                        {
                            Sheets[i] = row["TABLE_NAME"].ToString();
                            //Sheets[i] = Sheets[i].Replace("$", "");
                            //Sheets[i] = Sheets[i].Replace("'", "");
                            i++;
                        }
                    }
                }
                return Sheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }