CODEWITHSUNDEEP
c#.netmysqlsql-serverarrays
sqlchild
sqlchild

Reputation: 9074

inserting an array into sql server and mysql database using c# .net

I have a List in C# for my Winforms application:

List<string> StudentSubjects = new List<>(string);

I have inserted these into List : 

 StudentSubjects.Add("Physics", "Chemistry", "Mathematics", "English");

Now i have a table: STUDENTS

----------
**StudentID     | SubjectsSelected**
---------
STDNT001        |  [HERE all subjects selected by the student should be inserted]     
STDNT002        |  Physics, Chemistry, Mathematics, English
STDNT002        |  Physics, Chemistry, Mathematics, English
----------

Shall i use the implode function for MySql? But what's the syntax? Also , what to do for SQL Server.

I am doing:

string query =
"INSERT INTO STUDENTS VALUES 
('S001', implode('" + StudentSubjects.ToArray() + " ')) "

But there's some error. Please help.

Upvotes: 1

Views: 5714

Answers (1)

Alex Aza
Alex Aza

Reputation: 78457

This should fix your query

string query = @"
    INSERT INTO STUDENTS VALUES 
    ('S001', '" + string.Join(",", StudentSubjects) + "')";

However, you better use parametrized query, instead of string concatenation.

For MySQL:

using (var connection = new MySqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
    command.CommandText = @"
        INSERT INTO STUDENTS VALUES 
        ('S001', ?subjects)";
    var param = command.Parameters.Add("?subjects", MySqlDbType.VarChar);
    param.Value = string.Join(",", StudentSubjects);
    connection.Open();
    command.ExecuteNonQuery();
}

For SQL Server:

using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
    command.CommandText = @"
        INSERT INTO STUDENTS VALUES 
        ('S001', @subjects)";
    command.Parameters.AddWithValue("subjects", string.Join(",", StudentSubjects))
    connection.Open();
    command.ExecuteNonQuery();
}

Upvotes: 2

Related Questions