Asp.net DBHelper class with the stored procedure name parameter (add, delete, modify, and query)

 

 

Using System;

Using System. Collections. Generic;

Using System. Text;

Using MyHRAdmin. Model;

Using System. Data;

Using System. Data. SqlClient;

Namespace MyHRAdmin. DAL

{

Public class DBHelper

{

Private static SqlConnection connection;

 

Public static SqlConnection Connection

{

 

Get

{

String con = "Data Source =.; Initial Catalog = HRAdmin; Integrated Security = True ";

If (connection = null)

{

Connection = new SqlConnection (con );

Connection. Open ();

}

Else if (connection. State = System. Data. ConnectionState. Closed)

{

Connection. Open ();

}

Else if (connection. State = System. Data. ConnectionState. Broken)

{

Connection. Close ();

Connection. Open ();

}

Return connection;

}

}

// Obtain reader through SQL and storage parameters to determine whether the reader exists (LOGIN)

/// <Summary>

/// Important

/// </Summary>

/// <Param name = "sqName"> </param>

/// <Param name = "value"> </param>

/// <Returns> </returns>

Public static SqlDataReader GetReader (string sqName, params SqlParameter [] value)

{

SqlCommand com = new SqlCommand (sqName, Connection );

Com. Parameters. AddRange (value );

Com. CommandText = sqName;

Com. CommandType = CommandType. StoredProcedure;

SqlDataReader reader = com. ExecuteReader ();

Return reader;

}

// Obtain the table (query) through SQL and storage Parameters)

Public static DataTable GetTable (string sqName, params SqlParameter [] value)

{

SqlCommand com = new SqlCommand (sqName, Connection );

DataSet ds = new DataSet ();

Com. CommandText = sqName;

Com. CommandType = CommandType. StoredProcedure;

Com. Parameters. AddRange (value );

SqlDataAdapter da = new SqlDataAdapter (com );

Da. Fill (ds );

Return ds. Tables [0];

}

Public static DataTable GetTable (string sqName)

{

SqlCommand com = new SqlCommand (sqName, Connection );

DataSet ds = new DataSet ();

Com. CommandText = sqName;

Com. CommandType = CommandType. StoredProcedure;

// Com. Parameters. AddRange (value );

SqlDataAdapter da = new SqlDataAdapter (com );

Da. Fill (ds );

Return ds. Tables [0];

}

// Use SQL to obtain the first column () of the first row of the storage Parameter ()

Public static int GetScalar (string sqName, params SqlParameter [] value)

{

SqlCommand com = new SqlCommand (sqName, Connection );

Com. CommandText = sqName;

Com. Parameters. AddRange (value );

Com. CommandType = CommandType. StoredProcedure;

Int result = Convert. ToInt32 (com. ExecuteScalar ());

Return result;

 

}

Public static int GetScalar (string sqName, bool Bvalue, params SqlParameter [] value)

{

Int result = 0;

If (Bvalue)

{

SqlCommand com = new SqlCommand (sqName, Connection );

Com. CommandText = sqName;

Com. Parameters. AddRange (value );

Com. CommandType = CommandType. StoredProcedure;

SqlParameter par = com. Parameters. Add ("@ IDENTITY", SqlDbType. Int );

Par. Direction = ParameterDirection. ReturnValue;

Com. ExecuteNonQuery ();

Result = Convert. ToInt32 (par. Value );

}

Return result;

}

// Use SQL to store parameters to obtain the number of affected rows (Increase)

Public static int GetTheChange (string sqName, params SqlParameter [] value)

{

SqlCommand com = new SqlCommand (sqName, Connection );

Com. CommandText = sqName;

Com. Parameters. AddRange (value );

Com. CommandType = CommandType. StoredProcedure;

Int result = com. ExecuteNonQuery ();

Return result;

}

 

}

}

 

From Keep Going