Please advise as to choose between SqlCommand and SqlDataAdapter for SQL Server INSERT, UPDATE, and DELETE operations.
Let's consider the following code snippet for SQL Server:
public int Execute(SqlCommand commandSql)
or
public int Insert(SqlCommand commandSql)
Considering the obvious advantage of SqlCommand (Execute method) is that it can be used for all INSERT, UPDATE, and DELETE operations and the advantage of SqlDataAdapter (Insert, Update, Delete methods) is disconnected architecture, what are the pros and cons of using SqlCommand ExecuteNonQuery vis-à-vis SqlDataAdapter ExecuteNonQuery?
Caveats for enterprise-class web applications? Please let experts clarify.
Please note that this question is not about LINQ or Entity Framework, this is about SqlCommand ExecuteNonQuery vis-à-vis SqlDataAdapter ExecuteNonQuery?
Important question: does SqlCommand ExecuteNonQuery use a connected or disconnected architecture?
//Insert
private bool Insert(string firstName, string lastName, string synonym)
{
bool isInserted = false;
try
{
int rowsAffected = 0;
StringBuilder insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO Customer ");
insertQuery.Append("(");
insertQuery.Append("FirstName, ");
insertQuery.Append("LastName, ");
insertQuery.Append("Synonym ");
insertQuery.Append(") ");
insertQuery.Append("VALUES ");
insertQuery.Append("(");
insertQuery.Append("@FirstName, ");
insertQuery.Append("@LastName, ");
insertQuery.Append("@Synonym ");
insertQuery.Append(");");
using (SqlCommand commandSql = new SqlCommand(insertQuery.ToString()))
{
commandSql.Parameters.AddWithValue("@FirstName", firstName);
commandSql.Parameters.AddWithValue("@LastName", lastName);
commandSql.Parameters.AddWithValue("@Synonym", synonym);
DataAccessLayer dal = new DataAccessLayer();
rowsAffected = dal.Execute(commandSql); // SqlCommand ????
rowsAffected = dal.Insert(commandSql); // SqlDataAdapter ???
}
if (rowsAffected > 0)
{
isInserted = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return isInserted;
}
//UPDATE
private bool Update(int custId, string firstName, string lastName, string synonym)
{
bool isUpdated = false;
try
{
int rowsAffected = 0;
StringBuilder updateQuery = new StringBuilder();
updateQuery.Append("UPDATE Customer ");
updateQuery.Append("SET ");
updateQuery.Append("FirstName = @FirstName, ");
updateQuery.Append("LastName = @LastName, ");
updateQuery.Append("Synonym = @Synonym ");
updateQuery.Append("WHERE ");
updateQuery.Append("CustomerId = @CustomerId;");
using (SqlCommand commandSql = new SqlCommand(updateQuery.ToString()))
{
commandSql.Parameters.AddWithValue("@CustomerId", custId);
commandSql.Parameters.AddWithValue("@FirstName", firstName);
commandSql.Parameters.AddWithValue("@LastName", lastName);
commandSql.Parameters.AddWithValue("@Synonym", synonym);
DataAccessLayer dal = new DataAccessLayer();
rowsAffected = dal.Execute(commandSql); // SqlCommand ????
rowsAffected = dal.Update(commandSql); // SqlDataAdapter ???
}
if (rowsAffected > 0)
{
isUpdated = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return isUpdated;
}
//DELETE
private bool Delete(int custId)
{
bool isDeleted = false;
try
{
int rowsAffected = 0;
StringBuilder deleteQuery = new StringBuilder();
deleteQuery.Append("DELETE FROM Customer ");
deleteQuery.Append("WHERE ");
deleteQuery.Append("CustomerId = @CustomerId;");
using (SqlCommand commandSql = new SqlCommand(deleteQuery.ToString()))
{
commandSql.Parameters.AddWithValue("@CustomerId", custId);
DataAccessLayer dal = new DataAccessLayer();
rowsAffected = dal.Delete(commandSql);
rowsAffected = dal.Execute(commandSql); // SqlCommand ????
rowsAffected = dal.Delete(commandSql); // SqlDataAdapter ???
}
if (rowsAffected > 0)
{
isDeleted = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return isDeleted;
}
// SqlCommand
public int Execute(SqlCommand commandSql)
{
int rowsAffected = 0;
using (SqlConnection connectionSql = new SqlConnection("ConnectionString"))
{
commandSql.Connection = connectionSql;
commandSql.Connection.Open();
rowsAffected = commandSql.ExecuteNonQuery();
}
return rowsAffected;
}
// SqlDataAdapter
public int Insert(SqlCommand commandSql)
{
int rowsAffected = 0;
using (SqlConnection connectionSql = new SqlConnection("ConnectionString"))
{
using (SqlDataAdapter dataAdapterSql = new SqlDataAdapter())
{
dataAdapterSql.InsertCommand = commandSql;
dataAdapterSql.InsertCommand.Connection = connectionSql;
dataAdapterSql.InsertCommand.Connection.Open();
rowsAffected = dataAdapterSql.InsertCommand.ExecuteNonQuery();
}
}
return rowsAffected;
}
// SqlDataAdapter
public int Update(SqlCommand commandSql)
{
int rowsAffected = 0;
using (SqlConnection connectionSql = new SqlConnection("ConnectionString"))
{
using (SqlDataAdapter dataAdapterSql = new SqlDataAdapter())
{
dataAdapterSql.UpdateCommand = commandSql;
dataAdapterSql.UpdateCommand.Connection = connectionSql;
dataAdapterSql.UpdateCommand.Connection.Open();
rowsAffected = dataAdapterSql.UpdateCommand.ExecuteNonQuery();
}
}
return rowsAffected;
}
// SqlDataAdapter
public int Delete(SqlCommand commandSql)
{
int rowsAffected = 0;
using (SqlConnection connectionSql = new SqlConnection("ConnectionString"))
{
using (SqlDataAdapter dataAdapterSql = new SqlDataAdapter())
{
dataAdapterSql.DeleteCommand = commandSql;
dataAdapterSql.DeleteCommand.Connection = connectionSql;
dataAdapterSql.DeleteCommand.Connection.Open();
rowsAffected = dataAdapterSql.DeleteCommand.ExecuteNonQuery();
}
}
return rowsAffected;
}
Please help.
Thanks
