Delete a row in the database

[Tony Johansson]

Hello!

Here I have a working program that delete the specified row in the database.
It works fine but I want to fully understand it.
In this example the SQLCommandBuilder will automatically create Sqlcommands
for
update, insert and delete.

In this program I specify that I want to delete the row with primary key =
"ZACHI"
So I had hoped that this string would be included in the command
deleteCommand.CommandText.

When I check this command just before the update command the string ZACHI is
not included at all
in the deleteCommand.

So how can this work if not the string ZACHI is included in the
deleteCommand ?

//Tony



static void Main(string[] args)
{
//Specify SQL Server-specific connection string

using ( SqlConnection thisConnection = new SqlConnection(
@"Server=UHT-DEMO1; Integrated Security=True;" +
"Database=northwind"))
{
//Create DataAdapter object for update and other operations
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"Select CustomerID, CompanyName from Customers",
thisConnection);

//Create CommandBuilder object to build SQL commands
SqlCommandBuilder thisBuilder = new
SqlCommandBuilder(thisAdapter);

//Create DataSet to contain related data tables, rows, and
columns
DataSet thisDataSet = new DataSet();

//Fill DataSet using query previously defined for
DataAdapter
thisAdapter.Fill(thisDataSet, "Customers");

Console.WriteLine("# rows before change: {0}",
thisDataSet.Tables["Customers"].Rows.Count);

//Set up keys object for defining primary key
DataColumn[] keys = new DataColumn[1];
keys[0] =
thisDataSet.Tables["Customers"].Columns["CustomerID"];
thisDataSet.Tables["Customers"].PrimaryKey = keys;

DataRow findRow =
thisDataSet.Tables["Customers"].Rows.Find("ZACHI");

if (findRow != null)
{
Console.WriteLine("ZACHI already in Customers table");
Console.WriteLine("Removing ZACHI . . .");

findRow.Delete();

SqlCommand deleteCommand =
thisBuilder.GetDeleteCommand();
Console.WriteLine("SQL DELETE comamnd is = \n{0}\n",
deleteCommand.CommandText);

thisAdapter.Update(thisDataSet, "Customers");
}

Console.WriteLine("# rows after change: {0}",
thisDataSet.Tables["Customers"].Rows.Count);
}

Console.WriteLine("Program finished, press Enter/Return to
continue");
Console.ReadLine();
}

 

[Ken Foskey]

In this program I specify that I want to delete the row with primary key
= "ZACHI"
So I had hoped that this string would be included in the command
deleteCommand.CommandText.

The delete command has parameters:

delete from table where key = ?

The ? is the parameter and this is associated to a variable that contains
the key, in your case ZACHI.

Ken

 

[Tony Johansson]

Hello!

Is it possible to see the complete command after the parameter has been
inserted into the deleteCommand in any way ?

//Tony

 

[Tony Johansson]

Hello!

I just wonder when will the parameter be replaced with the string ZACHI ?
Is it within the update method perhaps which will explain why I can't see it
when I look at the deleteCommand before
the update method.

 

[Ken Foskey]

Hello!

Is it possible to see the complete command after the parameter has been
inserted into the deleteCommand in any way ?

No.

Here is a simple SQL statement:

"DELETE FROM `mytable` WHERE `ID` = ?";

The delete command is handed to a prepare and the values are associated
later. I do not know the specifics of how .NET does the association
sorry.

You can build and execute your own SQL commands if you want or need to.

Ken

 

[Arne Vajhøj]

Here is a simple SQL statement:

"DELETE FROM `mytable` WHERE `ID` = ?";

The `` are MySQL specific.

Arne