Exemple #1
1
        //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;
        }
Exemple #2
0
        public static DataTable GetDataTable(IGTDataContext oDC, string sSQL, GTDiagnostics _diag)
        {
            DataTable dt = null;

            try
            {
                //IGTDataContext oDC = GTClassFactory.Create<IGTApplication>().DataContext;
                Recordset        oRS = oDC.OpenRecordset(sSQL, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int)CommandTypeEnum.adCmdText);
                OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
                dt = new DataTable();
                oDA.Fill(dt, oRS);
                oDA.Dispose();
                oRS.Close();
            }
            catch (Exception ex)
            {
                if (_diag.IsEnabled(GTDiagCat.EE))
                {
                    _diag.LogException("CommandUtilities.GetDataTable", ex);
                }
                throw ex;
            }

            return(dt);
        }
        /// <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;
    }
Exemple #5
0
        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;
        }
Exemple #6
0
        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());

            }
        }
Exemple #8
0
        private DataTable LoadWorksheet(string worksheetName)
        {
            OleDbConnection  connection = new System.Data.OleDb.OleDbConnection(_ConnectionString);
            OleDbDataAdapter cmd        = null;

            try
            {
                cmd = new System.Data.OleDb.OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", worksheetName), connection);
                connection.Open();

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

                return(dt);
            }
            catch
            {
                return(null);
            }
            finally
            {
                if (connection != null)
                {
                    try { connection.Dispose(); }
                    catch { }
                }
                if (cmd != null)
                {
                    try { cmd.Dispose(); }
                    catch { }
                }
            }
        }
Exemple #9
0
        /// <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;
        }
Exemple #10
0
        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();
        }
Exemple #11
0
        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();
        }
Exemple #12
0
        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);
            }
        }
Exemple #13
0
 //返回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 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;
 }
Exemple #15
0
        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 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 GetDataSet(string SqlMetin)
 {
     DataSet ds = new DataSet();
     OleDbConnection Baglanti = this.Baglan();
     OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
     adapter.Fill(ds);
     adapter.Dispose();
     Baglanti.Dispose();
     Baglanti.Close();
     return ds;
 }
 DataSet GetDataSet(string sql)
 {
     DBConnection dbconn = new DBConnection();
     OleDbConnection connection = dbconn.getConnection();
     connection.Open();
     OleDbCommand cmd = new OleDbCommand(sql, connection);
     OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
     DataSet ds = new DataSet();
     dataAdapter.Fill(ds);
     cmd.Dispose();
     dataAdapter.Dispose();
     return ds;
 }
    public string FillLegal()
    {
        string tbl = "admin";
        string sqlStr = "SELECT * FROM " + tbl;
        string legal = string.Empty;

        try
        {
            OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
            OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
            OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
            cnn.Open();
            OleDbDataReader drr = cmd.ExecuteReader();

            DataSet ds = new DataSet();

            oda.Fill(ds, "admin");

            while (drr.Read())
            {
                legal = EncDec.Decrypt(drr["legal"].ToString(), Base.hashKey);
                break;
            }

            drr.Close();
            cnn.Close();

            cmd.Dispose();
            drr.Dispose();
            ds.Dispose();
            oda.Dispose();
            cnn.Dispose();

            cmd = null;
            drr = null;
            ds = null;
            oda = null;
            cnn = null;
        }
        catch
        {
            legal = errInvalidLegal;
        }
        finally
        {
            tbl = null;
            sqlStr = null;
        }

        return legal;
    }
Exemple #20
0
 protected void Page_Load(object sender, EventArgs e)
 {
     string sql = "SELECT * FROM [user_details] where [ID] = " + Request.QueryString["ID"];
     DataSet ds = new DataSet();
     DBConnection dbconn = new DBConnection();
     OleDbConnection connection = dbconn.getConnection();
     connection.Open();
     OleDbCommand cmd = new OleDbCommand(sql, connection);
     OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
     dataAdapter.Fill(ds);
     cmd.Dispose();
     dataAdapter.Dispose();
     showuser.DataSource = ds;
     showuser.DataBind();
 }
Exemple #21
0
        public DataSet GetWorkplace()
        {
            DataSet workplace;

            OleDbConnection connection = new OleDbConnection(strConnection);
            OleDbDataAdapter adaptor = new OleDbDataAdapter("SELECT * FROM *", connection);
            workplace = new DataSet();
            adaptor.FillSchema(workplace, SchemaType.Source);
            adaptor.Fill(workplace);

            adaptor.Dispose();
            connection.Close();

            return workplace;
        }
