private void CreateAttemptedSheet(String buildingName) { try { //Fetches sheet with master list data using (OLEDB.OleDbConnection conn = returnConnection()) { try { conn.Open(); OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"Create Table " + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"Insert Into [" + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and PackageType NOT IN " + SizeListString() + " and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and (Location NOT LIKE '%versum%' and Location NOT LIKE '%MarkGruver%' and (Location LIKE '%" + buildingName + "%attempt%' OR (Location LIKE '%" + buildingName + "%pallet%' AND LocType LIKE '%ATTEMPT%') OR (BUStaging LIKE '%" + buildingName + "%' AND LocType Like '%ATTEMPT%'))) Order By BU;"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void ENT_Click() { //项目名称修改 if (TbFileName.Text == "") { MessageBox.Show("文件项目名不能为空!", "提示"); } else { //更新数据库 //Window_Project11.CurrentItemName; OleDbConnection conn = new OleDbConnection(odbcConnStr); string sql = "select * from ItemInfor where ItemName='" + TbFileName.Text.Trim() + "'"; OleDbCommand cmd = new OleDbCommand(sql, conn); conn.Open(); if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("该项目已经存在!", "提示"); } else { // sql = "update tbCourseInfo set filedCourseName='" + tbCourseName.Text.Trim() + "' where filedCourseID=" + courseID; sql = "update ItemInfor set ItemName='" + TbFileName.Text.Trim() + "' where ItemID=" + Window_Project11.CurrentItemID; MessageBox.Show(sql); cmd.CommandText = sql; cmd.ExecuteNonQuery(); MessageBox.Show("项目修改成功!", "提示"); } } }
public static void updatePosition(string swimlane, List<string> swimlaneBacklog, List<string> swimlaneBacklogPos) { OleDbConnection conn = new OleDbConnection(); OleDbCommand myCommand = new OleDbCommand(); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|LanbanDatabase.mdb;"; conn.Open(); myCommand.Connection = conn; int statusID = 1; if (swimlane == "5") statusID = 3; if (swimlane == "4") statusID = 2; for (int i = 0; i < swimlaneBacklog.Count; i++) { if (swimlane == "5") { myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i] + ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = '" + DateTime.Today + "' WHERE backlogID = " + swimlaneBacklog[i]; myCommand.ExecuteNonQuery(); myCommand.CommandText = "UPDATE Tasks SET TaskStatusID = " + statusID + ", TaskCompletedDate = '" + DateTime.Today + "' WHERE backlogID = " + swimlaneBacklog[i] + " AND TaskStatusID <> 3"; myCommand.ExecuteNonQuery(); } else { myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i] + ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = null" + " WHERE backlogID = " + swimlaneBacklog[i]; myCommand.ExecuteNonQuery(); } } conn.Close(); }
private void CreateLargeAttemptedSheet(String roomName) { try { using (OLEDB.OleDbConnection conn = returnConnection()) { try { conn.Open(); OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(); cmd.Connection = conn; cmd.CommandText = @"Create Table " + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);"; cmd.ExecuteNonQuery(); cmd.CommandText = @"Insert Into [" + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and PackageType IN " + SizeListString() + " and (Location NOT LIKE '%RVN%' and Location NOT LIKE '%RCV-Stag%' and Location NOT LIKE '%versum%' and (Location LIKE '%attempt%' OR LocType LIKE '%ATTEMPT%') and Location NOT LIKE '%B72%' and BUStaging NOT LIKE '%B72%') and BUStaging='" + roomName + "' Order By BU;"; cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); conn.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
protected void BtnSaveComment_Click(object sender, EventArgs e) { Button Btn = (Button)sender; DataListItem DLI = (DataListItem)Btn.Parent; DataList DL = (DataList)DLI.Parent; DataTable EvalTextItem = (DataTable)ViewState["EvalTextItem"]; string EvalItemTextId = string.Empty; EvalItemTextId = EvalTextItem.Rows[DLI.ItemIndex]["EvalItemTextId"].ToString(); TextBox txt = (TextBox)DLI.FindControl("TxtComment"); Label Lbl = (Label)DLI.FindControl("LblNotify"); Lbl.Text = string.Empty; int Idx = (int)DLI.ItemIndex; OleDbConnection Con = ET_Lib.E_trainingConnection; OleDbCommand Cmd = new OleDbCommand("UPDATE DowraEvalioutionTrainerText SET dateEval = #" + DateTime.Today.ToShortDateString() + "#, EvalText = '" + txt.Text + "' WHERE (DowraEvalioutionTrainerText.TRshehId = " + GetTRshehId() + ") AND (DowraEvalioutionTrainerText.EvalItemTextId = " + EvalItemTextId + ")", Con); try { Con.Open(); if (Cmd.ExecuteNonQuery() == 0) { Cmd.CommandText = "INSERT INTO DowraEvalioutionTrainerText (TRshehId, EvalItemTextId, dateEval, EvalText) VALUES (" + GetTRshehId() + ", " + EvalItemTextId + ", #" + DateTime.Today.ToShortDateString() + "#, '" + txt.Text + "')"; Cmd.ExecuteNonQuery(); } Lbl.Text = "تم الاضـــــــافه"; Lbl.ForeColor = System.Drawing.Color.Green; } catch (Exception ex) { Lbl.Text = ex.Message; Lbl.ForeColor = System.Drawing.Color.Red; } Con.Close(); }
protected void BtnAdd_Click(object sender, EventArgs e) { OleDbConnection Con = ET_Lib.E_trainingConnection; string ApoloReason = "NULL"; if (TxtReason.Text.Trim().Length != 0) { ApoloReason = TxtReason.Text; } OleDbCommand Cmd = new OleDbCommand("", Con); try { Con.Open(); Cmd.CommandText = "UPDATE DowraApology SET ApologyDate = #" + DateTime.Today.ToShortDateString().ToString() + "#, ApologyReson = '" + ApoloReason + "' WHERE (DowraApology.TRshehId = " + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ")"; if (Cmd.ExecuteNonQuery() == 0) { Cmd.CommandText = "INSERT INTO DowraApology (TRshehId, ApologyDate, ApologyReson) VALUES (" + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ", #" + DateTime.Today.ToShortDateString().ToString() + "#, '" + ApoloReason + "')"; Cmd.ExecuteNonQuery(); } Response.Redirect("~/TrainerCourses.aspx"); } catch (Exception ex) { LblState.Text = ex.Message; LblState.Visible = true; } Con.Close(); }
private void addbtn_Click(object sender, EventArgs e) { if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1) { MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (birthdayPicker.Value.AddYears(18) > DateTime.Today) { MessageBox.Show("Your employee must be atleast 18 years old!", "Invalid Birthday", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Connection connection = new Connection(); connection.openConnection(); OleDbCommand employeeCommand = new OleDbCommand(); employeeCommand.Connection = connection.connection; string name = nameTxt.Text; string lastName = lastNameTxt.Text; string address = addressTxt.Text; string phone = phoneTxt.Text; string birthday = birthdayPicker.Value.ToShortDateString(); string employment = DateTime.Now.ToString(); if (edit) { employeeCommand.CommandText = "UPDATE Employees SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + employee.id; employeeCommand.ExecuteNonQuery(); employee.name = name; employee.lastName = lastName; employee.address = address; employee.phone = phone; employee.birthday = birthdayPicker.Value.Date; } else { employeeCommand.CommandText = "INSERT INTO Employees (First_Name,Last_Name,Address,Phone,Birthday,Employmend,Fired) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#, #" + employment + "#, false)"; employeeCommand.ExecuteNonQuery(); employeeCommand.CommandText = "Select @@Identity"; string idEmployee = employeeCommand.ExecuteScalar().ToString(); employee = new CEmployee(idEmployee, name, lastName, address, phone, birthdayPicker.Value.Date, DateTime.Now, false); } connection.closeConnection(); DialogResult = System.Windows.Forms.DialogResult.OK; Close(); }
protected void BtnSaveResearch_Click(object sender, EventArgs e) { if (TxtResearchSubject.Text == string.Empty) { LblResearchError.Text = "من فضلك املاء البيانات"; LblResearchError.Visible = true; return; } try { string search_code = string.Empty; OleDbConnection Con = MySchool.LibraryConnection; OleDbCommand CMD = new OleDbCommand("Select TOP 1 MAX(search_code) From search1", Con); OleDbDataReader DR; string SavingFileName = string.Empty; if (FileUploadResearch.HasFile) { SavingFileName = DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Day.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + FileUploadResearch.FileName.ToString(); FileUploadResearch.SaveAs(HttpContext.Current.Server.MapPath("ResearchsFiles\\") + SavingFileName); SavingFileName = "~/ResearchsFiles/" + SavingFileName; } Con.Open(); DR = CMD.ExecuteReader(); if (DR.Read()) { search_code = DR.GetValue(0).ToString(); DR.Close(); search_code = (Convert.ToInt16(search_code) + 1).ToString(); CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (" + search_code + "," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')"; CMD.ExecuteNonQuery(); LblResearchError.Text = "تم اضافة البحث"; LblResearchError.ForeColor = System.Drawing.Color.Green; LblResearchError.Visible = true; } else { search_code = "0"; CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (1," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')"; CMD.ExecuteNonQuery(); LblResearchError.Text = "تم اضافة البحث"; LblResearchError.ForeColor = System.Drawing.Color.Green; LblResearchError.Visible = true; } CMD.CommandText = "Insert Into search1 (search_code, code_name) VALUES (" + search_code + ", " + StuID + ")"; CMD.ExecuteNonQuery(); Con.Close(); } catch (Exception ex) { LblResearchError.Text = ex.Message.ToString(); LblResearchError.ForeColor = System.Drawing.Color.Red; LblResearchError.Visible = true; } }
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(); }
private void btn_excel_db_check_Click(object sender, EventArgs e) { // //try // //{ // for (int i = 1; i < db_excel_data.Rows.Count; i++) // { // myConn.Open(); // SqlDataAdapter adp_sales = new SqlDataAdapter("insert into satislar(urun_id, musteri_id, adet) values('" + Convert.ToInt32(db_excel_data.Rows[i].Cells[1].Value.ToString()) + "','" + Convert.ToInt32(db_excel_data.Rows[i].Cells[2].Value.ToString()) + "','" + Convert.ToInt32(db_excel_data.Rows[i].Cells[4].Value.ToString()) + "'", myConn); // //SqlDataAdapter adp_sales = new SqlDataAdapter("exec proc_add_sale " + Convert.ToInt32(db_excel_data.Rows[i].Cells[1].Value.ToString()) + " , " + Convert.ToInt32(db_excel_data.Rows[i].Cells[2].Value.ToString()) + " , " + Convert.ToInt32(db_excel_yedek.Rows[i].Cells[3].Value.ToString()) + " , " + Convert.ToInt32(db_excel_yedek.Rows[i].Cells[4].Value.ToString()) + "", db); // DataTable tbl_adp_sales = new DataTable(); // adp_sales.Fill(tbl_adp_sales); // myConn.Close(); // } // MessageBox.Show("Veriler başarılı bir şekilde eklendi !"); // //} // //catch (Exception) // //{ // // MessageBox.Show("Veriler eklenirken hata oluştu !"); // //} if (textBox4.Text != string.Empty && textBox5.Text != string.Empty && textBox6.Text != string.Empty && db_excel_data.SelectedRows.Count != 0) { System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(); string sql = null; string sql2 = null; string sql3 = null; OleDbConnection Baglanti = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lb_dosya_yolu.Text + "'; Extended Properties='Excel 12.0 xml;HDR=YES;'"); Baglanti.Open(); myCommand.Connection = Baglanti; sql = "Update [Sheet1$] set urun_id=" + textBox4.Text + " where adet=" + satis_id; sql2 = "Update [Sheet1$] set musteri_id=" + textBox5.Text + " where adet=" + satis_id; sql3 = "Update [Sheet1$] set adet=" + textBox6.Text + " where adet=" + satis_id; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); myCommand.CommandText = sql2; myCommand.ExecuteNonQuery(); myCommand.CommandText = sql3; myCommand.ExecuteNonQuery(); Baglanti.Close(); MessageBox.Show("Güncellendi"); } else { MessageBox.Show("Boş değer girilemez!"); } }
private void updateBtn_Click(object sender, RoutedEventArgs e) { if (openedFilePath == string.Empty) { System.Windows.MessageBox.Show("No sheet opened!"); return; } File.Delete(openedFilePath); string connectionString = getConnectionStr(openedFilePath, openedFileExtention); using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString)) { con.Open(); StringBuilder strSQL = new StringBuilder(); System.Data.OleDb.OleDbCommand cmd; System.Data.DataTable dt = ((System.Data.DataView)grdData.ItemsSource).Table; // create sheet/table strSQL.Append("CREATE TABLE ").Append("[" + dt.TableName + "]"); strSQL.Append("("); for (int i = 0; i < dt.Columns.Count; i++) { strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,"); } strSQL = strSQL.Remove(strSQL.Length - 1, 1); strSQL.Append(")"); cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con); cmd.ExecuteNonQuery(); // insert data line by line for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i].RowState == System.Data.DataRowState.Deleted) { continue; } strSQL.Clear(); StringBuilder strvalue = new StringBuilder(); for (int j = 0; j < dt.Columns.Count; j++) { strvalue.Append("'" + dt.Rows[i][j].ToString() + "'"); if (j != dt.Columns.Count - 1) { strvalue.Append(","); } } cmd.CommandText = strSQL.Append(" insert into [" + dt.TableName + "] values (").Append(strvalue).Append(")").ToString(); cmd.ExecuteNonQuery(); } con.Close(); } System.Windows.Forms.MessageBox.Show("Update successfully!"); }
/// <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 Guncelle() { using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hesap.mdb")) { conn.Open(); OleDbCommand komut = new OleDbCommand(); komut.Connection = conn; komut.CommandText = "Select * from hesap"; // sorgu / komut cumlemi yazıyorum. komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz. OleDbDataReader dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz while (dr.Read()) // datareader ile okuyoruz. { string hadi = dr["hadi"].ToString(); string kadi = dr["kadi"].ToString(); string q; using (WebClient asd = new WebClient()) { asd.Encoding = Encoding.UTF8; q = asd.DownloadString("http://gdata.youtube.com/feeds/api/users/" + kadi + "/uploads?v=2&alt=jsonc&max-results=0"); } string[] adet1 = q.Split(new string[] { "totalItems\":" }, StringSplitOptions.None); string[] adet2 = adet1[1].Split(','); listView1.Items.Add(new ListViewItem(new string[] { hadi, "Adet: "+adet2[0] })); } dr.Close(); komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz. dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz while (dr.Read()) // datareader ile okuyoruz. { string kadi = dr["hadi"].ToString(); // veritabanimdaki "kadi" alanımdaki veriyi alip kadi değişkenine atıyorum(yukarıda string olusturmustum) string sifre = dr["hsifresi"].ToString(); // aynı durum söz konusu string devkey = dr["devkey"].ToString(); Random a = new Random(); string id = a.Next(100000, 999999).ToString(); YouTubeRequestSettings settings = new YouTubeRequestSettings(id, devkey, kadi,sifre); YouTubeRequest request = new YouTubeRequest(settings); string feedUrl = "https://gdata.youtube.com/feeds/api/users/default/uploads"; Feed<Video> videoFeed = request.Get<Video>(new Uri(feedUrl)); foreach (Video entry in videoFeed.Entries) { string vid_thumb ="http://img.youtube.com/vi/"+entry.VideoId+"/0.jpg"; int izlenme = entry.ViewCount; if(izlenme == -1) izlenme = 0; listView1.Items.Add(new ListViewItem(new string[] { kadi,entry.YouTubeEntry.Title.Text,izlenme.ToString() })); } } } }
static void Main(string[] args) { _dt = new DataTable(); _ds = new DataSet(); _repository = new Repository(); _helpers = new Helpers(); _oldFile = @"E:\Development\Projects\Transfer data exel\1.xlsx"; _newFile = @"E:\Development\Projects\Transfer data exel\empty.xlsx"; //Path to location file after instaling application //string path = Path.Combine(Environment.CurrentDirectory, @"Data\empty.xlsx"); _ds = _repository.GetDataSetFromExcelFile(_oldFile); _helpers.Ds = _ds; _thicknessOfMaterials = _helpers.GetThicknessOfMaterials(); System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(); OleDbConnection MyConnection = new OleDbConnection(_repository.GetConnectionString(_newFile)); MyConnection.Open(); myCommand.Connection = MyConnection; foreach (string thkness in _thicknessOfMaterials) { _dt = _helpers.GetAllPartWithThikness(thkness); myCommand.Parameters.Add(new OleDbParameter("@thikness", string.Format("Материал с толщиной {0}мм", thkness.ToString()))); _sql = "insert into [Распил$C:C" + _count + "] values (@thikness)"; myCommand.CommandText = _sql; myCommand.ExecuteNonQuery(); myCommand.Parameters.Clear(); _count++; foreach (DataRow dr in _dt.Rows) { _sql = "insert into [Распил$A:J" + _count + "] values (@sectionNumber, @numberOfDetail, @nameOfDitails, '', '', '', '', @x, @y, @number)"; myCommand.CommandText = _sql; myCommand.Parameters.Add(new OleDbParameter("@sectionNumber", dr["SectionNumber"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@numberOfDetail", _count.ToString())); myCommand.Parameters.Add(new OleDbParameter("@nameOfDitails", dr["NameOfDitails"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@x", dr["X"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@y", dr["Y"].ToString())); myCommand.Parameters.Add(new OleDbParameter("@number", dr["Number"].ToString())); myCommand.ExecuteNonQuery(); myCommand.Parameters.Clear(); _count++; } _count += 2; } MyConnection.Close(); }
//清除用户 private void Btn_Delete_User_Click(object sender, EventArgs e) { OleDbConnection oleDB = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dac.accdb"); oleDB.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = oleDB; cmd.CommandText = "delete from [主体信息表] where 主体名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "delete from [用户-角色指派信息表] where 用户名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'"; cmd.ExecuteNonQuery(); oleDB.Close(); MessageBox.Show(this, "删除用户成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information); Refresh_Users_Roles(0); }
private void btnReset_Click(object sender, EventArgs e) { if (connection.State.Equals(ConnectionState.Closed)) { connection = new OleDbConnection(); connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\UC.mdb;Persist Security Info=False"; connection.Open(); } OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "DELETE * FROM InputTable"; cmd.Connection = connection; int a = cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE * FROM OutputTable"; a = cmd.ExecuteNonQuery(); }
private void addbtn_Click(object sender, EventArgs e) { if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1) { MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Connection connection = new Connection(); connection.openConnection(); OleDbCommand customerCommand = new OleDbCommand(); customerCommand.Connection = connection.connection; string name = nameTxt.Text; string lastName = lastNameTxt.Text; string address = addressTxt.Text; string phone = phoneTxt.Text; string birthday = birthdayPicker.Value.ToShortDateString(); if (edit) { customerCommand.CommandText = "UPDATE Customers SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + customer.id; customerCommand.ExecuteNonQuery(); customer.name = name; customer.lastName = lastName; customer.address = address; customer.phone = phone; customer.birthday = birthdayPicker.Value.Date; } else { customerCommand.CommandText = "INSERT INTO Customers (First_Name,Last_Name,Address,Phone,Birthday) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#)"; customerCommand.ExecuteNonQuery(); customerCommand.CommandText = "Select @@Identity"; string idCustomer = customerCommand.ExecuteScalar().ToString(); customer = new CCustomer(idCustomer, name, lastName, address, phone, birthdayPicker.Value.Date); } connection.closeConnection(); DialogResult = System.Windows.Forms.DialogResult.OK; Close(); }
public bool set_on(string dev) { try { string sets = "[" + dev + "]=1";//,[Switch_Toshiba1(7/9:40/2)]=0,[Switch_Toshiba2(8/10:42/2)]=0,[Switch_Toshiba3(9/11:44/2)]=0,[Switch_Toshiba4(10/12:46/2)]=0,[Switch_Toshiba5(16/13:48/2)]=0,[Fan(22/9:39/2)]=0,[Humidity_BLed(26/15:33/2)]=0,[Humidity_GLed(25/15:35/2)]=0,[Humidity_RLed(24/15:37/2)]=0,[Niazy Lamp1(11/9:41/2)]=0,[Niazy Lamp2(12/10:43/2)]=0,[Niazy Lamp3(13/11:45/2)]=0,[Niazy Lamp4(14/12:47/2)]=0,[Niazy Lamp5(15/13:49/2)]=0,[LDR(20/15:14/2)]=0,[DHT11(21/15:15/2)]=0"; System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); string SQL = null; ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;"); ExcelCon.Open(); cmd.Connection = ExcelCon; SQL = "Update [" + Title + "] set " + sets + " WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)"; cmd.CommandText = SQL; 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"); cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1; cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2; cmd.CommandText = SQL; int x = cmd.ExecuteNonQuery(); ExcelCon.Close(); return(true); } catch { return(false); } }
protected void Button1_Click(object sender, EventArgs e) { String fName = TextBox1.Text.ToString(); String lName = TextBox2.Text.ToString(); String email = TextBox3.Text.ToString(); String country = TextBox4.Text.ToString(); string gender = rb.SelectedItem.Value.ToString(); OleDbConnection conn = new OleDbConnection(); //Use a string variable to hold the ConnectionString. conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\Users\\Intag\\Documents\\GitHub\\Form\\Forms\\App_Data\\Database3.accdb"; //new String query = @"INSERT INTO Users (FirstName,LastName,Email,Country,Gender) VALUES(@fName,@lname,@email,@country,@gender)"; System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@fName", fName); cmd.Parameters.AddWithValue("@lName", lName); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@country", country); cmd.Parameters.AddWithValue("@gender", gender); conn.Open(); cmd.ExecuteNonQuery(); Response.Write("<script>alert('Form Successfully Submitted');</script>"); }
public int insertDb(string queryString) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); // TODO: Modify the connection string and include any // additional required properties for your database. conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source = szglDB1.mdb"; try { System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString); command.Connection = conn; conn.Open(); command.ExecuteNonQuery(); this.Hide(); return(0); //insert(conn, command); //return 0; } catch (Exception ex) { MessageBox.Show("Failed to connect to data source" + ex.ToString()); return(1); } finally { conn.Close(); } }
/// <summary> /// Generic method for issuing simple Update commands to the current VWA4 database, /// based on provided connection. /// </summary> /// <param name="sql">SQL query string to execute.</param> /// <param name="conn">OleDbConnection to desired database.</param> /// <param name="trans"></param> /// <returns>true if successful.</returns> public static bool Update(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans) { // Connect up to the database int iSqlStatus; try { // Tee up the command System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn); if (trans != null) { cmd.Transaction = trans; } // // Issue the command // iSqlStatus = cmd.ExecuteNonQuery(); //Now check the status if (iSqlStatus < 0) { //DO your failed messaging here MessageBox.Show(null, "VWA4.NET UPDATE ERROR: Query failed to update with status: " + iSqlStatus, "Update Error"); } } catch (Exception ex) { MessageBox.Show(null, "VWA4.NET UPDATE Exception: Error " + ex.Message + "!", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(false); } return(iSqlStatus == 0); }
//刪除項目 private void deldata_btn_Click(object sender, EventArgs e) { // === 對 Access 資料庫下SQL語法 === //// Transact-SQL 陳述式 String strSQL = "DELETE FROM [WindowsTarget] WHERE id =" + int.Parse(ShowTargetID.Text); System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb"); //// 開啟資料庫連接。 oleConn.Open(); //// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection,初始化 OleDbCommand 類別的新執行個體。 System.Data.OleDb.OleDbCommand oleCmd = new System.Data.OleDb.OleDbCommand(strSQL, oleConn); oleCmd.ExecuteNonQuery(); //// 關閉資料庫連接。 oleConn.Close(); //重設ui 並且青空 list ShowTargetID.Text = "_"; ShowTargetName.Text = ""; ShowTargetIP.Text = ""; ShowTargetDomain.Text = ""; ShowTargetUser.Text = ""; ShowTargetPassword.Text = ""; ShowTargetDeviceID.Text = "_"; ShowTargetAlert.Text = ""; ShowTargetRes.Text = ""; ShowTargetWarning.Text = ""; TargetListView.Items.Clear(); //關閉更新即刪除 updata_btn.Enabled = false; deldata_btn.Enabled = false; }
protected void Button1_Click(object sender, EventArgs e) { if (TextBox1.Text != "" && TextBox2.Text != "" && TextBox3.Text != "" && TextBox4.Text != "" && TextBox5.Text != "" && TextBox6.Text != "") { string fname = TextBox1.Text; string lname = TextBox2.Text; string uname = TextBox5.Text; string pass = TextBox6.Text; string ph = TextBox3.Text; string addr = TextBox4.Text; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data source= C:\Users\OK\Documents\Ecafe.accdb"; conn.Open(); String sql = "insert into Users ([Firstname], [Lastname], [Username], [Password],[Type],[Address],[PhoneNo]) values ('" + fname + "','" + lname + "','" + uname + "','" + pass + "','Customer','" + addr + "','" + Convert.ToInt64(ph) + "')"; System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql, conn); var dr1 = cmd1.ExecuteNonQuery(); MessageBox.Show("Successfully Registered."); Server.Transfer("Login.aspx"); } else { MessageBox.Show("Fill in the form properly"); } }
public bool set_off(string dev) { try { string sets = "[" + dev + "]=0"; string y0 = DateTime.Parse("14/12/2014 18:" + DateTime.Now.Minute + "").ToString("dd/MM/yyyy HH:mm"); string y1 = DateTime.Parse("01/01/2015 " + DateTime.Now.ToLocalTime().Hour + ":" + DateTime.Now.ToLocalTime().Minute + "").ToString("dd/MM/yyyy HH:mm"); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); string SQL = null; ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;"); ExcelCon.Open(); cmd.Connection = ExcelCon; SQL = "Update [" + Title + "] set " + sets + " WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)"; cmd.CommandText = SQL; 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"); cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1; cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2; cmd.CommandText = SQL; int x = cmd.ExecuteNonQuery(); ExcelCon.Close(); return(true); } catch { return(false); } }
/// <summary> /// Generic method for issuing simple Insert commands to the current VWA4 database, /// based on provided connection. /// </summary> /// <param name="sql">SQL query string to execute.</param> /// <param name="conn">OleDbConnection to desired database.</param> /// <param name="trans"></param> /// <returns>PK/ID of inserted record.</returns> public static int Insert(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans) { // Connect up to the database int id = -1; try { // Tee up the command System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn); // // Issue the command // if (trans != null) { cmd.Transaction = trans; } int iSqlStatus = cmd.ExecuteNonQuery(); //Now check the status if (iSqlStatus <= 0) { //DO your failed messaging here MessageBox.Show(null, "VWA4.NET INSERT ERROR: Query Failed to insert with status: " + iSqlStatus, "Insert Error"); } cmd.CommandText = "SELECT @@Identity"; id = (int)cmd.ExecuteScalar(); } catch (Exception ex) { MessageBox.Show(null, "VWA4.NET INSERT Exception: Error " + ex.Message + "!", "Insert Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(id); }
public static bool ExecuteNonQuery(System.Data.OleDb.OleDbCommand sqlCmd) { bool bCloseConnection = false; try { sqlCmd.Connection = Connection; if (Transaction != null) { sqlCmd.Transaction = Transaction; } Cursor.Current = Cursors.WaitCursor; if (sqlCmd.Connection.State == ConnectionState.Closed) { sqlCmd.Connection.Open(); bCloseConnection = true; } sqlCmd.ExecuteNonQuery(); return(true); } catch (Exception ex) { Cursor.Current = Cursors.Default; MsgBoxX.Show(ex.Message); return(false); } finally { Cursor.Current = Cursors.Default; if (bCloseConnection) { sqlCmd.Connection.Close(); } } }
/// <summary> /// 执行SQL语句 返回影响的记录数 /// </summary> /// <param name="SQLString"></param> /// <returns></returns> public int ExecuteSql(string SQLString) { System.Data.OleDb.OleDbCommand Cmd = new System.Data.OleDb.OleDbCommand(); Cmd.Connection = _Con; string sql = ""; try { if (_sTrAccessPath == "") { sql = SQLString.Replace("#", "'");//如果是SQL数据库则需要将#替换为' } else { sql = SQLString; } Cmd.CommandText = sql; int i = Cmd.ExecuteNonQuery(); Cmd = null; return(i); } catch (OleDbException e) { CLDC_DataCore.Const.GlobalUnit.Logger.Fatal("向数据库插入数据失败:" + sql, e); return(0); } catch (Exception ex) { CLDC_DataCore.Const.GlobalUnit.Logger.Fatal("向数据库插入数据失败:", ex); return(0); } }
public override bool CreateTable() { if (m_pOleDbConnection != null) { try { base.CreateTable(); string strCommand = "Create Table " + TableName_TempTable + "(" + FieldName_IndexID + " int primary key," + FieldName_LineNodeID + " int)"; OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection); oleDbCommand.ExecuteNonQuery(); nIndex = 0; return true; } catch (Exception ex) { LogAPI.WriteErrorLog(ex); } } return false; }
public override string guardar(Entidades.PlatosCartaPlatos platosCarta) { string mensaje = ""; try { int cont = 0; OleDbConnection conexion = Entidades.Conexion.GetInstancia().crearConexion(); OleDbCommand cmdUpdate = new OleDbCommand("Update CartaPlatoPlatos Set Activo = @activo Where IdCartaPlato = @idCartaPlato and IdPlato = @idPlato", conexion); cmdUpdate.Parameters.AddWithValue("@activo", platosCarta.Activo); cmdUpdate.Parameters.AddWithValue("@idCartaPlato", platosCarta.IdCartaPlato); cmdUpdate.Parameters.AddWithValue("@idProducto", platosCarta.IdPlato); cont = cmdUpdate.ExecuteNonQuery(); mensaje = "Actualizado con exito."; if (cont == 0) { OleDbCommand cmd = new OleDbCommand("Insert Into CartaPlatoPlatos(IdCartaPlato,IdPlato,Activo) Values(@idCartaPlato,@idPlato,@activo)", conexion); cmd.Parameters.AddWithValue("@idCartaPlato", platosCarta.IdCartaPlato); cmd.Parameters.AddWithValue("@idPlato", platosCarta.IdPlato); cmd.Parameters.AddWithValue("@activo", platosCarta.Activo); cmd.ExecuteNonQuery(); mensaje = "Guardado con exito."; } } catch (Exception ex) { mensaje = "Ocurrio un problema al guardar " + ex.Message; } return mensaje; }
protected void addbutton_Click(object sender, EventArgs e) { host temphost = new host(hostipform.Text, hostnameform.Text, System.Int32.Parse(hostscanint.Text), grouptextbox.Text); string strAccessSelect = "SELECT Код FROM hosts"; int lastnum = 0; OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbaddress); OleDbCommand cmd = new OleDbCommand(strAccessSelect, connection); cmd.CommandText = strAccessSelect; cmd.Connection = connection; connection.Open(); OleDbDataReader readID = cmd.ExecuteReader(); // Выполняем команду while (readID.Read()) { lastnum = Convert.ToInt32(readID["Код"]); // Присваиваем таймеру значение максимального id } readID.Close(); lastnum++; string strAccessInsert = "INSERT INTO hosts (код,ip,name,mac,scanint,grp) values (" + lastnum + ",'" + temphost.Ip + "','" + temphost.Name + "','" + temphost.Mac + "','" + temphost.Scanint + "','" + temphost.Group + "')"; cmd.CommandText = strAccessInsert; cmd.ExecuteNonQuery(); 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"); }
private bool CreateIndex(System.Data.OleDb.OleDbCommand poCmd, string psIdxName, string psTableName, List <string> psColumns, out string psErrMsg) { string xsErrMsg = "", xsSQL = ""; xsSQL = "CREATE INDEX [" + psIdxName.Trim() + "] ON [" + psTableName.Trim() + "] ("; for (int xiColm = 0; xiColm < psColumns.Count; xiColm++) { if (xiColm > 0) { xsSQL += ", "; } xsSQL += "[" + psColumns[xiColm].Trim() + "]"; } xsSQL += ")"; try { poCmd.CommandText = xsSQL; poCmd.CommandType = System.Data.CommandType.Text; poCmd.ExecuteNonQuery(); } catch (Exception xoExc) { xsErrMsg = xoExc.Message + " (" + xsSQL + ")"; } psErrMsg = xsErrMsg; return(xsErrMsg.Length == 0); }
private void button1_Click(object sender, EventArgs e) { if (comboBox1.SelectedIndex != -1) { int billno = int.Parse(comboBox1.SelectedItem.ToString()); OleDbConnection cnon = new System.Data.OleDb.OleDbConnection(); cnon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb"; System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(); command.CommandText = "DELETE FROM Purchase_Bill where Bill_No=" + billno; cnon.Open(); command.Connection = cnon; command.ExecuteNonQuery(); OleDbConnection cnon1 = new System.Data.OleDb.OleDbConnection(); cnon1.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb"; System.Data.OleDb.OleDbCommand command1 = new System.Data.OleDb.OleDbCommand(); command1.CommandText = "DELETE FROM Purchase_Inventory where Bill_No=" + billno; cnon1.Open(); command1.Connection = cnon1; command1.ExecuteNonQuery(); MessageBox.Show("Purchase Entry Deleted"); cnon.Close(); this.Dispose(); } }
// Add new breed to database public static Boolean addBreed(string[,] breedData) { OleDbConnection myConnection = GetConnection(); string myQuery = "INSERT INTO breeds (name, spieces_id, food_cost, housing_cost) VALUES (?, ?, ?, ?)"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); myCommand.Parameters.Add("@name", OleDbType.VarWChar, 50).Value = breedData[0, 1]; myCommand.Parameters.Add("@spieces_id", OleDbType.Integer, 5).Value = breedData[1, 1]; myCommand.Parameters.Add("@food_cost", OleDbType.Double, 5).Value = breedData[2, 1]; myCommand.Parameters.Add("@housing_cost", OleDbType.Double, 5).Value = breedData[3, 1]; try { myConnection.Open(); myCommand.ExecuteNonQuery(); return true; } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); return false; } finally { myConnection.Close(); } }
/// <summary>執行 SQL 指令</summary> /// <param name="strSQL">欲執行的 SQL 指令</param> /// <param name="OleDbConn">OleDbConnection 連線物件(不指定表使用預設連線)</param> /// <returns>Boolean</returns> /// <remarks></remarks> public static bool execOleDbCommand(string strSQL, System.Data.OleDb.OleDbConnection OleDbConn) { try { if (OleDbConn == null) { OleDbConn = createOleDbConnection(); } System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(strSQL, OleDbConn); if (!(OleDbConn.State == ConnectionState.Open)) { OleDbConn.Open(); } cmd.ExecuteNonQuery(); } catch (Exception Ex) { //Message.alertMessage("C0002", null, Ex.Message.ToString(), null); return(false); } finally { if (!(OleDbConn.State == ConnectionState.Closed)) { OleDbConn.Close(); } } return(true); }
private void LoadItems() { string query = ""; OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../App_Data/items.mdb")); myConn.Open(); for (int index = 0; index <= 24; index ++) { query = "update Items set Items = rnd() * 10 where Id = " + index.ToString(); OleDbCommand myComm1 = new OleDbCommand(query, myConn); myComm1.ExecuteNonQuery(); query = "update Items set Price = rnd() * 100 where Id = " + index.ToString(); OleDbCommand myComm2 = new OleDbCommand(query, myConn); myComm2.ExecuteNonQuery(); } query = "SELECT Id, Name, Items, Price FROM Items"; OleDbCommand myComm = new OleDbCommand(query, myConn); OleDbDataReader myReader = myComm.ExecuteReader(); gridItems.DataSource = myReader; gridItems.DataBind(); myReader.Close(); }
public static void WriteInExcel(string testerName, string placeName, string placeArea, float slope, string date, string testResult) { OleDbConnection excelConnection = new OleDbConnection(OutputFileConnectionString); using (excelConnection) { excelConnection.Open(); DataTable tableSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = tableSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand excelDbCommand = new OleDbCommand( "INSERT INTO [" + sheetName + "] VALUES (@TesterName, @PlaceName, @PlaceArea, @Slope, @Date, @TestResult)", excelConnection); excelDbCommand.Parameters.AddWithValue("@TesterName", testerName); excelDbCommand.Parameters.AddWithValue("@PlaceName", placeName); excelDbCommand.Parameters.AddWithValue("@PlaceArea", placeArea); excelDbCommand.Parameters.AddWithValue("@Slope", slope); excelDbCommand.Parameters.AddWithValue("@Date", date); excelDbCommand.Parameters.AddWithValue("@testResult", testResult); excelDbCommand.ExecuteNonQuery(); Console.WriteLine("Added new row in Excel output file!"); } }
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { string name = Request["name"].ToString(); OleDbConnection con = DB.createDB(); con.Open(); OleDbCommand cmd = new OleDbCommand();//声明一个OleDbCommand的 cmd对象,并将其实例化 cmd.Connection = con; cmd.CommandText = "select * from tb_qianfei where name='" + name + "'"; cmd.ExecuteNonQuery(); OleDbDataReader sdr = cmd.ExecuteReader(); sdr.Read(); if (name!= "") { this.lblqfname.Text = sdr.GetString(0).ToString(); this.lblzz.Text = sdr.GetString(1); this.lbllb.Text = sdr.GetString(2); this.lblrqzs.Text = sdr.GetDateTime(3).ToShortDateString(); this.lblzzsj.Text = sdr.GetDateTime(4).ToShortDateString(); this.lblje.Text = sdr.GetString(5).ToString(); this.Lblzt.Text = sdr.GetString(6).ToString(); this.lbljsr.Text = sdr.GetString(7).ToString(); } else { Response.Write("暂无主题,不能显示"); Response.Redirect("Default.aspx");//将该页跳转到指定的页面中 } } }
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel) { try { string tem_sql = ""; //定义字符串 string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True"; //记录连接Access的语句 System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr); //连接Access数据库 System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类 tem_conn.Open(); //打开连接的Access数据库 tem_sql = "select Count(*) From " + AccTable; //设置SQL语句,获取记录个数 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类 int RecordCount = (int)tem_comm.ExecuteScalar(); //执行SQL语句,并返回结果 //每个Sheet只能最多保存65536条记录。 tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目"; //记录连接Excel的语句 tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类 tem_comm.ExecuteNonQuery(); //执行SQL语句,将数据表的内容导入到Excel中 tem_conn.Close(); //关闭连接 tem_conn.Dispose(); //释放资源 tem_conn = null; MessageBox.Show("导入完成"); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示!"); } }
private void button8_Click(object sender, EventArgs e) { string strFilePath = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" + Application.StartupPath + "\\makemoney.mdb"; System.Data.OleDb.OleDbConnection con = new OleDbConnection(strFilePath); try { string strRow = dataGridView1.CurrentCell.Value.ToString(); string sql = "delete from 工程参数表 Where 参数名称 = '" + strRow + "'"; con.Open(); System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(sql, con); OleDbCommand thisCommand = new System.Data.OleDb.OleDbCommand(sql, con); thisCommand.ExecuteNonQuery(); //this.工程参数表TableAdapter.Fill(this.makemoneyDataSet1.工程参数表); } catch (Exception ex) { throw new Exception(ex.ToString()); } finally { con.Close(); con.Dispose(); } }
static void Main() { var excelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\TestBook.xlsx; " + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""); excelConn.Open(); using (excelConn) { // Task 6 Console.WriteLine("\n\nName and Score Cells:"); var cmdScores = new OleDbCommand("SELECT * FROM [Scores$]", excelConn); var reader = cmdScores.ExecuteReader(); using (reader) { while (reader.Read()) { string name = reader["Name"].ToString(); string score = reader["Score"].ToString(); Console.WriteLine("{0,-15} - {1}", name, score); } } // Task 7 Console.WriteLine("\n\nInserting new row into the excel sheet Scores..."); string scName = "Some Student"; double scScore = 21; var cmdInsertProduct = new OleDbCommand("INSERT INTO [Scores$]([Name],[Score]) VALUES (@scName, @scScore)", excelConn); cmdInsertProduct.Parameters.AddWithValue("@scName", scName); cmdInsertProduct.Parameters.AddWithValue("@scScore", scScore); cmdInsertProduct.ExecuteNonQuery(); Console.WriteLine("Name: {0} - Score: {1}", scName, scScore); } }
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 bool DeleteSurplusRows() { if (m_pOleDbConnection != null) { try { //删除反向次数为偶数的记录记录 string strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_LineNodeID + " in ( Select " + FieldName_LineNodeID + " From (" + " Select " + FieldName_LineNodeID + ",Count(*) as RowCount From " + TableName_TempTable + " Group by "+FieldName_LineNodeID +" ) Where RowCount Mod 2=0 )"; OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection); oleDbCommand.ExecuteNonQuery(); //删除重复记录(只保留一个) strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_IndexID + " in( Select a." + FieldName_IndexID + " From " + TableName_TempTable + " As a," + TableName_TempTable + " As b Where a." + FieldName_LineNodeID + "=b." + FieldName_LineNodeID + " And a." + FieldName_IndexID + "<b." + FieldName_IndexID + ")"; oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection); oleDbCommand.ExecuteNonQuery(); return true; } catch (Exception ex) { LogAPI.WriteErrorLog(ex); } } return false; }
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 Roles_CreateRole (DbConnection connection, string applicationName, string rolename) { string appId = (string) DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName); if (appId == null) return 1; string querySelect = "SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName = ?"; OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection); AddParameter (cmdSelect, "ApplicationId", appId); AddParameter (cmdSelect, "LoweredRoleName", rolename.ToLowerInvariant ()); using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) { if (reader.Read ()) return 2; // role already exists } string queryInsert = "INSERT INTO aspnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName) VALUES (?, ?, ?, ?)"; OleDbCommand cmdInsert = new OleDbCommand (queryInsert, (OleDbConnection) connection); AddParameter (cmdInsert, "ApplicationId", appId); AddParameter (cmdInsert, "RoleId", Guid.NewGuid ().ToString ()); AddParameter (cmdInsert, "RoleName", rolename); AddParameter (cmdInsert, "LoweredRoleName", rolename.ToLowerInvariant ()); cmdInsert.ExecuteNonQuery (); return 0; }
public static void ExecuteSqlTran(Hashtable SQLStringList) { using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); using (System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction()) { System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(); try { foreach (DictionaryEntry dictionaryEntry in SQLStringList) { string cmdText = dictionaryEntry.Key.ToString(); System.Data.OleDb.OleDbParameter[] cmdParms = (System.Data.OleDb.OleDbParameter[])dictionaryEntry.Value; DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, oleDbTransaction, cmdText, cmdParms); int num = oleDbCommand.ExecuteNonQuery(); oleDbCommand.Parameters.Clear(); oleDbTransaction.Commit(); } } catch { oleDbTransaction.Rollback(); throw; } } } }
// Add new pet to database public static Boolean addPet(string[,] petData) { OleDbConnection myConnection = GetConnection(); string myQuery = "INSERT INTO pets (name, breed_id, spieces_id, sanctuary_id, age, gender, weight, bills, rescue_date, picture_path) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); myCommand.Parameters.Add("@name", OleDbType.VarWChar, 50).Value = petData[0, 1]; myCommand.Parameters.Add("@breed_id", OleDbType.Integer, 5).Value = petData[1, 1]; myCommand.Parameters.Add("@spieces_id", OleDbType.Integer, 5).Value = petData[2, 1]; myCommand.Parameters.Add("@sanctuary_id", OleDbType.Integer, 5).Value = petData[3, 1]; myCommand.Parameters.Add("@age", OleDbType.Integer, 3).Value = petData[4, 1]; myCommand.Parameters.Add("@gender", OleDbType.Integer, 1).Value = petData[5, 1]; myCommand.Parameters.Add("@weight", OleDbType.Double, 5).Value = petData[6, 1]; myCommand.Parameters.Add("@bills", OleDbType.Double, 5).Value = petData[7, 1]; myCommand.Parameters.Add("@rescue_date", OleDbType.DBDate).Value = petData[8, 1]; myCommand.Parameters.Add("@picture_path", OleDbType.VarWChar, 50).Value = petData[9, 1]; try { myConnection.Open(); myCommand.ExecuteNonQuery(); return true; } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); return false; } finally { myConnection.Close(); } }
public static void ExecuteSqlTran(ArrayList SQLStringList) { using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(); oleDbCommand.Connection = oleDbConnection; System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction(); oleDbCommand.Transaction = oleDbTransaction; try { for (int i = 0; i < SQLStringList.Count; i++) { string text = SQLStringList[i].ToString(); if (text.Trim().Length > 1) { oleDbCommand.CommandText = text; oleDbCommand.ExecuteNonQuery(); } } oleDbTransaction.Commit(); } catch (System.Data.OleDb.OleDbException ex) { oleDbTransaction.Rollback(); throw new Exception(ex.Message); } } }
protected void BtnLackRelease_Click(object sender, EventArgs e) { string objid = HidObjId.Value; string rowversion = HidRowversion.Value; string action = HidAction.Value; decimal releaseQty = Convert.ToDecimal(HidReleaseQty.Value); if (objid == "" || rowversion == "" || action == "") { Misc.Message(this.GetType(), ClientScript, "�´�ʧ�ܣ���������"); return; } if (DdlLackReason.SelectedValue == "0") { Misc.Message(this.GetType(), ClientScript, "����ʧ�ܣ���ѡ��ȱ��ԭ��"); return; } if (action == "cancel") { using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { if (conn.State != ConnectionState.Open) conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "jp_pkg_requisition_api.cancel_"; cmd.Parameters.Add("v_objid", OleDbType.VarChar).Value = objid; cmd.Parameters.Add("v_rowversion", OleDbType.VarChar).Value = rowversion; cmd.Parameters.Add("v_lack_msg", OleDbType.VarChar).Value = DdlLackReason.SelectedItem.Text; cmd.Parameters.Add("v_user", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID; cmd.ExecuteNonQuery(); GVDataDataBind(); } return; } if (action == "release") { using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { if (conn.State != ConnectionState.Open) conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "jp_pkg_requisition_api.confirm_"; cmd.Parameters.Add("v_objid", OleDbType.VarChar).Value = objid; cmd.Parameters.Add("v_rowversion", OleDbType.VarChar).Value = rowversion; cmd.Parameters.Add("v_lack_msg", OleDbType.VarChar).Value = DdlLackReason.SelectedItem.Text; cmd.Parameters.Add("v_released_qty", OleDbType.Decimal).Value = releaseQty; cmd.Parameters.Add("v_user", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID; cmd.ExecuteNonQuery(); GVDataDataBind(); } return; } }
// DB Structure creation method public static void DBStructCreate() { if (connect.State == System.Data.ConnectionState.Closed) // If no connection to DB { Console.WriteLine("No DB connection! Connecting"); DBConnect(); } int z = 1; // Query counter string[] SqlQuery = new string[] // Sql query array { "CREATE TABLE [tfr_account_statement] ([id] counter, [stock_ticker] string, [trade_status] string, [trade_direction] string, [trade_opend_date] date, [trade_open_price] double, [trade_closed_date] date, [stock_quantity] int, [trade_close_price] double, [profit_per_stock] double, [profit_per_quantity] double, [account_balance] double, [trade_profit_prcnt] double, [accumulated_sum_prcnt] string, [open_message] string, [close_message] string)" }; foreach (string i in SqlQuery) { Console.WriteLine("DB create structure. Query №: " + z); var command = new oledb.OleDbCommand(i, connect); try { command.ExecuteNonQuery(); // Execute query Console.WriteLine("DB create structure. Table created! "); } catch (Exception err) { Console.WriteLine("DB create structure. Table is not created! " + err); } z++; // Increase counter } }
public static void RegisterUser(string firstName, string LastName, string Phone, string Initials, string OrgCode, string Corr) { //System.DateTime DateAdded = System.DateTime.Today; if (Initials.Length == 0) { Initials = ""; } if (OrgCode.Length == 0) { OrgCode = ""; } if (Corr.Length == 0) { Corr = ""; } if (Phone.Length == 0) { Phone = ""; } try { System.Data.OleDb.OleDbConnection ConnReg = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand CmdReg = new System.Data.OleDb.OleDbCommand(); ConnReg.ConnectionString = ConnectionString.CSUsersTables(); //"Provider=VFPOLEDB.1;Data Source=c:\\data;Database='';Mode=Share Deny None;Extended Properties='';User ID='';Password='';Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE"; CmdReg.CommandText = Queries.RegisterNewUser(firstName, LastName, Phone, Initials, OrgCode, Corr); //"INSERT into Users (Fname,Lname,Phone,Initials,Orgcode,Corr,Dateadded) values ('" + firstName + "','" + LastName + "','" + Phone + "','" + Initials + "','" + OrgCode + "','" + Corr + "', Date())"; CmdReg.Connection = ConnReg; CmdReg.Connection.Open(); CmdReg.ExecuteNonQuery(); } catch (System.Exception ex) { Console.Write(ex.Message); } }//end of method
public void updateProductPrice(ProductInBag Product) { OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString()); OleDbCommand myCmd; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(); try { myCmd = new OleDbCommand("UpdateUnitPrice", myConn); myCmd.CommandType = CommandType.StoredProcedure; OleDbParameter objParam; objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal); objParam.Direction = ParameterDirection.Input; objParam.Value = Product.Price; objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer); objParam.Direction = ParameterDirection.Input; objParam.Value = Product.ProdID; myConn.Open(); myCmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { myConn.Close(); } }
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams) { bool mustClose = false; if (myConn == null) { mustClose = true; myConn = clsConn.getConnOLE(); } if (myConn.State != ConnectionState.Open) myConn.Open(); OleDbCommand myCMD = new OleDbCommand(); // myCMD.Connection = myConn as OleDbConnection; if (myTrans != null) myCMD.Transaction = myTrans as OleDbTransaction; // myCMD.CommandType = CommandType.Text; myCMD.CommandText = TSQL; myCMD.CommandTimeout = 180000;//3 phut // if (myParams != null) AttachParameters(myCMD, myParams); int CMDResult = myCMD.ExecuteNonQuery(); // if (mustClose) myConn.Close(); return CMDResult; }
private void addToDatabase(string sql) { OleDbConnection db = null; try { db = new OleDbConnection(); db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName; db.Open(); OleDbCommand command = new OleDbCommand(sql, db); command.ExecuteNonQuery(); } catch (Exception ex) { showErrorMessage(ex.Message); } finally { if (db != null) { db.Close(); } } }
protected void Button1_Click(object sender, EventArgs e) { try { OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder(); sb.Provider = "Microsoft.ACE.OLEDB.12.0"; sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb"); OleDbConnection conn = new OleDbConnection(sb.ConnectionString); conn.Open(); string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID"; OleDbCommand com = new OleDbCommand(updateQuery, conn); com.Parameters.AddWithValue("@ID", txtBagId.Text); com.Parameters.AddWithValue("@name", txtBagName.Text); com.Parameters.AddWithValue("@supplier", txtSupplier.Text); com.Parameters.AddWithValue("@color", txtColor.Text); com.Parameters.AddWithValue("@category", txtCategory.Text); com.Parameters.AddWithValue("@price", txtPrice.Text); com.Parameters.AddWithValue("@description", txtDescription.Text); com.ExecuteNonQuery(); lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !"; conn.Close(); } catch (Exception ex) { Response.Write("Error: " + ex.ToString()); lblMessage.Text = "Error !"; } }
protected void btnAddBag_Click(object sender, EventArgs e) { try { OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder(); sb.Provider = "Microsoft.ACE.OLEDB.12.0"; sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb"); OleDbConnection conn = new OleDbConnection(sb.ConnectionString); conn.Open(); string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)"; OleDbCommand com = new OleDbCommand(insertQuery, conn); com.Parameters.AddWithValue("@name", txtBagName.Text); com.Parameters.AddWithValue("@supplier", txtSupplier.Text); com.Parameters.AddWithValue("@color", txtColor.Text); com.Parameters.AddWithValue("@category", txtCategory.Text); com.Parameters.AddWithValue("@price", txtPrice.Text); com.Parameters.AddWithValue("@description", txtDescription.Text); com.ExecuteNonQuery(); lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !"; conn.Close(); } catch (Exception ex) { Response.Write("Error: " + ex.ToString()); lblMessage.Text = "Error" + txtBagName.Text + " !"; } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OleDb.OleDbException E) { tx.Rollback(); throw new Exception(E.Message); } } }
protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { String fName = TextBox1.Text.ToString(); String lName = TextBox2.Text.ToString(); String userName = TextBox3.Text.ToString(); String pasword = TextBox4.Text.ToString(); OleDbConnection conn = new OleDbConnection(); //Use a string variable to hold the ConnectionString. conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\Users\\Intag\\Documents\\GitHub\\Lab2new\\lms\\lms\\App_Data\\Database1.accdb"; //new String query = @"INSERT INTO Users (FirstName,LastName,Username,Pasword) VALUES(@fName,@lname,@userName,@pasword)"; System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@fName", fName); cmd.Parameters.AddWithValue("@lName", lName); cmd.Parameters.AddWithValue("@userName", userName); cmd.Parameters.AddWithValue("@pasword", pasword); conn.Open(); cmd.ExecuteNonQuery(); Response.Write("<script>alert('Registration successful');</script>"); //Response.Redirect("search.aspx"); Response.Write("<script>window.location.href='search.aspx';</script>"); conn.Close(); }
private static int WriteToExcelFile(string connExcel, OleDbConnection dbCon) { var excelSchema = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES(@name, @score)", dbCon); string name = "Evlogy Christov"; int score = 34; command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@score", score); int queryResult = command.ExecuteNonQuery(); command.Parameters.Clear(); name = "Christian Zaklev"; score = 32; command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@score", score); queryResult += command.ExecuteNonQuery(); // a ready-made way to "invent" new parameters: for (int i = 5; i < 10; i++) { command.Parameters.Clear(); command.Parameters.AddWithValue("@name", "Student" + i); command.Parameters.AddWithValue("@score", i); queryResult += command.ExecuteNonQuery(); } return queryResult; }
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; }