CODEWITHSUNDEEP
c#sqloptimization
Andrew
Andrew

Reputation: 7788

Adding data to the database and how to make it right

I have been writing some simple c# and I usually use the same class and functions that insert and grab the data from the database.

For example, this is my function:

    public bool insert_and_ConfirmSQL(String Query, String comments)
    {
        bool success = false;
        NpgsqlCommand cmd = new NpgsqlCommand();
        NpgsqlConnection mycon = new NpgsqlConnection();
        string connstring = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};timeout=1000;CommandTimeout=120;", tbHost, tbPort, tbUser, tbPass, tbDataBaseName);
        mycon.ConnectionString = connstring;
        cmd = mycon.CreateCommand();
        cmd.CommandText = Query;
        mycon.Open();

        int temp = 0;
        try
        {
            temp = cmd.ExecuteNonQuery();
            success = true;
        }
        catch
        {
           if (mycon.State == ConnectionState.Open)
            {
                mycon.Close();
            }
        }

        return success;
    }

Now I know that this query is not safe against injections and I need to use prepared statements. But I don't understand how should I approach this when each of my queries is different? is there a "universal" function that inserts any query and "prepares" it?

Upvotes: 0

Views: 154

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

If you are looking for a more generalized way of performing inserts/updates/deletes, perhaps the following is suitable (uses SqlClient but is easily adapted for NpgsqlClient):

public static object ExecuteActionProcedure(System.Data.CommandType CommandType, string CommandText, string[] Parameters, object[] Values)
{
  try
  {
    using (SqlConnection con = new SqlConnection())
    {
      con.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnection"].ConnectionString;
      con.Open();
      using (SqlCommand cmd = new SqlCommand())
      {

        cmd.Connection = con;
        cmd.CommandType = CommandType;
        cmd.CommandText = CommandText;
        SqlParameter result = new SqlParameter();
        result.ParameterName = "ResultValue";
        result.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(result);
        for (int i = 0; i < Parameters.Length; i++)
        {
          cmd.Parameters.AddWithValue(Parameters[i], Values[i]);
        }
        cmd.ExecuteNonQuery();
        return (int)result.Value;
      }
    }
  }
    }
  }
  catch (Exception ex)
  {
    throw new Exception(ex.Message);
    return null;
  }
}

Call statement:

ExecuteActionProcedure(CommandType.StoredProcedure, "aspnet_SaveFullName", new string[] { "UserName", "FullName" }, new object[] { model.UserName, model.FullName });

Note that you can pass this method an SQL string that includes parameters as well as a stored procedure.

Upvotes: 2

HatSoft
HatSoft

Reputation: 11201

I would advise you to use it this way because the using statement takes care of diposing connection & command

Also in order to take safety against sqlinjection please SqlParameters (equivalent in Npgsl) to pass value

public void insert_and_ConfirmSQL(String Query, String comments) {

     using(NpgsqlConnection mycon = new NpgsqlConnection())
      {
        using(NpgsqlCommand cmd = mycon.CreateCommand())
        {
              string connstring = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};timeout=1000;CommandTimeout=120;", tbHost, tbPort, tbUser, tbPass, tbDataBaseName);
              mycon.ConnectionString = connstring;
              cmd = mycon.CreateCommand();
             cmd.CommandText = Query;
              mycon.Open();

            cmd.ExecuteNonQuery();
       }
     }

}

Upvotes: 3

Related Questions