Exemple #22
0
        //파일에서 불러오기
        private void buttonAddByFile_Click(object sender, EventArgs e)
        {
            //xls파일 불러와서 테이블에 저장
            string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data.xls;Extended Properties='Excel 8.0;HDR=No'";
            DataTable dTable = new DataTable();
            int rCnt=0;

            //SQL문을 통한 OleDb로 자료 읽기
            try
            {
                OleDbConnection oleDbCon = new OleDbConnection(strConnection);
                oleDbCon.Open();
                string strSQL = "SELECT * FROM [Course$]";
                OleDbCommand dbCommand = new OleDbCommand(strSQL, oleDbCon);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

                dataAdapter.Fill(dTable);
                dTable.Dispose();
                dataAdapter.Dispose();
                dbCommand.Dispose();
                oleDbCon.Close();
                oleDbCon.Dispose();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }

            //데이터그리드뷰에 데이터 입력
            try
            {
                foreach (DataRow row in dTable.Rows)
                {
                    int cnt = 0;
                    pRO1COURSESBindingSource.AddNew();
                    foreach (DataColumn col in dTable.Columns)
                    {
                        dataGridViewMakeCrs.Rows[rCnt].Cells[cnt].Value = dTable.Rows[rCnt].ItemArray[cnt];
                        cnt++;
                    }
                    rCnt++;
                }
            }
            catch (NoNullAllowedException ex)
            {
                MessageBox.Show("완성되지 않은 행이 있습니다. 삭제 후 다시 시도해주세요.");
            }
        }
Exemple #23
0
 public static DataSet GetDataExcel(string fPath, string sheetname)
 {
     DataSet ds = new DataSet();
     string connectionstring = OpenExcelFile(fPath);
     string query = "SELECT * FROM [" + sheetname + "$]";
     using (OleDbConnection cnn = new OleDbConnection(connectionstring))
     {
         cnn.Open();
         OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, cnn);
         oleAdapter.Fill(ds, sheetname);
         oleAdapter.Dispose();
         cnn.Close();
         cnn.Dispose();
     }
     return ds;
 }
Exemple #24
0
 public static DataTable GetDataTable(Recordset oRS)
 {
     try
     {
         OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
         DataTable        oDT = new DataTable();
         oDA.Fill(oDT, oRS);
         oDA.Dispose();
         oRS.Close();
         return(oDT);
     }
     catch (Exception oEx)
     {
         throw oEx;
     }
 }
Exemple #25
0
        public DataTable getData(string foxFile)
        {
            OleDbConnection cnn = null;
            OleDbDataAdapter dataadapter = null;
            OleDbCommand cmd = null;
            DataSet dataset = null;
            DataTable T =null;
            try
            {
                cnn = new System.Data.OleDb.OleDbConnection();
                cmd = new OleDbCommand();
                dataadapter = new System.Data.OleDb.OleDbDataAdapter();
                dataset = new DataSet();
                cnn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + foxFile.Trim();

                if (cnn.State == System.Data.ConnectionState.Closed)
                {
                    cnn.Open();
                }

                cmd.Connection = cnn;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select * from " + foxFile.Trim();
                dataadapter.SelectCommand = cmd;
                dataadapter.Fill(dataset);

                T = dataset.Tables[0];

                dataset.Dispose();
                dataadapter.Dispose();
                cmd.Dispose();
                cnn.Close();
                Message = "Done...";
            }
            catch (Exception e)
            {
                Message = e.Message;
            }
            finally
            {
                dataadapter = null;
                dataset = null;
                cnn = null;
                cmd = null;
            }
            return T;
        }
Exemple #26
0
 private DataTable GetDataTable(IGTDataContext oDC, Recordset oRS)
 {
     try
     {
         //  Recordset oRS = oDC.OpenRecordset(sSQL, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int)CommandTypeEnum.adCmdText);
         OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
         DataTable        oDT = new DataTable();
         oDA.Fill(oDT, oRS);
         oDA.Dispose();
         oRS.Close();
         return(oDT);
     }
     catch (Exception oEx)
     {
         throw oEx;
     }
 }
 public DataSet GetDataSet(string sql)
 {
     OleDbConnection baglanti = this.baglan();
     OleDbDataAdapter adapter = new OleDbDataAdapter(sql, baglanti);
     DataSet ds = new DataSet();
     try
     {
         adapter.Fill(ds);
     }
     catch (OleDbException ex)
     {
         throw new Exception(ex.Message + " (" + sql + ")");
     }
     adapter.Dispose();
     baglanti.Close();
     baglanti.Dispose();
     return ds;
 }
 public static System.Data.DataSet DataSet(string sqlText, OleDbParameter[] parms)
 {
     System.Data.DataSet dataSet = new System.Data.DataSet();
     OleDbDataAdapter adapter = new OleDbDataAdapter(sqlText, connectionString)
     {
         SelectCommand = { CommandType = CommandType.Text }
     };
     if (parms != null)
     {
         foreach (OleDbParameter parameter in parms)
         {
             adapter.SelectCommand.Parameters.Add(parameter);
         }
     }
     adapter.Fill(dataSet, "DataList");
     adapter.SelectCommand.Parameters.Clear();
     adapter.Dispose();
     return dataSet;
 }
