如何在C＃中插入SQL [英] How can I insert with SQL in C#

问题描述

I want to know how I can do an insert in an sql database with C #

< b>我尝试了什么：

What I have tried:

string setting = ConfigurationManager.AppSettings["setting1"];
            string conn = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
            using (SqlConnection sqlConn = new SqlConnection(conn))
            {
                //string sqlQuery2 = @"UPDATE testenumserie " + "SET Estado = " + comboBox1.Text + " WHERE NumSerie = '" + textBox2.Text + "'" ;
                string sqlQuery2 = @"INSERT into dbo.testenumserie(Estado) VALUES ('" + comboBox1.Text + "')" + " WHERE NumSerie = '" + textBox2.Text + "'";
                MessageBox.Show(sqlQuery2);
                SqlCommand cmd2 = new SqlCommand(sqlQuery2, sqlConn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable table2 = new DataTable();

我试过这段代码，但插入不起作用。

I tried this code but the insert did not work.

推荐答案

以下是解决问题的代码。

Hi,

Below is the code to solve the problem.

string connectionstring = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connectionstring);
sqlConn.Open();
 
string sqlQuery2 = @"UPDATE dbo.testenumserie SET ESTADO = @estado WHERE NumSerie = @numSeries"; 

SqlCommand SQLcm = new SqlCommand();
SQLcm.Connection = sqlConn;
SQLcm.CommandText = sqlQuery2;
SQLcm.CommandType = CommandType.Text;
SQLcm.Parameters.AddWithValue("@estado", comboBox1.Text);
SQLcm.Parameters.AddWithValue("@numSeries", textBox2.Text);
SQLcm.ExecuteNonQuery();
sqlConn.Close();

Oki首先考虑读取sql注入，因为你真的不想使用变量直接来自查询中的用户界面。



更新特定数量的Estado

Oki first of all, consider reading up on sql injection, because you REALLY don't want to use variables directly from a user interface in a query.

Update Estado in a specific numseries

string estado = CheckMyUiValues(comoBox1.Text); //Must make that method ;)
string nums = CheckMyUiValues(comoBox1.Text);

var sql = "UPDATE dbo.testenumserie SET ESTADO = '{0}' WHERE NUMSERIE = '{1}'";
using(var cn = new GetOpenConnection()){   //Could have a method to make here too
    using(var cmd = new SqlCommand(cn, string.format(sql, estado, nums)){
        cmd.ExecuteNonQuery();
    }
}

或稍微粗暴的方式

or in a somewhat more crude way

string estado = comoBox1.Text; 
string nums = comoBox1.Text;
//TODO: Implement ui variable checking
var sql = "UPDATE dbo.testenumserie SET ESTADO = '{0}' WHERE NUMSERIE = '{1}'";
string conn = "put your connection string details in here";
using(var cn = new SqlConnection(conn)){   
    using(var cmd = new SqlCommand(cn, string.Format(sql, estado, nums)){
        cmd.ExecuteNonQuery();
    }
}

要执行你的sql你会做cmd2.ExecuteNonQuery（）;.你不需要DataAdapter也不需要DataTable。



您还需要更改为使用参数。您的代码现在非常不安全。您的数据库很容易被黑客攻击。而是执行以下操作：

To execute your sql you would do cmd2.ExecuteNonQuery();. You do not need the DataAdapter nor the DataTable.

You'll also want to change to using parameters. Your code is very insecure right now. Your database could be easily hacked. Instead do something like:

string sqlQuery2 = @"INSERT into dbo.testenumserie(Estado) VALUES (@estado) WHERE NumSerie = @numSeries";            
...
cmd2.Parameters.AddWithValue("@estado", comboBox1.Text);
cmd2.Parameters.AddWithValue("@numSeries", textBox2.Text);
cmd2.ExecuteNonQuery();

此外，您应该使用更好的名称来命名控件。例如，您的textBox2应命名为txtNumSeries。这将为您节省大量时间以正确命名控件。

Also, you should name your controls using better names. For example, your textBox2 should be named txtNumSeries. It will save you a lot of time later on to name your controls properly.

这篇关于如何在C＃中插入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！