SQL Server database universal access class

private static string connstring=configurationmanager.connstrings["ConnString"]. ToString (); Add the appropriate database connection profile to the configuration in app. Config

1. Formatting SQL statements

Perform additions and deletions and change the search

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 ();

}

}

Single result query

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 Data Set 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

Perform additions and deletions and change the search

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 ();

}

}

Querying single results

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 ();

}

}

Querying a 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

Perform additions and deletions and change the search

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 ();

}

}

Querying single results

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 ();

}

}

Querying a 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. Using Transaction Updates

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 ();//Open transaction

foreach (String sql in Sqllist)

{

Cmd.commandtext=sql;

Cmd. ExecuteNonQuery ();

}

Cmd.Transaction.Commit ();//Commit a transaction

return true;

}

catch (Exception ex)

{

if (cmd. Transaction!=null)

{

Cmd. Transaction.rollback ();//ROLLBACK TRANSACTION

}

Throw ex;

}

Finally

{

if (cmd. Transaction!=null)

{

Cmd. transaction=null;//emptying transactions

}

Conn. Close ();

}

}

SQL Server database universal access class