private void lc(object sender, MouseEventArgs e) { c.Open(); DataSet ds = new DataSet(); string query = "select ID,pname,bill,pbill from pdetails where date=@bc "; OleDbCommand cmd = new OleDbCommand(query, c); cmd.Parameters.Add("@bc", OleDbType.Date).Value = dateTimePicker1.Value.Date; OleDbDataReader dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); /*ds.Tables.Add(dt); OleDbDataAdapter da = new OleDbDataAdapter(); da.Fill(dt);*/ dataGridView1.DataSource = dt.DefaultView; c.Close(); try { c.Open(); String str = @"SELECT SUM(pbill) FROM pdetails WHERE date=@bb;"; OleDbCommand comm2 = new OleDbCommand(str, c); comm2.Parameters.Add("@bb", OleDbType.Date).Value = dateTimePicker1.Value.Date; bill = Convert.ToDouble(comm2.ExecuteScalar()); label3.Text = bill.ToString() + "/-"; } catch(Exception ex) { MessageBox.Show("selected date miss match"); c.Close(); } c.Close(); }
public void FillCombo() { try { con = new OleDbConnection(cs); con.Open(); string ct = "select RTRIM(CategoryName) from Category order by CategoryName"; cmd = new OleDbCommand(ct); cmd.Connection = con; rdr = cmd.ExecuteReader(); while (rdr.Read()) { cmbCategory.Items.Add(rdr[0]); } con.Close(); con = new OleDbConnection(cs); con.Open(); string ct1 = "select RTRIM(CompanyName) from Company order by CompanyName"; cmd = new OleDbCommand(ct1); cmd.Connection = con; rdr = cmd.ExecuteReader(); while (rdr.Read()) { cmbCompany.Items.Add(rdr[0]); } con.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public override List<DomainAlias> GetDomainAliases(string domainName) { var _tmp = new List<DomainAlias>(); using (OleDbConnection _conn = new OleDbConnection(Settings.Default.connectionString)) { _conn.Open(); using (OleDbCommand _cmd = new OleDbCommand(@"SELECT domain_aliases.name AS alias, domains.name AS [domain], domain_aliases.status FROM (domain_aliases INNER JOIN domains ON domain_aliases.dom_id = domains.id) WHERE (domain_aliases.status = 0) AND (domains.name = ?)", _conn)) { _cmd.CommandType = CommandType.Text; _cmd.Parameters.AddWithValue("NAME", domainName); using (OleDbDataReader _read = _cmd.ExecuteReader()) { while (_read.Read()) { var _d = new DomainAlias(); _d.Domain = _read["domain"].ToString(); _d.Alias = _read["alias"].ToString(); _tmp.Add(_d); } } } _conn.Close(); } return _tmp; }
public string LoadContain() { if (Request.QueryString["CourseId"] == null) { return string.Empty; } string ThePath = string.Empty; string RetData = string.Empty; using (OleDbConnection Con = new OleDbConnection(constr)) { OleDbCommand cmd = new OleDbCommand(String.Format("SELECT TOP 1 DataPath FROM CoursenotimeDataPath WHERE CourseId = {0}", Request.QueryString["CourseId"]), Con); try { Con.Open(); ThePath = cmd.ExecuteScalar().ToString(); //if (ThePath != string.Empty) // ThePath = MapPath(DB.CourseNoTimeFileDir + ThePath); ThePath = DB.CourseNoTimeFileDir + ThePath; TextReader TR = new StreamReader(ThePath); RetData = TR.ReadToEnd(); TR.Close(); TR.Dispose(); } catch (Exception ex) { RetData = ex.Message; } Con.Close(); } return HttpUtility.HtmlDecode(RetData); }
protected void Button1_Click(object sender, EventArgs e) { int lgflg = 0; OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["classDB"]); OleDbCommand cmd = new OleDbCommand("SELECT * FROM student WHERE studentpassword = '******' AND email = '" + userName.Text + "'", conn); conn.Open(); OleDbDataReader myReader = cmd.ExecuteReader(); while (myReader.Read()) { lgflg = 1; Session.Add("fname", myReader["Contact_name"]); Session.Add("address", myReader["address"]); Session.Add("city", myReader["city"]); Session.Add("state", myReader["state"]); Session.Add("zipcode", myReader["zipcode"]); } myReader.Close(); conn.Close(); if (lgflg == 1) { Response.Write(Session["fname"]); } else { error.Visible = true; } }
private void addToDatabase(string sql) { OleDbConnection db = null; try { db = new OleDbConnection(); db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName; db.Open(); OleDbCommand command = new OleDbCommand(sql, db); command.ExecuteNonQuery(); } catch (Exception ex) { showErrorMessage(ex.Message); } finally { if (db != null) { db.Close(); } } }
public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow) { var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName); using (var excelConnection = new OleDbConnection(connectionString)) { excelConnection.Open(); if (sheetName == null) { var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (excelSchema != null) { sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); } } var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection); using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand)) { var dataSet = new DataSet(); oleDbDataAdapter.Fill(dataSet); using (var reader = dataSet.CreateDataReader()) { while (reader.Read()) { actionForEachRow(reader); } } } } }
public void updateProductPrice(ProductInBag Product) { OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString()); OleDbCommand myCmd; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(); try { myCmd = new OleDbCommand("UpdateUnitPrice", myConn); myCmd.CommandType = CommandType.StoredProcedure; OleDbParameter objParam; objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal); objParam.Direction = ParameterDirection.Input; objParam.Value = Product.Price; objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer); objParam.Direction = ParameterDirection.Input; objParam.Value = Product.ProdID; myConn.Open(); myCmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { myConn.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { try { OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder(); sb.Provider = "Microsoft.ACE.OLEDB.12.0"; sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb"); OleDbConnection conn = new OleDbConnection(sb.ConnectionString); conn.Open(); string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID"; OleDbCommand com = new OleDbCommand(updateQuery, conn); com.Parameters.AddWithValue("@ID", txtBagId.Text); com.Parameters.AddWithValue("@name", txtBagName.Text); com.Parameters.AddWithValue("@supplier", txtSupplier.Text); com.Parameters.AddWithValue("@color", txtColor.Text); com.Parameters.AddWithValue("@category", txtCategory.Text); com.Parameters.AddWithValue("@price", txtPrice.Text); com.Parameters.AddWithValue("@description", txtDescription.Text); com.ExecuteNonQuery(); lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !"; conn.Close(); } catch (Exception ex) { Response.Write("Error: " + ex.ToString()); lblMessage.Text = "Error !"; } }
public static void readFromDB(OleDbConnection conn) { string testSelect = " SELECT * FROM ImportAccess"; OleDbCommand comm = new OleDbCommand(testSelect, conn); OleDbDataReader dr = comm.ExecuteReader(); object[] buffer = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; StringBuilder sb = new StringBuilder(); while (dr.Read())// populate buffer with values from db { int value = dr.GetValues(buffer); for (int i = 0; i < buffer.Length; i++) { sb.Append(buffer[i] + ","); } sb.Remove(buffer.Length - 1, 1); } ConsoleKeyInfo cki = new ConsoleKeyInfo(); while (true) // keep console window open { Console.WriteLine("Returned values " + sb.ToString()); cki = Console.ReadKey(true); if (cki.Key == ConsoleKey.X) { break; } } dr.Close(); }
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams) { bool mustClose = false; if (myConn == null) { mustClose = true; myConn = clsConn.getConnOLE(); } if (myConn.State != ConnectionState.Open) myConn.Open(); OleDbCommand myCMD = new OleDbCommand(); // myCMD.Connection = myConn as OleDbConnection; if (myTrans != null) myCMD.Transaction = myTrans as OleDbTransaction; // myCMD.CommandType = CommandType.Text; myCMD.CommandText = TSQL; myCMD.CommandTimeout = 180000;//3 phut // if (myParams != null) AttachParameters(myCMD, myParams); int CMDResult = myCMD.ExecuteNonQuery(); // if (mustClose) myConn.Close(); return CMDResult; }
public MSAccessDataUtility() { command = new OleDbCommand(); command.Connection = GetConnection(); }
public DbSource() { OleDbCommand cmdMyQuery = new OleDbCommand("SELECT * FROM DictionaryDB"); DataTable dTable = DbUtils.SQLSelect(cmdMyQuery); m_orders = dTable; }
protected void datefilterPlanMeal_DayRender(object sender, DayRenderEventArgs e) { OleDbCommand command = new OleDbCommand("SELECT * FROM PlannedMeal WHERE UserDataID = " + userID + " ORDER BY CreatedDate DESC", myConnection); OleDbDataReader dr = command.ExecuteReader(); // Read DataReader till it reaches the end while (dr.Read() == true) { // Assign the Calendar control dates // already contained in the database //datefilterPlanMeal.SelectedDates.Add((DateTime)dr["CreatedDate"]); if (e.Day.Date == (DateTime)dr["CreatedDate"]) { e.Cell.BackColor = System.Drawing.Color.Silver; } } if (e.Day.IsSelected) { e.Cell.BackColor = System.Drawing.ColorTranslator.FromHtml("#4db6ac"); e.Cell.ForeColor = System.Drawing.Color.White; } // Close DataReader dr.Close(); }
public IDataReader ExecuteDataReader(string connectionString, string query) { _connection = new OleDbConnection(connectionString); _connection.Open(); var command = new OleDbCommand(query, _connection); return command.ExecuteReader(); }
protected void Page_Load(object sender, System.EventArgs e) { // resolve the address to the Access database string fileNameString = this.MapPath("."); fileNameString += "..\\..\\..\\..\\data\\chartdata.mdb"; // initialize a connection string string myConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameString; // define the database query string mySelectQuery="SELECT GrossSales FROM SALES WHERE QuarterEnding < #01/01/2002#;"; // create a database connection object using the connection string OleDbConnection myConnection = new OleDbConnection(myConnectionString); // create a database command on the connection using query OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection); // open the connection myCommand.Connection.Open(); // create a database reader OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // since the reader implements and IEnumerable, pass the reader directly into // the DataBind method with the name of the Column selected in the query Chart1.Series["Default"].Points.DataBindY(myReader, "GrossSales"); // close the reader and the connection myReader.Close(); myConnection.Close(); }
public int EnterToSite(UserDetails userDetails)//בודק על פי האימייל והסיסמא האם המשתשמש שמנסה להיכנס לאתר קיים או לא { OleDbCommand myCmd = new OleDbCommand("CheckIfUserExist", myConnection); myCmd.CommandType = CommandType.StoredProcedure; OleDbParameter objParam; objParam = myCmd.Parameters.Add("@Email", OleDbType.BSTR); objParam.Direction = ParameterDirection.Input; objParam.Value = userDetails.email; objParam = myCmd.Parameters.Add("@Password", OleDbType.BSTR); objParam.Direction = ParameterDirection.Input; objParam.Value = userDetails.password; int x = 0; try { myConnection.Open(); x = (int)myCmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { myConnection.Close(); } return x; }
//Create an Excel file with 2 columns: name and score: //Write a program that reads your MS Excel file through //the OLE DB data provider and displays the name and score row by row. static void Main() { OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes"""); OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn); dbConn.Open(); //First way //using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using. //{ OleDbDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { string name = (string)reader["Name"]; double score = (double)reader["Score"]; Console.WriteLine("{0} - score: {1}", name, score); } //} dbConn.Close(); //Second way dbConn.Open(); Console.WriteLine(); Console.WriteLine("Second Way"); Console.WriteLine("----------"); DataTable dataSet = new DataTable(); OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn); adapter.Fill(dataSet); foreach (DataRow item in dataSet.Rows) { Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]); } dbConn.Close(); }
/// <summary> /// Reads the selected file and places the contents into a DataTable. /// </summary> /// <returns>Datatable of students in file</returns> public DataTable ReadFile() { DataTable data = new DataTable(); try { using (cmd = con.CreateCommand()) { cmd.CommandText = string.Format("SELECT * from [{0}]", file.Name); con.Open(); using (dr = cmd.ExecuteReader()) { data.Load(dr); } } } catch (OleDbException oExe) { Debug.WriteLine(oExe.Message); } finally { con.Close(); } return data; }
//Gets the value of the show id. public int getShowID(OleDbCommand command) { int showID = 0; try { getSetCon().Open(); //Open con using (OleDbDataReader reader = command.ExecuteReader()) { //Reads all values. while (reader.Read()) { showID = Convert.ToInt32(reader["Show_ID"].ToString()); //Get the ID } } } catch(Exception ex) { //Error message MetroMessageBox.Show(form, ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { //Clean up command.Dispose(); con.Close(); } //Return the ID return showID; }
protected void Page_Load(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(); Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/productsdb.mdb"); Conn.Open(); //Response.Write(Conn.State); System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand(); System.Data.OleDb.OleDbDataReader dr; Comm.Connection = Conn; Comm.CommandText = "select productid, productname, productiondescription, price, qoh, imagelocation from products"; if (Request.Params["categoryid"] != null && Request.Params["categoryid"].Length >0) { Comm.CommandText += " where categoryid = ?" ; Comm.Parameters.AddWithValue("anything", Request.Params["categoryid"].ToString()); } dr = Comm.ExecuteReader(); bool firstone = true; Response.Write("{\"product\":["); while (dr.Read()) { if (!firstone) { Response.Write(","); } Response.Write(dr[0].ToString()); firstone = false; } Response.Write("]}"); }
public void binderStudentInfo(string username) { try { OleDbConnection con=DB.createcon(); OleDbCommand cmd=new OleDbCommand(); cmd.CommandText="select * from [student] where [studentUsername]='"+username+"'"; cmd.Connection=con; con.Open(); OleDbDataReader odr= cmd.ExecuteReader(); while(odr.Read()){ this.userName.Text=username; this.student_name.Value=odr["studentName"].ToString(); //this.student_id.Value = odr["studentID"].ToString(); //this.student_xibie.Value = odr["studentDepartment"].ToString(); // this.student_grate.Value = odr["studentGrade"].ToString(); //this.student_class.Value = odr["studentClass"].ToString(); //this.tel.Value = odr["studentTel"].ToString(); this.qq.Value = odr["studentQQ"].ToString(); this.about.Value = odr["studentAbout"].ToString(); ////this.ChTypeTrainCount.Text=odr["trainChCount"].ToString(); //this.EnTypeTrainCount.Text=odr["trainEnCount"].ToString(); //this.ChypeMatchCount.Text=odr["mathchChCount"].ToString(); //this.EnTypeMatchCount.Text=odr["mathchEnCount"].ToString(); } odr.Close(); con.Close(); } catch (Exception exp) { saveErrorMessage.writeFile("查询学生的个人信息时出错",exp.ToString()); } }
private void fill_form(string p_id) { String strQuery = String.Empty; OleDbConnection sqlConn = new OleDbConnection(); OleDbCommand sqlComm = new OleDbCommand(); OleDbDataReader sqlRead;// = new OleDbDataReader(); DateTime strReturn = DateTime.Now; // sqlConn.ConnectionString = PCPUB.m_oledb_connection.ToString(); sqlConn.Open(); // strQuery = String.Empty; strQuery += " SELECT"; strQuery += " [yarn_count_id],"; strQuery += " [yarn_count_name]"; strQuery += " FROM [tis_yarn_count]"; strQuery += " WHERE [yarn_count_id] = '" + p_id + "'"; // sqlComm.Connection = sqlConn; sqlComm.CommandText = strQuery.ToString(); sqlRead = sqlComm.ExecuteReader(); // if (sqlRead.Read()) { txt_yarn_count_id.Text = sqlRead["yarn_count_id"].ToString(); txt_yarn_count_name.Text = sqlRead["yarn_count_name"].ToString(); } // sqlRead.Close(); sqlConn.Close(); sqlRead.Dispose(); sqlComm.Dispose(); sqlConn.Dispose(); }
public DataTable sepetcevir(string uyeID, string map) { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + map); cnn.Open(); OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + uyeID, cnn); OleDbDataReader rdr = cmd.ExecuteReader(); rdr.Read(); string[] urunler = rdr[0].ToString().Split(','); DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn("fid"), new DataColumn("aciklama"), new DataColumn("fiyat") }); if (rdr[0].ToString() != "") { foreach (string item in urunler) { cmd.Dispose(); string fid = item; cmd = new OleDbCommand("select * from fotolar where fot_id=" + fid, cnn); OleDbDataReader rdr2 = cmd.ExecuteReader(); rdr2.Read(); string ack = rdr2[5].ToString(); string fiyat = rdr2[6].ToString(); dt.Rows.Add(fid, ack, fiyat); } } cnn.Close(); return dt; }
private void btn_Browser_Click(object sender, EventArgs e) { OpenFileDialog P_open = new OpenFileDialog();//创建打开文件对话框对象 P_open.Filter = "文本文件|*.txt|所有文件|*.*";//设置筛选字符串 if (P_open.ShowDialog() == DialogResult.OK) { txt_Path.Text = P_open.FileName;//显示文件路径 string conn = string.Format(//创建连接字符串 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=text", P_open.FileName.Substring(0,P_open.FileName.LastIndexOf(@"\"))); OleDbConnection P_OleDbConnection = new OleDbConnection(conn);//创建连接对象 try { P_OleDbConnection.Open();//打开连接 OleDbCommand cmd = new OleDbCommand(//创建命令对象 string.Format("select * from {0}", P_open.FileName.Substring(P_open.FileName.LastIndexOf(@"\"), P_open.FileName.Length - P_open.FileName.LastIndexOf(@"\"))), P_OleDbConnection); OleDbDataReader oda = cmd.ExecuteReader();//得到数据读取器对象 while (oda.Read()) { txt_Message.Text += oda[0].ToString();//得到文本文件中的字符串 } } catch (Exception ex) { MessageBox.Show(ex.Message);//弹出消息对话框 } finally { P_OleDbConnection.Close();//关闭连接 } } }
/// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand cmd = new OleDbCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (OleDbException e) { //LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms }); throw; } finally { cmd.Dispose(); connection.Close(); } return ds; } } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OleDb.OleDbException E) { tx.Rollback(); throw new Exception(E.Message); } } }
protected void btnAddBag_Click(object sender, EventArgs e) { try { OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder(); sb.Provider = "Microsoft.ACE.OLEDB.12.0"; sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb"); OleDbConnection conn = new OleDbConnection(sb.ConnectionString); conn.Open(); string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)"; OleDbCommand com = new OleDbCommand(insertQuery, conn); com.Parameters.AddWithValue("@name", txtBagName.Text); com.Parameters.AddWithValue("@supplier", txtSupplier.Text); com.Parameters.AddWithValue("@color", txtColor.Text); com.Parameters.AddWithValue("@category", txtCategory.Text); com.Parameters.AddWithValue("@price", txtPrice.Text); com.Parameters.AddWithValue("@description", txtDescription.Text); com.ExecuteNonQuery(); lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !"; conn.Close(); } catch (Exception ex) { Response.Write("Error: " + ex.ToString()); lblMessage.Text = "Error" + txtBagName.Text + " !"; } }
private void Autocomplete() { try { con = new OleDbConnection(cs); con.Open(); OleDbCommand cmd = new OleDbCommand("SELECT distinct ProductName FROM product", con); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds, "Product"); AutoCompleteStringCollection col = new AutoCompleteStringCollection(); int i = 0; for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { col.Add(ds.Tables[0].Rows[i]["productname"].ToString()); } txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource; txtProductName.AutoCompleteCustomSource = col; txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest; con.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void button3_Click(object sender, EventArgs e) { setOfData = new DataSet(); dabse.OleDbCommand command = new dabse.OleDbCommand(); adapter = new dabse.OleDbDataAdapter("Select * From " + nameoftable, Date_baseone);; adapter.Fill(setOfData, nameoftable); dataGridView1.DataSource = setOfData; dataGridView1.DataMember = nameoftable; MessageBox.Show("Загружено!"); }
public void uptime0() { //INSTANT C# NOTE: VB local static variable moved to class level: // Static m As Integer = 0 //On Error Resume Next if (string.IsNullOrEmpty(TextBox13.Text).Trim(' ')) { return; } SQLstr = "SELECT * FROM Table1 WHERE id LIKE '" + TextBox13.Text + "'"; Conn.Open(); OleDbDataAdapter DataAdapter1 = new OleDbDataAdapter(SQLstr, Conn); DataAdapter1.Fill(DataSet1, "Table1"); Conn.Close(); TextBox6.DataBindings.Add("Text", DataSet1, "Table1.id"); TextBox7.DataBindings.Add("Text", DataSet1, "Table1.user"); TextBox8.DataBindings.Add("Text", DataSet1, "Table1.pass"); TextBox9.DataBindings.Add("Text", DataSet1, "Table1.time"); DataGridView1.DataSource = DataSet1; DataGridView1.DataMember = "Table1"; if (this.BindingContext[DataSet1, "Table1"].Count == 0) { return; } OleDbDataAdapter adapter2 = new OleDbDataAdapter(SQLstr, Conn); System.Data.OleDb.OleDbCommand SavInto = new System.Data.OleDb.OleDbCommand(); SavInto.Connection = Conn; SavInto.CommandType = CommandType.Text; SavInto.CommandText = "DELETE FROM Table1 WHERE id ='" + TextBox13.Text.Trim(' ') + "'"; Conn.Open(); SavInto.ExecuteNonQuery(); DataSet1.Clear(); adapter2.Fill(DataSet1, "Table1"); Conn.Close(); TextBox9.Text = uptime; Conn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = Conn; //, [Status] ', @Status cmd.CommandText = "insert into Table1([id], [user], [pass], [time]) VALUES(@id, @user, @pass, @time)"; cmd.Parameters.AddWithValue("@id", TextBox6.Text); cmd.Parameters.AddWithValue("@user", TextBox7.Text); cmd.Parameters.AddWithValue("@pass", TextBox8.Text); cmd.Parameters.AddWithValue("@time", TextBox9.Text); cmd.ExecuteNonQuery(); Conn.Close(); }
private void удалитьПациентаToolStripMenuItem_Click(object sender, EventArgs e) { int result = 0; DialogResult res = MessageBox.Show("Удалить запись?", "Удаление сведений о пациенте", MessageBoxButtons.YesNo, MessageBoxIcon.Question); switch (res) { case DialogResult.Yes: using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\registr_v3.mdb;Persist Security Info=True;User ID=admin;Jet OLEDB:Database Password=12345")) { using (System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand()) { try { DataGridViewRow position = patient_informationDataGridView.Rows[0]; cm.CommandType = CommandType.Text; cm.CommandText = "DELETE FROM [patient information] WHERE [id patient] ='" + patient_informationDataGridView.SelectedRows.Contains(position) + "';"; cm.Connection = conn; conn.Open(); result = cm.ExecuteNonQuery(); conn.Close(); MessageBox.Show("Запись успешно удалена"); } catch (System.Data.OleDb.OleDbException oEx) { MessageBox.Show("Ошибка подключения к базе данных: " + oEx.ToString()); } catch (Exception ex) { MessageBox.Show("Ошибка удаления данных: " + ex.ToString()); } } } break; case DialogResult.No: break; } return; }
public void VnesiGrupa() { String komanda = ""; Boolean update = false; OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija); OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(); OleDbDataReader dt; if (DaliPostoiGrupa()) { komanda = "EXECUTE spUpdateGrupa"; OleCm.Connection = OleCn; OleCm.CommandText = komanda; update = true; } else { komanda = "EXECUTE spDodadiGrupa"; OleCm.Connection = OleCn; OleCm.CommandText = komanda; } if (update) { OleCm.Parameters.Add(new OleDbParameter("@Staro_Ime", StaroIme.ToString())); } if (groupTextBox.Enabled == false) { if (update) { return; } OleCm.Parameters.Add(new OleDbParameter("@Name", novoImeTextBox.Text.ToString().Trim())); } else { OleCm.Parameters.Add(new OleDbParameter("@Name", groupTextBox.Text.ToString().Trim())); } try { OleCn.Open(); dt = OleCm.ExecuteReader(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public string getASIN(string imageURL) { string asin; OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString); conn.Open(); OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "Select ItemID From Item Where ImageURL = '" + imageURL + "'"; asin = (cmd.ExecuteScalar()).ToString(); conn.Close(); return(asin); }
static void Main(string[] args) { var connection = new oleDB.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\new_DB.mdb"); connection.Open(); var command = new oleDB.OleDbCommand("INSERT INTO [Phones] (fio, phone) VALUES ('Света-Х', '521-61-41')"); command.Connection = connection; command.ExecuteNonQuery(); MessageBox.Show("В таблицу 'Phones' добавлена запись"); connection.Close(); }
public Boolean DaliPostoiGrupa() { String komanda = "EXECUTE spProveriGrupa"; OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija); OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn); OleDbDataReader dt; if (groupTextBox.Enabled) { OleCm.Parameters.Add(new OleDbParameter("@Code", groupTextBox.Text.Trim())); } else { OleCm.Parameters.Add(new OleDbParameter("@Code", novoImeTextBox.Text.Trim())); } int rezultat = -1; try { OleCn.Open(); dt = OleCm.ExecuteReader(); if (dt.Read()) { rezultat = Convert.ToInt32(dt["Rezultat"].ToString()); } dt.Close(); OleCn.Close(); if (rezultat >= 1) { OleCn.Close(); return(true); } else if (rezultat == 0) { OleCn.Close(); return(false); } } catch (Exception ex) { return(false); } return(false); }
//更新項目明細 private void updata_btn_Click(object sender, EventArgs e) { // === 對 Access 資料庫下SQL語法 === //// Transact-SQL 陳述式 String strSQL = "UPDATE [WindowsTarget] SET TargetName = '" + ShowTargetName.Text + "' ,TargetIP = '" + ShowTargetIP.Text + "' ,TargetDomain = '" + ShowTargetDomain.Text + "' ,TargetUser = '******' ,TargetPassword = '******' ,TargetAlert = '" + ShowTargetAlert.Text + "' , TargetRes='" + ShowTargetRes.Text + "' , TargetWarning='" + ShowTargetWarning.Text + "', TargetNonPageCheck = " + ShowNonPageCheckBox.Checked + ", TargetTimeCheck = " + ShowTimeCheckBox.Checked + " WHERE id=" + int.Parse(ShowTargetID.Text); System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb"); //// 開啟資料庫連接。 oleConn.Open(); //// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection,初始化 OleDbCommand 類別的新執行個體。 System.Data.OleDb.OleDbCommand oleCmd = new System.Data.OleDb.OleDbCommand(strSQL, oleConn); oleCmd.ExecuteNonQuery(); //// 關閉資料庫連接。 oleConn.Close(); }
public static System.Data.OleDb.OleDbDataReader ExecuteReader(System.Data.OleDb.OleDbCommand sqlCmd) { try { sqlCmd.Connection = Connection; if (sqlCmd.Connection.State == ConnectionState.Closed) { sqlCmd.Connection.Open(); } return(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)); } catch (Exception ex) { MsgBoxX.Show(ex.Message); return(null); } }
public static System.Data.OleDb.OleDbDataReader ExecuteReader(string strSQL) { System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString); System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSQL, oleDbConnection); System.Data.OleDb.OleDbDataReader result; try { oleDbConnection.Open(); System.Data.OleDb.OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(); result = oleDbDataReader; } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return(result); }
public override System.Data.DataColumn[] ObtenerColumnas(FuenteInformacion fuenteInformacion, string rutaArchivo) { try { DataSet dsMsExcel = new DataSet(); using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + rutaArchivo + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"")) { System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(); objOleConnection.Open(); DataTable worksheets = objOleConnection.GetSchema("Tables"); string hoja = worksheets.Rows[0][2].ToString(); System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection); select.CommandType = CommandType.Text; adapter.SelectCommand = select; dsMsExcel.Tables.Clear(); adapter.Fill(dsMsExcel); if (dsMsExcel.Tables.Count > 0) { DataRow col = dsMsExcel.Tables[0].Rows[0]; DataColumn[] columnas = new DataColumn[col.ItemArray.Length]; int index = 0; string nombre; foreach (object campo in col.ItemArray) { if (!string.IsNullOrEmpty(campo.ToString())) { nombre = campo.ToString().Trim(); } else { nombre = "-----------" + index.ToString(); } columnas[index] = new DataColumn(nombre); index++; } return(columnas); } } } catch (Exception ex) { throw ex; } return(null); }
public void TransferData() { _helpers.Ds = _repository.GetDataSetFromExcelFile(_selectedFile); _thicknessOfMaterials = _helpers.GetThicknessOfMaterials(); _newFile = _repository.CreateExcelFile(_emptyFile, Path.GetDirectoryName(_selectedFile), _newFileName); if (_newFile == null) { return; } System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(); OleDbConnection MyConnection = new OleDbConnection(_repository.GetConnectionString(_newFile)); MyConnection.Open(); myCommand.Connection = MyConnection; foreach (string thkness in _thicknessOfMaterials) { _dt = _helpers.GetAllPartWithThikness(thkness); myCommand.Parameters.Add(new OleDbParameter("@thikness", string.Format("Материал с толщиной {0}мм", thkness.ToString()))); _sql = "insert into [Распил$C:C" + _count + "] values (@thikness)"; myCommand.CommandText = _sql; myCommand.ExecuteNonQuery(); myCommand.Parameters.Clear(); _count++; foreach (DataRow dr in _dt.Rows) { _sql = "insert into [Распил$A:J" + _count + "] values (@sectionNumber, @numberOfDetail, @nameOfDitails, '', '', '', '', @x, @y, @number)"; myCommand.CommandText = _sql; myCommand.Parameters.Add(new OleDbParameter("@sectionNumber", dr["SectionNumber"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@numberOfDetail", _numberOfDetail.ToString())); myCommand.Parameters.Add(new OleDbParameter("@nameOfDitails", dr["NameOfDitails"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@x", dr["X"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@y", dr["Y"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@number", dr["Number"].ToString())); myCommand.ExecuteNonQuery(); myCommand.Parameters.Clear(); _numberOfDetail++; _count++; } _count += 2; } MyConnection.Close(); MessageBox.Show("!! Перенос данных завершен !!"); }
public static void DBReadTable() { if (connect.State == System.Data.ConnectionState.Closed) // If no connection to DB { Console.WriteLine("DBReadTable(). No DB connection! Connecting"); DBConnect(); } string[] SqlQuery = new string[] // Sql query array { "SELECT id, ticker, tick_time, price FROM stock_ticks", "SELECT number, ticker, tick_time, price FROM futures_ticks" }; for (int z = 0; z <= 1; z++) // Run cycle two times for stock and future tables { //string queryString = "SELECT id, ticker, tick_time, price FROM stock_ticks"; var command = new oledb.OleDbCommand(SqlQuery[z], connect); OleDbDataReader reader; reader = command.ExecuteReader(); int i = 0; while (reader.Read() && (i < 40)) // Always call Read before accessing data { //double zz = Convert.ToDouble(reader.GetString(3)) + 100; if (z == 0) // stock_ticks has price in string format by the misstake { //Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetDateTime(2) + " " + reader.GetString(3)); ArraySync.list1.Add(reader.GetDateTime(2)); } else // futures_ticks has price in double { //Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetDateTime(2) + " " + reader.GetDouble(3)); ArraySync.list2.Add(reader.GetDateTime(2)); } i++; } Console.WriteLine("***************************"); reader.Close(); // Always call Close when done reading } }
/// <summary> /// using the OleDb to Delete DataTable /// </summary> /// <param name="Path">路径</param> /// <param name="dt">DataTable</param> /// <param name="HDR">HDR true:读取column false:column行作为内容读取</param> public void DropTable(string sheetName, string Path, string HDR = "true") { string HDRStr = "YES"; if (!HDR.Equals("true")) { HDRStr = "NO"; } string strCon = string.Empty; FileInfo file = new FileInfo(Path); string extension = file.Extension; switch (extension) { case ".xls": strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;HDR=" + HDRStr + ";"; break; case ".xlsx": strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=" + HDRStr + ";IMEX=0;'"; break; default: strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=" + HDRStr + ";IMEX=0;'"; break; } using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon)) { con.Open(); System.Data.OleDb.OleDbCommand cmd; try { cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", sheetName), con); //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下 cmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { con.Close(); } } }
public void addItemToDatabase(string asin) { string description = getItemDescription(asin); string price = getCurrentPrice(asin); string lastStoredPrice = "0"; string url = getImageURL(asin); DateTime date = DateTime.Now; OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString); conn.Open(); OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "Insert Into User_Item (UserID, ItemID) values ('testuser', '" + asin + "')"; cmd.ExecuteNonQuery(); //only insert into price if price is different from current price try { cmd.CommandText = "Select Price From Price Where ItemID = '" + asin + "' Order By PriceDate DESC"; lastStoredPrice = (cmd.ExecuteScalar()).ToString(); } catch (Exception) { //do nothing if no record found in price } if (lastStoredPrice != price) { cmd.CommandText = "Insert Into Price(ItemID, Price, PriceDate) values ('" + asin + "', '" + price + "', '" + date + "')"; cmd.ExecuteNonQuery(); } try { cmd.CommandText = "Insert Into Item (ItemID, Description, ImageURL, CurPrice, CurDate) values ('" + asin + "', '" + description + "', '" + url + "', '" + price + "', '" + date + "')"; cmd.ExecuteNonQuery(); } catch (Exception) { //do nothing if duplicate } conn.Close(); }
/// <summary> /// 读取Excel文件 /// </summary> /// <param name="filepath">文件路径</param> /// <returns>DataTable</returns> public static DataTable ReadExcel(string filename, bool isHdR) { string hdr = "N0"; if (isHdR) { hdr = "YEX"; } System.Data.DataSet itemDS = new DataSet(); if (filename.Trim().ToUpper().EndsWith("XLS") || filename.Trim().ToUpper().EndsWith("XLSX")) { string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\""; System.Data.OleDb.OleDbConnection conn = null; System.Data.OleDb.OleDbCommand oledbCommd = null; try { conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = connStr; conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //判断连接Excel sheet名 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; string sqlText = "select * from [" + dr["TABLE_NAME"] + "]"; oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn); oledbCommd.CommandTimeout = 100000; //执行 System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd); oledbDA.Fill(itemDS); } } catch { } finally { //释放 oledbCommd.Dispose(); conn.Close(); } //创建连接 } return(itemDS.Tables[0]); }
private void button1_Click(object sender, EventArgs e) { if (comboBox1.SelectedIndex == -1) { MessageBox.Show("Please Select Series for Item"); } if (textBox1.Text == "" || textBox1.Text == " ") { MessageBox.Show("Please Enter code for Item"); } if (textBox2.Text == "" || textBox2.Text == " ") { MessageBox.Show("Please Enter Rate for Item"); } if (comboBox1.SelectedIndex != -1 && textBox1.Text != "" && textBox2.Text != "") { String series, code; float price; series = comboBox1.SelectedItem.ToString(); code = textBox1.Text; price = float.Parse(textBox2.Text); OleDbConnection cnon = new System.Data.OleDb.OleDbConnection(); // cnon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb"; cnon.ConnectionString = Utility.con; System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(); command.CommandText = "INSERT INTO Item(Series,Code,Rate)VALUES(@series,@code,@rate)"; command.Parameters.AddWithValue("@series", series); command.Parameters.AddWithValue("@code", code); command.Parameters.AddWithValue("@rate", price); cnon.Open(); command.Connection = cnon; command.ExecuteNonQuery(); MessageBox.Show("Item Added"); cnon.Close(); clear_data(); } }
//打开恢复数据库专用的数据连接,用master数据库 public string DBCreate(string str_Sql) { string myConnectionStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" + ConfigurationSettings.AppSettings["WorkstationSet"] + ";Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master" + ";Data Source=" + SystemInformation.ComputerName + "\\MSDEDH" + ";User ID=" + ConfigurationSettings.AppSettings["DatabaseUser"] + ";Password="******"DatabasePassword"]; myConnection = new System.Data.OleDb.OleDbConnection(myConnectionStr); try { myConnection.Open(); } catch (SqlException e) { string errorMessage = e.Message; return(errorMessage); } if (HaveDB() == false) { myCommand = new System.Data.OleDb.OleDbCommand("CREATE DATABASE CityEduManage", myConnection); myCommand.ExecuteNonQuery(); myCommand.Dispose(); } //断开CityEduManage的一切连接 //myCommand = new SqlCommand(str_Sql,myConnection); string str_Sql_DisConnect = "declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('" + ConfigurationSettings.AppSettings["Database"] + "') exec sp_msforeach_worker '?'"; myCommand = new System.Data.OleDb.OleDbCommand(str_Sql_DisConnect, myConnection); myCommand.ExecuteNonQuery(); myCommand.Dispose(); myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection); myCommand.ExecuteNonQuery(); myCommand.Dispose(); myConnection.Close(); return("OK"); }
public static System.Data.OleDb.OleDbDataReader ExecuteReader(string SQLString, params System.Data.OleDb.OleDbParameter[] cmdParms) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString); System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(); System.Data.OleDb.OleDbDataReader result; try { DbHelperOleDb.PrepareCommand(oleDbCommand, conn, null, SQLString, cmdParms); System.Data.OleDb.OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(); oleDbCommand.Parameters.Clear(); result = oleDbDataReader; } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return(result); }
private void saver_Click(object sender, EventArgs e) { command = new dabse.OleDbCommand(); command.CommandText = "UPDATE Группы WHERE Номер = ?"; command.Parameters.Add(new dabse.OleDbParameter("Original_Номер", dabse.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, (byte)0, (byte)0, "Номер", System.Data.DataRowVersion.Original, null)); command.Connection = Date_baseone; adapter.UpdateCommand = command; try { int kol = adapter.Update(setOfData, nameoftable); MessageBox.Show("Обновлено " + kol + " записей"); Date_baseone.Close(); } catch (Exception Ситуация_однако) { MessageBox.Show(Ситуация_однако.Message, "Недоразумение"); } }
public static DataTable ReadFromExcal(string excalPath, string sheetName) { string connectionString = @"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + excalPath + ";Extended Properties=Excel 12.0 Xml"; // Connection string OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(connectionString); string excelQuery = "Select * from [" + sheetName + "$]"; // SQl statement OleDbCommand excelCommand = new System.Data.OleDb.OleDbCommand(excelQuery, excelConnection); excelConnection.Open(); // open Connection OleDbDataReader excelReader; excelReader = excelCommand.ExecuteReader(); // excute quary DataTable excelTable = new DataTable(); excelTable.Load(excelReader); // put the excal data into data table excelConnection.Close(); return(excelTable); }
public bool removeItemFromDatabase(string imageURL) { try { OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString); conn.Open(); OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "Delete From User_Item Where ItemID = (Select ItemID From Item Where ImageURL = '" + imageURL + "')"; cmd.ExecuteNonQuery(); conn.Close(); return(true); } catch { return(false); } }
//確定更新按鈕 private void Update_btn_Click(object sender, EventArgs e) { // === 對 Access 資料庫下SQL語法 === //// Transact-SQL 陳述式 String strSQL = "UPDATE [SystemSetting] SET Timer = " + Timer_num.Value + " ,MaxThread = " + Thread_num.Value + " ,MaxIOThread = " + ThreadIO_num.Value + " ,LimitNonPage = " + LimitNonPage_Num.Value + " ,LimitTimeRange = " + LimitTimeRange_Num.Value + " WHERE id=1"; System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb"); //// 開啟資料庫連接。 oleConn.Open(); //// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection,初始化 OleDbCommand 類別的新執行個體。 System.Data.OleDb.OleDbCommand oleCmd = new System.Data.OleDb.OleDbCommand(strSQL, oleConn); oleCmd.ExecuteNonQuery(); //// 關閉資料庫連接。 oleConn.Close(); this.Close(); }
public bool update_history(string dev) { string sheet = (dev.Replace('/', '-')).Replace(':', '@'); string src = "C:\\WoT Testbed\\" + sheet + ".xls"; string headers = "[RecDateTime] datetime, [reading] int, [state] int"; System.Data.OleDb.OleDbCommand cmd; System.Data.OleDb.OleDbConnection ExcelCon_dev = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + src + "';Extended Properties=Excel 8.0;"); if (!File.Exists(src)) { cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet + "] (" + headers + " ) ", ExcelCon_dev); ExcelCon_dev.Open(); cmd.ExecuteNonQuery(); ExcelCon_dev.Close(); } try { // check history size ExcelCon_dev.Open(); cmd = new System.Data.OleDb.OleDbCommand("Select count([reading]) from [" + sheet + "] ", ExcelCon_dev); int history_count = int.Parse(cmd.ExecuteScalar().ToString()); DateTime d = new DateTime(); d = DateTime.Parse(DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute); // delete old rows string val = monitor(dev); if (history_count > 20) { cmd = new OleDbCommand(" update [" + sheet + "] set [RecDateTime]='" + d + "', [reading]=" + int.Parse(val) + " where [RecDateTime]=(select min([RecDateTime]) from [" + sheet + "]) ", ExcelCon_dev); } // insert new rows else { cmd = new OleDbCommand(" insert into [" + sheet + "] ([RecDateTime], [reading]) values ('" + d + "','" + val + "')", ExcelCon_dev); } cmd.ExecuteNonQuery(); ExcelCon_dev.Close(); } catch { ExcelCon_dev.Close(); } return(true); }
private void CreateTables(System.Data.OleDb.OleDbCommand poCmd, ref List <string> psErrMsgs) { for (int xii = 0; xii < TablesAsTheyShouldBe.Count; xii++) { if (TablesAsTheyShouldBe[xii].Action == TableActionType.Create) { // // create the table // string xsSQL = "CREATE TABLE [" + TablesAsTheyShouldBe[xii].Name + "] ("; for (int xiColm = 0; xiColm < TablesAsTheyShouldBe[xii].Columns.Count; xiColm++) { if (xiColm > 0) { xsSQL += ", "; } xsSQL += "[" + TablesAsTheyShouldBe[xii].Columns[xiColm].Name.Trim() + "] " + ColumnTypeForSQL(TablesAsTheyShouldBe[xii].Columns[xiColm]); } xsSQL += ")"; try { poCmd.CommandText = xsSQL; poCmd.CommandType = System.Data.CommandType.Text; poCmd.ExecuteNonQuery(); } catch (Exception xoExc) { psErrMsgs.Add(xoExc.Message + " (" + xsSQL + ")"); } // // create any indices for the table // for (int xiIdx = 0; xiIdx < TablesAsTheyShouldBe[xii].Indices.Count; xiIdx++) { string xsErr = ""; if (!CreateIndex(poCmd, TablesAsTheyShouldBe[xii].Indices[xiIdx].Name, TablesAsTheyShouldBe[xii].Name, TablesAsTheyShouldBe[xii].Indices[xiIdx].Columns, out xsErr)) { psErrMsgs.Add(xsErr); } } } } }
public static DataTable Obt_dbf(string sql, string retail_noretail) { DataTable dt = null; string conex = ""; if (retail_noretail == "5") { conex = Conn2; } else { conex = Conn1; } using (OleDbConnection dbConn = new OleDbConnection(conex)) { dbConn.Open(); try { // FALTA EVALUAR new System.Data.OleDb.OleDbCommand("set enginebehavior 80", dbConn).ExecuteNonQuery(); using (OleDbCommand cmd = dbConn.CreateCommand()) { cmd.CommandText = "set enginebehavior 70"; cmd.ExecuteNonQuery(); } //-- Obtenemos datos del DBF System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sql, dbConn); System.Data.OleDb.OleDbDataAdapter ada = new System.Data.OleDb.OleDbDataAdapter(com); dt = new DataTable(); ada.Fill(dt); } catch { // omitido } } return(dt); }
/// <summary> /// 创建命令 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="para">Sql参数</param> /// <returns></returns> public override System.Data.IDbCommand GetCommand(string sql, System.Data.IDataParameter[] para) { //创建新命令 if (command == null) { command = new System.Data.OleDb.OleDbCommand(sql, (OleDbConnection)Connection); } //更新命令 else { command.Parameters.Clear(); command.CommandText = sql; } if (para != null) { ((OleDbCommand)command).Parameters.AddRange(para); } return(command); }
private void DobavitZapis_Click(object sender, EventArgs e) { var Command1 = new dabse.OleDbCommand("INSERT INTO Студенты (Фамилия, Имя, Отчество, Специальность, Гражданство, Форма_обучения, Признак_отчисления) VALUES (?, ?, ?, ?, ?, ?, ?)"); Command1.Parameters.Add("Фамилия", dabse.OleDbType.VarWChar).Value = Familiya.Text; Command1.Parameters.Add("Имя", dabse.OleDbType.VarWChar).Value = textBox1.Text; Command1.Parameters.Add("Отчество", dabse.OleDbType.VarWChar).Value = Otchestvo.Text; Command1.Parameters.Add("Специальность", dabse.OleDbType.Integer).Value = int.Parse(specialnost.SelectedValue.ToString()); Command1.Parameters.Add("Гражданство", dabse.OleDbType.VarWChar).Value = Grazhdanstvo.Text; Command1.Parameters.Add("Форма_обучения", dabse.OleDbType.VarWChar).Value = studyform.Text; Command1.Parameters.Add("Признак_отчисления", dabse.OleDbType.Integer).Value = int.Parse(priznakotchis.SelectedValue.ToString()); Command1.Connection = Date_baseone; Command1.ExecuteNonQuery(); MessageBox.Show("Запись добавлена"); dataGridView1.DataSource = MainForm.FunctionOfAdding(nameoftable); }
private void Students_Load(object sender, EventArgs e) { var data1 = new List <Dvapolya>(); Date_baseone = new dabse.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Медведев.mdb"); Date_baseone.Open(); dabse.OleDbCommand com1 = new dabse.OleDbCommand("Select Название, Шифр FROM Специальности", Date_baseone); dabse.OleDbDataReader resulter = com1.ExecuteReader(); while (resulter.Read() == true) { var mc = new Dvapolya { secondpole = resulter[0].ToString().Trim(), firstpole = resulter[1].ToString().Trim() }; data1.Add(mc); } specialnost.DataSource = data1; specialnost.DisplayMember = "secondpole"; specialnost.ValueMember = "firstpole"; var data2 = new List <Dvapolya>(); var nc = new Dvapolya { secondpole = "обучается", firstpole = "1" }; data2.Add(nc); var nc1 = new Dvapolya { secondpole = "отчислен", firstpole = "2" }; data2.Add(nc1); priznakotchis.DataSource = data2; priznakotchis.DisplayMember = "secondpole"; priznakotchis.ValueMember = "firstpole"; }
static void Main(string[] args) { var connection = new oleDB.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\new_DB.mdb"); connection.Open(); var command = new oleDB.OleDbCommand("CREATE TABLE [Phones] ([num] counter, [fio] char(20), [phone] char(20))", connection); try { command.ExecuteNonQuery(); MessageBox.Show("Структура таблицы 'Phones' записана в пустую БД"); } catch (Exception ex) { MessageBox.Show(ex.Message); } connection.Close(); }
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; }