in this article, we will go through the Ado.net data access utility class for SQL server
Executenonquery helper class in Ado.net
In Ado.Net ExecuteNonQuery can return the number of rows affected and return type is int. the given below static SqlExecuteNonQuery() function can accept the Queries only (Create, Alter, Drop, Insert, Update, Delete)
public static void SqlExecuteNonQuery(string sqlQry)//, SqlConnection sqlCon)
{
try
{
using (sqlCon = new SqlConnection(conString))
{
sqlCon.Open();
using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
catch { throw; }
}
Execute Scalar helper class in Ado.net
Execute Scalar() Function will work for non-action queries and it contains aggregate functions. it will return the first row and first column value of the query result
public static void SqlExecuteNonQuery(string sqlQry,SqlTransaction trans)
{
try
{
using (sqlCon = new SqlConnection(conString))
{
sqlCon.Open();
using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
catch { throw; }
}
Return DataTable in Ado.Net
the given below static SqlExecuteDataTable() function will return the data table and it is depending on passing queries
public static DataTable SqlExecuteDataTable(string sqlQry)//, SqlConnection sqlCon)
{
try
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
// string conString = ConfigurationManager.ConnectionStrings["SqlConString"].ToString();
using (sqlCon = new SqlConnection(conString))
{
if (sqlCon.State != ConnectionState.Open)
{
sqlCon.Open();
}
using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter da = new SqlDataAdapter(sqlQry, sqlCon))
{
da.Fill(ds);
dt = ds.Tables[0];
sqlCon.Close();
}
//return cmd.ExecuteScalar();
//sqlcon.Close();
}
}
return dt;
}
catch {
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
throw; }
finally
{
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
}
}
Append Where Clause
the static AppendWhereClause() function will the appending the where clause to passing query
public static string AppendWhereClause(string strQry)
{
try
{
if (strQry.Contains(" WHERE"))
{
strQry = strQry + " " + "AND";
}
else
{
strQry = strQry + " " + " WHERE";
}
return strQry;
}
catch { throw; }
}
Return SqlDataReader object
public SqlDataReader ExecuteReader(string sql, SqlParameter[] parameters = null)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conString;
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null)
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
}
else
{
cmd.CommandType = CommandType.Text;
}
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}