Click here to Skip to main content
15,895,537 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
public void InsertDataToDb()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["connection"].
            ConnectionString;
        var records = GetRecords();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            SqlCommand cmd =
                new SqlCommand(
                    "INSERT INTO TableName (param1, param2, param3) " +
                    " VALUES (@param1, @param2, @param3)");
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            cmd.Parameters.Add("@param1", DbType.String);
            cmd.Parameters.Add("@param2", DbType.String);
            cmd.Parameters.Add("@param3", DbType.String);

            foreach (var item in records)
            {
                cmd.Parameters[0].Value = item.param1;
                cmd.Parameters[1].Value = item.param2;
                cmd.Parameters[2].Value = item.param3;

                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }





I want to do this in stored procedure . how to pass list to SP as parameter. anyone pls suggest

What I have tried:

With out converting list to xml as parameter. Need to directly pass list to a SP.
Posted
Updated 10-May-18 18:40pm

Personally I like table typed parameters, mostly because of the ease of usage and being able to define exact structure.

For an example, have a look at How to pass multiple records to a Stored Procedure[^]
 
Share this answer
 
The only ways to pass a list through to SQL Server are as XML, JSON if you have 2016, or as a CSV, the last option being a nightmare. You can't pass in a List<t>, SQL Server has no concept of that. Your XML or JSON gets converted to a table, tables are what SQL Server knows about
 
Share this answer
 
Comments
rajah rajah 11-May-18 0:39am    
Thanks chris

if we want to do without having XML below one is the only solution to pass values to a SP . right ?


create procedure insert_data
(
@Id int, @Name Varchar(20) , @address varchar(50)
)
begin

Insert into table1(Id,Name,address) values(@Id,@Name,@address)

end


sqlConnection con = new SqlConnection(dbConnectionString);
SqlCommand comm= new SqlCommand("insert_data", con);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@Id", SqlDbType.VarChar).Value = txtId.Text;
comm.Parameters.Add("@Name", SqlDbType.DateTime).Value = txtName.Text;
comm.Parameters.Add("@Address", SqlDbType.DateTime).Value = txtaddress.Text;
sqlConnection.Open();
return comm.ExecuteNonQuery();
sqlConnection.Close();
Richard Deeming 11-May-18 9:53am    
If you're using SQL 2008 or later, you can also use table valued parameters[^].

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900