Exemple #29
0
        private void button3_Click(object sender, EventArgs e)
        {
            DatabaseManager dbManager = DatabaseManager.dataBaseManager();
            dbManager.BookConn = new OleDbConnection(dbManager.getConnParam());
            dbManager.BookConn.Open();
            dataGridView2.DataSource = null;
            dataGridView2.Rows.Clear();
            dataGridView2.Refresh();

            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from Rezervasyon WHERE RezervasyonKodu = '" + textBox3.Text + "';", dbManager.BookConn);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();

            dAdapter.Fill(dataTable);
            dataGridView2.DataSource = dataTable;
            dAdapter.Dispose();
            dbManager.BookConn.Close();
        }
 public static void ExecuteQuery(string query)
 {
     try
     {
         DataTable dt = new DataTable();
         OleDbConnection conn = new OleDbConnection(ConnectionString);
         OleDbCommand cmd = new OleDbCommand(query, conn);
         OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
         conn.Open();
         adapter.Fill(dt);
         adapter.Dispose();
         cmd.Dispose();
         conn.Close();
     }
     catch (Exception e)
     {
         MessageBox.Show("DbError: " + e.Message + "");
     }
 }
Exemple #31
0
        private void btnSearch_Click(object sender, EventArgs e)
        {
            OleDbConnection conAcc = new OleDbConnection(capaDb.dbPath());
            try
            {
                string fechas = "", turno = "", empleado = "", depto = "", codOp = "",voucher="",linea="";
                if (!cbNoDate.Checked)
                    fechas = "and (IndiceVoucher.Fecha Between #" + date1.Value.ToShortDateString() +
                        "# And #" + date2.Value.ToShortDateString() + "#) ";
                if (tbVoucher.Text != "")
                    voucher = " AND IndiceVoucher.novoucher= " + tbVoucher.Text;
                //if (tbTurno.Text != "")
                //    turno = " AND IndiceVoucher.Turno= " +tbTurno.Text;
                if (tbEmp.Text != "")
                    empleado = " AND EmpVoucher.Empleado="+ tbEmp.Text;
                //if (tbLinea.Text != "")
                //    linea = " AND DetVoucher.linea='" + tbLinea.Text+"'";
                if (tbCo.Text != "")
                    codOp = " AND DetVoucher.CodigoOperacion='" + tbCo.Text+"'";

                string sql = "SELECT IndiceVoucher.novoucher,IndiceVoucher.Fecha, IndiceVoucher.Turno, EmpVoucher.Empleado, " +
                    "DetVoucher.Empezo,DetVoucher.Termino,DetVoucher.Horas,DetVoucher.CodigoOperacion,DetVoucher.Linea," +
                    "DetVoucher.Eficiencia " +
                    "FROM (IndiceVoucher INNER JOIN EmpVoucher ON IndiceVoucher.NoVoucher = EmpVoucher.NoVoucher) INNER JOIN DetVoucher ON IndiceVoucher.NoVoucher = DetVoucher.NoVoucher " +
                    "WHERE IndiceVoucher.operadores > 0 " + fechas + turno + empleado + linea + codOp+voucher;
                conAcc.Open();
                OleDbDataAdapter da2 = new OleDbDataAdapter(sql, conAcc);
                DataTable dt2 = new DataTable();
                da2.Fill(dt2);
                dgResult.DataSource = dt2;
                dt2.Dispose();
                da2.Dispose();
            }
            catch(Exception err)
            {
                MessageBox.Show("Error en la busqueda:"+err.Message);
            }
            finally
            {
                conAcc.Close();
                conAcc.Dispose();
            }
        }
    protected void Page_Load(object sender, EventArgs e)
    {
        OleDbConnection Connection = new OleDbConnection();
        Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Page.Server.MapPath("../App_Data/db.mdb") + ";";
        Connection.Open();

        if (Page.IsPostBack)
        {
            //
            //   Here one can save postbacked HTML content (editor.Content)
            //
            OleDbCommand myCommand;
            string sqlString = "Update tbTest Set fldContent=? Where id=?";
            myCommand = new OleDbCommand(sqlString, Connection);

            myCommand.Parameters.Add(new OleDbParameter("@fldContent", OleDbType.VarChar));
            myCommand.Parameters["@fldContent"].Value = editor.Content;

            myCommand.Parameters.Add(new OleDbParameter("@id", OleDbType.Integer));
            myCommand.Parameters["@id"].Value = 1;

            myCommand.ExecuteNonQuery();
        }
        else
        {
            //
            //   Here one can set initial HTML content of Editor
            //
            string sqlString = "Select fldContent from tbTest Where id=1";
            OleDbDataAdapter eAdapter = new OleDbDataAdapter(sqlString, Connection);
            DataTable eTable = new DataTable();
            OleDbCommandBuilder CommandBuilder = new OleDbCommandBuilder(eAdapter);

            eAdapter.Fill(eTable);
            editor.Content = (string)eTable.Rows[0][0];

            eAdapter.Dispose();
            eTable.Dispose();
        }

        Connection.Close();
        Connection.Dispose();
    }
