Skip Navigation LinksНа-языке-C-Sharp

Фрагмент Кода программы на С# по управлению базой данных
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
 
namespace IndivPlan
{
    public partial class Form2 : Form
    {
        int hours = 0;
        int hoursEx = 0;
        int hEx = 0;
        int g = 0;
 
        public Form2()
        {
            InitializeComponent();
        }
 
        private void Form2_Load(object sender, EventArgs e)
        {
            listBoxPrepLoad();
            DataGridViewCheckBoxColumn CheckCol1 = new DataGridViewCheckBoxColumn();
            DataGridViewCheckBoxColumn CheckCol2 = new DataGridViewCheckBoxColumn();
            dataGridView1.Columns.Add(CheckCol1);
            dataGridView2.Columns.Add(CheckCol2);
            CheckCol1.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCellsExceptHeader;
            CheckCol2.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCellsExceptHeader;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView2.AllowUserToAddRows = false;
        }
 
        private void listBoxPrepLoad()
        {
            string cmd = "SELECT Prepod.Преподаватель FROM Prepod";
            SqlConnection conn = new SqlConnection(Data.connString);
            SqlCommand comm = new SqlCommand(cmd, conn);
            conn.Open();
            SqlDataReader dr;
            dr = comm.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                listBox1.Items.Add(dr[0].ToString());
                i = i + 1;
            }
            conn.Close();
        }
 
        private void dataGridView1Load()
        {
            string cmd = "exec NeraspNagr";
            SqlConnection conn = new SqlConnection(Data.connString);
            SqlCommand comm = new SqlCommand(cmd, conn);
            SqlDataAdapter adap = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            conn.Open();
            adap.Fill(ds, "Result");
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Result";
            conn.Close();
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            for (int i = 1; i < dataGridView1.Columns.Count; i++)
            {
                dataGridView1.Columns[i].ReadOnly = true;
            }
            dataGridView1.Columns[1].Visible = false;
        }
 
