how to import .csv file into sql server using c#

I am using the ASP.NET MVC. here is the code that might help you that how to you convert the CSV file in List and after that you will able to save the List data in to SQL.


     [HttpPost]  
     public ActionResult Create(t_NextDevAttendence t_nextdevattendence, IEnumerable<HttpPostedFileBase> files)  
     {  
       // Read the CSV file name & file path  
                // I am usisg here Kendo UI Uploader  
                string path = "";  
       string filenamee = "";  
       if (files != null)  
       {  
         foreach (var file in files)  
         {  
           var fileName = Path.GetFileName(file.FileName);  
           path = Path.GetFullPath(file.FileName);  
           filenamee = fileName;  
         }  
                     // Read the CSV file data  
         StreamReader sr = new StreamReader(path);  
         string line = sr.ReadLine();  
         string[] value = line.Split(',');  
         DataTable dt = new DataTable();  
         DataRow row;  
         foreach (string dc in value)  
         {  
           dt.Columns.Add(new DataColumn(dc));  
         }  
         while (!sr.EndOfStream)  
         {  
           value = sr.ReadLine().Split(',');  
           if (value.Length == dt.Columns.Count)  
           {  
             row = dt.NewRow();  
             row.ItemArray = value;  
             dt.Rows.Add(row);  
           }  
         }  
         // Insert CSV data in List  
         List<EmpAttendence> employeeList = new List<EmpAttendence>();  
         var myEnumerable = dt.AsEnumerable();  
         foreach (var item in myEnumerable)  
         {  
                     // set the CSV file column with my class  
                     // here it is Important that all field should be string when your inset in DB  
           EmpAttendence emp = new EmpAttendence();  
           emp.EmployeeId = item.Field<String>("User ID");  
           emp.EntryDate = item.Field<String>("Date");  
           emp.EntryTime = item.Field<String>("Time");  
           emp.fileName = filenamee;  
           employeeList.Add(emp);  
         }  
         // Insert in database from List  
         foreach (var item in employeeList)  
         {  
                     // table name  
           t_NextDevAttendence t_next = new t_NextDevAttendence();  
           t_next.EmployeeCode = item.EmployeeId;  
           t_next.EntryDate = item.EntryDate;  
           t_next.EntryTime = item.EntryTime;  
           t_next.CSVFileName = item.fileName;  
           db.t_NextDevAttendence.AddObject(t_next);  
         }  
         db.SaveChanges();  
         return RedirectToAction("Index");  
       }  
       return View(t_nextdevattendence);  
     }  

Popular posts from this blog

WPF Crystal Report Viewer Using SAP

mvc razor textboxfor change event change another textboxfor value

ASP.NET MVC razor SAP Crystal report