. NET in DBHelper (SQL Server Edition)

Most of the applications need to be supported by the database interaction, then the relative database access is very important, the most basic is through the way of ADO, there are some relative ORM framework, such as Ef,nhibernate, and so on, follow-up, the corresponding namespace corresponding to the import can be

Provide a connection string or app. config in Web. config

<connectionStrings>
<!--Oracle Connection string--
<add name= "Oracleconn" connectionstring= "Data source= (description= (address= (protocol=tcp) (HOST=127.0.0.1) (PORT =1521)) (Connect_data= (SERVICE_NAME=ORCL))); User Id=scott; Password=tiger; Unicode=true "providername=" System.Data.OracleClient "/>
<!--SQL Server connection string--
<add name= "Constrsql" connectionstring= "server=.\mssqlserver1;database= database name; uid=sa;pwd=*****;integrated Security=false; Persist Security info=false; "providername=" System.Data.Sqlclient "/>
</connectionStrings>

Configure the connection string to be placed behind <configSections>, as far as you decide

#region Several other ways to connect
static string conner = @ "Data source=.\mssqlserver1;initial catalog= database name; Persist Security info=true; User id=sa;pwd=*** ";
static string conner = configurationsettings.appsettings["Constrsql"]. ToString (); ConfigurationSettings Configuring node Reads
#endregion
static string conner = configurationmanager.connectionstrings["Constrsql"]. ConnectionString; connectionstrings configuration file read
private static SqlConnection con = new SqlConnection (Conner);

#region the old
<summary>
Perform additions and deletions and change operations
</summary>
<param name= "SQL" ></param>
<returns></returns>
public static int Exexutecommand (String sql)
{
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand (sql, con);
return CMD. ExecuteNonQuery ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}

<summary>
Perform additions and deletions and change operations
</summary>
<param name= "SQL" ></param>
<returns></returns>
public static int Exexutecommand (String sql, params sqlparameter[] para)
{
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand (sql, con);
To add a parameter to the parameter collection
Cmd. Parameters.addrange (para);
return CMD. ExecuteNonQuery ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}

<summary>
Returns the Query method for DataReader
</summary>
<param name= "SQL" ></param>
<returns></returns>
public static SqlDataReader Getreader (String sql)
{
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand (sql, con);
SqlDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);
return reader;
}
catch (Exception ex)
{
Throw ex;
}
}

//<summary>
//Return DataReader Query method
//</summary>
//<param name= "SQL" ></param
//<returns></returns>
public static SqlDataReader Getreader (String sql, params sqlparameter[ ] para
{
try
{
//if (con). state = = connectionstate.closed)
//{
Con. Open ();
//}
SqlCommand cmd = new SqlCommand (sql, con);
Cmd. Parameters.addrange (para);
SqlDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);
Return reader;
}
catch (Exception ex)
{
throw ex;
}
}

//<summary>
//Return Query method for DataTable
//</summary>
//<param name= "SQL" ></param
//<returns></returns>
public static DataTable getdatetable (String sql)
{
Try
{
Con. Open ();
SqlDataAdapter myadapter = new SqlDataAdapter (sql, con);
DataSet ds = new DataSet ();
Myadapter.fill (DS);
Return DS. Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con. Close ();
}
}

///<summary>
//Query method to return to DataTable
//</summary>
//<param name= "SQL" ></ Param>
//<returns></returns>
public static DataTable getdatetable (String sql, params Sqlparameter[] para)
{
Try
{
Con. Open ();
//sqldataadapter myadapter = new SqlDataAdapter (sql, con);
SqlCommand cmd = new SqlCommand (sql, con);
Cmd. Parameters.addrange (para);
SqlDataAdapter myadapter = new SqlDataAdapter (cmd);
DataSet ds = new DataSet ();
Myadapter.fill (DS);
Return DS. Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con. Close ();
}
}