        private void dataGridView2Load()
        {
            string cmd = "SELECT T101.id_101,T101.Дисциплина,T101.Семестр,T101.Группа,T101.[Вид работы],T101.[Количество часов в неделю],T101.[Количество часов за семестр] FROM T101 INNER JOIN NagrPrep ON NagrPrep.id_101=T101.id_101 INNER JOIN Prepod ON NagrPrep.id_p=Prepod.id_p WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            SqlConnection conn = new SqlConnection(Data.connString);
            SqlCommand comm = new SqlCommand(cmd, conn);
            SqlDataAdapter adap = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            conn.Open();
            adap.Fill(ds, "Resul");
            conn.Close();
            dataGridView2.DataSource = ds;
            dataGridView2.DataMember = "Resul";
            conn.Close();
            dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            for (int i = 1; i < dataGridView2.Columns.Count; i++)
            {
                dataGridView2.Columns[i].ReadOnly = true;
            }
            dataGridView2.Columns[1].Visible = false;
        }
 
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            dataGridView1Load();
            dataGridView2Load();
            button1.Enabled = true;
            button2.Enabled = true;
            string cmd = "SELECT sum(T101.[Количество часов за семестр]) FROM Prepod INNER JOIN NagrPrep ON Prepod.id_p=NagrPrep.id_p INNER JOIN T101 ON NagrPrep.id_101=T101.id_101 WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            SqlConnection conn = new SqlConnection(Data.connString);
            SqlCommand comm = new SqlCommand(cmd, conn);
            conn.Open();
            if (comm.ExecuteScalar() != DBNull.Value)
                hours = Convert.ToInt16(comm.ExecuteScalar());
            else
                hours = 0;
            conn.Close();
            textBox1.Text = hours.ToString();
            hoursEx = hours;
            textBox2.Text = hoursEx.ToString();
            textBox3.Text = hours.ToString();
            textBox4.Text = hoursEx.ToString();
            string cmd2 = "SELECT Dolzh.Должность FROM Prepod INNER JOIN Dolzh ON Prepod.id_dol=Dolzh.id_dol WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            string cmd3 = "SELECT Dolzh.[Норматив нагрузки] FROM Prepod INNER JOIN Dolzh ON Prepod.id_dol=Dolzh.id_dol WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            SqlCommand comm2 = new SqlCommand(cmd2, conn);
            SqlCommand comm3 = new SqlCommand(cmd3, conn);
            conn.Open();
            string dol = Convert.ToString(comm2.ExecuteScalar());
            g = Convert.ToInt16(comm3.ExecuteScalar());
            conn.Close();
            label7.Visible = true;
            label8.Visible = true;
            label9.Visible = true;
            label10.Visible = true;
            label8.Text = dol;
            label10.Text = g.ToString();
            if (listBox1.SelectedItem != null)
                button3.Enabled = true;
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView2.Rows.Count; i++)
            {
                if (dataGridView2.Rows[i].Cells[0].Value != null && (bool)dataGridView2.Rows[i].Cells[0].Value != false)
                {/*
                    string cmd5 = "UPDATE T101 SET Зачеты=Зачеты+" + dataGridView1[9, i].Value + "WHERE T101.id_101=" + dataGridView1[1, i].Value;
                    string cmd3 = "UPDATE T101 SET Экзамены=Экзамены+" + dataGridView1[11, i].Value + "WHERE T101.id_101=" + dataGridView1[1, i].Value;
                    string cmd4 = "UPDATE T101 SET [КР/КП]=[КР/КП]+" + dataGridView1[13, i].Value + "WHERE T101.id_101=" + dataGridView1[1, i].Value;
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm5 = new SqlCommand(cmd5, conn);
                    SqlCommand comm3 = new SqlCommand(cmd3, conn);
                    SqlCommand comm4 = new SqlCommand(cmd4, conn);
                    conn.Open();
                    comm5.ExecuteNonQuery();
                    comm3.ExecuteNonQuery();
                    comm4.ExecuteNonQuery();*/
                   
                    string cmd = "DELETE FROM NagrPrep WHERE NagrPrep.id_p=(SELECT id_p FROM Prepod WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "') AND NagrPrep.id_101=(SELECT id_101 FROM T101 WHERE T101.id_101=" + dataGridView2[1, i].Value + ")";
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm = new SqlCommand(cmd, conn);
                    conn.Open();
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
            }
            dataGridView1Load();
            dataGridView2Load();
            string cmd2 = "SELECT sum(T101.[Количество часов за семестр]) FROM Prepod INNER JOIN NagrPrep ON Prepod.id_p=NagrPrep.id_p INNER JOIN T101 ON NagrPrep.id_101=T101.id_101 WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            SqlConnection conn2 = new SqlConnection(Data.connString);
            SqlCommand comm2 = new SqlCommand(cmd2, conn2);
            conn2.Open();
            if (comm2.ExecuteScalar() != DBNull.Value)
                hours = Convert.ToInt16(comm2.ExecuteScalar());
            else
                hours = 0;
            conn2.Close();
            textBox1.Text = hours.ToString();
            hoursEx = hours;
            textBox3.Text = hours.ToString();
            textBox2.Text = hours.ToString();
            textBox4.Text = hoursEx.ToString();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                if (dataGridView1.Rows[i].Cells[0].Value != null && (bool)dataGridView1.Rows[i].Cells[0].Value != false)
                {   /*
                    string cmd5 = "UPDATE T101 SET [Зачеты задано]" + dataGridView1[9, i].Value + "WHERE T101.id_101=" + dataGridView1[1,i].Value;
                    string cmd3 = "UPDATE T101 SET [Экзамены задано]" + dataGridView1[11, i].Value + "WHERE T101.id_101=" + dataGridView1[1, i].Value;
                    string cmd4 = "UPDATE T101 SET [КР/КП задано]" + dataGridView1[13, i].Value + "WHERE T101.id_101=" + dataGridView1[1, i].Value;
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm5 = new SqlCommand(cmd5, conn);
                    SqlCommand comm3 = new SqlCommand(cmd3, conn);
                    SqlCommand comm4 = new SqlCommand(cmd4, conn);
                    conn.Open();
                    comm5.ExecuteNonQuery();
                    comm3.ExecuteNonQuery();
                    comm4.ExecuteNonQuery();*/
 
                    string cmd = "INSERT INTO NagrPrep SELECT Prepod.id_p,T101.id_101 FROM Prepod,T101 WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "' AND T101.id_101=" + dataGridView1[1, i].Value;
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm = new SqlCommand(cmd, conn);
                    conn.Open();
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
            }
            dataGridView1Load();
            dataGridView2Load();
            string cmd2 = "SELECT sum(T101.[Количество часов за семестр]) FROM Prepod INNER JOIN NagrPrep ON Prepod.id_p=NagrPrep.id_p INNER JOIN T101 ON NagrPrep.id_101=T101.id_101 WHERE Prepod.Преподаватель='" + listBox1.SelectedItem + "'";
            SqlConnection conn2 = new SqlConnection(Data.connString);
            SqlCommand comm2 = new SqlCommand(cmd2, conn2);
            conn2.Open();
            if (comm2.ExecuteScalar() != DBNull.Value)
                hours = Convert.ToInt16(comm2.ExecuteScalar());
            else
                hours = 0;
            conn2.Close();
            textBox1.Text = hours.ToString();
            hoursEx = hours;
            textBox3.Text = hours.ToString();
            textBox2.Text = hours.ToString();
            textBox4.Text = hoursEx.ToString();
        }
 
        void dataGridView1_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            if (dataGridView1.IsCurrentCellDirty)
                dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
        }
 
        public void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //int g = 0;
            hEx = 0;
            hoursEx = hours;
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                if (dataGridView1.Rows[i].Cells[0].Value != null && (bool)dataGridView1.Rows[i].Cells[0].Value != false)
                {
                    string cmd = "SELECT T101.[Количество часов за семестр] FROM T101 WHERE T101.id_101=" + dataGridView1.Rows[i].Cells[1].Value;
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm = new SqlCommand(cmd, conn);
                    conn.Open();
                    hEx = hEx + Convert.ToInt16(comm.ExecuteScalar());
                    conn.Close();
                }
            }
            hoursEx = hoursEx + hEx;
            textBox2.Text = hoursEx.ToString();
 
            if (hoursEx > g)
            {
                label6.Visible = true;
                button1.Enabled = false;
            }
            else
            {
                label6.Visible = false;
                button1.Enabled = true;
            }
        }
 
        void dataGridView2_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            if (dataGridView2.IsCurrentCellDirty)
                dataGridView2.CommitEdit(DataGridViewDataErrorContexts.Commit);
        }
 
        public void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            hEx = 0;
            hoursEx = hours;
            for (int i = 0; i < dataGridView2.Rows.Count; i++)
            {
                if (dataGridView2.Rows[i].Cells[0].Value != null && (bool)dataGridView2.Rows[i].Cells[0].Value != false)
                {
                    string cmd = "SELECT T101.[Количество часов за семестр] FROM T101 WHERE T101.id_101=" + dataGridView2.Rows[i].Cells[1].Value;
                    SqlConnection conn = new SqlConnection(Data.connString);
                    SqlCommand comm = new SqlCommand(cmd, conn);
                    conn.Open();
                    hEx = hEx + Convert.ToInt16(comm.ExecuteScalar());
                    conn.Close();
                }
            }
            hoursEx = hoursEx - hEx;
            textBox4.Text = hoursEx.ToString();
        }
 
        private void tabPage1_Leave(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
                dataGridView1[0, i].Value = false;
        }
 
        private void tabPage2_Leave(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView2.Rows.Count; i++)
                dataGridView2[0, i].Value = false;
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            Data.prep = listBox1.SelectedItem.ToString();
            Form f5 = new Form5();
            f5.Show();
        }
 
 
    }
}​​