Skip to main content

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);  
     }  

Comments

Popular posts from this blog

C# run powershell script as administrator

Recently I was fetching a problem that I need to run a PowerShell script that will change TFS user Display name and SID. I was trying to run that script from C# that was not working due to TFS security update and TLS certificate. Using this code block I resolve the Issue. var newProcessInfo = new System.Diagnostics.ProcessStartInfo(); newProcessInfo.FileName = @"C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe"; newProcessInfo.Verb = "runas"; // Define Run as administrator newProcessInfo.Arguments = script; //Define your powershell script newProcessInfo.UseShellExecute = false; newProcessInfo.RedirectStandardOutput = true; // This will enable to read Powershell run output newProcessInfo.RedirectStandardError = true; Process proces = System.Diagnostics.Process.Start(newProcessInfo); proces.WaitForExit(); // I want to read the output string from powershell window StringBuilder output = new StringBuilder(); output.Append("Started"); while (!proces.St

mvc razor textboxfor change event change another textboxfor value

Based on value of Weight, Rate , CNF & AWB it will change the value of Freight , TTLCNF anfd TTLFright . Freight= Weight*Rate; TTLCNF  = Weight*CNF; TTLFright=  Freight+ TTLCNF  + AWB; @Html.TextBoxFor(model => model.Weight, new { onChange="return GetWight(this);"}) @Html.TextBoxFor(model => model.Rate, new { onChange="return GetWight(this);"})/Kg @Html.TextBoxFor(model => model.Freight, new {disabled = "disabled" , @readonly = "readonly" ,onChange="return GetTTLFright(this);"}) @Html.TextBoxFor(model => model.CNFPK, new { onChange="return GetCNFPK(this);"}) @Html.TextBoxFor(model => model.TTLCNF, new {disabled = "disabled" , @readonly = "readonly",onChange="return GetTTLFright(this);" }) @Html.TextBoxFor(model => model.AWB, new { onChange="return GetTTLFright(this);"}) and script <script> function GetW

ASP.NET MVC razor SAP Crystal report

Crete a new project: Add a aspx Master Page Create a new folder Reports and 2 sub folder crystal & crystalviewer Now add a web form page in crystalviewer  folder. Add the master page namespace in your web form page. MasterPageFile ="~/Views/Shared/ReportSite.Master" Replace your web form by this code < asp : Content ID ="Content1" ContentPlaceHolderID ="ContentPlaceHolder1" runat ="server">      </ asp : Content > Now go to design mode of your web form drag & drop the crystal report viewer in your web form. After that your page will be look look like this. Replace the code: < CR : CrystalReportViewer ID ="EmployeeList" runat ="server"   HasCrystalLogo ="False"     AutoDataBind ="True"   Height ="50px"   EnableParameterPrompt ="false" EnableDatabaseLogonPrompt