About Me

My photo
Hello, I'm a technology geek looking to upgrade my programming skills. These are some of the things I'm learning along my journey.

Saturday, August 18, 2012

ASP.NET Photo Viewer

This program selects photos based on location, displays a matching photo, caption and date.




To start, create a database and table with the following columns:

Example:

Photos Table
PhotoID - integer
Filename - nvarchar
Caption - nvarchar
Date - nvarchar
Location - nvarchar

Create the necessary labels, radio buttons and image control to display the page.



Add logic to connect to the database.
var dbPath = Server.MapPath("~/App_Data/TravelPhotos.mdb");
oleDbConnection1 = new OleDbConnection();
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath;
//Set the path to our image files into a variable for future use (performs this concatenation only once)
string MapPath1 = Server.MapPath("~/App_Data/TravelPhoto.mdb");


Create the variables that will hold the results from the database.
//Two-dimensional Array to hold the info retrieved from the DB
private string[,] m_strTravelInfo;

//Other module level variables
private string m_strFilePath;
private int m_intPhotoCount;
private int m_intCurrentIndex;


Add the logic to handle the check-changed events for the radio buttons.
Stored Procedures:
GetCountOfPhotosByLocation 
SELECT COUNT(*)
FROM Photos
WHERE Location=[@Location];
GetPhotoInfoByLocation
SELECT Filename, Caption, Date
FROM Photos
WHERE Location=[@Location];
protected void rbBigSur_CheckedChanged(object sender, EventArgs e)
{
  //load array
  //diplay first image, caption and date
  GetPhotoData("BigSur");
  //retreive m_intPhotoCount from Session variable
  m_strTravelInfo = (string[,])Session["s_strTravelInfo"];

  string strFileName = m_strTravelInfo[0, 0];
  lblCaption.Text = m_strTravelInfo[0, 1];
  lblTravelDate.Text = m_strTravelInfo[0, 2];
  imgMain.ImageUrl = "~/images/" + strFileName;
}// end rbBigSur_CheckedChanged

private void GetPhotoData(string strLocation)
{
 try
    {
     //Open our connection, provided that it isn't already open
     if (oleDbConnection1.State != ConnectionState.Open)
     {
       oleDbConnection1.Open();
     }
     //Find out how many photos in our DB correspond to the selected location. 
     m_intPhotoCount = GetPhotoCount(strLocation);
     //store m_intPhotoCount in a Session variable
     Session["s_intPhotoCount"] = m_intPhotoCount;
     //Use that value to size our TravelInfo array. 
     if (m_intPhotoCount == 0)
     { //Exit this function if the PhotoCount query returned 0 results
                Console.WriteLine("PhotoCount returned 0 results.  Please investigate.");
      return;
     }
     m_strTravelInfo = new string[m_intPhotoCount, 3];
     //Initialize the location parameter to our GetPhotoInfoByLocation ADO Command Object
     oleDbCommandGetPhotoInfoByLocation = new OleDbCommand("GetPhotoInfoByLocation_sp", oleDbConnection1);
     oleDbCommandGetPhotoInfoByLocation.CommandType = CommandType.StoredProcedure;
     oleDbCommandGetPhotoInfoByLocation.Parameters.Clear();
     oleDbCommandGetPhotoInfoByLocation.Parameters.Add("@Location", OleDbType.VarChar, 25);
     oleDbCommandGetPhotoInfoByLocation.Parameters["@Location"].Value = strLocation;
     //Use a DataReader to get the info from our database
            oleDbDataReader1 = oleDbCommandGetPhotoInfoByLocation.ExecuteReader(CommandBehavior.CloseConnection);
     int intLoopCounter = 0;
     //Loop through this data to populate our TravelInfo array.
     while (oleDbDataReader1.Read() == true)
     {
       m_strTravelInfo[intLoopCounter, 0] = oleDbDataReader1.GetString(0); //Filename
       m_strTravelInfo[intLoopCounter, 1] = oleDbDataReader1.GetString(1); //Caption
       m_strTravelInfo[intLoopCounter, 2] = oleDbDataReader1.GetString(2); //TravelDate
       intLoopCounter++;
      }

      oleDbDataReader1.Close();
      //Display the first photo from our collection matching this location. 
      m_intCurrentIndex = 0;
      //Store m_intCurrentIndex in a Session variable
      Session["s_intCurrentIndex"] = m_intCurrentIndex;
      //store the array in a Session object
      Session["s_strTravelInfo"] = m_strTravelInfo;
      }// end try
      catch (Exception ex)
      {
       Console.WriteLine(ex.Message);
      }
      finally
      { 
       oleDbConnection1.Close();
      }

}// end GetPhotoData


