ExecuteNonQuery
ExecuteNonQuery() is used for executing statements that do not return result sets (ie. statements like insert data , update data etc.)
public bool SetSQL(string connectionString, string commandString)
{
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlComm = new SqlCommand(commandString, sqlConn))
{
sqlConn.Open();
sqlComm.ExecuteNonQuery();
}
}
return true;
}
catch
{
return false;
}
}
ExecuteScalar
ExecuteScalar() is using for retrieve a single value from Database. It executes SQL statements & Stored Procedure and returned a scalar value on first column of first row in the returned Result Set.
public string GetSQLSingle(string connectionString, string commandString)
{
string strReturn = "";
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlComm = new SqlCommand(commandString, sqlConn))
{
sqlConn.Open();
strReturn = sqlComm.ExecuteScalar().ToString();
}
}
return strReturn;
}
catch
{
return strReturn;
}
}
ExecuteReader
ExecuteReader() instantiates a SqlClient.SqlDataReader Object.
public string GetSQL(string connectionString, string commandString)
{
string strColumn1 = "", strColumn2 = "";
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlComm = new SqlCommand(commandString, sqlConn))
{
sqlConn.Open();
SqlDataReader sqlReader = sqlComm.ExecuteReader();
while (sqlReader.Read())
{
strColumn1 = sqlReader["Column1"].ToString();
strColumn2 = sqlReader["Column2"].ToString();
}
sqlReader.Close();
}
}
return strColumn1 + "," + strColumn2;
}
catch
{
return "";
}
}
SqlDataAdapter DataSet
Use SqlDataAdapter to fill a DataSet (data in a table style)
public DataSet GetSQLDataSet(string connectionString, string commandString)
{
DataSet dataSet = new DataSet();
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlComm = new SqlCommand(commandString, sqlConn))
{
sqlConn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(dataSet);
}
}
}
return dataSet;
}
catch
{
return dataSet;
}
}
SqlDataAdapter DataTable
Use SqlDataAdapter to fill a DataTable (data in a table style) – TAKE LESS MEMORY
public DataTable GetSQLDataTable(string connectionString, string commandString)
{
DataTable dataTable = new DataTable();
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlComm = new SqlCommand(commandString, sqlConn))
{
sqlConn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(sqlComm))
{
da.Fill(dataTable);
}
}
}
return dataTable;
}
catch
{
return dataTable;
}
}