通过C＃中使用ado.net将值插入SQL Server数据库 [英] Inserting values into a SQL Server database using ado.net via C#

问题描述

我创建了一个简单的程序来将值插入表 [注册] ，但我不断收到错误

I have created a simple program to insert values into the table [regist], but I keep getting the error

附近有语法错误'）的

在 cmd.ExecuteNonQuery（）; ：

 private void button1_Click(object sender, EventArgs e)
 {
      SqlConnection cn = new SqlConnection("Data Source=DELL-PC;initial catalog=AdventureWorks2008R2 ; User ID=sa;Password=sqlpass;Integrated Security=SSPI;");

      SqlCommand cmd = new SqlCommand("INSERT INTO dbo.regist (" + " FirstName, Lastname, Username, Password, Age, Gender,Contact, " + ") VALUES (" + " @textBox1.Text, @textBox2.Text, @textBox3.Text, @textBox4.Text, @comboBox1.Text,@comboBox2.Text,@textBox7.Text" + ")", cn);

      cn.Open();
      cmd.ExecuteNonQuery();
      cn.Close();
}

我是新来这个，我真的很困惑。

I am new to this and I am really confused.

推荐答案

正如我在评论说 - 你应该的总是使用您的查询参数 - 永远不要串联在一起您的SQL语句自己。

As I said in comments - you should always use parameters in your query - NEVER EVER concatenate together your SQL statements yourself.

另外：我会推荐给Click事件处理程序从实际的code分离插入数据

Also: I would recommend to separate the click event handler from the actual code to insert the data.

所以，我会重写你的code是像

So I would rewrite your code to be something like

在你的网页的code-隐藏文件（<$ C C $> yourpage.aspx.cs ）

In your web page's code-behind file (yourpage.aspx.cs)

private void button1_Click(object sender, EventArgs e)
{
      string connectionString = "Data Source=DELL-PC;initial catalog=AdventureWorks2008R2 ; User ID=sa;Password=sqlpass;Integrated Security=SSPI;";

      InsertData(connectionString,
                 textBox1.Text.Trim(),  -- first name
                 textBox2.Text.Trim(),  -- last name
                 textBox3.Text.Trim(),  -- user name
                 textBox4.Text.Trim(),  -- password
                 Convert.ToInt32(comboBox1.Text),  -- age
                 comboBox2.Text.Trim(), -- gender
                 textBox7.Text.Trim() );  -- contact
}

在其他一些code（如 databaselayer.cs ）：

In some other code (e.g. a databaselayer.cs):

private void InsertData(string connectionString, string firstName, string lastname, string username, string password
                        int Age, string gender, string contact)
{
    // define INSERT query with parameters
    string query = "INSERT INTO dbo.regist (FirstName, Lastname, Username, Password, Age, Gender,Contact) " + 
                   "VALUES (@FirstName, @Lastname, @Username, @Password, @Age, @Gender, @Contact) ";

    // create connection and command
    using(SqlConnection cn = new SqlConnection(connectionString))
    using(SqlCommand cmd = new SqlCommand(query, cn))
    {
        // define parameters and their values
        cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstName;
        cmd.Parameters.Add("@Lastname", SqlDbType.VarChar, 50).Value = lastName;
        cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = userName;
        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = password;
        cmd.Parameters.Add("@Age", SqlDbType.Int).Value = age;
        cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 50).Value = gender;
        cmd.Parameters.Add("@Contact", SqlDbType.VarChar, 50).Value = contact;

        // open connection, execute INSERT, close connection
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}

code是这样的：

Code like this:

• 在不容易受到SQL注入式攻击
• 在执行SQL Server上更好（因为查询解析一旦进入执行计划，然后缓存并重复使用以后）
• 分离事件处理程序（code-隐藏文件）从实际的数据库code（把事情属于他们的地方 - 有助于避免增持code-屁股与吨面条$ C $的C，从处理UI事件数据库访问所做的一切！ - 不会一个好的设计）

• is not vulnerable to SQL injection attacks
• performs much better on SQL Server (since the query is parsed once into an execution plan, then cached and reused later on)
• separates the event handler (code-behind file) from your actual database code (putting things where they belong - helping to avoid "overweight" code-behinds with tons of spaghetti code, doing everything from handling UI events to database access - NOT a good design!)

这篇关于通过C＃中使用ado.net将值插入SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！