internal OleDbTransaction (OleDbConnection connection, int depth, IsolationLevel isolevel) { this.connection = connection; gdaTransaction = libgda.gda_transaction_new (depth.ToString ()); switch (isolevel) { case IsolationLevel.ReadCommitted : libgda.gda_transaction_set_isolation_level (gdaTransaction, GdaTransactionIsolation.ReadCommitted); break; case IsolationLevel.ReadUncommitted : libgda.gda_transaction_set_isolation_level (gdaTransaction, GdaTransactionIsolation.ReadUncommitted); break; case IsolationLevel.RepeatableRead : libgda.gda_transaction_set_isolation_level (gdaTransaction, GdaTransactionIsolation.RepeatableRead); break; case IsolationLevel.Serializable : libgda.gda_transaction_set_isolation_level (gdaTransaction, GdaTransactionIsolation.Serializable); break; } libgda.gda_connection_begin_transaction (connection.GdaConnection, gdaTransaction); }
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 bool InsertFtpRecord(FileDetail fileDetail) { var lcsql = "insert into MasterFtp(FileName, CreateTime, Folder, Records, DlTime) values ( ?, ?,?,?,?)"; var connectionString = ConfigurationManager.ConnectionStrings["vfpConnectionString"].ConnectionString; using (var connection = new OleDbConnection(connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "SET NULL OFF"; command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = lcsql; command.Parameters.AddWithValue("FileName", fileDetail.FileName); command.Parameters.AddWithValue("CreateTime", fileDetail.FileDate); command.Parameters.AddWithValue("Folder", fileDetail.Folder); command.Parameters.AddWithValue("Records", fileDetail.Records); command.Parameters.AddWithValue("DlTime", fileDetail.DownloadTime); //connection.Open(); var retval = command.ExecuteNonQuery(); var success = (retval == 1); return success; } } }
public ReservationForm() { reservationConn = new OleDbConnection(connString); drawPanel(); drawPlan(); InitializeComponent(); }
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 static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString) { DataSet sheet1 = new DataSet(); using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString)) { connection.Open(); string selectSql = @"SELECT * FROM [Sheet1$]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.Fill(sheet1); } connection.Close(); } var table = sheet1.Tables[0]; foreach (DataRow row in table.Rows) { foreach (DataColumn column in table.Columns) { Console.Write("{0, -20} ", row[column]); } Console.WriteLine(); } }
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 + " !"; } }
public IDataReader ExecuteDataReader(string connectionString, string query) { _connection = new OleDbConnection(connectionString); _connection.Open(); var command = new OleDbCommand(query, _connection); return command.ExecuteReader(); }
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; }
/// <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; } } }
public void update(string query) { OleDbConnection connection = new OleDbConnection(); string executable = System.Reflection.Assembly.GetExecutingAssembly().Location; string path = (System.IO.Path.GetDirectoryName(executable)); AppDomain.CurrentDomain.SetData("DataDirectory", path); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/LMS.accdb"; OleDbCommand command; command = connection.CreateCommand(); try { command.CommandText = query; command.CommandType = CommandType.Text; connection.Open(); //SqlCommand comm = new SqlCommand(query, connection); command.ExecuteNonQuery(); } catch (Exception) { } finally { if (connection != null) connection.Close(); } }
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("]}"); }
/** * Constructor that gets the connection to the database and Car information * * @param V VIN of the Car * @param T Type of the Car * @param cn Connection to the database */ public MakeTires(string S, string T, string TS, OleDbConnection cn) { this.SerialNumber = S; this.Type = T; this.TireSize = TS; this.cn = cn; }
// Gets all the countries that start with the typed text, taking paging into account protected DataTable GetCountries(string text, int startOffset, int numberOfItems) { OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../App_Data/continent.mdb")); myConn.Open(); string whereClause = " WHERE CountryName LIKE @CountryName"; string sortExpression = " ORDER BY CountryName"; string commandText = "SELECT TOP " + numberOfItems + " CountryID, CountryName FROM Country"; commandText += whereClause; if (startOffset != 0) { commandText += " AND CountryID NOT IN (SELECT TOP " + startOffset + " CountryID FROM Country"; commandText += whereClause + sortExpression + ")"; } commandText += sortExpression; OleDbCommand myComm = new OleDbCommand(commandText, myConn); myComm.Parameters.Add("@CountryName", OleDbType.VarChar).Value = text + '%'; OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = myComm; da.Fill(ds, "Country"); myConn.Close(); return ds.Tables[0]; }
//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(); }
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(); } }
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; }
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(); } } }
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(); }
private void FormFirma_Load(object sender, EventArgs e) { PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt"); string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt"); string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+PutBaze; //----------SQL instrukcija-----------\\ string sql = "SELECT * FROM sve WHERE Firma LIKE '" + PutKriterij + "%'"; //klase za povezivanje na ACCESS bazu podataka// OleDbConnection conn = new OleDbConnection(connString); OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn); //\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\ ds = new DataSet(); //kreira noi objekt(kopiju) DataSet()-a conn.Open(); //otvara spoj s bazom podataka adapter.Fill(ds, "sve"); //puni se DataSet s podacima tabele t_razred conn.Close(); //zatvara se baza podataka //nakon zatvaanja BP imamo odgovarajuće podatke u ds objektu ( DataSet() ) dataGridView2.DataSource = ds; //upisivanje podataka id ds u dataGridView2 dataGridView2.DataMember = "sve"; ukupnaCijenaDataGridViewTextBoxColumn.DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("de-DE"); this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending); System.IO.File.Delete(@"privPonuda.txt"); }
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();//关闭连接 } } }
protected void btTaiLen_Click1(object sender, EventArgs e) { if (Request.Cookies["ADMIN"] == null) return; if (FileUpload1.HasFile) try { string path = Server.MapPath(@"EXCEL\") + "Question_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx"; FileUpload1.SaveAs(path); string txt = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;", path); OleDbConnection con = new OleDbConnection(txt); con.Open(); txt = "Select * FROM [Sheet1$]"; OleDbDataAdapter da = new OleDbDataAdapter(txt, con); DataSet ds = new DataSet(); da.Fill(ds); con.Close(); gvCauHoi.DataSource = ds; gvCauHoi.DataBind(); string temp; foreach (GridViewRow dr in gvCauHoi.Rows) { temp = Server.HtmlDecode(dr.Cells[0].Text); if (temp.StartsWith("#")) dr.CssClass = "Q1"; else if (temp.StartsWith("$")) // Không đảo phương án trả lời dr.CssClass = "Q2"; else if (temp.StartsWith("*")) // Phương án trả lời đúng dr.CssClass = "A1"; } } catch (Exception ex) { lbError.Text = ex.Message; } }
public DataSet GetDataSet(string filepath, string excelFileExtension) { try { System.Data.OleDb.OleDbConnection oledbcon = null; string strConn = string.Empty; switch (excelFileExtension.Trim()) { case "xls": oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\""); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;"; break; case "xlsx": oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'"); strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0;"; break; } //excel OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString(); System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon); //select DataSet ds = new DataSet(); oledbAdaptor.Fill(ds); oledbcon.Close(); return ds; } catch (Exception ex) { throw ex; } }
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 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); }
/// <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); } } }
public List<MonthlyExpenseReportItem> cercaMovimentiCategoria(int idCat) { OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.scadenzettiDbConnectionString); string queryString = "SELECT * FROM QueryReportCategoria"; OleDbCommand cmd = new OleDbCommand(queryString, conn); cmd.Parameters.Add("idCat", OleDbType.Integer).Value = idCat; conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); List<MonthlyExpenseReportItem> list = new List<MonthlyExpenseReportItem>(); while (reader.Read()) { MonthlyExpenseReportItem mov = new MonthlyExpenseReportItem(); mov.Scadenza = DateTime.Parse(reader[0].ToString()); mov.Importo = decimal.Parse(reader[1].ToString()); mov.Debitore = reader[2].ToString(); mov.Creditore = reader[3].ToString(); mov.Causale = reader[4].ToString(); mov.Tipo = reader[5].ToString(); mov.Ultimato = bool.Parse(reader[6].ToString()); list.Add(mov); } reader.Close(); conn.Close(); return list; }
public void BeginTransaction_Connection_Closed () { OleDbConnection cn = new OleDbConnection (); try { cn.BeginTransaction (); Assert.Fail ("#A1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2"); Assert.IsNull (ex.InnerException, "#A3"); Assert.IsNotNull (ex.Message, "#A4"); } try { cn.BeginTransaction ((IsolationLevel) 666); Assert.Fail ("#B1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2"); Assert.IsNull (ex.InnerException, "#B3"); Assert.IsNotNull (ex.Message, "#B4"); } try { cn.BeginTransaction (IsolationLevel.Serializable); Assert.Fail ("#C1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2"); Assert.IsNull (ex.InnerException, "#C3"); Assert.IsNotNull (ex.Message, "#C4"); } }
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(); }
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(); }
/// <summary> /// Class constructor, to retrieve data from .xls data source /// </summary> /// <param name="strXlsFile">The .xls file to be connected. /// This file should exist and it can be writable.</param> public XlsDBConnector(String strXlsFile) { // Validate the specified if (!ValidateFile(strXlsFile)) { throw new ArgumentException("The specified file doesn't exists or has readonly attribute.", strXlsFile); } // establish a connection to the data source. m_connectStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \"" + strXlsFile + "\"; Extended Properties = \"Excel 8.0;HDR=YES;\""; // create the .xls connection m_objConn = new System.Data.OleDb.OleDbConnection(m_connectStr); m_objConn.Open(); }
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); }
public void queryDatabase(string sqlString, customQuery findResult, System.Data.OleDb.OleDbConnection conn) { OleDbCommand Com = new OleDbCommand(); Com.CommandText = sqlString; Com.Connection = conn; OleDbDataReader objDataReader = null; objDataReader = Com.ExecuteReader(); if (objDataReader == null) { return; } while (objDataReader.Read()) { findResult.SetIndex(objDataReader["PersonID"].ToString()); } objDataReader.Close(); string whereClause = "(MyNumber='" + findResult.GetIndex(0) + "') "; for (int i = 1; i < findResult.GetIndexCount(); i++) { whereClause = whereClause + "OR (MyNumber='" + findResult.GetIndex(i) + "') "; } string localsqlString = "SELECT PersonName FROM tblPeeps WHERE (" + whereClause + ")"; OleDbCommand Com2 = new OleDbCommand(); Com2.CommandText = localsqlString; Com2.Connection = conn; OleDbDataReader objDataReader2 = null; objDataReader2 = Com2.ExecuteReader(); if (objDataReader2 == null) { return; } while (objDataReader2.Read()) { findResult.SetName(objDataReader2["PersonName"].ToString()); } objDataReader2.Close(); }
private void infoBtn_Click(object sender, RoutedEventArgs e) { string filepath = (string)fileLbl.Content; if (filepath == "") { System.Windows.MessageBox.Show("File path required!"); return; } FileInfo file = new FileInfo(filepath); if (!file.Exists) { System.Windows.MessageBox.Show("File doesn't exist!"); return; } string sheetName = locationTxt.Text; if (sheetName == "") { System.Windows.MessageBox.Show("Sheet name required!"); return; } string connectionString = getConnectionStr(filepath, file.Extension); System.Data.DataTable dt = new System.Data.DataTable(); using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString)) { string sql = string.Format("SELECT * FROM [{0}$]", sheetName); System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(sql, conn); try { apt.Fill(dt); } catch (Exception) { System.Windows.MessageBox.Show("no such sheet!"); return; } dt.TableName = sheetName; grdData.ItemsSource = dt.DefaultView; } openedFilePath = filepath; openedFileExtention = file.Extension; }
//2018.02.26 Arthur 新增 取得OleDb連線 //取得OleDb連線 public System.Data.OleDb.OleDbConnection GetOleDBcon(string FileName) { string M_str_sqlcon = ""; string sFileExtension = Path.GetExtension(FileName); if (sFileExtension.ToUpper() == ".XLS") { M_str_sqlcon = "Data Source=" + FileName + ";" + "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties='Excel 8.0;" + "HDR=No;" + "IMEX=1;'"; } else if (sFileExtension.ToUpper() == ".XLSX") { M_str_sqlcon = "Data Source=" + FileName + ";" + "Provider=Microsoft.ACE.OLEDB.12.0;" + "Extended Properties='Excel 12.0;" + "HDR=YES;" + "IMEX=1;'"; } else if (sFileExtension.ToUpper() == ".CSV") { string temp = Path.GetDirectoryName(FileName); M_str_sqlcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + temp + ";" + "Extended Properties='Text;HDR=NO;IMEX=1'"; } System.Data.OleDb.OleDbConnection OleDbCon = new System.Data.OleDb.OleDbConnection(M_str_sqlcon); return(OleDbCon); }
private void search(String file) { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" + file + "';Extended Properties='Excel 8.0;HDR=Yes;'"; string query = "select * from [PSC_ECs$A4:X2000] WHERE F1 <> ''"; db.OleDbConnection cn = new db.OleDbConnection(); cn.ConnectionString = strConn; cn.Open(); db.OleDbDataAdapter da = new db.OleDbDataAdapter(query, cn); ds = new DataSet(); da.Fill(ds, "tbl"); dtgData.DataSource = ds.Tables["tbl"]; cn.Close(); }
public TTopic createTopic(TUser tUserStarter, TCourse tCourse, string title) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbDataReader dbDataReader; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; TTopic tTopic = new TTopic(tCourse, title); try { //Begin Transaction tTopic.startDateTime = DateTime.Now; tTopic.starterTeacher = tUserStarter; dbCommand.CommandText = "INSERT INTO Topics (course_id, group_id, title, startDateTime, starterTeacher) VALUES('" + tTopic.tCourse.id + "', '" + tTopic.tCourse.groupId + "', '" + tTopic.title + "', '" + tTopic.startDateTime.ToString() + "', '" + tUserStarter.id + "')"; dbCommand.ExecuteNonQuery(); dbCommand.CommandText = "SELECT * FROM Topics WHERE (course_id = '" + tTopic.tCourse.id + "') AND (group_id = '" + tTopic.tCourse.groupId + "') AND (title = '" + tTopic.title + "') AND (startDateTime = '" + tTopic.startDateTime.ToString() + "')"; dbDataReader = dbCommand.ExecuteReader(); dbDataReader.Read(); tTopic.id = Convert.ToInt32(dbDataReader["id"]); dbDataReader.Close(); dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); return(null); } dbCommand.Transaction = null; dbTransaction = null; dbDataReader.Close(); dbConnection.Close(); return(tTopic); }
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 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); }
protected void ListBox4_SelectedIndexChanged(object sender, EventArgs e) { ListBox1.ClearSelection(); ListBox2.ClearSelection(); ListBox3.ClearSelection(); ListBox5.ClearSelection(); ListBox6.Items.Clear(); String selection = ListBox4.SelectedItem.Text; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data source= C:\Users\OK\Documents\Ecafe.accdb"; conn.Open(); String my_querry1 = "select Item from Items where Type = '" + selection + "'"; OleDbCommand cmd1 = new OleDbCommand(my_querry1, conn); var dr1 = cmd1.ExecuteReader(); // MessageBox.Show("Query executed"); while (dr1.Read()) { ListBox6.Items.Add(dr1[0].ToString()); //MessageBox.Show(dr1[1].ToString()); } Label2.Text = selection; ListBox6.Visible = true; ListBox7.Visible = true; ListBox8.Visible = true; ListBox9.Visible = true; Label1.Visible = true; Label2.Visible = true; Label3.Visible = true; Label5.Visible = true; Label6.Visible = true; Label7.Visible = true; Label8.Visible = true; Label9.Visible = true; Label10.Visible = true; TextBox2.Visible = true; Button1.Visible = true; Button2.Visible = true; }
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { string strExcelPathName, Table = ""; OpenFileDialog openDialog = new OpenFileDialog(); openDialog.Title = "Select file"; openDialog.InitialDirectory = @"c:\"; openDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*"; openDialog.FilterIndex = 1; openDialog.RestoreDirectory = true; try { if (openDialog.ShowDialog() == DialogResult.OK) { if (openDialog.FileName != "") { strExcelPathName = openDialog.FileName; //TxtPath.Text = strExcelPathName.ToString(); // comboBox1.DataSource = GetSheetNames(openDialog.FileName); System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;;Data Source=" + strExcelPathName + ";Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "Net-informations.com"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close(); //return ds.Tables[0]; } else { MessageBox.Show("chose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } finally { } }
static void TestLocalVpaModel() { //string databaseName = "Contoso"; //const string serverName = @".\tab17"; // string databaseName = "CalculationGroups_Currency"; // const string serverName = @".\tab19"; string databaseName = "338e5409-5010-4864-8226-722165625dd5"; const string serverName = @"localhost:49851"; var connStr = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog={databaseName};"; var conn = new System.Data.OleDb.OleDbConnection(connStr); Dax.Metadata.Model m = new Dax.Metadata.Model(); Dax.Metadata.Extractor.DmvExtractor.PopulateFromDmv(m, conn, serverName, databaseName, "Test", "0.1"); Dax.Metadata.Extractor.StatExtractor.UpdateStatisticsModel(m, conn, 10); DumpRelationships(m); }
static void TestLocalVpaModel() { //string databaseName = "Contoso"; //const string serverName = @".\tab17"; // string databaseName = "CalculationGroups_Currency"; // const string serverName = @".\tab19"; string databaseName = "32f0add7-3e4c-4fd7-8e26-3961f25d7c5a"; const string serverName = @"localhost:60812"; var connStr = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog={databaseName};"; var conn = new System.Data.OleDb.OleDbConnection(connStr); Dax.Metadata.Model m = new Dax.Metadata.Model(); Dax.Metadata.Extractor.DmvExtractor.PopulateFromDmv(m, conn, serverName, databaseName, "Test", "0.1"); Dax.Metadata.Extractor.StatExtractor.UpdateStatisticsModel(m, conn, 10); DumpRelationships(m); }
//?要怎麼加try catch比較好呢 //使用方法 // System.Data.DataTable dt = new func_excel_read_ole().CreateDataSource(@"Z:\cadmen\服務部\客戶需求單\YFY\ERP標準書(SAP).xls;", "內容物重量"); // for (int i = 0; i < dt.Rows.Count; i++) // { // string msg = dt.Rows[i]["內容物"].ToString() + "--" + dt.Rows[i]["重量"].ToString(); // MessageBox.Show(msg); // } public DataTable CreateDataSourceTop1(string filePath, string sheetName) { DataSet myDataSet = new DataSet(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source={0};" + "Extended Properties='Excel 8.0;IMEX=1;HDR=yes;'"; strConn = string.Format(strConn, filePath); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); System.Data.OleDb.OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter("SELECT top 1 * FROM [" + sheetName + "$]", strConn); Adapter.Fill(myDataSet); return(myDataSet.Tables[0]); }
//更新項目明細 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 DataSet Listar(string OLE) { try { Dbcon = new System.Data.OleDb.OleDbConnection(StringConexao); Dbcon.Open(); DBDA = new System.Data.OleDb.OleDbDataAdapter(OLE, Dbcon); DBDA.Fill(ds); } finally { Dbcon.Close(); } return(ds); }
//Sing : Login for Front-end. //Function to authenticate a user based on credentials entered. //Entering username: admin and password: admin will return true. public static bool authenticateUser(string username, string password) { try // in case database connection fails. { //Sing : login database declarations System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(); OleDbDataAdapter ad; DataTable dtable = new DataTable(); OleDbCommand command = new OleDbCommand(); //Sing : Login database connection connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=User.mdb;Jet OLEDB:Database Password=;"; command.Connection = connection; //Sing:Opening a connection to the database connection.Open(); //Sing: defining the query ad = new OleDbDataAdapter("select * from Accounts where username ='******'and password='******'", connection); //Filling the table adaptor ad.Fill(dtable); //If statement for log in authenticaion - Checks if username and password is present in the Accounts table. Also checks whether admin details have been entered. if (dtable.Rows.Count <= 0) { //Details do not exist in the database connection.Close(); return(false); } else if (dtable.Rows.Count > 0 && username == "admin" && password == "admin") { //Data exists in the database, therefore return true as admin credentials have been entered. connection.Close(); return(true); } else { return(false); } } catch (Exception) { return(false); } }
public System.Data.OleDb.OleDbConnection ConnectToAccess() { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= C:\Program Files\Imi Project\Pro-Dictionary\dizionario.mdb"; try { conn.Open(); // Insert code to process data. } catch (Exception ex) { MessageBox.Show("Failed to connect to data source"); } return(conn); }
public System.Data.DataTable GetWorksheet(string worksheetName) { if (!worksheetName.Contains("$")) { worksheetName = worksheetName + "$"; } OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString); OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter( "select * from [" + worksheetName + "]", con); con.Open(); System.Data.DataSet excelDataSet = new DataSet(); cmd.Fill(excelDataSet); con.Close(); return(excelDataSet.Tables[0]); }
public void CSVToDataGridView(string filePath, DataGridView dgv) { char[] chr = new char[1] { '\\' }; string[] strs = filePath.Split(chr, StringSplitOptions.RemoveEmptyEntries); string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath.Substring(0, filePath.Length - strs[strs.Length - 1].Length) + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\""; System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr); con.Open(); System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strs[strs.Length - 1] + "]", con); DataTable dt = new DataTable(); adapter.Fill(dt); con.Close(); dgv.DataSource = dt; }
public void enterTopic(TUser tUser, TTopic tTopic) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; System.Data.OleDb.OleDbDataReader dbDataReader; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //Begin Transaction //Verify if the user is already on-line dbCommand.CommandText = "SELECT * FROM User_Topic WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL"; dbDataReader = dbCommand.ExecuteReader(); if (dbDataReader.HasRows) { dbDataReader.Close(); dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL"; dbCommand.ExecuteNonQuery(); } dbDataReader.Close(); dbCommand.CommandText = "INSERT INTO User_Topic (user_id, topic_id, startDateTime) VALUES ('" + tUser.id + "', '" + tTopic.id + "', '" + DateTime.Now.ToString() + "')"; dbCommand.ExecuteNonQuery(); tUser.topic = tTopic; dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
private void button1_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data source= C:\Users\OK\Documents\Library.accdb"; try { conn.Open(); String username = textBox1.Text.ToString(); String Paswrd = textBox2.Text.ToString(); String my_querry = "select * from tblMembers where UserName = '******' and Pasword = '" + Paswrd + "'"; OleDbCommand cmd = new OleDbCommand(my_querry, conn); //OleDbDataReader dr = cmd.ExecuteReader(); var dr = cmd.ExecuteReader(); if (dr.Read() == true) { MessageBox.Show("Login Successful"); // MessageBox.Show(dr[0].ToString()); this.Hide(); //Form3 f3 = new Form3(); //f3.Show(); //this.Hide(); Form4 f4 = new Form4(); f4.Show(); } else { MessageBox.Show("Invalid Credentials, Please Re-Enter"); } } catch (Exception ex) { MessageBox.Show("Failed due to" + ex.Message); } finally { conn.Close(); } }
/// <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(); } } }
/// <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]); }
//打开恢复数据库专用的数据连接,用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 void XLSX2Grid(GridControl gc) { OpenFileDialog choofdlog = new OpenFileDialog(); choofdlog.Filter = "Excel file (*.xlsx)|*.xlsx"; choofdlog.ShowDialog(); //string filePath = System.IO.Path.GetDirectoryName(choofdlog.FileName); string filePath = choofdlog.FileName; //XtraMessageBox.Show(filePath); System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; string conStr; if (Path.GetExtension(filePath).ToLower().Trim() == ".xls" && Environment.Is64BitOperatingSystem == false) { conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR= Yes" + ";IMEX=0\""; } else { conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR= Yes" + ";IMEX=0\""; } MyConnection = new System.Data.OleDb.OleDbConnection(conStr); //XtraMessageBox.Show(MyConnection.State.ToString()); if (choofdlog.ShowDialog() == DialogResult.OK) { MyConnection.Open(); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet$]", MyConnection); //MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", MyConnection); MyCommand.TableMappings.Add("Table", "Net-informations.com"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); gc.DataSource = DtSet.Tables[0]; MyConnection.Close(); } else { choofdlog.Dispose(); } }
public static List <List <string> > GetTasks(string filename) { var path = ProductProperties.NetworkStoragePlace + filename;// System.IO.Path.GetFullPath(filename); List <List <string> > result = new List <List <string> >(); if (DoFileExist(path)) { try { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + ProductProperties.InputFileSheetName + "$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); var rows = DtSet.Tables[0].Rows; for (int i = 0; i < rows.Count; i++) { var a = rows[i].ItemArray; if (!a.Any(x => x.GetType() == typeof(System.DBNull))) { result.Add(new List <string>() { (string)a[0], //CRK_NumerPelny (string)a[1], //Ope_Kod (string)a[2], //Knt_Kod (string)a[3], //Kat_KodOgolny //(string)a[4],//CRK_DataDok Convert.ToString(Convert.ToInt32(a[5])), //CRK_CRKId }); } } MyConnection.Close(); return(result); } catch (Exception ex) { } } return(null); }
public int OutExcel() { DataTable dt = GetDataTable(); string FileName = Guid.NewGuid().ToString().Substring(8) + ".xlsx"; string sNewFullFile = "C:\\" + FileName; string strConn = GetConnectionString(sNewFullFile); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); OleDbCommand cmd = null; try { conn.Open(); cmd = new OleDbCommand("create table [Sheet1]( [位置编码] Text,[关联位置] Text,[公司编号] Text,[计划工厂] Text,[维护工厂] Text,[工厂区域] Text,[计划员组] Text,[成本中心] Text,[位置类型] Text,[位置状态] Text,[开始日期] Text,[修改日期] Text,[业务范围] Text,[是否安装] Text,[包含删除] Text,[维护班组] Text)", conn); cmd.ExecuteNonQuery(); for (int i = 0; i < dt.Rows.Count; i++) { var sRows = "INSERT INTO [Sheet1$] ([位置编码], [关联位置],[公司编号],[计划工厂],[维护工厂],[工厂区域],[计划员组],[成本中心],[位置类型],[位置状态],[开始日期],[修改日期],[业务范围],[是否安装],[包含删除],[维护班组]) VALUES ("; for (int j = 1; j < dt.Columns.Count; j++) { sRows += "'" + dt.Rows[i][j] + "',"; } sRows = sRows.TrimEnd(','); string strSQL = sRows + ")"; cmd = new OleDbCommand(strSQL, conn); cmd.ExecuteNonQuery(); } return(1); } catch (Exception er) { throw er; } finally { if (cmd != null) { cmd.Dispose(); } conn.Dispose(); } }
private void bt_load_Click(object sender, EventArgs e) { /*string cont = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+tb_import.Text+";Extend Properties=\"Excel 8.0;HDR=Yes;\";"; * OleDbConnection cone = new OleDbConnection(cont); * OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM [" + tb_load.Text + "$]", cone); * DataTable dt = new DataTable(); * dgv_siswa.DataSource = dt;*/ /*if (System.IO.File.Exists(tb_import.Text)) * { * string constring = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; * Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""", tb_import.Text); * string query = String.Format("select * from [{0}$]", "Sheet1"); * OleDbDataAdapter adp = new OleDbDataAdapter(query, constring); * DataSet ds = new DataSet(); * adp.Fill(ds); * dgv_siswa.DataSource = ds.Tables[0]; * } * else * { * MessageBox.Show("Tidak Ada File Yang Di Load"); * }*/ /*if (System.IO.File.Exists(tb_import.Text)) * { * string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", tb_import.Text); * * //string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text); * string query = String.Format("select * from [{0}$]", tb_import); * OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString); * DataSet dataSet = new DataSet(); * dataAdapter.Fill(dataSet); * //DataTable dtView = dataSet.Tables[0]; * dgv_siswa.DataSource = dataSet.Tables[0]; * }*/ System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tb_file.Text + ";Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "Net-informations.com"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dgv_siswa.DataSource = DtSet.Tables[0]; MyConnection.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; }