Exemple #33
0
        public static DataTable GetTable(string sql)
        {
            using (var conn = new OleDbConnection(_dbConnectionString))
            {
                var da = new OleDbDataAdapter(sql, conn);
                var dt = new DataTable();
                try
                {
                    da.Fill(dt);

                    return dt;
                }
                finally
                {
                    da.Dispose();
                    conn.Dispose();
                }
            }
        }
        public static DataTable readData()
        {
            try {
                string _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GlobalData.initSetting.BOSAREPORT + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(_connectionString)) {
                    DataTable dt = new DataTable();
                    conn.Open();
                    OleDbDataAdapter objDA        = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$A8:H]", conn);
                    DataSet          excelDataSet = new DataSet();
                    objDA.Fill(excelDataSet);
                    dt = excelDataSet.Tables[0];

                    excelDataSet.Dispose();
                    objDA.Dispose();
                    conn.Dispose();
                    return(dt);
                }
            } catch (Exception ex) {
                System.Windows.MessageBox.Show(ex.ToString(), "Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
                return(null);
            }
        }
    public static bool ExportToXLSX(string sheetToCreate, List <DataRow> selectedRows, System.Data.DataTable origDataTable, string tableName)
    {
        bool status = false;

        System.Data.OleDb.OleDbConnection cn = new OleDbConnection();
        try
        {
            char   Space = ' ';
            string dest  = sheetToCreate;


            if (File.Exists(dest))
            {
                File.Delete(dest);
            }

            sheetToCreate = dest;

            if (tableName == null)
            {
                tableName = string.Empty;
            }

            tableName = tableName.Trim().Replace(Space, '_');
            if (tableName.Length == 0)
            {
                tableName = origDataTable.TableName.Replace(Space, '_');
            }

            if (tableName.Length == 0)
            {
                tableName = "NoTableName";
            }

            if (tableName.Length > 30)
            {
                tableName = tableName.Substring(0, 30);
            }

            //Excel names are less than 31 chars
            string queryCreateExcelTable         = "CREATE TABLE [" + tableName + "] (";
            Dictionary <string, string> colNames = new Dictionary <string, string>();

            foreach (DataColumn dc in origDataTable.Columns)
            {
                //Cause the query to name each of the columns to be created.
                string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
                string origColName     = dc.ColumnName;
                colNames.Add(modifiedcolName, origColName);

                queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
            }

            queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";

            //adds the closing parentheses to the query string
            if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
            {
                //use Excel 2007 for large sheets.
                sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
            }

            string strCn = string.Empty;
            string ext   = System.IO.Path.GetExtension(sheetToCreate).ToLower();
            if (ext == ".xls")
            {
                strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
            }
            if (ext == ".xlsx")
            {
                strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
            }
            if (ext == ".xlsb")
            {
                strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
            }
            if (ext == ".xlsm")
            {
                strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
            }

            cn = new System.Data.OleDb.OleDbConnection(strCn);
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            System.Data.OleDb.OleDbDataAdapter    da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
            System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);

            //creates the INSERT INTO command
            cb.QuotePrefix = "[";
            cb.QuoteSuffix = "]";
            cmd            = cb.GetInsertCommand();

            //gets a hold of the INSERT INTO command.
            foreach (DataRow row in selectedRows)
            {
                foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
                {
                    param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];
                }

                cmd.ExecuteNonQuery(); //INSERT INTO command.
            }

            cn.Close();
            cn.Dispose();
            da.Dispose();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            status = true;
        }
        catch (Exception ex)
        {
            status = false;
            if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
        }
        return(status);
    }
        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;
        }