Tuesday, 11 October 2011

Get Data from Excel and Save into database in c#

 protected void btnupload_Click(object sender, EventArgs e)
    {
        String ExtFileName = Path.GetExtension(fileuploader.FileName);
        String FileName = fileuploader.FileName;
        String FilePath = Server.MapPath("~/Auditsystem/AuditAdmin/") + FileName;

        GenerateAccountExcel(FilePath);


    }


    private string GenerateAccountExcel(String filePath)
    {

        string strSQL = string.Empty;
        string retValue = string.Empty;
        DataTable dtExcel = null;
        System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'");

        System.Text.StringBuilder sb = new System.Text.StringBuilder();


        try
        {
            strSQL = "SELECT * FROM [Questionnaire$]";

            System.Data.OleDb.OleDbCommand cmd = null;
            System.Data.OleDb.OleDbDataAdapter daExcel = null;

            dbConn.Open();
            dtExcel = new DataTable();
            cmd = new System.Data.OleDb.OleDbCommand(strSQL, dbConn);
            daExcel = new System.Data.OleDb.OleDbDataAdapter(cmd);
            dtExcel.Columns.Add("UserID", typeof(String), "123");
            daExcel.Fill(dtExcel);
            int dsExcelRecordCount = 0;
            dsExcelRecordCount = dtExcel.Rows.Count;
            if (dsExcelRecordCount == 0)
            {
                retValue = "No records to update in excel file";

            }
            else
            {


                BLL_UploadQuestionnaire objUploadQuestionnaire = null;
                int Result = 0;

                objUploadQuestionnaire = new BLL_UploadQuestionnaire();

                Result = objUploadQuestionnaire.ImportIUploadQuestionnaire(

                   new OM_UploadQuestionnaire
                   {
                       ImportDatTable = dtExcel
                   });

                GetUploadQuestionnaire("123");

            }


        }
        catch (Exception ex)
        {

            retValue = ex.Message + " :Error while importing Data.";
        }
        finally
        {


            dbConn.Close();
            dbConn.Dispose();
        }

        return retValue;
    }

No comments:

Post a Comment