C#: How to insert multiple param dynamically in Insert Statement in MySQL

In My scenario, I want to insert the sql statements having 15 columns data.Is any other way to handle the parameter for insert statements?

    public bool GetCustomersList(ref DataSet dataset, String x, String y, string z, string x1, string y1, string z1 )
    {
        MySqlConnection SQLConn = GetConnection();
        try
        {
            string get_records;
            if (SQLConn.State == ConnectionState.Closed)
            {
                SQLConn.Open();
            }
            if (searchtype == "x")
            {
                get_records = "INSERT into table values(x, y,z,x1,y1,z1);
            }
            MySqlCommand command = new MySqlCommand(get_records, SQLConn);
            MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(command);
            mySqlAdapter.Fill(dataset);

            if (SQLConn.State == ConnectionState.Open)
            {
                SQLConn.Close();
            }
            return true;
        }
        catch (Exception )
        {
            if (SQLConn.State == ConnectionState.Open)
            {
                SQLConn.Close();
            }
            return false;
        }
    }

Here the parameter may be extended to 15 or more?How to handle this situation in asp.net?

3 Answers
  1. This will do that.

                List<SqlParameter> myParamList = new List<SqlParameter>();
                SqlParameter myParam = default(SqlParameter);
    
    
                myParam = new SqlParameter("@RoomID", SqlDbType.Int);
                myParam.Value = x
                myParamList.Add(myParam);
    

    Basically, you create your parameter list, and for each parameter add it using the new SqlParameter, noting the Item name & the item type.

    Then add the parameter list to the command.

    Ryan McDonough2012-11-05 12:36:24
  2. I would suggest you use :

    command.Parameters.Add( ... );
    
    2012-11-05 12:31:14
  3. From MSDN:

    Use the CreateParameter method to create a new Parameter object with a specified name, type, direction, size, and value. Any values you pass in the arguments are written to the corresponding Parameter properties.

    Using inline parameters is unsafe and can result in sql injection. Using SqlParameter instead is the prefered and more secure way of providing parameters to sql statements in .Net.

    In addition, if you want to be more pure and the same get rid of depending on specific implementations from your code, use more interfaces instead, for example:

    using (var connection = GetConnection())
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = "INSERT into table(x, y) VALUES (@x, @y)";
            command.Parameters.Add(CreateParameter(command, "@x", x, DbType.String));
            command.Parameters.Add(CreateParameter(command, "@y", y, DbType.String));
            command.ExecuteNonQuery();
        }
    }
    

    where your custom CreateParameter wrapper has following implementation:

    private IDataParameter CreateParameter(IDbCommand command, string name, object value, DbType type)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        parameter.DbType = type;
        return parameter;
    }
    

    The same, you throw out redundant specific class types like: MySqlCommand or SqlParameter. Besides, there is not necessity of using try{} catch{}. Using statement will close the connection on dispose, even when exception is thrown. If you don't need, do not open and close the connection on/after each request. Take the advantage of connection pooling.

    Jarosław Waliszko2012-11-05 13:48:14
Related Articles
You Might Also Like