C # SQLHelper (for winForm) implementation Code _c# Tutorial

SQLHelper.cs

Copy Code code as follows:

Using System;
Using System.Collections.Generic;
Using System.Text;
Using System.Collections;
Using System.Data;
Using System.Data.SqlClient;
Using System.Configuration;
Namespace Hellowinform.dbutility
{
Class SQLHelper
{
#region General Method
Data Connection Pool
Private SqlConnection con;
<summary>
Returns the database connection string
</summary>
<returns></returns>
public static String Getsqlconnection ()
{
String conn = configurationmanager.appsettings["ConnectionString"]. ToString ();
Return conn;
}
#endregion
#region Execute SQL string
<summary>
Execute an SQL statement with no parameters
</summary>
<param name= "Sqlstr" ></param>
<returns></returns>
public static int ExecuteSQL (String sqlstr)
{
String connstr = Getsqlconnection ();
using (SqlConnection conn = new SqlConnection (CONNSTR))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = Sqlstr;
Conn. Open ();
Cmd. ExecuteNonQuery ();
Conn. Close ();
return 1;
}
}
<summary>
Execute an SQL statement with parameters
</summary>
<param name= "sqlstr" >sql statement </param>
<param name= "param" > Parameter object array </param>
<returns></returns>
public static int ExecuteSQL (String sqlstr, sqlparameter[] param)
{
String connstr = Getsqlconnection ();
using (SqlConnection conn = new SqlConnection (CONNSTR))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = Sqlstr;
Cmd. Parameters.addrange (param);
Conn. Open ();
Cmd. ExecuteNonQuery ();
Conn. Close ();
return 1;
}
}
<summary>
Back to DataReader
</summary>
<param name= "Sqlstr" ></param>
<returns></returns>
public static SqlDataReader ExecuteReader (String sqlstr)
{
String connstr = Getsqlconnection ();
SqlConnection conn = new SqlConnection (CONNSTR);//When returning DataReader, it is not possible to use a using ()
Try
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = Sqlstr;
Conn. Open ();
return CMD. ExecuteReader (System.Data.CommandBehavior.CloseConnection);//close associated connection
}
Catch//(Exception ex)
{
return null;
}
}
<summary>
Execute SQL statement and return data table
</summary>
<param name= "sqlstr" >sql statement </param>
<returns></returns>
public static DataTable Executedt (String sqlstr)
{
String connstr = Getsqlconnection ();
using (SqlConnection conn = new SqlConnection (CONNSTR))
{
SqlDataAdapter da = new SqlDataAdapter (SQLSTR, Conn);
DataTable dt = new DataTable ();
Conn. Open ();
Da. Fill (DT);
Conn. Close ();
return DT;
}
}
<summary>
Execute the SQL statement and return the dataset
</summary>
<param name= "sqlstr" >sql statement </param>
<returns></returns>
public static DataSet executeds (String sqlstr)
{
String connstr = Getsqlconnection ();
using (SqlConnection conn = new SqlConnection (CONNSTR))
{
SqlDataAdapter da = new SqlDataAdapter (SQLSTR, Conn);
DataSet ds = new DataSet ();
Conn. Open ();
Da. Fill (DS);
Conn. Close ();
return DS;
}
}
#endregion
#region Manipulating Stored Procedures
<summary>
Run stored procedure (overloaded)
</summary>
<param name= "procname" > Stored procedure name </param>
<returns> return value of stored procedure </returns>
public int Runproc (string procname)
{
SqlCommand cmd = CreateCommand (procname, NULL);
Cmd. ExecuteNonQuery ();
This. Close ();
return (int) cmd. parameters["ReturnValue"]. Value;
}
<summary>
Run stored procedure (overloaded)
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure input Parameters list </param>
<returns> return value of stored procedure </returns>
public int Runproc (string procname, sqlparameter[] prams)
{
SqlCommand cmd = CreateCommand (procname, prams);
Cmd. ExecuteNonQuery ();
This. Close ();
return (int) cmd. Parameters[0]. Value;
}
<summary>
Run stored procedure (overloaded)
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "DataReader" > Result set </param>
public void Runproc (String procname, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand (procname, NULL);
DataReader = cmd. ExecuteReader (System.Data.CommandBehavior.CloseConnection);
}
<summary>
Run stored procedure (overloaded)
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure input Parameters list </param>
<param name= "DataReader" > Result set </param>
public void Runproc (string procname, sqlparameter[] prams, out SqlDataReader DataReader)
{
SqlCommand cmd = CreateCommand (procname, prams);
DataReader = cmd. ExecuteReader (System.Data.CommandBehavior.CloseConnection);
}
<summary>
Creating command objects for accessing stored procedures
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure input Parameters list </param>
<returns>command Objects </returns>
Private SqlCommand CreateCommand (String procname, sqlparameter[] prams)
{
Make sure the connection is open
Open ();
Command = new SqlCommand (sProcName, New SqlConnection (Configmanager.dalconnectionstring));
SqlCommand cmd = new SqlCommand (procname, con);
Cmd.commandtype = CommandType.StoredProcedure;
Add a list of input parameters for a stored procedure
if (prams!= null)
{
foreach (SqlParameter parameter in prams)
Cmd. Parameters.Add (parameter);
}
Return Command object
return cmd;
}
<summary>
Create input parameters
</summary>
<param name= "paramname" > Parameter name </param>
<param name= "DbType" > Parameter type </param>
<param name= "Size" > Parameter size </param>
<param name= "value" > Parameter value </param>
<returns> New Parameter Object </returns>
Public SqlParameter Makeinparam (string paramname, SqlDbType DbType, int Size, Object Value)
{
Return Makeparam (ParamName, DbType, Size, ParameterDirection.Input, Value);
}
<summary>
Create output parameters
</summary>
<param name= "paramname" > Parameter name </param>
<param name= "DbType" > Parameter type </param>
<param name= "Size" > Parameter size </param>
<returns> New Parameter Object </returns>
Public SqlParameter Makeoutparam (string paramname, SqlDbType DbType, int Size)
{
Return Makeparam (ParamName, DbType, Size, parameterdirection.output, NULL);
}
<summary>
Creating stored Procedure Parameters
</summary>
<param name= "paramname" > Parameter name </param>
<param name= "DbType" > Parameter type </param>
<param name= "Size" > Parameter size </param>
<param name= direction of "Direction" > parameters (Input/output) </param>
<param name= "value" > Parameter value </param>
<returns> New Parameter Object </returns>
Public SqlParameter Makeparam (string paramname, SqlDbType DbType, Int32 Size, ParameterDirection Direction, Object Value)
{
SqlParameter param;
if (Size > 0)
{
param = new SqlParameter (paramname, DbType, Size);
}
Else
{
param = new SqlParameter (paramname, DbType);
}
Param. Direction = Direction;
if (!) ( Direction = = ParameterDirection.Output && Value = null))
{
Param. Value = value;
}
return param;
}
#endregion
#region Database connection and shutdown
<summary>
Open Connection Pool
</summary>
private void Open ()
{
Open Connection Pool
if (con = = null)
{
This requires not only a using system.configuration, but also a reference directory to add
con = new SqlConnection (Getsqlconnection ());
Con. Open ();
}
}
<summary>
Close Connection Pool
</summary>
public void Close ()
{
if (Con!= null)
Con. Close ();
}
<summary>
Free Connection Pool
</summary>
public void Dispose ()
{
Make sure the connection is off
if (Con!= null)
{
Con. Dispose ();
con = null;
}
}
#endregion
}
}

Simple to use:

Copy Code code as follows:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Hellowinform.dbutility;
Namespace Hellowinform.dal
{
class Student
{
public string test ()
{
String str = " ";
SqlDataReader dr = Sqlhelper.executereader ("SELECT * from Student");
while (Dr. Read ())
{
str + = dr["Studentno"]. ToString ();
}
Dr. Close ();
return str;
}
}
}