Database generic category, database generic

Database generic category, database generic

Private static string connString = ConfigurationManager. ConnStrings ["connString"]. ToString (); // Add the corresponding database connection configuration file in app. config.

1. Format SQL statements

// Add, delete, modify, and query

Public static int Update (string SQL)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Return cmd. ExecuteNonQuery ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Query a single result

Public static object GetSingleResult (string SQL)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Return cmd. ExecuteScalar ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Read-only result set Query

Public static SqlDataReader GetReader (string SQL)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Return cmd. ExecuteReader (CommandBehavior. CloseConnection );

}

Catch (Exception ex)

{

Conn. Close ();

Throw ex;

}

}

// DataSet Query

Public static DataSet GetDataSet (string SQL)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

DataSet ds = new DataSet ();

SqlDataAdapter da = new SqlDataAdapter ();

Try

{

Conn. Open ();

Da. Fill (ds );

Return ds;

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

2. SQL statements with Parameters

// Add, delete, modify, and query

Public static int Update (string SQL, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Cmd. Parameters. AddRange (param );

Return cmd. ExecuteNonQuery ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Query a single result

Public static object GetSingleResult (string SQL, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Cmd. Parameters. AddRange (param );

Retrurn cmd. ExecuteScalar ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Query the read-only result set

Public static SqlDataReader GetReader (string SQL, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand (SQL, conn );

Try

{

Conn. Open ();

Cmd. Parameters. AddRange (param );

Return cmd. ExecuteReader (CommandBehavior. CloseConnection );

}

Catch (Exception ex)

{

Conn. Close ();

Throw ex;

}

}

3. Stored Procedures with Parameters

// Add, delete, modify, and query

Public static int UpdateByProcedure (string procedureName, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Try

{

Conn. Open ();

Cmd. CommandType = CommandType. StoredProcedure;

Cmd. CommandText = procedureName;

Cmd. Parameters. AddRange (param );

Return cmd. ExecuteNonQuery ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Query a single result

Public static object GetSingleResultByProcedure (string procedureName, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Try

{

Conn. Open ();

Cmd. CommandType = CommandType. StoredProcedure;

Cmd. CommandText = procedureName;

Cmd. Parameters. AddRange (param );

Retrurn cmd. ExecuteScalar ();

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Conn. Close ();

}

}

// Query the read-only result set

Public static SqlDataReader GetReaderByProcedure (string procedureName, SqlParameter [] param)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Try

{

Conn. Open ();

Cmd. CommandType = CommandType. StoredProcedure;

Cmd. CommandText = procedureName;

Cmd. Parameters. AddRange (param );

Return cmd. ExecuteReader (CommandBehavior. CloseConnection );

}

Catch (Exception ex)

{

Conn. Close ();

Throw ex;

}

}

4. Use transaction update

Public static bool UpdateByTracsaction (List <string> sqlList)

{

SqlConnection conn = new SqlConnection (connString );

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Try

{

Conn. Open ();

Cmd. Transaction = conn. BeginTransaction (); // start the Transaction

Foreach (string SQL in sqlList)

{

Cmd. CommandText = SQL;

Cmd. ExecuteNonQuery ();

}

Cmd. Transaction. Commit (); // submit the Transaction

Return true;

}

Catch (Exception ex)

{

If (cmd. Transaction! = Null)

{

Cmd. Transaction. Rollback (); // roll back the Transaction

}

Throw ex;

}

Finally

{

If (cmd. Transaction! = Null)

{

Cmd. Transaction = null; // clear the Transaction

}

Conn. Close ();

}

}