自编自用DataProxy-.NET教程,数据库应用

using system;
using system.data;
using system.data.sqlclient;

namespace dataproxy
{
 /// <summary>
 /// 说明:  本类主要实现对数据库的操作(查询|sp)
 /// 建立者:  黄宗银
 /// 建立时间: 2004-12-4
 /// </summary>
 public class dataproxy
 {
  #region 读取数据
  /// <summary>
  /// 从数据库查询数据
  /// </summary>
  /// <param name=”rowscount”>返回最 top 的记录数</param>
  /// <param name=”columns”>查询列名</param>
  /// <param name=”target”>查询目标</param>
  /// <param name=”condition”>查询条件</param>
  /// <param name=”orderby”>排序</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>查询结果</returns>
  public static dataset getdbdata( int rowscount, string columns, string target, string condition, string orderby, string connect, ref string ex )
  {
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    string[] arrcolumns = columns.split( , );
    string strquery = “select top ” + rowscount + ” ” + ((arrcolumns[0].trim() == “*”) ? ” *” : ” [” + arrcolumns[0].trim() + “]”);
    for( int i = 1; i < arrcolumns.length; i++ )
    {
     strquery += “, [” + arrcolumns[i].trim() + “]”;
    }
    strquery += ” from [” + target + “]”;
    
    if( condition != null && condition.trim() != string.empty )
    {
     strquery += ” where ” + condition;
    }

    if( orderby != null && orderby.trim() != string.empty )
    {
     strquery += ” order by ” + orderby;
    }

    sqldataadapter sqldataadapter = new sqldataadapter( strquery, sqlconnection );

    dataset ds = new dataset();
    sqldataadapter.fill( ds );

    sqlconnection.close();
    return ds;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return null;
   }
  }

