2
\$\begingroup\$

I have written some code for mapping excel columns to table columns and processing data after mapping columns. Please review this code, check for SOLID, OOP principles and suggest any improvements and optimizations. Any critiques, suggestions or comments are warmly welcomed.

API Action Method to get table and excel columns to map in view.

    [Route("GetTableAndExcelColumnsForMapping")]
    public List<Mapping> GetTableAndExcelColumnsForMapping(string FileName)
    {
        string sPath = "";
        sPath = System.Web.Hosting.HostingEnvironment.MapPath("~/Uploads/");

        return Mapping.GetTableAndExcelAndTableColumns(sPath + Path.GetFileName(FileName));
    }

Acutal method which will return excel and table columns to be shown on view for mapping.

    public static List<Mapping> GetTableAndExcelAndTableColumns(string FileName)
    {
        string pramName = String.Empty;
        List<Mapping> countyList = new List<Mapping>();
        DataSet ds = null;
        DataTable dt = null;

        try
        {
            using (var db = DbManager.GetDbManager())
            {
                var prams = new List<SqlParameter>();
                ds = db.GetDataSet("Mapping_GetTableColumns", prams.ToArray());

                dt = Upload.GetDataTableFromPostedFile(".xls", FileName);

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    countyList.Add(new Mapping { TableColumnName = row.Field<string>("COLUMN_NAME") });
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    countyList[i].ExcelColumnName = dt.Columns[i].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            new SqlLog().InsertSqlLog(UserInfo.GetCurrentUserInfo().UserId, "GetTableColumns", ex);
        }
        return countyList;
    }

API Action to check whether mappings exists for county and state, if so no new mapping will be created, previous mapping will be used.

    [Route("GetPreMappings")]
    public List<string> GetPreMappings(int StateId,int CountyId)
    {
        return Upload.GetPreMappings(StateId, CountyId);
    }

Method to get already saved mapping.

    public static List<string> GetPreMappings(int StateId, int CountyId)
    {
        string pramName = String.Empty;
        List<string> countyList = new List<string>();
        DataSet ds = null;

        try
        {
            using (var db = DbManager.GetDbManager())
            {
                var prams = new List<SqlParameter>();
                prams.Add(db.MakeInParam("@StateId", SqlDbType.Int, 0, StateId));
                prams.Add(db.MakeInParam("@CountyId", SqlDbType.Int, 0, CountyId));
                ds = db.GetDataSet("Mapping_GetPreMappings", prams.ToArray());

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    countyList.Add(row.Field<string>("MappingColumn"));
                }
            }
        }
        catch (Exception ex)
        {
            new SqlLog().InsertSqlLog(UserInfo.GetCurrentUserInfo().UserId, "GetPreMappings", ex);
        }
        return countyList;
    }

API Action to process and save data in database according to mapping done in above code.

    [HttpPost]
    public int Post(MappingRequest mapping)
    {
        string sPath = "";
        sPath = System.Web.Hosting.HostingEnvironment.MapPath("~/Uploads/");

        DataTable dt = Upload.GetDataTableFromPostedFile(".xls", sPath + Path.GetFileName(mapping.FileName));
        if (mapping.IsPressReleaseData)
        {
            return Upload.UploadPressReleases(dt, mapping.PublicationName);
        }
        else
        {
            if (!mapping.IsPreMapped)
                Upload.AddMappingData(mapping);

            Upload.TransferDataFromExcelToMappingFlatTable(dt, mapping, Mapping.GetTableAndExcelAndTableColumns(sPath + Path.GetFileName(mapping.FileName)), Mapping.GetEmptyMapTable());

            return 0;
        }
    }

This method will generate a data table from excel posted from view.

    public static DataTable GetDataTableFromPostedFile(string fileExtension, string fileLocation)
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();

        string excelConnectionString = string.Empty;
        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        if (fileExtension == ".xls")
        {
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        else if (fileExtension == ".xlsx")
        {
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }

        using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
        {
            excelConnection.Open();
            dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int t = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (!row["TABLE_NAME"].ToString().Contains("FilterDatabase"))
                {
                    excelSheets[t] = row["TABLE_NAME"].ToString();
                    t++;
                }
            }

            string query = string.Format("Select * from [{0}]", excelSheets[0]);

            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
            {
                dataAdapter.Fill(ds);
                dt = ds.Tables[0];
            }
        }
        return dt;
    }