<summary>
Query method that returns a single value
</summary>
<param name= "SQL" ></param>
<returns></returns>
public static Object Getscalar (String sql)
{
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand (sql, con);
return CMD. ExecuteScalar ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}

<summary>
Query method that returns a single value (query statement with parameters)
</summary>
<param name= "SQL" ></param>
<returns></returns>
public static Object Getscalar (String sql, params sqlparameter[] para)
{
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand (sql, con);
Cmd. Parameters.addrange (para);
return CMD. ExecuteScalar ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}

#region Stored Procedure Call method
//<summary>
//Invoke the parameter stored procedure to perform additions or deletions
//</summary>
//<param name = "name" ></PARAM>
//<param Name= "values" ></PARAM>
//<returns></returns>
public static int exectuecommandstoredprocedure (string name, params sqlparameter[] values)
{
// SqlConnection conn = new SqlConnection (connection);

Try
{
//connection. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
Comm. Parameters.addrange (values);
Return Comm. ExecuteNonQuery ();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con. Close ();
}
}
//<summary>
//method of calling a stored procedure without parameters
//</summary>
//<param name= "name" ></ Param>
//<returns></returns>
public static int exectuecommandstoredprocedure (string name)
{

Try
{
//connection. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
//comm. Parameters.addrange (values);
Return Comm. ExecuteNonQuery ();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con. Close ();
}
}
//<summary>
///return method for calling stored procedures with DataTable type
//</summary>
//<param name= "name "></PARAM>
//<returns></returns>
public static DataTable Gettablebystoredprocedure ( String name)
{

//sqlconnection conn = new SqlConnection (connection. ConnectionString);
Try
{
if (con. state = = connectionstate.closed)
{
Con. Open ();
}
DataSet ds = new DataSet ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
SqlDataAdapter da = new SqlDataAdapter (comm);
DS. Clear ();
da. Fill (DS);
Return DS. Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con. Close ();
}
}
//<summary>
///return method for calling a DataTable-type stored procedure (with parameters)
//</summary>
//<param name= " Name "></PARAM>
//<returns></returns>
public static DataTable Gettablebystoredprocedure (string name, params sqlparameter[] valuse)
{
//sqlconnection conn = new SqlConnection ( Connection. ConnectionString);
Try
{
if (con. state = = connectionstate.closed)
{
Con. Open ();

}
Conn. Open ();
DataSet ds = new DataSet ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
Comm. Parameters.addrange (Valuse);
SqlDataAdapter da = new SqlDataAdapter (comm);
Da. Fill (DS);
Return DS. Tables[0];
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}
<summary>
Returns the method of a reader-type, non-parametric call to a stored procedure
</summary>
<param name= "Name" ></param>
<returns></returns>
public static SqlDataReader Getreaderbystoredprocedure (string name)
{
Try
{

SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;

SqlDataReader reader = comm. ExecuteReader (commandbehavior.closeconnection);
Return reader;
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
///Return reader-type (with parameters) method of calling stored procedure
//</summary>
//<param name= "name" & Gt;</param>
//<param Name= "values" ></PARAM>
///<returns></returns>
public static SqlDataReader Getreaderbystoredprocedure (string name, params sqlparameter[] values)
{
Try
{
Con. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
Comm. Parameters.addrange (values);

SqlDataReader reader = comm. ExecuteReader (commandbehavior.closeconnection);
Return reader;
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
//Return single value type (no parameter)
//</summary>
//<param name= "name" ></param& Gt
//<returns></returns>
public static Object getscalarbystoredprocedure (string name)
{
Try
{
Con. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;

Return COMM. ExecuteScalar ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}
<summary>
Return single value type (with parameters)
</summary>
<param name= "Name" ></param>
<returns></returns>
public static Object Getscalarbystoredprocedure (string name, params sqlparameter[] values)
{
Try
{
Con. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = con;
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = name;
Comm. Parameters.addrange (values);

Return COMM. ExecuteScalar ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Con. Close ();
}
}
#endregion
#endregion

. NET in DBHelper (SQL Server Edition)