使用 C# 的 SQL 插入查询

SQL Insert Query Using C#

提问by voskart

I'm having an issue at the moment which I am trying to fix. I just tried to access a database and insert some values with the help of C#

目前我遇到了一个问题，我正在尝试解决这个问题。我只是尝试在 C# 的帮助下访问数据库并插入一些值

The things I tried (worked)

我尝试过的事情（工作）

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES ('abc',      'abc', 'abc', 'abc')";

A new line was inserted and everything worked fine, now I tried to insert a row using variables:

插入了一个新行，一切正常，现在我尝试使用变量插入一行：

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id, @username, @password, @email)";

command.Parameters.AddWithValue("@id","abc")
command.Parameters.AddWithValue("@username","abc")
command.Parameters.AddWithValue("@password","abc")
command.Parameters.AddWithValue("@email","abc")

command.ExecuteNonQuery();

Didn't work, no values were inserted. I tried one more thing

没有用，没有插入值。我又尝试了一件事

command.Parameters.AddWithValue("@id", SqlDbType.NChar);
command.Parameters["@id"].Value = "abc";

command.Parameters.AddWithValue("@username", SqlDbType.NChar);
command.Parameters["@username"].Value = "abc";

command.Parameters.AddWithValue("@password", SqlDbType.NChar);
command.Parameters["@password"].Value = "abc";

command.Parameters.AddWithValue("@email", SqlDbType.NChar);
command.Parameters["@email"].Value = "abc";

command.ExecuteNonQuery();

May anyone tell me what I am doing wrong?

谁能告诉我我做错了什么？

Kind regards

亲切的问候

EDIT:

编辑：

in one other line I was creating a new SQL-Command

在另一行中，我正在创建一个新的 SQL-Command

var cmd = new SqlCommand(query, connection);

Still not working and I can't find anything wrong in the code above.

仍然无法正常工作，我在上面的代码中找不到任何错误。

采纳答案by Andrew Paes

I assume you have a connection to your database and you can not do the insert parameters using c #.

我假设你有一个到你的数据库的连接，你不能使用 c# 来做插入参数。

You are not adding the parameters in your query. It should look like:

您没有在查询中添加参数。它应该看起来像：

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");

command.ExecuteNonQuery();

Updated:

更新：

using(SqlConnection connection = new SqlConnection(_connectionString))
{
    String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

    using(SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@id", "abc");
        command.Parameters.AddWithValue("@username", "abc");
        command.Parameters.AddWithValue("@password", "abc");
        command.Parameters.AddWithValue("@email", "abc");

        connection.Open();
        int result = command.ExecuteNonQuery();

        // Check Error
        if(result < 0)
            Console.WriteLine("Error inserting data into Database!");
    }
}

回答by Marc Gravell

The most common mistake (especially when using express) to the "my insert didn't happen" is : looking in the wrong file.

最常见的错误（尤其是在使用 express 时）“我的插入没有发生”是：查看错误的文件。

If you are using file-based express (rather than strongly attached), then the file in your project folder (say, c:\dev\myproject\mydb.mbd) is notthe file that is used in your program. When you build, that file is copied - for example to c:\dev\myproject\bin\debug\mydb.mbd; your program executes in the context of c:\dev\myproject\bin\debug\, and so it is herethat you need to look to see if the edit actually happened. To check for sure: query for the data inside the application(after inserting it).

如果您使用基于文件的 express（而不是强附加），则项目文件夹中的文件（例如c:\dev\myproject\mydb.mbd）不是程序中使用的文件。当您构建时，该文件被复制 - 例如到c:\dev\myproject\bin\debug\mydb.mbd; 您的程序在 的上下文中执行c:\dev\myproject\bin\debug\，因此您需要在此处查看编辑是否确实发生。确定检查：查询应用程序内的数据（插入后）。

回答by theLaw

Try

