genericdataaccess.cs

医院的用户管理与缴费与当病人去医院看病的时候要挂号等作用。

using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// GenericDataAccess 的摘要说明
/// </summary>
public class GenericDataAccess
{
  static GenericDataAccess( )
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
 
    public static DbCommand CreateCommand()
    {
        try
        {
            // Obtain the database provider name
            string dataProviderName = Configuration.DbProviderName;
            // Obtain the database connection string
            string connectionString = Configuration.DbConnectionString;
            // Create a new data provider factory
            DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
            // Obtain a database specific connection object
            DbConnection conn = factory.CreateConnection();
            // Set the connection string
            conn.ConnectionString = connectionString;
            // Create a database specific command object
            DbCommand comm = conn.CreateCommand();

            return comm;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    // creates and prepares a new DbCommand object on a new connection
    public static SqlCommand CreateStoredProcedureCommand()
    {

        string connectionString = Configuration.DbConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        // Create a database specific command object
        SqlCommand comm = conn.CreateCommand();
        // Set the command type to stored procedure
        comm.CommandType = CommandType.StoredProcedure;
        // Return the initialized command object
        return comm;
    }
    public static int ExecuteLogNonQuery(String sql)
    {
        DbCommand command = GenericDataAccess.CreateCommand();
        command.CommandText = sql;
        // The number of affected rows 
        int affectedRows = -1;
        // Execute the command making sure the connection gets closed in the end

        // Open the connection of the command
        try
        {
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            string tmpsql = sql.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
                affectedRows = command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
            // SendMailClass.LogError(ex);
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the number of affected rows
        return affectedRows;
    }



    public static string ExecuteScalar(String sql, Page p)
    {

        DbCommand command = GenericDataAccess.CreateCommand();
        command.CommandText = sql;
        // The value to be returned 
        string value = "";
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            string tmpsql = sql.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
                value = command.ExecuteScalar().ToString();
      
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
          
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the result
        return value;
    }


    public static int ExecuteNonQuery(String sql, Page p)
    {
        DbCommand command = GenericDataAccess.CreateCommand();
        command.CommandText = sql;
        // The number of affected rows 
        int affectedRows = -1;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            string tmpsql = sql.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
                affectedRows = command.ExecuteNonQuery();
            else
            {

            
                return -1;
            }
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
           
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the number of affected rows
        return affectedRows;
    }
    public static DataTable ExecuteSelectCommand(String sql, Page p)
    {
        // The DataTable to be returned 
        DataTable table = new DataTable();
        DbCommand command = GenericDataAccess.CreateCommand();
        command.CommandText = sql;
        try
        {
            // Open the data connection 
            command.Connection.Open();
            string tmpsql = sql.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
            {
                DbDataReader reader = command.ExecuteReader();
                table.Load(reader);
                reader.Close();
          
            }

        }
        catch (Exception ex)
        {
            // Utilities.LogError(ex);
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        return table;
    }
    /// <summary>
    /// 分页专用
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="p"></param>
    /// <returns></returns>
    public static DataTable ExecuteSelectCommand(String sql, int pagenum, int num, Page p)
    {

        DataTable table = new DataTable();
        string connectionString =Configuration.DbConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        try
        {
            string tmpsql = sql.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
            {
                SqlDataAdapter myadapter = new SqlDataAdapter(sql, con);
                con.Open();
                myadapter.Fill((pagenum - 1) * num, num, table);
                myadapter.Dispose();
            
            }

        }
        catch (Exception ex)
        {
            
            throw ex;
        }
        finally
        {
            // Close the connection
            con.Close();
        }
        return table;

    }

    public static string ExecuteScalar(DbCommand command)
    {
        // DbCommand command = GenericDataAccess.CreateStoredProcedureCommand();
        // command.CommandText = sql;
        // The value to be returned 
        string value = "";
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            string tmpsql = command.CommandText.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
                value = command.ExecuteScalar().ToString();
        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
          
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the result
        return value;
    }
    public static int ExecuteNonQuery(DbCommand command)
    {
      

        int affectedRows = -1;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            string tmpsql = command.CommandText.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
                affectedRows = command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            // Log eventual errors and rethrow them
         
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the number of affected rows
        return affectedRows;
    }
    public static DataTable ExecuteSelectCommand(DbCommand command)
    {
        // The DataTable to be returned 
        DataTable table = new DataTable();
     
        try
        {
            string tmpsql = command.CommandText.ToLower();
            if (tmpsql.IndexOf("script") == -1 && tmpsql.IndexOf("iframe") == -1)
            {
                // Open the data connection 
                command.Connection.Open();
                DbDataReader reader = command.ExecuteReader();
                table.Load(reader);
                reader.Close();
              
            }

        }
        catch (Exception ex)
        {
          
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        return table;
    }
}