CODEWITHSUNDEEP
c#databasevariablesarraylist
SiRaZz
SiRaZz

Reputation: 43

Add Arraylist data to database

I have some variables which are Arraylists and some are strings. I have class method which takes variables from another class using inheritence and add them to database.Here is my code:

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Darius\Documents\Visual Studio 2013\Projects\FilmuBiblioteka\FilmuBiblioteka\duombaze.mdf");

            sqlConnection1.Open();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT filmai (Id, Pavadinimas, Metai, trukme, salis, kalba, reitingas, zanras, aktoriai, link, plot, rezisieriai) VALUES (@Id, @Title, @Year, @Runtime, @Countries, @Languages, @Rating, @Genres, @Cast, @ImdbURL, @Plot, @Directors)";
            cmd.Parameters.AddWithValue("Id", Id);
            cmd.Parameters.AddWithValue("Title", Title);
            cmd.Parameters.AddWithValue("Year", Year);
            cmd.Parameters.AddWithValue("Runtime", Runtime);
            cmd.Parameters.AddWithValue("Countries", Countries); *
            cmd.Parameters.AddWithValue("Languages", Languages); *
            cmd.Parameters.AddWithValue("Rating", Rating);
            cmd.Parameters.AddWithValue("Genres", Genres); *
            cmd.Parameters.AddWithValue("Cast", Cast); *
            cmd.Parameters.AddWithValue("ImdbURL", ImdbURL);
            cmd.Parameters.AddWithValue("Plot", Plot);
            cmd.Parameters.AddWithValue("Directors", Directors); *
            cmd.Connection = sqlConnection1;
            cmd.ExecuteNonQuery();
            sqlConnection1.Close();

Symbol - * represent that these variables are arraylists. So, I'am getting this error: No mapping exists from object type System.Collections.ArrayList to a known managed provider native type.

How to fix, so could add everything to database?

Upvotes: 0

Views: 182

Answers (1)

Henrik Nielsen
Henrik Nielsen

Reputation: 410

If your database doesn't support array types and you don't want to extract your Contries, Languages etc into separate referenced tables, I guess you are left with flattening your ArrayList to a string like so:

    // Convert the ArrayList to a string
    string separator = "|"; // Any string that doesn't collide with content
    string arrayListAsString = String.Join(separator, Contries.ToArray());

Then save the string in the database. When reading the string from the database you'll probably want to convert it back to an ArrayList:

    // Convert the string back to an ArrayList
    ArrayList list = new ArrayList();
    list.AddRange(arrayListAsString.Split(new string[]{separator}, StringSplitOptions.RemoveEmptyEntries));

Upvotes: 1

Related Questions