Update Databases Using ADO.NET in C#
Setup
If you have not already done so, open Visual Studio as well as Microsoft Access. The database in this tutorial is an actual Access file.We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
The Concept…
We will be using a DataGrid to display the information from the database as well as be able to edit, delete and add records to the database.Step one.
The first step we need to take is making sure we have our default page set up with the appropriate controls. Copy and paste the following code into your default.aspx page and save.<form id="form1" runat="server">
<div>
<asp:DataGrid ID="DataGrid1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyField="ID" ForeColor="#333333" GridLines="None" OnCancelCommand="DataGrid1_CancelCommand" OnDeleteCommand="DataGrid1_DeleteCommand" OnEditCommand="DataGrid1_EditCommand" OnUpdateCommand="DataGrid1_UpdateCommand">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditItemStyle BackColor="#2461BF" />
<SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<AlternatingItemStyle BackColor="White" />
<ItemStyle BackColor="#EFF3FB" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:EditCommandColumn CancelText="Cancel" EditText="Edit" UpdateText="Update"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PetName" HeaderText="Pet Name"></asp:BoundColumn>
<asp:BoundColumn DataField="PetType" HeaderText="Pet Type"></asp:BoundColumn>
<asp:ButtonColumn CommandName="Delete" Text="Delete"></asp:ButtonColumn>
</Columns>
</asp:DataGrid>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add" /></div>
</form>
As you can see, we’ve added a DataGrid, a few styling items and an “Add” button. When you switch over to design or split view, you will see exactly what has been added and the format it has been added in. Double click the button so that Visual Studio adds the OnClick event into your code behind.
Within the code behind, we will need to query the database to retrieve its information as well as include the OnClick event for the button and the Update, Cancel, Delete commands. Add the necessary methods and modify the OnCLick event and the Page_Load event as it appears below:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
ReadRecords();
}
private void ReadRecords()
{
OleDbConnection conn = null;
OleDbDataReader reader = null;
try
{
conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("db/Database1.mdb"));
conn.Open();
OleDbCommand cmd =
new OleDbCommand("Select * FROM Table1", conn);
reader = cmd.ExecuteReader();
DataGrid1.DataSource = reader;
DataGrid1.DataBind();
}
finally
{
if (reader != null) reader.Close();
if (conn != null) conn.Close();
}
}
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
The above piece of code simply reads the data from the database and binds it to the DataGrid. We can now add more functionality to the DataGrid by adding the Button Columns Add, Update and Cancel, as well as Delete. We do this by going into the Property Builder (Property Pages) and choosing these buttons from the Button Column list.First, we uncheck Create columns automatically at run time, and then we also add a Bound Column for each database column we want displayed by the DataGrid. For each of these Bound Columns, the Header Text and the Data Field should correspond with the column ID in the Access database.
Step two.
Now we must add the code to our edit and update methods. Each one will need to call the ReadRecords() method in order to go about changing or displaying the particular data field. Modify your code behind to include the following:protected void DataGrid1_CancelCommand(object source, DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
ReadRecords();
}
protected void DataGrid1_EditCommand(object source, DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = e.Item.ItemIndex;
ReadRecords();
}
protected void DataGrid1_UpdateCommand(object source, DataGridCommandEventArgs e)
{
int ID = (int)DataGrid1.DataKeys[(int)e.Item.ItemIndex];
string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string sql =
"UPDATE Table1 SET PetName=\"" + name +
"\", PetType=\"" + type + "\"" +
" WHERE ID=" + ID;
ExecuteNonQuery(sql);
DataGrid1.EditItemIndex = -1;
ReadRecords();
}
private void ExecuteNonQuery(string sql)
{
OleDbConnection conn = null;
try
{
conn = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("db/Database1.mdb"));
conn.Open();
OleDbCommand cmd =
new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null) conn.Close();
}
}
Save and run your code. You should now be able to modify the database content as you wish. If you created your own database for this, you may encounter some issues and these lie within the connection strings as well as the calling of particular fields from your table(s). For further reference, download the code source if you have not already done so.
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
