自編自用DataProxy

來源:互聯網
上載者:User
 

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




相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。