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(); } } }