ASP.NET Basics (Part 9): Different Strokes - In With The New
(Page 3 of 5 )
In With The New Let's now look at adding a new record to the database.
<%@ Page Language="C#" Debug="true" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<SCRIPT language=C# runat="server">
void Page_Load()
{
if(IsPostBack)
{
if(name.Value == "" )
{
error.Style["color"] = "Red";
error.Text = "Please enter your name.";
return;
}
else
{
// we have all the data,
// let's insert it
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter = new SqlDataAdapter(strSQL, objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
// add a new row to our local DataTable object
DataRow objNewRecord = objTab.NewRow();
// add the data from the form
// into the new row
objNewRecord["name"] = name.Value;
objNewRecord["homeworld"] = homeworld.Value;
objNewRecord["species"] = species.Value;
objNewRecord["gender"] = gender.Value;
objNewRecord["affiliation"] = affiliation.Value;
// add the new record to our local DataTable object
objTab.Rows.Add(objNewRecord);
// build the required SQL command
// automatically using the CommandBuilder Object
SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
objAdapter.InsertCommand = objCmdBuilder.GetInsertCommand();
// update the database server to reflect the changes
objAdapter.Update(objDataSet, "starwars");
// clear up memory by closing all objects
objDataSet.Clear();
objConn.Close();
// clear the Form fields
name.Value = "";
homeworld.Value = "";
species.Value = "";
gender.Value = "";
affiliation.Value = "";
error.Text = "Record added successfully.";
return;
}
}
}
</SCRIPT>
<BASEFONT face=Arial>
<FORM runat="server">
<DIV align=center>
<TABLE cellSpacing=5 cellPadding=10 border=2>
<TBODY>
<TR>
<TD align=middle colSpan=2><B>Registration Desk</B></TD></TR>
<TR>
<TD colSpan=2><asp:label id=error runat="server" name="error"
text="* - Indicates compulsory field"></asp:label></TD></TR>
<TR>
<TD>Name<B>*</B></TD>
<TD><INPUT id=name style="BACKGROUND-COLOR: #ffffa0" runat="server"></TD></TR>
<TR>
<TD>Home World</TD>
<TD><INPUT id=homeworld runat="server"></TD></TR>
<TR>
<TD>Species</TD>
<TD><INPUT id=species runat="server"></TD></TR>
<TR>
<TD>Gender</TD>
<TD><INPUT id=gender runat="server"></TD></TR>
<TR>
<TD>Affiliation</TD>
<TD><INPUT id=affiliation runat="server"></TD></TR>
<TR>
<TD align=middle colSpan=2><INPUT id=submit type=submit value=Submit runat="server"></TD></TR>
<TR>
<TD align=middle colSpan=2><A href="list.aspx">Get complete
listing</A></TD></TR></TBODY></TABLE></DIV></FORM>
That's a lot of code for a simple INSERT statement. But don't despair - I'll dissect it with you and it should all make sense shortly.
Basically, the above code listing consist of two parts. The first part displays an HTML form and asks the user for input, while the second part takes that user input and inserts it into the database. Take a look at the skeletal structure of the code, which illustrates this clearly:
<SCRIPT language=C# runat="server">
void Page_Load()
{
if(IsPostBack)
{
// all the code for data validation and insertion
}
}
</SCRIPT>
<!-- form goes here -->
Here, the web server will use the "IsPostBack" variable to check if the form is being displayed for the first time to the user, or whether it is POST-ing data submitted by the user. If the former, then the value of "IsPostBack" variable is false. As a result, the compiler skips the entire block of code responsible for data insertion, and the browser then proceeds to render the HTML form to the user. On the other hand, if the user was indeed submitting data to the server, then the "IsPostBack" variable will be true and the code to insert the record into the database will be triggered.
Let's begin by taking a look at the HTML form:
<FORM runat="server">
<DIV align=center>
<TABLE cellSpacing=5 cellPadding=10 border=2>
<TBODY>
<TR>
<TD align=middle colSpan=2><B>Registration Desk</B> </TD></TR>
<TR>
<TD colSpan=2><asp:label id=error runat="server" name="error"
text="* - Indicates compulsory field"></asp:label></TD>a
<TR>
<TD>Name<B>*</B></TD>
<TD><INPUT id=name style="BACKGROUND-COLOR: #ffffa0" runat="server"></TD></TR>
<TR>
<TD>Home World</TD>
<TD><INPUT id=homeworld runat="server"></TD></TR>
<TR>
<TD>Species</TD>
<TD><INPUT id=species runat="server"></TD></TR>
<TR>
<TD>Gender</TD>
<TD><INPUT id=gender runat="server"></TD></TR>
<TR>
<TD>Affiliation</TD>
<TD><INPUT id=affiliation runat="server"></TD></TR>
<TR>
<TD align=middle colSpan=2><INPUT id=submit type=submit value=Submit runat="server"></TD></TR>
<TR>
<TD align=middle colSpan=2><A href="list.aspx">Get complete
listing</A></TD></TR></TBODY></TABLE></DIV></FORM>
If you've been following along, you'll see that this is a plain-vanilla HTML form which merely displays various input fields to the user. Here's what it looks like:
The user is compulsorily required to enter a name - hence the following lines of code, which verify the presence of this information.
<%
if(IsPostBack)
{
if(name.Value == "" )
{
error.Style["color"] = "Red";
error.Text = "Please enter your name.";
return;
}
else
{
// name exists
// insert the record
}
}
%>
Here's what happens if the data is absent:
If things are all hunky-dory, I can go ahead and prepare to insert the record. This involves the usual suspects: creating an SqlConnection object by providing database access details and user credentials, and then creating a SqlDataAdapter to connect to the database. Since I plan to update the "starwars" table, a simple SELECT query is needed to fetch the contents of this table from the database; the result set can then be bound to the SqlDataAdapter:
<%
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
%>[code]
The next step is to create a DataTable object and store the
result set in it. This is needed to manipulate the information using the
different methods of the DataTable object.
[code] <%
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
%>
Once a DataTable object has been instantiated, a new DataRow object can be created via the NewRow() method of the object. The fields of this row can now be accessed as elements, and the data entered into the form by the user can be inserted into this row simply by assigning them to the appropriate fields.
<%
// add a new row to our local DataTable object
DataRow objNewRecord = objTab.NewRow();
// add the data from the form
// into the new row
objNewRecord["name"] = name.Value;
objNewRecord["homeworld"] = homeworld.Value;
objNewRecord["species"] = species.Value;
objNewRecord["gender"] = gender.Value;
objNewRecord["affiliation"] = affiliation.Value;
// add the new record to our local DataTable object
objTab.Rows.Add(objNewRecord);
%>
At this point, you've essentially inserted a new record into the DataTable. But this isn't enough in itself - the data still needs to be saved to the actual database. In order to do this, it is necessary to instantiate another object, the SqlCommandBuilder object, which takes care of building the SQL commands needed to transfer the changes made in the local copy to the database server.
<%
// build the required SQL command
// automatically using the CommandBuilder Object
SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
objAdapter.InsertCommand = objCmdBuilder.GetInsertCommand();
// update the database server to reflect the changes
objAdapter.Update(objDataSet, "starwars");
%>
The first step is to give the SqlCommandBuilder object something to start with; here, the SqlDataAdapter. If you remember, this was created using a simple SELECT query. Now, this object will proceed to automatically create the SQL INSERT, UPDATE or DELETE commands needed after studying the SELECT query.
Since this example is all about inserting data into the table, the GetInsertCommand() method of the SqlCommandBuilder object is all I need, followed by a call to the Update() method of the SqlDataAdapter to execute this command on the server. For the records, you can use the GetUpdateCommand() and GetDeleteCommand() methods as well for updates and deletions.
You can view the newly-inserted record by checking the records on the server, or by using the very first script in this article to display all the records from the database.
Next: Winds of Change >>
More ASP.NET Articles
More By Harish Kamath (c) Melonfire
/ 18 