Method to upload press releases.

    public static int UploadPressReleases(DataTable dt, string PublicationName)
    {
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.Trim();
        }

        List<string> headerTemplate = new List<string>() { "header", "sourceurl", "metroname", "region", "date", "city", "state", "details", "abstract", "writtenby" };
        List<string> missingColumns = new List<string>();
        List<string> ColumnNames = dt.Columns.Cast<DataColumn>().Select(s => s.ColumnName.ToLower()).ToList();

        missingColumns = headerTemplate.Except(ColumnNames).ToList();

        if (missingColumns.ToArray().Length > 0)
        {
            string Message = string.Empty;
            foreach (var col in missingColumns)
            {
                Message += col + ", ";
            }
            string ExceptionMessage = ErrorMessage.MissingColumnNames + Message.Trim().TrimEnd(',');
            throw new HeaderNameException(ExceptionMessage);
        }

        string stateAbbreviation = dt.Rows[0]["State"].ToString(),
               City = dt.Rows[0]["City"].ToString();

        int rowsAffected = 0;
        dt.Columns.Remove(dt.Columns["State"]);
        dt.Columns.Remove(dt.Columns["City"]);

        DataColumn stateColumn = new DataColumn("StateID");
        DataColumn cityColumn = new DataColumn("CityID");
        if (!string.IsNullOrEmpty(stateAbbreviation) && !string.IsNullOrEmpty(City))
        {
            int? stateId = Upload.GetStateByAbbreviation(stateAbbreviation);
            int? cityId = Upload.GetCityIdByCityName(City, stateId);

            if (cityId == 0)
            {
                cityId = null;
            }
            if (stateId == 0)
            {
                stateId = null;
            }

            stateColumn.DataType = typeof(int);
            stateColumn.DefaultValue = stateId;
            cityColumn.DataType = typeof(int);
            cityColumn.DefaultValue = cityId;
        }
        dt.Columns.Add(stateColumn);
        dt.Columns.Add(cityColumn);

        DataColumn publicationColumn = new DataColumn("PublicationName");
        publicationColumn.DataType = typeof(string);
        publicationColumn.DefaultValue = PublicationName;
        dt.Columns.Add(publicationColumn);

        DataColumn Header = dt.Columns["Header"];
        DataColumn Abstract = dt.Columns["Abstract"];
        DataColumn Details = dt.Columns["Details"];
        DataColumn Date = dt.Columns["Date"];
        DataColumn WrittenBy = dt.Columns["WrittenBy"];
        DataColumn SourceUrl = dt.Columns["SourceUrl"];
        DataColumn MetroNameCol = dt.Columns["MetroName"];
        DataColumn RegionCol = dt.Columns["Region"];
        DataColumn StateCol = dt.Columns["StateID"];
        DataColumn CityCol = dt.Columns["CityID"];

        Header.SetOrdinal(0);
        Abstract.SetOrdinal(1);
        Details.SetOrdinal(2);
        Date.SetOrdinal(3);
        WrittenBy.SetOrdinal(4);
        StateCol.SetOrdinal(5);
        SourceUrl.SetOrdinal(6);
        MetroNameCol.SetOrdinal(7);
        CityCol.SetOrdinal(8);
        RegionCol.SetOrdinal(9);
        publicationColumn.SetOrdinal(10);


        while (dt.Columns.Count > 11)
        {
            dt.Columns.RemoveAt(dt.Columns.Count - 1);
        }

        foreach (DataRow dr in dt.Rows)
        {
            string inputDate = dr["Date"].ToString().Trim();
            if (!string.IsNullOrEmpty(inputDate))
            {
                try
                {
                    DateTime dtParsedDate = DateTime.Parse(inputDate);
                    dr["Date"] = dtParsedDate.ToString();
                }
                catch (FormatException)
                {
                    dr["Date"] = DBNull.Value;
                }
            }
            else
                dr["Date"] = DBNull.Value;
        }
        rowsAffected = Upload.BulkCopyPressRelease(dt);

        return rowsAffected;
    }

