How do I...Count the no of records inserted.

Question

I'm getting the values form excel in datatable then finally i'm updating/Inserting the record in database.

Every thing is working fine but what i need is once finishing all the records it should display the message to the user as ..no of records inserted.

and also while inserting some time it is throwing an error as

 String was not recognized as a valid DateTime.

Eg: 20 records inserted.

Here is my code:

 private void Import_To_Grid(string FilePath, string Extension, string isHDR)
       {
           String strConnString = ConfigurationManager.ConnectionStrings["CARGONETConnectionString"].ConnectionString;
           //file upload path
           string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
           //file name
           string FileName = lblFileName.Text;
           //Create connection string to Excel work book

           string conStr = "";
           switch (Extension)
           {
               case ".xls": //Excel 97-03
                   conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                   break;
               case ".xlsx": //Excel 07
                   conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FolderPath + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                   break;
           }

            conStr = String.Format(conStr, FilePath, isHDR);
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            cmdExcel.Connection = connExcel;

           //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = ddlSheets.SelectedValue.ToString();
            connExcel.Close();

           //Read Data from First Sheet
            connExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dt);
            connExcel.Close();

           //Bind to Database

           int count=0;
            using (LQTransAgentSeaFreightRateDataContext DB = new LQTransAgentSeaFreightRateDataContext())
            {
                foreach (DataRow r in dt.Rows)
                {
                    var newSFR = new TB_TransAgentSeaFreightRate_2
                    {
                        POD = r["POL"].ToString(),
                        POL = r["POD"].ToString(),
                        Forwarder = r["FORWARDER"].ToString(),
                        ForwarderReference = r["FORWARDER REFERENCE"].ToString(),
                        ShippingLine = r["SHIPPING LINE"].ToString(),
                        ContainerType = r["CONTAINER TYPE"].ToString(),
                        ContainerSize = r["CONTAINER SIZE"].ToString(),
                        ValidFrom = Convert.ToDateTime(r["VALIDITY FROM"].ToString()),
                        ValidTo = Convert.ToDateTime(r["VALITITY TO"].ToString()),
                        BasicRate = Convert.ToDecimal(r["BASIC RATE"]),
                        PAF = Convert.ToDecimal(r["PAF "]),
                        CAF = Convert.ToDecimal(r["CAF"]),
                        PSS = Convert.ToDecimal(r["PSS"]),
                        TotalAmount = Convert.ToDecimal(r["TOTAL AMOUNT"]),
                        FreeDays = Convert.ToDecimal(r["FREE DAYS"]),
                        CreditDays = r["CREDIT DAYS"].ToString(),
                        NITDeposit = r["NIT DEPOSIT"].ToString(),
                        tASF_NUIsActive = 1,
                        tASF_mCMP_NUUniqueId = mobjGenlib.ConvertLong(TXTCompanyID.Text)

                    };
                    DB.TB_TransAgentSeaFreightRate_2s.InsertOnSubmit(newSFR);
                    DB.SubmitChanges();
                    count = count + 1;
                }
            }
            //ScriptManager.RegisterStartupScript(this, Up.GetType(), "ALERT", "alert('Saved Successfully');", true);
            //Bind Data to GridView
            dg_AgentSFR.Caption = Path.GetFileName(FilePath);
            dg_AgentSFR.DataSource = dt;
            dg_AgentSFR.DataBind();
            //savedatafromgv();

       }

please help me. Thanks in advance.

Answer 1

dt.Rows.Count() would tell how many rows available in dataset..It will helpful to determine the total no of records inserted