自编自用DataProxy

using System;
using System.Data;
using System.Data.SqlClient;

namespace DataProxy
{
///

1<summary>   
2/// 说明: 本类主要实现对数据库的操作(查询|SP)   
3/// 建立者: 黄宗银   
4/// 建立时间: 2004-12-4   
5/// </summary>

public class DataProxy
{
#region 读取数据
///

1<summary>   
2/// 从数据库查询数据   
3/// </summary>

///

1<param name="RowsCount"/>

返回最 Top 的记录数
///

1<param name="Columns"/>

查询列名
///

1<param name="Target"/>

查询目标
///

1<param name="Condition"/>

查询条件
///

1<param name="OrderBy"/>

排序
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

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

///

1<summary>   
2/// 从数据库查询数据   
3/// </summary>

///

1<param name="Columns"/>

查询列名
///

1<param name="Target"/>

查询目标
///

1<param name="Condition"/>

查询条件
///

1<param name="OrderBy"/>

排序
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

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

///

1<summary>   
2/// 从数据库查询数据   
3/// </summary>

///

1<param name="Columns"/>

查询列名
///

1<param name="Target"/>

查询目标
///

1<param name="Condition"/>

查询条件
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

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

///

1<summary>   
2/// 从数据库查询数据   
3/// </summary>

///

1<param name="Columns"/>

查询列名
///

1<param name="Target"/>

查询目标
///

1<param name="PKColumn"/>

主键列名
///

1<param name="PKValue"/>

主键值
///

1<param name="Q"/>

是否加引号
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<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 执行存储过程
///

1<summary>   
2/// 执行某个存储过程通过参数返回值   
3/// </summary>

///

1<param name="P"/>

存储过程名
///

1<param name="xsd"/>

数据集对象
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<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 四种基本语句

///

1<summary>   
2/// 执行SELECT   
3/// </summary>

///

1<param name="Text"/>

SELECT后的文本
///

1<param name="ds"/>

输出查询结果
///

1<param name="srcTable"/>

用于表映射的源表的名称
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<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 );
}

///

1<summary>   
2/// 执行一串SQL语句   
3/// </summary>

///

1<param name="Text"/>

SQL文本
///

1<param name="ds"/>

输出查询结果
///

1<param name="srcTable"/>

用于表映射的源表的名称
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<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 );
}

///

1<summary>   
2/// 执行INSERT   
3/// </summary>

///

1<param name="Text"/>

INSERT后的文本
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<returns>返回行数</returns>

public static int Insert( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "INSERT " + Text, Connect, ref Ex );
}

///

1<summary>   
2/// 执行UPDATE   
3/// </summary>

///

1<param name="Text"/>

UPDATE后的文本
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<returns>返回行数</returns>

public static int Update( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "UPDATE " + Text, Connect, ref Ex );
}

///

1<summary>   
2/// 执行DELETE   
3/// </summary>

///

1<param name="Text"/>

DELETE后的文本
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

1<returns>返回行数</returns>

public static int Delete( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "DELETE " + Text, Connect, ref Ex );
}

///

1<summary>   
2/// 执行某SQL语句(非SELECT子句)   
3/// </summary>

///

1<param name="Text"/>

SQL文本
///

1<param name="Connect"/>

连接字符串
///

1<param name="Ex"/>

异常信息
///

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

Published At
Categories with Web编程
Tagged with
comments powered by Disqus