自编自用DataProxy

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
 }
}