C # basic database operations (sqlserver)

I. Basic operations with sqlserver
 
 
The connection character is written in the configuration file in this way.
[Html]
<ConnectionStrings>
<Add name = "ConnStr" connectionString = "data source = xp-ba785745002d; database = databaseonline; Uid = sa; pwd = sasa"/>
</ConnectionStrings>
 
The basic operation class is as follows:
[Csharp]
Public class SQLHelper
{
Private SqlConnection sqlCon = null;
Private SqlCommand cmd = null;
Private SqlDataReader sdr = null;
Public SQLHelper ()
{
SqlCon = new SqlConnection (ConfigurationManager. ConnectionStrings ["ConnStr"]. ConnectionString );
}
/// <Summary>
/// Open the database connection
/// </Summary>
/// <Returns> </returns>
Private SqlConnection GetCon ()
{
If (sqlCon. State = ConnectionState. Closed)
{
SqlCon. Open ();
}
Return sqlCon;
}
/// <Summary>
/// Execute add, delete, modify, and modify SQL statements or stored procedures without Parameters
/// </Summary>
/// <Param name = "plain text"> add, delete, modify, and delete SQL statements or stored procedures </param>
/// <Param name = "ct"> command type </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string plain text, CommandType ct)
{
Int rex;
Try
{
SqlCommand sqlcom = new SqlCommand (plain text, GetCon ());
Sqlcom. CommandType = ct;
Rex = sqlcom. ExecuteNonQuery ();
}
Catch (Exception ex)
{
 
Throw ex;
}
Finally
{
If (sqlCon. State = ConnectionState. Open)
{
SqlCon. Close ();
}
}
Return rex;
}
/// <Summary>
/// Execute the add, delete, modify, SQL statement or stored procedure with Parameters
/// </Summary>
/// <Param name = "plain text"> add, delete, modify, and delete SQL statements or stored procedures </param>
/// <Param name = "ct"> command type </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string plain text, SqlParameter [] paras, CommandType ct)
{
Int res;
Using (cmd = new SqlCommand (plain text, GetCon ()))
{
Cmd. CommandType = ct;
Cmd. Parameters. AddRange (paras );
Res = cmd. ExecuteNonQuery ();
}
Return res;
}
/// <Summary>
/// Execute SQL statements or stored procedures with Parameters
/// </Summary>
/// <Param name = "plain text"> query SQL statements or stored procedures </param>
/// <Param name = "paras"> parameter set </param>
/// <Param name = "ct"> command type </param>
/// <Returns> </returns>
Public DataTable ExecuteQuery (string plain text, SqlParameter [] paras, CommandType ct)
{
DataTable dt = new DataTable ();
Cmd = new SqlCommand (plain text, GetCon ());
Cmd. CommandType = ct;
Cmd. Parameters. AddRange (paras );
Using (sdr = cmd. ExecuteReader (CommandBehavior. CloseConnection ))
{
Dt. Load (sdr );
}
Return dt;
}
/// <Summary>
/// Execute SQL statements or stored procedures without Parameters
/// </Summary>
/// <Param name = "plain text"> query SQL statements or stored procedures </param>
/// <Param name = "ct"> command type </param>
/// <Returns> </returns>
Public DataTable ExecuteQuery (string plain text, CommandType ct)
{
DataTable dt = new DataTable ();
Cmd = new SqlCommand (plain text, GetCon ());
Cmd. CommandType = ct;
Using (sdr = cmd. ExecuteReader (CommandBehavior. CloseConnection ))
{
Dt. Load (sdr );
}
Return dt;
}
}
The following two methods are called:
[Csharp]
Public int logincheck (string admin, string pwd)
{
 
SqlParameter [] paras = new SqlParameter [] {
New SqlParameter ("@ AdminName", admin ),
New SqlParameter ("@ Password", pwd)
};
Int I = Convert. ToInt32 (sqlhelper. ExecuteQuery ("Admin_check_login", paras, CommandType. StoredProcedure). Rows [0] [0]. ToString ());
Return I;
}
 
The sqlhelper class is not used for direct interaction with the database.
[Html]
DataRow dr;
String SQL = "SELECT PKID, User_Name, Password, User_Grade, (SELECT UserGrade FROM UserGrade WHERE pkid = user_grade) AS UG FROM Users WHERE (User_Name = @ UserName) AND (Password = @ Password )";
 
SqlConnection sqlConnection = new SqlConnection (ConfigurationManager. ConnectionStrings ["DBConnection"]. ConnectionString );
SqlDataAdapter sqlAdapter1 = new SqlDataAdapter (SQL, sqlConnection );
SqlAdapter1.SelectCommand. Parameters. Add (new SqlParameter ("@ UserName", SqlDbType. NVarChar ));
SqlAdapter1.SelectCommand. Parameters. Add (new SqlParameter ("@ Password", SqlDbType. NVarChar ));
SqlAdapter1.SelectCommand. Parameters ["@ UserName"]. Value = tbName. Text. Trim ();
SqlAdapter1.SelectCommand. Parameters ["@ Password"]. Value = tbPwd. Text. Trim ();
 
DataSet product = new DataSet ();
SqlAdapter1.Fill (product, "Users ");
Dr = product. Tables [0]. Rows [0];