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

Why do you use OpenAsync ? Plain old Open wil do.

Antonio Bakula
– Antonio Bakula

2015-10-15 15:30:50 +00:00
Commented Oct 15, 2015 at 15:30 — Antonio Bakula
– Antonio Bakula, Commented Oct 15, 2015 at 15:30

Antonio Bakula · Accepted Answer · 2015-10-15 15:31:06Z

4

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();

edited Oct 15, 2015 at 15:31

Antonio Bakula

20.7k7 gold badges80 silver badges106 bronze badges

answered Oct 15, 2015 at 15:04

Paolo

2,2631 gold badge17 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Multiple row insert into MS Sql Server using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related