从FTP下载文件后把数据放在数据库里.
相关需求是:
1.从FTP上下载文件(此FTP不知因为什么原因.下载东东时二次连接才会连上一次.FTP软件是,在.NET程序下一样如此).
2.下载文件时先要看这个文件的控制文件在不在,在才下载,不在不下载.
3.解析文件每行数据.客户会给出这个字段在这行数据中的位置.如Name(0,8),Status(8,5),T1(13,7),T2(20,30),T3(50,50).......
4.因相关要求.可以要加上一些数据库字段,字段名固定.Updatename表示文件批次.可能有的文件需要一个Ldata字段表示插入时间.
5.把数据解析后放在相应的数据表.
6.删除原文件和相关控件文件.
7.Mail报告当前情况
很简单的一个需求.开始只有二份文件,我想那好,我就用SSIS好了.也做好了.
但是后有六份文件,可能不至,吐血的是,都是上面的要求.只是在第三步会不同,字段名不同,位置不同........用SSIS我就要做六份差不多的.
然后一个有问题.可能另个五个就有相同的问题.可能就要改六次.
算了.我还是用我比较擅长的C#写吧.用什么模式也要对六个文件来生成对应的情况.还是用LinqToSQL生成的类.再加上反射与泛形解决吧.(^_^说的有的大,就是些相关小应用).
首先分析三,如Name(0,8),Status(8,5),T1(13,7),T2(20,30),T3(50,50).......
数据行分割是有规则的.就是一个接一个的分割,那么(这就要求数据库字段要和文件顺序一样,并且长度要正确.).先看一下LinqToSql为我们生成的类吧.
呵呵,可以看到上面的字段里有关于这个字段的长度信息.下面看下我的实现代码.
DataOperate
public class DataOperate<T> where T : new()
{
private static readonly List<PropertyInfo> ps = null;
private static readonly List<Info> infos = null;
private static readonly PropertyInfo time_Info = null;
private static readonly DateTime time = DateTime.Now;
private const int startIndex = 9;
static DataOperate()
{
if (ps == null)
{
infos = new List<Info>();
ps = typeof(T).GetProperties().ToList();
foreach (PropertyInfo item in ps)
{
if (item.ToString().Contains("String"))
{
Info info = new Info();
ColumnAttribute a = Attribute.GetCustomAttribute(item, typeof(ColumnAttribute)) as ColumnAttribute;
string length = a.DbType.Substring(startIndex, a.DbType.IndexOf(')') - startIndex);
int len = 0;
if (int.TryParse(length, out len))
{
info.Length = len;
info.ProInfo = item;
infos.Add(info);
}
}
else if(item.ToString().Contains("DateTime"))
{
time_Info = item;
}
}
}
}
public DataOperate()
{
}
//把一行数据转换成相应的对象
public bool SetData(string line, T t)
{
int start = 0;
try
{
//遍历所有正确的属性
foreach (Info info in infos)
{
if (info.ProInfo.Name != "Updatename")
{
string value = line.Substring(start, info.Length).Trim();
start += info.Length;
info.ProInfo.SetValue(t, value, null);
}
else
{
info.ProInfo.SetValue(t, FtpFileDown.SaveName, null);
}
}
if (time_Info != null)
{
time_Info.SetValue(t, time, null);
}
return true;
}
catch
{
return false;
}
}
public void GetData(string Path, List<T> ts)
{
StreamReader reader = new StreamReader(Path);
GetData(reader, ts);
}
/// <summary>
/// 把字节流转换成对应的实体
/// </summary>
/// <param name="reader"></param>
/// <param name="ts"></param>
public void GetData(StreamReader reader, List<T> ts)
{
string line = reader.ReadLine();
try
{
while ((line = reader.ReadLine()) != null)
{
T t = new T();//t = System.Activator.CreateInstance<T>();
if (this.SetData(line, t))
{
ts.Add(t);
}
else
{
//记录当前行有错误发生.
string message = string.Format("File:{0}-{1},Line:{2},Warning:{3}.<br>",typeof(T).Name,FtpFileDown.SaveName,ts.Count+2,"数据没有导入进来.可能是这行资料不全!");
message +="资料行信息:"+ line + "<br>";
FtpFileDown.Message_Body += message;
}
}
}
//最后文件读完后可能发生一个意思为不能读取已经完成的数据流(直接读网络数据时发生,读文件正确操作没有问题),属于正常情况.
catch (Exception e)
{
string message = string.Format("File:{0}-{1}发生如下异常,请确认是否正常!<br>{2}.<br>",typeof(T).Name, FtpFileDown.SaveName,e.Message);
FtpFileDown.Message_Body += message;
reader.Close();
}
//reader.Close();
}
}
public class Info
{
public PropertyInfo ProInfo { get; set; }
public int Length { get; set; }
}
{
private static readonly List<PropertyInfo> ps = null;
private static readonly List<Info> infos = null;
private static readonly PropertyInfo time_Info = null;
private static readonly DateTime time = DateTime.Now;
private const int startIndex = 9;
static DataOperate()
{
if (ps == null)
{
infos = new List<Info>();
ps = typeof(T).GetProperties().ToList();
foreach (PropertyInfo item in ps)
{
if (item.ToString().Contains("String"))
{
Info info = new Info();
ColumnAttribute a = Attribute.GetCustomAttribute(item, typeof(ColumnAttribute)) as ColumnAttribute;
string length = a.DbType.Substring(startIndex, a.DbType.IndexOf(')') - startIndex);
int len = 0;
if (int.TryParse(length, out len))
{
info.Length = len;
info.ProInfo = item;
infos.Add(info);
}
}
else if(item.ToString().Contains("DateTime"))
{
time_Info = item;
}
}
}
}
public DataOperate()
{
}
//把一行数据转换成相应的对象
public bool SetData(string line, T t)
{
int start = 0;
try
{
//遍历所有正确的属性
foreach (Info info in infos)
{
if (info.ProInfo.Name != "Updatename")
{
string value = line.Substring(start, info.Length).Trim();
start += info.Length;
info.ProInfo.SetValue(t, value, null);
}
else
{
info.ProInfo.SetValue(t, FtpFileDown.SaveName, null);
}
}
if (time_Info != null)
{
time_Info.SetValue(t, time, null);
}
return true;
}
catch
{
return false;
}
}
public void GetData(string Path, List<T> ts)
{
StreamReader reader = new StreamReader(Path);
GetData(reader, ts);
}
/// <summary>
/// 把字节流转换成对应的实体
/// </summary>
/// <param name="reader"></param>
/// <param name="ts"></param>
public void GetData(StreamReader reader, List<T> ts)
{
string line = reader.ReadLine();
try
{
while ((line = reader.ReadLine()) != null)
{
T t = new T();//t = System.Activator.CreateInstance<T>();
if (this.SetData(line, t))
{
ts.Add(t);
}
else
{
//记录当前行有错误发生.
string message = string.Format("File:{0}-{1},Line:{2},Warning:{3}.<br>",typeof(T).Name,FtpFileDown.SaveName,ts.Count+2,"数据没有导入进来.可能是这行资料不全!");
message +="资料行信息:"+ line + "<br>";
FtpFileDown.Message_Body += message;
}
}
}
//最后文件读完后可能发生一个意思为不能读取已经完成的数据流(直接读网络数据时发生,读文件正确操作没有问题),属于正常情况.
catch (Exception e)
{
string message = string.Format("File:{0}-{1}发生如下异常,请确认是否正常!<br>{2}.<br>",typeof(T).Name, FtpFileDown.SaveName,e.Message);
FtpFileDown.Message_Body += message;
reader.Close();
}
//reader.Close();
}
}
public class Info
{
public PropertyInfo ProInfo { get; set; }
public int Length { get; set; }
}
下面就是关于相关FTP功能的说明 .
代码
public class FtpFile
{
public FtpFile()
{
ControlFileExit = false;
}
public string Name { get; set; }
public string Path { get; set; }
public string SavePath { get; set; }
public string FullName
{
get
{
if (!string.IsNullOrEmpty(Name) && !string.IsNullOrEmpty(Path))
{
return Path + @"/" + Name;
}
return string.Empty;
}
}
public string ControlFileName { get; set; }
public bool ControlFileExit { get; set; }
public List<T> GetFile<T>() where T : new()
{
if (ControlFileExit)
{
DataOperate<T> t = new DataOperate<T>();
List<T> ts = new List<T>();
if (string.IsNullOrEmpty(FullName))
{
t.GetData(FullName, ts);
}
return ts;
}
return null;
}
public List<T> GetFile<T>(StreamReader data) where T : new()
{
if (ControlFileExit)
{
DataOperate<T> t = new DataOperate<T>();
List<T> ts = new List<T>();
if (data != null)
{
t.GetData(data, ts);
}
return ts;
}
return null;
}
}
public class FtpClass : IDisposable
{
public string Server { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string WorkDir { get; set; }
// private FtpWebRequest client = null;
public const int CountConnecting = 5;
public void Dispose()
{
}
//私有化无参构造函数.不让外部调用
private FtpClass()
{
}
public FtpClass(string server, string userName, string password, string workDir)
{
this.Server = server;
this.UserName = userName;
this.Password = password;
this.WorkDir = workDir;
}
//得到一个FTP客户端
public FtpWebRequest Create()
{
string url = "ftp://" + UserName + ":" + Password + "@" + Server + "/" + WorkDir;
Uri uri = new Uri(url);
FtpWebRequest client = (FtpWebRequest)FtpWebRequest.Create(uri);
client.KeepAlive = true;
client.Proxy = GlobalProxySelection.GetEmptyWebProxy(); //FtpWebRequest.GetSystemWebProxy();
return client;
}
//得到FTP服务器应答的字节流
public Stream GetStream(string method)
{
if (string.IsNullOrEmpty(method))
return null;
Stream data = null;
for (int i = 0; i < CountConnecting; i++)
{
//如果联接上了,直接跳出当前循环.
try
{
FtpWebRequest client = this.Create();
client.Method = method;
client.Timeout = 8000;
WebResponse server = client.GetResponse();
data = server.GetResponseStream();
break;
}
catch (Exception e)
{
//因为相关FTP外部原因,联接可能联不上.这个属于正常情况,可以尝试继续联接
if (e.Message.Contains("530"))
continue;
else
{
//文件不存在.另有处理.
if (!e.Message.Contains("550"))
{
string message = string.Format("发生如下异常,请确认是否正常!<br>{0}.<br>", e.Message);
FtpFileDown.Message_Body += message;
}
continue;
}
}
}
return data;
}
//得到文件夹下文件列表
public List<string> GetFileList()
{
//this.WorkDir = "EDI855";
Stream data = this.GetStream(WebRequestMethods.Ftp.ListDirectory);
if (data == null)
return null;
StreamReader reader = new StreamReader(data);
List<string> fileList = new List<string>();
while (true)
{
string file = reader.ReadLine();
if (string.IsNullOrEmpty(file))
break;
fileList.Add(file);
}
data.Close();
return fileList;
}
//把文件信息转化成对应的数据对像
public List<T> DownData<T>(FtpFile file)where T:new()
{
//当前FTP工作目录为当前文件的文件夹(用于查看文件列表)
this.WorkDir = file.Path;
List<T> files = null;
try
{
//得到当前文件的所在路径下的所有文件.
List<string> filelist = GetFileList();
//查看当前文件有没此文件的控件文件
if (filelist != null && filelist.Where(p => p.Contains(file.ControlFileName)).Count() > 0)
{
//当前FTP工作目录变成当前文件(用于来下载和删除文件)
this.WorkDir = file.FullName;
//得到当前文件的字节流.
Stream filedata = GetStream(WebRequestMethods.Ftp.DownloadFile);
if (filedata != null)
{
//控件文件存在.
file.ControlFileExit = true;
string savePath = file.SavePath + @"\" + FtpFileDown.SaveName;
//进行字节流到实体对象的转换
if (!Directory.Exists(savePath))
{
//复制文件到所需目录
FileStream fs = File.Create(savePath);
StreamReader read = new StreamReader(filedata);
StreamWriter sw = new StreamWriter(fs);
sw.Write(read.ReadToEnd());
read.Close();
sw.Close();
fs.Close();
//当前文件已经复制到相应的目录.
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "数据已经从FTP上下载到目地服务器,下面开始导入数据库!");
FtpFileDown.Message_Body += message;
//把当前文件的数据转换成数据对像
StreamReader reader = new StreamReader(savePath);
files = file.GetFile<T>(reader);
reader.Close();
}
filedata.Close();
//删除在FTP上的当前文件.
//GetStream(WebRequestMethods.Ftp.DeleteFile);
}
else
{
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "FTP上没有这个文件.");
FtpFileDown.Message_Body += message;
}
}
else
{
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "FTP上没有这个文件的控制文件.");
FtpFileDown.Message_Body += message;
}
}
catch (Exception e)
{
string message = string.Format("File:{0}发生如下异常,请确认是否正常!<br>{1}.<br>",file.Name, e.Message);
FtpFileDown.Message_Body += message;
}
return files;
}
}
{
public FtpFile()
{
ControlFileExit = false;
}
public string Name { get; set; }
public string Path { get; set; }
public string SavePath { get; set; }
public string FullName
{
get
{
if (!string.IsNullOrEmpty(Name) && !string.IsNullOrEmpty(Path))
{
return Path + @"/" + Name;
}
return string.Empty;
}
}
public string ControlFileName { get; set; }
public bool ControlFileExit { get; set; }
public List<T> GetFile<T>() where T : new()
{
if (ControlFileExit)
{
DataOperate<T> t = new DataOperate<T>();
List<T> ts = new List<T>();
if (string.IsNullOrEmpty(FullName))
{
t.GetData(FullName, ts);
}
return ts;
}
return null;
}
public List<T> GetFile<T>(StreamReader data) where T : new()
{
if (ControlFileExit)
{
DataOperate<T> t = new DataOperate<T>();
List<T> ts = new List<T>();
if (data != null)
{
t.GetData(data, ts);
}
return ts;
}
return null;
}
}
public class FtpClass : IDisposable
{
public string Server { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string WorkDir { get; set; }
// private FtpWebRequest client = null;
public const int CountConnecting = 5;
public void Dispose()
{
}
//私有化无参构造函数.不让外部调用
private FtpClass()
{
}
public FtpClass(string server, string userName, string password, string workDir)
{
this.Server = server;
this.UserName = userName;
this.Password = password;
this.WorkDir = workDir;
}
//得到一个FTP客户端
public FtpWebRequest Create()
{
string url = "ftp://" + UserName + ":" + Password + "@" + Server + "/" + WorkDir;
Uri uri = new Uri(url);
FtpWebRequest client = (FtpWebRequest)FtpWebRequest.Create(uri);
client.KeepAlive = true;
client.Proxy = GlobalProxySelection.GetEmptyWebProxy(); //FtpWebRequest.GetSystemWebProxy();
return client;
}
//得到FTP服务器应答的字节流
public Stream GetStream(string method)
{
if (string.IsNullOrEmpty(method))
return null;
Stream data = null;
for (int i = 0; i < CountConnecting; i++)
{
//如果联接上了,直接跳出当前循环.
try
{
FtpWebRequest client = this.Create();
client.Method = method;
client.Timeout = 8000;
WebResponse server = client.GetResponse();
data = server.GetResponseStream();
break;
}
catch (Exception e)
{
//因为相关FTP外部原因,联接可能联不上.这个属于正常情况,可以尝试继续联接
if (e.Message.Contains("530"))
continue;
else
{
//文件不存在.另有处理.
if (!e.Message.Contains("550"))
{
string message = string.Format("发生如下异常,请确认是否正常!<br>{0}.<br>", e.Message);
FtpFileDown.Message_Body += message;
}
continue;
}
}
}
return data;
}
//得到文件夹下文件列表
public List<string> GetFileList()
{
//this.WorkDir = "EDI855";
Stream data = this.GetStream(WebRequestMethods.Ftp.ListDirectory);
if (data == null)
return null;
StreamReader reader = new StreamReader(data);
List<string> fileList = new List<string>();
while (true)
{
string file = reader.ReadLine();
if (string.IsNullOrEmpty(file))
break;
fileList.Add(file);
}
data.Close();
return fileList;
}
//把文件信息转化成对应的数据对像
public List<T> DownData<T>(FtpFile file)where T:new()
{
//当前FTP工作目录为当前文件的文件夹(用于查看文件列表)
this.WorkDir = file.Path;
List<T> files = null;
try
{
//得到当前文件的所在路径下的所有文件.
List<string> filelist = GetFileList();
//查看当前文件有没此文件的控件文件
if (filelist != null && filelist.Where(p => p.Contains(file.ControlFileName)).Count() > 0)
{
//当前FTP工作目录变成当前文件(用于来下载和删除文件)
this.WorkDir = file.FullName;
//得到当前文件的字节流.
Stream filedata = GetStream(WebRequestMethods.Ftp.DownloadFile);
if (filedata != null)
{
//控件文件存在.
file.ControlFileExit = true;
string savePath = file.SavePath + @"\" + FtpFileDown.SaveName;
//进行字节流到实体对象的转换
if (!Directory.Exists(savePath))
{
//复制文件到所需目录
FileStream fs = File.Create(savePath);
StreamReader read = new StreamReader(filedata);
StreamWriter sw = new StreamWriter(fs);
sw.Write(read.ReadToEnd());
read.Close();
sw.Close();
fs.Close();
//当前文件已经复制到相应的目录.
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "数据已经从FTP上下载到目地服务器,下面开始导入数据库!");
FtpFileDown.Message_Body += message;
//把当前文件的数据转换成数据对像
StreamReader reader = new StreamReader(savePath);
files = file.GetFile<T>(reader);
reader.Close();
}
filedata.Close();
//删除在FTP上的当前文件.
//GetStream(WebRequestMethods.Ftp.DeleteFile);
}
else
{
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "FTP上没有这个文件.");
FtpFileDown.Message_Body += message;
}
}
else
{
string message = string.Format("FileName:{0}.<br>Message:{1}.<br>", file.Name, "FTP上没有这个文件的控制文件.");
FtpFileDown.Message_Body += message;
}
}
catch (Exception e)
{
string message = string.Format("File:{0}发生如下异常,请确认是否正常!<br>{1}.<br>",file.Name, e.Message);
FtpFileDown.Message_Body += message;
}
return files;
}
}
最后要下载此类文件,只要如下代码.
代码
BomDataDataContext db = new BomDataDataContext();
FtpClass ftp = new FtpClass("----------", "-----------", "---------", null);
//关于A51Bom的处理
FtpFile file1 = new FtpFile { Name = "----------", Path = "test/----", ControlFileName = "----_ENDFILE", SavePath = @"-----------" };
//取得数据
List<A51BOM> datas1 = ftp.DownData<A51BOM>(file1);
//入数据库
if (datas1 != null)
{
db.A51BOM.InsertAllOnSubmit(datas1);
Insert_Message(file1.Name,datas1.Count);
}
FtpClass ftp = new FtpClass("----------", "-----------", "---------", null);
//关于A51Bom的处理
FtpFile file1 = new FtpFile { Name = "----------", Path = "test/----", ControlFileName = "----_ENDFILE", SavePath = @"-----------" };
//取得数据
List<A51BOM> datas1 = ftp.DownData<A51BOM>(file1);
//入数据库
if (datas1 != null)
{
db.A51BOM.InsertAllOnSubmit(datas1);
Insert_Message(file1.Name,datas1.Count);
}
总的来说,是取了个巧,其实一样有六个类,只是因为那六个类是用LinqToSql上直接拉表就行了.其中表要建好是关建.
不过总的来说,能很好解决相关问题.比如修改文件出错的异常,在SSIS上,我可能要修改六次(可能SSIS也能做到.不过我现在做不到).而用我上面程序,只要修改一个地方.那六份文件便都能应用到.
至于其中说用到了反射会不会有性能问题.就我调试而言.反射部分我是感觉不到任何执行时间长的.时间长的只有从FTP下载文件时才会有感觉.一份6000行的文件.把每行全用上面方法转换为对象时就我调试时而言我都感觉要不到1S,更不要说发布后.至于前面分析类的属性.我放在静态函数里了这样声明N个对象时都只有一份对应地址.保证多个对象不会重复构造这一部分.像单例模式那样.
