About .net C# Sql database SQLHelper class example code

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class SQLHelper
{
//Get the database connection configuration in web.config
public static readonly string ConnectString = ConfigurationManager.ConnectionStrings[ "DBString"].ConnectionString;

///

                                                                                                                             param name="cmdType">Stored procedure or Sql statement
/// Stored procedure name or Sql statement content
/ // Parameter list
; ///
; ; public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter [] CommandParams)
                                                                                SqlCommand cmd = new SqlCommand(); reCommand( cmd, conn, null, cmdType, cmdText, CommandParams);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;

      }

            catch

                                                                                                                                                            

#}


                                                            
/// Operation category (stored procedure, sql)
/// Stored procedure name or Sql statement
/// Parameters
/// Return the number of affected data rows< ;/returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
{

SqlCommand cmd = new SqlCommand();
PrepareComman d( cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);
                                                                                                                                             .Direction = ParameterDirection.ReturnValue;

                                                                                                               cmd.ExecuteNonQuery(); if (cmdType == CommandType.Text) ery();

cmd.Parameters.Clear ();

return val;

}

///

       /// 返回数据集 DataReader
       ///

       ///
       ///
       ///
       ///
       public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlCommand cmd = new SqlCommand();
           SqlConnection conn = new SqlConnection(ConnectString);

try
           {
               PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);
               SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
               cmd.Parameters.Clear();
               return rdr;
           }
           catch (Exception ex)
           {
               conn.Close();
               //   throw new Exception("操作失败！");
               throw new Exception(ex.Message);
           }
       }

///

       /// 有事务的取数据
       ///

       ///
       ///
       ///
       ///
       ///
       public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlCommand cmd = new SqlCommand();
           PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);
           SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
           cmd.Parameters.Clear();
           return rdr;
       }

 

public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(ConnectString);

try
           {
               PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);
               object val = cmd.ExecuteScalar();
               cmd.Parameters.Clear();
               return val;
           }
           catch
           {
               throw;
           }
           finally
           {
               conn.Close();
           }

}

public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlCommand cmd = new SqlCommand();
           PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);
           object val = cmd.ExecuteScalar();
           cmd.Parameters.Clear();
           return val;

}

 

///

       /// 根据Sql语句取得表
       ///

       ///
       ///
       ///
       ///
       public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           DataTable temptable = new DataTable();

SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(ConnectString);

try
           {
               PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);

SqlDataAdapter da = new SqlDataAdapter(cmd);

SqlCommandBuilder scb = new SqlCommandBuilder(da);

da.Fill(temptable);
           }
           finally
           {
               conn.Close();
           }

return temptable;
       }

       public static DataTable ExecuteTable(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           DataTable temptable = new DataTable();
           SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);
           SqlDataAdapter da = new SqlDataAdapter(cmd);
           SqlCommandBuilder scb = new SqlCommandBuilder(da);
           da.Fill(temptable);
           cmd.Parameters.Clear();

return temptable;

}

       ///

       /// 根据Sql语句或存储过程取得数据
       ///

       ///
       ///
       ///
       ///
       public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlConnection conn = new SqlConnection(ConnectString);

SqlCommand cmd = new SqlCommand();

DataSet TempDataSet = new DataSet();

try
           {
               PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams);
               SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
               sqlAdapter.Fill(TempDataSet);
               cmd.Parameters.Clear();
               return TempDataSet;

}
           finally
           {
               conn.Close();
           }

}

       public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams)
       {
           SqlCommand cmd = new SqlCommand();
           DataSet TempDataSet = new DataSet();
           PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams);
           SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
           sqlAdapter.Fill(TempDataSet);
           cmd.Parameters.Clear();
           return TempDataSet;
       }

///

       /// 生成Sql语句或准备
       ///

       ///
       ///
       ///
       ///
       ///
       ///
       private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
       {

if (conn.State != ConnectionState.Open)
               conn.Open();

cmd.Connection = conn;
           cmd.CommandText = cmdText;

if (trans != null)
               cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
           {
               foreach (SqlParameter parm in cmdParms)
                   cmd.Parameters.Add(parm);
           }
       }

public static object ToDBValue(object value)
       {
           return value == null ? DBNull.Value : value;
       }

public static object FromDBValue(object dbValue)
       {
           return dbValue == DBNull.Value ? null : dbValue;
       }
   }

The above is the detailed content of About .net C# Sql database SQLHelper class example code. For more information, please follow other related articles on the PHP Chinese website!