private int GetPhotoCount(string strLocation)
{
  try
  {
   //Set the parameter to allow us to find out how many photos match our selected location. 
   oleDbCommandGetCountOfPhotosByLocation = new OleDbCommand("GetCountOfPhotosByLocation_sp", oleDbConnection1);
   oleDbCommandGetCountOfPhotosByLocation.CommandType = CommandType.StoredProcedure;
   oleDbCommandGetCountOfPhotosByLocation.Parameters.Clear();
   oleDbCommandGetCountOfPhotosByLocation.Parameters.Add("@Location", OleDbType.VarChar, 25);
   oleDbCommandGetCountOfPhotosByLocation.Parameters["@Location"].Value = strLocation;
   //Execute our stored procedure to retrieve the number that we need
   int intNoOfPhotos = Convert.ToInt32(oleDbCommandGetCountOfPhotosByLocation.ExecuteScalar());
   //Send that value back to the calling function
   return intNoOfPhotos;
  }
  catch (Exception ex)
  {
   Console.WriteLine(ex.Message);
   return 0;
  }

}// end GetPhotoCount


Add code to handle the "Previos" and "Next" button click events.
protected void btnNext_Click(object sender, System.EventArgs e)
{
 try
    {
     if (Session["s_intPhotoCount"] != null)
       m_intPhotoCount = (int)Session["s_intPhotoCount"];

     if (m_intPhotoCount == 0)
     { //Exit this function if the PhotoCount query returned 0 results
      lblError.Text = "Please Select an Option Before Clicking on Next";
      lblError.Visible = true;
      return;
     }

     lblError.Visible = false;
     //retreive m_intPhotoCount from Session variable
     m_intPhotoCount = (int)Session["s_intPhotoCount"];
     //retreive m_intCurrentIndex from Session variable
     m_intCurrentIndex = (int)Session["s_intCurrentIndex"];    
            
     if (m_intCurrentIndex < m_intPhotoCount - 1)
     {
      m_intCurrentIndex += 1;
      //Store m_intCurrentIndex in a Session variable
      Session["s_intCurrentIndex"] = m_intCurrentIndex;
      DisplayInfo(m_intCurrentIndex);
     }
     else
     { 
      //Loop around to the other end of our array
      m_intCurrentIndex = 0;
      //Store m_intCurrentIndex in a Session variable
      Session["s_intCurrentIndex"] = m_intCurrentIndex;
      DisplayInfo(m_intCurrentIndex);
     }
   }// end try
   catch (Exception ex)
   {
    Console.WriteLine(ex.Message);
   }
}// btnNext_Click

private void DisplayInfo(int intCounter)
{
 //retreive m_intPhotoCount from Session variable
 m_strTravelInfo = (string[,])Session["s_strTravelInfo"];
 string strFileName = m_strTravelInfo[intCounter, 0];
 lblCaption.Text = m_strTravelInfo[intCounter, 1];
 lblTravelDate.Text = m_strTravelInfo[intCounter, 2];

 if (File.Exists(m_strFilePath + strFileName))
 {
  imgMain.ImageUrl = "~/images/" + strFileName;
 }

}// end DisplayInfo



Click ((here)) for the project code.

Thursday, August 16, 2012

Exchanging Data Between XML and SQL Server With Validation

This post is a continuation of "Exchanging Data Between XML and SQL Server". Here we will add data validation before the data is written to the XML file.

The first thing to do is let Visual Studio generate an XSD (schema) file for you. Double-click on the "books.xml" file in the "Solution Explorer" and click on "XML" at the top menu. Select "Create Schema".


Once the "books.xsd" file has been created, right-click on the "books.xsd" tab and select "Save books.xsd" and choose the current location the "books.xml" is located.


Once this is done, the code to read the XSD file can be done before the XML is processed.


