public DataTable GetDataTable(string sql)
{
DataSet ds = new DataSet();
OpenConn();
OracleDataAdapter da = new OracleDataAdapter(sql, Connection);
try
{
da.Fill(ds, "tb");
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
SClog.insert("Error", "DB ExecuteSQL Error --> " + sql + " " + ex.ToString());
}
finally
{
CloseConn();
da.Dispose();
}
DataTable result = ds.Tables["tb"];
return(result);
}
/// <summary>
/// 删除文件 高俊涛添加2014-10-26
/// </summary>
/// <param name="fileName"></param>
public void Delete(string package, string fileName)
{
//FTP服务器IP地址
string ftpServerIP = System.Configuration.ConfigurationSettings.AppSettings["ftpIP"].ToString();
//FTP登录用户名
string ftpUserID = System.Configuration.ConfigurationSettings.AppSettings["ftpuser"].ToString();
//FTP登录密码
string ftpPassword = System.Configuration.ConfigurationSettings.AppSettings["ftppasswd"].ToString();
try
{
FtpWebRequest reqFTP;
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + package + "/" + fileName));
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
reqFTP.KeepAlive = false;
reqFTP.Method = WebRequestMethods.Ftp.DeleteFile;
string result = String.Empty;
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
long size = response.ContentLength;
Stream datastream = response.GetResponseStream();
StreamReader sr = new StreamReader(datastream);
result = sr.ReadToEnd();
sr.Close();
datastream.Close();
response.Close();
}
catch (Exception ex)
{
SClog.insert("Error", "FtpWEB Delete Error --> " + ex.Message + " 文件名:" + fileName);
}
}
/// <summary>
/// 执行SQL语句，返回Bool值
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>返回BOOL值，True为执行成功</returns>
public bool ExecuteSQL(OracleCommand cmd)
{
OpenConn();
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (System.Exception ex)
{
SClog.insert("Error", "DB ExecuteSQL Error --> " + cmd + " " + ex.ToString());
return(false);
}
finally
{
CloseConn();
}
}
/// <summary>
/// 执行SQL语句，返回Bool值
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>返回BOOL值，True为执行成功</returns>
public bool ExecuteSQL(string sql)
{
OpenConn();
OracleCommand command = new OracleCommand(sql, Connection);
try
{
command.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
SClog.insert("Error", "DB ExecuteSQL Error --> " + sql + " " + ex.ToString());
return(false);
}
finally
{
CloseConn();
}
}
/// <summary>
/// 执行指定Sql语句,同时给传入DataTable进行赋值
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="dt">ref DataTable dt </param>
public void dataTable(string sql, ref DataTable dt)
{
OracleDataAdapter da = new OracleDataAdapter(sql, Connection);
try
{
OpenConn();
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
SClog.insert("Error", "DB DataTable Error --> " + e.Message);
}
finally
{
CloseConn();
da.Dispose();
}
}
/// <summary>
/// 执行SQL语句并返回受影响的行数
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>返回Int类型的受影响的行数</returns>
public int GetCount(string sql)
{
OpenConn();
OracleCommand command = new OracleCommand(sql, Connection);
try
{
int count = Convert.ToInt32(command.ExecuteScalar());
return(count);
}
catch (Exception ex)
{
SClog.insert("Error", "DB ExecuteSQL Error --> " + sql + " " + ex.ToString());
return(0);
}
finally
{
CloseConn();
command.Dispose();
}
}
public DataTable dataTable(string sql)
{
OracleDataAdapter da = new OracleDataAdapter(sql, Connection);
DataTable datatable = new DataTable();
try
{
OpenConn();
da.Fill(datatable);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
SClog.insert("Error", "DB DataTable Error --> " + ex.ToString());
}
finally
{
CloseConn();
da.Dispose();
}
return(datatable);
}
/// <summary>
/// 执行SQL语句,返回所影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteSql(string sql)
{
int Cmd = 0;
OpenConn();
OracleCommand command = new OracleCommand(sql, Connection);
try
{
Cmd = command.ExecuteNonQuery();
}
catch (Exception ex)
{
SClog.insert("Error", "DB ExecuteSQL Error --> " + sql + " " + ex.ToString());
}
finally
{
CloseConn();
}
return(Cmd);
}
private DataSet GetDataSet()//根据对象构造函数参数获取DataSet
{
OleDbConnection conn = GetConn();
OleDbDataAdapter oa = null;
DataTable dt = null;
DataSet ds = new DataSet();
string sql_F = "select * from [{0}]";
try
{
conn.Open();
string SheetName = "";//工作表的名字
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
oa = new OleDbDataAdapter();
for (int i = 0; i < dt.Rows.Count; i++)//循环每一个工作表
{
SheetName = (string)dt.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
oa.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
oa.Fill(dsItem, SheetName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
conn.Close();
oa.Dispose();
conn.Dispose();
}
catch (Exception e)
{
SClog.insert("Upfile ", e.ToString());
}
return(ds);
}
/// <summary>
/// 执行SQL语句，返回DataSet
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="tablename">DataSet中要填充的表名</param>
/// <returns>返回dataSet类型的执行结果</returns>
public DataSet GetDataSet(string sql, string tablename)
{
DataSet ds = new DataSet();
OpenConn();
OracleDataAdapter da = new OracleDataAdapter(sql, Connection);
try
{
da.Fill(ds, tablename);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
SClog.insert("Error", "DB ExecuteSQL Error --> " + sql + " " + ex.ToString());
}
finally
{
CloseConn();
da.Dispose();
}
return(ds);
}
//用户登录
public int UserLogIn(string sql)
{
int flag = 0;
string username = "";
string dm = "";
//DataSet ds = new DataSet();
DataTable dt = new DataTable();
OpenConn();
OracleDataAdapter da = new OracleDataAdapter(sql, Connection);
try
{
da.Fill(dt);
if (dt.Rows.Count != 0)
{
username = dt.Rows[0]["USERNAME"].ToString();
dm = dt.Rows[0]["DM"].ToString();
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
SClog.insert("Error", "DB UserLogIn Error --> " + ex.ToString());
}
finally
{
CloseConn();
da.Dispose();
}
if (dt.Rows.Count != 0)
{
flag = 1;
}
return(flag);
}
/// <summary>
/// FTP获取文件列表
/// </summary>
/// <param name="ftpServerIP"></param>
/// <param name="ftpUserID"></param>
/// <param name="ftpPassword"></param>
/// <returns></returns>
public string[] FTPGetFileList(string ftpServerIP, string ftpUserID, string ftpPassword)
{
//响应结果
System.Text.StringBuilder result = new StringBuilder();
//FTP请求
System.Net.FtpWebRequest ftpRequest = null;
//FTP响应
WebResponse ftpResponse = null;
//FTP响应流
StreamReader ftpResponsStream = null;
try
{
//生成FTP请求
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/"));
//设置文件传输类型
ftpRequest.UseBinary = true;
//FTP登录
ftpRequest.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
//设置FTP方法
ftpRequest.Method = WebRequestMethods.Ftp.ListDirectory;
//生成FTP响应
ftpResponse = ftpRequest.GetResponse();
//FTP响应流
ftpResponsStream = new StreamReader(ftpResponse.GetResponseStream());
string line = ftpResponsStream.ReadLine();
while (line != null)
{
result.Append(line);
result.Append("\n");
line = ftpResponsStream.ReadLine();
}
//去掉结果列表中最后一个换行
result.Remove(result.ToString().LastIndexOf('\n'), 1);
//返回结果
return(result.ToString().Split('\n'));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
SClog.insert("Error", "FtpWEB GetFileList Error --> " + ex.Message);
return(null);
}
finally
{
if (ftpResponsStream != null)
{
ftpResponsStream.Close();
}
if (ftpResponse != null)
{
ftpResponse.Close();
}
}
}
/// <summary>
/// FTP上传文件
/// </summary>
/// <param name="ftpServerIP">FTP服务器IP</param>
/// <param name="ftpUserID">FTP登录帐号</param>
/// <param name="ftpPassword">FTP登录密码</param>
/// <param name="filename">上文件文件名（绝对路径）</param>
public void FTPUploadFile(string ftpServerIP, string ftpUserID, string ftpPassword, string filename)
{
//上传文件
FileInfo uploadFile = null;
//上传文件流
FileStream uploadFileStream = null;
//FTP请求对象
FtpWebRequest ftpRequest = null;
//FTP流
Stream ftpStream = null;
try
{
//获取上传文件
uploadFile = new FileInfo(filename);
//创建FtpWebRequest对象
//ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/1234.txt" ));
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + uploadFile.Name));
//ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + downloadFileName));
//FTP登录
ftpRequest.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
// 默认为true，连接不会被关闭
// 在一个命令之后被执行
ftpRequest.KeepAlive = false;
//FTP请求执行方法
ftpRequest.Method = WebRequestMethods.Ftp.UploadFile;
// 指定数据传输类型
ftpRequest.UseBinary = true;
// 上传文件时通知服务器文件的大小
ftpRequest.ContentLength = uploadFile.Length;
// 缓冲大小设置为2kb
int buffLength = 2048;
byte[] buff = new byte[buffLength];
int contentLen;
// 打开一个文件流读上传的文件
uploadFileStream = uploadFile.OpenRead();
// 把上传的文件写入流
ftpStream = ftpRequest.GetRequestStream();
// 每次读文件流的2kb
contentLen = uploadFileStream.Read(buff, 0, buffLength);
// 流内容没有结束
while (contentLen != 0)
{
// 把内容从file stream 写入 upload stream
ftpStream.Write(buff, 0, contentLen);
contentLen = uploadFileStream.Read(buff, 0, buffLength);
}
Console.WriteLine("上传成功！");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
SClog.insert("Error", "FtpWEB UpLoad Error --> " + ex.Message + " 文件名:" + filename);
}
finally
{
if (uploadFileStream != null)
{
uploadFileStream.Close();
}
if (ftpStream != null)
{
ftpStream.Close();
}
}
}
/// <summary>
/// FTP下载文件
/// </summary>
/// <param name="ftpServerIP">FTP服务器IP</param>
/// <param name="ftpUserID">FTP登录帐号</param>
/// <param name="ftpPassword">FTP登录密码</param>
/// <param name="saveFilePath">保存文件路径</param>
/// <param name="saveFileName">保存文件名</param>
/// <param name="downloadFileName">下载文件名</param>
public void FTPDownloadFile(string ftpServerIP, string ftpUserID, string ftpPassword,
string saveFilePath, string saveFileName, string downloadFileName)
{
//定义FTP请求对象
FtpWebRequest ftpRequest = null;
//定义FTP响应对象
FtpWebResponse ftpResponse = null;
//存储流
FileStream saveStream = null;
//FTP数据流
Stream ftpStream = null;
try
{
//生成下载文件
saveStream = new FileStream(saveFilePath + "\\" + saveFileName, FileMode.Create);
//生成FTP请求对象
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + downloadFileName));
//设置下载文件方法
ftpRequest.Method = WebRequestMethods.Ftp.DownloadFile;
//设置文件传输类型
ftpRequest.UseBinary = true;
//设置登录FTP帐号和密码
ftpRequest.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
//生成FTP响应对象
ftpResponse = (FtpWebResponse)ftpRequest.GetResponse();
//获取FTP响应流对象
ftpStream = ftpResponse.GetResponseStream();
//响应数据长度
long cl = ftpResponse.ContentLength;
int bufferSize = 2048;
int readCount;
byte[] buffer = new byte[bufferSize];
//接收FTP文件流
readCount = ftpStream.Read(buffer, 0, bufferSize);
while (readCount > 0)
{
saveStream.Write(buffer, 0, readCount);
readCount = ftpStream.Read(buffer, 0, bufferSize);
}
Console.WriteLine("下载成功！");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
SClog.insert("Error", "FtpWEB DownLoad Error --> " + ex.Message + " 下载路径：" + saveFilePath + " 保存文件名:" + saveFileName + " 下载文件名：" + downloadFileName);
}
finally
{
if (ftpStream != null)
{
ftpStream.Close();
}
if (saveStream != null)
{
saveStream.Close();
}
if (ftpResponse != null)
{
ftpResponse.Close();
}
}
}
/// <summary>
/// 将输入流作为源头实现FTP上传
/// </summary>
/// <param name="package">FTP文件夹</param>
/// <param name="fs">文件输入流</param>
/// <param name="length">流长度（字节）</param>
/// <param name="suffix">上传文件后缀</param>
/// <returns>返回上传到ftp服务器的文件名</returns>
public string FTPUploadFile(string package, Stream fs, int length, string suffix)
{
//FTP服务器IP地址
string ftpServerIP = System.Configuration.ConfigurationSettings.AppSettings["ftpIP"].ToString();
//FTP登录用户名
string ftpUserID = System.Configuration.ConfigurationSettings.AppSettings["ftpuser"].ToString();
//FTP登录密码
string ftpPassword = System.Configuration.ConfigurationSettings.AppSettings["ftppasswd"].ToString();
//FTP请求对象
FtpWebRequest ftpRequest = null;
//FTP流
Stream ftpStream = null;
string fileName = "";
try
{
fileName = GenerateFileName() + suffix;
//创建FtpWebRequest对象
ftpRequest = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + package + "/" + fileName));
//FTP登录
ftpRequest.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
// 默认为true，连接不会被关闭
// 在一个命令之后被执行
ftpRequest.KeepAlive = false;
//FTP请求执行方法
ftpRequest.Method = WebRequestMethods.Ftp.UploadFile;
// 指定数据传输类型
ftpRequest.UseBinary = true;
// 上传文件时通知服务器文件的大小
ftpRequest.ContentLength = length;
// 缓冲大小设置为2kb
int buffLength = 2048 * 10;
byte[] buff = new byte[buffLength];
int contentLen;
// 把上传的文件写入流
ftpStream = ftpRequest.GetRequestStream();
// 每次读文件流的2kb
contentLen = fs.Read(buff, 0, buffLength);
// 流内容没有结束
while (contentLen != 0)
{
// 把内容从file stream 写入 upload stream
ftpStream.Write(buff, 0, contentLen);
contentLen = fs.Read(buff, 0, buffLength);
}
Console.WriteLine("上传成功！");
return(fileName);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
SClog.insert("Error", "FtpWEB UpLoad Error --> " + ex.Message + " 文件名:" + fileName);
return("");
}
finally
{
if (fs != null)
{
fs.Close();
}
if (ftpStream != null)
{
ftpStream.Close();
}
}
}
