Multiple row insert into MS Sql Server using C#

Question

I am trying to insert multiple rows into MS SQL server using C#. My code inserts one value into the DB but then throws an exception. I am new to C# and would appreciate any help.

Code I am using :

 private int InsertDataintoDB()
    {
        var dbConnection = new DBConnection();
        UserAppList userAppList = new UserAppList();
        userAppList.initList();
        var sqlString = "Insert into dbo.[OneSiteUserAccess](UserType, ApplicationCode) values(@appuserType,@appCode)";
        string connectionString = dbConnection.GetLASCentralWriterConnectionString();
        SqlConnection sqlConnection = new SqlConnection(connectionString);
        int rowsAffected = 0;
        List<USerType_AndAppCode> mylist = userAppList.myList;
        try
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sqlString;
            sqlConnection.OpenAsync();
            mytextArea.Append(sqlConnection.ConnectionTimeout.ToString());

            foreach (var user in mylist)
            {

                sqlCommand.Parameters.AddWithValue("@appuserType", user.UserType);
                sqlCommand.Parameters.AddWithValue("@appCode", user.AppCode);
                sqlCommand.ExecuteNonQuery();
                rowsAffected++;

            }
            sqlConnection.Close();
        }

        catch (SqlException e)
        {
            Console.WriteLine(e.StackTrace);
            mytextArea.Append(e.StackTrace);
        }
        catch (Exception e)
        {

            Console.WriteLine(e.StackTrace); mytextArea.Append(e.StackTrace);
        }

        return rowsAffected;
    }
}

Error I am getting

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at WebApplication_EmptyWebApp1.UserData.InsertDataintoDB() in c:\Users\csaini\Documents\Visual Studio 2012\Projects\WebApplication_EmptyWebApp1\WebApplication_EmptyWebApp1\UserData.aspx.cs:line 120rows inserted : 1

Blockquote

Paolo Paolo · Accepted Answer · 2015-10-15T15:04:22

i think the issue may be the AddWithValue inside the foreach loop. for each iteration you add the same parameter over and over. your code should be something like this (check the syntax):

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = sqlString;
sqlConnection.Open();
mytextArea.Append(sqlConnection.ConnectionTimeout.ToString());

sqlCommand.Parameters.Add("@appuserType", SqlDbType.NVarChar);
sqlCommand.Parameters.Add("@appCode", SqlDbType.NVarChar);

foreach (var user in mylist)
{

    sqlCommand.Parameters["@appuserType"].Value = user.UserType;
    sqlCommand.Parameters["@appCode"].Value = user.AppCode;
    sqlCommand.ExecuteNonQuery();
    rowsAffected++;

}
sqlConnection.Close();

Multiple row insert into MS Sql Server using C#

1 Answers