Example
 //*******************test schema******************
 try
    {
      XmlDocument xmld = new XmlDocument();
      xmld.Load(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoDatabaseWithValidation\XMLtoDatabaseWithValidation\books.xml");
      xmld.Schemas.Add(null, @"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoDatabaseWithValidation\XMLtoDatabaseWithValidation\books.xsd");
      xmld.Validate(ValidationCallBack);
    }
catch (Exception ex)
    {
      Console.WriteLine("Caught a problem: " + ex.Message);
    }
//******************end test schema***************

private void ValidationCallBack(object sender, ValidationEventArgs e)
{
   Console.WriteLine("There is a problem with the schema or xml document: " + "\n" + e.Message);
            
   throw new Exception();
}


Once this is done, you can try running the XML with no problems and then introduce errors in either the XSD file or XML file to see what happens.

Click ((here)) for the project code.

Exchanging Data Between XML and SQL Server

Here I will convert SQL Server to XML and back from XML to SQL Server. I will be using the "bookstore.xml" file to read and write. I made some modifications to for this example:



Create a Bookstore database. The XML nodes, attributes, sibling nodes and child nodes must be represented in one database record.

Example:

Books Table
RecordID - type int (identity column)
Genre - type varchar
ISBN - type varchar
PublicationDate - type date
Title - type varchar
AuthorFirstName - type varchar
AuthorLastname - type varchar
Price - type int

Create a basic form with 2 buttons with labels to trigger the read and write actions.


Once the Bookstore database has been created with a "books" table, add some sample data to the first row.



Add the "bookstore.xml" file to the project and make a copy of it with the name "bookstore1.xml". Create the code to process the "Write XML to Database" button.

Example:

        private void btnWriteXMLtoDatabase_Click(object sender, EventArgs e)
        {

            // Create an XmlReader
            //using (XmlReader reader = XmlReader.Create(new StringReader(xmlString)))
            using (XmlReader reader = XmlReader.Create(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books.xml"))
            {
                reader.ReadToFollowing("book");
                reader.MoveToFirstAttribute();
                strGenre = reader.Value;
                reader.MoveToNextAttribute();
                strPubDate = reader.Value;
                datPubDate = DateTime.Parse(strPubDate); //convert to date format
                reader.MoveToNextAttribute();
                strISBN = reader.Value;
                reader.ReadToFollowing("title");
                strTitle = reader.ReadInnerXml();
                reader.ReadToFollowing("first-name");
                strAuthorFirstName = reader.ReadInnerXml();
                reader.ReadToFollowing("last-name");
                strAuthorLastName = reader.ReadInnerXml();


            }

            //Output to database
            try
            {
                conn.ConnectionString = @"Data Source=KEN-HP\SQLSERVER2008R2;Initial Catalog=Bookstore;Integrated Security=True";
                conn.Open();

                string queryStmt = "INSERT INTO Books(Genre, PublicationDate, ISBN, Title, AuthorFirstName, AuthorLastName)" +
                                    "VALUES(@Genre, @PublicationDate, @ISBN, @Title, @AuthorFirstName, @AuthorLastName)";

                using (SqlCommand _cmd = new SqlCommand(queryStmt, conn))
                {
                    _cmd.Parameters.Add("@Genre", SqlDbType.NVarChar, 50);
                    _cmd.Parameters.Add("@PublicationDate", SqlDbType.DateTime, 50);
                    _cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50);
                    _cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50);
                    _cmd.Parameters.Add("@AuthorFirstName", SqlDbType.NVarChar, 50);
                    _cmd.Parameters.Add("@AuthorLastName", SqlDbType.NVarChar, 50);
                    _cmd.Parameters["@Genre"].Value = strGenre;
                    _cmd.Parameters["@PublicationDate"].Value = datPubDate;
                    _cmd.Parameters["@ISBN"].Value = strISBN;
                    _cmd.Parameters["@Title"].Value = strTitle;
                    _cmd.Parameters["@AuthorFirstName"].Value = strAuthorFirstName;
                    _cmd.Parameters["@AuthorLastName"].Value = strAuthorLastName;

                    _cmd.ExecuteNonQuery();
                }

            }// end try

            catch (Exception ex)
            {
                MessageBox.Show("Failed to insert to Books Table");
            }
            finally
            {
                conn.Close();
            }

            lblXMLtoDatabase.Visible = true;

        }// end btnWriteXMLtoDatabase_Click



Create code to write from the database to XML.

Example:


        private void btnWriteToXML_Click(object sender, EventArgs e)
        {
            conn.ConnectionString = @"Data Source=KEN-HP\SQLSERVER2008R2;Initial Catalog=Bookstore;Integrated Security=True";


            //********************READ*********************************
            try
            {
                conn.Open();

                SqlCommand command = new SqlCommand(
                    "SELECT * FROM Books;",
                    conn);

                SqlDataReader reader = command.ExecuteReader();


                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        strGenre = (string)reader[1];
                        datPubDate = (System.DateTime)reader[2];
                        strPubDate = datPubDate.ToString();
                        strISBN = (string)reader[3];
                        strTitle = (string)reader[4];
                        strAuthorFirstName = (string)reader[5];
                        strAuthorLastName = (string)reader[6];

                        // Open an XML document.
                        System.Xml.XmlDocument myXmlDocument = new System.Xml.XmlDocument();
                        myXmlDocument.Load(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books1.xml");
                        System.Xml.XmlNode myXmlNodeFirstBook = myXmlDocument.DocumentElement.FirstChild;

                        // Create a Book element and populate its attributes
                        System.Xml.XmlElement XmlElementMyBook = myXmlDocument.CreateElement("book");
                        XmlElementMyBook.SetAttribute("genre", strGenre);
                        XmlElementMyBook.SetAttribute("publicationdate", strPubDate);
                        XmlElementMyBook.SetAttribute("ISBN", strISBN);
                        // Insert the new element into the XML tree under Catalog
                        myXmlDocument.DocumentElement.InsertBefore(XmlElementMyBook, myXmlNodeFirstBook);

                        // Create a new child of the book element
                        System.Xml.XmlElement myXmlElement2 = myXmlDocument.CreateElement("title");
                        myXmlElement2.InnerText = strTitle;
                        // Insert the new element under the node we created
                        XmlElementMyBook.AppendChild(myXmlElement2);

                        // Create a new child of the book element
                        System.Xml.XmlElement myXmlElement3 = myXmlDocument.CreateElement("author");
                        XmlElementMyBook.AppendChild(myXmlElement3);

                        // Create new child of the author element
                        System.Xml.XmlElement myXmlElement4 = myXmlDocument.CreateElement("first-name");
                        myXmlElement4.InnerText = strAuthorFirstName;
                        myXmlElement3.AppendChild(myXmlElement4);

                        // Create new child of the author element
                        System.Xml.XmlElement myXmlElement5 = myXmlDocument.CreateElement("last-name");
                        myXmlElement5.InnerText = strAuthorLastName;
                        myXmlElement3.AppendChild(myXmlElement5);

                        myXmlDocument.Save(@"C:\Users\ken\Documents\Visual Studio 2010\Projects\XMLtoSQLserver\XMLtoSQLserver\books1.xml");

                    }// end while
                }
                else
                {
                    Console.WriteLine("No rows found");
                }// end if (reader.HasRows)

                reader.Close();

            }// end try

            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to Books Table");
            }
            finally
            {
                conn.Close();
            }


            //********************END READ******************************

            lblDataWrittenToXML.Visible = true;

        }// end btnWriteToXML_Click

