C# OleDbConnection.Dispose方法代码示例

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

        //导入excel代码
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog of = new OpenFileDialog();
            if (of.ShowDialog() == DialogResult.OK)
            {
                string filepath = of.FileName;
                FileInfo fi = new FileInfo(filepath);
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                DataSet ds = new DataSet();
                using (OleDbConnection con = new OleDbConnection(strCon))
                {
                    con.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", con);
                    adapter.Fill(ds, "dtSheet1");
                    con.Close();
                    con.Dispose();
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
                {
                    string number = ds.Tables[0].Rows[i]["编号"].ToString();
                    string name = ds.Tables[0].Rows[i]["姓名"].ToString();
                    string attendance = ds.Tables[0].Rows[i]["出勤次数"].ToString();
                    string absence = ds.Tables[0].Rows[i]["缺勤次数"].ToString();
                    string sql = "insert into teacher(number,name,chu,que) values ('" + number + "','" + name + "','" + attendance + "','" + absence + "')";
                    int j=DBConnection.OperateData(sql);
                    if (j==0)
                    {
                        MessageBox.Show("插入数据失败");
                        return;
                    }
                }
                  MessageBox.Show("插入数据成功！");

            }
        }

        /// <summary>
        /// Executes an inline SQL statement and returns a data table.
        /// </summary>
        /// <param name="dbStatement">Inline SQL</param>
        /// <param name="connectionString">Connection string</param>
        /// <returns>Data table containing the return data</returns>
        public static DataTable RunQuery(string dbStatement, string connectionString)
        {
            OleDbConnection dbConnection = null;
            OleDbCommand dbCommand = null;
            OleDbDataAdapter adapter = null;
            DataTable dt = null;

            try
            {
                dbConnection = new OleDbConnection(connectionString);
                dbCommand = new OleDbCommand(dbStatement, dbConnection);
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandTimeout = 600;

                adapter = new OleDbDataAdapter(dbCommand);
                dt = new DataTable();

                dbConnection.Open();
                adapter.Fill(dt);
                return dt;
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (dbCommand != null)
                    dbCommand.Dispose();
                if (dbConnection != null)
                    dbConnection.Dispose();
            }
        }

        public List<SheetInfo> GetSheetlist()
        {
            if (excelpath == null||excelpath.Length<=0)
                return null;
            List<SheetInfo> list = new List<SheetInfo>();
            string connStr = "";
            string sql_F = "Select * FROM [{0}]";
            string fileType = System.IO.Path.GetExtension(excelpath);
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbDataAdapter da = null;
            try
            {
                conn.Open();
                string sheetname = "";
                DataTable dtSheetName = 
                    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count;i++ )
                {
                    sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                    if (sheetname.Contains("$") )
                    {
                        SheetInfo info = new SheetInfo();
                        info.SheetName = sheetname.Replace("$", "");

                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn);
                        DataSet dsItem = new DataSet();
                        da.Fill(dsItem, sheetname);
                        int cnum = dsItem.Tables[0].Columns.Count;
                        int rnum = dsItem.Tables[0].Rows.Count;
                        info.StartRange = "A1";
                        char c = (char)('A' + cnum - 1);
                        info.EndRange = c + Convert.ToString(rnum);
                        list.Add(info);
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误消息");
                return null; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    if(da!=null)
                        da.Dispose();
                    conn.Dispose();
                }
            }

            return list;
        }

    public bool DeleteContentType(string id)
    {
        String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id);

        String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id);

        OleDbConnection conn = new OleDbConnection(StrConn);
        OleDbTransaction transaction = null;
        bool flag = false;
        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction);
            contentCommand.ExecuteNonQuery();

            OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction);
            typeCommand.ExecuteNonQuery();

            transaction.Commit();
            flag = true;
        }
        catch (Exception ex)
        {
            flag = false;
            transaction.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return flag;
    }

        private void button1_Click(object sender, EventArgs e)
        {
            string query = string.Empty;


            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            try
            {
                conn.Open();
                query = "INSERT INTO About(Name, Version , FromDate , ToDate ,IdeaFr , Email) VALUES('" + textBox1.Text + "','1.0.0.2','unlimited', 'unlimited','" + textBox2.Text + "')";
                OleDbCommand sqlCommand = new OleDbCommand(query, conn);
                int i = sqlCommand.ExecuteNonQuery();
                //button1.Items.Add(textBox1);
               // button1.Items.Add(textBox2);
                
                MessageBox.Show("Cám ơn bạn đã góp ý cho chúng tôi");
                MessageBox.Show("Mọi ý kiến của bạn sẽ được lắng nghe giúp phần mềm phát triển ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string b = RID;// Request["QUERY_STRING"];
        string a = Session["mzj"].ToString().Trim();

        if (a == "没中奖")
        {

            string url = Request.Url.ToString();
            Response.Redirect(url);
        }
        else
        {

            string PathDataBase = System.IO.Path.Combine(Server.MapPath("~"), "USER_DIR\\SYSUSER\\LotteryTicket\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" +
            String source = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase;

            OleDbConnection con = new OleDbConnection(source);
            //2、打开连接 C#操作Access之按列读取mdb
            con.Open();

            DbCommand com = new OleDbCommand();
            com.Connection = con;
            com.CommandText = "select jp from wx_ggk_jp where name='" + b + "'";
            DbDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                string jp = reader["jp"].ToString();
                bd.Style["Display"] = "None";//转盘层
                csyw.Style["Display"] = "None";//次数用完次数
                zjl.Style["Display"] = "Block";//中奖页面
                zjjs.Style["Display"] = "None";//中奖结束页面

                Label3.Text = "恭喜你以中奖" + jp + "请填写相关信息";

                con.Close();
            }
            else
            {

                OleDbCommand comd = new OleDbCommand();
                //连接字符串
                comd.CommandText = "insert into wx_ggk_jp (name,jp,sjh) values ('" + b + "','" + a + "','手机号')";
                comd.Connection = con;

                //执行命令，将结果返回
                int sm = comd.ExecuteNonQuery();
                con.Close();
                //释放资源
                con.Dispose();

                bd.Style["Display"] = "None";//转盘层
                csyw.Style["Display"] = "None";//次数用完次数
                zjl.Style["Display"] = "Block";//中奖页面
                zjjs.Style["Display"] = "None";//中奖结束页面

                Label3.Text = "恭喜你以中奖" + a + "请填写相关信息";
            }
        }
    }

        // TODO : Gestion du fichier Excel
        // - Import du fichier Excel en entier dans la base de données MERCURE
        // - Lecture du fichier Excel
        // - Accéder à un élément précis
        public static void ReadExcel(string path, string sheet, int nbColumns)
        {
            String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
                                  + "Data Source=" + path + ";"
                                  + "Extended Properties='Excel 8.0;HDR=YES'";

            OleDbConnection connExcel = new OleDbConnection(strExcelConn);
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "$]", connExcel);

            try
            {
                connExcel.Open();
                OleDbDataReader dataReader = cmd.ExecuteReader();
                while (dataReader != null && dataReader.Read())
                {
                    for (int i = 0; i < nbColumns && i < dataReader.FieldCount-1; i++)
                    {
                        Console.Write("{0} || ", dataReader.GetString(i));
                    }
                    Console.WriteLine("");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connExcel.Dispose();
            }
        }

        public DataTable ExecuteQuery(string sql)
        {
            conn = new OleDbConnection(connection);
            var results = new DataTable();

            try
            {
                conn.Open();

                OleDbDataAdapter adapter = null;

                using (adapter = new OleDbDataAdapter(CreateCommand(sql)))
                {
                    adapter.Fill(results);
                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return results;
        }

 private void button1_Click(object sender, EventArgs e)
 {
     string query = string.Empty;
     string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
     string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
     OleDbConnection conn = new OleDbConnection(strCnn);
     try
     {
         conn.Open();
         folderBrowserDialog1.ShowDialog();
         query = "INSERT INTO BlockFolder(LinkFolder, Status) VALUES('" + folderBrowserDialog1.SelectedPath + "','bat')";
         Directory.Move(folderBrowserDialog1.SelectedPath, folderBrowserDialog1.SelectedPath + ".{20D04FE0-3AEA-1069-A2D8-08002B30309D}");
         // tren day là lệnh khóa máy folder à ? dung r a
         //truyền vào là cái gì ? truyen vao day ky tu do do'a
         OleDbCommand sqlCommand = new OleDbCommand(query, conn);
         int i = sqlCommand.ExecuteNonQuery();
         listView1.Items.Add(folderBrowserDialog1.SelectedPath);
         MessageBox.Show("Đã Nhập Vào Folder Cần Chặn !!!");
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.StackTrace);
     }
     finally
     {
         conn.Close();
         conn.Dispose();
     }
 }

    public String[] GetFirstnames()
    {
        List<string> items = new List<string>();
        string strSQL = "";

        OleDbDataReader objReader = null;
        OleDbConnection objConn = null;
        OleDbCommand objCommand = null;

        strSQL = "SELECT firstname from Employees ORDER BY firstname";
        objConn = new OleDbConnection(GetconnectstringLocal());
        objConn.Open();
        objCommand = new OleDbCommand(strSQL, objConn);
        objReader = objCommand.ExecuteReader();
        while (objReader.Read())
        {

            items.Add(objReader["firstname"].ToString());

        }
        objReader.Close(); objReader.Dispose(); objReader = null;
        objCommand.Dispose(); objCommand = null;
        objConn.Close(); objConn.Dispose(); objConn = null;
        return items.ToArray();
    }

        private void button2_Click(object sender, EventArgs e)
        {
            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            if (MessageBox.Show("Bạn có muốn xóa bản ghi này không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
            {
                try
                {
                    conn.Open();
                    string query = " Delete From LimitTime Where Hours ='" + listView2.SelectedItems[0].Text + "'";
                    MessageBox.Show("Đã xóa bản ghi thành công", "Thông báo", MessageBoxButtons.OK);

                    OleDbCommand sqlCommand = new OleDbCommand(query, conn);
                    sqlCommand.ExecuteNonQuery();
                    listView2.Items.RemoveAt(listView1.SelectedIndices[0]);

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Không thể xóa bản ghi này!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    MessageBox.Show(ex.Message);
                    conn.Close();
                    conn.Dispose();
                    return;
                }
            }
            return;
        }

    protected void btnlogout_Click(object sender, EventArgs e)
    {
        conlogout = i.GetOledbDbConnection();
        cmdlogout = i.GetOledbDbCommand();
        try
        {
            conlogout = DBConnection.GetConnection();
            cmdlogout.Connection = conlogout;
            cmdlogout.CommandType = CommandType.StoredProcedure;
            cmdlogout.CommandText = "Logout";
            cmdlogout.Parameters.AddWithValue("@USERNAME",s);
            int result = cmdlogout.ExecuteNonQuery();
            if (result == 1)
            {
               // Response.Write("logout successfull");
                Response.Redirect("Login.aspx");
                Session.Clear();
            }
            else
                Response.Write("logout unsuccessfull");

        }
        catch (Exception ex)
        {

            Response.Write("logout unsuccessfull"); 
        }
        finally
        {
            cmdlogout.Dispose();
            conlogout.Dispose();


        }
    }

 public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
 {
     try
     {
         string tem_sql = "";//定义字符串
         string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句
         System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库
         System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
         tem_conn.Open();//打开连接的Access数据库
         tem_sql = "select Count(*) From " + AccTable;//设置SQL语句，获取记录个数
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句，并返回结果
         //每个Sheet只能最多保存65536条记录。
         tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";//记录连接Excel的语句
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         tem_comm.ExecuteNonQuery();//执行SQL语句，将数据表的内容导入到Excel中
         tem_conn.Close();//关闭连接
         tem_conn.Dispose();//释放资源
         tem_conn = null;
         MessageBox.Show("导入完成");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message,"提示！");
     }
 }

        private void button2_Click(object sender, EventArgs e)
        {

            
            string query = string.Empty;


            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            try
            {
                conn.Open();
                query = "INSERT INTO NameChildren(FirstName, LastName , PhoneNumber) VALUES('" + textBox3.Text + "','" + textBox2.Text + "','" + textBox1.Text + "')";
                OleDbCommand sqlCommand = new OleDbCommand(query,conn);
                int i = sqlCommand.ExecuteNonQuery();
                MessageBox.Show("Đã insert [" + i.ToString() + "] dữ liệu");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            
        }