Add new web site

This document is an extension of doc_ado.Connected1.htm, and here we focusing on the followings.

Using Command to Update (and delete records, row )

The UpDate/Edit/Cancel worked, when we used code from a Class in App_code; but Delete and Sort did not work.

Sort Method with DataGrid ( as you will note later in the part of this document, ) where as update worked.

In document three we will show how to add new data, forcing with Button_Click Event.

Unsolved Issues : Edit, Sort and Inset Methods. (see appendix)

It appeared to me that Objects are very loosely connected or get disconnected

AspNet.Unleashed page 827 describes a Delete method, but did not work for me.

To bypass above we hard coded insert :Document3

http://msdn.microsoft.com/en-us/library/aa719927.aspx

Add new web site

We are going to sue same DataBase with connection string as show below. First add a class adoMain.cs, in app_code folder

Step:2

update the code Web.config

From

<connectionStrings/>
<system.web>


To

<connectionStrings>
<add name="adoCnn" connectionString="Data Source=MANAS6\SQLEXPRESS;Initial Catalog=adoConnect1;Integrated Security=True"
providerName="System.Data.SqlClient;" />

</connectionStrings>

Edit code in adoMain.cs

//The methods created in this class will be used with Griview in default.aspx

using System;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;

/// <summary>
/// Summary description for adoMain
/// </summary>
public class adoMain
{
static adoMain()
{
_connectionString = WebConfigurationManager.ConnectionStrings["adoCnn"].ConnectionString;

}
public adoMain()
{
//
// TODO: Add constructor logic here
// _connectionString = WebConfigurationManager.ConnectionStrings["addCnn"].ConnectionString;
//
}
private static readonly string _connectionString;
private string _sid;
private string _job;
private string _firstName;
private string _lastName;
private string _note;
public string strTime;
public string update_success;
public string sid
{
get { return _sid; }
set { _sid = value; }
}
public string Job
{
get { return _job; }
set { _job = value; }
}
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
public string Note
{
get { return _note; }
set { _note = value; }
}
public List<adoMain> GetAll()
{
List<adoMain> results = new List<adoMain>();
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM Main", cnn);
cnn.StatisticsEnabled = true;
using (cnn)
{
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
adoMain adObj = new adoMain();
adObj.sid = (string)reader["sid"];
adObj.Job = (string)reader["Job"];
adObj.FirstName = (string)reader["FirstName"];
adObj.LastName = (string)reader["LastName"];
adObj.Note = (string)reader["Note"];
results.Add(adObj);
}

}

IDictionary currentStatistics = cnn.RetrieveStatistics();
long bytesReceived = (long)currentStatistics["BytesReceived"];
long bytesSent = (long)currentStatistics["BytesSent"];
update_success = "Data Loaded";
return results;
}
public void UpDate(string sid, string job, string firstname, string lastname, string note)
{
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Main SET FirstName =@Firstname,Job=@Job,LastName = @LastName, Note=@Note WHERE sid=@sid", cnn);
cmd.Parameters.AddWithValue("@Job", job);
cmd.Parameters.AddWithValue("@FirstName", firstname);
cmd.Parameters.AddWithValue("@LastName", lastname);
cmd.Parameters.AddWithValue("@Note", note);
cmd.Parameters.AddWithValue("@sid", sid);
try{
using (cnn)
{
cnn.Open();
cmd.ExecuteNonQuery();
}

}catch(Exception error)
{
update_success = " Could not Update " + error.Message.ToString();
}
finally
{
update_success += "<br/>Row Update Suceessfull :"+ cnn.State.ToString();;
}
}
public void delete(string sid)
{
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE Main WHERE sid=@sid", cnn);
cmd.Parameters.AddWithValue("@sid", sid);
using (cnn)
{
cnn.Open();
cmd.ExecuteNonQuery();
}
update_success += "<br/>Row Delete Suceessfull";
}
}

Add the follwoing code in default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>mm.AdoConnectd 1: Connected ADO</title>
<style type="text/css">
#div1
{
position:absolute; background-color: #FFFFCC;
top: 15px;
left: 10px;
width: 350px;
}
body
{
background-color: Gray;
}
hr { width: 331px; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div id="div1">
<asp:GridView ID="GV1" runat="server" DataSourceID="adoObj"
Caption="Ado Connected :DataReader" >
<Columns>
<asp:CommandField ShowSelectButton="True" />
</Columns>
</asp:GridView>
<hr align="left" width="10px" />
<asp:DetailsView ID="DV1" runat="server" DataSourceID="adoObj"
AllowPaging="True" Caption="Details View " Width="333px" />
<asp:ObjectDataSource ID="adoObj" TypeName="adoMain" SelectMethod="GetAll" runat="server" >
</asp:ObjectDataSource>
<br />
<asp:Label ID="L1" runat="server" />
</div>
</form>
</body>
</html>

But Edit and update buttons need to be added

Next screen select your class adoMain, click Next

You will note, the GetAll() method, returns awaits

To update select correct method

We did not create any insert Method, but created delete

Now Reset

Now Runtime analysis

Now Edit David Tech to Tech Level2, in GridView and notice the change.

Click on Update, it will

Appendix:

Sort did not work (This code was deleted)