SQL Data Access ThroughControls
Updating the database from the backend is great and all, but if you aren’t exactly all too excited about that, we can always update the database from the actual controls on our web page. This tutorial will show how we can use a GridView control to display and update data in a SQL database.
For our DetailsView, we have its data source set to the SqlDataSource, which is connected to the database. We added some styling tags in there along with the fields for the header texts and content. For the actual Gridview, we changed it by its styling content and a command field setting the show edit button to true, which will give the user the ability to edit the presented text. We include a paging option in this control as well. Seeing a lot of database information can be overwhelming, especially if it continue for pages at a time even after sorting. The SelectedRowStyle will act as an indicator for the user to know which row they selected.
The SqlDataSource has the connection string within it from our web.config file, and a select and insert command as well as insert parameters. The select command states to select everything from tblOne. The Insert command states to insert theID, theName and theCity value into their respective column, into tblOne. The InsertParameters tag contains three parameters theID, theName and theCity which has the definition of each parameter. Each parameter has its own variable type.
The final product when ran appears as follows:
Button
Setup
Go ahead and open Visual Studio if you haven’t already done so. Set up a new project (Web Form) with the name of your choice.Need help with cloud hosting? Try Server Intellect. We used them for our cloud hosting services and we are very happy with the results!
Step 1.
The first step we will be taking is creating our database. The database needed for this tutorial has a simple three column table with an ID, name and city. Insert some additional content in order for the final results to be seen. The definition for this table is as follows:Step 2.
The next step we will take is creating our user interface. We must be able to edit data and page throughout. This will need a SqlDataSource, a GridView, and a DetailsView with a few properties used to be able to add, edit and page throughout the database in particular increments (of your choice). The code for the user interface is as follows:Code Block
Default.aspx
<div>
<h1>
Edit Database</h1>
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Height="50px"
Width="125px" AutoGenerateRows="False" DataKeyNames="theID">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
<EditRowStyle BackColor="#2461BF" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Fields>
<asp:BoundField DataField="theID" HeaderText="ID" ReadOnly="True" SortExpression="theID" />
<asp:BoundField DataField="theName" HeaderText="Name" SortExpression="theName" />
<asp:BoundField DataField="theCity" HeaderText="City" SortExpression="theCity" />
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333"
GridLines="None" PageSize="5">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="theID" HeaderText="ID" SortExpression="theID" />
<asp:BoundField DataField="theName" HeaderText="Name" SortExpression="theName" />
<asp:BoundField DataField="theCity" HeaderText="City" SortExpression="theCity" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [tblOne]"
InsertCommand="INSERT INTO [tblOne] ([theID], [theName], [theCity]) VALUES (@theID, @theName, @theCity)">
<InsertParameters>
<asp:Parameter Name="theID" Type="Int32" />
<asp:Parameter Name="theName" Type="String" />
<asp:Parameter Name="theCity" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</div>
The SqlDataSource has the connection string within it from our web.config file, and a select and insert command as well as insert parameters. The select command states to select everything from tblOne. The Insert command states to insert theID, theName and theCity value into their respective column, into tblOne. The InsertParameters tag contains three parameters theID, theName and theCity which has the definition of each parameter. Each parameter has its own variable type.
The final product when ran appears as follows:
A Few Last Words…
No code behind needs to be written for this simple data manipulation using the GridView. Visual Studio generates the code for you. Learning how to use and manipulate databases is easy and fun with ASP.NET and we intend to help you every step of the way. Join us next time for additional database tutorials!Button
We chose Server Intellect for its cloud hosting, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
