private void cartelera_Load(object sender, EventArgs e)
{
//frmCiudad ciudad = new frmCiudad();
String numeroSucursal = lblsucursal.Text;
string query = string.Format("select TRHORA.idSucursal,MAPELI.iidpelicula, MAPELI.bimagen FROM TRHORARIO TRHORA, MAPELICULA MAPELI WHERE TRHORA.iidpelicula=MAPELI.iidpelicula and TRHORA.idSucursal = '"+ numeroSucursal +"'");
MySqlCommand comando = new MySqlCommand(query, classCrearConexion.Conexion());
MySqlDataAdapter da = new MySqlDataAdapter(comando);
DataSet ds = new DataSet("MAPELICULA");
da.Fill(ds, "MAPELICULA");
byte[] datos = new byte[0];
DataRow dr = ds.Tables["MAPELICULA"].Rows[0];
datos = (byte[])dr["bimagen"];
System.IO.MemoryStream ms = new System.IO.MemoryStream(datos);
pb1.Image = System.Drawing.Bitmap.FromStream(ms);
/*pb1.Image = Properties.Resources.El_Destino_de_Júpite;
pb1.Refresh();
pb2.Image = Properties.Resources.FF2;
pb2.Refresh();
pb3.Image = Properties.Resources.In_to_the_Woods_En_el_Bosqu;
pb3.Refresh();
pb4.Image = Properties.Resources.Ombis_Alien_Invasion__2015_;
pb4.Refresh();
pb5.Image = Properties.Resources.peliculas_2015_15_e1422754101215;
pb5.Refresh();
pb6.Image = Properties.Resources.peliculas_2015_rapido_furioso_4;
pb6.Refresh();
pb7.Image = Properties.Resources.images;
pb7.Refresh();*/
}
public static object database_conn(string sqlstring)
{
conn = new MySqlConnection();
conn.ConnectionString = "server=" + Properties.Settings.Default.host + "; port=" + Properties.Settings.Default.port + "; user id=" + Properties.Settings.Default.username + "; password="******"; database=" + Properties.Settings.Default.database;
try
{
conn.Open();
MySqlCommand sql = new MySqlCommand(sqlstring, conn);
DataSet ds = new DataSet();
MySqlDataAdapter DataAdapter = new MySqlDataAdapter();
DataAdapter.SelectCommand = sql;
DataAdapter.Fill(ds, "table1");
return ds;
}
catch (MySqlException myerror)
{
MessageBox.Show("Error Connecting to Database: " + myerror.Message, "Database Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return "";
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void getData(string query)
{
this.dt = new DataTable();
try
{
string constr = ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString;
MySqlConnection con = new MySqlConnection();
con.ConnectionString = constr;
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
if (query.Equals("getStaffDataWithDailyHours"))
{
System.DateTime currentDate = System.DateTime.Now;
cmd.Parameters.AddWithValue("@currentDate", System.Convert.ToDateTime(currentDate).ToString("yyyy-MM-dd"));
}
sda.Fill(dt);
cmd.ExecuteNonQuery();
con.Close();
}
catch
{
System.Diagnostics.Debug.WriteLine("fail !");
}
}
public DataSet getData(string PONO, int ID)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT PONO,ID,COSTINGNO,COSTINGID,COSTINGDESC,MATERIALTYPEID,ITEMSID,DESCRIPTION,QUANTITY,UOM,PRICE,QTYPURCHASE,UOMPURCHASE,PRICEPURCHASE,TOTAL,CURRENCYID,KURS,DISCOUNT,PPN,CONVER" +
" FROM tblpurchasedetail WHERE PONO=@PONO AND ID=@ID";
strQuery.Parameters.AddWithValue("@PONO", PONO);
strQuery.Parameters.AddWithValue("@ID", ID);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblpurchasedetail");
Conn.Putus();
}
catch
{
}
return(ds);
}
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1)
{
MySqlCommand SqlInsertCommand1;
MySqlCommand SqlUpdateCommand1;
MySqlCommand SqlDeleteCommand1;
MySqlDataAdapter SqlDataAdapter1 = new MySqlDataAdapter();
SqlInsertCommand1 = new MySqlCommand("AlicuotasIva_Insertar", SqlConnection1);
SqlUpdateCommand1 = new MySqlCommand("AlicuotasIva_Actualizar", SqlConnection1);
SqlDeleteCommand1 = new MySqlCommand("AlicuotasIva_Borrar", SqlConnection1);
SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;
// IMPLEMENTACIÓN DE LA ORDEN INSERT
SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int16, 2, "IdAlicuotaALI");
SqlInsertCommand1.Parameters.Add("p_porcentaje", MySqlDbType.Decimal, 12, "PorcentajeALI");
SqlInsertCommand1.CommandType = CommandType.StoredProcedure;
SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int16, 2, "IdAlicuotaALI");
SqlUpdateCommand1.Parameters.Add("p_porcentaje", MySqlDbType.Decimal, 12, "PorcentajeALI");
SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN DELETE
SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 2, "IdAlicuotaALI");
SqlDeleteCommand1.CommandType = CommandType.StoredProcedure;
return SqlDataAdapter1;
}
public void fill_dg10()
{
try
{
_connection.Open();
_mySqlCommand.CommandText = @"SELECT
sensors.Id_Sensor AS 'ID',
sensors.NameSensor AS 'Название датчика'
FROM
sensors
WHERE sensors.active = 1
";
_mySqlCommand.Connection = _connection.MySqlConnection;
var dataAdapter = new MySqlDataAdapter(_mySqlCommand.CommandText, _connection.MySqlConnection);
var dset = new DataSet();
dataAdapter.Fill(dset);
dg10.ItemsSource = dset.Tables[0].DefaultView;
_connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public static DataTable showAllStudent(out string error)
{
try
{
using (MySqlConnection connection = new ConnectionManager().GetDatabaseConnection())
{
using (MySqlCommand command = new MySqlCommand("sp_ShowAllStudent", connection))
{
command.CommandType = CommandType.StoredProcedure;
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet dSet = new DataSet();
connection.Open();
adapter.SelectCommand = command;
adapter.Fill(dSet);
connection.Close();
DataTable dt = dSet.Tables[0];
error = null;
return dt;
}
}
}
catch (Exception ex)
{
error = ex.Message;
return null;
}
}
private void fillgridDef()
{
id = -1;
buttonDel.Visible = false;
buttonEdit.Visible = false;
buttonIns.Visible = false;
buttonCancel.Visible = false;
DBConnect NewcConnection = new DBConnect();
NewcConnection.dbConnection();
MySqlCommand querysql = new MySqlCommand("Select * From deficiencias", DBConnect.db);
try
{
MySqlDataAdapter dados = new MySqlDataAdapter();
dados.SelectCommand = querysql;
DataTable tabela = new DataTable();
dados.Fill(tabela);
BindingSource fonte = new BindingSource();
fonte.DataSource = tabela;
dataGridView1.DataSource = fonte;
this.dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Deficiencia";
dados.Update(tabela);
dataGridView1.AutoResizeColumns();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
DBConnect.db.Close();
textBoxDef.Clear();
}
private void WypelnijGridView()
{
txtDruzyna.Text = "";
txtImie.Text = "";
txtNazwisko.Text = "";
txtData.Text = "";
txtPozycja.Text = "";
txtWaga.Text = "";
txtWzrost.Text = "";
txtNumer.Text = "";
string constr = ConfigurationManager.ConnectionStrings["pol"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM pilkarze"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
internal static DataTable ExecuteTable(string sql, string connStr)
{
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
try
{
MySqlConnection conn = GetConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 240;
adapter.Fill(dt);
adapter.Dispose();
cmd.Dispose();
conn.Close();
}
catch (Exception ex)
{
dt = new DataTable();
}
return dt;
}
/// <summary>
/// Initializes a new instance of the <see cref="ManagerGeneralItemStagesGui"/> class.
/// </summary>
/// <param name="itemid">The itemid.</param>
public ManagerGeneralItemStagesGui(string itemid)
{
//Login.close = 1;
InitializeComponent();
this.WindowStartupLocation = WindowStartupLocation.CenterScreen;
this.itemID = itemid;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = "select itemName from item where itemid='" + itemID + "'";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
itemName = dr.GetString(0);
}
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
type_comboBox.Items.Add("רישום");
type_comboBox.Items.Add("בעבודה");
type_comboBox.Items.Add("תיקון");
type_comboBox.Items.Add("פסול");
type_comboBox.Items.Add("גמר ייצור");
type_comboBox.Items.Add("הסתיים");
type_comboBox.SelectedIndex = 0;
itemidlabel.Content = itemID;
itemnamelabel.Content = itemName;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT itemStageOrder as `מספר שלב`,stageName as `שם שלב` ,stage_discription as `תאור השלב` FROM item WHERE itemid='" + itemID + "' and itemStatus='רישום' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected void Dodaj(object sender, EventArgs e)
{
string DruzynaID = txtDruzyna.Text;
string Imie = txtImie.Text;
string Nazwisko = txtNazwisko.Text;
string Data = txtData.Text;
string Pozycja = txtPozycja.Text;
string Waga = txtWaga.Text;
string Wzrost = txtWzrost.Text;
string Numer = txtNumer.Text;
string sDate = String.Format("{0:yyyy-mm-dd}", Data);
MySqlConnection polaczenie = new MySqlConnection(url);
MySqlCommand cmd = new MySqlCommand("INSERT INTO pilkarze (ID_Druzyny, Imie, Nazwisko, Data_urodz, Pozycja, Waga, Wzrost, Nr_Kosz) VALUES (@IdDruzyny, @imie, @nazwisko, @data, @pozycja, @waga, @wzrost, @numer)", polaczenie);
MySqlDataAdapter sda = new MySqlDataAdapter();
cmd.Parameters.AddWithValue("@IdDruzyny", DruzynaID);
cmd.Parameters.AddWithValue("@imie", Imie);
cmd.Parameters.AddWithValue("@nazwisko", Nazwisko);
cmd.Parameters.AddWithValue("@data", sDate);
cmd.Parameters.AddWithValue("@pozycja", Pozycja);
cmd.Parameters.AddWithValue("@waga", Waga);
cmd.Parameters.AddWithValue("@wzrost", Wzrost);
cmd.Parameters.AddWithValue("@numer", Numer);
polaczenie.Open();
cmd.ExecuteNonQuery();
polaczenie.Close();
this.WypelnijGridView();
}
public DataTable GetDataTable(
ref MySql.Data.MySqlClient.MySqlConnection _SqlConnection,
string _SQL)
{
// Pass the connection to a command object
MySql.Data.MySqlClient.MySqlCommand _SqlCommand =
new MySql.Data.MySqlClient.MySqlCommand(_SQL, _SqlConnection);
MySql.Data.MySqlClient.MySqlDataAdapter _SqlDataAdapter
= new MySql.Data.MySqlClient.MySqlDataAdapter();
_SqlDataAdapter.SelectCommand = _SqlCommand;
DataTable _DataTable = new DataTable();
_DataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
// Adds or refreshes rows in the DataSet to match those in the data source
try
{
_SqlDataAdapter.Fill(_DataTable);
}
catch (Exception _Exception)
{
// Error occurred while trying to execute reader
// send error message to console (change below line to customize error handling)
// Console.WriteLine(_Exception.Message);
//MessageBox(_Exception);
return(null);
}
return(_DataTable);
}
public int ReadProfile(string username, string password)
{
Connection conn = new Connection();
if (ConnectionState.Closed == conn.frs_con.State)
{
conn.frs_con.Open();
}
// TextBox username = Application.OpenForms["Login"].Controls["Username"] as TextBox;
// TextBox password = Application.OpenForms["Login"].Controls["Password"] as TextBox;
string log_query = "Select * from frs_login where username='******' and password='******'";
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(log_query, conn.frs_con);
try
{
MySqlDataAdapter MyAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataTable table = new DataTable();
MyAdapter.Fill(table);
int count = table.Rows.Count;
return(count);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">sql语句(select)</param>
/// <returns>DataTable</returns>
public static DataTable GetQuery(string sql)
{
// 打开
OpenConnecion();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = ConnectionPool_mysql.con;
cmd.CommandTimeout = cmd.Connection.ConnectionTimeout;
if (m_isTransaction == true)
{
cmd.Transaction = ConnectionPool_mysql.trans;
}
DataSet ds = new DataSet();
DataTable dt = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
da.Fill(ds, "tmp");
dt = ds.Tables["tmp"];
if (!m_isTransaction)
{
CloseConnection();
}
return(dt);
}
public EditAlat(Alat alat)
{
InitializeComponent();
this.alat = alat;
status = new List<KV>();
status.Add(new KV("Baik", 1));
status.Add(new KV("Rusak", 0));
comboBox_Status.ItemsSource = status;
comboBox_Status.DisplayMemberPath = "Key";
comboBox_Status.SelectedValuePath = "Value";
comboBox_Status.SelectedValue = (alat.KondisiAlat) ? 1 : 0;
textbox_Laboratorium.Text = alat.Lokasi;
string query = "SELECT * FROM master_inventory_type";
using (MySqlCommand cmd = new MySqlCommand(query, db.ConnectionManager.Connection)) {
try {
DataTable dataSet = new DataTable();
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd)) {
dataAdapter.Fill(dataSet);
comboBox_Jenis_Barang.ItemsSource = dataSet.DefaultView;
comboBox_Jenis_Barang.DisplayMemberPath = dataSet.Columns["nama"].ToString();
comboBox_Jenis_Barang.SelectedValuePath = dataSet.Columns["id"].ToString();
comboBox_Jenis_Barang.SelectedValue = alat.IdJenis;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex) {
MessageBox.Show(ex.Message);
}
}
}
public void process(ServiceRequest request, ServiceResponse response)
{
List<Category> list = new List<Category>();
string sqlStr = "select * from category";
MySqlConnection conn = ConnectionManager.getInstance().getConnection();
conn.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(sqlStr, conn);
DataSet ds = new DataSet();
mda.Fill(ds,"table1");
conn.Close();
int count = ds.Tables["table1"].Rows.Count;
for (int i = 0; i < count; i++)
{
Category c = new Category();
c.categoryId = (int)ds.Tables["table1"].Rows[i][0];
c.categoryName = (string)ds.Tables["table1"].Rows[i][1];
list.Add(c);
}
GetCategoryResponse serviceResponse = new GetCategoryResponse();
serviceResponse.categories = list;
response.responseObj = serviceResponse;
response.returnCode = 0;
}
public void fill_dg1()
{
try
{
_connection.Open();
_mySqlCommand.CommandText = @"SELECT
operators.Id_Operator AS 'ID',
operators.Surname AS 'ФИО',
operators.LevelMD AS 'Уровень MD',
operators.LevelUSD AS 'Уровень USD'
FROM
operators
WHERE operators.active = 1
";
_mySqlCommand.Connection = _connection.MySqlConnection;
var dataAdapter = new MySqlDataAdapter(_mySqlCommand.CommandText, _connection.MySqlConnection);
var dset = new DataSet();
dataAdapter.Fill(dset);
dg1.ItemsSource = dset.Tables[0].DefaultView;
_connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
button5.Enabled = true;
selectedItem = listBox1.SelectedItem.ToString();
querry = "SELECT * FROM " + listBox1.SelectedItem.ToString();
connection = new MySqlConnection(connectionString);
dataAdapter = new MySqlDataAdapter(querry, connectionString);
dt = new DataTable();
dataAdapter.Fill(dt);
if (dataGridView1.DataSource == null)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
}
dataGridView1.DataSource = dt;
}
catch (NullReferenceException ex)
{
MessageBox.Show("Please select one of the databases in the list.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show("An error occured!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
protected void DeleteData()
{
if (con.State == ConnectionState.Closed) con.Open();
this.cmd = new MySqlCommand("Select * from Packagephotos where PackageKey='" + PackageKey + "' And PackagePhotoKey='" + PackagePhotoKey + "' ", this.con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
string ImageName = dt.Rows[0]["ImageName"].ToString();
try
{
if (File.Exists(Server.MapPath("../Packages/" + ImageName)) == true)
{
File.Delete(Server.MapPath("../Packages/" + ImageName));
}
}
catch (Exception ex)
{
}
}
this.cmd = new MySqlCommand("Delete from Packagephotos where PackagePhotoKey='" + PackagePhotoKey + "' ", this.con);
this.cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open) con.Close(); ;
}
public Decimal GET_AMOUNT(Decimal _basic_salary)
{
decimal d = 0;
MySqlCommand cmd = new MySqlCommand();
db.SET_COMMAND_PARAMS(cmd, "BENEFIT_SELECT_AMOUNT_BYID");
cmd.Parameters.AddWithValue("_code", code);
cmd.Parameters.AddWithValue("_basic_salary", _basic_salary);
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
DataRow r = dt.Rows[0];
d = Convert.ToDecimal(r["amount"].ToString());
return d;
}
else
{
return 0;
}
}
else {
return 0;
}
}
private void btn_ver_Click(object sender, EventArgs e)
{
if (cbExport.SelectedItem == "articulos")
{
MySqlCommand cmdDataBase = new MySqlCommand("select * from articulos ;", bd.cnn);
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = cmdDataBase;
DataTable dbdataset = new DataTable();
sda.Fill(dbdataset);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbdataset;
dataGridView1.DataSource = bSource;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Seleccione el Item", "", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1)
{
MySqlCommand SqlInsertCommand1;
MySqlCommand SqlUpdateCommand1;
MySqlCommand SqlDeleteCommand1;
MySqlDataAdapter SqlDataAdapter1 = new MySqlDataAdapter();
SqlInsertCommand1 = new MySqlCommand("Locales_Insertar", SqlConnection1);
SqlUpdateCommand1 = new MySqlCommand("Locales_Actualizar", SqlConnection1);
SqlDeleteCommand1 = new MySqlCommand("Locales_Borrar", SqlConnection1);
SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;
// IMPLEMENTACIÓN DE LA ORDEN UPDATE
SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdLocalLOC");
SqlUpdateCommand1.Parameters.Add("p_nombre", MySqlDbType.VarChar, 50, "NombreLOC");
SqlUpdateCommand1.Parameters.Add("p_direccion", MySqlDbType.VarChar, 50, "DireccionLOC");
SqlUpdateCommand1.Parameters.Add("p_telefono", MySqlDbType.VarChar, 50, "TelefonoLOC");
SqlUpdateCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebLOC");
SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN INSERT
SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdLocalLOC");
SqlInsertCommand1.Parameters.Add("p_nombre", MySqlDbType.VarChar, 50, "NombreLOC");
SqlInsertCommand1.Parameters.Add("p_direccion", MySqlDbType.VarChar, 50, "DireccionLOC");
SqlInsertCommand1.Parameters.Add("p_telefono", MySqlDbType.VarChar, 50, "TelefonoLOC");
SqlInsertCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebLOC");
SqlInsertCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN DELETE
SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdLocalLOC");
SqlDeleteCommand1.CommandType = CommandType.StoredProcedure;
return SqlDataAdapter1;
}
/// <summary>
/// Method in charge of all queries to database
/// </summary>
/// <param name="sql"></param>
/// <returns>DataTable</returns>
public DataTable query(string sql)
{
da = new MySqlDataAdapter(sql, cn);
dt = new DataTable();
da.Fill(dt);
return dt;
}
public String getUniqueName()
{
String unique_name;
unique_name = "-1";
MySqlConnection con = new MySqlConnection();
con.ConnectionString = MySQLDatabase.getConnectionString();
DataTable dt = new DataTable();
MySqlDataAdapter adpt = new MySqlDataAdapter("SELECT name FROM scientific_names where used = 0", con);
adpt.Fill(dt);
if (dt.Rows.Count != 0)
{
unique_name = dt.Rows[0]["name"].ToString();
con.Close();
con = new MySqlConnection();
con.ConnectionString = MySQLDatabase.getConnectionString();
con.Open();
String commandText =" UPDATE scientific_names SET used = 1 WHERE ( name = '" + unique_name + "')";
MySqlCommand comm = new MySqlCommand(commandText, con);
comm.ExecuteNonQuery();
con.Close();
}
return unique_name;
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
DataSet result = null;
using (MySqlConnection conn = GetConnection)
{
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
result = new DataSet();
adapter.Fill(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
public DataSet getData(string COSTINGNO)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
strQuery.CommandText = "SELECT COSTINGNO,ID,COSTID,DESCRIPTION,WIDTH,USAG,CONSUMPTION,UOMID," +
"PRICE,AMOUNT,CURRENCY,KURS,ALLOWANCE,APPROVE,APPROVEDATE,APPROVEBY" +
" FROM tblcostingdetail WHERE COSTINGNO=@COSTINGNO";
strQuery.Parameters.AddWithValue("@COSTINGNO", COSTINGNO);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblcostingdetail");
Conn.Putus();
}
catch
{
}
return(ds);
}
public Form4()
{
InitializeComponent();
try
{
MySqlConnection con = new MySqlConnection(ruta);
con.Open();
string consulta = "select * from pc_escritorio";
MySqlDataAdapter da = new MySqlDataAdapter(consulta, con);
//lleno
DataSet ds = new DataSet();
da.Fill(ds, "pc_escritorio");
//datos al datagriew
dataGridView1.DataSource = ds.Tables[0];
con.Close();
}
catch (Exception ee)
{
MessageBox.Show("Se produjo un error debido a : " + ee.ToString());
}
}
// GET: Test
public ActionResult Index()
{
MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=classicmodels; password=root");
con.Open();
MySqlCommand query = new MySqlCommand("SELECT contactFirstName, contactLastName FROM customers", con);
MySqlDataAdapter adp = new MySqlDataAdapter(query);
DataSet ds = new DataSet();
adp.Fill(ds);
List<String> list = ds.Tables[0].AsEnumerable()
.Select(r => r.Field<String>("contactFirstName"))
.ToList();
ViewBag.List = list;
//List<String> test = new List<String>();
//test.Add("een");
//test.Add("twee");
//test.Add("drie");
//ViewBag.List = test;
return View();
}
private string USERID = "root"; // ���[�U��
#endregion Fields
#region Methods
void SelectData()
{
// SQL�R�}���h��쐬
string conCmd =
"server=" + SERVER + ";" +
"database=" + DATABASE + ";" +
"userid=" + USERID + ";" +
"port=" + PORT + ";" +
"password="******"select * from guidlist", con);
}
private void GenerarReporte()
{
// Defino variables y sentencias a ejecutar
string mySqlStatement = "SELECT id_campo, desc_campo, areacampo, areasembrada, unidad, "+
"lineas, plantas, aspersores, mangueras FROM campos";
// Defino el DataSet
dsCampos myDsCampos = new dsCampos();
try
{
// Conexion
MySqlConnection myConexion = new MySqlConnection(Conexion.ConectionString);
// Creo los Data Adapters
MySqlDataAdapter myDACampos = new MySqlDataAdapter(mySqlStatement, myConexion);
// Llenando las tablas de Dataset Tipados
myDACampos.Fill(myDsCampos, "dtCampos");
// Generamos el Reporte
rptCampos informe = new rptCampos();
informe.SetDataSource(myDsCampos);
crViewer.ReportSource = informe;
}
catch (Exception myEx)
{
MessageBox.Show(myEx.Message);
}
}
// display all posted topics under the selected forum
protected void Load_Topics()
{
try
{
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM forum_discussions WHERE forum_name = " + "'" + txtForumName.Text + "'";
//cmd.CommandText = "SELECT * FROM forum_discussions where surname like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or firstname like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or customer_id like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or group_status like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or email like " + "'" + txtSearch.Text + "%' and group_name in ('clients') or institution like " + "'" + txtSearch.Text + "%' and group_name in ('clients') ";
adap = new MySqlDataAdapter(cmd);
ds1 = new DataSet();
adap.Fill(ds1, "forum");
grdTopics.DataSource = ds1.Tables[0];
grdTopics.DataBind();
lblError.Visible = false;
}
catch (Exception err)
{
lblError.Visible = true;
lblError.Text = "Error: " + err.Message;
}
con.Close();
}
public void efetuarLogin()
{
MySqlConnection con = new MySqlConnection(Form1.conexao);
con.Open();
MySqlCommand comando = new MySqlCommand("Select id,fcNomeAcesso,fcSenha,fcLoja from ubfuncionario where fcNomeAcesso =?Nome and fcSenha =?Senha and fcLoja=?Loja");
comando.Connection = con;
MySqlParameter Parametros = new MySqlParameter();
comando.Parameters.AddWithValue("?Senha", txtSenha.Text);
comando.Parameters.AddWithValue("?Loja", cbLojas.SelectedValue);
comando.Parameters.AddWithValue("?Nome", txtUsuario.Text);
MySqlDataAdapter daLogin = new MySqlDataAdapter(comando);
DataTable dtLogin = new DataTable();
daLogin.Fill(dtLogin);
try
{
idLoja = Int32.Parse(dtLogin.Rows[0][3].ToString());
idUsuario = Int32.Parse(dtLogin.Rows[0][0].ToString());
lblStatus.Text = "LOGIN EFETUADO!";
lblStatus.ForeColor = System.Drawing.Color.Green;
for (int i = 231; i <= 483; i += 50)
{
this.Width = i;
}
}
catch
{
lblStatus.Text ="USUÁRIO INEXISTENTE!";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed) con.Open();
DataTable dt = new DataTable();
if (ddlGuestType.Text == "De-Activate")
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels Where Status='De-Activate' ", con);
}
else if (ddlGuestType.Text == "Activate")
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels Where Status='Activate' ", con);
}
else
{
cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
" FROM Hotels ", con);
}
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
ExportToExcel(dt, "Hotels", ddlGuestType.Text + " Hotel" );
if (con.State == ConnectionState.Open) con.Close();
}
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1)
{
MySqlCommand SqlInsertCommand1;
MySqlCommand SqlUpdateCommand1;
MySqlCommand SqlDeleteCommand1;
MySqlDataAdapter SqlDataAdapter1 = new MySqlDataAdapter();
SqlInsertCommand1 = new MySqlCommand("Generos_Insertar", SqlConnection1);
SqlUpdateCommand1 = new MySqlCommand("Generos_Actualizar", SqlConnection1);
SqlDeleteCommand1 = new MySqlCommand("Generos_Borrar", SqlConnection1);
SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;
// IMPLEMENTACIÓN DE LA ORDEN UPDATE
SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlUpdateCommand1.Parameters.Add("p_descripcion", MySqlDbType.VarChar, 50, "DescripcionGEN");
SqlUpdateCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebGEN");
SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN INSERT
SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlInsertCommand1.Parameters.Add("p_descripcion", MySqlDbType.VarChar, 50, "DescripcionGEN");
SqlInsertCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebGEN");
SqlInsertCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN DELETE
SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlDeleteCommand1.CommandType = CommandType.StoredProcedure;
return SqlDataAdapter1;
}
private void recommend_songlist_Click(object sender, RoutedEventArgs e)
{
try
{
using (var sshClient = new SshClient("192.168.199.201", "hadoop", "hadoop"))
{
sshClient.Connect();
string command = "python /home/hadoop/PycharmProjects/untitled/recommend.py " + xmid.Text;
using (var cmd = sshClient.CreateCommand(command))
{
var res = cmd.Execute();
if (res == "107\n")
{
string rec_sql = "SELECT songname, singer, link FROM `" + xmid.Text + "_rec`";
con2 = null;
if (con2 == null)
{
con2 = new MySql.Data.MySqlClient.MySqlConnection(sqlstr);
con2.Open();
}
adapter2 = null;
if (adapter2 == null)
{
adapter2 = new MySql.Data.MySqlClient.MySqlDataAdapter(rec_sql, con2);
}
ds2 = null;
if (ds2 == null)
{
ds2 = new System.Data.DataSet();
}
ds2.Clear();
adapter2.Fill(ds2, "rec");
dt2 = null;
if (dt2 == null)
{
dt2 = ds2.Tables["rec"];
}
Music_Recommend.ItemsSource = dt2.DefaultView;
if (dt2.DefaultView.Count == 0)
{
MessageBox.Show("无推荐结果哦，试试今日推荐的音乐吧！");
todayRecomend();
}
con2.Close();
}
else
{
MessageBox.Show("无推荐结果哦，试试今日推荐的音乐吧！");
todayRecomend();
}
}
}
}
catch
{
MessageBox.Show("无推荐结果哦，试试今日推荐的音乐吧！");
todayRecomend();
}
}
private List <FingerprintAcoustID> GetFingerprintsMySQL(int[] fingerIDList)
{
DateTime startTime = DateTime.Now;
System.Collections.Concurrent.ConcurrentBag <FingerprintAcoustID> fingerBag = new System.Collections.Concurrent.ConcurrentBag <FingerprintAcoustID>();
using (MySql.Data.MySqlClient.MySqlConnection conn = CDR.DB_Helper.NewMySQLConnection())
{
StringBuilder sb = new StringBuilder(1024);
sb.Append("SELECT *\r\n");
sb.Append("FROM FINGERID AS T1,\r\n");
sb.Append(" TITELNUMMERTRACK_ID AS T2\r\n");
sb.Append("WHERE T1.TITELNUMMERTRACK_ID = T2.TITELNUMMERTRACK_ID\r\n");
sb.Append("AND T2.TITELNUMMERTRACK_ID IN (\r\n");
int count = 0;
System.Collections.Hashtable hTable = new System.Collections.Hashtable(fingerIDList.Length);
foreach (int id in fingerIDList)
{
if (count > 0)
{
sb.Append(',');
}
sb.Append(id.ToString());
hTable.Add(id, count);
count++;
}
sb.Append(')');
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sb.ToString(), conn);
command.CommandTimeout = 60;
MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(command);
System.Data.DataSet ds = new System.Data.DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
FingerprintAcoustID fs = new FingerprintAcoustID();
fs.Reference = row["TITELNUMMERTRACK"].ToString();
fs.Signature = (byte[])row["SIGNATURE"];
fs.DurationInMS = Convert.ToInt64(row["DURATIONINMS"]);
int titelnummertrackID = Convert.ToInt32(row["TITELNUMMERTRACK_ID"]);
fs.Tag = hTable[titelnummertrackID];
fingerBag.Add(fs);
}
}
}
List <FingerprintAcoustID> result = fingerBag.OrderBy(e => (int)e.Tag)
.ToList();
return(result);
}
private void LoadReceipt(string No)
{
reportViewer1.LocalReport.DataSources.Clear();
Orders v = new Orders();
Customer c = new Customer();
Company y = new Company();
v = Orders.SelectNo(No);
y = Company.Select();
/**Company Customer***/
Microsoft.Reporting.WinForms.ReportParameter rp = new Microsoft.Reporting.WinForms.ReportParameter("image", Helper.CompanyImage);
this.reportViewer1.LocalReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter[] { rp });
MySQL.Close();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM orders WHERE no='" + No + "'", MySQL.Conn);
DataSet ds = new DataSet();
da.Fill(ds);
MySql.Data.MySqlClient.MySqlDataAdapter da2 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM customer WHERE id='" + v.CustomerID + "'", MySQL.Conn);
DataSet ds2 = new DataSet();
da2.Fill(ds2);
MySql.Data.MySqlClient.MySqlDataAdapter da3 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT product.name AS itemID ,product.code AS self,product.description AS tax,casetransaction.cost,casetransaction.date as date,casetransaction.no as no,casetransaction.total as total,casetransaction.qty as qty,casetransaction.cost,casetransaction.created,casetransaction.sync,casetransaction.height,casetransaction.limits,casetransaction.weight,casetransaction.setting,casetransaction.instruction,casetransaction.period FROM casetransaction LEFT join product ON casetransaction.itemID = product.id WHERE casetransaction.no='" + No + "'", MySQL.Conn);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
MySql.Data.MySqlClient.MySqlDataAdapter da4 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM coverage WHERE customerID='" + v.CustomerID + "'", MySQL.Conn);
DataSet ds4 = new DataSet();
da4.Fill(ds4);
MySql.Data.MySqlClient.MySqlDataAdapter da5 = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM practitioner WHERE id='" + v.PractitionerID + "'", MySQL.Conn);
DataSet ds5 = new DataSet();
da5.Fill(ds5);
ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
ReportDataSource datasource2 = new ReportDataSource("DataSet2", ds2.Tables[0]);
ReportDataSource datasource3 = new ReportDataSource("DataSet3", ds3.Tables[0]);
ReportDataSource datasource4 = new ReportDataSource("DataSet4", ds4.Tables[0]);
ReportDataSource datasource5 = new ReportDataSource("DataSet5", ds5.Tables[0]);
reportViewer1.LocalReport.DataSources.Add(datasource);
reportViewer1.LocalReport.DataSources.Add(datasource2);
reportViewer1.LocalReport.DataSources.Add(datasource3);
reportViewer1.LocalReport.DataSources.Add(datasource4);
reportViewer1.LocalReport.DataSources.Add(datasource5);
reportViewer1.RefreshReport();
}
public override string LastTimeId()
{
string qry = $"select * from `{TimeTableName}`";
DataTable vt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(qry, _connection);
da.Fill(vt);
return(vt.Rows.Count > 0 ? vt.Rows[vt.Rows.Count - 1]["Key"].ToString() : "0");
}
//Finalmente, es el turno de definir CrearDataAdapter, el cual aprovecha el método Comando para crear el comando necesario.
protected override System.Data.IDataAdapter CrearDataAdapter(string procedimientoAlmacenado, params Object[] args)
{
var da = new MySql.Data.MySqlClient.MySqlDataAdapter((MySql.Data.MySqlClient.MySqlCommand)Comando(procedimientoAlmacenado));
if (args.Length != 0)
{
CargarParametros(da.SelectCommand, args);
}
return(da);
} // end CrearDataAdapter
DataTable getTable(string Database, string Tablename, string[] spalten, bool query, string filter)
{
MySqlConnection conn;
string connString = "Server=" + server + ";Database=" + dbName + ";Uid=" + user + ";Pwd=" + password + ";ConvertZeroDateTime=true";
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
string sql = null;
if (query)
{
sql = spalten[0];
}
else
{
sql = "SELECT ";
for (int i = 0; i < (spalten.Length - 1); i++)
{
sql += (spalten[i] + ", ");
}
sql += (spalten[spalten.Length - 1]);
sql += (" FROM " + Tablename);
sql += filter;
}
Console.WriteLine("Query: " + sql);
MySqlCommand command = conn.CreateCommand();
command.CommandText = sql;
int result = command.ExecuteNonQuery();
MySqlDataAdapter daDataAdapterMySql = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, conn);
DataTable data = new DataTable();
try
{
daDataAdapterMySql.Fill(data);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
return(data);
}
private void Form1_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection oCon = new MySql.Data.MySqlClient.MySqlConnection("SERVER=localhost; UID=root; DATABASE=ACERVO; SSLMode=none");
MySql.Data.MySqlClient.MySqlDataAdapter oTab = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT DSCCODIGO, DSCNOME FROM DISCOS", oCon);
DataTable oDados = new DataTable();
oCon.Open();
oTab.Fill(oDados);
cmbDisco.DataSource = oDados;
}
public DataSet RetDataSet(List <QueryHelper> list)
{
ds = new DataSet();
foreach (QueryHelper item in list)
{
da = new MySql.Data.MySqlClient.MySqlDataAdapter(item.Sql, comm);
cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da);
da.Fill(ds, item.Table);
}
return(ds);
}
//private static MySqlConnection conn;
static public DataSet Query(string constr, string sql)
{
MySqlConnection conn = new MySqlConnection(constr);
conn.Open();
DataSet reds = new DataSet();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
//cmd.ExecuteReader
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
da.Fill(reds);
return(reds);
}
public void ModificarBd(string Nombreprocedmiento, string PIddiagnostico, string PNombre, string PDescripcion, string TxtIddiagnostico, string TxtNombre, string Txtdescripcion)
{
string CadenaApp;
CadenaApp = Obj.Obtenercadena();// le asigamos la cadena de conexion a la variable CadenaApp
String stm = Nombreprocedmiento;
MySqlConnection conn = new MySqlConnection(CadenaApp);
DataTable odtable = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter odadapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?" + PIddiagnostico, TxtIddiagnostico);
cmd.Parameters["?" + PIddiagnostico].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?" + PNombre, TxtNombre);
cmd.Parameters["?" + PNombre].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?" + PDescripcion, Txtdescripcion);
cmd.Parameters["?" + PDescripcion].Direction = ParameterDirection.Input;
odadapter.SelectCommand = cmd;
cmd.ExecuteNonQuery();
//MessageBox .Show("Usuario Guardado Con Exito!!",MessageBoxIcon.Information);
MessageBox.Show("Los datos se han inserttado exitosamente ", " SISVEUG SOFTWARE ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(" No se pueden guardar los datos \n ERROR: " + ex.Message, "SISVEUG SOFTWARE ", MessageBoxButtons.OK, MessageBoxIcon.Stop);
// MessageBox.Show("No se puedo guardar!! ","ejemplo ",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
/// <summary>
/// 返回记录条数
/// </summary>
/// <param name="sql">sql语句(select)</param>
/// <returns>记录条数</returns>
private static int RowConut(string sql)
{
OpenConnecion();
DataSet ds = new DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter command = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, con);
command.Fill(ds, "ds");
if (!m_isTransaction)
{
CloseConnection();
}
con.Close();
return(ds.Tables.Count);
}
public DataSet prBOM(string COSTINGNO)
{
DataSet ds = null;
try
{
ds = new DataSet();
Connection Conn = new Connection();
Conn.Konek();
strQuery = new MySql.Data.MySqlClient.MySqlCommand();
strQuery.Connection = Conn.Conn;
strQuery.CommandType = CommandType.Text;
/*
* strQuery.CommandText = "SELECT * FROM tblcosting " +
* "INNER JOIN tblcostingdetail ON tblcosting.COSTINGNO=tblcostingdetail.COSTINGNO " +
* "LEFT JOIN tblbom ON tblcosting.COSTINGNO=tblbom.COSTINGNO AND tblcostingdetail.ID=tblbom.ID " +
* " INNER JOIN tblmitems ON tblmitems.ITEMSID=tblcosting.ITEMSID" +
* " LEFT JOIN tblmitems tblmitems1 ON tblmitems1.ITEMSID=tblbom.ITEMSID " +
* " INNER JOIN tblmcategory ON tblmcategory.CATEGORYID=tblcosting.CATEGORYID" +
* " INNER JOIN tblmtype ON tblmtype.TYPEID=tblcosting.TYPEID" +
* " INNER JOIN tblmcustvend ON tblmcustvend.CUSTVENDCODE=tblcosting.CUSTVENDCODE" +
* " INNER JOIN tblmcost ON tblmcost.COSTID=tblcostingdetail.COSTID" +
* " LEFT JOIN tbljoborder ON tblcosting.JONO=tbljoborder.ORDERNO" +
* " WHERE tblcosting.COSTINGNO = @COSTINGNO";
*/
strQuery.CommandText = "SELECT * FROM tblcosting " +
"INNER JOIN tblcostingdetail ON tblcosting.COSTINGNO=tblcostingdetail.COSTINGNO " +
"LEFT JOIN tblbom ON tblcosting.COSTINGNO=tblbom.COSTINGNO AND tblcostingdetail.ID=tblbom.ID " +
" INNER JOIN tblmitems ON tblmitems.ITEMSID=tblcosting.ITEMSID" +
" INNER JOIN tblmitems tblmitems1 ON tblmitems1.ITEMSID=tblbom.ITEMSID " +
" INNER JOIN tblmcategory ON tblmcategory.CATEGORYID=tblcosting.CATEGORYID" +
" INNER JOIN tblmtype ON tblmtype.TYPEID=tblcosting.TYPEID" +
" INNER JOIN tblmcustvend ON tblmcustvend.CUSTVENDCODE=tblcosting.CUSTVENDCODE" +
" INNER JOIN tblmcost ON tblmcost.COSTID=tblcostingdetail.COSTID" +
" LEFT JOIN tbljoborder ON tblcosting.JONO=tbljoborder.ORDERNO" +
" WHERE tblcosting.COSTINGNO = @COSTINGNO";
strQuery.Parameters.AddWithValue("@COSTINGNO", COSTINGNO);
MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(strQuery);
data.Fill(ds, "tblcosting");
Conn.Putus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(ds);
}
private void RefreshGridData()
{
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(
AppConfig.Instance.ConnectionString))
{
dataset.Clear();
MySql.Data.MySqlClient.MySqlDataAdapter adp = new MySql.Data.MySqlClient.MySqlDataAdapter(
"select p.id as id, p.name as name, p.initial_price as tarif_awal, p.extended_price as " +
" tarif_tambahan, p.extended_after as bertambah_setelah, g.group_name from tarifs p, tarifs_groups g where " +
" p.group_id = g.id", conn);
conn.Open();
adp.Fill(dataset, "tarifs");
dataGrid.DataSource = dataset.Tables["tarifs"].DefaultView;
}
}
//
public void insertMeasurement(string p1_meterreading_in_1, string p1_meterreading_in_2, string p1_current_power_in, string p1_current_tariff, string p1_channel_1_meterreading)
{
string Query = "";
Boolean connected = false;
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
try
{
//open the mySQL connection
connected = connect();
if (connected)
{
//INSERT INTO p1_log ('p1_meterreading_in_1', 'p1_meterreading_in_2', 'p1_current_tariff', 'p1_channel_1_meterreading') VALUES ('', '', '', '', '');
string values = " VALUES (" + p1_meterreading_in_1.Replace(",", ".") + ", " + p1_meterreading_in_2.Replace(",", ".") + ", " + p1_current_power_in.Replace(",", ".") + ", " + p1_current_tariff + ", " + p1_channel_1_meterreading.Replace(",", ".") + ");";
//insert the data
Query = "INSERT INTO p1_log (p1_meterreading_in_1, p1_meterreading_in_2, p1_current_power_in, p1_current_tariff, p1_channel_1_meterreading)" + values;
MySqlCommand insertMeasurement = new MySqlCommand(Query, conn);
try
{
insertMeasurement.ExecuteNonQuery();
}
catch (Exception ex)
{
string error = ex.Message.ToString();
func.logItem("mySQL.insertMeasurement(1) : " + error + "\n");
}
}
}
catch (Exception ex)
{
string error = ex.Message.ToString();
func.logItem("mySQL.insertMeasurement : " + error + "\n");
}
finally
{
if (connected)
{
//close the mySQL connection
close();
}
}
}
private void ChoseLayer_Load(object sender, EventArgs e)
{
string strSql;
switch (frmConnection.DbType)
{
case "MySQL 5.0":
strSql = "select featurename from features features";
IDbDataAdapter daMySql = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql, (MySqlConnection)frmConnection.SqlConnection);
DataSet dsMySql = new DataSet();
daMySql.Fill(dsMySql);
DataTable dtMySql = dsMySql.Tables[0];
for (int i = 0; i < dtMySql.Rows.Count; i++)
{
lstAvailbeLayers.Items.Add(dtMySql.Rows[i][0]);
}
break;
//case "Oracle 10g":
// strSql = "select \"featurename\" from OPENHYDRO.\"features\"";
// IDbDataAdapter daOracle = new OracleDataAdapter(strSql, (OracleConnection)frmConnection.SqlConnection);
// DataSet dsOracle = new DataSet();
// daOracle.Fill(dsOracle);
// DataTable dtOracle = dsOracle.Tables[0];
// for (int i = 0; i < dtOracle.Rows.Count; i++)
// {
// lstAvailbeLayers.Items.Add(dtOracle.Rows[i][0]);
// }
// break;
case "PostGIS":
strSql = "select f_table_name from geometry_columns";
IDbDataAdapter daPgsql = new Npgsql.NpgsqlDataAdapter(strSql, (Npgsql.NpgsqlConnection)frmConnection.SqlConnection);
DataSet dsPg = new DataSet();
daPgsql.Fill(dsPg);
DataTable dtPg = dsPg.Tables[0];
for (int i = 0; i < dtPg.Rows.Count; i++)
{
lstAvailbeLayers.Items.Add(dtPg.Rows[i][0]);
}
break;
default:
break;
}
}
private void Sychro_songlist_Click(object sender, RoutedEventArgs e)
{
try
{
// SELECT song_name, singer, link FROM xiami244467287
// 先 python
string[] strArr = new string[1]; //参数列表
string sArguments = @"xiami.py"; //这里是python的文件名字
strArr[0] = xiami_userid.Text; // 244467287
RunPythonScript(sArguments, "-u", strArr);
// 然后 db // 再然后 show
string sychro_sql = "SELECT song_name, singer, link FROM xiami" + xiami_userid.Text;
con1 = null;
if (con1 == null)
{
con1 = new MySql.Data.MySqlClient.MySqlConnection(sqlstr);
con1.Open();
}
adapter1 = null;
if (adapter1 == null)
{
adapter1 = new MySql.Data.MySqlClient.MySqlDataAdapter(sychro_sql, con1);
}
ds1 = null;
if (ds1 == null)
{
ds1 = new System.Data.DataSet();
}
ds1.Clear();
adapter1.Fill(ds1, "xiami");
dt1 = null;
if (dt1 == null)
{
dt1 = ds1.Tables["xiami"];
}
Music_World.ItemsSource = dt1.DefaultView;
con1.Close();
if (dt1.DefaultView.Count == 0)
{
MessageBox.Show("查询无结果，请检查您的输入！");
}
}
catch
{
MessageBox.Show("查询无结果，请检查您的输入！");
}
}
public void ActualizarPiezas(string Nombreprocedmiento, string PCanRestar, string Pidpieza, string TxtCanRestar, string Txtidpieza)
{
string CadenaApp;
CadenaApp = Obj.Obtenercadena();// le asigamos la cadena de conexion a la variable CadenaApp
String stm = Nombreprocedmiento;
MySqlConnection conn = new MySqlConnection(CadenaApp);
DataTable odtable = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter odadapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?" + PCanRestar, TxtCanRestar);
cmd.Parameters["?" + PCanRestar].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?" + Pidpieza, Txtidpieza);
cmd.Parameters["?" + Pidpieza].Direction = ParameterDirection.Input;
odadapter.SelectCommand = cmd;
cmd.ExecuteNonQuery();
//MessageBox .Show("Usuario Guardado Con Exito!!",MessageBoxIcon.Information);
// MessageBox.Show(" pieza actualizada exitosamente ", " SISTEMA VERTICAL ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(" No se pueden guardar los datos \n ERROR: " + ex.Message, "SISVEUG SOFTWARE ", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
public void InsertarBDPiezasXMto(string Nombreprocedmiento, string Pidmantenimiento, string Pidpiezas, string Txtidmantenimiento, string Txtidpiezas)
{
string CadenaApp;
CadenaApp = Obj.Obtenercadena();// le asigamos la cadena de conexion a la variable CadenaApp
String stm = Nombreprocedmiento;
MySqlConnection conn = new MySqlConnection(CadenaApp);
DataTable odtable = new DataTable();
MySql.Data.MySqlClient.MySqlDataAdapter odadapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?" + Pidmantenimiento, Txtidmantenimiento);
cmd.Parameters["?" + Pidmantenimiento].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?" + Pidpiezas, Txtidpiezas);
cmd.Parameters["?" + Pidpiezas].Direction = ParameterDirection.Input;
odadapter.SelectCommand = cmd;
cmd.ExecuteNonQuery();
//MessageBox .Show("Usuario Guardado Con Exito!!",MessageBoxIcon.Information);
// MessageBox.Show(" DATOS GUARDADOS EN TABLA DE MUCHOS A MUCHOS ", " SISVEUG SOFTWARE ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(" No se pueden guardar los datos \n ERROR: " + ex.Message, "SISVEUG SOFTWARE ", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
private void exportQuote_Click(object sender, RoutedEventArgs e)
{
ReportDocument expReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
conn.ConnectionString = Properties.Settings.Default.abab1199_qsyncConnectionString;
try
{
cmd.CommandText = "SELECT * FROM quotes WHERE QuoteNumber = " + qnpass.Text;
cmd.Connection = conn;
myAdapter.SelectCommand = cmd;
myAdapter.Fill(myData);
expReport.Load(reportLocation);
expReport.Database.Tables[0].SetDataSource(myData.Tables[0]);
expReport.SetParameterValue("Quote", qnpass.Text);
expReport.SetParameterValue("Quote_Number", qnpass.Text);
ExportOptions exportOption;
DiskFileDestinationOptions diskFileDestinationOptions = new DiskFileDestinationOptions();
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Pdf Files|*.pdf";
if (sfd.ShowDialog().ToString().Equals("OK"))
{
diskFileDestinationOptions.DiskFileName = sfd.FileName;
}
exportOption = expReport.ExportOptions;
{
exportOption.ExportDestinationType = ExportDestinationType.DiskFile;
exportOption.ExportFormatType = ExportFormatType.PortableDocFormat;
exportOption.DestinationOptions = diskFileDestinationOptions;
exportOption.FormatOptions = new PdfRtfWordFormatOptions();
}
expReport.Export();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message, "Database Connectivity Error!",
MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void emailQuote_Click(object sender, RoutedEventArgs e)
{
// var reportLocation = Properties.Settings.Default.ReportsFolder+"Quote.rpt";
ReportDocument mailReport = new ReportDocument();
{
//Report Data settings for export
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
conn.ConnectionString = Properties.Settings.Default.abab1199_qsyncConnectionString;
try
{
cmd.CommandText = "SELECT * FROM quotes WHERE QuoteNumber = " + qtNbr.Text;
cmd.Connection = conn;
myAdapter.SelectCommand = cmd;
myAdapter.Fill(myData);
mailReport.Load(reportLocation);
mailReport.Database.Tables[0].SetDataSource(myData.Tables[0]);
mailReport.SetParameterValue("Quote", qtNbr.Text);
mailReport.SetParameterValue("Quote_Number", qtNbr.Text);
//Report Exporting settings
ExportOptions CrExportOptions;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = tempLocation;
CrExportOptions = mailReport.ExportOptions;
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
mailReport.Export();
sendmail();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
public DataTable RetDataTable(string sql)
{
data = new DataTable();
try
{
da = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, comm);
cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
da.Fill(data);
}
return(data);
}
public async Task <DataTable> RetDataTableAsync(string sql)
{
var datatable = new DataTable();
using (var con = new MySql.Data.MySqlClient.MySqlConnection(commStr))
{
using (var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, con))
{
await con.OpenAsync();
cmd.CommandType = CommandType.Text;
var sda = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
await sda.FillAsync(datatable);
}
}
return(datatable);
}
//
public void insertMeasurementLIVE(string p1_meterreading_in_1, string p1_meterreading_in_2, string p1_current_power_in, string p1_current_tariff, string p1_channel_1_meterreading)
{
string Query = "";
Boolean connected = false;
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();
try
{
//open the mySQL connection
connected = connect();
if (connected)
{
//update the data
Query = "UPDATE p1_live SET p1_meterreading_in_1=" + p1_meterreading_in_1.Replace(",", ".") + ", p1_meterreading_in_2=" + p1_meterreading_in_2.Replace(",", ".") + ", p1_current_power_in=" + p1_current_power_in + ", p1_current_tariff=" + p1_current_tariff + ", p1_channel_1_meterreading=" + p1_channel_1_meterreading.Replace(",", ".") + " WHERE sample_nr=1";
MySqlCommand insertMeasurement = new MySqlCommand(Query, conn);
try
{
insertMeasurement.ExecuteNonQuery();
}
catch (Exception ex)
{
string error = ex.Message.ToString();
func.logItem("mySQL.insertMeasurementLIVE(1) : " + error + "\n");
}
}
}
catch (Exception ex)
{
string error = ex.Message.ToString();
func.logItem("mySQL.insertMeasurementLIVE : " + error + "\n");
}
finally
{
if (connected)
{
//close the mySQL connection
close();
}
}
}
// GET: Consulta
public ActionResult Consulta_btn()
{
string cs = @"server=localhost;user id=root;password=pass;persistsecurityinfo=True;database=quejas";
var con = new MySql.Data.MySqlClient.MySqlConnection(cs); con.Open();
var stm = "select count(queja.queja) as QUEJA , comercio1.nombre_comercio as COMERCIO , comercio1.idcomercio " +
"from comercio as comercio1 " +
"inner join sucursal on comercio1.idcomercio = sucursal.idcomercio " +
"inner join queja on sucursal.idsucursal = queja.idsucursal " +
"inner join municipio on sucursal.idmunicipio = municipio.idmunicipio " +
"inner join departamento on municipio.iddepartamento = departamento.iddepartamento " +
"inner join region on departamento.idregion = region.idregion " +
"where queja.fecha_queja BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE) and ( " +
"select count(comercio.idcomercio) from comercio " +
"inner join sucursal on comercio.idcomercio = sucursal.idcomercio " +
"inner join queja on sucursal.idsucursal = queja.idsucursal " +
"inner join municipio on sucursal.idmunicipio = municipio.idmunicipio " +
"inner join departamento on municipio.iddepartamento = departamento.iddepartamento " +
"inner join region on departamento.idregion = region.idregion " +
"where queja.fecha_queja BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE) and departamento.idregion = 3 and comercio.idcomercio = comercio1.idcomercio) = 0 " +
"group by comercio1.idcomercio";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(stm, con);
MySql.Data.MySqlClient.MySqlDataAdapter mySQLDataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
DataSet data = new DataSet();
mySQLDataAdapter.Fill(data);
var datos = data.Tables[0].Rows;
List <Consulta> detalles = new List <Consulta>();
foreach (DataRow item in datos)
{
detalles.Add(new Consulta()
{
Queja = item.ItemArray[0].ToString(),
Comercio = item.ItemArray[1].ToString(),
Id_comercio = item.ItemArray[2].ToString()
});
}
return(View(detalles));
}
public DataSet Buscar(string strSQL, string tabla)
{
DataSet ds = new DataSet();
try
{
cnSQL.Open();
MySql.Data.MySqlClient.MySqlDataAdapter daSQL = new MySql.Data.MySqlClient.MySqlDataAdapter(strSQL, cnSQL);
daSQL.Fill(ds, tabla);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cnSQL.Close();
}
return(ds);
}