Problem in stored procedure in mvc

Hi,

iam trying to convert a web application in to  MVC .

my requirement is ,i want to display records from a stored procedure in to a table format .

stored procedure contains a complex select query.

i have read some tutorial in which contains we can’t read data using storedprocedure in mvc.is it true.

then how to solve my problem

Regards

Baiju

klbaiju

have read some tutorial in which contains we can’t read data using storedprocedure in mvc.is it true.

false.

More, MVC does not matter how you obtain data. MVC View display a Model. Just make a class that reads the SP.

Do you know how to read the records from SP ? 

hi ignatandrei,

i already use sp to insert,update and delete in mvc.

here it is a select query in sp. also pivot query and don’t know the field names .

then how to write class in model and write fieldnames in view

1. You get DataTable or DataReader and populate List<yourcomplextype> by iterating you row from datatable or datareader
2. pass it to view from the controller

public class contact
{
    public string Name { get; set; }
    public string Address { get; set; }
}
public ActionResult GetList()
{
    List<contact> contacts = new List<contact>();

    DataSet ds = new DataSet("TimeRanges");
    using (SqlConnection conn = new SqlConnection("ConnectionString"))
    {
        SqlCommand sqlComm = new SqlCommand("Procedure1", conn);
        sqlComm.Parameters.AddWithValue("@paramname", paramvalue);
        //....
                
        sqlComm.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = sqlComm;

        da.Fill(ds);
                
        foreach(DataRow dr in ds.Tables[0].Rows)
        { 
            contact contact = new contact();

            contact.Name = dr["Name"].ToString();
            contact.Address = dr["Address"].ToString();

            contacts.Add(contact);
        }
    }
    return View(contacts);

}

hi cnuonline your code will work if we know the field names.

klbaiju

hi cnuonline your code will work if we know the field names.

Then don’t use list of complex objects , use list of strings.And add them to the view then iterate the html table attribute to display the records.

klbaiju

here it is a select query in sp. also pivot query and don’t know the field names

@using System.Data;
@model System.Data.DataTable
<table>
    <tr>
        @foreach (DataColumn dc in Model.Columns)
        {
            <th>@dc.ColumnName</th>
        }
    </tr>
   
    @foreach (DataRow dr in Model.Rows)
    {
        <tr>
            @foreach (DataColumn dc in Model.Columns)
            {
                <td>@dr[dc.ColumnName]</td>
            }
        </tr>
    }
   
</table>

Hi cnuonline,

any need of class file in model.

and what code should i write in controller

klbaiju

any need of class file in model.

Data table is the model for the view

klbaiju

and what code should i write in controller

refer the my first reply

Hi cnuonline,

i have solved my problem as per your example.

this is the code

code in controller

 public ActionResult Index()
        {

using (SqlConnection conn = new SqlConnection(@"Data Source=baiju-pcsqlexpress;Initial Catalog=MvcApplicationDataBase.Models.SimpleBookCatalog;Integrated Security=True"))
            {
                SqlCommand sqlComm = new SqlCommand("select * from dbo.books", conn);
               

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = sqlComm;
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                return View(dt);
            }

}

code in view

@model System.Data.DataTable
@using System.Data;

<h2>Report</h2>
<html>
<head>
<title>SQLQueryExample</title>
<style type="text/css">
table,th,td{border:1px solid black;border-collapse:collapse;}


</style>
</head>
<body>
<table>
    <thead>
    <tr>
    @foreach (DataColumn col in Model.Columns)   
    {         
        <th>@col.ColumnName</th>
    }    
    </tr>
    </thead>        
    <tbody>
    @foreach (DataRow row in Model.Rows)    
    {        
        <tr>
        @foreach (DataColumn col in Model.Columns)        
        {             
            <td>@row[col.ColumnName]</td>
           
        } 
         <td>@Html.ActionLink("Edit", "Edit", new { id = @row[0] }) |</td>
          <td>@Html.ActionLink("Delete", "Delete", new { id = @row[0] }) |</td>
           <td>@Html.ActionLink("Details", "Details", new { id = @row[0] }) </td> 
        </tr>
    }    
    </tbody>
</table>
</body>
</html>

this is working fine.

is this the right way of doing this kind of example in mvc.

is this completely follows mvc principle.

plz reply soon

Regards

Baiju

Here you are not violating any asp.net mvc rule.

SqlCommand sqlComm = new SqlCommand("select * from dbo.books", conn);

You have a few options which have been shown, If it was me I would open the database and look in the table called books and from there you can specify what the column names are and build a class around that and pass it to your view as a list.

The other things which is entirely up to you of course but from my preference I prefer to put SQL connections in a separate class within a folder called DataAccess of something more meaningful.

Leave a Reply