Which one is a better [InsertCommand]...

[RP]

From the below given codes used to insert a record in a table, which
code is well-optimized and must be used. Please also let me know why
it is better.

=======[ CODE 1 ]===========================================
public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (Exception ex)
{
MessageBox.Show("Routine: ModReUsable-
InsertNewRecord(" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}
}
========[ CODE 2 ]==================================================
public static OleDbDataAdapter CreateCustomerAdapter(
OleDbConnection connection)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command;

// Create the SelectCommand.
command = new OleDbCommand("SELECT CustomerID FROM Customers " +
"WHERE Country = ? AND City = ?", connection);

command.Parameters.Add("Country", OleDbType.VarChar, 15);
command.Parameters.Add("City", OleDbType.VarChar, 15);

adapter.SelectCommand = command;

// Create the InsertCommand.
command = new OleDbCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (?, ?)", connection);

command.Parameters.Add(
"CustomerID", OleDbType.Char, 5, "CustomerID");
command.Parameters.Add(
"CompanyName", OleDbType.VarChar, 40, "CompanyName");

adapter.InsertCommand = command;
return adapter;
}
=====================================================================================================

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

code is well-optimized and must be used. Please also let me know why
it is better.

Neither.

For only the purpose of inserting a record, just take out the code that
creates the insert command from the second piece of code, and skip the
data adapter entirely. Use the ExecuteNonQuery method to run it, just
like in the first piece of code.

Reasons:

There is no need for a data adapter to insert a record. You only need
the command.

You should always use parameters with your command:

- Putting the values directly in the query is difficult without exposing
your code to the risk of SQL injection.

- The database can cache the execution plan for parameterised queries,
as the query doesn't change when the values change. (Not really
important for just an insert, but relevant for more complex queries.)

 

[Cor Ligthert[MVP]]

RP,

This question sounds for me a little bit the same as asking "what is better
a plane or a car".

Beside that both codes are not quiet as good as it can be for several
reasons. Are you using C# 2003 by the way?

Cor

From the below given codes used to insert a record in a table, which

code is well-optimized and must be used. Please also let me know why
it is better.

=======[ CODE 1 ]===========================================
public Int32 InsertNewRecord(string myQuery)
{
objModCon.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(myQuery,
objModCon.myCN);
try
{
Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
return RecordsAffected;
}
catch (Exception ex)
{
MessageBox.Show("Routine: ModReUsable-
InsertNewRecord(" + myQuery + ") " + ex.ToString(), "Error:",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return 0;
}
finally
{
cmdInsert.Dispose();
objModCon.CloseConnection();
}
}
========[ CODE 2 ]==================================================
public static OleDbDataAdapter CreateCustomerAdapter(
OleDbConnection connection)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command;

// Create the SelectCommand.
command = new OleDbCommand("SELECT CustomerID FROM Customers " +
"WHERE Country = ? AND City = ?", connection);

command.Parameters.Add("Country", OleDbType.VarChar, 15);
command.Parameters.Add("City", OleDbType.VarChar, 15);

adapter.SelectCommand = command;

// Create the InsertCommand.
command = new OleDbCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (?, ?)", connection);

command.Parameters.Add(
"CustomerID", OleDbType.Char, 5, "CustomerID");
command.Parameters.Add(
"CompanyName", OleDbType.VarChar, 40, "CompanyName");

adapter.InsertCommand = command;
return adapter;
}
=====================================================================================================