C# System.Data.OleDb.OleDbConnection.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 int AddEvent(String eventName, String eventRoom,
			String eventAttendees, String eventDate)
        {
            System.Data.OleDb.OleDbConnection oleDbConnection1;
            System.Data.OleDb.OleDbDataAdapter daEvents;
            DataSet ds;

            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";
            String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +
                " EventDate) VALUES ('" + eventName + "', '" +
                eventRoom + "', '" + eventAttendees + "', '" +
                eventDate + "')";
            System.Data.OleDb.OleDbCommand insertCommand =
                new System.Data.OleDb.OleDbCommand(oleDbCommand,
                oleDbConnection1);
            oleDbConnection1.Open();

            int queryResult = insertCommand.ExecuteNonQuery();
            if (queryResult == 1)
            {
                daEvents = new System.Data.OleDb.OleDbDataAdapter(
                    "SELECT * FROM Events", oleDbConnection1);
                ds = (DataSet)Application["ds"];
                ds.Tables["Events"].Clear();
                daEvents.Fill(ds, "Events");
                Application.Lock();
                Application["ds"] = ds;
                Application.UnLock();
                oleDbConnection1.Close();
            }
            return queryResult;
        }

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

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

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

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

        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>
        /// 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 static bool AnalyzeExcel(ExcelXMLLayout layout)
        {
            System.Data.OleDb.OleDbConnection conn = null;
            try
            {
                conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));

                conn.Open();
                System.Data.DataTable table = conn.GetOleDbSchemaTable(
                    System.Data.OleDb.OleDbSchemaGuid.Columns,
                    new object[] { null, null, layout.sheet + "$", null });

                layout.Clear();
                System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");

                foreach (System.Data.DataRow row in table.Rows)
                {
                    string name = row["Column_Name"].ToString();

                    System.Diagnostics.Debug.WriteLine(name);

                    // 测试数据类型
                    ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
                    {
                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
                            string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
                            );
                        System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
                        while (r.Read())
                        {
                            System.Diagnostics.Debug.WriteLine(r[0].GetType());
                            if (r[0].GetType() == typeof(System.Double))
                            {
                                testType = ExcelXMLLayout.KeyType.Integer;
                                break;
                            }
                            if (testType == ExcelXMLLayout.KeyType.String)
                            {
                                break;
                            }
                            testType = ExcelXMLLayout.KeyType.String;
                        }
                        r.Close();
                        cmd.Dispose();
                    }

                    layout.Add(name, testType);
                }
                table.Dispose();
                conn.Close();

                return true;
            }
            catch (Exception outErr)
            {
                lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
            }
            return false;
        }

        private String[] GetExcelSheetNames(string excelFile, bool blnXlsx = false)
        {
            System.Data.OleDb.OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                String connString = null;
                if (blnXlsx)
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
                }
                else
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
                }

                objConn = new System.Data.OleDb.OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

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

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

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i += 1;
                }
                return excelSheets;
            }
            catch (Exception ex)
            {
                throw (new Exception("Cannot Read Excel Sheet Names -" + ex.Message));
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        protected void btnGo_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            string sql = "";
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.OleDb.OleDbDataReader dr;
            System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
            //http://www.c-sharpcorner.com/UploadFile/dchoksi/transaction02132007020042AM/transaction.aspx
            //get this from connectionstrings.com/access
            conn.ConnectionString = txtConnString.Text;
            conn.Open();
            //here I can talk to my db...
            System.Data.OleDb.OleDbTransaction Trans;
            Trans = conn.BeginTransaction(System.Data.IsolationLevel.Chaos);

            try
            {

                comm.Connection = conn;
                comm.Transaction = Trans;
               // Trans.Begin();

                //Console.WriteLine(conn.State);
                sql = txtSQL.Text;
                comm.CommandText = sql;
                if (sql.ToLower().IndexOf("select") == 0)
                {
                    dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        txtresults.Text = dr.GetValue(0).ToString();
                    }
                }
                else
                {
                    txtresults.Text = comm.ExecuteNonQuery().ToString();
                }
                Trans.Commit();
            }
            catch (Exception ex)
            {
                txtresults.Text = ex.Message;
                Trans.Rollback();
            }
            finally
            {

                comm.Dispose();
                conn.Close();
                conn = null;
            }
        }

        static void Main(string[] args)
        {
            using (var cn = new System.Data.OleDb.OleDbConnection(new Connection().ConnectionString))
            {
                cn.Open();

                //  手抜き：第一引数に以下の文字列を渡すことで処理分岐
                switch (args.FirstOrDefault().ToLower())
                {
                    case "select":
                        SelectAll(cn);
                        SelectWithoutClass(cn);
                        SelectWithClass(cn);
                        break;

                    case "insert":
                        Insert(cn);
                        SelectAll(cn);
                        break;

                    case "update":
                        Update(cn);
                        SelectAll(cn);
                        break;

                    case "commit":
                        RunTransaction(cn, hasError: false);
                        SelectAll(cn);
                        break;

                    case "rollback":
                        RunTransaction(cn, hasError: true);
                        SelectAll(cn);
                        break;

                    case "tableadapter":
                        GetIdentityWithTableAdapter();
                        break;

                    case "identity":
                        SelectAll(cn);
                        GetIdentityWithDapper(cn);
                        SelectAll(cn);
                        break;

                    default:
                        break;
                }

                cn.Close();
            }
        }

 public System.Data.DataTable Read(string filename)
 {
     var excelDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
     _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties = Excel 8.0;";
     var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString);
     excelConn.Open();
     var dtProtocol = new System.Data.DataTable { TableName = "Protocol" };
     var excelCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [протокол плавки$]", excelConn);
     excelDataAdapter.SelectCommand = excelCommand;
     excelDataAdapter.Fill(dtProtocol);
     excelConn.Close();
     return dtProtocol;
 }

        public void ConvertExcelToCsv(int worksheetNumber = 1)
        {
            if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
            if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

            // connection string
            var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
            var cnn = new System.Data.OleDb.OleDbConnection(cnnStr);

            // get schema, then data
            var dt = new DataTable();
            try
            {
                cnn.Open();
                var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                string sql = String.Format("select * from [{0}]", worksheet);
                var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                throw e;
            }
            finally
            {
                // free resources
                cnn.Close();
            }

            // write out CSV data
            using (var wtr = new StreamWriter(csvOutputFile))
            {
                foreach (DataRow row in dt.Rows)
                {
                    bool firstLine = true;
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
                        var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                        wtr.Write(String.Format("\"{0}\"", data));
                    }
                    wtr.WriteLine();
                }
            }
        }

        private void btnGo_Click(object sender, EventArgs e)
        {
            lblError.Text = "";
            //open connection to database

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = txtConnString.Text;
            conn.Open();
            // MessageBox.Show(conn.State.ToString());
            //create my command
            System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
            comm.Connection = conn;
            //send the sql through the command
            comm.CommandText = txtSQL.Text;

            //receive the result into a data container.

            System.Data.OleDb.OleDbDataReader dr;
            try
            {

                if (txtSQL.Text.ToUpper().StartsWith("SELECT"))
                {
                    dr = comm.ExecuteReader();
                    System.Data.DataTable dt = new DataTable();
                    dt.Load(dr);
                    Grid1.AutoGenerateColumns = true;
                    //bind the result to the grid

                    Grid1.DataSource = dt;
                }
                else
                {
                    MessageBox.Show(comm.ExecuteNonQuery().ToString());
                }

            }
            catch (Exception ex)
            {

                lblError.Text = ex.Message;
            }
            finally
            {
                conn.Close();

            }
        }