Friday, 13 December 2013

Insert more than 1000 records at a time into sql database

               StringBuilder sbSql = new StringBuilder();

                int Count = 0;

                int bunch = 999;

                int Total;
            
                Total = ContactList.Count;
             

                int Mod = Total % bunch;
                for (int l = 0; Mod == 0 ? l < Total / bunch : l <= Total / bunch; l++)
                {
                    sbSql.Append(@"INSERT INTO              ContactDetail(UserId,ContactName,ContactNo,EmailID,RecordID,IsFriend,Status,CreatedOn) VALUES");
                   // Response.Write("Outer Looop" + i.ToString() + "<br/><br/>");
                    for (int i = Count; i <= bunch * (l + 1); i++)
                    {
                      //  Response.Write("Inner Looop" + j.ToString() + "<br/>");
                        string values = string.Format("({0},{1},{2},{3},{4},{5},{6},{7}),",
                           new string[] { Convert.ToString(UserId), "'" + ContactList[i].name + "'", "'" + ContactList[i].contactNo + "'", "'" + ContactList[i].Emails + "'", ContactList[i].RecordID, "0", "0", "'" + CreatedOn + "'" });
                        sbSql.Append(values);
                        Count++;
                        if (Total == Count)
                            break;
                    }
                    string sql = sbSql.ToString();
                    sql = sql.TrimEnd(',');
                    SqlConnection con = new SqlConnection();
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
                    con.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.ExecuteNonQuery();
                    sbSql.Clear();

No comments:

Post a Comment