How can I use aGridView and maintain good MVC?

[nathj]

So I've got to learn ASP.Net and C# for work. The organisation has bought in some training and some books. I'm using the books and the web ahead of the training next month.

Presently I have worked through some tutorials online and in the books. I've got a basic handle on C# now and am working on some mock projects for ASP.Net. A good friend of mine , pointed me in the direction of the GridView as a very useful tool. I must agree this is very useful and I can see the benefits of it.

However, I feel I must be missing something with this as the code I have so far looks like:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MySQLConnStr %>"
            ProviderName="<%$ ConnectionStrings:MySQLConnStr.ProviderName %>" SelectCommand="SELECT a.id, a.name, a.country  from test_users a"
            UpdateCommand="UPDATE test_users SET
                name=@name,
                country=@country
                WHERE id=@id"
            DeleteCommand="Delete from test_users where id = @id">
            <UpdateParameters>
                <asp:Parameter Name="name" Type="String" />
                <asp:Parameter Name="country" Type="String" />
                <asp:Parameter Name="id" Type="Int32" />
            </UpdateParameters>
            <DeleteParameters>
                <asp:Parameter Name="id" Type="Int32" />
            </DeleteParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" PageSize="4" AllowSorting="True" AutoGenerateColumns="False"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" InsertVisible="False"
                    ReadOnly="true" />
                <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
                <asp:BoundField DataField="country" HeaderText="country" SortExpression="country" />
                <asp:CommandField ButtonType="Image" DeleteImageUrl="~/image/delete.png" EditImageUrl="~/image/edit.png"
                    ShowDeleteButton="True" ShowEditButton="True" />
            </Columns>
        </asp:GridView>

My issue with this is the mixing of the view and model layers. Surely I've got something wrong here right?

I had spent some time developing a simple Data Abstraction Layer to make a connection and issue queries. This used the Web config file and can be usedvery easily.

So, the point of all this rambling is to ask the question can the update and delete command be housed at the very least in the code behind file? I feel really uncomfortable having the code where it is.

I have tried another approach that I feel is heading in the right direction:

<asp:GridView ID="grd_userList" runat="server" AllowSorting="false" AllowPaging="false"
                AutoGenerateColumns="true" AutoGenerateDeleteButton="true" AutoGenerateEditButton="true"
                AutoGenerateSelectButton="true" OnRowWEditing="grd_userList_edit" />

The data is then bound in the code file usnig OdbcAdapter and OdbcCommand and this presents the data but I'm not getting the built in edit and delete functionality so I need to know what events I need to write or how to make sure the events I want to fire anre fired.

If anyone has any help on this that would be great, perhaps point me in the right direction.

Cheers
nathj

[nathj]

Hi,

I figured it out. So, in case anyone else is working on this sort of thing here is what I have done.

About.aspx

 <asp:SqlDataSource ID="sdsEmployee" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Enterprise4 %>" >
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
            AutoGenerateColumns="False" DataKeyNames="id" 
            DataSourceID="sdsEmployee">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
                    ShowSelectButton="True" />
                <asp:BoundField DataField="id" HeaderText="id" ReadOnly="True" 
                    SortExpression="id" />
                <asp:BoundField DataField="name" HeaderText="name" 
                    SortExpression="name" />
                <asp:BoundField DataField="username" HeaderText="username" 
                    SortExpression="username" />
                <asp:BoundField DataField="email" HeaderText="email" 
                    SortExpression="email" />
            </Columns>
        </asp:GridView>

About.aspx.cs

sdsEmployee.SelectCommand = "SELECT [id], [name], [username], [email] FROM [employee]";
        sdsEmployee.DeleteCommand = "DELETE FROM [employee] WHERE [id] = @original_id";
        sdsEmployee.InsertCommand = "INSERT INTO [employee] ([id], [name], [username], [email]) VALUES (@id, @name, @username, @email)";
        sdsEmployee.UpdateCommand = "UPDATE [employee] SET [name] = @name, [username] = @username, [email] = @email WHERE [id] = @original_id";
        sdsEmployee.OldValuesParameterFormatString = "original_{0}";

        sdsEmployee.InsertParameters.Add("id", DbType.Int32, "");
        sdsEmployee.InsertParameters.Add("name", DbType.String, "");
        sdsEmployee.InsertParameters.Add("username", DbType.String, "");
        sdsEmployee.InsertParameters.Add("email", DbType.String, "");

        sdsEmployee.UpdateParameters.Add("name", DbType.String, "");
        sdsEmployee.UpdateParameters.Add("username", DbType.String, "");
        sdsEmployee.UpdateParameters.Add("email", DbType.String, "");

        sdsEmployee.DeleteParameters.Add("original_id", DbType.Int32, "");

This is in a method that is called by Page_Load.

This is not perfect MVC as I would like to shift smoe of the code here further away from the view but it is a step in the right direction.

Hopefully this may help out someone else who is puzzling the same issue.

Cheers
nathj