About the. NET C # SQL database SqlHelper class instance code

Using System;
Using System.Collections.Generic;
Using System.Text;
Using System.Collections;
Using System.Data.SqlClient;
Using System.Data;
Using System.Configuration;

public class SQLHelper
{
Get database connection configuration in Web. config
public static readonly String connectstring = configurationmanager.connectionstrings["dbstring"]. ConnectionString;

<summary>
No transaction, data query
</summary>
<param name= "Cmdtype" > Stored procedures or SQL statements </param>
<param name= "cmdtext" > stored procedure name or SQL statement contents </param>
<param name= "commandparams" > Parameter list </param>
<returns></returns>
public static int ExecuteNonQuery (CommandType cmdtype, String cmdtext, params sqlparameter[] commandparams)
{
SqlCommand cmd = new SqlCommand ();

SqlConnection conn = new SqlConnection (connectstring);

Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparams);
int val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
return Val;
}
Catch
{
Throw
}
Finally
{
Conn. Close ();

}

}

<summary>
There are transactions, data manipulation classes
</summary>
<param name= "Trans" > Affairs </param>
<param name= "Cmdtype" > Operation category (Stored procedure,sql) </param>
<param name= "cmdtext" > stored procedure name or SQL statement </param>
<param name= "Commandparams" > Parameters </param>
<returns> returns the number of data rows affected </returns>
public static int ExecuteNonQuery (SqlTransaction trans, CommandType Cmdtype, String cmdtext, params sqlparameter[] Comman Dparams)
{

SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, trans. Connection, trans, Cmdtype, Cmdtext, commandparams);
/*if (Cmdtype = = CommandType.StoredProcedure)
{
Cmd. Parameters.Add ("@RETURN_VALUE", ""). Direction = ParameterDirection.ReturnValue;
Cmd. ExecuteNonQuery ();

val = (int) cmd. parameters["@RETURN_VALUE"]. Value;
}
Else
if (Cmdtype==commandtype.text)
{
val = cmd. ExecuteNonQuery ();
}*/
int val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();

return Val;
}

<summary>
Return Data Set DataReader
</summary>
<param name= "Cmdtype" ></param>
<param name= "Cmdtext" ></param>
<param name= "Commandparams" ></param>
<returns></returns>
public static SqlDataReader ExecuteReader (CommandType cmdtype, String cmdtext, params sqlparameter[] commandparams)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (connectstring);

Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparams);
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
return RDR;
}
catch (Exception ex)
{
Conn. Close ();
//throw new Exception ("The operation failed! ");
throw new Exception (ex. Message);
}
}

<summary>
Fetch data with transaction
</summary>
<param name= "Trans" ></param>
<param name= "Cmdtype" ></param>
<param name= "Cmdtext" ></param>
<param name= "Commandparams" ></param>
<returns></returns>
public static SqlDataReader ExecuteReader (SqlTransaction trans, CommandType Cmdtype, String cmdtext, params SqlParameter [] commandparams)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, trans. Connection, trans, Cmdtype, Cmdtext, commandparams);
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
return RDR;
}

public static Object ExecuteScalar (CommandType cmdtype, String cmdtext, params sqlparameter[] commandparams)
{
SqlCommand cmd = new SqlCommand ();

SqlConnection conn = new SqlConnection (connectstring);

Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparams);
Object val = cmd. ExecuteScalar ();
Cmd. Parameters.clear ();
return Val;
}
Catch
{
Throw
}
Finally
{
Conn. Close ();
}

}

public static Object ExecuteScalar (SqlTransaction trans, CommandType Cmdtype, String cmdtext, params sqlparameter[] Comma Ndparams)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, trans. Connection, trans, Cmdtype, Cmdtext, commandparams);
Object val = cmd. ExecuteScalar ();
Cmd. Parameters.clear ();
return Val;

}

<summary>
Obtaining a table from a SQL statement
</summary>
<param name= "Cmdtype" ></param>
<param name= "Cmdtext" ></param>
<param name= "Commandparams" ></param>
<returns></returns>
public static DataTable executetable (CommandType cmdtype, String cmdtext, params sqlparameter[] commandparams)
{
DataTable temptable = new DataTable ();

SqlCommand cmd = new SqlCommand ();

SqlConnection conn = new SqlConnection (connectstring);

Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparams);

SqlDataAdapter da = new SqlDataAdapter (cmd);

SqlCommandBuilder SCB = new SqlCommandBuilder (DA);

Da. Fill (temptable);
}
Finally
{
Conn. Close ();
}

return temptable;
}

public static DataTable executetable (SqlTransaction trans, CommandType Cmdtype, String cmdtext, params sqlparameter[] Com Mandparams)
{
DataTable temptable = new DataTable ();
SqlCommand cmd = new SqlCommand ();

PrepareCommand (cmd, trans. Connection, trans, Cmdtype, Cmdtext, commandparams);
SqlDataAdapter da = new SqlDataAdapter (cmd);
SqlCommandBuilder SCB = new SqlCommandBuilder (DA);
Da. Fill (temptable);
Cmd. Parameters.clear ();

return temptable;

}

<summary>
Get data based on SQL statements or stored procedures
</summary>
<param name= "Cmdtype" ></param>
<param name= "Cmdtext" ></param>
<param name= "Commandparams" ></param>
<returns></returns>
public static DataSet ExecuteDataset (CommandType cmdtype, String cmdtext, params sqlparameter[] commandparams)
{
SqlConnection conn = new SqlConnection (connectstring);

SqlCommand cmd = new SqlCommand ();

DataSet Tempdataset = new DataSet ();

Try
{
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparams);
SqlDataAdapter sqladapter = new SqlDataAdapter (cmd);
Sqladapter.fill (Tempdataset);
Cmd. Parameters.clear ();
return tempdataset;

}
Finally
{
Conn. Close ();
}

}

public static DataSet ExecuteDataset (SqlTransaction trans, CommandType Cmdtype, String cmdtext, params Sqlparamet Er[] commandparams)
{
SqlCommand cmd = new SqlCommand ();
DataSet Tempdataset = new DataSet ();
PrepareCommand (cmd, trans. Connection, trans, Cmdtype, Cmdtext, commandparams);
SqlDataAdapter sqladapter = new SqlDataAdapter (cmd);
Sqladapter.fill (Tempdataset);
Cmd. Parameters.clear ();
return tempdataset;
}

<summary>
Generate SQL statements or prepare
</summary>
<param name= "cmd" ></param>
<param name= "Conn" ></param>
<param name= "Trans" ></param>
<param name= "Cmdtype" ></param>
<param name= "Cmdtext" ></param>
<param name= "Cmdparms" ></param>
private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction Trans, CommandType cmdtype, string C Mdtext, sqlparameter[] cmdparms)
{

IF (Conn. State = ConnectionState.Open)
Conn. Open ();

Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;

if (trans! = null)
Cmd. Transaction = trans;

Cmd.commandtype = Cmdtype;

if (cmdparms! = null)
{
foreach (SqlParameter parm in cmdparms)
Cmd. Parameters.Add (Parm);
}
}

public static object Todbvalue (object value)
{
return value = = null? DBNull.Value:value;
}

public static object Fromdbvalue (Object Dbvalue)
{
return Dbvalue = = DBNull.Value? Null:dbvalue;
}
}