.Net sql复制记录对每个插入进行计数 [英] .Net sql Duplication Records Count on each insert

问题描述

我正在尝试在sql中插入重复记录计数。

I am trying to insert the duplication record count in sql.

示例。 Column2应根据名称计数添加值。让玛丽亚第一次插入其他价值  column1应该添加自动值为1和第二次，而maria插入其他值  column1应将auto值添加为2，因为已经在
id中存在1。玛丽亚第三次插入其他价值  column1应该将自动值添加为3，因为他的ID已经存在2。 

example. Column2 should add value as per name count. Let say first time while maria insert other value  column1 should add auto value as 1 and second time while maria insert other value  column1 should add auto value as 2 since 1 is exist in his id already. and third time while maria insert other value  column1 should add auto value as 3 since 2 is exist in his id already. 

每天每个人都有多个插入，我想按名称添加自动值

Everyday each person will have multiple insert and i would like to add auto value by name

预期。

推荐答案

您好Losio1，

Hi Losio1,

事实上，您只需要将第一列设置为 表中的主键（在数据库中创建）并将其设置为自我增长：

In fact, you just need to set the First column as the primary key in the table(created in database) and set it to self-growth:

例如：

在数据库中定义一个表并设置Column1到IDENTITY：

Define a table in database and set the Column1 to IDENTITY:

CREATE TABLE [dbo].[T_Test] (
    [Column1] INT        IDENTITY (1, 1) NOT NULL,
    [Column2] NCHAR (10) NULL,
    [Name]    NCHAR (10) NULL,
    [BD20]    INT        NULL,
    [BD10]    INT        NULL,
    PRIMARY KEY CLUSTERED ([Column1] ASC)
);

因此该字段将自动增长。

So this field will automatically grow.

C＃代码：

    public partial class Form4 : Form
    {
        public Form4()
        {
            InitializeComponent();
        }

        private void Form4_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = BindSource();
        }

        private DataTable dt = new DataTable();
        private DataSet ds = new DataSet();
        string sqlCon = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\DAL\Database1.mdf;Integrated Security=True";

        public DataTable BindSource()
        {
            string sqlSelect = @"select * from T_Test";
            using (SqlConnection conn = new SqlConnection(sqlCon))
            {
                using (SqlCommand cmd = new SqlCommand(sqlSelect, conn))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        ds.Clear();
                        adapter.Fill(ds);
                        dt = ds.Tables[0];
                        conn.Close();
                    }
                }
            }
            return dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(sqlCon))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"insert into T_Test(Column2, Name, BD20, BD10) values(@Column2, @Name, @BD20, @BD10)";
                    cmd.Parameters.AddWithValue("@Column2", textBox1.Text);
                    cmd.Parameters.AddWithValue("@Name", textBox2.Text);
                    cmd.Parameters.AddWithValue("@BD20", Convert.ToInt32(textBox3.Text));
                    cmd.Parameters.AddWithValue("@BD10", Convert.ToInt32(textBox4.Text));
                    cmd.ExecuteNonQuery();
                }
            }
            dataGridView1.DataSource = BindSource();
        }
    }

问候，

Frankie

这篇关于.Net sql复制记录对每个插入进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！