数据库数据操作源代码

weixin_30713953

2497人浏览 · 2005-07-14 16:59:00

weixin_30713953 · 2005-07-14 16:59:00 发布

#region 版权说明
//******************************************************
// 版权所有2003-2010 上海宝荣软件有限公司
//*****************************************************

//******************************************************
// 名称: 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()
  {//获取单个值结果
   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
}
}

详情访问 http://www.spsdotnet.com/page.aspx?pageid=110
wuhao@spsdotnet.com

转载于:https://www.cnblogs.com/spsdotnet/archive/2005/07/14/192889.html

AtomGit 开源协作平台测评赛

瓜分20万奖金获得内推名额丰厚实物奖励易参与易上手

更多推荐

ADS1292R 使用过程心电图高精度ADC模块

文章目录1 Fundamentals ofPrecision ADC Noise Analysis 精密模数转换器噪声分析基础1 Fundamentals ofPrecision ADC Noise Analysis 精密模数转换器噪声分析基础https://www.ti.com.cn/cn/lit/wp/slyy192/slyy192.pdf?ts=1600659610730&ref_u

开放原子开发者工作坊

实现一个家庭安防与环境监测系统（一）

开放原子开发者工作坊

【cf】Codeforces Round #774 (Div. 2) 前4题

题目A. Square Counting 简单数学题目大意题解代码B. Quality vs Quantity 排序题目大意题解代码C. Factorials and Powers of Two 状态压缩dp+位运算题目大意题解代码D. Weight the Tree 树形dp+dfs题目大意题解代码E. Power Board 看起来像是数论？许多年没打cf了，偶尔打了一盘，恢复紫名了。A. S