using System.Web.SessionState;

/// <summary>
/// ClsOracleDB 的摘要說明? 使用中
/// </summary>
public class ClsOracleDB
{
??? //用OracleConnection連接Oracle
??? public System.Data.OracleClient.OracleConnection cnn;

??? public string Connstr
??? {
??????? get
??????? {
??????????? return System.Configuration.ConfigurationSettings.AppSettings["Oracle"];
??????? }
??? }

　　/// <summary>
　　/// 打開數據庫連接
　　/// </summary>
??? public void Open()
　　{
??????? cnn=new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["Oracle"]);
　　??? cnn.Open();
　　}

?

??? /// <summary>
　　/// 打開數據庫連接，返回cnn
　　/// </summary>
　　public OracleConnection OpenCnn()
　　{
????? cnn=new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["Oracle"]);
　　? cnn.Open();
　　? return(cnn);
　　}

??? /// <summary>
　　/// 關閉數據庫連接
　　/// </summary>
　　public void Close()
　　{
????? cnn.Close();
　　}

??? /// <summary>
　　/// 返回DataSet
　　/// </summary>
　　/// <param name="CmdString"></param>
　　/// <param name="TableName"></param>
　　/// <returns></returns>
　　public DataSet GetDataSet(string CmdString,string TableName)
　　{
????? Open();
　　? OracleDataAdapter myDa =new OracleDataAdapter();
　　? myDa.SelectCommand = new OracleCommand(CmdString,cnn);
　　? DataSet myDs =new DataSet();
　　? myDa.Fill(myDs,TableName);
　　? Close();
　　? return myDs;
　　}

??? /// <summary>
　　/// 返回OleDbDataReader
　　/// </summary>
　　/// <param name="CmdString"></param>
　　/// <returns></returns>
　　public OracleDataReader GetDataReader(string CmdString)
　　{
????? Open();
　　? OracleCommand myCmd =new OracleCommand(CmdString,cnn);
　　? OracleDataReader myDr =myCmd.ExecuteReader();
　? 　return myDr;
　　}

??? /// <summary>
??? /// 返回影響數據庫的行數? 更新數據庫數據
　　/// </summary>
　　/// <param name="CmdString"></param>
　　/// <returns></returns>
　　public int ExecuteSQL(string CmdString)
　　{
????? Open();
　　? OracleCommand myCmd =new OracleCommand(CmdString,cnn);
　　? int Cmd =myCmd.ExecuteNonQuery();
　? 　Close();
　? 　return Cmd;
　　}

??? /// <summary>
　　/// 邦定DroDownList
　　/// </summary>
　　/// <param name="DroDList"></param>控件名
　　/// <param name="sql"></param>連接字符串
　　/// <param name="TableName"></param>表名
　　/// <param name="DataTextFd"></param>提供文本內容的數據源字段
　　/// <param name="DataValueFd"></param>為列表項提供值的數據源字段
??? public void DroList(DropDownList DroDList, string sql, string TableName, string DataTextFd, string DataValueFd)
??? {
??????? Open();
??????? OracleDataAdapter myDa = new OracleDataAdapter(sql, cnn);
??????? myDa.SelectCommand.CommandType = CommandType.Text;
??????? DataSet myDs = new DataSet();
??????? try
??????? {
??????????? myDa.Fill(myDs, TableName);
??????????? DroDList.DataSource = myDs.Tables[TableName];
??????????? DroDList.DataTextField = DataTextFd;
??????????? DroDList.DataValueField = DataValueFd;
??????????? DroDList.DataBind();
??????? }
??????? catch (System.Exception e)
??????? {
??????????? //Response.Write(e.Message);
??????????? LeeGunn.Web.UI.MessageBox.MessageBox.Show(e.Message);
??????? }
??????? finally
??????? {
??????????? Close();
??????? }
??? }

