如何按id(主键)winforms删除listview中的选定项

9nvpjoqh 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(317)

所以我在做一个程序，我可以把用户添加到数据库中，然后他们会显示在屏幕的底部。现在，我也尝试从数据库中删除某些项，但我需要id（这是主键），因为所有其他字段都可以重复，它将删除该字段id重复的所有项（这是允许的）

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; //sql server local db

namespace KermisInschrijven
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            bool isChecked = radioButton1.Checked;
            string day = "";
            if (isChecked)
            {
                day = radioButton1.Text;
                load_list_zat();
            } else
            {
                day = radioButton2.Text;
                load_list_zon();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (radioButton1.Checked)
            {
                add_entry_zat();
            } else if ( !radioButton1.Checked){
                add_entry_zon();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView1.Items)
            {
                System.Console.WriteLine(item.Selected);
                if (item.Selected)
                {
                    System.Console.WriteLine("deleting");
                    item.Remove();
                    string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";
                    SqlConnection cn_connection = new SqlConnection(cn_string);

                    string sql_Text = "DELETE FROM tbl_zaterdag WHERE id=@num";
                    SqlCommand cmd = new SqlCommand(sql_Text, cn_connection);
                    cmd.Parameters.AddWithValue("@num", <Insert_id_here>);
                    cn_connection.Open();
                    SqlDataReader r = cmd.ExecuteReader();
                    while (r.Read())
                    {

                    }
                    cn_connection.Close();

                    load_list_zat();
                }
            }
        }

        private void make_empty()
        {
            for (int i = listView1.Items.Count - 1; i >= 0; i--)
            {
                    listView1.Items[i].Remove();
            }
        }

        private void add_entry_zat()
        {
            string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";
            SqlConnection cn_connection = new SqlConnection(cn_string);

            string first_name = firstname.Text;
            string last_name = lastname.Text;

            decimal mg = mosg.Value;
            decimal mk = mosk.Value;
            decimal mgh = mosgh.Value;
            decimal mkh = moskh.Value;
            decimal pg = pag.Value;
            decimal pk = pak.Value;
            decimal pgh = pagh.Value;
            decimal pkh = pakh.Value;

            bool payed = checkBox1.Checked;

            string sql_Text = "INSERT INTO tbl_zaterdag (voornaam, achternaam, mg, mk, mgh, mkh, pg, pk, pgh, pkh, betaald) VALUES(@first, @last, @mg, @mk, @mgh, @mkh, @pg, @pk, @pgh, @pkh, @payed)";
            SqlCommand cmd = new SqlCommand(sql_Text, cn_connection);

            cmd.Parameters.AddWithValue("@first", first_name);
            cmd.Parameters.AddWithValue("@last", last_name);
            cmd.Parameters.AddWithValue("@mg", mg);
            cmd.Parameters.AddWithValue("@mk", mk);
            cmd.Parameters.AddWithValue("@mgh", mgh);
            cmd.Parameters.AddWithValue("@mkh", mkh);
            cmd.Parameters.AddWithValue("@pg", pg);
            cmd.Parameters.AddWithValue("@pk", pk);
            cmd.Parameters.AddWithValue("@pgh", pgh);
            cmd.Parameters.AddWithValue("@pkh", pkh);
            cmd.Parameters.AddWithValue("@payed", payed);

            try
            {
                cn_connection.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {

                }
                cn_connection.Close();
            } catch(Exception e)
            {
                MessageBox.Show(e.Message);
            }

            load_list_zat();
        }

        private void add_entry_zon()
        {
            string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";
            SqlConnection cn_connection = new SqlConnection(cn_string);

            string first_name = firstname.Text;
            string last_name = lastname.Text;

            decimal mg = mosg.Value;
            decimal mk = mosk.Value;
            decimal mgh = mosgh.Value;
            decimal mkh = moskh.Value;
            decimal pg = pag.Value;
            decimal pk = pak.Value;
            decimal pgh = pagh.Value;
            decimal pkh = pakh.Value;

            bool payed = checkBox1.Checked;

            string sql_Text = "INSERT INTO tbl_zondag (voornaam, achternaam, mg, mk, mgh, mkh, pg, pk, pgh, pkh, betaald) VALUES(@first, @last, @mg, @mk, @mgh, @mkh, @pg, @pk, @pgh, @pkh, @payed)";
            SqlCommand cmd = new SqlCommand(sql_Text, cn_connection);

            cmd.Parameters.AddWithValue("@first", first_name);
            cmd.Parameters.AddWithValue("@last", last_name);
            cmd.Parameters.AddWithValue("@mg", mg);
            cmd.Parameters.AddWithValue("@mk", mk);
            cmd.Parameters.AddWithValue("@mgh", mgh);
            cmd.Parameters.AddWithValue("@mkh", mkh);
            cmd.Parameters.AddWithValue("@pg", pg);
            cmd.Parameters.AddWithValue("@pk", pk);
            cmd.Parameters.AddWithValue("@pgh", pgh);
            cmd.Parameters.AddWithValue("@pkh", pkh);
            cmd.Parameters.AddWithValue("@payed", payed);

            try
            {
                cn_connection.Open();
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {

                }
                cn_connection.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            load_list_zon();
        }
        private void load_list_zat()
        {
            make_empty();
            //connection string
            string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";

            //connection
            SqlConnection con = new SqlConnection(cn_string);

            //get data
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM tbl_zaterdag", con);
            DataTable dt = new DataTable();
            da.Fill(dt);

            //loop through items in dt
            foreach (DataRow row in dt.Rows)
            {
                if (dt.Rows.IndexOf(row) != 0)
                {

                    ListViewItem item = new ListViewItem(row["id"].ToString());
                    item.SubItems.Add(row["voornaam"].ToString());
                    item.SubItems.Add(row["achternaam"].ToString());
                    item.SubItems.Add(row["mg"].ToString());
                    item.SubItems.Add(row["mk"].ToString());
                    item.SubItems.Add(row["mgh"].ToString());
                    item.SubItems.Add(row["mkh"].ToString());
                    item.SubItems.Add(row["pg"].ToString());
                    item.SubItems.Add(row["pk"].ToString());
                    item.SubItems.Add(row["pgh"].ToString());
                    item.SubItems.Add(row["pkh"].ToString());
                    double price = calc_price(row["mg"], row["mk"], row["mgh"], row["mkh"], row["pg"], row["pk"], row["pgh"], row["pkh"]);
                    item.SubItems.Add(price.ToString());
                    item.SubItems.Add(row["betaald"].ToString());

                    listView1.Items.Add(item);
                }
            }
        }

        private void load_list_zon()
        {
            make_empty();  //connection string
            string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";

            //connection
            SqlConnection con = new SqlConnection(cn_string);

            //get data
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM tbl_zondag", con);
            DataTable dt = new DataTable();
            da.Fill(dt);

            //loop through items in dt
            foreach (DataRow row in dt.Rows)
            {

                ListViewItem item = new ListViewItem(row["id"].ToString());
                item.SubItems.Add(row["voornaam"].ToString());
                item.SubItems.Add(row["achternaam"].ToString());
                item.SubItems.Add(row["mg"].ToString());
                item.SubItems.Add(row["mk"].ToString());
                item.SubItems.Add(row["mgh"].ToString());
                item.SubItems.Add(row["mkh"].ToString());
                item.SubItems.Add(row["pg"].ToString());
                item.SubItems.Add(row["pk"].ToString());
                item.SubItems.Add(row["pgh"].ToString());
                item.SubItems.Add(row["pkh"].ToString());
                double price = calc_price(row["mg"], row["mk"], row["mgh"], row["mkh"], row["pg"], row["pk"], row["pgh"], row["pkh"]);
                item.SubItems.Add(price.ToString());
                item.SubItems.Add(row["betaald"].ToString());

                listView1.Items.Add(item);
            }
        }

        private double calc_price(object mg, object mk, object mgh, object mkh, object pg, object pk, object pgh, object pkh)
        {
            string mg_str = mg.ToString();
            string mk_str = mk.ToString();
            string mgh_str = mgh.ToString();
            string mkh_str = mkh.ToString();
            string pg_str = pg.ToString();
            string pk_str = pk.ToString();
            string pgh_str = pgh.ToString();
            string pkh_str = pkh.ToString();

            double mg_int = Convert.ToDouble(mg_str);
            double mk_int = Convert.ToDouble(mk_str);
            double mgh_int = Convert.ToDouble(mgh_str);
            double mkh_int = Convert.ToDouble(mkh_str);
            double pg_int = Convert.ToDouble(pg_str);
            double pk_int = Convert.ToDouble(pk_str);
            double pgh_int = Convert.ToDouble(pgh_str);
            double pkh_int = Convert.ToDouble(pkh_str);

            double result = mg_int * 20 + mk_int * 15 + mgh_int * 10 + mkh_int * 7.5 + pg_int * 12 + pk_int * 8 + pgh_int * 6 + pkh_int * 4;
            return result;

        }
    }
}