This example is very basic so you will need to manually add , remove and view data from your sample XML files and database.

Click ((here)) for the project code. 


Thursday, August 2, 2012

Create a Basic Quiz Engine for ASP.NET

This is a continuation of "Create a Basic Quiz Engine (part4)".

Here we will convert the WinForm version of the Basic Quiz Engine to an ASP.NET version.

The are some additions and subtractions that are necessary to convert a Winform project to ASP.NET.
The page state must be maintained between page loads. I will use the session object to accomplish this.

Example:
//Creating a session variable
Session["m_strCorrectAnswerDescription"] = strCorrectAnswerDescription;

//................................
//Using the contents of the session variable
strCorrectAnswerDescription = (string)Session["m_strCorrectAnswerDescription"];


 Layout is different with ASP.NET. Positioning of controls is accomplished by changing the "positioning" attribute to "absolute".

Example:

The look and layout of the various controls can be modified by changing their setting in the "Properties Panel" and CSS

Example:






Here is a sample layout for the 2 pages that is similar to the Winform application.







The ASP.NET GridView control is different that the Winform version and must be data bound to a data source. The requires some changes in the .aspx file and .cs file.

Example:

Create the SqlCommand in the questionPage.aspx file and layout the columns you with to appear in the gridView.




Read the UserScore Table usine the SqlDataSource2 to create a gridView in "questionPage.aspx.cs".
dataGridView1.DataBind();


Click here for the project code. Just create a SQL Server database with test data, edit the connection string and run it.