??? //可以用了
??? public void SavePic()
??? {
??????? string WarnGradeName;
??????? int i;
??????? string TmpdirPath;
??????? TmpdirPath = "\\" + "file" + "\\" + "warnicon";
???????
??????? // WarnGradeName = HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath);

??????? string sqlstr;

??????? DataSet myallds = new DataSet();
??????? string tablename;
??????? tablename = "WARNMETEDIS";

??????? sqlstr = "SELECT ID,WARNNAME FROM WARNMETEDIS ORDER BY WARNNAME";
??????? i = 1;

??????? myallds = GetDataSet(sqlstr, tablename);

??????? if (myallds.Tables[0].Rows.Count >= 1)
??????? {
??????????? for (i = 0; i < myallds.Tables[0].Rows.Count; i ++ )
??????????? {
??????????????? WarnGradeName = myallds.Tables[0].Rows[i][1].ToString();
??????????????? string[] photos = System.IO.Directory.GetFiles(HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath) + TmpdirPath, WarnGradeName + ".jpg");

??????????????? if (photos[0].Trim() != "")
??????????????? {
??????????????????? StringBuilder sbSQL = new StringBuilder("UPDATE WARNMETEDIS SET WARNICON = :WARNICON11 WHERE WARNNAME='" + WarnGradeName + "'");

??????????????????? OracleConnection cn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["Oracle"]);
??????????????????? OracleCommand cmd = cn.CreateCommand();
??????????????????? cmd.CommandText = sbSQL.ToString();

??????????????????? FileStream fs;
??????????????????? fs =File.OpenRead(photos[0]);
??????????????????? int lentth;
??????????????????? byte[] pic = new byte[fs.Length];
??????????????????? lentth = Convert.ToInt32(fs.Length);
??????????????????? fs.Read(pic, 0, lentth);
??????????????????? fs.Close();

??????????????????? cmd.Parameters.Add(":WARNICON11", OracleType.Blob).Value = pic;
??????????????????? try
??????????????????? {
??????????????????????? cn.Open();
??????????????????????? cmd.ExecuteNonQuery();
??????????????????? }
??????????????????? catch (Exception ex)
??????????????????? {
??????????????????????? //Response.Write(ex.Message);
??????????????????????? LeeGunn.Web.UI.MessageBox.MessageBox.Show(ex.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cn.Close();
??????????????????? }
??????????????? }
??????????????? else
??????????????? {
??????????????????? LeeGunn.Web.UI.MessageBox.MessageBox.Show("沒有數據");
??????????????? }
??????????? }
??????? }
??? }

??? //public

?

??? #region 參考存取圖片代碼

??? #region 創建的數據表
??? // CREATE TABLE TEST_TABLE
??? //(
??? //?? ID???? VARCHAR2(36 BYTE),
??? //?? NAME?? VARCHAR2(50 BYTE),
??? //?? PHOTO? BLOB
??? //)
??? #endregion

??? //保存圖片到數據庫
??? private void CankaoSavePic()
??? {
??????? //StringBuilder sbSQL = new StringBuilder("insert into Test_Table(ID,Name,Photo) values(:ID,:Name,:Photo)");
??????? //OracleConnection cn = new OracleConnection(strCn);
??????? //OracleCommand cmd = cn.CreateCommand();
??????? //cmd.CommandText = sbSQL.ToString();
??????? //cmd.Parameters.Add(":ID", OracleType.VarChar, 36).Value = Guid.NewGuid().ToString();
??????? //cmd.Parameters.Add(":Name", OracleType.VarChar, 50).Value = fileUp.FileName; ;
??????? //int intLen = fileUp.PostedFile.ContentLength;
??????? //byte[] pic = new byte[intLen];
??????? //fileUp.PostedFile.InputStream.Read(pic, 0, intLen);
??????? //cmd.Parameters.Add(":Photo", OracleType.Blob).Value = pic;
??????? //try
??????? //{
??????? //??? cn.Open();
??????? //??? cmd.ExecuteNonQuery();
??????? //}
??????? //catch (Exception ex)
??????? //{
??????? //??? Response.Write(ex.Message);
??????? //}
??????? //finally
??????? //{
??????? //??? cn.Close();
??????? //}
??? }

??? //從數據庫中讀取圖片
??? private void CankaoReadPic()
??? {
?????? // OracleConnection cn = new OracleConnection(strCn);
?????? // OracleCommand cmd = cn.CreateCommand();
?????? // cmd.CommandText = "select photo from test_table";
?????? // try
?????? // {
?????? //???? cn.Open();
?????? //???? MemoryStream stream = new MemoryStream();
?????? //???? IDataReader reader = cmd.ExecuteReader();
?????? //???? if (reader.Read())
?????? //???? {

?????? //???????? byte[] pic = (byte[])reader[0];
?????? //???????? //byte[] pic = (byte[])cmd.ExecuteScalar();
?????? //???????? stream.Write(pic, 0, pic.Length);
?????? //???????? //Bitmap bitMap = new Bitmap(stream);
?????? //???????? //Response.ContentType = "image/Jpeg";
?????? //???????? //bitMap.Save(Response.OutputStream, ImageFormat.Jpeg);
?????? //???????? //注釋部分可以將圖片顯示在IE中，而不是下載圖片，
?????? //???????? //下面的方法直接下載文件?
?????? //???????? Response.ContentType = "application/octet-stream";
?????? //???????? Response.AddHeader("Content-Disposition", "attachment;FileName= demo.JPG");
?????? //???????? Response.BinaryWrite(pic);
?????? //???????? Response.End();
?????? //???? }
?????? // }
?????? // catch (Exception ex)
?????? //{
?????? //???? Response.Write(ex.Message);
?????? //}
?????? //finally
?????? //{
?????? //???? cn.Close();
?????? //}
?? }
??? #endregion