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

Question

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.

Alex Aza · Accepted Answer · 2011-06-17 06:57:50Z

2

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

edited Jun 17, 2011 at 6:57

answered Jun 17, 2011 at 6:44

Alex Aza

78.8k26 gold badges157 silver badges134 bronze badges

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

6 Comments

sqlchild Over a year ago

@Alex Aza : sir, can you please help me with the parameterized query code also.

sqlchild Over a year ago

@Alex Aza : sir,is this valid for both : Mysql as well as SQL Server?

sqlchild Over a year ago

thanks a lot sir, also, if i use Implode function , then will it work? I have a Linux server , so have PHP.

sqlchild Over a year ago

@Alex Aza : also, how would i read only particular subjects name from the table? like if i only want to view students who have opted for English, then what would be the query? -- select * from students where SubjectsSelected = 'English', then will it work?

sqlchild Over a year ago

@Alex Aza : or it would be this : select * from students where SubjectsSelected LIKE %English%

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related