ASP.NET DynamicData Import Datas from Excel

Question

I am using ASP.NET DynamicData v4.5 to allow admin to insert/update the records in the database.

My requirement is, -- Allow admin to import records for table from EXCEL file. Source for the table may be available in excel files also. so i want admin to import data from file.

Is there any way i can achieve this in DynamicData ?

Answer 1

Yes you can do it, I've done it many times. There is no built-in feature in Dynamic Data doing this but that's not a problem since it's pretty easy to implement.

The fact that you use ASP.NET Dynamic Data (like I do) is not really important for this task. As you probably know, you can create a regular ASP.NET form within a Dynamic Data project. You can also use a folder named /DynamicData/CustomPages to customize a Dynamic Data page. I suggest creating a new regular ASP.NET form called ImportingTool.aspx where your users will be able to import spreadsheets into your database. Once imported, they can use other dynamic data pages to edit the data.

Here is what you will need :

1- You need the user to upload a file, you will need asp:fileupload or ajaxToolkit:AjaxFileUpload

2- You need to open that file, it will look like :

public void Import(FileUpload fileUpload)
{
    if (fileUpload.HasFile)
    {
        string FileName = Path.GetFileName(fileUpload.PostedFile.FileName);
        string Extension = Path.GetExtension(fileUpload.PostedFile.FileName);
        string FilePath = HttpRuntime.AppDomainAppPath + "/Uploaded/" + FileName;
        fileUpload.SaveAs(FilePath);
        Import(FilePath, Extension);
    }
}

3- You will need to import that file in your database, it will look like :

public Boolean Import(string FilePath, string Extension)
{
    if (String.IsNullOrEmpty(FilePath) || String.IsNullOrEmpty(Extension))
    {
        return false;
    }

    string conStr;
    string conStrNoHDR;
    GetConnectionString(FilePath, Extension, out conStr, out conStrNoHDR);

    OleDbConnection connection = new OleDbConnection(conStr);
    OleDbConnection connectionNoHDR = new OleDbConnection(conStrNoHDR);

    // depending on file extension, you might want to use connectionNoHDR 
    Import(connection); 

    connection.Close();
    connectionNoHDR.Close();

}

private static void GetConnectionString(string FilePath, string Extension, out string conStr, out string conStrNoHDR)
{
    conStr = "";
    conStrNoHDR = "";
    switch (Extension)
    {
        case ".xls": //Excel 97-03            
            conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
            conStrNoHDR = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=NO\"";
            break;
        case ".xlsx": //Excel 07
            conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0 ";
            conStrNoHDR = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=NO\"";
            break;
        case ".csv":
            conStr = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + Path.GetDirectoryName(FilePath) + ";Extended Properties=\"Text;FMT=Delimited;HDR=NO\"";
            break;        
    }
}

public static void Import(OleDbConnection connection)
{
    String query = "SELECT * From [Report-LANG_VOCALLS$]";
    DataTable dt = ImportUtils.GetData(connection, query);

    string table = "Dialer";

    string conn = ConfigurationManager.ConnectionStrings["Telecom"].ConnectionString;
    SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);     

    bulkCopy.ColumnMappings.Add("Phone", "Phone");
    bulkCopy.ColumnMappings.Add("portfolio", "Portfolio_eng");
    bulkCopy.ColumnMappings.Add("dept", "Department_eng");

    ImportUtils.BulkCopy(dt, table, bulkCopy);

}

public static DataTable GetData(OleDbConnection connection, String query)
{
    DataTable dt = new DataTable();
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    OleDbCommand cmdExcel = new OleDbCommand();
    cmdExcel.CommandText = query;
    cmdExcel.Connection = connection;
    connection.Open();
    adapter.SelectCommand = cmdExcel;
    adapter.Fill(dt);
    Debug.WriteLine(dt.Rows.Count);
    connection.Close();
    return dt;
}