  /// <summary>
  /// 从数据库查询数据
  /// </summary>
  /// <param name=”columns”>查询列名</param>
  /// <param name=”target”>查询目标</param>
  /// <param name=”condition”>查询条件</param>
  /// <param name=”orderby”>排序</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>查询结果</returns>
  public static dataset getdbdata( string columns, string target, string condition, string orderby, string connect, ref string ex )
  {
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    string[] arrcolumns = columns.split( , );
    string strquery = “select”;
    int nstart = 0;
    if( arrcolumns[0].trim() == “*” )
    {
     strquery += ” *,”;
     nstart = 1;
    }
    for( int i = nstart; i < arrcolumns.length; i++ )
    {
     string[] arrcolumn = arrcolumns[i].trim().split( );
     strquery += ” [” + arrcolumn[0].trim() + “]”;
     if( arrcolumn.length > 1 )
     {
      strquery += ” [” + arrcolumn[1].trim() + “]”;
     }
     strquery += “,”;
    }
    strquery = strquery.substring( 0, strquery.length – 1 ) + ” from [” + target + “]”;

    if( condition != null && condition.trim() != string.empty )
    {
     strquery += ” where ” + condition;
    }

    if( orderby != null && orderby.trim() != string.empty )
    {
     strquery += ” order by ” + orderby;
    }

    sqldataadapter sqldataadapter = new sqldataadapter( strquery, sqlconnection );

    dataset ds = new dataset();
    sqldataadapter.fill( ds );

    sqlconnection.close();
    return ds;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return null;
   }
  }

  /// <summary>
  /// 从数据库查询数据
  /// </summary>
  /// <param name=”columns”>查询列名</param>
  /// <param name=”target”>查询目标</param>
  /// <param name=”condition”>查询条件</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>查询结果</returns>
  public static dataset getdbdata( string columns, string target, string condition, string connect, ref string ex )
  {
   ex = string.empty;

   dataset ds = getdbdata( columns, target, condition, null, connect, ref ex );

   if( ex != string.empty )
   {
    return null;
   }

   return ds;
  }

  /// <summary>
  /// 从数据库查询数据
  /// </summary>
  /// <param name=”columns”>查询列名</param>
  /// <param name=”target”>查询目标</param>
  /// <param name=”pkcolumn”>主键列名</param>
  /// <param name=”pkvalue”>主键值</param>
  /// <param name=”q”>是否加引号</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回查询结果</returns>
  public static datatable getdbdata( string columns, string target, string pkcolumn, string pkvalue, bool q, string connect, ref string ex )
  {
   ex = string.empty;
   string strcondition = null;

   if( pkcolumn != null )
   {
    strcondition = “[” + pkcolumn + “] = “;
    if( q )
    {
     strcondition += “” + pkvalue + “”;
    }
    else
    {
     strcondition += pkvalue;
    }
   }

   dataset ds = getdbdata( columns, target, strcondition, connect, ref ex );

   if( ex != string.empty )
   {
    return null;
   }

   return ds.tables[0];
  }
  #endregion

  #region 执行存储过程
  /// <summary>
  /// 执行某个存储过程通过参数返回值
  /// </summary>
  /// <param name=”p”>存储过程名</param>
  /// <param name=”xsd”>数据集对象</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>执行结果</returns>
  public static void execparam( string p, dataset xsd, string connect, ref string ex )
  {
   sqlconnection sqlconnection = new sqlconnection( connect );

   try
   {
    sqlcommand sqlcommand = new sqlcommand( p, sqlconnection );
    sqlcommand.commandtype = commandtype.storedprocedure;

    if( xsd.tables[“in”] != null )
    {
     foreach( datacolumn dc in xsd.tables[“in”].columns )
     {
      sqlcommand.parameters.add( “@” + dc.columnname, dc.datatype );
      sqlcommand.parameters[“@” + dc.columnname].value = dc.table.rows[0][dc.columnname];
     }
    }

    if( xsd.tables[“out”] != null )
    {
     foreach( datacolumn dc in xsd.tables[“out”].columns )
     {
      sqlparameter sqlparameter = new sqlparameter( “@” + dc.columnname, dc.datatype );
      sqlparameter.direction = parameterdirection.inputoutput;
      sqlparameter.value = system.dbnull.value;
      if( xsd.tables[“out”].rows.count > 0 )
      {
       sqlparameter.value = dc.table.rows[0][dc.columnname];
      }
      sqlcommand.parameters.add( sqlparameter );
     }
    }

    sqlconnection.open();
    sqlcommand.executenonquery();

    if( xsd.tables[“out”] != null )
    {
     datarow dr = xsd.tables[“out”].newrow();
     xsd.tables[“out”].rows.insertat( dr, 0 );
     for( int i = 0; i < xsd.tables[“out”].columns.count; i++ )
     {
      string strcolumnname = xsd.tables[“out”].columns[i].columnname;
      xsd.tables[“out”].rows[0][i] = sqlcommand.parameters[“@” + strcolumnname].value;
     }
    }
   }
   catch( sqlexception ex )
   {
    ex = ex.message;
   }
   finally
   {
    sqlconnection.close();
   }
  }
  #endregion

  #region 四种基本语句

  /// <summary>
  /// 执行select
  /// </summary>
  /// <param name=”text”>select后的文本</param>
  /// <param name=”ds”>输出查询结果</param>
  /// <param name=”srctable”>用于表映射的源表的名称</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int select( string text, ref dataset ds, string srctable, string connect, ref string ex )
  {
   return fill( “select ” + text, ref ds, srctable,  connect, ref ex );
  }

  public static int select( string text, ref dataset ds, string connect, ref string ex )
  {
   return fill( “select ” + text, ref ds, connect, ref ex );
  }

  /// <summary>
  /// 执行一串sql语句
  /// </summary>
  /// <param name=”text”>sql文本</param>
  /// <param name=”ds”>输出查询结果</param>
  /// <param name=”srctable”>用于表映射的源表的名称</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int fill( string text, ref dataset ds, string srctable, string connect, ref string ex )
  {
   ex = string.empty;
   int nfill = 0;
   try
   {
    sqldataadapter sqldataadapter = new sqldataadapter( text, connect );
    nfill = sqldataadapter.fill( ds, srctable );
    return nfill;
   }
   catch( sqlexception ex )
   {
    ex = ex.message;
    return nfill;
   }
   catch( exception ex )
   {
    throw ex;
   }
  }

  public static int fill( string text, ref dataset ds, string connect, ref string ex )
  {
   return fill( text, ref ds, “table”,  connect, ref ex );
  }

  /// <summary>
  /// 执行insert
  /// </summary>
  /// <param name=”text”>insert后的文本</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int insert( string text, string connect, ref string ex )
  {
   return executenonquery( “insert ” + text, connect, ref ex );
  }

  /// <summary>
  /// 执行update
  /// </summary>
  /// <param name=”text”>update后的文本</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int update( string text, string connect, ref string ex )
  {
   return executenonquery( “update ” + text, connect, ref ex );
  }

  /// <summary>
  /// 执行delete
  /// </summary>
  /// <param name=”text”>delete后的文本</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int delete( string text, string connect, ref string ex )
  {
   return executenonquery( “delete ” + text, connect, ref ex );
  }

  /// <summary>
  /// 执行某sql语句(非select子句)
  /// </summary>
  /// <param name=”text”>sql文本</param>
  /// <param name=”connect”>连接字符串</param>
  /// <param name=”ex”>异常信息</param>
  /// <returns>返回行数</returns>
  public static int executenonquery( string text, string connect, ref string ex )
  {
   int nfill = 0;
   ex = string.empty;
   sqlconnection sqlconnection = new sqlconnection( connect );
   try
   {
    sqlcommand sqlcommand = new sqlcommand( text, sqlconnection );
    sqlconnection.open();
    nfill = sqlcommand.executenonquery();
    return nfill;
   }
   catch( sqlexception ex )
   {
    sqlconnection.close();
    ex = ex.message;
    return nfill;
   }
   catch( exception ex )
   {
    sqlconnection.close();
    throw ex;
   }
   finally
   {
    sqlconnection.close();
   }
  }
  #endregion
 }
}