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 SQLCommandBuild er 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.C ommandText.

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=north wind"))
{
//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
SqlCommandBuild er thisBuilder = new
SqlCommandBuild er(thisAdapter) ;

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

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

Console.WriteLi ne("# rows before change: {0}",
thisDataSet.Tab les["Customers"].Rows.Count);

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

DataRow findRow =
thisDataSet.Tab les["Customers"].Rows.Find("ZAC HI");

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

findRow.Delete( );

SqlCommand deleteCommand =
thisBuilder.Get DeleteCommand() ;
Console.WriteLi ne("SQL DELETE comamnd is = \n{0}\n",
deleteCommand.C ommandText);

thisAdapter.Upd ate(thisDataSet , "Customers" );
}

Console.WriteLi ne("# rows after change: {0}",
thisDataSet.Tab les["Customers"].Rows.Count);
}

Console.WriteLi ne("Program finished, press Enter/Return to
continue");
Console.ReadLin e();
}

[Ken Foskey]

On Mon, 25 Aug 2008 11:54:56 +0200, Tony Johansson wrote:

>
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.C ommandText.

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

"Ken Foskey" <rm**********@o ptushome.com.au skrev i meddelandet
news:48******** @dnews.tpgi.com .au...

On Mon, 25 Aug 2008 11:54:56 +0200, Tony Johansson wrote:

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.C ommandText.

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!

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.
"Tony Johansson" <jo************ *****@telia.com skrev i meddelandet
news:OC******** ********@TK2MSF TNGP06.phx.gbl. ..

Hello!

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

//Tony

"Ken Foskey" <rm**********@o ptushome.com.au skrev i meddelandet
news:48******** @dnews.tpgi.com .au...

On Mon, 25 Aug 2008 11:54:56 +0200, Tony Johansson wrote:

>
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.C ommandText.

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

[Ken Foskey]

On Mon, 25 Aug 2008 12:57:21 +0200, Tony Johansson wrote:

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

[=?UTF-8?B?QXJuZSBWYWpow7hq?=]

Ken Foskey wrote:

Here is a simple SQL statement:

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

The `` are MySQL specific.

Arne