public void NapolniFormaProizvod() { String komanda = "EXECUTE spPodatociProizvod"; OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija); OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn); OleCm.Parameters.Add(new OleDbParameter("@Code", this.vnesiTexBox.Text.Trim())); OleDbDataReader dt; try { OleCn.Open(); dt = OleCm.ExecuteReader(); if (dt.Read()) { groupLbl.Text = dt["Ime_PG"].ToString(); descriptionLbl.Text = dt["Description"].ToString(); Sifra_Proizvod = dt["Sifra_Pro"].ToString(); } dt.Close(); OleCn.Close(); } catch (Exception ex) { OleCn.Close(); MessageBox.Show(ex.Message); } }
/// <summary> /// 读取Excel的方法 /// </summary> /// <param name="FilePath">xls文件路径</param> /// <param name="SelectSQL">查询SQL语句</param> /// <returns>DataSet</returns> public static DataSet SelectFromXLS(string FilePath, string SelectSQL) { string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + FilePath + "';Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\""; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr); System.Data.OleDb.OleDbDataAdapter da = null; DataSet ds = new DataSet(); try { conn.Open(); da = new System.Data.OleDb.OleDbDataAdapter(SelectSQL, conn); da.Fill(ds, "SelectResult"); } catch (Exception e) { conn.Close(); Logger.LogHelper.LogServiceWebError("iTrackStar.MYHM.Utility.ExcelHandler.SelectFromXLS()" + e.ToString()); //throw e; } finally { conn.Close(); } return(ds); }
public nouveau_etudiant(string l) { login = l; InitializeComponent(); string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "select module_ID,nom_module from module1 where login_enseignant='"+login+"'"; try { con.Open(); OleDbDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { id = (int)dr.GetValue(0); nom = (string)dr.GetValue(1); hash.Add(nom, id); checkedListBox1.Items.Add(nom); } dr.Close(); con.Close(); } catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { Label6.Visible = false; string id = ""; string isim = ""; string durum = ""; 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 + "' and parola='" + TextBox2.Text + "'", cnn); OleDbDataReader rdr = cmd.ExecuteReader(); if (rdr.Read() == false) { Label6.Visible = true; Label6.Text = "Üzgünüz ama hatalı kullanıcı adı ya da parola girdiniz."; } else { id = rdr[0].ToString(); isim = rdr[3].ToString(); durum = rdr[5].ToString(); cnn.Close(); Session.Add("oturumid", id); Session.Add("oturumadi", isim); Session.Add("oturumdurum", durum); Response.Redirect("uyepaneli.aspx"); } cnn.Close(); }
//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> /// Gathers existing customer information from the database to display in the GUI /// </summary> public List <String> getCustomerInfo(String email) { List <String> customerInformation = new List <String>(); try { connection.Open(); command.CommandText = "SELECT CustomerID FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); command.CommandText = "SELECT FirstName FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); command.CommandText = "SELECT LastName FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); command.CommandText = "SELECT CustomerAddress FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); command.CommandText = "SELECT CustomerNumber FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); command.CommandText = "SELECT CreditCardNumber FROM Customer WHERE CustomerEmail ='" + email + "'"; customerInformation.Add(command.ExecuteScalar().ToString()); } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } } return(customerInformation); }
protected void LinkButton5_Click(object sender, EventArgs e) { OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath(@"App_Data\db.mdb")+";Persist Security Info=True"); OleDbCommand cmd = new OleDbCommand("insert into siparis (UYEID,ADRES,TEL,DURUMU) values (@UYEID,@ADRES,@TEL,@DURUMU)",baglanti); cmd.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString()); cmd.Parameters.AddWithValue("@ADRES",TextBox1.Text); cmd.Parameters.AddWithValue("@TEL",TextBox2.Text); cmd.Parameters.AddWithValue("@DURUMU","Beklemede"); baglanti.Open(); cmd.ExecuteNonQuery(); baglanti.Close(); OleDbDataAdapter da = new OleDbDataAdapter("select ID from siparis where UYEID=@UYEID order by ID DESC",baglanti); da.SelectCommand.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString()); DataTable dt = new DataTable(); da.Fill(dt); DataTable sdt=((DataTable)(Session["SEPET"])); for (int i = 0; i < sdt.Rows.Count; i++) { OleDbCommand scmd = new OleDbCommand("insert into sepet (SIPID,URUNID,URUN,FIYAT,ADET,TOPLAM) VALUES (@SIPID,@URUNID,@URUN,@FIYAT,@ADET,@TOPLAM)",baglanti); scmd.Parameters.Clear(); scmd.Parameters.AddWithValue("@SIPID",dt.Rows[0][0].ToString()); scmd.Parameters.AddWithValue("@URUNID",sdt.Rows[i]["ID"].ToString()); scmd.Parameters.AddWithValue("@URUN",sdt.Rows[i]["URUN"].ToString()); scmd.Parameters.AddWithValue("@FIYAT",sdt.Rows[i]["FIYAT"].ToString()); scmd.Parameters.AddWithValue("@ADET",sdt.Rows[i]["ADET"].ToString()); scmd.Parameters.AddWithValue("@TOPLAM", sdt.Rows[i]["TOPLAM"].ToString()); baglanti.Open(); scmd.ExecuteNonQuery(); baglanti.Close(); } Label2.Text = "Sipariş Takip Numarasınız : "+dt.Rows[0][0].ToString(); }
public List <TTopic> getArchivedTopics(string course_id) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Topics WHERE course_id = '" + course_id + "' AND finishDateTime IS NOT NULL"; dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); TTopic tTopicAux; List <TTopic> startedTopics = new List <TTopic>(); while (dbDataReader.Read()) { tTopicAux = new TTopic(getCourse(dbDataReader["course_id"].ToString()), dbDataReader["title"].ToString()); tTopicAux.id = Convert.ToInt32(dbDataReader["id"]); tTopicAux.starterTeacher = getUser(dbDataReader["starterTeacher"].ToString()); tTopicAux.startDateTime = Convert.ToDateTime(dbDataReader["startDateTime"]); tTopicAux.finishDateTime = Convert.ToDateTime(dbDataReader["finishDateTime"]); startedTopics.Add(tTopicAux); } dbDataReader.Close(); dbConnection.Close(); return(startedTopics); }
public void finishTopic(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; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //---Begin Transaction--- tTopic.finishDateTime = DateTime.Now; //UPDATE Topic dbCommand.CommandText = "UPDATE Topics SET finishDateTime = '" + tTopic.finishDateTime.ToString() + "' WHERE id = " + tTopic.id.ToString(); dbCommand.ExecuteNonQuery();; dbTransaction.Commit(); //----End Transaction---- } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
}//end DBSetup() /******************************SELECT METHOD***********************************************/ public void SelectDB(string id) { DBSetup(); cmd = "Select * from PositionInfo where EmpId = '" + id + "'"; OleDbDataAdapter.SelectCommand.CommandText = cmd; OleDbDataAdapter.SelectCommand.Connection = OleDbConnection; Console.WriteLine(cmd); try { Console.WriteLine("Before Open"); OleDbConnection.Open(); Console.WriteLine("After Open"); System.Data.OleDb.OleDbDataReader dr; dr = OleDbDataAdapter.SelectCommand.ExecuteReader(); Console.WriteLine("After Execute"); dr.Read(); EmpId = id; JobTitle = dr.GetValue(1) + ""; JobDescription = dr.GetValue(2) + ""; Salary = Double.Parse(dr.GetValue(3) + ""); } catch (Exception ex) { Console.WriteLine("hello" + ex); } finally { OleDbConnection.Close(); } }//end SelectDB()
protected void Button2_Click(object sender, EventArgs e) { string s_add, r_add, message, heading,muid; s_add = Session["us_name"].ToString(); r_add = TextBox1.Text; heading = TextBox3.Text; message = TextBox2.Text; rec_mail te = new rec_mail(); conn = new OleDbConnection("Provider=MSDAORA;Data Source=orcl;Persist Security Info=True;Password=db_mail;User ID=db_mail"); conn.Open(); cmd = new OleDbCommand("insert into messages values(m_id.nextval,'" + heading + "','" + message + "'," + file_name + "',sysdate,sysdate,'Draft')", conn); dr = cmd.ExecuteReader(); cmd = new OleDbCommand("insert into mail_exchange values('" + s_add + "',m_id.currval,sysdate,sysdate,'" + r_add + "')", conn); dr = cmd.ExecuteReader(); cmd = new OleDbCommand("insert into draft values(m_id.currval)", conn); dr = cmd.ExecuteReader(); dr.Close(); cmd = new OleDbCommand("select m_id.currval from dual", conn); dr = cmd.ExecuteReader(); dr.Read(); muid = dr[0].ToString(); dr.Close(); conn.Close(); Label1.Text = "Message Saved. Your message id is " + muid; Label1.Visible = true; dr.Close(); conn.Close(); }
protected void Button1_Click(object sender, EventArgs e) { string b = RID;// Request["QUERY_STRING"]; string a = Session["mzj"].ToString().Trim(); if (a == "没中奖") { string url = Request.Url.ToString(); Response.Redirect(url); } else { string PathDataBase = System.IO.Path.Combine(Server.MapPath("~"), "USER_DIR\\SYSUSER\\LotteryTicket\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" + String source = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase; OleDbConnection con = new OleDbConnection(source); //2、打开连接 C#操作Access之按列读取mdb con.Open(); DbCommand com = new OleDbCommand(); com.Connection = con; com.CommandText = "select jp from wx_ggk_jp where name='" + b + "'"; DbDataReader reader = com.ExecuteReader(); if (reader.Read()) { string jp = reader["jp"].ToString(); bd.Style["Display"] = "None";//转盘层 csyw.Style["Display"] = "None";//次数用完次数 zjl.Style["Display"] = "Block";//中奖页面 zjjs.Style["Display"] = "None";//中奖结束页面 Label3.Text = "恭喜你以中奖" + jp + "请填写相关信息"; con.Close(); } else { OleDbCommand comd = new OleDbCommand(); //连接字符串 comd.CommandText = "insert into wx_ggk_jp (name,jp,sjh) values ('" + b + "','" + a + "','手机号')"; comd.Connection = con; //执行命令,将结果返回 int sm = comd.ExecuteNonQuery(); con.Close(); //释放资源 con.Dispose(); bd.Style["Display"] = "None";//转盘层 csyw.Style["Display"] = "None";//次数用完次数 zjl.Style["Display"] = "Block";//中奖页面 zjjs.Style["Display"] = "None";//中奖结束页面 Label3.Text = "恭喜你以中奖" + a + "请填写相关信息"; } } }
/// <summary> /// ���뵽dataset /// </summary> /// <param name="Path"></param> /// <param name="sheetname"></param> /// <returns></returns> public DataSet Getexcelds(string Path, String sheetname) { string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Path + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");//��һ����Ϊ�� OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); try { string strExcel = "select * from [" + sheetname + "]"; if (sheetname.IndexOf('$') < 0)//if it have not $ { strExcel = "select * from[" + sheetname + "$]"; } OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn); DataSet ds = new DataSet(); da.Fill(ds, "��˾Ա��"); conn.Close(); return ds; } catch (Exception error) { return null; } finally { conn.Close(); } }
public bool HaveDB() { string str_Sql_DB = "select * from sysdatabases where name='CityEduManage'"; //myAdapter = new SqlDataAdapter(str_Sql,myConnection) myAdapter = new System.Data.OleDb.OleDbDataAdapter(str_Sql_DB, myConnection); myAdapter.TableMappings.Add("Table", "TableIn"); ds = new DataSet(); try { myAdapter.Fill(ds); } catch (SqlException e) { string errorMessage = e.Message; } try { int count = ds.Tables[0].Rows.Count; ds.Clear(); if (count == 1) { return(true); } } catch { ds.Clear(); myConnection.Close(); return(false); } return(false); }
/// <summary> /// 取出sheet /// </summary> /// <param name="filePath"></param> /// <returns></returns> public string getFirstSheetName(string filePath) { string ret = ""; string strConn = sConnstring; //"Extended Properties=Excel 8.0;"; strConn = string.Format(strConn, filePath); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { ////取得工作表數量,法一 //foreach (DataRow dr in dt.Rows) //{ // Console.WriteLine((String)dr["TABLE_NAME"]); //} //取得工作表數量,法二 int TableCount = dt.Rows.Count; for (int i = 0; i < TableCount; i++) { ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); conn.Close(); return(ret); } } conn.Close(); return(ret); }
public Object[,] SorgulaDizi(String cmdSorgu) { Object[,] d = null; try{ DataSet dS = SorgulaDataSet(cmdSorgu); DataTable dT = dS.Tables[0]; d = new Object[dT.Rows.Count, dT.Columns.Count]; for (int i = 0; i < dT.Rows.Count; i++) { for (int j = 0; j < dT.Columns.Count; j++) { d[i, j] = dT.Rows[i][j]; } } } catch (OleDbException ole) { MessageBox.Show(ole.Message); } catch (Exception e) { MessageBox.Show(e.Message); } finally { bglnti.Close(); } return(d); }
protected void Page_Load(object sender, EventArgs e) { if (Session["oturumid"] == null) Response.Redirect("giris.aspx"); else if (Session["oturumdurum"].ToString() == "0") Response.Redirect("uyepaneli.aspx"); else if (Request.QueryString["mid"] == null|| Request.QueryString["mid"] == "") Response.Redirect("adminpaneli.aspx"); else { string mid = Request.QueryString["mid"]; 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 mesajlar where mesajid=" + mid, cnn); OleDbDataReader rdr = cmd.ExecuteReader(); if(rdr.Read()==false) { cnn.Close(); Response.Redirect("adminpaneli.aspx"); } else { Label2.Text = rdr[1].ToString(); Label4.Text = rdr[2].ToString(); Label6.Text = rdr[3].ToString(); Label7.Text = rdr[4].ToString(); } if(cnn.State==System.Data.ConnectionState.Open) cnn.Close(); } }
public DataSet Excel2003ToDataSet(string Path) { OleDbConnection conn = null; try { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;"; conn = new OleDbConnection(strConn); conn.Open(); System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string tableName = dtSheet.Rows[0][2].ToString(); string strExcel = ""; OleDbDataAdapter myCommand = null; DataSet ds = null; strExcel = "select * from [" + tableName + "]"; myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, tableName); conn.Close(); conn = null; return ds; } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) conn.Close(); conn = null; throw; } }
public string monitor(string dev) { string val = "no"; ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;"); ExcelCon.Close(); ExcelCon.Open(); try { System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = ExcelCon; string date1 = DateTime.Parse("01/01/2015 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm"); string date2 = DateTime.Parse("01/01/2016 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm"); // string SQL = " select [" + dev + "] FROM [" + Title + "] WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)"; string SQL = " select [" + dev + "] FROM [" + Title + "] WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)"; cmd.CommandText = SQL; cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1; cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2; OleDbDataReader DR = cmd.ExecuteReader(); DR.Read(); val = DR.GetValue(0).ToString(); // val = cmd.ExecuteScalar().ToString(); } catch { } ExcelCon.Close(); return(val); }
public void setMessageDeletedByStudent(string message_id) { //--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; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { dbCommand.CommandText = "UPDATE Messages SET deletedByStudent = true WHERE id = " + message_id; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
/// <summary> /// User details /// </summary> /// <param name="user_id"></param> /// <returns></returns> public TUser getUser(string user_id) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Users WHERE id = '" + user_id + "'"; dbConnection.Close(); dbConnection.Open(); try { dbDataReader = dbCommand.ExecuteReader(); TUser tUser; dbDataReader.Read(); tUser = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"])); dbDataReader.Close(); dbConnection.Close(); return(tUser); } catch { dbConnection.Close(); return(null); } }
public void exitTopic(TUser tUser) { //--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; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //Begin Transaction dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE topic_id = " + tUser.topic.id.ToString(); dbCommand.ExecuteNonQuery(); tUser.topic = null; dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
private void button2_Click(object sender, EventArgs e) { string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "select * from module1"; try { con.Open(); OleDbDataReader dr = cmd.ExecuteReader(); dataGridView1.Rows.Clear(); while (dr.Read()) { dataGridView1.Rows.Add(dr.GetValue(1), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4),dr.GetValue(0)); } dr.Close(); con.Close(); } catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); } // dataGridView1.CellClick += new DataGridViewCellEventHandler(dataGridView1_CellClick); }
private void button2_Click(object sender, EventArgs e) { string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "update enseignant set mdp='" + txt_g_password.Text + "',nom=' "+txt_g_family.Text+"',prenom='"+txt_g_name.Text+"', grade='"+txt_g_grade.Text+"' where login='******'"; try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Modification Réussi"); txt_g_password.Text = ""; txt_g_family.Text = ""; txt_g_name.Text = ""; txt_g_grade.Text = ""; con.Close(); new acceuil(login).Show(); this.Hide(); } catch (Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } }
protected void onClick_login(object sender, EventArgs e) { connection = new OleDbConnection(path); connection.Open(); cmd = new OleDbCommand("SELECT COUNT(*) FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection); cmd.ExecuteNonQuery(); int compareUsername = Convert.ToInt32(cmd.ExecuteScalar().ToString()); connection.Close(); if (compareUsername == 1) { connection.Open(); cmd = new OleDbCommand("SELECT user_password FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection); cmd.ExecuteNonQuery(); string comparePassword = cmd.ExecuteScalar().ToString(); connection.Close(); if (comparePassword == inputPassword.Text) { Session["username"] = inputUsername.Text; Server.Transfer("main.aspx"); } else errorPassword.Visible = true; } else errorUsername.Visible = true; }
public List <TUser> getUsersInTopic(int topic_id, bool teacher) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Users INNER JOIN User_Topic ON Users.id = User_Topic.user_id WHERE (User_Topic.topic_id = " + topic_id.ToString() + ") AND (Users.is_teacher = " + teacher + ") AND (User_Topic.finishDateTime IS NULL)"; dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); TUser tUserAux; List <TUser> usersInTopic = new List <TUser>(); while (dbDataReader.Read()) { tUserAux = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"])); usersInTopic.Add(tUserAux); } dbDataReader.Close(); dbConnection.Close(); return(usersInTopic); }
private void MigrateExcelFile(string fileName,string excel_column_range,string tempTable) { string virtualColumns = "null as user_id"; OleDbCommand command = default(OleDbCommand); OleDbDataReader rdr = default(OleDbDataReader); OleDbConnection conn = default(OleDbConnection); try { conn = new OleDbConnection(string.Format(excelConnectionString, fileName)); conn.Open(); if (virtualColumns != "") virtualColumns += ","; command = conn.CreateCommand(); command.CommandText = string.Format("Select {0} * From [{1}]",virtualColumns, excel_column_range); command.CommandType = CommandType.Text; rdr = command.ExecuteReader(); if (rdr.HasRows) { SqlBulkCopy sqlBulk = new SqlBulkCopy(dbConnection.ConnectionString); sqlBulk.DestinationTableName = tempTable; sqlBulk.WriteToServer(rdr); rdr.Close(); } conn.Close(); } catch (Exception ex) { if (conn.State == ConnectionState.Open) conn.Close(); throw ex; }; }
public void activeTopic(int topic_id) { //--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; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //---Begin Transaction--- //UPDATE Topic dbCommand.CommandText = "UPDATE Topics SET finishDateTime = NULL WHERE id = " + topic_id; dbCommand.ExecuteNonQuery();; dbTransaction.Commit(); //----End Transaction---- } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public DataSet Select(string selstr) { DataSet ds = new DataSet(); OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source =" + DBPath; try { conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(selstr, conn); da.Fill(ds); } catch (Exception) { conn.Close(); } finally { conn.Close(); } return ds; }
private void button2_Click(object sender, EventArgs e) { date.CustomFormat = "dd/MM/yyyy"; string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "Insert into module1(nom_module,coef,anne,semestre,login_enseignant) values ('" + txt_nv_module.Text + "','" + Convert.ToInt32(txt_coef.Text) + "','" + date.Text + "'," + Convert.ToInt32(txt_semestre.Text) + ",'" + login + "')"; try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Ajout Module Réussi"); txt_nv_module.Text = ""; txt_coef.Text = ""; txt_semestre.Text = ""; con.Close(); new acceuil(login).Show(); this.Hide(); } catch (Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } }
private void button2_Click(object sender, EventArgs e) { string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "Insert into enseignant(login,mdp,nom,prenom,grade) values ('" + txt_insc_login.Text + "','" + txt_insc_password.Text + "','" + txt_insc_family.Text + "','" + txt_insc_name.Text + "','" + txt_insc_grade.Text + "')"; try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Ajout Réussi"); txt_insc_login.Text = ""; txt_insc_password.Text = ""; txt_insc_family.Text = ""; txt_insc_name.Text = ""; txt_insc_grade.Text = ""; con.Close(); new Form1().Show(); this.Hide(); }catch(Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } }
/// <summary> /// 执行查询 /// </summary> /// <param name="ServerFileName">xls文件路径</param> /// <param name="SelectSQL">查询SQL语句</param> /// <returns>DataSet</returns> public static System.Data.DataSet SelectFromXLS(string ServerFileName, string SelectSQL) { string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + ServerFileName + "';Extended Properties=Excel 8.0"; OleDbConnection conn = new OleDbConnection(connStr); OleDbDataAdapter da = null; System.Data.DataSet ds = new System.Data.DataSet(); try { conn.Open(); da = new OleDbDataAdapter(SelectSQL, conn); da.Fill(ds, "SelectResult"); } catch (Exception e) { conn.Close(); throw e; } finally { conn.Close(); } return ds; }
public void NapolniForma() { String komanda = "EXECUTE spPodatociProizvod"; OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija); OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn); OleCm.Parameters.Add(new OleDbParameter("@Code", this.codeTextBox.Text.Trim())); OleDbDataReader dt; try { OleCn.Open(); dt = OleCm.ExecuteReader(); if (dt.Read()) { groupComboBox.SelectedIndex = groupComboBox.Items.IndexOf(dt["Ime_PG"].ToString()); description1TextBox.Text = dt["Description"].ToString(); Sifra_Proizvod = Convert.ToInt32(dt["Sifra_Pro"].ToString()); } dt.Close(); OleCn.Close(); this.codeTextBox.Enabled = false; } catch (Exception ex) { OleCn.Close(); MessageBox.Show(ex.Message); } }
public List <TUser> getUsersCourse(String course_id, bool current) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Users INNER JOIN User_Course_Group ON Users.id = User_Course_Group.user_id WHERE (User_Course_Group.course_id = '" + course_id + "') AND (User_Course_Group.is_current = " + current + ")"; dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); TUser tUserAux; List <TUser> usersCourse = new List <TUser>(); while (dbDataReader.Read()) { tUserAux = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"])); usersCourse.Add(tUserAux); } dbDataReader.Close(); dbConnection.Close(); return(usersCourse); }
public List <TCourse> getCourses(string user_id, bool current) { //--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; //------------------------------ dbCommand.CommandText = "SELECT id, course_name, group_id, user_id, is_current, responsable_teacher FROM Courses INNER JOIN User_Course_Group ON (Courses.id = User_Course_Group.course_id) WHERE (user_id = '" + user_id + "') AND (is_current = " + current + ")"; dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); List <TCourse> tCourseList = new List <TCourse>(); TCourse tCourseAux; while (dbDataReader.Read()) { TUser responsableTeacher = null; if (dbDataReader["responsable_teacher"] != null) { responsableTeacher = getUser(dbDataReader["responsable_teacher"].ToString()); } else { responsableTeacher = null; } tCourseAux = new TCourse(dbDataReader["id"].ToString(), dbDataReader["course_name"].ToString(), dbDataReader["group_id"].ToString(), responsableTeacher); tCourseList.Add(tCourseAux); } dbDataReader.Close(); dbConnection.Close(); return(tCourseList); }
/// <summary> /// 取出sheet /// </summary> /// <param name="filePath"></param> /// <returns></returns> public string getFirstSheetName(string filePath) { string ret = ""; string strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " + "Dbq= " + filePath + ";Extensions=csv,txt "; strConn = string.Format(strConn, filePath); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { ////取得工作表數量,法一 //foreach (DataRow dr in dt.Rows) //{ // Console.WriteLine((String)dr["TABLE_NAME"]); //} //取得工作表數量,法二 int TableCount = dt.Rows.Count; for (int i = 0; i < TableCount; i++) { ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$'); conn.Close(); return(ret); } } conn.Close(); return(ret); }
public String getUserPassword(string user_id) { //--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; //------------------------------ dbCommand.CommandText = "SELECT user_password FROM Users WHERE id = '" + user_id + "'"; dbConnection.Close(); dbConnection.Open(); try { dbDataReader = dbCommand.ExecuteReader(); dbDataReader.Read(); string userPassword = dbDataReader["user_password"].ToString(); dbDataReader.Close(); dbConnection.Close(); return(userPassword); } catch { dbConnection.Close(); return(null); } }
public TCourse getCourse(string course_id) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Courses INNER JOIN User_Course_Group ON Courses.id = User_Course_Group.course_id WHERE (id = '" + course_id + "')"; dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); TCourse tCourseAux; dbDataReader.Read(); TUser responsableTeacher = null; if (dbDataReader["responsable_teacher"] != null) { responsableTeacher = getUser(dbDataReader["responsable_teacher"].ToString()); } else { responsableTeacher = null; } tCourseAux = new TCourse(dbDataReader["id"].ToString(), dbDataReader["course_name"].ToString(), dbDataReader["group_id"].ToString(), responsableTeacher); dbDataReader.Close(); dbConnection.Close(); return(tCourseAux); }
protected void FormView1_ItemCommand(object sender, FormViewCommandEventArgs e) { if (e.CommandName == "Add to Cart") { FormViewRow row = this.FormView1.Row; string strPartid = Request.QueryString["prod"]; TextBox tboxQty = (TextBox)row.FindControl("txtQty"); Int32 intQty = Convert.ToInt32(tboxQty.Text); string sqlString; OleDbConnection myConn = new OleDbConnection(); myConn.ConnectionString = this.SqlDataSource1.ConnectionString; if (myConn.State == ConnectionState.Closed) myConn.Open(); sqlString = "insert into Cart_LineItems values (" + Session["cartid"] + ", '" + strPartid + "', '" + intQty.ToString() + "');"; OleDbCommand myCommand = new OleDbCommand(sqlString, myConn); try { myCommand.ExecuteNonQuery(); myConn.Close(); this.lblAdMessage.Text = "Added to Cart."; } catch (Exception ex) { this.lblErMessage.Text = "Cannot add record" + ex.Message; } myConn.Close(); } }
public void check_privillage() { string Select_SQL, Select_SQL2, Select_SQL3, Update_SQL1; OleDbConnection SqlConnection1 = new OleDbConnection(); SqlConnection1.ConnectionString = AccessDataSource1.ConnectionString; Select_SQL = "select module_name,page_name,user_name from module_user_mng,user_mng,module_mng where module_mng.module_id = module_user_mng.module_id and user_mng.user_id = module_user_mng.user_id and page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "' and user_name='" + Session["xsctintranet"] + "'"; SqlConnection1.Open(); OleDbCommand SqlCommand = SqlConnection1.CreateCommand(); SqlCommand.CommandText = Select_SQL; OleDbDataReader SqlDataReader = SqlCommand.ExecuteReader(); if (SqlDataReader.HasRows) { while (SqlDataReader.Read()) { OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand(); Update_SQL1 = "insert into visit_mng (visit_module_name,visit_user_name,visit_date,visit_ip,visit_module_page) VALUES ('" + SqlDataReader.GetString(0) + "','" + SqlDataReader.GetString(2) + "','" + DateTime.Now.ToString() + "','" + Page.Request.UserHostAddress + "','" + SqlDataReader.GetString(1) + "')"; SqlCommand2.CommandText = Update_SQL1; SqlCommand2.ExecuteNonQuery(); } } else { OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand(); Select_SQL2 = "select * from module_mng where page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "'"; SqlCommand2.CommandText = Select_SQL2; OleDbDataReader SqlDataReader2 = SqlCommand2.ExecuteReader(); if (SqlDataReader2.HasRows) { Response.Write("您没有访问该页面的权限!"); SqlDataReader2.Close(); SqlDataReader.Close(); SqlConnection1.Close(); Response.End(); } else { OleDbCommand SqlCommand3 = SqlConnection1.CreateCommand(); Select_SQL3 = "select * from user_mng where user_name='" + Session["xsctintranet"] + "'"; SqlCommand3.CommandText = Select_SQL3; OleDbDataReader SqlDataReader3 = SqlCommand3.ExecuteReader(); if (SqlDataReader3.HasRows) { } else { Response.Write("您没有访问该页面的权限!"); SqlDataReader3.Close(); SqlDataReader2.Close(); SqlDataReader.Close(); SqlConnection1.Close(); Response.End(); } SqlDataReader3.Close(); } SqlDataReader2.Close(); } SqlDataReader.Close(); SqlConnection1.Close(); }
private static int ExcelMaxRow = 999999999; //Convert.ToInt32(ConfigurationSettings.AppSettings["ExcelMaxRow"]); #endregion Fields #region Methods /// <summary> /// ����Excel ��ȡExcel���� ������DataSet���ݼ��� /// </summary> /// <param name="filepath">Excel������·��</param> /// <param name="tableName">Excel������</param> /// <returns></returns> public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName,string sheetName) { string errMsg = string.Empty; //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; ////OleDbConnection ExcelConn = new OleDbConnection(strCon); //try //{ // string strCom = string.Format("SELECT * FROM [Sheet1$]"); // DataSet ds = new DataSet(); // ds = DBdb2.RunDataSet(strCom, out errMsg); // return ds; //} //catch //{ // return null; //} string strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; OleDbConnection ExcelConn = new OleDbConnection(strCon); try { string strCom = string.Format("SELECT * FROM ["+sheetName+"$]"); ExcelConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[" + tableName + "$]"); ExcelConn.Close(); return ds; } catch { ExcelConn.Close(); return null; } }
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(); } }
private void FrmMain_Load(object sender, EventArgs e) { this.SetStyle(ControlStyles.OptimizedDoubleBuffer | ControlStyles.AllPaintingInWmPaint | ControlStyles.UserPaint, true); string strPath = System.IO.Directory.GetCurrentDirectory() + BGimage; this.BackgroundImage = Image.FromFile(strPath); BtnStart.Image = Image.FromFile(System.IO.Directory.GetCurrentDirectory() + "\\images\\btn_start.png"); //从数据库里读出所有可抽奖人员 OleDbConnection odcConnection = new OleDbConnection(strConn); odcConnection.Open(); OleDbCommand odCommand = odcConnection.CreateCommand(); odCommand.CommandText = "select count(*) as result from seedlist"; OleDbDataReader odrCount = odCommand.ExecuteReader(); odrCount.Read(); int allcount = Int32.Parse(odrCount[0].ToString()); odrCount.Close(); if (allcount < 9) { odcConnection.Close(); MessageBox.Show("可供抽奖的人数不足,无法抽奖"); this.Close(); } else { //读取所有记录到数据 SeedsList.Items.Clear(); odCommand.CommandText = "select id,employee_dept,employee_name,employee_no from Seedlist where award_flag = '0' order by id asc"; OleDbDataReader odrReader = odCommand.ExecuteReader(); while (odrReader.Read()) { SeedsList.Items.Add(odrReader[0].ToString() + ";" + odrReader[1].ToString() + ";" + odrReader[2].ToString() + ";" + odrReader[3].ToString()); } odrReader.Close(); odcConnection.Close(); } }
public DataSet ExecuteReader(string q) { q = CleanQuery(q); Logger.CreateLog("ExecuteReaderQuery", q, null, EnumLogLevel.INFO); DataSet dataset = null; OleDbConnection oConn = new OleDbConnection(GetConnexionString()); oConn.Open(); try { OleDbCommand oCmd = new OleDbCommand(q, oConn); OleDbDataReader reader = oCmd.ExecuteReader(); dataset = this.ConvertDataReaderToDataSet(reader); } catch (Exception ex) { oConn.Close(); ex.HelpLink = q; throw ex; } oConn.Close(); return dataset; }
internal static DataTable exceldata(string filePath) { DataTable dtexcel = new DataTable(); bool hasHeaders = false; string HDR = hasHeaders ? "Yes" : "No"; string strConn; if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx") { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; } else { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\""; } System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection(strConn); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dtexcel = DtSet.Tables[0]; MyConnection.Close(); MyConnection.Close(); return(dtexcel); }
private void button1_Click(object sender, EventArgs e) { dataGridView1.Rows.Clear(); string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); OleDbCommand cmd1 = con.CreateCommand(); cmd.CommandText = "select cin from assiste where id_module=" + label1.Text + " group by cin"; try { con.Open(); OleDbDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { //dataGridView1.Rows.Add(dr.GetValue(0), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4), dr.GetValue(0)); cmd1.CommandText="select nom,prenom from etudiant where cin="+dr.GetValue(0); try { OleDbDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); dataGridView1.Rows.Add(dr.GetValue(0), dr1.GetValue(0), dr1.GetValue(1),0); } catch (Exception ex) { } } dr.Close(); con.Close(); } catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); } }
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(); }
public List <TTopic> getStartedTopics(string user_id) { //--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; //------------------------------ dbCommand.CommandText = "SELECT * FROM Topics INNER JOIN User_Course_Group ON User_Course_Group.course_id = Topics.course_id AND User_Course_Group.group_id = Topics.group_id WHERE (User_Course_Group.user_id = '" + user_id + "') AND (Topics.finishDateTime IS NULL)"; /*AND (User_Course_Group.is_current = True)*/ dbConnection.Close(); dbConnection.Open(); dbDataReader = dbCommand.ExecuteReader(); TTopic tTopicAux; List <TTopic> startedTopics = new List <TTopic>(); while (dbDataReader.Read()) { tTopicAux = new TTopic(getCourse(dbDataReader["Topics.course_id"].ToString()), dbDataReader["title"].ToString()); tTopicAux.id = Convert.ToInt32(dbDataReader["id"]); tTopicAux.starterTeacher = getUser(dbDataReader["starterTeacher"].ToString()); tTopicAux.startDateTime = Convert.ToDateTime(dbDataReader["startDateTime"]); startedTopics.Add(tTopicAux); } dbDataReader.Close(); dbConnection.Close(); return(startedTopics); }
//------------------------------------------------------------------------------------------------------------------// //---------------------------------- Write to Snapshot Database ----------------------------------------------------// //------------------------------------------------------------------------------------------------------------------// public short WriteToSnapshot(List <List <string> > buffer) { string[] tempArray = new string[buffer.Count]; short ret = 0; char[] delim = { ',' }; tempArray = buffer[buffer.Count - 1][buffer[buffer.Count - 1].Count - 1].Split(delim); List <string> tempSplit = new List <string>(); tempSplit = tempArray.ToList(); try { conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\OnsrudLog.accdb;Persist Security Info=False;"); conn.Open(); System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(@"INSERT into dump(drives_ok, Alarm1, Alarm2, Alarm3, Alarm4, Alarm5, Alarm6, Alarm7, Alarm8, Alarm9, Alarm10, XPos, YPos, ZPos, APos, CPos, XLoad, YLoad, ZLoad, ALoad, CLoad, ULoad, Program, Sub_Program, Block, Line, Tool, Selected_Table, Process) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", conn); cmd.Parameters.Add("drives_ok", tempSplit[0][1]); cmd.Parameters.Add("Alarm1", tempSplit[1][1]); cmd.Parameters.Add("Alarm2", tempSplit[2][1]); cmd.Parameters.Add("Alarm3", tempSplit[3][1]); cmd.Parameters.Add("Alarm4", tempSplit[4][1]); cmd.Parameters.Add("Alarm5", tempSplit[5][1]); cmd.Parameters.Add("Alarm6", tempSplit[6][1]); cmd.Parameters.Add("Alarm7", tempSplit[7][1]); cmd.Parameters.Add("Alarm8", tempSplit[8][1]); cmd.Parameters.Add("Alarm9", tempSplit[9][1]); cmd.Parameters.Add("Alarm10", tempSplit[10][1]); cmd.Parameters.Add("XPos", tempSplit[11][1]); cmd.Parameters.Add("YPos", tempSplit[12][1]); cmd.Parameters.Add("ZPos", tempSplit[13][1]); cmd.Parameters.Add("APos", tempSplit[14][1]); cmd.Parameters.Add("CPos", tempSplit[15][1]); cmd.Parameters.Add("XLoad", tempSplit[16][1]); cmd.Parameters.Add("YLoad", tempSplit[17][1]); cmd.Parameters.Add("ZLoad", tempSplit[18][1]); cmd.Parameters.Add("ALoad", tempSplit[19][1]); cmd.Parameters.Add("CLoad", tempSplit[20][1]); cmd.Parameters.Add("ULoad", tempSplit[21][1]); cmd.Parameters.Add("Program", tempSplit[22][1]); cmd.Parameters.Add("Sub_Program", tempSplit[23][1]); //cmd.Parameters.Add("Cycle_Time", tempSplit[24][1]); cmd.Parameters.Add("Block", tempSplit[25][1]); cmd.Parameters.Add("Line", tempSplit[26][1]); cmd.Parameters.Add("Tool", tempSplit[27][1]); cmd.Parameters.Add("Selected_Table", tempSplit[28][1]); cmd.Parameters.Add("Process", tempSplit[29][1]); cmd.ExecuteNonQuery(); conn.Close(); } catch (Exception e) { conn.Close(); MessageBox.Show(e.ToString()); } return(ret); }
private void Availability_Check_Load(object sender, EventArgs e) { //check for recovery Avar Back_End.SecondaryDatabase.notifyRecovery(); { //pulls flight destinations from database OleDbCommand command = new OleDbCommand(); connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=PrimaryDB.mdb"; command.Connection = connection; connection.Open(); string query = "SELECT Destination FROM Flights"; command.CommandText = query; OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { Locationinput.Items.Add(reader["Destination"].ToString()); } connection.Close(); //pulls car rental service names from database connection.Open(); string query2 = "SELECT CarRentalCompany FROM Cars"; command.CommandText = query2; OleDbDataReader reader2 = command.ExecuteReader(); while (reader2.Read()) { Carinput.Items.Add(reader2["CarRentalCompany"].ToString()); } connection.Close(); //pulls hotel names from database connection.Open(); string query3 = "SELECT HotelName FROM Hotel"; command.CommandText = query3; OleDbDataReader reader3 = command.ExecuteReader(); while (reader3.Read()) { Hotelinput.Items.Add(reader3["HotelName"].ToString()); } connection.Close(); } }
private void btBrow_Click ( object sender, EventArgs e ) { if (FileDialog.ShowDialog ( ) == DialogResult.OK) { FileDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|Tất cả(*.*)|*.*"; OleDbConnection excelConn = new OleDbConnection ( ); //OracleConnection excelConn = new OracleConnection(); try { string filelocation = FileDialog.FileName; // lay ten file string[] array = filelocation.Split ( '\\' ); string path = array[array.Length - 1].ToString ( ); path = path.Substring ( path.IndexOf ( '.' )+1 ); dtSaving.Clear ( ); dtColla.Clear ( ); OleDbCommand excelCommandSv = new OleDbCommand ( ); OleDbCommand excelCommandCl = new OleDbCommand ( ); //OracleCommand excelCommand = new OracleCommand(); OleDbDataAdapter excelDataAdapterSv = new OleDbDataAdapter ( ); OleDbDataAdapter excelDataAdapterCl = new OleDbDataAdapter ( ); //OracleDataAdapter excelDataAdapter = new OracleDataAdapter(); tbFilepath.Text = filelocation; string excelConnStr = ""; if (path.Equals ( "xlsx" )) excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=Excel 12.0;"; else excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;"; excelConn.ConnectionString = excelConnStr; excelConn.Open ( ); //---------Load Elecra excelCommandSv = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[0] + "]", excelConn ); excelCommandCl = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[1] + "]", excelConn ); //excelCommand = new OracleCommand("SELECT * FROM [BHD$]", excelConn); excelDataAdapterSv.SelectCommand = excelCommandSv; excelDataAdapterCl.SelectCommand = excelCommandCl; //dgv_error.DataSource = null; excelDataAdapterSv.Fill ( dtSaving ); excelDataAdapterCl.Fill ( dtColla ); dtgvSaving.DataSource = dtSaving; dtgvColla.DataSource = dtColla; //Xử lý Insert MessageBox.Show ( "Load File Successful" ); excelConn.Close ( ); } catch (Exception ex) { MessageBox.Show ( ex.Message ); return; } if (excelConn.State == ConnectionState.Open) excelConn.Close ( ); } }
private void buttonConnection_Click(object sender, EventArgs e) { /*conn.ConnectionString = "Provider=MySqlProv;" + "Data Source=ServerName;" + "User id=UserName;" + "Password=Secret;"*/ //Provider=sqloledb;Data Source=yourServername\\yourInstance;Initial Catalog=databaseName;Integrated Security=SSPI; // using (OleDbConnection sqlConn = new OleDbConnection("Provider=MySqlProv; Data Source= 127.0.0.1\test")) // {//new OleDbConnection("Provider=MySqlProv; Data Source= " + textBoxConnection.Text) //Provider=MySQLProv;Data Source=127.0.0.1;User Id=root;Password=; //Server=127.0.0.1;Uid=root;Pwd=12345;Database=test; //MySQL Provider //Provider=MySQL Provider; //OleMySql.MySqlSource.1 //Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; /*string strConnect = "Provider=MySQL Provider;" + "Server=127.0.0.1;" + // "Initial Catalog=test;" + "Database=test;" + "Uid=root;" + "Pwd=;";*/ // string strConnect = "Provider=MySQL Provider; Data Source=127.0.0.1; User ID =root; Password=; Initial Catalog=test;"; string strConnect = "Provider=MySQL Provider;Data Source=" + textBoxConnection.Text; var sqlConn = new OleDbConnection(strConnect); //OleDbConnection sqlConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + textBoxConnection.Text); //OleDbConnection sqlConn = new OleDbConnection("Provider=MySQL Provider;Data Source= " + textBoxConnection.Text); try { sqlConn.Open(); //FormSelectionAction formActionSelection = new FormSelectionAction("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "+textBoxConnection.Text); FormSelectionAction formActionSelection = new FormSelectionAction(strConnect); sqlConn.Close(); this.Hide(); formActionSelection.Show(); } catch (TypeInitializationException error) { //MessageBox.Show(error.Message, "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information); FormSelectionAction formActionSelection = new FormSelectionAction(strConnect); //FormSelectionAction formActionSelection = new FormSelectionAction("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + textBoxConnection.Text); sqlConn.Close(); this.Hide(); formActionSelection.Show(); } /* catch (Exception) { MessageBox.Show("Не удалось выполнить подключение, проверьте корректность введенных данных", "Проверка подключения", MessageBoxButtons.OK); }*/ // } }
private void btBrowse_Click ( object sender, EventArgs e ) { DialogResult result = MessageBox.Show ( "Tắt file excel trước khi import !! Xác nhận?", "Lưu ý", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning ); if (result == DialogResult.OK) { this.Cursor = Cursors.WaitCursor; if (FileDialog.ShowDialog ( ) == DialogResult.OK) { FileDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|Tất cả(*.*)|*.*"; OleDbConnection excelConn = new OleDbConnection ( ); try { this.Cursor = Cursors.WaitCursor; string filelocation = FileDialog.FileName; // lay ten file string[] array = filelocation.Split ( '\\' ); string path = array[array.Length - 1].ToString ( ); path = path.Substring ( path.Length - 4 ); OleDbCommand excelCommand = new OleDbCommand ( ); OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter ( ); tbFilePath.Text = filelocation; string excelConnStr = ""; if (path.Equals ( "xlsx" )) excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=Excel 12.0;"; else excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;"; excelConn.ConnectionString = excelConnStr; excelConn.Open ( ); //---------Load Elecra excelCommand = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[0] + "]", excelConn ); excelDataAdapter.SelectCommand = excelCommand; //dgv_error.DataSource = null; //Xử lý Insert excelDataAdapter.Fill ( dtDetail ); dtgvDetail.DataSource = dtDetail; lbTotal.Text = "Total : " + dtDetail.Rows.Count; if (dtDetail.Rows.Count > 0) { Enable ( true ); excelConn.Close ( ); if (excelConn.State == ConnectionState.Open) excelConn.Close ( ); } this.Cursor = Cursors.Default; } catch (Exception ex) { MessageBox.Show ( ex.Message ); this.Cursor = Cursors.Default; return; } } } }
protected void Page_Load(object sender, EventArgs e) { // Check whether the user is authorized string user = HttpContext.Current.User.Identity.Name.Split("\\".ToCharArray())[1]; if (Session["user"] != null) { user = Session["user"].ToString(); } Session["user"] = user; if (!PrintingServices.Validate.isStaff(user)) { Response.Write("Not Authorized."); Response.End(); } List<Dictionary<string, string>> schools = new List<Dictionary<string, string>>(); OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\miso\shares\Groups\DCP\PS Data\PS5_be.accdb"); try { conn.Open(); // Get all catalog items string query = "SELECT * FROM Letterhead_Schools"; OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Dictionary<string, string> school = new Dictionary<string, string>(); school.Add("name", reader.GetString(reader.GetOrdinal("SchoolName"))); if (!reader.IsDBNull(reader.GetOrdinal("Address"))) { school.Add("address", reader.GetString(reader.GetOrdinal("Address"))); } else { school.Add("address", ""); } if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) { school.Add("phone", reader.GetString(reader.GetOrdinal("Phone"))); } else { school.Add("phone", ""); } if (!reader.IsDBNull(reader.GetOrdinal("Fax"))) { school.Add("fax", reader.GetString(reader.GetOrdinal("Fax"))); } else { school.Add("fax", ""); } schools.Add(school); } reader.Close(); // Send data JavaScriptSerializer serializer = new JavaScriptSerializer(); string schoolsJson = serializer.Serialize(schools); Response.Write(schoolsJson); conn.Close(); } catch (Exception err) { conn.Close(); Response.Write(err.Message); } }
public void addGame(Game obj, int num) { try { database = new OleDbConnection(connectionString); database.Open(); string queryString = "INSERT INTO Game (Title, Description, Publisher, ReleaseDate, Rating, PurchasePrice, Copies) " + "VALUES ('" + obj.Title + "', '" + obj.Description + "', '" + obj.Publisher + "', '" + obj.ReleaseDate + "', '" + obj.Rating + "', '" + obj.Price + "', '" + obj.Copies + "')"; string queryString2 = "SELECT * FROM Game WHERE Title = '" + obj.Title + "'"; string queryString3; OleDbCommand cmd = new OleDbCommand(queryString, database); OleDbCommand cmd2 = new OleDbCommand(queryString2, database); count = cmd.ExecuteNonQuery(); reader = cmd2.ExecuteReader(); Game temp = new Game(); reader.Read(); temp.Id = Int32.Parse(reader["ID"].ToString()); temp.Title = reader["Title"].ToString(); temp.Description = reader["Description"].ToString(); temp.Publisher = reader["Publisher"].ToString(); temp.ReleaseDate = reader["ReleaseDate"].ToString(); temp.Rating = reader["Rating"].ToString(); temp.Price = Double.Parse(reader["PurchasePrice"].ToString()); temp.Copies = Int32.Parse(reader["Copies"].ToString()); queryString3 = "INSERT INTO GameCopy (CheckedOut, GameID) " + "VALUES (false, '" + temp.Id + "')"; OleDbCommand cmd3 = new OleDbCommand(queryString3, database); for (int i = 0; i < num; i++) count2 = cmd3.ExecuteNonQuery(); if (count >= 1) MessageBox.Show(obj.Title + " has been added!"); else MessageBox.Show("Error: Could not add game!"); reader.Close(); database.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); reader.Close(); database.Close(); } }
private void save_result() { saveFlag = 1; string files = Application.StartupPath + "\\DataBase.mdb"; string aConnectString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="; aConnectString += files; sqlCon.ConnectionString = aConnectString; sqlCon.Open(); sqlCmd.Connection = sqlCon; for (int ind = 0; ind < Grid1.RowCount; ind++) { if (Grid1.Rows[ind].Cells[0].Value == null) { continue; } int flagValue = changeFlag[int.Parse(Grid1.Rows[ind].Cells[0].Value.ToString())]; if (flagValue == 1) { string sqlstr = "select * from userData where 编号 = " + Grid1.Rows[ind].Cells[0].Value.ToString(); sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon); DataSet ds = new DataSet(); sqlAdp.Fill(ds, "userData"); if (ds.Tables[0].Rows.Count > 0) { sqlCmd.CommandText = UpdateStr(ind); sqlCmd.ExecuteNonQuery(); } else { sqlCmd.CommandText = InsertStr(ind); sqlCmd.ExecuteNonQuery(); } } } //记录需要删除的就删掉 foreach (var item in changeFlag) { if (item.Value == 2) { string sqlstr = "select * from userData where 编号 = " + item.Key; sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon); DataSet ds = new DataSet(); sqlAdp.Fill(ds, "userData"); if (ds.Tables[0].Rows.Count > 0) { string delstr = "delete from userData where 编号 = " + item.Key; sqlCmd.CommandText = delstr; sqlCmd.ExecuteNonQuery(); } } } sqlCon.Close(); }
/// <summary> /// 关闭数据库和清除DateSet对象 /// </summary> public void Close() { if (ds != null) // 清除DataSet对象 { ds.Clear(); } if (myConnection != null) { myConnection.Close(); // 关闭数据库 } }
/// <summary> /// DataTable转换为Excel,支持xls,不支持xlsx /// </summary> /// <param name="dtSource"></param> /// <param name="strPath"></param> /// <param name="strSheetName"></param> public static void ToExcel(this DataTable dtSource, string strPath, string strSheetName = "Sheet1") { //http://flyspirit99.blogspot.com/2007/07/export-more-than-255-characters-into.html- System.Data.OleDb.OleDbConnection OleDb_Conn = new System.Data.OleDb.OleDbConnection(); OleDb_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';" + "Data Source=\"" + strPath + "\""; try { OleDb_Conn.Open(); System.Data.OleDb.OleDbCommand OleDb_Comm = new System.Data.OleDb.OleDbCommand(); OleDb_Comm.Connection = OleDb_Conn; string strCmd; try { strCmd = "drop table [" + strSheetName + "]"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); } catch { } strCmd = "create Table [" + strSheetName + "]("; foreach (DataColumn dc in dtSource.Columns) { strCmd += "[" + dc.ColumnName + "] memo,"; } strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1); strCmd += ")"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); foreach (DataRow dr in dtSource.Rows) { if (dr.RowState != System.Data.DataRowState.Deleted) { strCmd = "insert into [" + strSheetName + "] values("; foreach (DataColumn dc in dtSource.Columns) { strCmd += "'" + dr[dc.ColumnName].ToString().Trim().Replace("'", "") + "',"; } strCmd = strCmd.Substring(0, strCmd.Length - 1); strCmd += ")"; OleDb_Comm.CommandText = strCmd; OleDb_Comm.ExecuteNonQuery(); } } OleDb_Conn.Close(); } catch (Exception ex) { throw ex; } finally { OleDb_Conn.Close(); } }
private void button4_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection con = null; OleDbCommand command = null; InputBoxValidation validation = delegate(string val) { if (val == "") { return("Value cannot be empty."); } if (!val.Equals("zuojian")) { return("password is not correct!"); } else { return(""); } }; string value = "*****@*****.**"; if (InputBox.Show("請輸入密碼後刪除資料", "密碼:", ref value, validation) == DialogResult.OK) { DialogResult dialogResult = MessageBox.Show("刪除資料之前,請先備份,以及關閉\"檢測條碼視窗\"\r\n確定刪除嗎?", "刪除資料", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { try { con = this.bAR_CODE_SCAN_HISTORYTableAdapter.Connection; if (con.State != ConnectionState.Open) { con.Open(); } command = new OleDbCommand("DELETE FROM BAR_CODE_SCAN_HISTORY", con); command.ExecuteNonQuery(); MessageBox.Show("Delete Successful"); if (con != null && con.State == ConnectionState.Open) { con.Close(); } } catch (Exception eeee) { if (con != null && con.State == ConnectionState.Open) { con.Close(); } } } else if (dialogResult == DialogResult.No) { } } }
//------------------------------------------------------------------------------------------------------------------// //---------------------------------- Write To The Events Database --------------------------------------------------// //------------------------------------------------------------------------------------------------------------------// public short WriteToEvent(List <List <string> > buffer) { string[] bufferArray = GetChanged(buffer); short ret = 0; char[] delim = { ',' }; string[][] bufferSplit = new string[bufferArray.Length][]; bool changedValues = false; for (int i = 0; i < bufferArray.Length; i++) { if (bufferArray[i] != null) { bufferSplit[i] = bufferArray[i].Split(delim); changedValues = true; } } if (changedValues == true) { try { conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\OnsrudLog.accdb;Persist Security Info=False;"); conn.Open(); for (int i = 0; i < bufferSplit.Length; i++) { if (bufferSplit[i] != null && bufferSplit[i][1] != "0") { string header = bufferSplit[i][0]; string content = bufferSplit[i][1]; System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(@"INSERT into events(header, content) values(?,?)", conn); cmd.Parameters.Add("header", header); cmd.Parameters.Add("content", content); cmd.ExecuteNonQuery(); } } conn.Close(); } catch (Exception e) { conn.Close(); MessageBox.Show("Could not open Database! Error: " + e.ToString()); ret = -1; return(ret); } return(ret); } return(ret); }