Adding Multiple Parameterized Variables to a Database in C#

Adding multiple parameterized variables to a database in c#

Since you are using c# and sql server 2008, you can use a table valued parameter to insert multiple rows to your database.
Here is a short description on how to do this:

First, you need to create a user defined table type:

CREATE TYPE MyTableType AS TABLE
(
    Col1 int,
    Col2 varchar(20) 
)
GO

Then, you need to create a stored procedure that will accept this table type as a parameter

CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- NOTE: table valued parameters must be Readonly!
)
AS

INSERT INTO MyTable (Col1, Col2)
SELECT Col1, Col2 
FROM @MyTable

GO

Finally, execute this stored procedure from your c# code:

DataTable dt = new DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Columns.Add("Col2", typeof(string));

// Fill your data table here

using (var con = new SqlConnection("ConnectionString"))
{
    using(var cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Add multiple SQL values with same parameterized query?

We can do what you want by parsing the headers into parameters in a pre-processing step. I have also removed the explicit transaction because every single insert already gets an implicit transaction by default (why pay the performance penalty of two transactions?).

using (var command = new SqlCommand()) {
    command.CommandText =
        "INSERT INTO " + table.Title + " ("
      + string.Join(", ", table.Headers)
      + ") VALUES ("
      + string.Join(", ", table.Headers.Select(x => "@" + x))
      + ");";
    command.Connection = connection;

    foreach (var header in table.Headers) {
        /*
             Add all parameters as strings. One could choose to infer the
             data types by inspecting the first N rows or by using some sort
             of specification to map the types from A to B.
         */
        command.Parameters.Add("@" + header, typeof(string));
    }

    foreach (var row in table.RowData) {
        for (var i = 0; i < table.Headers.Count(); i++) {
            if (!string.IsNullOrEmpty(row.ElementAt(i))) {
                command.Parameters["@" + table.Headers.ElementAt(i)].Value = row.ElementAt(i);
            }
            else {
                command.Parameters["@" + table.Headers.ElementAt(i)].Value = DBNull.Value;
            }
        }

        command.ExecuteNonQuery();
    }
}

C# MySQL adding multiple of the same parameter with a loop

You need to dispose your object to not get the exception. However you don't need to iterate over values and run a query for every value in the list. Try the following code. It makes a parameter for every value and adds it to command to use in "IN (...)" clause.

Also "using" keywords handles disposing objects.

List<string> productsIds = new List<string>() { "23", "46", "76", "88" };
        string query = @"
            SELECT *
            FROM products
            WHERE id IN ({0});";

        // Execute query
        using (MySqlCommand cmd = new MySqlCommand(query, dbConn))
        {
            int index = 0;
            string sqlWhere = string.Empty;
            foreach (string id in productsIds)
            {
                string parameterName = "@productId" + index++;
                sqlWhere += string.IsNullOrWhiteSpace(sqlWhere) ? parameterName : ", " + parameterName;
                cmd.Parameters.AddWithValue(parameterName, id);
            }

            query = string.Format(query, sqlWhere);
            cmd.CommandText = query;
            using (MySqlDataReader row = cmd.ExecuteReader())
            {
                while (row.Read())
                {
                    // Process result
                    // ...
                }
            }
        }

How to pass multiple parameters to the parametrized query in c# using DbParameter?

You're only creating one parameter

        DbParameter param = df.CreateParameter();

        param.ParameterName = "@name";
        param.Value = txtname.Text;

etc...

You need to create a new DbParameter for each one

        DbParameter param = df.CreateParameter();
        param.ParameterName = "@name";
        param.Value = txtname.Text;
        daa.UpdateCommand.Parameters.Add(param);

        param = df.CreateParameter();
        param.ParameterName = "@address";
        param.Value = txtadd.Text;
        daa.UpdateCommand.Parameters.Add(param);

etc....

and add each one to the command.

Inserting multiple parameter values to a sqlite table using c#

When you call the Add method of the DbParameterCollection (the base class behind the SQLiteParameterCollection) passing a DbParameter object, the return value is an Integer, not the DbParameter just added to the collection.

Thus you cannot use the return value as it was a DbParameter and try to set the Value property.

Instead you should use the Add overload that takes the parameter name and type. This returns an DbParameter object and thus you could write:

insertCommand.Parameters.Add("@param1", System.Data.SqlDbType.VarChar).Value = inputText;
if(fasting)
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 1;
}
else
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 0;
}

You are creating an integer type parameter so set its value to an integer not to a string

C# combobox adding multiple items text and values from database dynamically

Your error is in the Item.Insert method where the first parameter should be the position in the already existing items collection where you want to insert the new value and not the Item's value.

Instead of using this manual loop you could simply set the combo datasource to a DataTable and set the DisplayMember and ValueMember properties

string connStr = "My Connection string";
using(SqlConnection conn = new SqlConnection(connStr))
{
    string strQuery = "SELECT * FROM tbl_Companies ORDER BY id ASC";
    SqlCommand cmd = new SqlCommand(strQuery, conn);
    conn.Open();
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader())
    cmbCompany.DisplayMember = "name";
    cmbCompany.ValueMember = "id";
    cmbCompany.DataSource = dt;
}

Consider also that once you set the Datasource property in this way you shouldn't manually insert new items, instead you add elements to the underlying DataTable and refresh the combo datasource binding

---

Related Topics