//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 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; }
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()); } }
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 { } } } }
/// <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(); }
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 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 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; }
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; }
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(); }
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; }
//파일에서 불러오기 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("완성되지 않은 행이 있습니다. 삭제 후 다시 시도해주세요."); } }
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; }
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; } }
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; }
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; }
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 + ""); } }
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(); }
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; }