参数化查询 [英] Paramterized Queries

问题描述

我是新的Visual C＃，我对如何编写参数化查询感到困惑。这是我没有它们的代码，

I am new Visual C#, and I am confused on how to write parameterized queries. Here is my code without them,

using System;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Insert_Data
{
    public partial class Form1 : Form
    {
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=ZTABASSUM\\SQLEXPRESS01;Initial Catalog=IntroDataBase;Integrated Security=True");
            con.Open();
            SqlCommand sc = new SqlCommand("Insert into employee values ('"+ textBox1.Text +"' , " + textBox2.Text + ", '" + textBox3.Text + "',  " + textBox4.Text + ", " + textBox5.Text + ");", con);
            int o = sc.ExecuteNonQuery();
            MessageBox.Show(o + ":Record has been inserted");
            con.Close();
        }
    }
}

我不确定如何为每个文本框编写参数化查询。

I am not sure on how to write parameterized queries for each of the text boxes.

推荐答案

我在代码中添加了注释以及最佳做法概述

I added notes in the code along with a best practices recap after.

// best practice - use meaningful method names
private void buttonSaveEmployee_Click(object sender, EventArgs e)
{
    // best practice - wrap all database connections in a using block so they are always closed & disposed even in the event of an Exception
    // best practice - retrieve the connection string by name from the app.config or web.config (depending on the application type) (note, this requires an assembly reference to System.configuration)
    using(SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionName"].ConnectionString))
    {
        // best practice - use column names in your INSERT statement so you are not dependent on the sql schema column order
        // best practice - always use parameters to avoid sql injection attacks and errors if malformed text is used like including a single quote which is the sql equivalent of escaping or starting a string (varchar/nvarchar)
        // best practice - give your parameters meaningful names just like you do variables in your code
        SqlCommand sc = new SqlCommand("INSERT INTO employee (FirstName, LastName, DateOfBirth /*etc*/) VALUES (@firstName, @lastName, @dateOfBirth /*etc*/)", con);

        // best practice - always specify the database data type of the column you are using
        // best practice - check for valid values in your code and/or use a database constraint, if inserting NULL then use System.DbNull.Value
        sc.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 200){Value = string.IsNullOrEmpty(textBoxFirstName.Text) ? (object) System.DBNull.Value : (object) textBoxFirstName.Text});
        sc.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar, 200){Value = string.IsNullOrEmpty(textBoxLastName.Text) ? (object) System.DBNull.Value : (object) textBoxLastName.Text});

        // best practice - always use the correct types when specifying your parameters, in this case a string is converted to a DateTime type before being assigned to the SqlParameter.Value
        // note - this is not a very robust way to parse a date as the user is never notified in the event of failure, the purpose here is simply to show how to use parameters of various types
        DateTime dob;
        sc.Parameters.Add(new SqlParameter("@dateOfBirth", SqlDbType.Date){Value = DateTime.TryParse(textBoxDateOfBirth.Text, out dob) ? (object) dob : (object) System.DBNull.Value});

        // best practice - open your connection as late as possible unless you need to verify that the database connection is valid and wont fail and the proceeding code execution takes a long time (not the case here)
        con.Open();
        int o = sc.ExecuteNonQuery();
        MessageBox.Show(o + ":Record has been inserted");

        // the end of the using block will close and dispose the SqlConnection
        // best practice - end the using block as soon as possible to release the database connection
    }
}

---

最佳实践总结 ADO.NET

• 将所有数据库连接都包装在using块中，以便它们始终处于关闭状态&即使发生异常也要处理。有关使用语句的更多信息，请参见使用语句（C＃参考） li>
  
• 从app.config或web.config中检索名称的连接字符串（取决于应用程序类型）
  
  
  
  
  • 此要求对 System.configuration
  的程序集引用
  
  • 请参阅连接字符串和配置文件以获取有关如何构造配置文件的其他信息
  
  

  ---

  Best practice recap for working with ADO.NET

  • Wrap all database connections in a using block so they are always closed & disposed even in the event of an Exception. See using Statement (C# Reference) for more information on using statements
  • Retrieve the connection strings by name from the app.config or web.config (depending on the application type)
    • This requires an assembly reference to System.configuration
    • See Connection Strings and Configuration Files for additional information on how to structure your configuration file
    
    
    • 避免 sql注入攻击
    
    
    • 如果使用格式错误的文本（例如包括单引号），则应避免错误，该单引号与sql等效转义或开始字符串（varchar / nvarchar）
    
    
    • 离开d atabase提供程序重用查询计划（并非所有数据库提供程序都支持），可以提高效率
    
    
    • Avoid sql injection attacks
    • Avoid errors if malformed text is used like including a single quote which is the sql equivalent of escaping or starting a string (varchar/nvarchar)
    • Letting the database provider reuse query plans (not supported by all database providers) which increases efficiency
    
    
    • 为您的Sql参数赋予有意义的名称，就像在代码中执行变量一样
    
    
    • 指定要使用的列的数据库数据类型，这样可以确保未使用错误的参数类型可能会导致意外的结果
    
    
    • 在将传入的参数传递给命令之前，请先对其进行验证，其中有一个称为垃圾桶的表达式。尽早验证堆栈中的传入值
    
    
    • 在分配参数值时使用正确的类型，例如：不要分配DateTime的字符串值，而是将实际的DateTime实例分配给参数值
    
    
    • 请勿使用方法 AddWithValue ，主要原因是很容易忘记需要时指定参数类型或精度/小数位数。有关其他信息，请参见我们可以已经停止使用AddWithValue吗？
    
    
    • Give your Sql parameters meaningful names just like you do variables in your code
    • Specify the database data type of the column you are using, this ensures the wrong parameter types is not used which could lead to unexpected results
    • Validate your incoming parameters before you pass them into the command, there is an expression called garbage in garbage out. Validate incoming values as early as possible in the stack
    • Use the correct types when assigning your parameter values, example: do not assign the string value of a DateTime, instead assign an actual DateTime instance to the value of the parameter
    • Do not use the method AddWithValue, the main reason is it is very easy to forget to specify the parameter type or the precision/scale when needed. For additional information see Can we stop using AddWithValue already?
    
    
    • 尽可能早地打开连接并尽快将其关闭。这是使用任何外部资源时的一般准则
    
    
    • 永不共享数据库连接（例如：让一个单例主机共享数据库连接）。让您的代码总是在需要时创建一个新的数据库连接实例，然后由调用代码来处理它，并在完成时丢弃它。原因是
      
      
    • Open the connection as late as possible and close it as soon as possible. This is a general guideline when working with any external resource
    • Never share database connections (example: having a singleton host a shared database connection). Have your code always create a new database connection instance when needed and then have the calling code dispose of it and "throw it away" when it is done. The reason for this is
    
    
    1. 大多数数据库提供者都有某种连接池，因此在托管代码中这样做非常便宜
    
    
    2. 如果代码开始使用多个线程，它将消除以后的任何错误
    
    

    
    

    这篇关于参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！