添加列后，Getchanges不起作用 [英] Getchanges doesn't work after adding column

问题描述

当我以编程方式添加新列时，它在新添加的列中不起作用，但在其余列中工作

when i add a new column programmatically it doesn't work in the newly added column but working in the rest of columns

SqlDataAdapter da;
DataSet ds;


string query = @"Select sale.SaleID,item.ItemID, invoice.InvoiceID,
 Item.ItemName as
item,  invoice.Qty
,invoice.SalePrice as [Sale Price] FROM invoice
JOIN item ON invoice.ItemID = item.ItemID
JOIN sale ON invoice.SaleID = sale.SaleID
where sale.SaleID = '" + textBox1.Text + "'";
da = new SqlDataAdapter(query, con);
ds = new System.Data.DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];


//creating new column
DataGridViewTextBoxColumn column2 = new DataGridViewTextBoxColumn();
column2.ReadOnly = false;
column2.Name = "column2";
column2.Resizable = DataGridViewTriState.False;
//column1.SortMode = DataGridViewColumnSortMode.Automatic;
column2.HeaderCell.Style.Alignment =
DataGridViewContentAlignment.MiddleCenter;
column2.DefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;
column2.HeaderText = "New Quantity";

//adding column to dataGridView
dataGridView1.Columns.Add(column2);

    private void button3_Click(object sender, EventArgs e)
{

    DataSet dasetGetChanges = ds.GetChanges();
    dataGridView2.DataSource = dasetGetChanges.Tables[0];


}

我的尝试：



i搜索了很多东西，但是找不到美丽的东西

What I have tried:

i searched alot but donot find something beautiful

推荐答案

先做好事情;您的代码容易受到 SQL注入！永远不会通过在命令和变量中进行分段创建来创建SQL查询。正确的方法是使用参数。



这只添加2行代码;一个定义命令，另一个添加参数。然后该命令可用于您的数据适配器。

Well first things first; your code is vulnerable to SQL Injection! Never ever ever create a SQL query by piecemealing in the command and variables. The proper way to do this would be to use Parameters.

This only adds 2 lines of code; one defining the command, and the other adding the parameter to it. That command can then be used for your data adapter.

string query = @"Select sale.SaleID,item.ItemID, invoice.InvoiceID,
 Item.ItemName as
item,  invoice.Qty
,invoice.SalePrice as [Sale Price] FROM invoice
JOIN item ON invoice.ItemID = item.ItemID
JOIN sale ON invoice.SaleID = sale.SaleID
where sale.SaleID = @SaleID";

SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@SaleID", textBox1.Text);
da = new SqlDataAdapter(cmd);

现在提出原始问题

您想在DataSet中为新数量添加一个新列。一种简单的方法是在原始查询中添加占位符。我所做的只是在你的陈述中添加一个别名。如果您需要迁移到对象模型或强类型数据集，此更改也将包含正确的SQL元数据

Now for your original question
You want to add in a new column into your DataSet for "new quantity". An easy way to do this would be to add a placeholder into your original query. What I did was to just add in an alias to your statement. If you ever would need to migrate into Object Models or strongly typed data sets this change would also have the correct SQL Metadata already in it

SELECT sale.SaleID,
	item.ItemID,
	invoice.InvoiceID,
	Item.ItemName as item,
	invoice.Qty,
	invoice.SalePrice as [Sale Price],
	invoice.Qty as [New Quantity]      -- New column prefilled with current values
FROM invoice
JOIN item ON invoice.ItemID = item.ItemID
JOIN sale ON invoice.SaleID = sale.SaleID

以下是有关SQL注入的一些资源供您阅读：

xkcd：对妈妈的漏洞利用 [ ^ ]

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

示例的SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ - 信息安全堆栈交换 [ ^ ]

Here are some resources about SQL Injection for you to read up on:
xkcd: Exploits of a Mom[^]
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

首先，不要使用连接文本作为查询！它可能会提供严重的问题，因为您的代码是 SQL注入 [ ^ ]易受攻击！



您必须使用参数化查询！

First of all, do not use concatenated text as a query! It might provide serious problems, because your code is SQL Injection[^] vulnerable!

You have to use parameterized queries instead!

DataTable dt = new DataTable();
string sSqlConn = "enter connection details here";
using (SqlConnection connection = new SqlConnection(sSqlConn))
{
    string sql = @"SELECT * FROM YourTable WHERE FieldName=@param1";
    connection.Open();
    
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@param1", textBox1.Text);
        using (SqlDataReader reader = command.ExecuteReader())
        {
            dt.Load(reader); //load data into datatable!	
        }
    }
}
DataColumn dc = dt.Columns.Add("New Quantity", typeof(double));
dc.Expression = "qty * SalePrice";

dataGridView1.DataSource = dt;
}

详情请见：

SqlParameterCollection.AddWithValue(String,Object）方法（System.Data.SqlClient） ）| Microsoft Docs [ ^ ]

在sql server中编写安全的动态sql [ ^ ]

< a href =https://blogs.msdn.microsoft.com/raulga/2007/01/04/dynamic-sql-sql-injection/>动态SQL& SQL注入 - Raul Garcia的博客 [ ^ ]

DataColumn.Expression属性（System.Data）| Microsoft Docs [ ^ ]

创建表达式列| Microsoft Docs [ ^ ]

For further details, please see:
SqlParameterCollection.AddWithValue(String, Object) Method (System.Data.SqlClient) | Microsoft Docs[^]
Writing secure dynamic sql in sql server[^]
Dynamic SQL & SQL injection – Raul Garcia's blog[^]
DataColumn.Expression Property (System.Data) | Microsoft Docs[^]
Creating Expression Columns | Microsoft Docs[^]

这篇关于添加列后，Getchanges不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！