希望在代码的帮助下使用C#防止记录中的重复 [英] want to prevent duplication in records using C# with the help of code

问题描述

  try
            {
                SqlConnection con = new SqlConnection(" Data Source=AHSAN-PC\\SQLEXPRESS;Initial Catalog=bank;Integrated Security=SSPI;MultipleActiveResultSets=True;");
                con.Open();
                //MessageBox.Show("connected");
                SqlCommand cmd = new SqlCommand("select * from account",con);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read().Equals(textBox1.Text))
                {
                    MessageBox.Show("sorry duplicate acc no.");
                }
                else
                {
                    //SqlCommand cmd = new SqlCommand("select * from account",con);
                    cmd=new SqlCommand("insert into account values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')", con);
                    SqlDataReader drr = cmd.ExecuteReader();
                    drr.Close();
                    MessageBox.Show("data added");
                    
                    
                }
                dr.Close();
                con.Close();
                //MessageBox.Show("data added");
            }
            catch (Exception ex)
            {
                MessageBox.Show("error" + ex.Message);
}

我收到"There is an already open Datareader associated with this command that must be closed first."
的错误
请帮助我.我正在使用sql server 2005和C#.

我要在代码中选择2和插入1的其他SQL查询，因为我想防止重复在我的应用程序中的帐户号记录中.

I am getting error of "There is an already open Datareader associated with this command that must be closed first."

Kindly help me. I am using sql server 2005 and C#.

I am giving 2 sql queries in code one of select and other of insert.as i want to prevent duplication in account No. records in my application.

推荐答案

错误消息不足以调试问题.

如果您使用的是嵌套数据读取器，则需要启用多个活动记录集.
数据库也应该支持这一点.这是一个示例 [连接字符串来激活MARS [ ^ ].

Just the error message is not sufficient to debug the issue.

If you are using nested Datareaders, you will need to enable Multiple Active Record Sets.
The database should support this as well. Here is an example[^].

MARS is generally activated via the connection string[^].

在第一个请求未完成时，您不能将SqlDataReader用于第二个请求(Close()命令).最好有第二个SqlDataReader对象用于第二个查询.

You cant use the SqlDataReader for a second request while the first request is not jet finished (Close() command). Better have a second SqlDataReader object for the second query.

您可以使用打开的datareader并同时执行命令.
在这种情况下，您可以替换以下代码块:

You use opened datareader and execute command at the same time.
In this case you could replace following block of code:

SqlCommand cmd = new SqlCommand("select * from account",con);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read().Equals(textBox1.Text))

像这样:

With something like this:

var cmd = new SqlCommand("SELECT COUNT (*) FROM [account] WHERE fieldname = @fieldname",con);
cmd.Parameters.AddWithValue("@fieldname", textBox1.Text);
var isExists = Convert.ToInt32(cmd.ExecuteScalar()) > 0;
if (isExists)

这将解决问题，只需将您要比较textBox1.Text的db表中的字段名称替换为"fieldname"即可.

一些小建议:
1.不要在您的sql查询中使用未准备好的字符串(sql-injection问题).请改用参数；
2.将连接字符串存储在< connectionstrings>中的应用程序配置文件中.部分；
3.更加注意可支配资源.如果在try块中遇到任何问题，db连接将保持打开；
4.使用string.Format或StringBuilder进行字符串连接；
如果只是测试项目或示例，那就不要介意:)

This will solve problem, just replace "fieldname" with name of field in db table to which you compare textBox1.Text.

Some small recommendations:
1. Do not use non-prepared strings in your sql queries (problem of sql-injection). Use parameters instead;
2. Store connection string in application configuration file in <connectionstrings> section;
3. Provide more attention to disposable resources. In case of any problem in try block db connection will stay open;
4. Use string.Format or StringBuilder for string concatenation;
If it''s just test project or example then never mind :)

这篇关于希望在代码的帮助下使用C#防止记录中的重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！