0

How do I check if value already exists in a SQL Server database when I insert values using C# ? Can anybody give advise? Thanks

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(string Name, string ID,, form fo)
{
    string myConnectionString;
    DataTable dt = new DataTable();

    // connect to DB
    myConnectionString = "Data Source=xxxxxx"
    string cmdText = "select ID from [db.tablename] where ID=\'" + fo.ID + "\'";

    // Validation   
    if (ModelState.IsValid)
    {
        if (cmdText != null)
        {
            return View(fo);
        }
        else
        {
            using (SqlConnection sqlConn = new SqlConnection(myConnectionString))
            {
                string sql = "insert into [db.tablename] (ID,Name) Values('" + ID + "','" + Name + ")";

                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                {
                    sqlCmd.CommandType = CommandType.Text;
                    sqlConn.Open();

                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd))
                    {
                        sqlAdapter.Fill(dt);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ericso
  • 109
  • 1
  • 9
  • I'm guessing you mean `[.net-core]` and not `[.net]` and `[core]`? – ProgrammingLlama Dec 02 '20 at 03:16
  • 2
    What is the point of `if (cmdText != null)`? It's never ever ever going to be null. – ProgrammingLlama Dec 02 '20 at 03:17
  • sorry about that, i dont have any idea for checking if already exisis in db – ericso Dec 02 '20 at 03:19
  • 2
    1) To check if an item already exists: just query for it! 2) Perhaps you're really looking for an [upsert](https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/). 3) In any case, look at this example; it should completely answer your question: [Update if the name exists else insert - in SQL Server](https://stackoverflow.com/a/39250202/421195) – paulsm4 Dec 02 '20 at 03:22
  • 1
    Sorry, my point is that `if (cmdText != null)` checks if the _string_ is `null`. Clearly it can never be null because you're creating it and assigning a value to it 4 lines above. While it's a string that contains the text of an SQL query, it's only a string so it doesn't interact with the database. – ProgrammingLlama Dec 02 '20 at 03:35
  • 2
    As @John has said, your `if (cmdText != null)` is not needed. You've said `cmdText = "This text"; if (cmdText != null)`, which clearly can't happen, so the `if` before the `else` is meaningless.. You've also duplicated your `using` statement, which is redundant and unnecessary. To find out if a row already exists, you use `SELECT` to see if it exists. (Oh, I missed one. You also can't use `sqlAdapter.Fill(ds)` when you've not populated anything with a `SELECT` to assign to it.) – Ken White Dec 02 '20 at 03:53
  • 3
    https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection - you need to read this, now. **Then** read https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15 . – mjwills Dec 02 '20 at 03:53
  • Does this answer your question? [Check if record in a table exist in a database through ExecuteNonQuery](https://stackoverflow.com/questions/17903092/check-if-record-in-a-table-exist-in-a-database-through-executenonquery) – Jamshaid K. Dec 02 '20 at 04:21
  • 1
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) – marc_s Dec 02 '20 at 05:52

0 Answers0