This method is responsible for processing and saving data from excel to database table based on mapping, done in previous methods.

    public static void TransferDataFromExcelToMappingFlatTable(DataTable excelData, MappingRequest mappingReq, List<Mapping> listColumns, DataTable flatEmptyMapTable)
    {
        List<string> rows = mappingReq.MappingData.Split(',').Select(s => s).ToList();
        //Remove MappingId Column
        flatEmptyMapTable.Columns.Remove("MappingFlatID");

        //Add County and State Id columns
        flatEmptyMapTable.Columns.Add("CountyID");
        flatEmptyMapTable.Columns.Add("StateCode");

        for (int i = 0; i < excelData.Rows.Count; i++)
        {
            DataRow row = flatEmptyMapTable.NewRow();
            foreach (var item in rows)
            {
                if (excelData.Rows[i][item.Split('_')[1].ToString()].ToString() != "")
                    row[item.Split('_')[0]] = excelData.Rows[i][item.Split('_')[1].ToString()].ToString();

                row["CountyID"] = mappingReq.CountyId;
                row["StateCode"] = mappingReq.StateId.ToString();
            }
            flatEmptyMapTable.Rows.Add(row);
        }

        BulkCopyMapping_Flat(flatEmptyMapTable);
    }

Method for saving mapping done on front end (view) in database table.

    public static void BulkCopyMapping_Flat(DataTable dt)
    {
        using (DbManager db = DbManager.GetDbManager())
        {
            try
            {
                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(db.MakeInParam("@CustomType", SqlDbType.Structured, 0, dt));
                db.RunProc("PopulateMapping_Flat", param.ToArray());
            }
            catch (Exception ex)
            {
                new SqlLog().InsertSqlLog(0, "Upload.cs BulkCopyMapping_Flat", ex);
            }
        }
    }

Method to get table and excel columns to be shown on front end for mapping.

    public static List<Mapping> GetTableAndExcelAndTableColumns(string FileName)
    {
        string pramName = String.Empty;
        List<Mapping> countyList = new List<Mapping>();
        DataSet ds = null;
        DataTable dt = null;

        try
        {
            using (var db = DbManager.GetDbManager())
            {
                var prams = new List<SqlParameter>();
                ds = db.GetDataSet("Mapping_GetTableColumns", prams.ToArray());

                dt = Upload.GetDataTableFromPostedFile(".xls", FileName);

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    countyList.Add(new Mapping { TableColumnName = row.Field<string>("COLUMN_NAME") });
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    countyList[i].ExcelColumnName = dt.Columns[i].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            new SqlLog().InsertSqlLog(UserInfo.GetCurrentUserInfo().UserId, "GetTableColumns", ex);
        }
        return countyList;
    }

This method will generate an empty data table from a flat table in database in which all mapping data will be stored.

    public static DataTable GetEmptyMapTable()
    {
        string pramName = String.Empty;
        DataSet flatEmptyMapTable = null;

        try
        {
            using (var db = DbManager.GetDbManager())
            {
                var prams = new List<SqlParameter>();
                flatEmptyMapTable = db.GetDataSet("Mapping_GetEmptyMapTable", prams.ToArray());
            }
        }
        catch (Exception ex)
        {
            new SqlLog().InsertSqlLog(UserInfo.GetCurrentUserInfo().UserId, "Mapping_GetEmptyMapTable", ex);
        }
        return flatEmptyMapTable.Tables[0];
    }
\$\endgroup\$
2
  • 1
    \$\begingroup\$ Good question (+1). I don't have the time to do a review of the size I'd like to so until then, I think you need to read up about Dependency Inversion which is the D in SOLID. The practical implication is that you want to use dependency injection. \$\endgroup\$
    – RobH
    Oct 21 '16 at 7:39
  • \$\begingroup\$ Also as a hint while reading about the D go for I (nversion of control) and to restructure this static untestable thingie to something like CQS. \$\endgroup\$
    – kayess
    Oct 21 '16 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.