jeudi 21 février 2019

How to add a static parameter to a sql query using sqlcommand C#

Is it possible to add a static parameter to a sql query using a sql command in C#? I am trying to either INSERT or UPDATE a database table depending on if a client already exists. I am trying to make the 'UpdateType' = 1 if INSERT is executed OR 2 if UPDATE is executed.

Is there a way to remove the dynamic Parameter @UpdateType and just a static value 1 or 2 for either INSERT or UPDATE queries?

Here is an example of the query: .....

string sqlQuery = "IF NOT EXISTS(SELECT 1 FROM Client WHERE [ClientID] =" +  
    "@ClientID) " +  
    "INSERT INTO Client (ClientId, LastName, FirstName, UpdateType)" + 
    "VALUES " +  
    "(@ClientID, @LastName, @FirstName, @UpdateType)" +
    "ELSE " +  
    "UPDATE Client " + 
    "SET [ClientID] = @ClientID, [LastName] = @LastName, [FirstName] = "+ 
    "@FirstName, [UpdateType] = @UpdateType"+
    "WHERE ClientID = ClientID;";

using (SqlCommand MyCommand = new SqlCommand(sqlQuery, SQLConn))
{
    MyCommand.Parameters.Add("@ClientID", SqlDbType.VarChar);
    MyCommand.Parameters.Add("@LastName", SqlDbType.VarChar);
    MyCommand.Parameters.Add("@FirstName", SqlDbType.VarChar);
    MyCommand.Parameters.Add("@UpdateType", SqlDbType.Int);

    try{
        SQLConn.Open();
        foreach (DataRow row in SourceData.Rows)
        {
            MyCommand.Parameters["@ClientID"].Value = row["ClientId"].ToString();
            MyCommand.Parameters["LastName"].Value = row["LastName"].ToString();
            MyCommand.Parameters["@FirstName"].Value = row["FirstName"].ToString();
            MyCommand.Parameters["@UpdateType"].Value = Convert.ToInt32(row["UpdateType"]);
            MyCommand.ExecuteNonQuery();
        }
     }catch{
         ....
     }    
}

...... Any help on the matter is greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire