如何使用sqlcommand删除多条记录 [英] how deleteing multi records with sqlcommand

问题描述

我有一个名为rowlist的列表

  if （dataGridView1 .SelectedRows.Count >   1 ）
 {
 List< string> rowList =  new  List< string>（）; 
 
  foreach （DataGridViewRow r  in  dataGridView1.SelectedRows）
 < span class =code-keyword> if （r.Cells [ 3 ]。Value.ToString（）！=  null ）
 rowList.Add（r.Cells [ 3 ]。Value.ToString（））; 
} 

然后我想基于此列表用sqlcommand删除数据库。



< pre lang =c＃> commandtext = 删除mytable where recordid in（select ???）

选择后我不知道输入。

请帮帮我。

解决方案

见下文：

 commandtext =  删除mytable where recordid in（ +  string  .Join（  ，，rowList.ToList（））+  ） 

直接在FE中使用Sql删除命令不是一个好主意。我建议你创建一个程序删除所选行。当您将所选行ID存储在列表< string>< ; / string> ，将列表传递给程序（见这里： C＃SQL Server - 将列表传递给存储过程 [ ^ ]。）并执行删除操作使用Sql Transaction的过程。



请参阅：将SQL保存在存储过程与代码中的优缺点是什么[ ^ ]。

- Amit

试试这个： -

从rowlist和ad中创建数据字符串d进入你的删除查询
 
 string data = null; 
 if（dataGridView1.SelectedRows.Count> 1）
 {
 List< string> rowList = new List< string>（）; 
 
 foreach（dataGridViewRow r in dataGridView1.SelectedRows）
 {
 if（r.Cells [3] .Value.ToString（）！= null）
 {
 rowList.Add（r.Cells [3] .Value.ToString（））; 
 data = data +'+ r.Cells [3] .Value.ToString（）+'，; 
} 
} 
} 
 if（Strings.Len（data）> = 1）{
 data = Strings.Mid（data，1，Strings.Len （数据） -  1）; 
} 
 commandtext =删除mytable where recordid in（+ data +）; 

I have a List named rowlist

if (dataGridView1.SelectedRows.Count > 1)
            {
                List<string> rowList = new List<string>();

                foreach (DataGridViewRow r in dataGridView1.SelectedRows)
                    if (r.Cells[3].Value.ToString() != null)
                        rowList.Add(r.Cells[3].Value.ToString());
}

then I want to delete database with sqlcommand based on this list.

commandtext ="delete mytable where recordid in(select ???)

i dont know input after select.
please help me.

解决方案

See below :

commandtext = "delete mytable where recordid in(" + string.Join(",", rowList.ToList()) + ")"

Using Sql Delete Command directly in FE is not a good idea. I would suggest you to create a procedure to delete the selected row. As you are storing the selected rows ID in list<string></string>, pass the list to the procedure(See here : C# SQL Server - Passing a list to a stored procedure[^].) and perform the delete operation in procedure with Sql Transaction.

Please see : What are the pros and cons to keeping SQL in Stored Procs versus Code[^].

--Amit

try this :-

Make string of data from rowlist and add into your delete query

string data= null;
if (dataGridView1.SelectedRows.Count > 1)
            {
                List<string> rowList = new List<string>();
 
                foreach (DataGridViewRow r in dataGridView1.SelectedRows)
		  {
                    if (r.Cells[3].Value.ToString() != null)
                        {
                          rowList.Add(r.Cells[3].Value.ToString());
			  data = data + "'" + r.Cells[3].Value.ToString() + "',";
                        }
		  }
}
if (Strings.Len(data) >= 1) {
		data = Strings.Mid(data, 1, Strings.Len(data) - 1);
}
commandtext = "delete mytable where recordid in(" + data + ")";

这篇关于如何使用sqlcommand删除多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！