How do i add my customer data into a SQL database?

Question

This is my CustomerRegister class, but I cant seem to input data from my addressTextBox into the CustomerTbl.

DataBase dbObj = new DataBase();

string selStr = "Update CustomerTbl Set customer_address = '" + addressTextBox.Text + "' Where custID = " + "NULL";
 int i = dbObj.ExecuteNonQuery(selStr);

This is my DataBase class but return comdObj.ExecuteNonQuery(); doesnt work as there is not such custID named NULL. So how do i program in such a way so that i am able to constantly update the database when a new user registers?

class DataBase
    {
        string connStr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\OOPG\Banking Mini Project Raynard\Banking Mini Project Raynard\Database1.mdf;Integrated Security = True";
        SqlConnection connObj;
        SqlCommand comdObj;
        SqlDataReader dR;

        public DataBase()
        {
            connObj = new SqlConnection(connStr);
            connObj.Open();
        }
        public SqlDataReader ExecuteReader(string selStr)
        {
            comdObj = new SqlCommand(selStr, connObj);
            dR = comdObj.ExecuteReader();
            return dR;
        }
        public int ExecuteNonQuery(string sqlStr)
        {
            comdObj = new SqlCommand(sqlStr, connObj);
            return comdObj.ExecuteNonQuery();
        }
    }

important: your approach to SQL is *ripe* for SQL injection. You should **never** concatenate user input to create SQL (it is also bad for i18n/l10n and other reasons). Always always always: use parameters. This is **really really** important (someone could destroy or exfil your data trivially, otherwise; it is quite honestly the easiest way to hack an app) — Marc Gravell, Jan 25 '21 at 14:01
Outside the glaring sql injection weakness: Really you should not try to reuse connections, commands, and other ado.net instances. Create, use, dispose is what is generally considered best practice. — Igor, Jan 25 '21 at 14:02
See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204) to see how to use sql parameters. See [Exploits of a Mom](https://xkcd.com/327/) for a funny cartoon illustrating how sql injection can harm your application data. — Igor, Jan 25 '21 at 14:04
See [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) — Charlieface, Jan 25 '21 at 14:16
Also see [what's the issue with AttachDbFilename](https://stackoverflow.com/questions/11178720/whats-the-issue-with-attachdbfilename) — Charlieface, Jan 25 '21 at 14:17

score 2 · Accepted Answer · answered Jan 25 '21 at 14:10

First you should create a connection to SQL database before executing any query. After then you should be able to insert data before updating any data into database. After you insert data successfully you can update data using above command text. Here is some sample code for inserting data for registering customer.

using (SqlCommand command = new SqlCommand())
{
    command.Connection = connection;            // <== lacking
    command.CommandType = CommandType.Text;
    command.CommandText = "INSERT into CustomerTbl (CustId, Name, Address) VALUES (@CustId, @Name, @Address)";
    command.Parameters.AddWithValue("@CustId", name);
    command.Parameters.AddWithValue("@Name", userId);
    command.Parameters.AddWithValue("@Address", idDepart);

    try
    {
        connection.Open();
        int recordsAffected = command.ExecuteNonQuery();
    }
    catch(SqlException)
    {
        // error here
    }
    finally
    {
        connection.Close();
    }
}

minor observation: since we can't see the lifetime of this connection, it might be inappropriate to be using `Open()`/`Close()` like this (if we were *creating* the connection, then yes: a call to `Open()` would be warranted) — Marc Gravell, Jan 25 '21 at 14:14

score 0 · Answer 2 · answered Jan 25 '21 at 14:06

If you're adding a record, you're going to need to INSERT, not UPDATE. For example (here using "Dapper" to do all the heavy work, including parameter handling):

using Dapper;
//...
void UpsertAddress(int? id, string address)
{
    if (id is null)
    {
        connection.Execute("insert CustomerTbl (customer_address) values (@address);",
            new { address }); // possibly using the OUTPUT clause to fetch an IDENTITY
    }
    else
    {
        connection.Execute(
            "update CustomerTbl set customer_address = @address where custID = @id;",
            new { id, address });
    }
}

How do i add my customer data into a SQL database?

2 Answers2