public DataSet Get_Propietarios_Gestion(int GestionId) { try { if (ds.Tables["DATOS"] != null) { ds.Tables.Remove("DATOS"); } cn.Open(); OleDbCommand cmd = new OleDbCommand("SP_Get_Propietarios_Gestion", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@GestionId", OleDbType.Integer).Value = GestionId; OleDbDataAdapter adp = new OleDbDataAdapter(cmd); adp.Fill(ds, "DATOS"); cn.Close(); return(ds); } catch (Exception ex) { cn.Close(); DataSet ds = new DataSet(); return(ds); } }
static public DataTable importItemList() { try { using (OleDbConnection conn = new OleDbConnection()) { string path = dirExecutable + @"\Data\MasterItemList.xlsx"; DataTable dt = new DataTable(); conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"; using (OleDbCommand comm = new OleDbCommand()) { comm.CommandText = "Select * from [MasterItemList$]"; comm.Connection = conn; using (OleDbDataAdapter da = new OleDbDataAdapter()) { da.SelectCommand = comm; da.Fill(dt); conn.Close(); conn.Dispose(); return(dt); } } } } catch (Exception e) { DialogResult result = MessageBox.Show(@"The item list for market prices was not cloned properly. It is in the Github under " + "/DOA Tracker v3/Data and should be put into the same folder in the executable directory; The " + "template DOA.accdb file should be located in there as well. Do you want to view the error log?", "File Missing!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { MessageBox.Show(e.ToString()); } return(null); } }
public DataTable LoadDataTable(string connectionString, string sql) { if (MyLoadDataTable != null) { return(MyLoadDataTable(connectionString, sql)); } DataTable table = new DataTable(); DbDataAdapter adapter = null; var connection = Helper.DbConnectionFromConnectionString(connectionString); connection.Open(); if (connection is OdbcConnection) { adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection); } else { adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection); } adapter.SelectCommand.CommandTimeout = SelectTimeout; adapter.Fill(table); return(table); }
public DataSet Persona_Juridica_Get(int UsuarioId) { try { if (ds.Tables["DATOS"] != null) { ds.Tables.Remove("DATOS"); } cn.Open(); OleDbCommand cmd = new OleDbCommand("SP_Persona_Juridica_GET", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UsuarioId", OleDbType.Integer).Value = UsuarioId; OleDbDataAdapter adp = new OleDbDataAdapter(cmd); adp.Fill(ds, "DATOS"); cn.Close(); return(ds); } catch (Exception ex) { cn.Close(); DataSet ds = new DataSet(); return(ds); } }
public void loadround() { ivRound.Items.Clear(); try { loaddb.checkdb(); string loadr = "SELECT * FROM DBRunRound "; OleDbCommand cmd = new OleDbCommand(loadr, loaddb.cnn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = new DataTable(); da.Fill(ds, "DBRunRound"); loaddb.cnn.Close(); dt = ds.Tables["DBRunRound"]; for (int i = 0; i < dt.Rows.Count; i++) { ivRound.Items.Add(dt.Rows[i]["IDRR"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RTime"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["IDCard"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RName"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RLName"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RPhone"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RPST"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RNCar"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["Round"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RTicket"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RMoney"].ToString()); ivRound.Items[i].SubItems.Add(dt.Rows[i]["RCheck"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message, "ตรวจพบข้อผิดพลาด"); } }
private void search_box_Click(object sender, EventArgs e) { count = 0; con.Open(); OleDbCommand cmd = con.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = " select Name, Location, Weight, Bgroup, Gender, Phone from Table1 where Bgroup = '" + bg.Text + "' and Location = '" + loc.Text + "' "; cmd.ExecuteNonQuery(); DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); count = Convert.ToInt32(dt.Rows.Count.ToString()); dataGridView1.DataSource = dt; con.Close(); if (count == 0) { MessageBox.Show("Record is not found"); } }
public void DataKelas() { OleDbConnection koneksi = new OleDbConnection(db); koneksi.Open(); string query = "SELECT KELAS FROM vw_DATA_JADWAL WHERE NIP = '" + labelNIP.Text + "' GROUP BY KELAS"; OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = query; cmd.Connection = koneksi; OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { comboBox_LihatJadwalKelas.Items.Add(dt.Rows[i]["KELAS"].ToString()); comboBox_InputNilaiKelas.Items.Add(dt.Rows[i]["KELAS"].ToString()); comboBox_KelasDataNilaiSiswa.Items.Add(dt.Rows[i]["KELAS"].ToString()); } koneksi.Close(); }
private void BillProducts_Load(object sender, EventArgs e) { //MessageBox.Show(billno); OleDbDataAdapter ad4 = new OleDbDataAdapter("Select billno as Bill_No,bdate as Bill_Date,pname as Products,PSIZE AS P_SIZE,PCOLOR AS P_COLOR,QPC AS Q_P_CASE,PCASE AS P_CASE,pqty as T_QTY, pqtype as Unit,prate as Rate,pamount as Amount,bid from bill where billno=@billno and pamount>0 order by bid", con); ad4.SelectCommand.Parameters.Add("@billno", OleDbType.VarChar).Value = billno; DataSet ds4 = new DataSet(); ad4.Fill(ds4); dataGridView1.DataSource = ds4.Tables[0]; dataGridView1.Columns[0].Width = 50; dataGridView1.Columns[1].Width = 80; dataGridView1.Columns[2].Width = 100; dataGridView1.Columns[3].Width = 50; dataGridView1.Columns[4].Width = 75; dataGridView1.Columns[5].Width = 65; dataGridView1.Columns[6].Width = 65; //dataGridView1.Columns[7].Width = 75; //dataGridView1.Columns[8].Width = 75; //dataGridView1.Columns[9].Width = 35; //dataGridView1.Columns[10].Width = 75; //dataGridView1.Columns[11].Width = 75; //dataGridView1.Columns[12].Width = 75; }
private void button3_Click(object sender, EventArgs e) { OleDbConnection DB1 = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = apteka.mdb"); DB1.Open(); OleDbCommand drop_command = new OleDbCommand("delete from " + comboBox1.SelectedItem + " where " + dataGridView1.Columns[0].HeaderText + "=" + dataGridView1.SelectedCells[0].Value.ToString(), DB1); if (drop_command.ExecuteNonQuery() == 0) { MessageBox.Show("Ошибка в запросе!"); } DB1.Close(); OleDbConnection DB = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = apteka.mdb"); DB.Open(); OleDbDataAdapter DA = new OleDbDataAdapter("select * from " + comboBox1.SelectedItem, DB); DataTable DT = new DataTable(); DA.Fill(DT); dataGridView1.DataSource = DT; DB.Close(); }
private void comboBox_InputNilaiKelas_SelectedIndexChanged(object sender, EventArgs e) { comboBox_InputNilaiMapel.Items.Clear(); OleDbConnection koneksi = new OleDbConnection(db); koneksi.Open(); string query = "SELECT NAMA_PELAJARAN FROM vw_DATA_JADWAL WHERE NIP = '" + labelNIP.Text + "' AND KELAS = '" + comboBox_InputNilaiKelas.Text + "' GROUP BY NAMA_PELAJARAN"; OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = query; cmd.Connection = koneksi; OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { comboBox_InputNilaiMapel.Items.Add(dt.Rows[i]["NAMA_PELAJARAN"].ToString()); } string query_2 = "SELECT NIS, NAMA_LENGKAP, KELAS FROM MASTER_SISWA WHERE KELAS='" + comboBox_InputNilaiKelas.Text + "' ORDER BY NAMA_LENGKAP ASC"; OleDbCommand cmd_2 = new OleDbCommand(); cmd_2.CommandText = query_2; cmd_2.Connection = koneksi; OleDbDataAdapter da_2 = new OleDbDataAdapter(cmd_2); DataTable dt_2 = new DataTable(); da_2.Fill(dt_2); dataGridView_InputNilai_Jadwal.DataSource = dt_2; koneksi.Close(); }
private void FillServiceList() { string queryText = "SELECT ID,servicename FROM services ORDER BY servicename"; OleDbDataAdapter adapter = new OleDbDataAdapter(queryText, connectionString); DataSet dataset = new DataSet(); adapter.Fill(dataset, "services"); ServicesDataGrid.DataSource = dataset.Tables["services"].DefaultView; ServicesDataGrid.Columns["ID"].Visible = false; ServicesDataGrid.Columns["servicename"].Width = 120; ServicesDataGrid.Columns["servicename"].DefaultCellStyle.BackColor = Color.LightGray; ServicesDataGrid.Columns["servicename"].ReadOnly = true; ServicesDataGrid.Columns.Add("quantity", ""); ServicesDataGrid.Columns["quantity"].Width = 50; ServicesDataGrid.Columns.Add("unit", ""); ServicesDataGrid.Columns["unit"].Width = 50; ServicesDataGrid.Columns["unit"].DefaultCellStyle.BackColor = Color.LightGray; ServicesDataGrid.Columns["unit"].ReadOnly = true; for (int i = 0; i < ServicesDataGrid.RowCount; i++) { ServicesDataGrid[2, i].Value = "0"; ServicesDataGrid[3, i].Value = "шт."; } }
public bool OledbRead(string filePath) { //office 2007 or 2013 string strCon = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0;'"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(strCon)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表] ole_cmd.CommandText = "select * from [Sheet1$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd); DataSet ds = new DataSet(); adapter.Fill(ds, "Sheet1"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Console.WriteLine(ds.Tables[0].Rows[i]["Name"].ToString());//注意:列名不区分大小写 } } } return(true); }
private void Sorgula_Click(object sender, EventArgs e) { OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = vt.mdb"); baglanti.Open(); DataSet dt1 = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter("Select tarih,urunadi,fiyat,miktar from fatura where tarih between @tarih1 and @tarih2 and alici='" + Program.kullaniciadi + "'", baglanti); da.SelectCommand.Parameters.AddWithValue("@tarih1", dateTimePicker1.Value.ToString("d.MM.yyyy")); da.SelectCommand.Parameters.AddWithValue("@tarih2", dateTimePicker2.Value.ToString("d.MM.yyyy")); da.Fill(dt1, "veri"); dataGridView1.DataSource = dt1.Tables["veri"]; baglanti.Close(); //Tabloların isimlerini düzenle dataGridView1.Columns[0].HeaderText = "Tarih"; dataGridView1.Columns[1].HeaderText = "Satılan Ürün"; dataGridView1.Columns[2].HeaderText = "Birim Fiyatı"; dataGridView1.Columns[3].HeaderText = "Ürün Miktarı"; }
public void veriler() { if (secilenislem == 0) { baglan.Open(); OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where ad='" + txtad.Text + "' and soyad='" + txtsoyad.Text + "'", baglan); adp.Fill(tablo); baglan.Close(); ad = tablo.Rows[0][0].ToString(); soyad = tablo.Rows[0][1].ToString(); kul_ad = tablo.Rows[0][2].ToString(); posta = tablo.Rows[0][4].ToString(); } if (secilenislem == 1) { baglan.Open(); OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where kullanici_adi='" + txtkulad.Text + "' ", baglan); adp.Fill(tablo); baglan.Close(); ad = tablo.Rows[0][0].ToString(); soyad = tablo.Rows[0][1].ToString(); kul_ad = tablo.Rows[0][2].ToString(); posta = tablo.Rows[0][4].ToString(); } if (secilenislem == 2) { baglan.Open(); OleDbDataAdapter adp = new OleDbDataAdapter("select * from ADMIN where eposta='" + txteposta.Text + "'", baglan); adp.Fill(tablo); baglan.Close(); ad = tablo.Rows[0][0].ToString(); soyad = tablo.Rows[0][1].ToString(); kul_ad = tablo.Rows[0][2].ToString(); posta = tablo.Rows[0][4].ToString(); } }
public static dsPersonnel GetPersonnel(string Database, string strSearch) { dsPersonnel DS; OleDbConnection sqlConn; OleDbDataAdapter sqlDA; // Instantiation of OleDbConnection 'sqlConn' sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Database); // Instantiation of OledbDataAdapter 'sqlDA' if (strSearch == null || strSearch.Trim() == "") { sqlDA = new OleDbDataAdapter("select * from tblPersonnel", sqlConn); } else { sqlDA = new OleDbDataAdapter("select * from tblPersonnel where LastName = '" + strSearch + "'", sqlConn); } // Instantiation of dsUserActivity 'DS' DS = new dsPersonnel(); //The 'Fill' method of 'sqlDA' class used with a parameter to access the table in the dataset sqlDA.Fill(DS.tblPersonnel); // The method will return information from the UserActivity Dataset return(DS); }
/// find user method // used to find the user in the database public dsProgramaholics FindUser(string LastName) { // sql statements to find user in datbase /*string sqlStmt = "SELECT * " + * "FROM tblUserDetails " + * "LEFT JOIN tblProgram ON (tblUserDetails.id = tblProgram.id) " + * "LEFT JOIN tblCompleted ON (tblUserDetails.id = tblCompleted.id)" + * " WHERE tblUserDetails.LastName like '" + LastName + "';"; */ string sqlStmt = "SELECT * FROM tblUserDetails, tblProgram, tblCompleted WHERE tblUseDetails.id = tblProgram.id and tblUseDetails.id = tblCompleted.id and tblUserDetails.LasName like ‘“ + LastName + “‘"; OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection); // new dataset to store dsProgramaholics myStoreData = new dsProgramaholics(); sqlDataAdapter.Fill(myStoreData.tblUserDetails); sqlDataAdapter.Fill(myStoreData.tblPrograms); sqlDataAdapter.Fill(myStoreData.tblCompleted); // return dataset return(myStoreData); }
public static DataSet GetRandomSong() { OleDbConnection conn = OpenConnection(); Random random = new Random((int)DateTime.Now.Ticks); String getLastID = "SELECT TOP 1 TrackID FROM tblSong ORDER BY TrackID DESC"; OleDbCommand cmd = new OleDbCommand(getLastID, conn); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); int lastID = Convert.ToInt32(reader["TrackID"]); int randomID = random.Next(1, lastID); String strSQL = "SELECT TOP 1 TrackID, TrackTitle, TrackFilePath, tblAlbum.AlbumID, tblAlbum.AlbumTitle, tblAlbum.AlbumArtPath, " + "tblArtist.ArtistID, tblArtist.ArtistName FROM (tblSONG INNER JOIN tblAlbum ON tblAlbum.AlbumID = tblSong.AlbumID) " + "INNER JOIN tblArtist ON tblArtist.ArtistID = tblSong.ArtistID WHERE TrackID = " + randomID + ";"; DataSet randomSong = new DataSet(); OleDbDataAdapter randomAdapter = new OleDbDataAdapter(strSQL, conn); randomAdapter.Fill(randomSong, "randomSet"); CloseConnection(conn); return(randomSong); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { (Page.Master.FindControl("ProductsClassTreeView") as TreeView).ExpandDepth = 0; string strConnection = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open(); using (objConnection) { string _sql = "SELECT * FROM XXTable"; OleDbDataAdapter da = new OleDbDataAdapter(_sql, objConnection); DataSet ds = new DataSet(); //填充DataSet da.Fill(ds, "nb"); DataTable _dt = ds.Tables["nb"]; strContactInf = _dt.Rows[0]["LXFS"].ToString(); AboutUsInfLabel.Text = _dt.Rows[0]["GSXJ"].ToString(); JoinUsLabel.Text = _dt.Rows[0]["ZPXX"].ToString(); if (Request.QueryString["class"] == "joinus") { stitle.Text = "招聘信息"; JoinUsLabel.Visible = true; AboutUsInfLabel.Visible = false; bannerImage.ImageUrl = "~/images/b/jobs.jpg"; } else { stitle.Text = "公司简介"; AboutUsInfLabel.Visible = true; JoinUsLabel.Visible = false; bannerImage.ImageUrl = "~/images/b/aboutus.jpg"; } } } }
public DataTable Query(string sql, params object[] parameters) { DataTable table = new DataTable(); //try { var cmd = new OleDbCommand(sql, connection); var adp = new OleDbDataAdapter(cmd); if (parameters != null) { foreach (var prm in parameters) { cmd.Parameters.Add(new OleDbParameter() { Value = prm }); } } adp.Fill(table); try{} catch (Exception e) { table.Columns.Add("ERROR"); table.Rows.Add(e.Message); } return(table); }
public DataSet GetDataTable(string strQuery) { DataSet tempDs = null; string filePath = Server.MapPath("Download\\" + excelFileUpload.FileName); { string conPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\""; OleDbConnection conn = new OleDbConnection(conPath); try { conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn); tempDs = new DataSet(); adapter.Fill(tempDs); } catch (Exception ex) { Response.Write("<h4>" + ex.Message); } conn.Close(); } return(tempDs); }
private void updateLogDb() { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand("SELECT * FROM LogTable", connection); OleDbDataAdapter Adapter = new OleDbDataAdapter(command); try { connection.Open(); OleDbCommandBuilder cbClientOrder = new OleDbCommandBuilder(Adapter); Adapter.Update(Local_LogTable); connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } } }
private void FillComboBox() { OleDbConnection _connector = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\oksan\Desktop\AISforC#.mdb"); DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); try { _connector.Open(); OleDbCommand cmd1 = new OleDbCommand("select product_ID,product_name from Order_lines group by product_id, product_name", _connector); OleDbDataAdapter da1 = new OleDbDataAdapter(); da1.SelectCommand = cmd1; da1.Fill(ds1); comboBox2.DisplayMember = "product_name"; comboBox2.ValueMember = "product_ID"; comboBox2.DataSource = ds1.Tables[0]; OleDbCommand cmd2 = new OleDbCommand("select order_id from Orders group by order_ID", _connector); OleDbDataAdapter da2 = new OleDbDataAdapter(); da2.SelectCommand = cmd2; da2.Fill(ds2); comboBox1.DisplayMember = "order_ID"; comboBox1.ValueMember = "order_ID"; comboBox1.DataSource = ds2.Tables[0]; } catch (Exception e) { MessageBox.Show("ERROR"); } finally { _connector.Close(); _connector.Dispose(); } }
public void delete(Form3 f, int found) { OleDbConnection conn = new OleDbConnection(); String s = System.Environment.CurrentDirectory; string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; connStr += @s + @"//StudentManager.mdb"; // Console.WriteLine("当前连接字符串为:\n" + connStr + "\n"); conn.ConnectionString = connStr; string strSelectQuery = "select * from students"; OleDbCommand cmd = new OleDbCommand(strSelectQuery, conn); OleDbDataAdapter da = new OleDbDataAdapter(); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.SelectCommand = cmd; da.UpdateCommand = cb.GetUpdateCommand(); DataSet result = new DataSet(); da.Fill(result, "students"); result.Tables[0].Rows[found].Delete(); da.Update(result, "students"); conn.Close(); }
public static System.Data.DataTable ToDataTable(string DataSource, string version, OleDbCommand cmd) { try { //ClearCache(); //Connection().Open(); using (OleDbConnection con = Connection(DataSource, version)) { using (cmd.Connection = con) { using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)) { using (System.Data.DataTable dt = new System.Data.DataTable()) { da.Fill(dt); return(dt); } } } } } catch (Exception) { throw; } finally { ConnectionClose(); } }
public static DataTable GetExcelData(string _FilePath, string ExcelSheetName = "", string Condition = "") { DataTable tbl = new DataTable(); string excelConnString = @"provider=Microsoft.ACE.OLEDB.12.0;data source=" + _FilePath + ";extended properties=" + "\"excel 12.0;hdr=yes;IMEX=1\""; //Create Connection to Excel work book using (OleDbConnection excelConnection = new OleDbConnection(excelConnString)) { ///---------------------------- string sql = "Select * " + " from [" + ExcelSheetName + "$] "; if (Condition.Trim().Length > 0) { sql += Condition; //sql = sql + " where Format(" + DateColumn + ",'" + itm.MDataFormat + "') between '" + AttendanceFromDate.Value.ToString(itm.MDataFormat) + "' and '" + AttendanceToDate.Value.ToString(itm.MDataFormat) + "'"; } OleDbDataAdapter da = new OleDbDataAdapter(sql, excelConnection); da.Fill(tbl); } return(tbl); }
void TryPreview() { try { lbName.Items.Clear(); string s = "provider = Microsoft.Jet.OLEDB.4.0;" + "data source = " + tbFile.Text + ";" + "extended properties = Excel 8.0;"; OleDbConnection conn = new OleDbConnection(s); string sheet = tbSheet.Text.Replace('.', '#'); s = string.Format("SELECT * FROM [{0}${1}]", sheet, tbRange.Text); OleDbDataAdapter da = new OleDbDataAdapter(s, conn); conn.Open(); DataTable dtData = new DataTable(); da.FillSchema(dtData, SchemaType.Source); conn.Close(); foreach (DataColumn c in dtData.Columns) { lbName.Items.Add((string)c.ColumnName); } } catch { } }
void GetTables(OleDbConnection conn) { // add tables var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow dr in dt.Rows) { // get type (table/view) var type = (string)dr[TABLE_TYPE]; if (type != TABLE && type != VIEW && type != LINK) { continue; } // create table var name = (string)dr[TABLE_NAME]; var table = new DataTable(name); table.ExtendedProperties[TABLE_TYPE] = type; // save definition in extended properties foreach (DataColumn col in dt.Columns) { table.ExtendedProperties[col.ColumnName] = dr[col]; } // get table schema and add to collection try { var select = GetSelectStatement(table); var da = new OleDbDataAdapter(select, conn); da.FillSchema(table, SchemaType.Mapped); Tables.Add(table); } catch { } } }
public bool charge(string phone, int money) { string sql = "select * from t_user WHERE 联系电话='" + phone + "'"; //获取表1中昵称为LanQ的内容 OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter(sql, oleDb); //创建适配对象 DataTable dt = new DataTable(); //新建表对象 dbDataAdapter.Fill(dt); //用适配对象填充表对象 if (dt.Rows.Count == 0) { MessageBox.Show("电话不存在!"); return(false); } foreach (DataRow item in dt.Rows) { money += Convert.ToInt32(item[6].ToString()); } Form1Form2.mm.caculator = money; sql = "update t_user set 账户余额 ='" + money.ToString() + "'"; OleDbCommand oleDbCommand = new OleDbCommand(sql, oleDb); int i = oleDbCommand.ExecuteNonQuery(); return(i > 0); }
private void btn_verdatos_Click(object sender, EventArgs e) { OleDbCommand com = new OleDbCommand(); try { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Producto, Descripcion, Cantidad, Precio from ProductoTB"; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataverproductos.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
public DataTable JobDetails(string JobId) { OleDbConnection con = new OleDbConnection(constring); con.Open(); try { OleDbCommand cmd = new OleDbCommand("Sp_Career_Sites_JobDetails", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@JobId", JobId); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return(ds.Tables[0]); } catch (Exception ex) { throw ex; } finally { con.Close(); } }
public int Update(ViewDataSet.品號ViewDataTable table) { OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.UpdateCommand = UpdateCommand; adapter.InsertCommand = InsertCommand; adapter.DeleteCommand = DeleteCommand; this.Connection.Open(); OleDbTransaction trans = this.Connection.BeginTransaction(); adapter.InsertCommand.Transaction = trans; adapter.UpdateCommand.Transaction = trans; adapter.DeleteCommand.Transaction = trans; try { int count = 0; count = adapter.Update(table); trans.Commit(); return count; } catch (System.Exception ex) { trans.Rollback(); throw ex; } finally { this.Connection.Close(); } }
public int Update(DatabaseSet.假日ViewDataTable table) { OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.UpdateCommand = UpdateCommand; adapter.InsertCommand = InsertCommand; adapter.DeleteCommand = DeleteCommand; this.Connection.Open(); OleDbTransaction trans = this.Connection.BeginTransaction(); adapter.InsertCommand.Transaction = trans; adapter.UpdateCommand.Transaction = trans; adapter.DeleteCommand.Transaction = trans; try { int count = 0; foreach (DatabaseSet.假日ViewRow row in table) { switch (row.RowState) { case DataRowState.Added: //如果要加入週六日 if (row.週六日) { adapter.DeleteCommand.Parameters["日期"].Value = row.日期; count += adapter.DeleteCommand.ExecuteNonQuery(); } else { adapter.InsertCommand.Parameters["日期"].Value = row.日期; adapter.InsertCommand.Parameters["增加"].Value = true; count += adapter.InsertCommand.ExecuteNonQuery(); } break; case DataRowState.Deleted: //如果要移除週六日 if ((bool)row["週六日", DataRowVersion.Original]) { adapter.InsertCommand.Parameters["日期"].Value = row["日期", DataRowVersion.Original]; adapter.InsertCommand.Parameters["增加"].Value = false; count += adapter.InsertCommand.ExecuteNonQuery(); } else { adapter.DeleteCommand.Parameters["日期"].Value = row["日期", DataRowVersion.Original]; count += adapter.DeleteCommand.ExecuteNonQuery(); } break; case DataRowState.Modified: //adapter.UpdateCommand.Parameters["日期"].Value = row.日期; //adapter.UpdateCommand.Parameters["Original_日期"].Value = row["日期", DataRowVersion.Original]; //count += adapter.UpdateCommand.ExecuteNonQuery(); break; default: break; } } trans.Commit(); return count; } catch (System.Exception ex) { trans.Rollback(); throw ex; } finally { this.Connection.Close(); } }
/// <summary> /// Initialize variables and sets the connectionstring and select query string. /// </summary> /// <param name="connString">Connection String.</param> /// <param name="sqlSelectString">Query String.</param> public OleDbRecordsetClass(String connString, String sqlSelectString) { this.dbConnection = new OleDbConnection(connString); this.SqlQuery = sqlSelectString; this.operationFinished = false; this.dbAdapter = CreateAdapter(SqlQuery, dbConnection); this.dbDataSet = new DataSet(); this.dbTable = new DataTable(); this.dbTable = dbDataSet.Tables.Add("Table"); this.dbAdapter.Fill(this.dbTable); CheckUpdateCommandsFromMetaData(); this.operationFinished = true; this.newRow = false; this.newRowChanged = false; this.foundRecordsCounter = 0; this.OnAfterQuery(); classFields = new FieldsClass(this); SetInitialPosition(); }
public DataSet fetch(string query) { OleDbTransaction trans = this.conn.BeginTransaction(); OleDbCommand comm = new OleDbCommand(); comm.CommandText = query; comm.Connection = this.conn; comm.Transaction = trans; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet ds = new DataSet(); adapter.Fill(ds, "tbl_0"); trans.Commit(); return ds; }
/// <summary> /// Initialize variables and sets the connection string. /// </summary> /// <param name="connString">string</param> public OleDbRecordsetClass(String connString) { dbConnection = new OleDbConnection(connString); dbAdapter = new OleDbDataAdapter(); dbDataSet = new DataSet(); newRow = false; newRowChanged = false; foundRecordsCounter = 0; classFields = new FieldsClass(this); SetInitialPosition(); }