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; }
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 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("项目修改成功!", "提示"); } } }
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(); }
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 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 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(); }
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() })); } } } }
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 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 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 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 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; }
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); } }
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");//将该页跳转到指定的页面中 } } }
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(); }
// 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(); } }
// 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(); } }
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(); }
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; } }
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; }
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!"); } }
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; }
private void addToDatabase(string sql) { OleDbConnection db = null; try { db = new OleDbConnection(); db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName; db.Open(); OleDbCommand command = new OleDbCommand(sql, db); command.ExecuteNonQuery(); } catch (Exception ex) { showErrorMessage(ex.Message); } finally { if (db != null) { db.Close(); } } }
public void 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; }
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 [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] 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); } } }
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; }