更好的代码 [英] better code for this

问题描述

protected void imgbtnSave_Click(object sender, ImageClickEventArgs e)
    {

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"].ToString());
        con.Open();

        for (int i = 0; i < ddlTesterName.Items.Count && i < ddlBuildNO.Items.Count && i < lbAddedItems.Items.Count; i++)
        {
          
            
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "usp_InsertWorkAllocation";
            com = new SqlCommand(str, con);
           

            com.Parameters.AddWithValue("@LID", ddlTesterName.Items[i].ToString());
            com.Parameters.AddWithValue("@BID", ddlBuildNO.Items[i].ToString());
            com.Parameters.AddWithValue("@TID", lbAddedItems.Items[i].ToString());
            com.ExecuteNonQuery();
            com = new SqlCommand(str, con);
            com.ExecuteNonQuery();




        }
    }

推荐答案

不是这样.处理数据库时的第一条规则，请使用参数化查询！！！我不能说太多次了，但是我已经知道了.
首先，参数化查询可以提高性能. Sql Server会缓存您的查询，并通过对它们进行参数化来重新使用查询的机会，从而大大提高了使用缓存而不是再次执行整个查询的过程.
但是，我一年您说我的计算机足够快，我不在乎我是在10到15毫秒内获得数据".好吧，您可能是对的，因此还有另一个非常重要的原因可以参数化您的查询: SQL注入 [ ^ ]！我不知道您的下拉框是如何填充的，但是假设用户可以在某处输入下拉值.用户输入"D" Artagnan"，您看到发生了什么吗? ''将中断您的查询，您的用户将获得异常，而不是预期的结果！在最坏的情况下，黑客将获得用户信息，登录名，未加密的密码等，或者删除整个表和数据库！ 哇"，我听说您认为这很严重，我该怎么办！?".
好吧，幸运的是，参数化查询并不困难.这是该怎么做:

Not like that. Rule number one when working with databases, USE PARAMETERIZED QUERIES!!! I can''t say this too many times, and yet I already have.
First of all, parameterized queries make for better performance. Sql server caches your queries and by parameterizing them the chance of a query being re-used, and thus using cache instead of doing the entire query again, raises significantly.
But, I year you say "my computer is fast enough I don''t care if I get my data in 10 or 15 milliseconds". Well, you might be right, so there is another VERY IMPORTANT REASON to parameterize your queries: SQL INJECTION[^]! I don''t know how your drop down box is filled, but let''s say a user can enter dropdown values somewhere. The user enters "D''Artagnan", do you see what happens? The '' will break your query and your user will get an exception instead of expected results! In worst case scenario''s hackers will get user information, login names, unencrypted passwords etc. or delete entire tables and databases! "Wow", I hear you think, "that''s pretty serious, what do I have to do!?".
Well, luckily, parameterizing your queries isn''t to difficult. Here''s what to do:

// Replace this code:
str = "insert into tblWorkAllocation values('" + ddlTesterName.Items[i].ToString() + "','" + ddlBuildNO.Items[i].ToString() + "','" + lbAddedItems.Items[i].ToString() + "')";
com = new SqlCommand(str, con);
com.ExecuteNonQuery();

// With this code:
str = "insert into tblWorkAllocation values('@TesterName','@BuildNO','@AddedItem')";
com = new SqlCommand(str, con);
com.Parameters.AddWithValue("@TesterName", ddlTesterName.Items[i].ToString());
com.Parameters.AddWithValue("@BuildNO", ddlBuildNO.Items[i].ToString());
com.Parameters.AddWithValue("@AddedItem", ddlAddedItems.Items[i].ToString());
com.ExecuteNonQuery();

如您所见，您的代码更加简洁易读.不再需要讨厌的字符串连接.但是这里最重要的是，现在您的参数(@ TesterName，@ BuildNO，@ AddedItem)已替换为您添加到Command Object的参数集合中的值.现在将接受"D''Artagnan"作为适当的值，sql注入已经成为不可能，或者至少真的非常不可能，并且您和您的用户可以轻松知道自己的代码是正确和安全的！

至于你的问题:
我认为这行代码可以阻止循环多次循环:

As you can see your code is cleaner and more readable. No more nasty string concatenation. But the biggest importance here is that your parameters (@TesterName, @BuildNO, @AddedItem) are now replaced with the values you added to the parameter collection of your Command Object. "D''Artagnan" will now be accepted as a proper value, sql injection has become an impossibility or at least really very unlikely and you and your users can breathe easy knowing that your code is correct and safe!

As for your question:
I think this line of code is stopping the loop from ever looping more than once:

if (ddlTesterName.Items[i].Selected == true && ddlBuildNO.Items[i].Selected == true && lbAddedItems.Items[i].Selected ==true)

，因为一次只能选择一项:)
希望对您有所帮助！

Since only one item can be selected at one time :)
Hope this helps!

您可以构建包含多个行信息的单个命令:

You can build up a single command containing multiple row information:

INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...) ("next value1", "next value2", ...)

我会使用一个StringBuilder而不是一个字符串.

不幸的是，AFAIK(我想告诉大家不同的是)您不能使用参数化查询来进行多次插入，您实际上应该使用该参数来避免SQL注入攻击.

[edit]
有一种方法可以执行此操作，而无需构建命令字符串，使用参数化查询并仅发出单个更新:DataTable中的BulkUpdate:

I would use a StringBuilder rather than a string for this.

Unfortunately, AFAIK (and I would love to be told different) you can''t do multiple inserts using parametrized queries, which you should really be using to avoid SQL Injection attacks.

[edit]
There is a way to do this, without building a command string, and using parametrized queries, and issuing just the single update: BulkUpdate from a DataTable:

string strConnect = @"Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlBulkCopy bulk = new SqlBulkCopy(con))
        {
        bulk.DestinationTableName = "Test";
        DataTable dt = new DataTable();
        dt.Columns.Add("Id");
        dt.Columns.Add("Data");
        dt.Rows.Add("ID 1", "DATA 1");
        dt.Rows.Add("ID 2", "DATA 2");
        dt.Rows.Add("ID 3", "DATA 3");
        dt.Rows.Add("ID 4", "DATA 4");
        bulk.WriteToServer(dt);
        }
    }

您要做的就是创建三列，每列均带有数据库列的名称，然后在循环时添加每一行.
[/edit]

All you have to do is create three columns, each with the name of your DB column, then add each row as you go round your loop.
[/edit]

使用以下代码:
创建您的连接并打开它，然后添加以下内容:

首先，运行查询:

Use the following code:
Create your connection and open it, then add the following:

First, run a query:

Select top 1 WID from tblWorkAllocation order by WID desc

这将为您提供最后输入的WID(主键).将其存储在变量中

This will give you the last entered WID(primary key). Store it in a variable

int wid=0;
object obj=SqlCommand1.ExecuteScalar();
if(obj!=null)
   wid=Convert.ToInt32(obj.ToString());

using (SqlBulkCopy bulk = new SqlBulkCopy(con))
{
    bulk.DestinationTableName = "Test";
    DataTable dt = new DataTable();
    dt.Columns.Add("WID");
    dt.Columns.Add("LID");
    dt.Columns.Add("BID");
    dt.Columns.Add("TID"); 
    for(int i=0; i<lbAddedItems.Items.Count; i++)
    {
        if(lbAddedItems.Items[i].Selected)
        {   dt.Rows.Add(++wid, ddlTesterName.SelectedItem.ToString(), ddlBuildNO.SelectedItem.ToString(), lbAddedItems.Items[i].ToString());
        }
    }
    bulk.WriteToServer(dt);
}

[/EDIT]

这篇关于更好的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！