在c#中更新数据库字段SQL

本文关键字：字段 SQL 数据库 更新 | 更新日期: 2023-09-27 18:07:45

我尝试了很多方法，但都没有结果。我的问题是，正如标题所说，如何使用SQL更新数据库中的字段?由于我是编程和SQL的初学者，我将复制我的整个代码如下，不知道你可能需要什么信息:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using HPJFRMS;
namespace HPJFRMS
{
    public partial class HomeFRM : Form
    {
        private string conn;
        MySqlConnection connect;
        string _naam = "";
        Form _Loginfrm;
        public HomeFRM(Form logFrom, string _name)
        {
            _Loginfrm = logFrom;
            InitializeComponent();
            lbWelkom.Text = "welkom " + _name;
            _naam = _name;
        }
        private void HomeFRM_Load(object sender, EventArgs e)
        {
            tmCheck.Enabled = true;
        }
        private bool Todo_ophalen()
        {
            db_connection();
            MySqlCommand cmdRead = new MySqlCommand();
            cmdRead.CommandText = "SELECT `todo` FROM `user` WHERE `username` LIKE '" + _naam + "'";
            cmdRead.Connection = connect;
            MySqlDataReader tdOphalen = cmdRead.ExecuteReader();
            if (tdOphalen.Read())
            {
                tbTodo.Text = tdOphalen.GetString(0);
                connect.Close();
                return true;
            }
            else
            {
                connect.Close();
                return false;
            }
        }
        private void db_connection()
        {
            try
            {
                conn = "Server=127.0.0.1;Database=users;Uid=root;Pwd=;";
                connect = new MySqlConnection(conn);
                connect.Open();
            }
            catch (MySqlException e)
            {
                throw;
            }
            finally
            {
                lbStatus.ForeColor = Color.Red;
            }
        }
        private void btBewerk_Click(object sender, EventArgs e)
        {
            if (btBewerk.Text == "Bewerken")
            {
                tbTodo.ReadOnly = false;
                btBewerk.Text = "Opslaan";
                tmCheck.Enabled = false;
            }
            else
            {
                /* HERE COMES THE "UPDATE" CODE */
            }
        }
        private void tmCheck_Tick(object sender, EventArgs e)
        {
            try
            {
                bool T = Todo_ophalen();
                if (T)
                {
                    lbStatus.ForeColor = Color.Green;
                    lbStatus.Text = "Online";
                }
            }
            catch
            {
                lbStatus.ForeColor = Color.Red;
                lbStatus.Text = "Offline";
            }
        }
    }
}

在c#中更新数据库字段SQL

有不同的方法，您可以使用其中任何一个。

方法1:使用简单的SQL查询 
public void Update(Int UserId,String UserName  )
{
    SqlConnection con = new SqlConnection("Your Connection String");  
    con.Open();  
    string str = " UPDATE [dbo].[User] SET [UserName] = "+UserName  +" WHERE   [UserId] ="+ UserId+"";
    SqlCommand cmd = new SqlCommand(str , con);
    cmd.ExecuteNonQuery();
    con.Close();
}
方法2:使用存储过程

首先在数据库中执行存储过程。

例子
CREATE PROCEDURE [dbo].[UpdateUser]   
    @UserId int,   
    @UserName varchar(25)   
AS   
BEGIN   
    UPDATE [dbo].[User] 
    SET [UserName] = @UserName 
    WHERE [UserId] = @UserId   
END   
public void Update(Int UserId,String UserName  )
{ 
    SqlConnection con = new SqlConnection("Your Connection String");  
    con.Open();
    SqlCommand cmd = new SqlCommand("UpdateUser", con); //UpdateUser is the name of stored procedure you created
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("UserName ", UserName );
    cmd.Parameters.AddWithValue("UserId", UserId);
    cmd.ExecuteNonQuery();
    con.Close();
 }

下面是一个更新的示例方法:

public void UpdateUser(User userToUpdate)
{
    try
    {
        string sqlStatement = @"UPDATE USERS " +
                               "SET DisplayName = @DisplayName, Username = @Username" +
                               "WHERE Id = @Id";
        using (SqlConnectionconn = new SqlConnection("connection string here"))
        using (SqlCommand cmd = new SqlCommand(sqlStatement, conn))
        {
            cmd.Parameters.Add(new SqlParameter("@Id", userToUpdate.Id));
            cmd.Parameters.Add(new SqlParameter("@DisplayName", userToUpdate.DisplayName));
            cmd.Parameters.Add(new SqlParameter("@UserName", userToUpdate.UserName));
            cmd.ExecuteNonQuery();
        }
    }
    catch (DbException ex)
    {
        throw ExceptionHandler.CreateSystemException(ex, ErrorStrings.DATABASE_ERROR);
    }
}

这应该是你的入门，但你真的需要仔细阅读这个主题，因为解释正确的做法对这里的答案来说太长了。

这可能是你需要的

MySqlConnection con = new MySqlConnection(conStr);
try 
{
    con.Open();
    MySqlCommand cmd = new MySqlCommand(con);
    cmd.CommandText = "UPDATE table_name SET field_name_1 = ?param1, field_name_2 = ?param2 WHERE id = ?id";
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("?param1", value1);
    cmd.Parameters.AddWithValue("?param2", value2);
    cmd.Parameters.AddWithValue("?id", value3);
    cmd.ExecuteNonQuery(); 
}
catch (MySqlException ee)
{
     MessageBox.Show(ee.Message);
}
finally
{
    con.Close();
    con.Dispose();
}
相关文章：