// 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();
        }

        /// <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);
            }
        }

        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 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 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();
                        }
                    }
                }
            }
        }

        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 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;
        }

    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(); ;
    }

        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);
            }
        }

        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 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;
        }

 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 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=" + Properties.Settings.Default.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();
     }
 }

        /// <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;
        }

        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;
        }

        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;
        }

        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);
            }
        }

 /// <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 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);
            }
        }

        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);
            }

        }