Below code is used to read the html files from ftp site and import all the data into Database.
Add the below Key codes into web.config file
Add the below Key codes into web.config file
<appSettings>
<add key="SourcePath" value="C:\RootFolder\SubFolder\"/>
<add key="GroupListFilename" value="HTMLFile.htm"/>
<add key="FtpUserName" value="xxx"/>
<add key="FtpPassWord" value="xxx"/>
<add key="conString" value="Data Source=.;Initial
Catalog=DBNAME; User ID=xxx; Password=xxxx"/>
</appSettings>
private void ImportHTMLFile()
{
try
{
String strConnection = ConfigurationSettings.AppSettings["conString"];
SqlConnection conn = new SqlConnection(strConnection);
string SourcePath = ConfigurationSettings.AppSettings["SourcePath"];
string path = @SourcePath + "\\" + ConfigurationSettings.AppSettings["Filename"];
path = path.Replace("\\\\", "\\");
Download(ConfigurationSettings.AppSettings["Filename"].ToString());
string line = string.Empty;
if (Path.GetExtension(path) == ".htm")
{
using (StreamReader reader
= new StreamReader(@path))
{
//while ((line = reader.ReadToEnd()) != null)
//{
line =
reader.ReadToEnd();
List<string> tableContents =
GetContents(line, table_pattern);
int tableIndex = 0;
List<string> list = new List<string>();
foreach (string tableContent in tableContents)
{
List<string> trContents =
GetContents(tableContent, tr_pattern);
int trIndex = 0;
foreach (string trContent in trContents)
{
List<string> tdContents =
GetContents(trContent, td_pattern);
string[] columns = new string[tdContents.Count];
foreach (string tdContent in tdContents)
{
string result = Regex.Replace(tdContent, @"<[^>]*>", string.Empty);
list.Add(result);
}
if (trIndex != 0)
{
if (list.Count > 0)
{
string Column1 = list[0].Trim();
string Column2=
list[1].Trim();
{
insertDetailstoSqlServer(Column1, Column2);
}
}
}
list.Clear();
trIndex++;
}
tableIndex++;
}
//}
}
}
}
catch (Exception ex)
{
}
}
private void Download(string file)
{
try
{
string uri = "ftp://" + "ftp.xxxx.com" + "/" + "RootFolder/SubFolder" + "/" + file;
Uri serverUri = new Uri(uri);
if (serverUri.Scheme != Uri.UriSchemeFtp)
{
return;
}
FtpWebRequest reqFTP;
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + "ftp.xxxx.com" +
"/" + "RootFolder/SubFolder" + "/" + file));
string ftpUserName = ConfigurationSettings.AppSettings["FtpUserName"];
string ftpPassword = ConfigurationSettings.AppSettings["FtpPassWord"];
reqFTP.Credentials = new NetworkCredential(ftpUserName,
ftpPassword);
reqFTP.KeepAlive = false;
reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
reqFTP.UseBinary = true;
reqFTP.Proxy = null;
reqFTP.UsePassive = false;
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream responseStream = response.GetResponseStream();
string SourcePath = ConfigurationSettings.AppSettings["SourcePath"];
FileStream writeStream = new FileStream(@SourcePath + "/" + file, FileMode.Create);
int Length = 2048;
Byte[] buffer = new Byte[Length];
int bytesRead = responseStream.Read(buffer, 0, Length);
while (bytesRead > 0)
{
writeStream.Write(buffer,
0, bytesRead);
bytesRead =
responseStream.Read(buffer, 0, Length);
}
writeStream.Close();
response.Close();
}
catch (WebException wEx)
{
}
catch (Exception ex)
{
}
}
public void insertDetailstoSqlServer (string Column1, string Column2)
{
String strConnection = ConfigurationSettings.AppSettings["conString"];
using (SqlConnection con = new SqlConnection(strConnection))
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_insertDetailstoSqlServer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Column1", Column1);
cmd.Parameters.AddWithValue("@Column2", Column2);
cmd.ExecuteNonQuery();
con.Close();
}
}
0 comments:
Post a Comment