--- Rolalo --- Navegando en el mundo de los bits

Ejemplo de Altas, Bajas, Consultas y Cambios en CSharp (C#) usando Access (2003). En este ejemplo se dan de alta peliculas, se eliminan, se modifica la información y se pueden hacer busquedas de las mismas.

private void button6_Click(object sender, EventArgs e)
      {
          string typeString;
          try
          {
              typeString = comboBox1.SelectedItem.ToString();
          }
          catch (Exception ex) {
              MessageBox.Show("You must enter movie type\nError: " + ex.Message + "");
              return;
          }
          int type = 0;
          string name = textBox1.Text.ToString();
          string publisher = textBox2.Text.ToString();
          string year = textBox3.Text.ToString();
          int yr = 0;
          if (year != "")
          {
              yr = CheckYear(year);
          }
          string previewed;
          if (radioButton1.Checked == true)
          {
              previewed = "Yes";
          }
          else
          {
              previewed = "No";
          }
          if (yr != 1)
          {
              if (typeString == "Adventure") type = 1;
              if (typeString == "Comedy") type = 2;
              if (typeString == "Action") type = 3;
              if (typeString == "Cartoon") type = 4;
              if (typeString == "Romantic") type = 5;
              if (typeString == "Fantasy") type = 6;
              if (typeString == "Thriller") type = 7;
              if (typeString == "Historic") type = 8;
              if (typeString == "Drama") type = 9;
              if (typeString == "Horor") type = 10;
              if (typeString == "Sci-Fi") type = 11;
              if (typeString == "Crime") type = 12;
              if (typeString == "Biografy") type = 13;
              if (typeString == "Documentary") type = 14;
            
              string SQLString ="";
 
                  if (year == "")
                  {
                      SQLString = "INSERT INTO movie(Title, Publisher, Previewed, typeID) VALUES('" + name.Replace("'", "''") + "','" + publisher + "','" + previewed + "'," + type + ");";
                  }
                  else
                  {
                      MessageBox.Show(yr.ToString());
                      SQLString = "INSERT INTO movie(Title, Publisher, Previewed, MovieYear, typeID) VALUES('" + name.Replace("'", "''") + "','" + publisher + "','" + previewed + "'," + yr + "," + type + ");";
                  }


              OleDbCommand SQLCommand = new OleDbCommand();
              SQLCommand.CommandText = SQLString;
              SQLCommand.Connection = database;
              int response = -1;
              try
              {
                  response = SQLCommand.ExecuteNonQuery();
              }
              catch (Exception ex)
              {
                  MessageBox.Show(ex.Message);
              }
              if (response >= 1) MessageBox.Show("Movie is added to database","Successful",MessageBoxButtons.OK, MessageBoxIcon.Information);
              textBox1.Clear();
              textBox2.Clear();
              textBox3.Clear();
              comboBox1.ResetText();
              radioButton1.Checked = radioButton2.Checked = false;
          }
          else
          {
              MessageBox.Show("The year format is not correct!\nPlease try to pick a valid year.", "Warning",MessageBoxButtons.OK, MessageBoxIcon.Warning);
              textBox3.Clear();
              textBox3.Focus();
          }
      }

      public int CheckYear(string year)
      {
          int yr = int.Parse(year);
          if (yr >= 2100 || yr <= 1900)             {                 return 1;             }             else             {                 return yr;             }         }  

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
       {

           string queryString = "SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID";

           int currentRow = int.Parse(e.RowIndex.ToString());
           try
           {
               string movieIDString = dataGridView1[0, currentRow].Value.ToString();
               movieIDInt = int.Parse(movieIDString);
           }
           catch (Exception ex) { }
           // edit button
           if (dataGridView1.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
           {   string title = dataGridView1[1, currentRow].Value.ToString();
               string publisher = dataGridView1[2, currentRow].Value.ToString();
               string previewed = dataGridView1[3, currentRow].Value.ToString();
               string year = dataGridView1[4, currentRow].Value.ToString();
               string type = dataGridView1[5, currentRow].Value.ToString();
               //runs form 2 for editing   
               Form2 f2 = new Form2();
               f2.title = title;
               f2.publisher = publisher;
               f2.previewed = previewed;
               f2.year = year;
               f2.type  = type;
               f2.movieID = movieIDInt;
               f2.Show();
               dataGridView1.Update();

            
           }
           // delete button
           else if (dataGridView1.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
           {
               // delete sql query
               string queryDeleteString = "DELETE FROM movie where movieID = "+movieIDInt+"";
               OleDbCommand sqlDelete = new OleDbCommand();
               sqlDelete.CommandText = queryDeleteString;
               sqlDelete.Connection = database;
               sqlDelete.ExecuteNonQuery();
               loadDataGrid(queryString);
           }
           
        }

#region search by title
        private void button1_Click(object sender, EventArgs e)
        {
            string title = textBox4.Text.ToString();
            if (title != "")
            {
                string queryString = "SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.title LIKE '" + title + "%'";
                loadDataGrid(queryString);
            }
            else
            {
                MessageBox.Show("You muste enter movie title","Warning",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }
        }
        #endregion

        #region search by type
        private void button5_Click(object sender, EventArgs e)
        {
            int type = 0;
            string typeString = comboBox2.SelectedItem.ToString();
            if (typeString == "Adventure") type = 1;
            if (typeString == "Comedy") type = 2;
            if (typeString == "Action") type = 3;
            if (typeString == "Cartoon") type = 4;
            if (typeString == "Romantic") type = 5;
            if (typeString == "Fantasy") type = 6;
            if (typeString == "Thriller") type = 7;
            if (typeString == "Historic") type = 8;
            if (typeString == "Drama") type = 9;
            if (typeString == "Horor") type = 10;
            if (typeString == "Sci-Fi") type = 11;
            if (typeString == "Crime") type = 12;
            if (typeString == "Biografy") type = 13;
            if (typeString == "Documentary") type = 14;
            string queryString = "SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.typeID = " + type + "";
            loadDataGrid(queryString);
        }
        #endregion

private void button4_Click(object sender, EventArgs e)
        {
            string firstYear = textBox5.Text.ToString();
            string secondYear = textBox6.Text.ToString();;
            int yr1 = CheckYear(firstYear);
            int yr2 = CheckYear(secondYear);
            if ((yr1 != 1 && yr2 != 1) && yr1 <= yr2)
            {
                string queryString = "SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.MovieYear BETWEEN " + yr1 + " AND " + yr2 + "";
                loadDataGrid(queryString);
            }
            else
            {
                MessageBox.Show("The year format isn't correct, pleas check again.","Warning",MessageBoxButtons.OK, MessageBoxIcon.Warning);
                textBox5.Clear();
                textBox5.Focus();
                textBox6.Clear();
            }
        }
        #endregion