Monday, March 31, 2014

String was not recognized as a valid DateTime.

After spending a lots of time i solve the problem


  string strDate = PreocessDate(data);  
       string[] dateString = strDate.Split('/');  
       DateTime enter_date = Convert.ToDateTime(dateString[1]+"/"+dateString[0]+"/"+dateString[2]);  


Thursday, March 27, 2014

Import Data from Excel to SQL Server ASP.NET MVC


I want to save the excel sheet data in my SQL or other database. In this sheet you just mind the name of the table Sheet1.







Now the controller



  public ActionResult ManualAttendence()  
     {  
       return View();  
     }  



And the ManualAttendence view



 @using (Html.BeginForm("ManualAttendence", "AttendanceManual", FormMethod.Post, new { enctype = "multipart/form-data" }))  
 {  
   <input type="file" name="file" />  
   <input type="submit" value="OK" />  
 }  



Create a new folder name ManualAttendenceSheet in your solution App_Data folder.
Now brows the file & click ok



 [HttpPost]  
     public ActionResult ManualAttendence(HttpPostedFileBase file, AttendanceManualModels model)  
     {  
       int returnValue = 0;  
       // Verify that the user selected a file  
       if (file != null && file.ContentLength > 0)  
       {  
         // extract only the fielname  
         var fileName = Path.GetFileName(file.FileName);  
         // store the file inside ~/App_Data/uploads folder  
         var path = Path.Combine(Server.MapPath("~/App_Data/ManualAttendenceSheet"), fileName);  
         file.SaveAs(path);  
         //string pathName = "~/App_Data/uploads/'" + fileName + "'";  
         string pathName = Server.MapPath("~/App_Data/ManualAttendenceSheet/" + fileName);  
         string workSheetName = "Sheet1";  
         string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties=Excel 12.0;Persist Security Info=False";  
         //Create Connection to Excel work book  
         OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);  
         //Create OleDbCommand to fetch data from Excel  
         string query = string.Format("SELECT * FROM [{0}$]", workSheetName);  
         DataSet data = new DataSet();  
         using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(excelConnectionString))  
         {  
           con.Open();  
           System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(query, con);  
           adapter.Fill(data);  
           returnValue = AttendanceManualBLL.SaveAttendenceManual(data, "I");  
         }  
         if (returnValue < 0)  
         {  
           model.Message = "Transection Error...!";  
         }  
         else  
         {  
           ModelState.Clear();  
           model = new AttendanceManualModels();  
           model.Message = "Manual Leave saved successfully...!";  
         }  
       }  
       return RedirectToAction("ManualAttendence");  
     }  



Now AttendanceManualBLL:  



 public class AttendanceManualBLL  
   {  
     public static List<AttendenceManual> PreocessData(DataSet data)  
     {  
       List<AttendenceManual> _AttendenceManualList = new List<AttendenceManual>();  
       for (int i = 4; i < data.Tables[0].Rows.Count; i++)  
       {  
         AttendenceManual Student = new AttendenceManual();  
         Student.StrEmpID = data.Tables[0].Rows[i][1].ToString();  
         Student.StrEmpCardNo = data.Tables[0].Rows[i][2].ToString();  
         Student.StrAttendanceDeviceNo = data.Tables[0].Rows[i][3].ToString();  
         Student.StrEmpName = data.Tables[0].Rows[i][4].ToString();  
         Student.StrDesignation = data.Tables[0].Rows[i][5].ToString();  
         Student.StrFunctionalDesignation = data.Tables[0].Rows[i][6].ToString();  
         Student.StrMobileNo = data.Tables[0].Rows[i][7].ToString();  
         Student.AttendanceBonusDeduction = data.Tables[0].Rows[i][8].ToString();  
         Student.StrInTime = data.Tables[0].Rows[i][9].ToString();  
         Student.StrOutTime = data.Tables[0].Rows[i][10].ToString();  
         Student.StrEntryDate = data.Tables[0].Rows[i][11].ToString();  
         Student.InTime = Convert.ToDateTime(Student.StrInTime);  
         Student.OutTime = Convert.ToDateTime(Student.StrOutTime);  
         Student.EntryDate = Convert.ToDateTime(Student.StrEntryDate);  
         _AttendenceManualList.Add(Student);  
       }  
       return _AttendenceManualList;  
     }  
     public static int SaveAttendenceManual(DataSet data, string mode)  
     {  
       List<AttendenceManual> _AttendenceManualList = new List<AttendenceManual>();  
       _AttendenceManualList = PreocessData(data);  
       int i = 0;  
       if (_AttendenceManualList.Count > 0)  
       {  
         foreach (AttendenceManual item in _AttendenceManualList)  
         {  
           i = AttendanceManualDAL.SaveAttendenceManual(item, mode);  
         }  
       }  
       return i;  
     }  
   }  



In AttendanceManualBLL class I set the dataset value in my list. Now I can save value from my list in database.

Note:
#1.in for loop i start from, because of i want to read my excel sheet from row 4.
int i = 4




SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT...