0

I've tried to use the ExecuteNonQuery() to update a table using the SqlCommand. However, nothing happened. I'd like to know what happened. I've run the same command in SSMS and everything worked successfully. Don't know why it doesn't work on my C# code (.NET 4.6).

Thank you!

AppUser Schema

Department Schema

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp_StoredProc
{
    class Program
    {
        static void Main(string[] args)
        {
            string columnName = "DepartmentId";
            string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            string commandText = @"IF EXISTS(SELECT * FROM sys.columns where object_id=OBJECT_ID('Test.dbo.AppUser') and name = '"+columnName+"') " +
                "UPDATE Test.dbo.AppUser SET ["+columnName+ "] = @deptId where Id = @id";
            int rowUpdated = 0;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    SqlParameter param2 = new SqlParameter("@deptId", 2);
                    SqlParameter param3 = new SqlParameter("@id", 4);
                    cmd.Parameters.Add(param2);
                    cmd.Parameters.Add(param3);

                    cmd.CommandText = commandText;
                    cmd.Connection = conn;

                    conn.Open();
                    rowUpdated = cmd.ExecuteNonQuery();
                    Console.WriteLine(rowUpdated);
                    Console.ReadKey();
                }
            }
        }
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Casey
  • 25
  • 5
  • What does the `commandText` look like when you inspect it, before calling `ExecuteNonQuery` ? – marc_s Apr 18 '22 at 16:53
  • ```"IF EXISTS(SELECT * FROM sys.columns where object_id=OBJECT_ID('Test.dbo.AppUser') and name = 'DepartmentId') UPDATE Test.dbo.AppUser SET [DepartmentId] = @deptId where Id = @id"``` – Casey Apr 18 '22 at 16:55
  • 1
    Have you tried removing the `EXISTS` statement, does the `AppUser` table get updated? – Trevor Apr 18 '22 at 16:57
  • Hi Trevor, it worked! Shall I execute `EXISTS` command separately to prevent SQL injection? – Casey Apr 18 '22 at 17:04
  • What is in `ConnectionString`? If you're not in the context of the `Test` database then `sys.columns` will be in whatever database context you have. So try `Test.sys.columns` or make sure you're connecting to the right database in the first place... – Aaron Bertrand Apr 18 '22 at 17:45

0 Answers0