Using system; using system.data.sqlclient; using system.configuration;
Namespace SQLSERVERDAL {/// /// Sqldal's summary description. /// public class SQLDAL {#region properties private static string connectionString = ConfigurationSettings.AppSettings [ "ConnectionString"]; private static SqlConnection con = new SqlConnection (); private static SqlCommand cmd = new SqlCommand (); #endregion Public Sqldal () {// // Todo: Add constructor logic //} here
#Region Open Current Database Connection /// /// Open Current Database Connection /// Private Static Void OpenConnection () {if (Con.State == ConnectionsTate.Closed) {Con.Connectionstring = ConnectionsTRING ; Cmd.connection = con;} Try {con.open ();} catch (exception e) {throw new exception (E.MESSAGE);}} #ENDREGION
#Region Close Current Database Connection /// /// Close Current Database Connection /// Private Static Void CloseConnection () {if (Con.State == ConnectionsTate.Open) {cmd.dispose () Con. close (); con.dispose ();}} #ENDREGION
#REGON executes the SQL statement, and returns the record number //// // / execution SQL statement, and returns the number of records of the impact /// /// < / param> /// public static int ExecuteSql (string strsql) {int count = 0; try {openConnection (); cmd.CommandType = CommandType.Text; cmd.CommandText = strsql; count = cmd .ExecutenonQuery (); catch new exception (E.MESSAGE);} finally {closeConnection ();} return count;} #ENDREGION
#Region executes the SQL statement and returns the first record of the first line, and the return value is required to remove the SQL statement and return to the first record of the first line of the first line. Upload operations to Object /// /// public static object executescalar (string strsql) {Object Obj = new object (); try {openConnection (); cmd.CommandType = CommandType.Text; cmd.CommandText = strsql; obj = cmd.ExecuteScalar ();} catch (Exception e) {throw new Exception (e.Message); } Finally {closeConnection ();} return} #endregion #Region executes a SQL statement, while transaction /// // / execute a SQL statement, while transaction /// /// public static void ExecuteSqlWithTransaction (string strsql) {SqlTransaction Trans; Trans = con.BeginTransaction (); cmd.Transaction = Trans; try {openConnection (); cmd.CommandType = CommandType.Text; cmd.commandtext = strsql; cmd.executenonQuery (); trans.Commit ();} catch (Exception E) {Trans. Rollback (); throw new EXCEPTION (E.MESSAGE);} finally {closeConnection ();}
#Region Execute a plurality of SQL statements, while transaction /// // / execute multiple SQL statements, while transaction /// /// the storage array sql statement public static void ExecuteSqlWithTransaction (string [] strsql) {SqlTransaction Trans; Trans = con.BeginTransaction (); cmd.Transaction = Trans; try {openConnection (); cmd.CommandType = CommandType .Text; Foreach (String SQL in strsql) {cmd.Commandtext = SQL; cmd.executenonQuery ();} trans.commit ();} catch (exception e) {trans. Rollback (); throw new exception (E.MESSAGE );} Finally {closeConnection ();}} # endregion # region returns SQLDataReader ////////////////////////////////////// incoming sql statement /// SqlDataReader public static SqlDataReader dataReader (string strsql) {SqlDataReader dr = null; try {openConnection (); cmd.CommandType = CommandType. Text; cmd.commandtext = strsql; dr = cmd.executeReader (Comman Dbehavior.closeConnection);} catch {try {dr.close (); closeconnection ();} catch {}} return DR;} #ENDREGION
#REGON Returns SqlDataReader /// /// (/// ///) of the reference to the specified SQL statement, returns SQLDataReader /// /// incoming SQL statement /// Reference SqlDataReader Public Static Void DataRead (String Strsql, Ref SqlDataReader DR) {Try {OpenConnection (); cmd.commandtype = CommandType.Text; cmd .Commandtext = strsql; DR = cmd.executeReader (Commandbehavior.CloseConnection);} catch {try {if (DR! = Null&&! Dr.isclosed) {Dr.close ();}} catch {} finally} catch {} finally} catch {} finally} }}} # EndRegion # Region Performs a stored procedure without parameters, and returns the record number //// // / execution without parameters, and returns the number of records //// /// Stored Procedure Name /// public static int executeProcedure (string procname) {int count; try {OpenConnection (); cmd. CommandType = commandType.StoredProcedure; cmd.commandtext = procname; count = cmd.executenonury ();} catch (Exception E) {throw n EW exception (E.MESSAGE);} finally {closeconnection ();} return count;
#Region Execute the stored procedure /// // / / / / /// stored procedure name /// parameter array public static void executeProcedure (String procname, sqlparameter [] coll) {{openconnection (); cmd.parameters.clear (); for (int i = 0; i
#Region Execute the stored procedure with multiple parameters and return to the referenced DataSet
public static void ExecuteProcedure (string procName, SqlParameter [] coll, ref DataSet ds) {try {SqlDataAdapter da = new SqlDataAdapter (); for (int i = 0; i
Da.selectcommand = cmd; da.fill (ds);} catch (exception e) {throw new exception (E.MESSAGE);} finally {cmd.Parameters.clear (); closeconnection ();}} #ENDREGION
#REGON executes a SQL statement and returns the specified DataSet /// // / / / execution of a SQL statement and returns the specified DataSet /// /// incoming SQL statement /// public static dataset dataset (string strsql) {dataset ds = new dataset (); sqldataadapter Da = new sqldataadapter (); try {openconnection (); cmd.commandtype = CommandType.StoredProcedure; cmd.commandtext = strsql;
Da.selectCommand = cmd; da.fill (ds);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();} return ds;} # endregion # region executes a SQL statement and Returns the referenced DataSet /// // / execution of a SQL statement and returns a referenced DataSet /// /// Inferred SQL statement / // reference DataSet public static void dataSet (string strsql, ref DataSet ds) {SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); cmd.CommandType = CommandType. TEXT; cmd.commandtext = strsql; da.selectcommand = cmd; da.fill (ds);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeconnection ();}} #ENDREGION
#Region Returns a DataTable specified by the SQL statement
public static DataTable dataTable (string strsql) {DataTable dt = new DataTable (); SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); cmd.CommandType = CommandType.Text; cmd.CommandText = strsql;
Da.selectCommand = cmd; da.fill (dt);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();} return dt;} #ENDREGION
#Region Returns a DataTable /// // / / / / / / ////// /// incoming SQL statement to specify the reference to the reference to the specified SQL statement. /// Reference DataTable public static void data (string strsql, ref data) {sqldataadapter da = new sqldataadapter (); try {openconnection (); cmd .Commandtype = commandType.text; cmd.commandtext = strsql;
Da.selectCommand = cmd; da.fill (dt);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();}} # endregion # region performs stored procedures with parameters and returns DataTable /// /// Execute a stored procedure with parameters and return data set /// /// stored procedure name /// SqlParameterCollection input parameters /// DataTable public static DataTable dataTable (string procName, SqlParameterCollection parameters) {SqlDataAdapter da = new SqlDataAdapter (); DataTable dt = new DataTable ( ); try {openConnection (); cmd.Parameters.Clear (); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; foreach (SqlParameter parameter in parameters) {SqlParameter p = (SqlParameter) parameter; cmd.Parameters. Add (p);} da.selectcommand = cmd; da.fill (dt);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();} return dt;} #endr Egion
#Region executes the SQL statement and returns a dataview /// // / execute SQL statement and returns DataView /// /// Inferred SQL statement /// DataView public static DataView dataView (string strsql) {SqlDataAdapter da = new SqlDataAdapter (); DataSet ds = new DataSet (); DataView dv = new DataView (); try {openConnection (); Cmd.commandType = commandType.text; cmd.commandtext = strsql;
Da.selectCommand = cmd; da.fill (ds); DV = ds.tables [0] .defaultview;} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();} Return DV } # Endregion # region Execute SQL statements and returns the referenced DataView /// // /// /// biography SQL statement /// incoming reference DataView Public Static Void DataView (String Strsql, Ref DataView DV) {Dataset DS = New DataSet (); Sqldataadapter Da = new sqldataadapter (); try {OpenConnection (); cmd.commandtype = CommandType.Text; cmd.commandtext = strsql; da.selectcommand = cmd; da.fill (DS);
DV = ds.tables [0] .defaultView;} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();}} #endregion
}
