using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;    // Using Sql classes
using System.Web.Configuration; // Using web.config file

public partial class Lec7_Intro_ASPNet_WritingDatabase : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection;
        SqlCommand command;
        int numRowsAdded;
        int id;
        string theName;
        string theAge;
        string theCourse;

        // Set the content type
        Response.ContentType = "text/plain";
        Response.ContentEncoding = System.Text.Encoding.UTF8;

        // Read the information posted to this page from the form
        theName = Request.Form["studentNameText"];
        theAge = Request.Form["studentAgeText"];
        theCourse = Request.Form["studentCourseText"];

        // If we got to this page without posting from the Form
        // then display the form for the user to enter the data.
        if (theName == null)
        {
            Server.Transfer("DataEntryForm.htm");
        }

        // To include new lines in string, use verbatim string by adding @ symbol
        Response.Write(@"
            <html xmlns=""http://www.w3.org/1999/xhtml"">
            <head runat=""server"">
            <title>Untitled Page</title>
            </head>
            <body>");

        try
        {
            // Connect to the database using the connection string in the web.config file
            connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

            // Create an INSERT Sql statement
            // To prevent an Sql injection attack, we add parameters with names starting with an @ symbol
            command = new SqlCommand("INSERT INTO Student(Name,Age,Course) VALUES(@Name, @Age, @Course)", connection);

            // Replace the parameters with the actual values read from the form
            command.Parameters.AddWithValue("@Name", theName);
            command.Parameters.AddWithValue("@Age", theAge);
            command.Parameters.AddWithValue("@Course", theCourse);

            using(connection)
            {
                // Open the connection
                connection.Open();

                // Insert the record by executing the command
                numRowsAdded = command.ExecuteNonQuery();

                // Use @@IDENTITY to work out the primary key of 
                // the new row and display it
                command.CommandText = "SELECT @@IDENTITY";
                id = Convert.ToInt32(command.ExecuteScalar());
                Response.Write("Customer added, ID is " + id);

                // Close the connection
                connection.Close();
            }
        }
        catch
        {
            Response.Write("Error. Customer not added");
        }

        Response.Write(@"
            </div>
            </form>
            </body>
            </html>
            ");

    }
}