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:
Figure 1
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:
Figure 2
Open Microsoft Visual Studio 2010
- Create a New Project (File>New>Project>Visual C#>Windows Form Application)
- Name it as ReadingOnlineExcelFile
Figure 3
By default, the new project includes a form called ReadExcel.cs. Design the form like below:
Figure 4
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:
Figure 5
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
.
Figure 6
Add line from stream
into array for easy manipulation.
String []row = streamReader.ReadLine().ToString().Split(',');
Figure 7
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:
Figure 8
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
- 28th March, 2011: Initial post