C# OleDb.OleDbConnection类代码示例

        private void buttonInfoMessage_Click(object sender, EventArgs e)
        {

            /**** Create this Store Procedure in the tempdb database first ****
                        Create Proc TestInfoMessage
                        as
                        Print 'Using a Print Statement'
                        RaisError('RaisError in Stored Procedures', 9, 1)
            ****************************************************/

            //1. Make a Connection
            System.Data.OleDb.OleDbConnection objOleCon = new System.Data.OleDb.OleDbConnection();
            objOleCon.ConnectionString = strConnection;
            objOleCon.Open();

            objOleCon.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(objOleCon_InfoMessage);

            //2. Issue a Command
            System.Data.OleDb.OleDbCommand objCmd;
            objCmd = new System.Data.OleDb.OleDbCommand("Raiserror('Typical Message Goes Here', 9, 1)", objOleCon);
            objCmd.ExecuteNonQuery();
            objCmd = new System.Data.OleDb.OleDbCommand("Exec TestInfoMessage", objOleCon); //This executes the stored procedure.
            objCmd.ExecuteNonQuery();

            //3. Process the Results
            /** No Results at this time **/

            //4. Clean up code
            objOleCon.Close();
        }

        protected void Application_Start(Object sender, EventArgs e)
        {
            System.Data.DataSet ds;
            System.Data.OleDb.OleDbConnection oleDbConnection1;
            System.Data.OleDb.OleDbDataAdapter daAttendees;
            System.Data.OleDb.OleDbDataAdapter daRooms;
            System.Data.OleDb.OleDbDataAdapter daEvents;

            oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
            oleDbConnection1.Open();
            ds = new DataSet();
            daAttendees = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Attendees", oleDbConnection1);
            daRooms = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Rooms", oleDbConnection1);

            daEvents = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Events", oleDbConnection1);
            daAttendees.Fill(ds, "Attendees");
            daRooms.Fill(ds, "Rooms");
            daEvents.Fill(ds, "Events");
            oleDbConnection1.Close();

            Application["ds"] = ds;
        }

        /// <summary>
        /// 取得 OLEDB 的连接字符串.
        /// 优先启动 ACE 驱动，
        /// 假如 ACE 失败，再尝试启动 JET
        /// 
        /// 该方法可能用不上。
        /// 因为 在 Office 2010 上面，Jet 与 ACE 都能正常运作
        /// 
        /// 唯一需要注意的是， 如果目标机器的操作系统，是64位的话。
        /// 项目需要 编译为 x86， 而不是简单的使用默认的 Any CPU.
        /// </summary>
        /// <param name="excelFileName"></param>
        /// <returns></returns>
        public static string GetOleDbConnectionString(string excelFileName)
        {

            // Office 2007 以及 以下版本使用.
            string jetConnString =
              String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFileName);

            // xlsx 扩展名 使用.
            string aceConnXlsxString =
              String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"", excelFileName);

            // xls 扩展名 使用.
            string aceConnXlsString =
              String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", excelFileName);


            // 默认非 xlsx
            string aceConnString = aceConnXlsString;


            if (excelFileName.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase))
            {
                // 如果扩展名为 xlsx.
                // 那么需要将 驱动切换为 xlsx 扩展名 的.
                aceConnString = aceConnXlsxString;
            }

            // 尝试使用 ACE. 假如不发生错误的话，使用 ACE 驱动.
            try
            {
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(aceConnString);
                cn.Open();
                cn.Close();
                // 使用 ACE
                return aceConnString;
            }
            catch (Exception)
            {
                // 启动 ACE 失败.
            }


            // 尝试使用 Jet. 假如不发生错误的话，使用 Jet 驱动.
            try
            {
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(jetConnString);
                cn.Open();
                cn.Close();
                // 使用 Jet
                return jetConnString;
            }
            catch (Exception)
            {
                // 启动 Jet 失败.
            }


            // 假如 ACE 与 JET 都失败了，默认使用 JET.
            return jetConnString;
        }

 public System.Collections.Generic.List<string> GetColumnNames(string file, string table)
 {
     System.Data.DataTable dataSet = new System.Data.DataTable();
     string connString = "";
     if (Global.filepassword != "")
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
     }
     else
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;";
     }
     using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString))
     {
         connection.Open();
         System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table, connection);
         using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command))
         {
             dataAdapter.Fill(dataSet);
         }
     }
     System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>();
     for (int i = 0; i < dataSet.Columns.Count; i++)
     {
         columns.Add(dataSet.Columns[i].ColumnName);
     }
     return columns;
 }

        public void write_log_file(Form1 f,String type_of_network)
        {
            this.f = f;
            int time = f.time;
            Console.Write("Time in excel : " + time);
            Console.Write("Selected network : " + type_of_network);
            String network_type = make_network_string(type_of_network);
            nodes = Convert.ToString(f.no_of_nodes);
            String connections = make_connections_string();
            if (System.IO.File.Exists("log.xls"))
                file_exists = true;
             //       MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\ABHISHEK\\ExcelData1.xls;;Extended Properties=Excel 8.0;");
            MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source = log.xls;;Extended Properties=Excel 8.0;");
            MyConnection.Open();
            myCommand.Connection = MyConnection;
            if (!file_exists)
            {
                Console.Write("does not exist");
                sql = "CREATE TABLE Simulation (TypeofNetwork String, Nodes int, Packets int,Seeder_Nodes int,Seeder_Packets int,Download_edges int,Upload_edges int,Ratio float,Thresh_Probability float,Nodes_row int,Connections String,Alturists int,Traders int,Parasites int,Time_taken int)";

                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();

            }
            sql = "INSERT INTO [Simulation$] (TypeofNetwork, Nodes, Packets,Seeder_Nodes,Seeder_Packets,Download_edges,Upload_edges,Ratio,Thresh_Probability,Nodes_row,Connections,Alturists,Traders,Parasites,Time_taken) values (" + " ' " + network_type + " ' " + "," + nodes + "," + f.no_of_packets.ToString() + "," + f.seeder_nodes.ToString() + "," + f.seeder_packets.ToString() + "," + f.no_of_download_edges.ToString() + "," + f.no_of_upload_edges.ToString() + "," + f.ratio.ToString() + "," + (f.threshold_probability / 100.0).ToString() + "," + f.widthstep.ToString() + "," + "'" + connections +"'" + "," +f.alturists.ToString() + "," + f.traders.ToString() + "," + f.parasites.ToString()+"," +time.ToString()+ ")";

            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();
            MyConnection.Close();
        }

            public void DBSetup()
            {
                // +++++++++++++++++++++++++++  DBSetup function +++++++++++++++++++++++++++
                // This DBSetup() method instantiates all the DB objects needed to access a DB, 
                // including OleDbDataAdapter, which contains 4 other objects(OlsDbSelectCommand, 
                // oleDbInsertCommand, oleDbUpdateCommand, oleDbDeleteCommand.) And each
                // Command object contains a Connection object and an SQL string object.
                OleDbDataAdapter2 = new System.Data.OleDb.OleDbDataAdapter();
                OleDbSelectCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbInsertCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbUpdateCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbDeleteCommand2 = new System.Data.OleDb.OleDbCommand();
                OleDbConnection2 = new System.Data.OleDb.OleDbConnection();

                OleDbDataAdapter2.DeleteCommand = OleDbDeleteCommand2;
                OleDbDataAdapter2.InsertCommand = OleDbInsertCommand2;
                OleDbDataAdapter2.SelectCommand = OleDbSelectCommand2;
                OleDbDataAdapter2.UpdateCommand = OleDbUpdateCommand2;

                // The highlighted text below should be changed to the location of your own database

                OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Reg" + "istry Path =; Jet OLEDB:Database L" +
                "ocking Mode=1;Data Source= C:\\Users\\Trenton MCleod\\Desktop\\RegistrationDB.mdb;J" +
                "et OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System datab" +
                "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" +
                "hare Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet " +
                "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repai" +
                "r=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";

            }  //end DBSetup()

        /// <summary>
        ///创建excel数据源连接 
        /// </summary>
        /// <param name="strFileName">文件路径名</param>
        /// <param name="DbConn">返回数据源连接</param>
        /// <returns></returns>
        public static bool CreateConnection(string strFileName, ref IDbConnection DbConn)
        {
            bool bolReturn = false;
            string strConnectString = string.Empty;

            if (string.IsNullOrEmpty(strFileName))
            {
                return false;
            }
            if (!System.IO.File.Exists(strFileName))
            {
                return false;
            }
            strConnectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\";Data Source={0};", strFileName);
            try
            {
                //实例化连接
                DbConn = new System.Data.OleDb.OleDbConnection();
                DbConn.ConnectionString = strConnectString;
                DbConn.Open();
                bolReturn = true;
            }
            catch (Exception exp)
            {
                Hy.Common.Utility.Log.OperationalLogManager.AppendMessage(exp.ToString());

            }
            return bolReturn;
        }

 /// <summary>
 /// M�todo necesario para admitir el Dise�ador, no se puede modificar
 /// el contenido del m�todo con el editor de c�digo.
 /// </summary>
 private void InitializeComponent()
 {
     System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
     this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
     this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
     this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
     this.datosInforme1 = new informe_1.datosInforme();
     ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).BeginInit();
     //
     // oleDbDataAdapter1
     //
     this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
     this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                 new System.Data.Common.DataTableMapping("Table", "Table", new System.Data.Common.DataColumnMapping[] {
                                                                                                                                                                                                          new System.Data.Common.DataColumnMapping("NOMBRE", "NOMBRE")})});
     //
     // oleDbSelectCommand1
     //
     this.oleDbSelectCommand1.CommandText = "SELECT \'\' AS NOMBRE FROM DUAL";
     this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
     //
     // oleDbConnection1
     //
     this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string))));
     //
     // datosInforme1
     //
     this.datosInforme1.DataSetName = "datosInforme";
     this.datosInforme1.Locale = new System.Globalization.CultureInfo("es-ES");
     this.datosInforme1.Namespace = "http://www.tempuri.org/datosInforme.xsd";
     this.Load += new System.EventHandler(this.Page_Load);
     ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).EndInit();
 }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
            Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/products.mdb");
            Conn.Open();
            //Response.Write(Conn.State);

            string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)";
            object returnVal;

            System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
            Comm.Connection = Conn;

            Comm.CommandText = "select [username] from [users] where [username] = ?";
            Comm.Parameters.AddWithValue("[username]", txtUserName.Text);
            returnVal = Comm.ExecuteScalar();
            if (returnVal == null)
            {
                Comm.Parameters.Clear();
                Comm.CommandText = strSQL;
                Comm.Parameters.AddWithValue("username", txtUserName.Text);
                Comm.Parameters.AddWithValue("firstname", txtFName.Text);
                Comm.Parameters.AddWithValue("lastname", txtLName.Text);
                Comm.Parameters.AddWithValue("email", txtEmail.Text);
                Comm.Parameters.AddWithValue("password", txtPassword.Text);
                Comm.ExecuteNonQuery();
            }
            else {
                Response.Write("Username already exists.");
            }
            Conn.Close();
            Conn = null;
        }

        /// <summary>
        /// Create access database file
        /// </summary>
        public static void create_mdb_file(DataSet ds, string destination, string template)
        {
            if(SqlConvert.ToString(template) == "")
                throw new Err("You must specify the location of a template mdb file inherit from.");
            if(!System.IO.File.Exists(template))
                throw new Err("The specified template file \"" + template + "\" could not be found.");
            if(SqlConvert.ToString(destination) == "")
                throw new Err("You must specify the destination location and filename of the mdb file to create.");

            //COPY THE TEMPLATE AND CREATE DESTINATION FILE
            System.IO.File.Copy(template,destination,true);
            //CONNECT TO THE DESTINATION FILE
            string sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destination + ";";
            System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection(sconn);
            oconn.Open();
            System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
            ocmd.Connection = oconn;
            //WRITE TO THE DESTINATION FILE
            try
            {
                oledb.insert_dataset(ds,ocmd);
            }
            catch(Exception ex)
            {
                //System.Web.HttpContext.Current.Response.Write(ex.ToString());
                General.Debugging.Report.SendError("Error exporting to MS Access", ex.ToString() + "\r\n\r\n\r\n" + ocmd.CommandText);
            }
            finally
            {
                // Close the connection.
                oconn.Close();
            }
        }

 public DataTable SelectToDataTable(string sql)
 {
     //set connection
     System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
     con.ConnectionString = connectString;
     //set commandtext
     System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
     command.CommandText = sql;
     command.Connection = con;
     //set adapter
     System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
     adapter.SelectCommand = command;
     //creat a datatable
     DataTable dt = new DataTable();
     try
     {
         //open this connection
         con.Open();
         adapter.Fill(dt);
     }
     catch (Exception ex)
     {
         //throw new Exception
         con.Close();
     }
     finally
     {
         //close this connection
         con.Close();
     }
     //return a datatable
     return dt;
 }

        public Form1()
        {
            InitializeComponent();
            comboBox2.Text = "Column";
            comboBox2.Items.Add("Column");
            comboBox2.Items.Add("Lines");
            comboBox2.Items.Add("Pie");
            comboBox2.Items.Add("Bar");
            comboBox2.Items.Add("Funnel");
            comboBox2.Items.Add("PointAndFigure");

            comboBox1.Items.Clear();
            if (System.IO.File.Exists("your_base.mdb"))
            {
                string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
                System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
                connectDb.Open();
                DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow row in cbTb.Rows)
                {
                    string tbName = row["TABLE_NAME"].ToString();
                    comboBox1.Items.Add(tbName);
                }
                connectDb.Close();
            }
        }

        // The following methods handles SQL Server, Oledb, Odbc
        public static IDbConnection CreateConnection(EnumProviders provider)
        {
            IDbConnection cnn;

            switch (provider)
            {
                case EnumProviders.SqlServer:
                    cnn = new System.Data.SqlClient.SqlConnection();
                    break;
                case EnumProviders.OleDb:
                    cnn = new System.Data.OleDb.OleDbConnection();
                    break;
                case EnumProviders.Odbc:
                    cnn = new System.Data.Odbc.OdbcConnection();
                    break;
                case EnumProviders.Oracle:
                    throw new NotImplementedException("Provider not implemented");
                    break;
                default:
                    cnn = new System.Data.SqlClient.SqlConnection();
                    break;
            }

            return cnn;
        }

 /// <summary>
 /// M�todo necesario para admitir el Dise�ador, no se puede modificar
 /// el contenido del m�todo con el editor de c�digo.
 /// </summary>
 private void InitializeComponent()
 {
     this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
     this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
     this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
     this.dsFicha1 = new ficha_antecedentes_personales.dsFicha();
     ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).BeginInit();
     //
     // oleDbConnection1
     //
     this.oleDbConnection1.ConnectionString = "Provider=SQLOLEDB;server=edoras;OLE DB Services = -2;uid=protic;pwd=,.protic;init" +
         "ial catalog=protic2";
     //
     // oleDbSelectCommand1
     //
     this.oleDbSelectCommand1.CommandText = @"select '' as nombre, '' as rut, '' as pasaporte, '' as fecha_nac, '' as fono, '' as nacionalidad, '' as Estado_civil, '' as Direccion, '' as comuna, '' as ciudad, '' as region, '' as colegio_egreso, '' as ano_egreso, '' as proced_educ, '' as inst_educ_sup, '' as Carrera, '' as ano_ingr, '' as FinanciaEst, '' as ultimo_post_ncorr, '' as nombre_sost_ec, '' as RUT_sost_ec, '' as fnac_sost_ec, '' as edad_sost, '' as fono_sost_ec, '' as pare_sost_ec, '' as dire_tdesc_sost_ec, '' as comu_sost_ec, '' as ciud_sost_ec, '' as regi_sost_ec";
     this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
     //
     // oleDbDataAdapter1
     //
     this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
     //
     // dsFicha1
     //
     this.dsFicha1.DataSetName = "dsFicha";
     this.dsFicha1.Locale = new System.Globalization.CultureInfo("es-CL");
     this.dsFicha1.Namespace = "http://www.tempuri.org/dsFicha.xsd";
     this.Load += new System.EventHandler(this.Page_Load);
     ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).EndInit();
 }

 /// <summary> Private constructor ... checks system properties for connection 
 /// information
 /// </summary>
 private NormativeDatabase()
 {
     //UPGRADE_ISSUE: Method 'java.lang.System.getProperty' was not converted. "ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?index='!DefaultContextWindowIndex'&keyword='jlca1000_javalangSystem'"
     _connectionString = ca.uhn.Properties.Settings.Default.ConnectionString;
     _conn = new System.Data.OleDb.OleDbConnection(_connectionString);
     _conn.Open();
 }