Compilation of SQL parameter encapsulation class for database
Copy Code code as follows:
Using System;
Using System.Data;
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;
Using System.Text;
Namespace Chinasite.classes
{
public class DbAccess
{
SqlConnection conn = null;
SqlCommand cmd = null;
Public DbAccess ()
{
//
TODO: Add constructor logic here
//
conn = new SqlConnection ();
Conn. ConnectionString = "initial catalog=pubs;data source=.; User id=sa;password= ";
Conn. ConnectionString = convert.tostring (system.configuration.configurationsettings.appsettings["datasource"));
Conn. ConnectionString = convert.tostring (system.configuration.configurationmanager.appsettings["datasource"));
cmd = new SqlCommand ();
Cmd. Connection = conn;
}
<summary>
Get data based on SQL statement
</summary>
<param name= "SQL" ></param>
<returns></returns>
Public DataTable gettable (String sql)
{
DataSet ds = new DataSet ();
Try
{
Cmd.commandtext = SQL;
SqlDataAdapter da = new SqlDataAdapter ();
Da. SelectCommand = cmd;
Da. Fill (DS);
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
Return DS. Tables[0]?? New DataTable ();
}
<summary>
Gets the data based on the SQL statement with parameters
</summary>
<param name= "SQL" ></param>
<param name= "pas" ></param>
<returns></returns>
Public DataTable gettable (String sql, params sqlparameter[] pas)
{
DataSet ds = new DataSet ();
Try
{
Cmd.commandtext = SQL;
SqlDataAdapter da = new SqlDataAdapter ();
Da. SelectCommand = cmd;
Cmd. Parameters.clear ();
foreach (SqlParameter temppa in PAS)
{
Cmd. Parameters.Add (Temppa);
}
Da. Fill (DS);
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
Return DS. Tables[0]?? New DataTable ();
}
<summary>
Return to the new state based on the SQL statement
</summary>
<param name= "SQL" ></param>
<returns></returns>
public bool GetState (String sql)
{
BOOL succ = false;
Try
{
Cmd.commandtext = SQL;
Conn. Open ();
succ = cmd. ExecuteNonQuery () > 0? (true): (false);
Conn. Close ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return false;
}
return succ;
}
<summary>
Returns a parameter with the new state, based on the SQL statement.
</summary>
<param name= "SQL" >sql statement </param>
<param name= "pas" > Set of Parameters </param>
<returns></returns>
public bool GetState (String sql, params sqlparameter[] pas)
{
BOOL succ = false;
Try
{
Cmd.commandtext = SQL;
Cmd. Parameters.clear ();
foreach (SqlParameter temppa in PAS)
{
Cmd. Parameters.Add (Temppa);
}
Conn. Open ();
succ = cmd. ExecuteNonQuery () > 0? (true): (false);
Conn. Close ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return false;
}
return succ;
}
<summary>
Returns the data for the first cell based on the SQL statement
</summary>
<param name= "SQL" ></param>
<returns></returns>
public string GetOne (String sql)
{
string res = "";
Try
{
Cmd.commandtext = SQL;
Conn. Open ();
res = cmd. ExecuteScalar () = null? (""): (convert.tostring (CMD). ExecuteScalar ()));
Conn. Close ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
return res;
}
<summary>
Returns the data band parameter of the first cell based on the SQL statement.
</summary>
<param name= "SQL" ></param>
<param name= "pas" ></param>
<returns></returns>
public string GetOne (String sql, params sqlparameter[] pas)
{
string res = "";
Try
{
Cmd.commandtext = SQL;
Cmd. Parameters.clear ();
foreach (SqlParameter temppa in PAS)
{
Cmd. Parameters.Add (Temppa);
}
Conn. Open ();
res = cmd. ExecuteScalar () = null? (""): (convert.tostring (CMD). ExecuteScalar ()));
Conn. Close ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
return res;
}
<summary>
Returns the DataReader of the data
</summary>
<param name= "SQL" ></param>
<returns></returns>
Public SqlDataReader Getdatareader (String sql)
{
SqlDataReader dr = null;
Try
{
Conn. Open ();
Cmd.commandtext = SQL;
Dr = cmd. ExecuteReader ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
Return Dr;
}
<summary>
That returns the DataReader with parameters of the data.
</summary>
<param name= "SQL" ></param>
<param name= "pas" ></param>
<returns></returns>
Public SqlDataReader Getdatareader (String sql, params sqlparameter[] pas)
{
SqlDataReader dr = null;
Try
{
Conn. Open ();
Cmd. Parameters.clear ();
foreach (SqlParameter temppa in PAS)
{
Cmd. Parameters.Add (Temppa);
}
Cmd.commandtext = SQL;
Dr = cmd. ExecuteReader ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
return null;
}
Return Dr;
}
<summary>
Open connection
</summary>
public void Openconn ()
{
IF (Conn. State!= ConnectionState.Open)
{
Try
{
Conn. Open ();
}
catch (Exception ex)
{
This. ShowError (ex. message);
Return
}
}
}
<summary>
Close connection
</summary>
public void Closeconn ()
{
IF (Conn. State!= connectionstate.closed)
{
Try
{
Conn. Close ();
cmd = null;
conn = null;
}
catch (Exception ex)
{
This. ShowError (ex. message);
Return
}
}
}
<summary>
Eject the wrong message
</summary>
<param name= "Err" ></param>
public void ShowError (string err)
{
System.Web.HttpContext.Current.Response.Write (Script (Err, ""));
}
<summary>
Display information
</summary>
<param name= "Err" ></param>
public void ShowMessage (string mes, String loc)
{
System.Web.HttpContext.Current.Response.Write (Script (MES, loc));
}
<summary>
JavaScript script
</summary>
<param name= "Mess" ></param>
<param name= "Loc" ></param>
<returns></returns>
public string Script (string mess, String loc)
{
StringBuilder sb = new StringBuilder ();
Sb. Append ("<script language= ' JavaScript ' >");
Sb. Append ("Alter");
Sb. Append (mess);
Sb. Append ("');");
Sb. Append (Loc);
Sb. Append ("</script>");
Return SB. ToString ();
}
}
}
