Insert a list of objects to SQL Server table

Question

want to insert a list of objects into sql server table. I just wonder if there is a way I can insert all the objects in the record list at one time?

public int stockInsert()
{
       int result = 0;

       string queryStr = "insert into Stock(stockID,color_Available,size_Available,qty_Available,detail_Img,productId)"
       + "values(@stockID,@color_Available,@size_Available,@qty_Available,@detail_Img,@productId)";

       con.Open();
       SqlCommand cmd = new SqlCommand(queryStr, con);

       cmd.Parameters.AddWithValue("@stockID", this.StockID);
       cmd.Parameters.AddWithValue("@color_Available", this.ColorAvailable);
       cmd.Parameters.AddWithValue("@size_Available", this.SizeAvailable);
       cmd.Parameters.AddWithValue("@qty_Available", this.QtyAvailable);
       cmd.Parameters.AddWithValue("@detail_Img", this.DetailImg);
       cmd.Parameters.AddWithValue("@productId", this.ProductID);

       result += cmd.ExecuteNonQuery();
       con.Close();

       return result;
   }

Is the List a collection of stocks (referenced by "this" on the sample code)? — Afonso, Jul 23 '18 at 13:53
What is `this`? Is it an object with those properties that is contained in a `List`? Something else? — JNevill, Jul 23 '18 at 13:54
Perfect. Take a look at that suggested duplicate. It's a suggestion to convert the `List` over to a `DataTable` and call `SqlBulkCopy.WriteToServer` to push the `DataTable` into the sql server table. It's a little round-a-bout but it's better than single line insert. Your DBA will thank you. — JNevill, Jul 23 '18 at 13:56
one way could be to loop over your record list(after you open your connection) and insert rows in that loop, by doing this you don't need to open and close the connection multiple times. — Rajat, Jul 23 '18 at 14:19

score 0 · Answer 1 · answered Jul 23 '18 at 19:18

Give this a shot, and feed back. You are loading data froma text file, into a DataTable, and from there, pushing the data into a SQL Server DB.

private void Button1_Click(object sender, System.EventArgs e) {
        DataTable tblReadCSV = new DataTable();
        tblReadCSV.Columns.Add("FName");
        tblReadCSV.Columns.Add("LName");
        tblReadCSV.Columns.Add("Department");
        TextFieldParser csvParser = new TextFieldParser("C:\\your_path_here\\Employee.txt");
        csvParser.Delimiters = new string[] {
                ","};
        csvParser.TrimWhiteSpace = true;
        csvParser.ReadLine();
        while (!(csvParser.EndOfData == true)) {
            tblReadCSV.Rows.Add(csvParser.ReadFields());
        }

        SqlConnection con = new SqlConnection("Server=your_server_name;Database=your_DB_name;Trusted_Connection=True;");
        string strSql = "Insert into Employee(FName,LName,Department) values(@Fname,@Lname,@Department)";
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSql;
        cmd.Connection = con;
        cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FName");
        cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LName");
        cmd.Parameters.Add("@Department", SqlDbType.VarChar, 50, "Department");
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.InsertCommand = cmd;
        int result = dAdapter.Update(tblReadCSV);
    }

Insert a list of objects to SQL Server table

1 Answers1