如何防止将重复记录插入到db中 [英] How to prevent insertion of duplicate records into db
本文介绍了如何防止将重复记录插入到db中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的代码问题是当我点击保存时插入重复的记录
idont知道我如何解决这个问题
使用系统;
使用 System.Collections.Generic;
使用 System.ComponentModel;
使用 System.Data;
使用 System.Drawing;
使用 System.Linq;
使用 System.Text;
使用 System.Threading.Tasks;
使用 System.Windows.Forms;
使用 System.Data.SqlClient;
使用 System.IO;
命名空间 Carprogram
{
public partial class Form1:Form
{
SqlConnection mm = new SqlConnection( Data Source = NAWAF; Initial Catalog = CAR; Integrated Security = True );
DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
}
string imgloc = < span class =code-string>;
SqlCommand cmd;
private void linkLabel4_LinkClicked( object sender,LinkLabelLinkClickedEventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = png文件(* .png)| * .png | jpg文件(*。 jpg)| * .jpg |所有文件(*。*)| *。*;
if (dialog.ShowDialog()== DialogResult.OK)
{
imgloc = dialog.FileName.ToString();
pictureBox1.ImageLocation = imgloc;
}
}
private void groupBox1_Enter( object sender,EventArgs e)
{
}
private void save_Click( object sender,EventArgs e)
{
string stat = 活性跨度>;
byte [] images = null ;
FileStream Streem = new FileStream(imgloc,FileMode.Open,FileAccess.Read);
BinaryReader brs = new BinaryReader(Streem);
images = brs.ReadBytes(( int )Streem.Length);
mm.Open();
string vmd = 插入vehicleinfo(模式,制造,型号,颜色,类型,里程表,odoty,vin,车辆,驾驶员,部门,发动机,变速箱,轮胎,平板,续订,公司,帐户,保险费,到期,注意,img,状态) VALUES(' + modely.Text + ',' + make.Text + ',' + model.Text + ',' + color.Text + ',' + type.Text + ',' +里程表.Text + ',' + odoty.Text + ',' + vin.Text + ',' + vehicle.Text + ',' + driverop.Text + ',' + department.Text + ',' + engine.Text + ',' + transmission.Text + ',' + tiresize.Text + ',' + platlic .Text + ',' + renewal.Text + ',' + company.Text + ',' + account.Text + ',' + premium.Text + ',' + due.Text + ',' + note.Text + ',@ images,@ stat);
cmd = new SqlCommand(vmd,mm);
cmd.Parameters.Add( new SqlParameter( @images跨度>,图像));
cmd.Parameters.Add( new SqlParameter( @stat,stat));
cmd.ExecuteNonQuery();
int N = cmd.ExecuteNonQuery();
mm.Close();
MessageBox.Show(N.ToString()+ 数据已保存 );
modely.Text = ;
make.Text = ;
model.Text = ;
color.Text = ;
type.Text = ;
odometer.Text = ;
vin.Text = ;
vehicle.Text = ;
driverop.Text = ;
department.Text = ;
engine.Text = ;
transmission.Text = ;
tiresize.Text = ;
platlic.Text = ;
renewal.Text = ;
company.Text = ;
premium.Text = ;
due.Text = ;
note.Text = ;
}
private void Form1_Load( object sender,EventArgs e)
{
fillData();
}
private void fillData()
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand rrm;
string sql = select * from vehicleinfo;
rrm = new SqlCommand(sql,mm);
adapter.SelectCommand = rrm;
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}
private void comboBox1_SelectedIndexChanged( object sender,EventArgs e)
{
DataView dv = new DataView(dt);
if (comboBox1.SelectedItem.ToString()== 显示全部)
{
dataGridView1.DataSource = dt;
}
else
{
dv.RowFilter = string .Format( status LIKE'%0%',comboBox1.SelectedItem的ToString());
}
}
}
}
我尝试了什么:
i希望单个记录不重复
解决方案
1。切勿使用字符串连接来创建SQL查询。您打开系统进行称为注入的攻击 - xkcd:对妈妈的漏洞利用 [ ^ ]
2.防止重复数据的唯一正确方法是通过声明唯一索引在您的数据库中... SQL Server提示:使用唯一约束来防止重复记录LGIT智能解决方案 [ ^ ]
删除该行并解决问题
cmd.ExecuteNonQuery();
EASY SOLUTION
the problem with my code is when i click save it insert duplicate records
idont know how i fix that
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;
using System.IO;
namespace Carprogram
{
public partial class Form1 : Form
{
SqlConnection mm = new SqlConnection("Data Source=NAWAF;Initial Catalog=CAR;Integrated Security=True");
DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
}
string imgloc="";
SqlCommand cmd;
private void linkLabel4_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog ();
dialog.Filter = "png files(*.png)|*.png|jpg files(*.jpg)|*.jpg|All files(*.*)|*.*";
if(dialog.ShowDialog()==DialogResult.OK)
{
imgloc =dialog.FileName.ToString();
pictureBox1.ImageLocation=imgloc;
}
}
private void groupBox1_Enter(object sender, EventArgs e)
{
}
private void save_Click(object sender, EventArgs e)
{
string stat = "active";
byte[] images = null;
FileStream Streem = new FileStream(imgloc, FileMode.Open, FileAccess.Read);
BinaryReader brs = new BinaryReader(Streem);
images = brs.ReadBytes((int)Streem.Length);
mm.Open();
string vmd = "Insert into vehicleinfo(modely,make,model,color,type,odometer,odoty,vin,vehicle,driverop,department,engine,transmission,tiresize,platlic,renewal,company,account,premium,due,note,img,status)VALUES('" + modely.Text + "' ,'" + make.Text + "' , '" + model.Text + "' , '" + color.Text + "' , '" + type.Text + "','" + odometer.Text + "' ,'" + odoty.Text + "','" + vin.Text + "' , '" + vehicle.Text + "' , '" + driverop.Text + "' , '" + department.Text + "','" + engine.Text + "','" + transmission.Text + "','" + tiresize.Text + "','" + platlic.Text + "','" + renewal.Text + "','" + company.Text + "','" + account.Text + "','" + premium.Text + "','" + due.Text + "','" + note.Text + "',@images,@stat)";
cmd = new SqlCommand (vmd,mm);
cmd.Parameters.Add(new SqlParameter("@images",images));
cmd.Parameters.Add(new SqlParameter("@stat", stat));
cmd.ExecuteNonQuery();
int N = cmd.ExecuteNonQuery();
mm.Close();
MessageBox.Show(N.ToString() + "Data Saved");
modely.Text ="";
make.Text ="";
model.Text ="";
color.Text ="";
type.Text ="";
odometer.Text="";
vin.Text ="";
vehicle.Text ="";
driverop.Text ="";
department.Text ="";
engine.Text ="";
transmission.Text="";
tiresize.Text ="";
platlic.Text ="";
renewal.Text ="";
company.Text ="";
premium.Text ="";
due.Text = "";
note.Text = "";
}
private void Form1_Load(object sender, EventArgs e)
{
fillData();
}
private void fillData()
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand rrm;
string sql = "select * from vehicleinfo";
rrm = new SqlCommand(sql, mm);
adapter.SelectCommand = rrm;
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
DataView dv = new DataView(dt);
if(comboBox1.SelectedItem.ToString()=="Show ALL")
{
dataGridView1.DataSource = dt;
}
else
{
dv.RowFilter = string.Format("status LIKE '%0%'", comboBox1.SelectedItem.ToString());
}
}
}
}
What I have tried:
i want single record not duplicate
解决方案
1. NEVER use string concatenation to create SQL query. You open your system to attacks called 'injection' - xkcd: Exploits of a Mom[^]
2. The only true - and right - way to prevent duplicate data is by declaring unique indexes in your database... SQL Server Tip : Preventing Duplicate Records Using the "Unique" Constraint | LGIT Smart Solutions[^]
Delete the line and resolve the problem
cmd.ExecuteNonQuery();
EASY SOLUTION
这篇关于如何防止将重复记录插入到db中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文