private void button_val_Click(object sender, EventArgs e) { if (!(ChampVide() == false)) { MessageBox.Show("Veuillez saisir les informations manquantes"); } else { try { connexion.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM Utilisateur WHERE ID='" + textBox_id.Text + "' AND MDP ='" + textBox_mdp.Text + "';", connexion); cmd.ExecuteNonQuery(); cmd.Clone(); DialogResult resultat; resultat = MessageBox.Show("Bienvenu dans votre session", "Avertissement", MessageBoxButtons.OK, MessageBoxIcon.Information); if (resultat == System.Windows.Forms.DialogResult.OK) { connexion.Close(); } this.Close(); // a.ID = textBox_id.Text; a.MDP = textBox_mdp.Text; } catch (Exception ex) { MessageBox.Show(" -- L'identifiant ou le mot de passe est incorrect -- " + ex.ToString()); } } connexion.Close(); }
private void cb_cliente_SelectedIndexChanged(object sender, EventArgs e) { #region busqueda cliente try { OleDbCommand comando = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); comando.Connection = cnn; cnn.Open(); string cadena = "Select * from Table_Cotizacion where Nombre ='" + cb_cliente.Text + "' "; comando.CommandText = cadena; OleDbDataAdapter adaptador = new OleDbDataAdapter(comando); DataTable dt = new DataTable(); adaptador.Fill(dt); dGV_CotizacionCliente.DataSource = dt; comando.Clone(); } catch (Exception ex) { MessageBox.Show("Tenemos problemas" + ex.ToString()); } #endregion }
private void Valider_Click(object sender, EventArgs e) { if (!(ChampVide() == false)) { MessageBox.Show("Veuillez saisir toutes les informations demandées"); } else { try { DateTime heure = DateTime.Now; //string format = "yyyy-MM-dd HH:MM:ss"; connexion.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO Utilisateur (Nom,Prénom,ID,MDP,Taille,Naissance,Genre) VALUES ('" + textBox2.Text + "','" + textBox1.Text + "','" + textBox_id.Text + "','" + textBox_mdp.Text + "','" + textBox3.Text + "','" + textBox5.Text + "','" + listBox1.SelectedIndex.ToString() + "')", connexion); cmd.ExecuteNonQuery(); cmd.Clone(); DialogResult resultat; resultat = MessageBox.Show("Les informations ont été bien prises en compte", "Avertissement", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); if (resultat == System.Windows.Forms.DialogResult.OK) { connexion.Close(); } this.Hide(); Form c = new Form_identification(); c.ShowDialog(); this.Close(); } catch (Exception ex) { MessageBox.Show("Problème de connexion avec la base de donnée" + ex.ToString()); } } connexion.Close(); }
public bool DropTable(string tablename) { try { if (Connection.State != ConnectionState.Open) { Connection.Open(); } const string cmdText = "Drop Table [{0}]"; var myCommand = new OleDbCommand { Connection = Connection, CommandText = string.Format(cmdText, tablename) }; myCommand.ExecuteNonQuery(); Thread.Sleep(1000); myCommand.Clone(); myCommand.Dispose(); Connection.Close(); return(true); } catch (Exception ex) { OnWriteProgress(0); return(false); } }
private void comboBox_formadepago_SelectedIndexChanged(object sender, EventArgs e) { #region busqueda formadepago try { OleDbCommand comando = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); comando.Connection = cnn; cnn.Open(); string cadena = "Select * from Table_Venta where FormadePago ='" + comboBox_formadepago.Text + "' "; comando.CommandText = cadena; OleDbDataAdapter adaptador = new OleDbDataAdapter(comando); DataTable dt = new DataTable(); adaptador.Fill(dt); dataGridView_verdatos.DataSource = dt; comando.Clone(); } catch (Exception ex) { MessageBox.Show("Tenemos problemas" + ex.ToString()); } #endregion }
private void btn_buscar_Click(object sender, EventArgs e) { //string fecha; lbl_Antes.Text = monthAntes.SelectionRange.Start.ToString("dd/MM/yyyy"); lbl_Despues.Text = monthAntes.SelectionRange.Start.ToString("dd/MM/yyyy"); //fecha = Convert.ToString(txt_Antes); try { OleDbDataReader dr; OleDbCommand com = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Folio, Nombre, RFC, Email from VentaTB where Fecha = @fecha "; com.Parameters.AddWithValue("@fecha", monthAntes.SelectionRange.Start); com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataMostrarDatos.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
public bool WriteTable(string tableName, Dictionary <string, string> tableDefination) { try { if (Connection.State != ConnectionState.Open) { Connection.Open(); } var com = new OleDbCommand { Connection = Connection, CommandText = GenerateCreateTable(tableName, tableDefination) }; com.ExecuteNonQuery(); com.Clone(); com.Dispose(); Connection.Close(); return(true); } catch { return(false); } }
private void btn_ver_Click(object sender, EventArgs e) { OleDbDataReader dr; OleDbCommand com = new OleDbCommand(); try { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Nombre, RFC, Telefono, Email, Direccion, Fecha, Producto, Cantidad, PrecioUnitario, SubTotal, Total from CotizacionTB"; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataverCot.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
private void comboCliente_SelectedIndexChanged(object sender, EventArgs e) { try { OleDbDataReader dr; OleDbCommand com = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Folio, Nombre, RFC, Email from VentaTB where Nombre ='" + comboCliente.Text + "' "; //com.Parameters.AddWithValue("@nombre", mon); com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataMostrarDatos.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
private void info() { connexion.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM Utilisateur WHERE ID='" + textBox_id.Text + "' AND MDP ='" + textBox_mdp.Text + "';", connexion); cmd.ExecuteNonQuery(); cmd.Clone(); }
private static void Test_Command_Cancel() { using (OleDbCommand cmd = new OleDbCommand("select * from code;", conn)) { cmd.ExecuteReader(); cmd.Cancel(); OleDbCommand cmd_clone = cmd.Clone(); Assert.AreEqual(cmd.ToString(), cmd_clone.ToString()); } }
protected void Button1_Click(object sender, EventArgs e) { string id = TextBox1.Text; string pwd = password.Text; string type = DropDownList1.SelectedItem.Value; OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/db.mdb")); conn.Open(); //打开数据库 OleDbCommand cmd = new OleDbCommand(); string sql = "select count(*) from [user] where Uno = '" + id + "' and Upwd = '" + pwd + "' and Utype= '" + type + "'"; cmd.CommandText = sql; cmd.Connection = conn; int count = (int)cmd.ExecuteScalar(); if (count == 1) { sql = "SELECT Uno, Uname, Utype, Nborrow, Total, Pay, Credit FROM [user] WHERE Uno='" + id + "'"; cmd.CommandText = sql; cmd.Connection = conn; OleDbDataReader reader = cmd.ExecuteReader(); //登录获取用户信息并存Session if (reader.Read()) { Session["name"] = reader["Uname"].ToString(); Session["borrow"] = reader["Nborrow"].ToString(); Session["total"] = reader["Total"].ToString(); Session["pay"] = reader["Pay"].ToString(); Session["credit"] = reader["Credit"].ToString(); } Label1.Text = "登录成功!"; Session["id"] = id; Session["type"] = type; //Response.Write(Session["name"]); if ((string)Session["type"] == "管理员") { Response.Redirect("control.aspx"); } else { Response.Redirect("index.aspx"); } } else { Label1.Text = "登录失败!请重新登录"; } cmd.Clone(); conn.Close(); }
private void btn_consultar_Click(object sender, EventArgs e) { #region mostrar cliente try { OleDbCommand comando = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); comando.Connection = cnn; cnn.Open(); string cadena = "Select * from Table_Cotizacion"; comando.CommandText = cadena; OleDbDataAdapter adaptador = new OleDbDataAdapter(comando); DataTable dt = new DataTable(); adaptador.Fill(dt); dGV_CotizacionCliente.DataSource = dt; comando.Clone(); } catch (Exception ex) { MessageBox.Show("Tenemos problemas" + ex.ToString()); } #endregion #region mostrar productos try { OleDbCommand comando = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); comando.Connection = cnn; cnn.Open(); string cadena = "Select * from Table_VentasTemporales"; comando.CommandText = cadena; OleDbDataAdapter adaptador = new OleDbDataAdapter(comando); DataTable dt = new DataTable(); adaptador.Fill(dt); dGV_CotizacionProducto.DataSource = dt; comando.Clone(); } catch (Exception ex) { MessageBox.Show("Tenemos problemas" + ex.ToString()); } #endregion }
public bool AddNewRow(DataRow dr, string tableName) { var myCommand = new OleDbCommand { Connection = Connection, CommandText = GenerateInsertStatement(dr, tableName) }; myCommand.ExecuteNonQuery(); myCommand.Clone(); myCommand.Dispose(); return(true); }
public string selectcode(string codeid, string unm, int flag) { string canshu = ""; if (flag == 1) { canshu = "yuancode"; } else { canshu = "path"; } OleDbConnection conn = getconn(); OleDbCommand com = new OleDbCommand(); OleDbDataReader reader; string codedocument = ""; com.Connection = conn; com.CommandText = "SELECT code.yuancode FROM code INNER JOIN [user] ON code.myid = user.myid where code.codeid=@codeid and [user].username=@unm"; com.Parameters.AddWithValue("@codeid", SqlDbType.Int); com.Parameters["@codeid"].Value = codeid; com.Parameters.AddWithValue("@unm", SqlDbType.VarChar); com.Parameters["@unm"].Value = unm; conn.Open(); reader = com.ExecuteReader(); reader.Read(); try { codedocument = reader[canshu].ToString(); } catch (Exception e) { com.Clone(); conn.Close(); return(null); } com.Dispose(); conn.Close(); return(codedocument); }
public Boolean login(string username, string userpass, int flag) { if (username == null || userpass == null) { return(false); } OleDbConnection conn = getconn(); OleDbCommand com = new OleDbCommand(); OleDbDataReader reader; com.Connection = conn; if (flag == 1) { com.CommandText = "select myid,username,userpassword from [user] where username=@username and userpassword=@userpass"; com.Parameters.AddWithValue("@username", SqlDbType.VarChar); com.Parameters["@username"].Value = username; com.Parameters.AddWithValue("@userpass", SqlDbType.VarChar); com.Parameters["@userpass"].Value = userpass; } else { com.CommandText = "select myid,username from [user] where username=@username"; com.Parameters.AddWithValue("@username", SqlDbType.VarChar); com.Parameters["@username"].Value = username; } conn.Open(); reader = com.ExecuteReader(); reader.Read(); try { int a = (int)reader["myid"]; } catch (Exception e) { com.Clone(); conn.Close(); return(false); } com.Dispose(); conn.Close(); return(true); }
private void btn_guncelle_Click(object sender, EventArgs e) { baglanti_kontrol(); try { OleDbCommand cm = new OleDbCommand("update hatirlatma set konu = @konu , cikis_tarih = @c_tarih , cikis_saat = @c_saat where hatirlatma_id = @id", cn); cm.Parameters.AddWithValue("@konu", cbox_konu.Text); cm.Parameters.AddWithValue("@c_saat", dt_saat.Text); cm.Parameters.AddWithValue("@c_tarih", dt_tarih.Text); cm.Parameters.AddWithValue("@id", cbox_konu.SelectedValue); cn.Open(); cm.ExecuteNonQuery(); cm.Clone(); MessageBox.Show("Güncelleme işlemi başarılı.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Hata Metni : " + ex.Message); } }
private void Valider_Click(object sender, EventArgs e) { try { connexion.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM Utilisateur WHERE ID='" + textBox_id.Text + "' AND MDP ='" + textBox_mdp.Text + "';", connexion); cmd.ExecuteNonQuery(); cmd.Clone(); DialogResult resultat; resultat = MessageBox.Show("Bienvenu dans votre session", "Avertissement", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); if (resultat == System.Windows.Forms.DialogResult.OK) { connexion.Close(); } this.Close(); } catch (Exception ex) { MessageBox.Show("L'identifiant ou le mot de passe est incorrect" + ex.ToString()); } connexion.Close(); }
private void btn_verRe_Click(object sender, EventArgs e) { try { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EjemProyec.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Nombre, Direccion, Telefono from Cliente"; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataMuestraD.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
public DataTable ReadTable(string tableName, string criteria) { try { if (Connection.State != ConnectionState.Open) { Connection.Open(); OnReadProgress(10); } var cmdText = "Select * from [{0}]"; if (!string.IsNullOrEmpty(criteria)) { cmdText += " Where " + criteria; } var cmd = new OleDbCommand(string.Format(cmdText, tableName)) { Connection = Connection }; var adpt = new OleDbDataAdapter(cmd); OnReadProgress(30); var ds = new DataSet(); OnReadProgress(50); adpt.Fill(ds, tableName); OnReadProgress(100); cmd.Clone(); cmd.Dispose(); adpt.Dispose(); Connection.Close(); return(ds.Tables.Count == 1 ? ds.Tables[0] : null); } catch { return(null); } }
private void btn_verdatos_Click(object sender, EventArgs e) { OleDbCommand com = new OleDbCommand(); try { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Nombre, RFC, Direccion, Telefono, Email, FechaCliente from ClienteTB"; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); datamostrardatos.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
public void PointSet(double point, string ID) { /*string sqlstr = "select Point from Menu where ID="; * sqlstr += "'" + ID + "'"; * command = connect.CreateCommand(); * command.CommandText = sqlstr; * OleDbDataReader reader = command.ExecuteReader(); * while (reader.Read()) * { * bpoint = reader["Point"].ToString(); * count = reader["Count"].ToString(); * } * int npoint = (int.Parse(bpoint) + point) / int.Parse(count); */ string Ppoint = point.ToString(); string sqlstr = "Update Menu Set Point="; sqlstr += "'" + Ppoint + "'"; sqlstr += "WHERE ID="; sqlstr += "'" + ID + "'"; command = new OleDbCommand(sqlstr, connect); command.ExecuteNonQuery(); command.Clone(); }
private void btn_verdatos_Click(object sender, EventArgs e) { OleDbCommand com = new OleDbCommand(); try { OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); com.Connection = cnn; cnn.Open(); string query = "select Producto, Descripcion, Cantidad, Precio from ProductoTB"; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataverproductos.DataSource = dt; com.Clone(); } catch (Exception ex) { MessageBox.Show("No se pudo llenar" + ex.ToString()); } }
private void btn_agregar_Click(object sender, EventArgs e) { /* try * { * OleDbCommand com = new OleDbCommand(); * OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); * cnn.Open(); * com.Connection = cnn; * //com.CommandText = "insert into VentasTemporalesTB (Producto, Cantidad, Precio) VALUES ('" + comboproducto.Text + "','" + txt_conCant.Text + "','" + txt_PU.Text + "')"; * com.CommandText = "insert into VentasTemporalesTB (Producto, Cantidad, Precio, IdVT) VALUES ('" + comboproducto.Text + "','" + txt_conCant.Text + "','" + txt_PU.Text + "','" + lbl_con.Text + "')"; * com.ExecuteNonQuery(); * //MessageBox.Show("Cliente guardado exitosamente"); * cnn.Close(); * } * catch (Exception ex) * { * * MessageBox.Show("No se pudo guardar " + ex); * } */ try { OleDbCommand com = new OleDbCommand(); OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); cnn.Open(); com.Connection = cnn; //string query = "select Producto, Cantidad, Precio from VentasTemporalesTB"; string query = "select Producto, Cantidad, PrecioUnitario from CotizacionTB where IdCo=" + lbl_con.Text; com.CommandText = query; OleDbDataAdapter da = new OleDbDataAdapter(com); DataTable dt = new DataTable(); da.Fill(dt); dataMostrarInfo.DataSource = dt; com.Clone(); cnn.Close(); } catch (Exception ex) { //connection.Close(); MessageBox.Show("No se pudo llenar el Datagridview: " + ex.ToString()); } /* try * { * OleDbCommand com = new OleDbCommand(); * OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PuntodeVentaBD.accdb"); * cnn.Open(); * com.Connection = cnn; * //string IdProducto = Convert.ToString(txtIdProducto.Text); * com.CommandText = "insert into DetalleVentaTB (Cantidad, Precio) VALUES ('" + txt_conCant.Text + "','" + txt_PU.Text + "')"; * com.ExecuteNonQuery(); * MessageBox.Show("Venta guardada exitosamente"); * cnn.Close(); * } * catch (Exception ex) * { * //connection.Close(); * MessageBox.Show("error " + ex); * } */ double subtotal = 0; double iva = 0; foreach (DataGridViewRow row in dataMostrarInfo.Rows) { //subtotal += Convert.ToDouble(row.Cells["Precio"].Value); subtotal += Convert.ToDouble(row.Cells["PrecioUnitario"].Value); } txt_subtotal.Text = Convert.ToString(subtotal); if (checkB_iva.Checked == true) { iva += Convert.ToDouble(subtotal * 0.16); txt_total.Text = Convert.ToString(iva + subtotal); } else { txt_total.Text = Convert.ToString(subtotal); } }
private static async Task XMLToDatabase() { //throw new NotImplementedException(); FileInfo xFile = new FileInfo("Request_Definition.xml"); if (!xFile.Exists) { throw new Exception("XML File not exist."); } await Task.Run(() => { XElement xRequest = XElement.Load(xFile.FullName); //Debug.Print(xRequest.ToString()); //string Method, Input Address $H$51, //return value DateTime xDate(string Address) { IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; IEnumerable <string> ieRangeVal = from XElement x in ieFilter where x.Attribute("CellAddress").Value == Address && x.Attribute("Type").Value == "xlRange" select x.Attribute("CellValue").Value; int FoundNode = ieRangeVal.Count(); if (FoundNode != 1) { return(DateTime.Parse("1900-01-01")); } string DateString = ieRangeVal.First(); bool IsDate = DateTime.TryParse(DateString, out DateTime result); if (IsDate) { return(result); } else { return(DateTime.Parse("1900-01-01")); } } string xRange(string Address) { IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; IEnumerable <string> ieRangeVal = from XElement x in ieFilter where x.Attribute("CellAddress").Value == Address && x.Attribute("Type").Value == "xlRange" select x.Attribute("CellValue").Value; int FoundNode = ieRangeVal.Count(); if (FoundNode != 1) { return("未入力"); } return(ieRangeVal.First()); } //string Method, Input Checkbox Area $H$42:$K$43 //No, there is no need string xCheck(string Address) { CellRange TargetCellArea = new CellRange(Address); string AddressRange = TargetCellArea.Extend(); //Check if any of that is in the IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; //Debug.Print(AddressRange); IEnumerable <string> ieCheckVal = from XElement x in ieFilter where AddressRange.Contains(x.Attribute("CellAddress").Value) && x.Attribute("Type").Value == "CheckBox" select x.Attribute("CellValue").Value; int ValueFound = ieCheckVal.Count(); switch (ValueFound) { case 0: return("未入力"); case 1: return(ieCheckVal.First()); default: return("無効な入力"); } } // Get Cell Value: xRange("$H$8")); // Get Checkbox Value: xCheck("$H$34:$K$36")); // Connect to the Database and sync it using (OleDbConnection conn = DatabaseConnection()) { conn.StateChange += (object sender, StateChangeEventArgs e) => Debug.Print("Database Status Changed from {0} to {1}" , e.OriginalState, e.CurrentState); OleDbCommand INSERT_Host = new OleDbCommand(); OleDbCommand INSERT_Excel = new OleDbCommand(); OleDbCommand INSERT_Agent = new OleDbCommand(); OleDbTransaction TransactAll = null; conn.Open(); TransactAll = conn.BeginTransaction(IsolationLevel.ReadCommitted); INSERT_Host.Connection = conn; INSERT_Host.Transaction = TransactAll; INSERT_Agent = INSERT_Host.Clone(); INSERT_Excel = INSERT_Host.Clone(); //Make 3 Commands, one for each column //return value is rows affected. int MakeCommandForServer(string StartAddress) { Regex ValidHostname = new Regex(@"(\w|\d){8,}\.?"); CellRange caStart = new CellRange(StartAddress); string MA_Hostname = xRange(caStart.Extend()); Match mhValidHost = ValidHostname.Match(MA_Hostname); if (mhValidHost.Success) { INSERT_Host.CommandText = File.ReadAllText(SQLFolder + "INSERTHost.sql"); INSERT_Host.Parameters.AddWithValue("@ostname", MA_Hostname); INSERT_Host.Parameters.AddWithValue("@PAddress", xRange(caStart.NextRow())); if (caStart.InitRange().Contains("49")) //M/Agent Range at 49 is VIP { INSERT_Host.Parameters.AddWithValue("@aker", "VIP"); INSERT_Host.Parameters.AddWithValue("@odel", "VIP"); INSERT_Host.Parameters.AddWithValue("@PUCount", "VIP"); INSERT_Host.Parameters.AddWithValue("@PUMicroprocessor", "VIP"); INSERT_Host.Parameters.AddWithValue("@S", "VIP"); INSERT_Host.Parameters.AddWithValue("@ersion", "VIP"); INSERT_Host.Parameters.AddWithValue("@itVal", "VIP"); INSERT_Host.Parameters.AddWithValue("@lusterBox", "VIP"); INSERT_Host.Parameters.AddWithValue("@lusterIndex", "VIP"); } else { INSERT_Host.Parameters.AddWithValue("@aker", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@odel", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@PUCount", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@PUMicroprocessor", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@S", xCheck(caStart.NextArea(3))); INSERT_Host.Parameters.AddWithValue("@ersion", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@itVal", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@lusterBox", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@lusterIndex", xRange(caStart.NextRow())); } //Debug.Print("Parameters Before Clear: {0}", INSERT_Host.Parameters.Count); int RowsAffected = INSERT_Host.ExecuteNonQuery(); INSERT_Host.Parameters.Clear(); return(RowsAffected); } return(0); } int MakeCommandforAgent(char column) { if (!char.IsLetter(column)) { throw new Exception("Invalid Column Character"); } Regex ValidHostname = new Regex(@"(\w|\d){8,}\.?"); INSERT_Agent.CommandText = File.ReadAllText(SQLFolder + "INSERTAgent.sql"); //Agent Name is like uny40310.abc00101 //If VIP Exist, use VIP; If VIP not exist, use Pri //Validate VIP At 49, and PRI at 51 string magentName = ""; string tempRange = string.Format(@"${0}$49", column); if (ValidHostname.IsMatch(xRange(tempRange))) { magentName = xRange(tempRange); } tempRange = string.Format(@"${0}$51", column); if (ValidHostname.IsMatch(xRange(tempRange))) { magentName = xRange(tempRange); } if (magentName == "") { throw new Exception("Invalid MA Column, magentName Failed to caputre."); } tempRange = string.Format(@"${0}$98", column); string mserverName = xRange(tempRange); string AgentName = string.Format(@"{0}.{1}" , ControlMServer.Convert(mserverName), magentName); INSERT_Agent.Parameters.AddWithValue("@AgentName", AgentName); INSERT_Agent.Parameters.AddWithValue("@rlnFileName", xRequest.Element("xlname").Value); tempRange = string.Format(@"${0}$31", column); CellRange cell = new CellRange(tempRange); INSERT_Agent.Parameters.AddWithValue("@ApplyType", xCheck(cell.NextArea(2))); INSERT_Agent.Parameters.AddWithValue("@ChangePoint", xCheck(cell.NextArea(3))); INSERT_Agent.Parameters.AddWithValue("@SIer", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@ServerPIC", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@SystemID", xRange(cell.NextRow())); //7 INSERT_Agent.Parameters.AddWithValue("@SystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@SystemSubName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@NetworkLocation", xCheck(cell.NextArea(2))); INSERT_Agent.Parameters.AddWithValue("@NetworkArea", xCheck(cell.NextArea(4))); //11 INSERT_Agent.Parameters.AddWithValue("@ServerVIP", xRange(cell.NextRow(2))); INSERT_Agent.Parameters.AddWithValue("@ServerPRI", xRange(cell.NextRow(2))); INSERT_Agent.Parameters.AddWithValue("@ServerSEC", xRange(cell.NextRow(13))); //14 INSERT_Agent.Parameters.AddWithValue("@MStMACommunicationPort", xRange(cell.NextRow(13))); INSERT_Agent.Parameters.AddWithValue("@MA_InstallDate", xDate(cell.NextRow())); //16 INSERT_Agent.Parameters.AddWithValue("@MS_Connection", xDate(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@JobStartDate", xDate(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@JobCount", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@HasCallorder", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@HasFirewall", xCheck(cell.NextArea(1))); //21 INSERT_Agent.Parameters.AddWithValue("@MA_Version", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@IsFirstTime", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@IsProduction", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@TestDoneDate", xDate(cell.NextRow())); //25 INSERT_Agent.Parameters.AddWithValue("@CostFrom", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@CostFromSystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@CostFromSubSystemName", xRange(cell.NextRow())); //28 INSERT_Agent.Parameters.AddWithValue("@HasSundayJobs", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@HasRelatedSystems", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemID", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemSubName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemDatacenter", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MAtMSCommunicationPort", xRange(cell.NextRow())); //30 INSERT_Agent.Parameters.AddWithValue("@MSVIP", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MSPRI", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MSSEC", xRange(cell.NextRow())); //33 int RowsAffected = INSERT_Agent.ExecuteNonQuery(); INSERT_Agent.Parameters.Clear(); return(RowsAffected); /* * @AgentName, @rlnFileName, @ApplyType, @ChangePoint, @SIer, @ServerPIC, * @SystemID, @SystemName, @SystemSubName, @NetworkLocation, @NetworkArea, * @ServerVIP, @ServerPRI, @ServerSEC, @MStMACommunicationPort, @MA_InstallDate, * @MS_Connection, @JobStartDate, @JobCount, @HasCallorder, @HasFirewall, @MA_Version, * @IsFirstTime, @IsProduction, @TestDoneDate, @CostFrom, @CostFromSystemName, * @CostFromSubSystemName, @HasSundayJobs, @HasRelatedSystems, @RelatedSystemID, * @RelatedSystemName, @RelatedSystemSubName, @RelatedSystemDatacenter, * @MAtMSCommunicationPort, @MSVIP, @MSPRI, @MSSEC 38 */ } // Sync the information of the new task // return values is rows affected int MakeCommandforExcel() { INSERT_Excel.CommandText = File.ReadAllText(SQLFolder + "INSERTExcel.sql"); INSERT_Excel.Parameters.AddWithValue("@lname", xRequest.Element("xlname").Value); CellRange caStart = new CellRange("$H$7"); string applydate = xRange(caStart.EndinRange()); Debug.Print(applydate); INSERT_Excel.Parameters.AddWithValue("@ate_apply", DateTime.Parse(applydate)); INSERT_Excel.Parameters.AddWithValue("@pplier", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@ailaddress", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@honenumber", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@pprover", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@obcon_accept", "陳 黙"); INSERT_Excel.Parameters.AddWithValue("@obcon_confirm", "徐 長練"); INSERT_Excel.Parameters.AddWithValue("@obcon_approve", "孫 紅莉"); INSERT_Excel.Parameters.AddWithValue("@pecialcomment", xRange("$E$161")); Debug.Print("INSERT_Excel Parameters Count: {0}", INSERT_Excel.Parameters.Count); //Debug.Print(INSERT_Excel.CommandText); return(INSERT_Excel.ExecuteNonQuery()); }//(@lname, @ate_apply, @pplier, @ailaddress, @honenumber, @pprover, @obcon_accept, @obcon_confirm, @obcon_approve, @pecialcomment); if (MakeCommandForServer("$H$49") == 1 || MakeCommandForServer("$H$51") == 1 || MakeCommandForServer("$H$64") == 1) { MakeCommandforAgent('H'); } if (MakeCommandForServer("$L$49") == 1 || MakeCommandForServer("$L$51") == 1 || MakeCommandForServer("$L$64") == 1) { MakeCommandforAgent('L'); } if (MakeCommandForServer("$P$49") == 1 || MakeCommandForServer("$P$51") == 1 || MakeCommandForServer("$P$64") == 1) { MakeCommandforAgent('P'); } MakeCommandforExcel(); TransactAll.Commit(); } }); }
private void comboBox_clienteCot_SelectedIndexChanged(object sender, EventArgs e) { #region busqueda de combobox y seleccion producto string cadena = "Select * from Table_Cotizacion where FolioCot = '" + comboBox_clienteCot.Text + "' "; OleDbConnection cnn = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); OleDbCommand comando = new OleDbCommand(cadena, cnn); cnn.Open(); OleDbDataReader leer = comando.ExecuteReader(); if (leer.Read() == true) { txt_nombre.Text = leer["Nombre"].ToString(); txt_rfc.Text = leer["RFC"].ToString(); txt_razonsocial.Text = leer["RazonSocial"].ToString(); txt_direccion.Text = leer["Direccion"].ToString(); txt_email.Text = leer["Email"].ToString(); txt_telefono.Text = leer["Telefono"].ToString(); comboBox_productos.Text = leer["Producto"].ToString(); txt_cantidad.Text = leer["Cantidad"].ToString(); txt_subtotal.Text = leer["Subtotal"].ToString(); txt_total.Text = leer["Total"].ToString(); lbl_numerodeventa.Text = leer["FolioCot"].ToString(); lbl_Ultimacotizacion.Text = leer["IDCliente"].ToString(); } else { txt_nombre.Text = ""; txt_rfc.Text = ""; txt_razonsocial.Text = ""; txt_direccion.Text = ""; txt_email.Text = ""; txt_telefono.Text = ""; comboBox_productos.Text = ""; txt_cantidad.Text = ""; txt_subtotal.Text = "$"; txt_total.Text = "$"; lbl_numerodeventa.Text = "#"; lbl_Ultimacotizacion.Text = "#"; } cnn.Close(); #endregion #region llenar el comobobox try { OleDbCommand coma = new OleDbCommand(); OleDbConnection cone = new OleDbConnection("Provider=sqloledb;Data Source=LENOY97;Initial Catalog=ProyectoPdeVDelsel;Integrated Security=SSPI"); cone.Open(); coma.Connection = cone; string query = "select Producto, Cantidad, Precio, CostoTo from Table_VentasTemporales where IDCliente=" + lbl_Ultimacotizacion.Text; //com.Connection = cnn; coma.CommandText = query; //com.ExecuteNonQuery(); //MessageBox.Show("Venta realizada"); //lbl_mostrarExito.Visible = true; OleDbDataAdapter da = new OleDbDataAdapter(coma); DataTable dt = new DataTable(); da.Fill(dt); dataGridView_verdatos.DataSource = dt; coma.Clone(); cone.Close(); } catch (Exception ex) { MessageBox.Show("Hay problemas " + ex); //lbl_ventabad.Visible = true; } #endregion #region suma datagrid double subtotal = 0; double iva = 0; foreach (DataGridViewRow row in dataGridView_verdatos.Rows) { //subtotal += Convert.ToDouble(row.Cells["Column4"].Value); subtotal += Convert.ToDouble(row.Cells["CostoTo"].Value); } txt_subtotal.Text = Convert.ToString(subtotal); if (checkBox_iva.Checked == true) { iva += Convert.ToDouble(subtotal * 0.16); txt_total.Text = Convert.ToString(iva + subtotal); } else { txt_total.Text = Convert.ToString(subtotal); } #endregion }
public DataTable GetYourData(string fullPath) { DataTable YourResultSet = new DataTable(); OleDbConnection yourConnectionHandler = new OleDbConnection($"Provider=VFPOLEDB.1;Data Source={new FileInfo(fullPath)};"); yourConnectionHandler.Open(); if (yourConnectionHandler.State == ConnectionState.Open) { string mySQL = $"select * from {new FileInfo(fullPath).Name}"; OleDbCommand MyQuery = new OleDbCommand(mySQL, yourConnectionHandler); OleDbDataReader reader = MyQuery.ExecuteReader(); DataTable schemaTable = reader.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) { if (column.ColumnName == "ColumnName") { YourResultSet.Columns.Add(new DataColumn(row[column].ToString())); } } } while (reader.Read()) { DataRow dataRow = YourResultSet.NewRow(); for (int i = 0; i < YourResultSet.Columns.Count; i++) { try { var value = ConvertTo.UnicodeFrom(TextEncoding.Arabic1256, Convert.ToString(reader[i])); var reg = new Regex(@"\d{1,2}\/\d{1,2}\/\d{1,2}"); if (((DataColumn)YourResultSet.Columns[i]).DataType == typeof(System.String)) { if (reg.IsMatch(value)) { dataRow[((DataColumn)YourResultSet.Columns[i])] = ConvertTo.UnicodeFrom(TextEncoding.Arabic1256, Convert.ToString(reader[i]), false); } else if (reader[i].ToString().ToLower() == "true" || reader[i].ToString().ToLower() == "false") { dataRow[((DataColumn)YourResultSet.Columns[i])] = Convert.ToString(reader[i]); } else { dataRow[((DataColumn)YourResultSet.Columns[i])] = value; } } } catch (Exception) { dataRow[((DataColumn)YourResultSet.Columns[i])] = String.Empty; } } YourResultSet.Rows.Add(dataRow); Application.DoEvents(); } reader.Close(); MyQuery.Clone(); yourConnectionHandler.Close(); MyQuery.Dispose(); yourConnectionHandler.Dispose(); } return(YourResultSet); }
public DataTable readfileExcel(string pathFile, string strSelect, string strWhere) { OleDbCommand command; OleDbDataReader dr; DataTable dt = new DataTable("dt"); // var pathFile = ""; try { if (pathFile != "") { #region Đọc excel string connStr = string.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;", pathFile); var conn = new OleDbConnection(connStr); conn.Open(); var dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dbSchema == null || dbSchema.Rows.Count < 1) { return(null); } string excelSheetName = ""; if (dbSchema != null) { foreach (DataRow drSheet in dbSchema.Rows) { if (drSheet["TABLE_NAME"].ToString().Contains("$")) { excelSheetName = drSheet["Table_Name"].ToString(); break; } } } command = new OleDbCommand(strSelect + " from [" + excelSheetName + "] " + strWhere, conn); try { dr = command.ExecuteReader(); } catch (Exception ex) { conn.Dispose(); conn.Close(); command.Clone(); if (pathFile != "" && File.Exists(pathFile)) { File.Delete(pathFile); } //MessageBox.Show("File chưa đúng định dạng, hoặc đang mở, xin vui lòng kiểm tra lại" + ex.ToString()); return(null); } if (dr != null) { dt.Load(dr); } conn.Dispose(); conn.Close(); command.Clone(); dr.Close(); if (pathFile != "" && File.Exists(pathFile)) { File.Delete(pathFile); } #endregion } return(dt); } catch (Exception ex) { //MessageBox.Show("File chưa đúng định dạng, hoặc đang mở, xin vui lòng kiểm tra lại" + ex.ToString()); return(null); } }
public bool ExportData(string zoneId, string importDatabase, bool chkFormatConn) { Database database = DatabaseFactory.CreateDatabase(); DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Ad_ADZone_GetExportList"); OleDbConnection connection = new OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data Source = " + importDatabase); if (chkFormatConn) { OleDbCommand command2 = new OleDbCommand("DELETE FROM [PE_AdZone]", connection); command2.CommandType = CommandType.Text; connection.Open(); try { command2.ExecuteNonQuery(); } catch { return(false); } finally { command2.Cancel(); connection.Close(); } } database.AddInParameter(storedProcCommand, "@ZoneID", DbType.String, zoneId); using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand))) { connection.Open(); object obj2 = new OleDbCommand("SELECT MAX(ZoneId) FROM PE_AdZone", connection).ExecuteScalar(); connection.Close(); int num = Convert.IsDBNull(obj2) ? 1 : (Convert.ToInt32(obj2) + 1); bool flag = false; while (reader.Read()) { if (!flag) { flag = true; } else { num++; } OleDbCommand command4 = new OleDbCommand("INSERT INTO PE_AdZone (ZoneID, ZoneName, ZoneJSName, ZoneIntro, ZoneType, DefaultSetting, ZoneSetting, ZoneWidth, ZoneHeight, ShowType, Active) VALUES (@ZoneID, @ZoneName, @ZoneJSName, @ZoneIntro, @ZoneType, @DefaultSetting, @ZoneSetting, @ZoneWidth, @ZoneHeight, @ShowType, @Active)", connection); try { OleDbParameter parameter = new OleDbParameter("@ZoneId", num); OleDbParameter parameter2 = new OleDbParameter("@ZoneName", reader.GetString("ZoneName")); OleDbParameter parameter3 = new OleDbParameter("@ZoneJSName", reader.GetString("ZoneJSName")); OleDbParameter parameter4 = new OleDbParameter("@ZoneIntro", reader.GetString("ZoneIntro")); OleDbParameter parameter5 = new OleDbParameter("@ZoneType", reader.GetInt32("ZoneType")); OleDbParameter parameter6 = new OleDbParameter("@DefaultSetting", reader.GetBoolean("DefaultSetting")); OleDbParameter parameter7 = new OleDbParameter("@ZoneSetting", reader.GetString("ZoneSetting")); OleDbParameter parameter8 = new OleDbParameter("@ZoneWidth", reader.GetInt32("ZoneWidth")); OleDbParameter parameter9 = new OleDbParameter("@ZoneHeight", reader.GetInt32("ZoneHeight")); OleDbParameter parameter10 = new OleDbParameter("@ShowType", reader.GetInt32("ShowType")); OleDbParameter parameter11 = new OleDbParameter("@Active", reader.GetBoolean("Active")); command4.Parameters.Add(parameter); command4.Parameters.Add(parameter2); command4.Parameters.Add(parameter3); command4.Parameters.Add(parameter4); command4.Parameters.Add(parameter5); command4.Parameters.Add(parameter6); command4.Parameters.Add(parameter7); command4.Parameters.Add(parameter8); command4.Parameters.Add(parameter9); command4.Parameters.Add(parameter10); command4.Parameters.Add(parameter11); connection.Open(); command4.ExecuteNonQuery(); continue; } catch { return(false); } finally { command4.Clone(); connection.Close(); } } return(true); } }
protected void Button1_Click(object sender, EventArgs e) { string name = TextBox1.Text; string id = TextBox2.Text; string pwd = Password.Text; string type = DropDownList1.SelectedItem.Value; if (id == "" || name == "" || pwd == "" || (pwd != TextBox3.Text)) { return; } OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/db.mdb")); conn.Open(); //打开数据库 OleDbCommand cmd = new OleDbCommand(); string sql = "select count(*) from [user] where Uno = '" + id + "'"; cmd.CommandText = sql; cmd.Connection = conn; int count = (int)cmd.ExecuteScalar(); if (count == 0) { //可插入新用户 sql = "insert into [user](Uno, Upwd, Uname, Utype, Nborrow, Total, Pay, Credit) values('" + id + "','" + pwd + "','" + name + "','" + type + "',0,0,0,100)"; cmd.CommandText = sql; cmd.Connection = conn; bool dd = true;//标识 try { cmd.ExecuteNonQuery(); } catch (Exception ex) { dd = false; Response.Write(ex); } finally { cmd.Dispose(); conn.Close(); conn.Dispose(); } if (dd) { //成功 Session["id"] = id;//会注册完自动登录 Response.Redirect("login.aspx"); } else { //pass } } else { Lable1.Text = "用户已存在,请重新注册!"; } cmd.Clone(); conn.Close(); }