private void btnOk_Click(object sender, EventArgs e) { string sql = "select * from [staff] where [scode]=@scode and [password]=@password"; OleDbCommand cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection()); cmd.Parameters.AddWithValue("scode", ((StaffModel) cboUsername.SelectedValue).Value); cmd.Parameters.AddWithValue("password", txtPassword.Text); OleDbDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if(!rdr.GetBoolean(rdr.GetOrdinal("isactive"))) { MessageBox.Show("Your account has been deactivated. Please consult your administration regarding this"); rdr.Close(); cmd.Dispose(); return; } Program.loginStaff = new StaffModel(rdr.GetInt32(rdr.GetOrdinal("staffno")), rdr.GetString(rdr.GetOrdinal("scode")), rdr.GetString(rdr.GetOrdinal("sname")), ""); rdr.Close(); cmd.Dispose(); graceClose = true; Close(); } else { MessageBox.Show("You have supply a wrong credential", Application.ProductName); return; } }
public bool IsValidModerator(string login, string password) { var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Użytkownicy.accdb"); var cmd = new OleDbCommand("SELECT [Login] FROM [Users] " + @"WHERE [Login] = @l AND [Hasło] = @p AND [Prawa] = @a") { Connection = conn }; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("@l", OleDbType.VarWChar)).Value = login; cmd.Parameters.Add(new OleDbParameter("@p", OleDbType.VarWChar)).Value = password; cmd.Parameters.Add(new OleDbParameter("@a", OleDbType.VarWChar)).Value = "Moderator"; conn.Open(); var reader = cmd.ExecuteReader(); if (reader != null && reader.HasRows) { reader.Dispose(); cmd.Dispose(); return true; } else { reader?.Dispose(); cmd.Dispose(); return false; } }
private void btnNew_Click( object sender, EventArgs e ) { if( Program.UserType == UserType.Customer ) return; AddDlg dlg = new AddDlg(); if( dlg.ShowDialog( this ) != DialogResult.OK ) return; RefillComboBoxes(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "select top 1 * from WedDress order by [ID] desc"; cmd.Connection = Program.Database; cmd.CommandType = CommandType.Text; OleDbDataAdapter oda = new OleDbDataAdapter( cmd); DataTable dt = new DataTable(); oda.Fill( dt ); dsDress.Tables["WedDress"].Merge( dt ); dt.Dispose(); oda.Dispose(); cmd.Dispose(); }
private void btnDel_Click( object sender, EventArgs e ) { if( Program.UserType != UserType.Admin ) return; if( dataGridView.CurrentRow == null ) return; int id = (int)dataGridView.CurrentRow.Cells[0].Value; DataTable dt = dsDress.Tables["WedDress"]; DataRow[] rows = dt.Select( "[ID]=" + id ); if( rows.GetLength( 0 ) <= 0 ) return; if( MessageBox.Show( this, "��ȷʵҪɾ��ѡ���ķ�װ��?", Program.AppTitle, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2 ) == DialogResult.No ) return; OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "delete from [WedDress] where [ID]=" + id.ToString(); cmd.CommandType = CommandType.Text; cmd.Connection = Program.Database; cmd.ExecuteNonQuery(); cmd.Dispose(); dt.Rows.Remove( rows[0] ); }
public static int ExecuteSql(string SQLString) { int num2; using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand(SQLString, connection); try { connection.Open(); num2 = command.ExecuteNonQuery(); } catch (OleDbException exception) { connection.Close(); throw new Exception(exception.Message); } finally { if (command != null) { command.Dispose(); } } } return num2; }
public NewRentDlg( int nDressId, DateTime date ) { m_nDressId = nDressId; m_date = date; m_bModify = true; InitializeComponent(); OleDbCommand oc = new OleDbCommand(); oc.CommandText = "select startdate, enddate, customer from rent where dressid=? and ? between startdate and enddate"; oc.CommandType = CommandType.Text; oc.Connection = Program.Database; oc.Parameters.Add( "a", OleDbType.Integer ).Value = m_nDressId; oc.Parameters.Add( "b", OleDbType.Date ).Value = date; OleDbDataReader reader = oc.ExecuteReader(); reader.Read(); dtStartDate.Value = reader.GetDateTime( 0 ); dtEndDate.Value = reader.GetDateTime( 1 ); txtCustomer.Text = reader.GetString( 2 ); reader.Dispose(); oc.Dispose(); this.Text = "�ij�����Ϣ"; }
/// <summary> /// kjhkdfhgkjh /// </summary> /// <param name="sSQL"></param> /// <param name="conecOledb"></param> /// <returns> Ele retorna algo de bom</returns> protected static OleDbDataReader cria_DataReader_OleDb(String sSQL, OleDbConnection conecOledb) { OleDbCommand comando = new OleDbCommand(sSQL, conecOledb); OleDbDataReader dr = comando.ExecuteReader(CommandBehavior.CloseConnection); comando.Dispose(); return dr; }
public String[] GetFirstnames() { List<string> items = new List<string>(); string strSQL = ""; OleDbDataReader objReader = null; OleDbConnection objConn = null; OleDbCommand objCommand = null; strSQL = "SELECT firstname from Employees ORDER BY firstname"; objConn = new OleDbConnection(GetconnectstringLocal()); objConn.Open(); objCommand = new OleDbCommand(strSQL, objConn); objReader = objCommand.ExecuteReader(); while (objReader.Read()) { items.Add(objReader["firstname"].ToString()); } objReader.Close(); objReader.Dispose(); objReader = null; objCommand.Dispose(); objCommand = null; objConn.Close(); objConn.Dispose(); objConn = null; return items.ToArray(); }
public static bool delete_tis_color(String p_id) { try { String strQuery = String.Empty; OleDbCommand sqlComm = new OleDbCommand(); // strQuery = String.Empty; strQuery += "DELETE FROM [tis_color]"; strQuery += " WHERE [color_id] = '" + p_id + "'"; // connect_db(); sqlComm.Connection = sql_conn; sqlComm.CommandText = strQuery.ToString(); sqlComm.ExecuteNonQuery(); sqlComm.Dispose(); disconnect_db(); // return true; } catch (Exception e) { PCMSG.ShowError(e); return false; } }
/// <summary> /// 读取Excel文件的表头 指定Sheet /// </summary> /// <param name="filepath">文件路径</param> /// <returns>DataTable</returns> public static DataTable ReadExcelHeader(string fileName, string sheet) { 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=YES;\""; System.Data.OleDb.OleDbConnection conn = null; System.Data.OleDb.OleDbCommand oledbCommd = null; try { conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = connStr; conn.Open(); string sqlText = "select top 1 * from [" + sheet + "]"; 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 AttributesForm_Load(object sender, EventArgs e) { try { OleDbCommand command = new OleDbCommand(srcCommand, Con); this.attributeSchemaXml = Convert.ToString(command.ExecuteScalar()); command.Dispose(); this.attributeSet = new DataSet("AttributesSet"); if ((this.attributeSchemaXml == null) || (this.attributeSchemaXml.Length < 1)) { this.attributesTable = this.attributeSet.Tables.Add("AttributesTable"); this.attributesTable.Columns.Add("Attribute Name"); this.attributesTable.Columns.Add("Attribute Value"); DataRow row = this.attributesTable.NewRow(); row["Attribute Name"] = "Enter name Of attribute"; row["Attribute Value"] = "Enter attribute value"; this.attributesTable.Rows.Add(row); this.AttributesGrid.SetDataBinding(this.attributeSet, this.attributeSet.Tables[0].TableName); } else { this.attributeSet.ReadXml(new XmlTextReader(new StringReader(this.attributeSchemaXml))); this.attributeSchemaXml = null; this.AttributesGrid.SetDataBinding(this.attributeSet, this.attributeSet.Tables[0].TableName); } } catch (Exception exception) { BusinessLogic.MyMessageBox(exception.Message); } }
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; }
public String[] SutunAdlari(String cmdSorgu) { String[] dizi = null; try { Kontrol(); cmd = new OleDbCommand(cmdSorgu, bglnti); dR = cmd.ExecuteReader(); dizi = new String[dR.FieldCount]; for (int i = 0; i < dR.FieldCount; i++) { dizi[i] = dR.GetName(i); } dR.Close(); dR.Dispose(); cmd.Dispose(); } catch (OleDbException ole) { MessageBox.Show(ole.Message); } catch (Exception e) { MessageBox.Show(e.Message); } finally { bglnti.Close(); } return(dizi); }
/// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand cmd = new OleDbCommand(SQLString, connection); System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", OleDbType.VarChar); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.OleDb.OleDbException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
/// <summary> ///执行Sql语句 /// </summary> /// <param name="str_Sql">要执行的Sql语句</param> public string ExeSql(string str_Sql) { string errorstring = Open(); if (errorstring != "OK") { return(errorstring); } //myCommand = new SqlCommand(str_Sql,myConnection); myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection); try { myCommand.ExecuteNonQuery(); } catch (SqlException e) { string errorMessage = e.Message; return(errorMessage); } finally { myCommand.Dispose(); } return("OK"); }
/// <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 static bool DownLoadXml(string fileID, string filePath) { bool ret = true; string errMsg=""; try { OleDbConnection db2conn = new OleDbConnection(DBdb2.SetConString()); string sqlstr = "select * from T_SYS_MENU where T_XMLID='" + fileID + "'"; OleDbCommand db2cmd = new OleDbCommand(sqlstr, db2conn); db2conn.Open(); OleDbDataReader db2reader = db2cmd.ExecuteReader(); string FileName = filePath; if (!db2reader.Read()) { FileName = ""; } else { byte[] bytes = (byte[])db2reader["B_XML"]; FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); fs.Write(bytes, 0, bytes.Length); fs.Flush(); fs.Close(); } db2reader.Close(); db2cmd.Dispose(); db2conn.Close(); } catch (Exception ce) { errMsg = ce.Message; ret = false; } return ret; }
//Retourne les stats public static Statistique getSats(int noJoueur) { Statistique stat = null; OleDbConnection connexion = new OleDbConnection(connBD); try { connexion.Open(); commande = new OleDbCommand("SELECT * FROM tblStatistique WHERE noJoueur=@noJoueur", connexion); commande.Parameters.Add("@noJoueur", OleDbType.Integer).Value = noJoueur; OleDbDataReader reader = commande.ExecuteReader(); while (reader.Read()) { int nbGagne = reader["nbPartieGagne"] == DBNull.Value ? 0 : Convert.ToInt32(reader["nbPartieGagne"]); int nbPerdu = reader["nbPartiePerdu"] == DBNull.Value ? 0 : Convert.ToInt32(reader["nbPartiePerdu"]); int score = reader["score"] == DBNull.Value ? 0 : Convert.ToInt32(reader["score"]); stat = new Statistique(nbGagne, nbPerdu, score); } return stat; } catch (Exception e) { throw new Exception(e.Message); } finally { commande.Dispose(); connexion.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { if (Session["oturumid"] == null) Response.Redirect("giris.aspx"); else if (Session["oturumdurum"].ToString() == "1") Response.Redirect("adminpaneli.aspx"); else { string fid = (sender as Button).CommandArgument; string kid = Session["oturumid"].ToString(); string sepet = ""; OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb")); cnn.Open(); OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + kid, cnn); OleDbDataReader rdr = cmd.ExecuteReader(); rdr.Read(); sepet = rdr[0].ToString(); if (sepet == "") sepet = fid; else sepet += "," + fid; cmd.Dispose(); cmd = new OleDbCommand("update uyeler set sepet='" + sepet + "' where uyeid=" + kid, cnn); cmd.ExecuteNonQuery(); cnn.Close(); Session.Add("um", "Fotoğraf sepetinize eklendi. İyi alışverişler."); Response.Redirect("uyepaneli.aspx"); } }
protected void btnInstall_Click(object sender, EventArgs e) { string ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\{0}.mdb;", txtDatabaseName.Text); OleDbConnection OConn = new OleDbConnection(ConnectionString); StreamReader Sr = new StreamReader(Server.MapPath("~/Setup/Scripts/Access.sql")); try { File.Copy(Server.MapPath("~/Setup/Scripts/Blogsa.mdb"), Server.MapPath(string.Format("~/App_Data/{0}.mdb", txtDatabaseName.Text))); //Update WebSite Url string strUrl = Request.Url.AbsoluteUri.Substring(0 , Request.Url.AbsoluteUri.IndexOf(Request.Url.AbsolutePath) + (Request.ApplicationPath.Equals("/") ? 0 : Request.ApplicationPath.Length)) + "/"; OConn.Open(); while (!Sr.EndOfStream) { //Create DB string Commands = Sr.ReadLine().ToString(); if (!Commands.StartsWith("/*")) { OleDbCommand OComm = new OleDbCommand(Commands, OConn); OComm.ExecuteNonQuery(); OComm.Dispose(); } } Sr.Close(); string strLang = (string)Session["lang"]; string strRedirectPage = String.Format("Completed.aspx?Setup={0}&lang={1}", BSHelper.SaveWebConfig(ConnectionString, "System.Data.OleDb"), strLang); Response.Redirect(strRedirectPage, false); } catch (Exception ex) { BSLog l = new BSLog(); l.CreateDate = DateTime.Now; l.LogType = BSLogType.Error; l.LogID = Guid.NewGuid(); l.RawUrl = Request.RawUrl; l.Source = ex.Source; l.StackTrace = ex.StackTrace; l.TargetSite = ex.TargetSite; l.Url = Request.Url.ToString(); l.Save(); divError.Visible = true; lblError.Text = ex.Message; if (OConn.State == ConnectionState.Open) { OConn.Close(); } File.Delete(Server.MapPath("~/App_Data/" + txtDatabaseName.Text)); } finally { if (OConn.State == ConnectionState.Open) OConn.Close(); Sr.Close(); } }
/// <summary> /// Executes an inline SQL statement and returns a data table. /// </summary> /// <param name="dbStatement">Inline SQL</param> /// <param name="connectionString">Connection string</param> /// <returns>Data table containing the return data</returns> public static DataTable RunQuery(string dbStatement, string connectionString) { OleDbConnection dbConnection = null; OleDbCommand dbCommand = null; OleDbDataAdapter adapter = null; DataTable dt = null; try { dbConnection = new OleDbConnection(connectionString); dbCommand = new OleDbCommand(dbStatement, dbConnection); dbCommand.CommandType = CommandType.Text; dbCommand.CommandTimeout = 600; adapter = new OleDbDataAdapter(dbCommand); dt = new DataTable(); dbConnection.Open(); adapter.Fill(dt); return dt; } finally { if (adapter != null) adapter.Dispose(); if (dbCommand != null) dbCommand.Dispose(); if (dbConnection != null) dbConnection.Dispose(); } }
protected void Button1_Click(object sender, EventArgs e) { Label6.Visible = false; OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb")); cnn.Open(); OleDbCommand cmd = new OleDbCommand("select * from uyeler where uyeadi='" + TextBox1.Text + "'",cnn); OleDbDataReader rdr = cmd.ExecuteReader(); if(rdr.Read()==true) { Label6.Visible = true; Label6.Text = "Bu kullanıcı adı zaten alınmış."; } else { cmd.Dispose(); string sorgu = "insert into uyeler(uyeadi,parola,adsoyad,mail,durum) values('" + TextBox1.Text + "', '" + TextBox4.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '0')"; cmd = new OleDbCommand(sorgu, cnn); cmd.ExecuteNonQuery(); cnn.Close(); Server.Transfer("yenikayit.aspx"); } cnn.Close(); }
private void chkComplete_CheckedChanged(object sender, EventArgs e) { string sql = "update bulletin set [complete]=true where [seqno]=@seqno"; OleDbCommand cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection()); cmd.Parameters.AddWithValue("seqno", Program.listBulletin[iPosMessage].seqNo); cmd.ExecuteNonQuery(); cmd.Dispose(); //check it sql = "select [complete] from bulletin where seqno=@seqno"; cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection()); cmd.Parameters.AddWithValue("seqno", Program.listBulletin[iPosMessage].seqNo); OleDbDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (rdr.GetBoolean(rdr.GetOrdinal("complete"))) { chkComplete.Checked = true; Program.listBulletin[iPosMessage].complete = true; } else { MessageBox.Show("Unable to mark this task as complete! Please consult your system administration", Application.ProductName); } } rdr.Close(); cmd.Dispose(); }
protected void btnlogout_Click(object sender, EventArgs e) { conlogout = i.GetOledbDbConnection(); cmdlogout = i.GetOledbDbCommand(); try { conlogout = DBConnection.GetConnection(); cmdlogout.Connection = conlogout; cmdlogout.CommandType = CommandType.StoredProcedure; cmdlogout.CommandText = "Logout"; cmdlogout.Parameters.AddWithValue("@USERNAME",s); int result = cmdlogout.ExecuteNonQuery(); if (result == 1) { // Response.Write("logout successfull"); Response.Redirect("Login.aspx"); Session.Clear(); } else Response.Write("logout unsuccessfull"); } catch (Exception ex) { Response.Write("logout unsuccessfull"); } finally { cmdlogout.Dispose(); conlogout.Dispose(); } }
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 * FROM REPS;"; // 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); myConnection.Open(); // set chart data source - the data source must implement IEnumerable Chart1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // set series members names for the X and Y values Chart1.Series["Series 1"].XValueMember = "Name"; Chart1.Series["Series 1"].YValueMembers = "Sales"; // data bind to the selected data source Chart1.DataBind(); myCommand.Dispose(); myConnection.Close(); }
public static DataTable GetData(string strConn, string strSql) { DataTable dt = new DataTable("td"); using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); OleDbCommand cmd = null; OleDbDataAdapter da = null; try { cmd = new OleDbCommand(strSql, conn); da = new OleDbDataAdapter { SelectCommand = cmd }; da.Fill(dt); return dt; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
public int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) { try { OleDbConnection conn = null; OleDbCommand cmd = null; try { conn = new OleDbConnection(SqlHelper.ConnString); conn.Open(); cmd = new OleDbCommand(GenerateQuery(true, authenticationOption), conn); cmd.CommandTimeout = CommandTimeout; cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", OleDbType.VarChar, userInactiveSinceDate.ToUniversalTime())); return cmd.ExecuteNonQuery(); } finally { if (cmd != null) { cmd.Dispose(); } if (conn != null) { conn.Close(); conn = null; } } } catch { throw; } }
/// <summary> /// Execute Insert,Update /// </summary> /// <param name="sql"></param> /// <param name="cmdParams"></param> /// <returns></returns> public static int ExecuteNonQuery(string strSql, params OleDbParameter[] cmdParams) { OleDbConnection sqlCon = OleDbConnect(); OleDbCommand sqlCmd = new OleDbCommand(strSql, sqlCon); sqlCmd.CommandType = CommandType.Text; //OleDbTransaction trans = sqlCon.BeginTransaction(); //sqlCmd.Transaction = trans; if (cmdParams != null) { foreach (OleDbParameter parm in cmdParams) { sqlCmd.Parameters.Add(parm); } } try { sqlCon.Open(); int num = sqlCmd.ExecuteNonQuery(); //trans.Commit(); return num; } catch { //trans.Rollback(); return 0; } finally { sqlCmd.Dispose(); sqlCon.Close(); sqlCon.Dispose(); } }
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { int result; using (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.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@fs", System.Data.OleDb.OleDbType.Binary); oleDbParameter.Value = fs; oleDbCommand.Parameters.Add(oleDbParameter); try { oleDbConnection.Open(); int num = oleDbCommand.ExecuteNonQuery(); result = num; } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } finally { oleDbCommand.Dispose(); oleDbConnection.Close(); } } return(result); }
public static int ExecuteSql(string SQLString, string content) { int result; using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString)) { System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection); System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@content", System.Data.OleDb.OleDbType.VarChar); oleDbParameter.Value = content; oleDbCommand.Parameters.Add(oleDbParameter); try { oleDbConnection.Open(); int num = oleDbCommand.ExecuteNonQuery(); result = num; } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } finally { oleDbCommand.Dispose(); oleDbConnection.Close(); } } return(result); }
public static OleDbDataReader DataReader(OleDbConnection conn, string sqlText, OleDbParameter[] parms) { OleDbCommand sqlCommand = new OleDbCommand(); PrepareCommand(conn, sqlCommand, sqlText, parms); OleDbDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); sqlCommand.Dispose(); return reader; }
//הפעולה מקבלת שם מסד נתונים ומחרוזת מחיקה/ הוספה/ עדכון //ומבצעת את הפקודה על המסד הפיזי public static void DoQuery(string sqlQuery) { OleDbConnection conn = ConnectToDb(); conn.Open();//פתיחת חיבור למסד הנתונים OleDbCommand com = new OleDbCommand(sqlQuery, conn);//הגדרת אובייקט commend לטיפול בפעולת sql לעדכון המסד com.ExecuteNonQuery(); com.Dispose(); conn.Close();//סגירת חיבור }
//הפעולה מקבלת שם מסד נתונים ומחרוזת מחיקה/ הוספה/ עדכון //ומבצעת את הפקודה על המסד הפיזי /// <summary> /// To Execute update / insert / delete queries /// הפעולה מקבלת שם קובץ ומשפט לביצוע ומבצעת את הפעולה על המסד /// </summary> public static void DoQuery(string fileName, string sql) { OleDbConnection conn = ConnectToDb(fileName); conn.Open(); OleDbCommand com = new OleDbCommand(sql, conn); com.ExecuteNonQuery(); com.Dispose(); conn.Close(); }
static void Main(string[] args) { try { FileStream fs = new FileStream("connstr.txt", FileMode.Open); byte[] connstr = new byte[fs.Length]; fs.Read(connstr, 0, (int)fs.Length); fs.Close(); StringBuilder sb = new StringBuilder(); for(int i = 0; i < connstr.Length; i++) sb.Append((char)connstr[i]); connStrMdb = sb.ToString().Split('\n')[0].Replace("\r",""); connStrMSSql = sb.ToString().Split('\n')[1].Replace("\r", ""); accessConn = new OleDbConnection(connStrMdb); msSqlConn = new SqlConnection(connStrMSSql); accessConn.Open(); msSqlConn.Open(); } catch (Exception e) { Console.WriteLine(e.Message); Console.ReadKey(); return; } OleDbCommand accessCmd = new OleDbCommand(); accessCmd.Connection = accessConn; SqlCommand msSqlCmd = new SqlCommand(); msSqlCmd.Connection = msSqlConn; msSqlCmd2.Connection = msSqlConn; msSqlCmd2.CommandText = "delete FPObject"; msSqlCmd2.ExecuteNonQuery(); msSqlCmd2.CommandText = "delete Sequences"; msSqlCmd2.ExecuteNonQuery(); migrateCustomer(accessCmd, msSqlCmd); migratePrint_Job_Category(accessCmd, msSqlCmd); migratePrintJob(accessCmd, msSqlCmd); migratePrintJob_Detail1(accessCmd, msSqlCmd); migratePrintJobLookUp(accessCmd, msSqlCmd); migratePrintOrder(accessCmd, msSqlCmd); migrateUserAC(accessCmd, msSqlCmd); msSqlCmd2.CommandText = "insert into Sequences values('ObjectId'," + ObjectId + ")"; msSqlCmd2.ExecuteNonQuery(); accessCmd.Dispose(); msSqlCmd.Dispose(); accessConn.Close(); msSqlConn.Close(); }
public static void GetAllProtocolIP() { string strSql; try { tVehInfo nVehInfo = null; System.Data.OleDb.OleDbCommand GetProTypeCommand = null; System.Data.OleDb.OleDbDataReader GetProTypeReader = null; strSql = "select distinct IpAddress,type,TaxiNo from vehicle"; GetProTypeCommand = myConn.CreateCommand(); GetProTypeCommand.CommandText = strSql; GetProTypeReader = GetProTypeCommand.ExecuteReader(); CarInfo_Hash = new Hashtable(); while (GetProTypeReader.Read()) { nVehInfo = new tVehInfo(); nVehInfo.ipaddress = GetProTypeReader["IpAddress"].ToString(); if (GetProTypeReader["type"].ToString().ToUpper() == "TCP") { nVehInfo.isendtype = 1; } else { nVehInfo.isendtype = 0; } if (CarInfo_Hash.ContainsKey((string)GetProTypeReader["IpAddress"])) { CarInfo_Hash[(string)GetProTypeReader["IpAddress"]] = nVehInfo; } else { CarInfo_Hash.Add((string)GetProTypeReader["IpAddress"], nVehInfo); } } GetProTypeReader.Close(); GetProTypeReader = null; GetProTypeCommand.Dispose(); } catch (Exception ce) { GprsServer.WriteErrLog("GetAllProtocolIP", ce.Message.ToString() + ce.StackTrace.ToString()); if (myConn.State.ToString() == "Closed") { Conndb(); } return; } }
/// <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 Sil(String tabloAdi, String SutunAd, int id) { try { String cmdStr = String.Format("DELETE * FROM {0} WHERE {1}=@idm", tabloAdi, SutunAd); Kontrol(); cmd = new OleDbCommand(cmdStr, bglnti); cmd.Parameters.Add("@idm", OleDbType.Integer).Value = id; int i = cmd.ExecuteNonQuery(); MessageBox.Show(String.Format("{0} kadar satır silindi.", i), "Sonuç"); } catch (Exception e) { MessageBox.Show(e.Message); } finally { cmd.Parameters.Clear(); cmd.Dispose(); bglnti.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; }