#region 版权说明
//******************************************************
//  版权所有2005

//******************************************************
// 名称: DataBase
// 作者: wuhao     
// 版本: 1.0       
// 日期: 2005-02-28
// 描述: 数据库数据执行数据操作接口   
//  
//  
// 函数列表：
// 1. ExecuteReader()   返回数据结果集
// 2. ExecuteNonQuery()  不返回操作结果
// 3.  ExecuteDataSet()  返回DataSet结果集
// 4. ExecuteScalar()   返回单个值
// 5.  ExecuteGrams()   使用UpdateGrames
// 
// <作者>     <时间>       <版本>        <说明>
// wuhao      2005-02-28     1.0           重新编译
// wuhao      2005-03-01     1.0           重新编译
//*****************************************************/
#endregion
using Microsoft.Data.SqlXml;
using Microsoft.Win32;
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text;
namespace SPS.COMMON.DATABASE
{
 /// <summary>
 /// DataBase 的摘要说明。
 /// </summary>
 public class DataBase:Idatabase
 {
  public DataBase()
  {
   
  }
  
  public DataBase(string webConnectionName)
  {
   setDBConnectionString(webConnectionName);
  }

  public DataBase(string registerName,string connectionName)
  {
   setDBRegisterConnectionString(registerName,connectionName);
  }

