C# OleDbDataAdapter.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;
        }

        public static int SaveRecord(string sql)
        {
            const int rv = 0;
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
                    var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
                    comm.ExecuteNonQuery();

                    var ds = new DataSet();
                    var adapt = new OleDbDataAdapter(cmGetID);
                    adapt.Fill(ds);
                    adapt.Dispose();
                    cmGetID.Dispose();

                    return int.Parse(ds.Tables[0].Rows[0][0].ToString());

                }
            }
            catch (Exception)
            {
            }
            return rv;
        }

        /// <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 ProcessingResult ProcessAccessTableToJsonFile(string connString, string queryBegin, string queryEnd, string table, string file)
    {
      var result = new ProcessingResult();

      Debug.Print("ProcessAccessTableToJsonFile: Processing " + table);
      var last = DateTime.Now;

      var sql = queryBegin + " " + table + " " + queryEnd;
      var dsView = new DataSet();
      var adp = new OleDbDataAdapter(sql, connString);
      adp.Fill(dsView, "AccessData");
      adp.Dispose();
      var tbl = dsView.Tables["AccessData"];

      result.toProcess = tbl.Rows.Count;

      SaveObjToJsonFile(tbl, _folderPath + file + ".json");

      result.modified = tbl.Rows.Count;

      Debug.Print("ProcessAccessTableToJsonFile: Processed " + table);
      var diffFromLast = DateTime.Now - last;
      Debug.Print("TimeToProcess: " + diffFromLast.ToString());

      return result;
    }

        private void frmRConc_Load(object sender, EventArgs e)
        {
            string appPath = ConfigurationSettings.AppSettings["PathBd"];
            if (appPath == "Local")
                appPath = AppDomain.CurrentDomain.BaseDirectory;
            //DataSet ds = new DataSet();
            //ds.Tables.Add("conciliacion");
            OleDbConnection conAcc = new OleDbConnection(capaDb.dbPath());
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from conciliacion", conAcc);

            ds.Tables.Add("conciliacion");
            da.Fill(ds.Tables[0]);
            da.Dispose();
            int c = dt.Rows.Count;
            string num = "", name = "", horaChk = "", horaV = "", coord = "";
            //ds.Tables[0].Columns.Count
            foreach (DataRow dr in dt.Rows)
            {
                num = dr[0].ToString();
                name = dr[1].ToString();
                horaChk = dr[2].ToString();
                horaV = dr[3].ToString();
                coord = dr[4].ToString();
                ds.Tables[0].Rows.Add(new object[] { num, name, horaChk, horaV, coord });
            }
            ReportParameter pOH = new ReportParameter("fechas",_fecha);//"0.00"));
            this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { pOH });

            conciliacionBindingSource.DataSource = ds;
            ds.Dispose();
            this.reportViewer1.RefreshReport();
            dt.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 static DataTable GetData(string strConn, string strSql)
        {
            DataTable dt = new DataTable("td");

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

                OleDbCommand cmd = null;
                OleDbDataAdapter da = null;

                try
                {
                    cmd = new OleDbCommand(strSql, conn);
                    da = new OleDbDataAdapter { SelectCommand = cmd };

                    da.Fill(dt);

                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception("error getting data " + ex.Message);
                }
                finally
                {
                    if (da != null) { da.Dispose(); }
                    if (cmd != null) { cmd.Dispose(); }

                    conn.Close();
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            data3.DataSource = null;
            try
            {

                //System.Data.OleDb.OleDbConnection MyCnn;

                System.Data.DataSet DSet;
                //     '" + sem + "'
                System.Data.OleDb.OleDbDataAdapter MyCmd;
                MyCmd = new System.Data.OleDb.OleDbDataAdapter("select * from mca3 ", con);
                MyCmd.TableMappings.Add("Table", "mca3");
                DSet = new System.Data.DataSet();

                MyCmd.Fill(DSet);

                data3.DataSource = DSet.Tables[0];
                MyCmd.Dispose();

            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.ToString());

            }
        }

        /// <summary>
        /// Parses a csv file.
        /// http://tech.pro/tutorial/803/csharp-tutorial-using-the-built-in-oledb-csv-parser
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static DataTable ParseCSV(string path)
        {
            if (!File.Exists(path))
                return null;

            string full = Path.GetFullPath(path);
            string file = Path.GetFileName(full);
            string dir = Path.GetDirectoryName(full);

            //create the "database" connection string 
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
              + "Data Source=\"" + dir + "\\\";"
              + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

            //create the database query
            string query = "SELECT * FROM " + file;

            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();

            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

            try
            {
                //fill the DataTable
                dAdapter.Fill(dTable);
            }
            catch (InvalidOperationException /*e*/)
            { }

            dAdapter.Dispose();

            return dTable;
        }

        private void btnNew_Click( object sender, EventArgs e )
        {
            if( Program.UserType == UserType.Customer )
                return;

            AddDlg dlg = new AddDlg();
            if( dlg.ShowDialog( this ) != DialogResult.OK )
                return;

            RefillComboBoxes();

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "select top 1 * from WedDress order by [ID] desc";
            cmd.Connection = Program.Database;
            cmd.CommandType = CommandType.Text;

            OleDbDataAdapter oda = new OleDbDataAdapter( cmd);
            DataTable dt = new DataTable();
            oda.Fill( dt );

            dsDress.Tables["WedDress"].Merge( dt );
            dt.Dispose();
            oda.Dispose();
            cmd.Dispose();
        }

        public void UpdateChart()
        {
            try
            {
                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reference.Getdb();
                connection.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT print_report.[ID], print_report.[printer_id], print_report.[overall_pagecount], print_report.[color_pagecount], print_report.[mono_pagecount], print_report.[date_time], base_count.[printer_id], base_count.[overall_pagecount] AS baseo, base_count.[color_pagecount] AS basec, base_count.[mono_pagecount] AS basem, print_report.[overall_pagecount] - [baseo] AS totalo, print_report.[color_pagecount] - [basec] AS totalc, print_report.[mono_pagecount] - [basem] AS totalm FROM print_report INNER JOIN base_count ON format(DateAdd('h',1,base_count.[date_time]),'mm/dd/yyyy hh') = format(print_report.[date_time], 'mm/dd/yyyy hh') WHERE print_report.[printer_id] = " + currentchart + " and base_count.[printer_id] = " + currentchart + " and print_report.[overall_pagecount] > 0 and base_count.[overall_pagecount] > 0 and Format(TimeSerial(Hour(print_report.[date_time]),0,0),'Short Time') <= '" + stop_time + "' and INT(print_report.date_time) >= DateValue('" + start + "') and INT(print_report.date_time) <= DateValue('" + stop + "') and Format(TimeSerial(Hour(base_count.[date_time]),0,0),'Short Time') >= '" + start_time + "';", connection);
                DataTable table = new DataTable();
                adapter.Fill(table);
                adapter.Dispose();
                if (live_tracking_checkbox.Checked)
                {
                    adapter = new OleDbDataAdapter("SELECT print_report.[ID], print_report.[printer_id], print_report.[overall_pagecount], print_report.[color_pagecount], print_report.[mono_pagecount], print_report.[date_time], base_count.[printer_id], base_count.[overall_pagecount] AS baseo, base_count.[color_pagecount] AS basec, base_count.[mono_pagecount] AS basem, print_report.[overall_pagecount] - [baseo] AS totalo, print_report.[color_pagecount] - [basec] AS totalc, print_report.[mono_pagecount] - [basem] AS totalm FROM print_report INNER JOIN base_count ON format(DateAdd('h',1,base_count.[date_time]),'mm/dd/yyyy hh') = format(print_report.[date_time], 'mm/dd/yyyy hh') WHERE print_report.[printer_id] = " + currentchart + " and base_count.[printer_id] = " + currentchart + " and print_report.[overall_pagecount] > 0 and Format(TimeSerial(Hour(print_report.[date_time]),0,0),'Short Time') = '" + DateTime.Now.AddHours(-1).ToString("HH:") + "00" + "' and print_report.date_time > DateValue('" + DateTime.Now + "') and Format(TimeSerial(Hour(base_count.[date_time]),0,0),'Short Time') = '" + start_time + "';", connection);
                    DataTable table2 = new DataTable();
                    adapter.Fill(table2);
                    table.Merge(table2);

                    adapter.Dispose();
                }
                connection.Close();

                printed_pages_chart.DataSource = table;
                printed_pages_chart.ChartAreas[0].AxisX.LabelStyle.Format = "M/d/yy htt";
                printed_pages_chart.Series.Clear();
                printed_pages_chart.Series.Add("Total");
                printed_pages_chart.Series["Total"].XValueMember = "date_time";
                printed_pages_chart.Series["Total"].YValueMembers = "totalo";
                printed_pages_chart.Series["Total"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
                printed_pages_chart.Series["Total"].IsValueShownAsLabel = true;
                printed_pages_chart.Series.Add("Color");
                printed_pages_chart.Series["Color"].XValueMember = "date_time";
                printed_pages_chart.Series["Color"].YValueMembers = "totalc";
                printed_pages_chart.Series["Color"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
                printed_pages_chart.Series["Color"].IsValueShownAsLabel = true;
                printed_pages_chart.Series.Add("Mono");
                printed_pages_chart.Series["Mono"].XValueMember = "date_time";
                printed_pages_chart.Series["Mono"].YValueMembers = "totalm";
                printed_pages_chart.Series["Mono"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
                printed_pages_chart.Series["Mono"].IsValueShownAsLabel = true;

                foreach(System.Windows.Forms.DataVisualization.Charting.Series s in printed_pages_chart.Series)
                {
                    foreach (System.Windows.Forms.DataVisualization.Charting.DataPoint dp in s.Points)
                    {
                        if (dp.YValues[0] == 0)
                        {
                            dp.IsValueShownAsLabel = false;
                        }
                    }

                }
                printed_pages_chart.DataBind();
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);
            }
        }

 //返回dataset类型数据
 public DataSet GetDataSet(string sql)
 {
     OleDbConnection cn = get();
     OleDbDataAdapter dr = new OleDbDataAdapter(sql, cn);
     DataSet ds = new DataSet();
     dr.Fill(ds);
     dr.Dispose();
     cn.Close();
     return ds;
 }

 public string GetDataCell(string SqlMetin)
 {
     DataTable dt = new DataTable();
     OleDbConnection Baglanti = this.Baglan();
     OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
     adapter.Fill(dt);
     adapter.Dispose();
     Baglanti.Dispose();
     Baglanti.Close();
     return dt.Rows[0][0].ToString();
 }

        public DataSet SelectOleDbTable(OleDbConnection conn, string query)
        {
            
            OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            oleDbDataAdapter.SelectCommand = new OleDbCommand(query, conn);
            oleDbDataAdapter.Fill(ds);
            oleDbDataAdapter.Dispose();

            return ds;
        }

 public DataTable GetDataTable(string SqlMetin)
 {
     DataTable dt = new DataTable();
     OleDbConnection Baglanti = this.Baglan();
     OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
     adapter.Fill(dt);
     adapter.Dispose();
     Baglanti.Dispose();
     Baglanti.Close();
     return dt;
 }