尝试

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username, @password, @email)";
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(query, connection))
{
    //a shorter syntax to adding parameters
    command.Parameters.Add("@id", SqlDbType.NChar).Value = "abc";

    command.Parameters.Add("@username", SqlDbType.NChar).Value = "abc";

    //a longer syntax for adding parameters
    command.Parameters.Add("@password", SqlDbType.NChar).Value = "abc";

    command.Parameters.Add("@email", SqlDbType.NChar).Value = "abc";

    //make sure you open and close(after executing) the connection
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

回答by robin

static SqlConnection myConnection;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        myConnection = new SqlConnection("server=localhost;" +
                                                      "Trusted_Connection=true;" +
             "database=zxc; " +
                                                      "connection timeout=30");
        try
        {

            myConnection.Open();
            label1.Text = "connect successful";

        }
        catch (SqlException ex)
        {
            label1.Text = "connect fail";
            MessageBox.Show(ex.Message);
        }
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        String st = "INSERT INTO supplier(supplier_id, supplier_name)VALUES(" + textBox1.Text + ", " + textBox2.Text + ")";
        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            MessageBox.Show("insert successful");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

回答by Krishna

class Program
{
    static void Main(string[] args)
    {
        string connetionString = null;
        SqlConnection connection;
        SqlCommand command;
        string sql = null;

        connetionString = "Data Source=Server Name;Initial Catalog=DataBaseName;User ID=UserID;Password=Password";
        sql = "INSERT INTO LoanRequest(idLoanRequest,RequestDate,Pickupdate,ReturnDate,EventDescription,LocationOfEvent,ApprovalComments,Quantity,Approved,EquipmentAvailable,ModifyRequest,Equipment,Requester)VALUES('5','2016-1-1','2016-2-2','2016-3-3','DescP','Loca1','Appcoment','2','true','true','true','4','5')";
        connection = new SqlConnection(connetionString);

        try
        {
            connection.Open();
            Console.WriteLine(" Connection Opened ");
            command = new SqlCommand(sql, connection);                
            SqlDataReader dr1 = command.ExecuteReader();         

            connection.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Can not open connection ! ");
        }
    }
}

回答by Yasir Naeem

private void button1_Click(object sender, EventArgs e)
    {
        String query = "INSERT INTO product (productid, productname,productdesc,productqty) VALUES (@txtitemid,@txtitemname,@txtitemdesc,@txtitemqty)";
        try
        {
            using (SqlCommand command = new SqlCommand(query, con))
            {

                command.Parameters.AddWithValue("@txtitemid", txtitemid.Text);
                command.Parameters.AddWithValue("@txtitemname", txtitemname.Text);
                command.Parameters.AddWithValue("@txtitemdesc", txtitemdesc.Text);
                command.Parameters.AddWithValue("@txtitemqty", txtitemqty.Text);


                con.Open();
                int result = command.ExecuteNonQuery();

                // Check Error
                if (result < 0)
                    MessageBox.Show("Error");

                MessageBox.Show("Record...!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();
                loader();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }
    }

回答by Snookger Isolation

public static string textDataSource = "Data Source=localhost;Initial 
Catalog=TEST_C;User ID=sa;Password=P@ssw0rd";
public static bool ExtSql(string sql) {
    SqlConnection cnn;
    SqlCommand cmd;
    cnn = new SqlConnection(textDataSource);
    cmd = new SqlCommand(sql, cnn);
    try {
        cnn.Open();
        cmd.ExecuteNonQuery();
        cnn.Close();
        return true;
    }
    catch (Exception) {
        return false;
    }
    finally {
        cmd.Dispose();
        cnn = null;
        cmd = null; 
    }
}

回答by jonathana

I have just wrote a reusable method for that, there is no answer here with reusable method so why not to share...
here is the code from my current project:

我刚刚为此编写了一个可重用的方法，这里没有可重用方法的答案，所以为什么不分享...
这是我当前项目中的代码：

public static int ParametersCommand(string query,List<SqlParameter> parameters)
{
    SqlConnection connection = new SqlConnection(ConnectionString);
    try
    {
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {   // for cases where no parameters needed
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }

            connection.Open();
            int result = cmd.ExecuteNonQuery();
            return result;
        }
    }
    catch (Exception ex)
    {
        AddEventToEventLogTable("ERROR in DAL.DataBase.ParametersCommand() method: " + ex.Message, 1);
        return 0;
        throw;
    }

    finally
    {
        CloseConnection(ref connection);
    }
}

private static void CloseConnection(ref SqlConnection conn)
{
    if (conn.State != ConnectionState.Closed)
    {
        conn.Close();
        conn.Dispose();
    }
}