Reading Online Excel File and Manage it in Microsoft SQL Database

• Download source - 27.46 KB

Introduction

For software developers who have been working for long days, there might be a need to read online files from desktop or web applications and then store the data in local system or database. It is a little complex if you have no experience in it. There are several ways in which you can do it. In this demonstration, I will explain it from my experience to handle this type of situation.

Background

I have an Excel file online, the URL is http://velocitypath.com/odesk/buildings.csv. I need to read this file and manage it in Microsoft SQL Server database. The structure of the Excel file is given below:

Let’s Get Started

To store data from Excel file to database, it is required to create a table structure in Microsoft SQL Server database.

• Open Microsoft SQL Server Management Studio
• Create a table structure like below:

Open Microsoft Visual Studio 2010

• Create a New Project (File>New>Project>Visual C#>Windows Form Application)
• Name it as ReadingOnlineExcelFile

By default, the new project includes a form called ReadExcel.cs. Design the form like below:

To read the online file, you need to send web request. After processing successful web request, the web server will return a web response. To accomplish this, you need to use HttpWebRequest, HttpWebResponse classes. And WebRequest class is used to create a web request. All these classes can be found in System.Net namespace.

using System.Net;

Now, create a method for handling web request and to get counter response. Our intention is to get StreamReader object as a return value of this method. So you need to include:

using System.IO;

#region Method

private StreamReader GetStream()
        {
            String strURL = String.Empty;
            strURL = "http://velocitypath.com/odesk/buildings.csv";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(strURL);

            StreamReader reader;

            using (HttpWebResponse response =(HttpWebResponse)request.GetResponse())
            {
                reader = new StreamReader(response.GetResponseStream());
            }

            return reader;
        }

#endregion Method

In the above method, WebRequest.Create() is used to create web request, to get response from web request GetResponse(), to read stream from web response, GetResponseStream() is used.

Add System.Configuration reference:

Add the following namespace for data and SQL Operation.

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

Add the following code in button click event:

private void btnReadExcel_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["SQLConnection"].ToString()))
            {
                conn.Open();

                StreamReader streamReader;

                streamReader = GetStream();

                streamReader.ReadLine();
                while (!streamReader.EndOfStream)
                {
                    String []row = streamReader.ReadLine().ToString().Split(',');

                    SqlCommand selectCommand;
                    selectCommand = new SqlCommand("SELECT * FROM Location
                    WHERE Latitude=@Latitude AND Longitude=@Longitude", conn);

                    selectCommand.Parameters.Add("@Latitude",
                    SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
                    selectCommand.Parameters.Add("@Longitude",
                    SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);

                    SqlDataReader reader = selectCommand.ExecuteReader();

                    if (!reader.HasRows)
                    {
                        reader.Close();

                        SqlCommand insertCommand;
                        insertCommand = new SqlCommand("INSERT INTO Location
                        (Latitude,Longitude,BuildingName)
                        VALUES(@Latitude,@Longitude,@BuildingName)", conn);

                        insertCommand.Parameters.Add("@Latitude",
                        SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
                        insertCommand.Parameters.Add("@Longitude",
                        SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);
                        insertCommand.Parameters.Add("@BuildingName",
                        SqlDbType.VarChar, 100).Value = row[2].ToString();

                        int intResult;
                        intResult = insertCommand.ExecuteNonQuery();
                    }
                }
            }
        }

In the above code, the following GetStream() returns Stream from web request.

  streamReader = GetStream();

  streamReader.ReadLine();

The above line of code reads line from stream.

Add line from stream into array for easy manipulation.

String []row = streamReader.ReadLine().ToString().Split(',');

Now check whether data already exists in the database.

SqlCommand selectCommand;
selectCommand = new SqlCommand
     ("SELECT * FROM Location WHERE Latitude=@Latitude AND Longitude=@Longitude", conn);

     selectCommand.Parameters.Add("@Latitude",
     SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
     selectCommand.Parameters.Add("@Longitude",
     SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);

     SqlDataReader reader = selectCommand.ExecuteReader();

     if (!reader.HasRows)
     {
     }

If not, then insert data into database.

reader.Close();

SqlCommand insertCommand;
insertCommand = new SqlCommand("INSERT INTO Location
                (Latitude,Longitude,BuildingName) VALUES
                (@Latitude,@Longitude,@BuildingName)", conn);

insertCommand.Parameters.Add("@Latitude",
SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
insertCommand.Parameters.Add("@Longitude",
SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);
insertCommand.Parameters.Add("@BuildingName",
SqlDbType.VarChar, 100).Value = row[2].ToString();

int intResult;
intResult = insertCommand.ExecuteNonQuery();

Here is the result from the database:

Conclusion

By reading this article, you can create your online file reading application. I think this may help you in software development and professional work.

History

• 28^th March, 2011: Initial post

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)