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?

Mohnkuchenzentrale · Accepted Answer · 2012-11-05 12:31:14Z

1

I would suggest you use :

command.Parameters.Add( ... );

answered Nov 5, 2012 at 12:31

Mohnkuchenzentrale

5,9154 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryan McDonough · Accepted Answer · 2012-11-05 12:36:24Z

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.

answered Nov 5, 2012 at 12:36

Ryan McDonough

10k3 gold badges60 silver badges81 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:12:20Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related