Self-woven personal dataproxy

Using System;
Using System.Data;
Using System.Data.SqlClient;

Namespace Dataproxy
{
<summary>
Note: This class mainly implements the operation of the database (query | SP)
Established by: Huang Zongban
Establishment Time: 2004-12-4
</summary>
public class Dataproxy
{
#region Read Data
<summary>
Querying data from a database
</summary>
<param name= "Rowscount" > returns the top record number </param>
<param name= "Columns" > Query column name </param>
<param name= "target" > Query target </param>
<param name= "Condition" > Query conditions </param>
<param name= "ORDER By" > Sort </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> Query Results </returns>
public static DataSet getdbdata (int rowscount, string Columns, String Target, String Condition, string by, string Co Nnect, 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 (by!= null && Orderby.trim ()!= string. Empty)
{
Strquery + = "ORDER BY" + order;
}

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>
Querying data from a database
</summary>
<param name= "Columns" > Query column name </param>
<param name= "target" > Query target </param>
<param name= "Condition" > Query conditions </param>
<param name= "ORDER By" > Sort </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> Query Results </returns>
public static DataSet Getdbdata (String Columns, String Target, String Condition, string by, string Connect, ref stri ng 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 (by!= null && Orderby.trim ()!= string. Empty)
{
Strquery + = "ORDER BY" + order;
}

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>
Querying data from a database
</summary>
<param name= "Columns" > Query column name </param>
<param name= "target" > Query target </param>
<param name= "Condition" > Query conditions </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> Query Results </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>
  ///query data from the database
  ///</summary>
   ///<param name= "Columns" > Query column name </param>
  ///<param name= "target" > Query destination </ Param>
  ///<param name= "Pkcolumn" > Primary key column name </param>
  ///<param name= " Pkvalue > Primary key value </param>
  ///<param name= "Q" > whether to add quotes </param>
  /// <param name= "Connect" > Connection string </param>
  ///<param name= "Ex" > Exception information </param>
  ///<returns> return query results </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 Execute Stored Procedures
<summary>
Execute a stored procedure to return a value through a parameter
</summary>
<param name= "P" > Stored procedure name </param>
<param name= "xsd" > DataSet object </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> Implementation Results </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 ();
&NBSP;&NBSP;&NBSP}
  }
   #endregion

#region four basic statements

<summary>
Execute Select
</summary>
Text after <param name= "text" >select </param>
<param name= "DS" > Output query Results </param>
<param name= "srctable" > Name of the source table for table mappings </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> returns the number of rows </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>
Execute a string of SQL statements
</summary>
<param name= "text" >sql text </param>
<param name= "DS" > Output query Results </param>
<param name= "srctable" > Name of the source table for table mappings </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> returns the number of rows </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>
  ///Execute insert
  ///</summary>
   ///<param name= text </param> after "text" >insert
  ///<param name= "Connect" > Connection string </param>
  ///<param name= "Ex" > Exception information </param>
  ///<returns> Returns the number of rows </returns>
  public static int Insert (string Text, String Connect, ref string Ex)
   ; {
   return ExecuteNonQuery ("INSERT" + Text, Connect, ref Ex);
  }

  ///<summary>
  ///Execute update
  ///</summary>
   ///<param name= text </param> after "text" >update
  ///<param name= "Connect" > Connection string </param>
  ///<param name= "Ex" > Exception information </param>
  ///<returns> Returns the number of rows </returns>
  public static int Update (string Text, String Connect, ref string Ex)
   ; {
   return ExecuteNonQuery ("UPDATE" + Text, Connect, ref Ex);
  }

<summary>
Execute Delete
</summary>
Text after <param name= "text" >delete </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> returns the number of rows </returns>
public static int Delete (string Text, String Connect, ref string Ex)
{
Return ExecuteNonQuery ("DELETE" + Text, Connect, ref Ex);
}

<summary>
Execute an SQL statement (not a SELECT clause)
</summary>
<param name= "text" >sql text </param>
<param name= "Connect" > Connection string </param>
<param name= "Ex" > Exception information </param>
<returns> returns the number of rows </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
}
}