  #region 私有字段定义
  protected string m_Connection;       //数据库连接串
  protected string m_Sql;         //执行的sql
  protected string m_Error;        //系统执行出错信息
  protected SqlConnection m_SqlConnection=null;   //定义数据库连接
  protected bool m_Log=false;
  #endregion
  #region 私有方法定义
  //---------------------------------------------------------------------- *\
  // 函数名称： saveError
  // 函数说明： 数据库发生错误时，记录在系统表中sys_sps_log
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  protected void saveError(string error)
  {
   if (m_Log)
   {
    DataBaseLog.SaveLog(error,this.m_Connection);
   }
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： setDBConnectionString
  // 函数说明： 获取数据库连接串(注册表、webconfig)
  // 参数定义： connectionName:连接串名称
  // 返回值：  
  // ---------------------------------------------------------------------- */

  protected void setDBConnectionString(string connectionName)
  {
   m_Connection=ConfigurationSettings.AppSettings[connectionName];
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： setDBRegisterConnectionString
  // 函数说明： 获取数据库连接串(注册表)
  // 参数定义： registerName:注册表访问路径  connectionName:连接串名称
  // 返回值：  
  // ---------------------------------------------------------------------- */

  protected void setDBRegisterConnectionString(string registerName,string connectionName)
  {
   RegistryKey rk = Registry.LocalMachine.OpenSubKey(registerName,false);
   m_Connection = (string)rk.GetValue(connectionName);
  }
  #endregion

  #region 属性定义
  //数据库连接信息
  public string DBConnectionString
  {
   get{return m_Connection;}
   set{m_Connection=value;}
  }
  //执行Sql
  public string DBSql
  {
   get{return m_Sql;}
   set{m_Sql=value;}
  }
  //出错信息
  public string DBExecuteHint
  {
   get{return m_Error;}
   set{m_Error=value;}
  }
  //记录出错日志
  public bool DBErrorLog
  {
   get{return m_Log;}
   set{m_Log=value;}
  }
  #endregion
  #region 公有方法定义
  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteReader
  // 函数说明：
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual SqlDataReader ExecuteReader()
  {//获取查询结果
   SqlDataReader oDataReader=null;   //定义reader对象
   SqlCommand   oSqlCommand=null;
   try
   {
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    oDataReader=oSqlCommand.ExecuteReader();
    return oDataReader;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    
   }
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteScalar
  // 函数说明：
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual string  ExecuteScalar(string transName)
  {//获取单个值结果
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   try
   {
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          
    oTrans=m_SqlConnection.BeginTransaction(transName);
    oSqlCommand=new SqlCommand(this.m_Sql,m_SqlConnection);
    oSqlCommand.Transaction=oTrans;
    string sValue=oSqlCommand.ExecuteScalar().ToString() ;
    oTrans.Commit();
    return sValue;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    oTrans.Rollback(transName);
    saveError(this.m_Error+"**"+this.m_Sql);
    return "";
   }
   finally
   {
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteScalar
  // 函数说明：
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual string  ExecuteScalar()
  {//获取单个值结果
   SqlCommand   oSqlCommand=null;
   try
   {
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          
    oSqlCommand=new SqlCommand(this.m_Sql,m_SqlConnection);
    string sValue=oSqlCommand.ExecuteScalar().ToString() ;
    return sValue;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return "";
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteNonQuery
  // 函数说明： 主要用于 insert,update,delete
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual bool ExecuteNonQuery(string transName)
  {
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   try
   {
    int iRows=0;
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接
    oTrans=m_SqlConnection.BeginTransaction(transName);
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    oSqlCommand.Transaction=oTrans;
    iRows=oSqlCommand.ExecuteNonQuery();
    oTrans.Commit();
    return true;
   }
   catch(Exception e)
   {
    oTrans.Rollback(transName);
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return false;
   }
   finally
   {
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
   
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteNonQuery
  // 函数说明： 主要用于 insert,update,delete
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual bool ExecuteNonQuery()
  {
   SqlCommand   oSqlCommand=null;
   try
   {
    int iRows=0;
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    iRows=oSqlCommand.ExecuteNonQuery();
    return true;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return false;
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteXml
  // 函数说明： 主要用于 返回xml
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual XmlDocument ExecuteXml(string rootTag,string tableName,string transName)
  {
   
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   DataSet PADS=null;
   XmlDocument oXmlDoc=new XmlDocument();
   try
   {
    
    StringBuilder oSB=new StringBuilder();
    PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接
    oTrans=m_SqlConnection.BeginTransaction(transName);
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    oSqlCommand.Transaction=oTrans;
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    oSB.Append("<"+rootTag+">");
    foreach(DataTable oDTable in PADS.Tables)
    {
     foreach(DataRow oDR in oDTable.Rows)
     {
      oSB.Append(oDR[0].ToString());
     }
    }
    oSB.Append("</"+rootTag+">");
    oXmlDoc.LoadXml(oSB.ToString());
    
    oTrans.Commit();
    return oXmlDoc;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    oTrans.Rollback(transName); 
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (PADS!=null)
    {
     PADS.Dispose();
    }
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
  }

  public virtual XmlDocument ExecuteXml(string rootTag,string tableName)
  {
   
   SqlCommand   oSqlCommand=null;
   XmlDocument oXmlDoc=new XmlDocument();
   DataSet PADS=null;
   try
   {
    StringBuilder oSB=new StringBuilder();
    PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接 
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    oSB.Append("<"+rootTag+">");
    foreach(DataTable oDTable in PADS.Tables)
    {
     foreach(DataRow oDR in oDTable.Rows)
     {
      oSB.Append(oDR[0].ToString());
     }
    }
    oSB.Append("</"+rootTag+">");
    oXmlDoc.LoadXml(oSB.ToString());
    return oXmlDoc;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (PADS!=null)
    {
     PADS.Dispose();
    }
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteDataSet
  // 函数说明：
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual DataSet ExecuteDataSet(string tableName,string transName)
  {
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   try
   {
    DataSet PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接
    oTrans=m_SqlConnection.BeginTransaction(transName);
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
    oSqlCommand.Transaction=oTrans;
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    oTrans.Commit();
    return PADS;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    oTrans.Rollback(transName); 
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
   
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteDataSet
  // 函数说明：
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual DataSet ExecuteDataSet(string tableName)
  {
   
   SqlCommand   oSqlCommand=null;
   try
   {
    DataSet PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接
    oSqlCommand=new SqlCommand(this.m_Sql,this.m_SqlConnection);
   
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    return PADS;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
   
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteDynamicsDataSet
  // 函数说明： 执行动态sql
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public  virtual DataSet ExecuteDynamicsDataSet(string tableName,string transName)
  {//主要用于 返回dataSet
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   string sSql="";
   try
   {
    DataSet PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          //打开连接
    oTrans=m_SqlConnection.BeginTransaction(transName);
    sSql="exec sp_executesql N'"+this.m_Sql+"'";
    oSqlCommand=new SqlCommand(sSql,this.m_SqlConnection);
    oSqlCommand.Transaction=oTrans;
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    oTrans.Commit();
    return PADS;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    oTrans.Rollback(transName);
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
   
  }

  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteDynamicsDataSet
  // 函数说明： 执行动态sql
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual DataSet ExecuteDynamicsDataSet(string tableName)
  {
   SqlTransaction oTrans=null;
   SqlCommand   oSqlCommand=null;
   string sSql="";
   try
   {
    DataSet PADS=new DataSet();
    PADS.Clear();
    m_SqlConnection=new SqlConnection(this.m_Connection);
    m_SqlConnection.Open();          
    sSql="exec sp_executesql N'"+this.m_Sql+"'";
    oSqlCommand=new SqlCommand(sSql,this.m_SqlConnection);
    SqlDataAdapter oSqlDA = new SqlDataAdapter(oSqlCommand);
    int i=oSqlDA.Fill(PADS,tableName);
    return PADS;
   }
   catch(Exception e)
   {
    this.m_Error=e.Message;
    saveError(this.m_Error+"**"+this.m_Sql);
    return null;
   }
   finally
   {
    if (oSqlCommand!=null)
    {
     oSqlCommand.Dispose();
    }
    if (oTrans!=null)
    {
     oTrans.Dispose();
    }
    if (m_SqlConnection!=null)
    {
     m_SqlConnection.Close();
     m_SqlConnection.Dispose();
    }
   }
   
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： ExecuteSqlConnectionClose
  // 函数说明： 关闭数据库连接
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual void ExecuteSqlConnectionClose()
  {
   if (this.m_SqlConnection!=null)
   {
    this.m_SqlConnection.Close();
    this.m_SqlConnection.Dispose();
   }
  }
  //---------------------------------------------------------------------- *\
  // 函数名称： ReadStream
  // 函数说明： 读取流到后台
  // 参数定义：
  // 返回值：  
  // ---------------------------------------------------------------------- */
  public virtual string  ReadStream(ref Stream bufferStream,int i)
  {
   string sAccept="";
   try
   {
    Stream ReceiveStream = bufferStream;
    Byte[] read = new Byte[i];
    int bytes = ReceiveStream.Read(read, 0, i);
    while (bytes > 0)
    {
     System.Text.Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
     sAccept=sAccept+ encode.GetString(read, 0, bytes);
     bytes = ReceiveStream.Read(read, 0, i);
    }
   }
   catch(Exception)
   {
    sAccept = "error";
   }
   return sAccept;
  }
  #endregion
 }
}