我的表格是这样的（如果你想知道的话是荷兰语）：

我找不到如何获取所选项目的id，在我的代码中，我希望被项目的id替换。

sql Database c#winforms

来源：https://stackoverflow.com/questions/61933947/how-to-delete-selected-items-in-listview-by-idprimary-key-winforms

2条答案

按热度按时间

yhqotfr81#

找到它：只需做item.text，因为这是所选项目的名称。。。。

foreach (ListViewItem item in listView1.Items)
            {
                System.Console.WriteLine(item.Selected);
                if (item.Selected)
                {
                    System.Console.WriteLine("deleting");
                    item.Remove();
                    string cn_string = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=D:\\Admin\\Code\\C#\\Projects\\KermisInschrijven\\dbInschrijvingen.mdf;Integrated Security=True";
                    SqlConnection cn_connection = new SqlConnection(cn_string);

                    string sql_Text = "DELETE FROM tbl_zaterdag WHERE id=@num";
                    SqlCommand cmd = new SqlCommand(sql_Text, cn_connection);
                    cmd.Parameters.AddWithValue("@num", item.Text);
                    cn_connection.Open();
                    SqlDataReader r = cmd.ExecuteReader();
                    while (r.Read())
                    {

                    }
                    cn_connection.Close();

                    load_list_zat();
                }
            }

赞(0）回复(0）举报 2021-08-09

scyqe7ek2#

创建listviewitem时，传递 id there（then is）用作 Text 属性）。

ListViewItem item = new ListViewItem(row["id"].ToString());

您可以通过listview的selecteditems属性访问它：

var id = "";
if (listView1.SelectedItems.Count > 0)
    id = listView1.SelectedItems[0].Text;

赞(0）回复(0）举报 2021-08-09

我来回答

如何按id(主键)winforms删除listview中的选定项

2条答案

相关问题

热门标签

最新问答