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

Question

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?

score 1 · Answer 1 · answered Nov 05 '12 at 12:31

1

I would suggest you use :

command.Parameters.Add( ... );

answered Nov 05 '12 at 12:31

Mohnkuchenzentrale

5,745
4
30
41

score 1 · Answer 2 · answered Nov 05 '12 at 12:36

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.

score 0 · Answer 3 · edited May 23 '17 at 12:12

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.

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

3 Answers3