ASMX Web Service, Stored Procedures and MVC Models

Question

I am developing a web application using MVC 3. This application connects to an SQL Server database through ASMX Web Services. Each Web Method calls a Stored Procedure and returns a DataTable.

This is the code I'm using to call the Stored Procedure:

public static DataTable ExecSP(string StoredProcedureName, List<string> ParameterNames, List<Object> ParameterValues)
    {
        SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString);
        SqlDataReader Reader = null;
        DataTable SPResult = null;

        try
        {
            Connection.Open();
            SqlCommand Command = new SqlCommand("dbo." + StoredProcedureName, Connection);
            Command.CommandType = CommandType.StoredProcedure;

            if (ParameterNames != null)
            {
                for (int i = 0; i < ParameterNames.Count; i++)
                {
                    SqlParameter Parameter = new SqlParameter(ParameterNames[i], ParameterValues[i]);
                    if (Parameter.SqlDbType.Equals(SqlDbType.NVarChar))
                    {
                        Parameter.SqlDbType = SqlDbType.VarChar;
                    }

                    if (Parameter.SqlValue == null)
                    {
                        Parameter.SqlValue = DBNull.Value;
                    }

                    Command.Parameters.Add(Parameter);
                }
            }
            Reader = Command.ExecuteReader();
            SPResult = new DataTable();
            SPResult.Load(Reader);

        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            Connection.Close();

            if (Reader != null)
            {
                Reader.Close();
            }
        }
        return SPResult;
    }

I would like to know if there is a straight-forward way to convert this DataTable into a Model that can then be passed to a View (like, for example, the model binding that happens in an AJAX post) and, if there isn't, what are the alternatives. I know that using LINQ would probably solve this problem, but I can't use it.

Thanks in advance.

Best regards.

score 0 · Answer 1 · edited May 23 '17 at 12:12

Found a solution.

I built a generic method that translates any DataTable into a List of whatever class I specify:

public static List<T> Translate<T>(DataTable SPResult, Func<object[],T> del)
    {
        List<T> GenericList = new List<T>();

        foreach (DataRow Row in SPResult.Rows)
        {
            GenericList.Add(del(Row.ItemArray));
        }

        return GenericList;
    }

where del is a delegate. When calling this method, del should be the constructor of the specified class. Then, in all Model classes, I built a constructor that receives an object[] RowFromTable

public class MyClass
{
    public int ID { get; set; }
    public string Description { get; set; }

    public FormaProcesso(object[] RowFromTable)
    {
        this.ID = (int)RowFromTable[0];
        this.Description = RowFromTable[1].ToString();
    }
}

Finally, to put it all together, this is what happens when I call the Web Method:

public List<MyClass> GetAll()
    {

        DataTable SPResult = MyWebService.GetAll().Table;

        return Translate<MyClass>(SPResult, l => new MyClass(l));

    }

Got the idea from here

ASMX Web Service, Stored Procedures and MVC Models

1 Answers1