Validate Excel Row with Database Row

Dear Jignesh, 

 

My Syntax is working fine, My concern is when i upload excel file it check if it find true then insert data if false  to match it send back a result excel sheet like given highlited Excel Output below. Please give me suggestion for that??

 

Excel Sheet Data  

 

UserId

40

41

42

70

 

Database Data

 

 

 

UserId	ISUPDATE	ERRMESSAGE
40	TRUE	Data Is Exist
41	TRUE	Data Is Exist
42	TRUE	Data Is Exist
70	FALSE	Data Is not Exist

 

 

  private void ImporttoDatatable()
        {
            try
            {
                if (FileUpload1.HasFile)
                {
                    string path = Path.GetFileName(FileUpload1.FileName);
                     path = path.Replace(" ", "");
                      FileUpload1.SaveAs(Server.MapPath("~/ExcelFile/") + path);
                     String ExcelPath = Server.MapPath("~/ExcelFile/") + path;
                     OleDbConnection OleDbcon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + ExcelPath + "; Extended Properties=Excel 8.0; Persist Security Info = False");
                    OleDbcon.Open();

                    OleDbCommand cmd = new OleDbCommand("SELECT UserId FROM [Sheet1$] ", OleDbcon);
                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
                    ds = new DataSet();
                    objAdapter1.Fill(ds);
                    Dt = ds.Tables[0];
                    File.Delete(ExcelPath);
                }
            }
            catch (Exception ex)
            {

            }
        }

        private void ImporttoDatatable1( )
        {
            try
            {
               
                   

                    SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString);
                    con2.Open();
                    SqlCommand cnn = new SqlCommand("select UserId from Units", con2);
                    SqlDataAdapter adapter = new SqlDataAdapter(cnn);
                   
                    dt1 = new DataTable();

                    adapter.Fill(dt1);
                
               

            }
            catch (Exception ex)
            {

            }
        }

 


        private void CheckData()
        {
            try
            {

                for( int i = 0; i < Dt.Rows.Count; i++)
                {
                    
                    for(int j=0; j< dt1.Rows.Count;j++)
                {


                        if (Dt.Rows[i][0].ToString() != dt1.Rows[j][0].ToString())
                        {


                            int RowNo = i + 2;
                            Response.Write("Please enter Employee ID in row '" + RowNo + "'");
                            return;

                        }
                      

                              
                    }
                    
                }

hi You just read excel data and Db data into data table and compatre both the datatable.

 chekc the link to ompare the datatable

https://www.codeproject.com/Questions/1224003/Compare-two-datatables-in-Csharp

https://stackoverflow.com/questions/7517968/how-to-compare-2-datatables

Hi Rishi,

 

Check validation for user Exists,

https://www.aspsnippets.com/Articles/Validating-Check-Username-and-Password-from-database-in-ASPNet.aspx 

 

Just pass userId  from your excel row and check against your table. If it returns any row then user is exists.

then you should write code for insert that row else skip that row.