SQL Delete Query - How to ?

Class library usage, coding and language questions.
Zee357
Posts: 8
Joined: Tue Jan 05, 2016 7:43 pm

SQL Delete Query - How to ?

Post by Zee357 » Thu Feb 18, 2016 5:55 pm

How do I fire a SQL delete query to clear out records created by my web app? Is this a simple API call, or does it involve a SQL Data Connector to be setup?

All the steps / actions needed here.

Thanks.

Zee

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: SQL Delete Query - How to ?

Post by krstcs » Thu Feb 18, 2016 8:06 pm

First, you do not need (nor should you use) a Ranorex Data Connector to delete data from a SQL database. You should use a user code module.

Second, this depends on your setup and whether you have access to the appropriate database, scheme, tables, etc.

Third, no one can give you all the code you need because your setup is different than anyone else's, but I'll give you some generic code that should help.

Assuming you are using a SQL Server database (any other DB, you will need to get help from your DBA):

1. You will need to at System.Data and System.Data.SqlClient to your using directives (System.Data should already be in the project's References section).
2. You will need to setup the SqlConnection using a connection string (get this from your DBA).
3. You will need to setup the SqlCommand using the SqlConnection object from above as well as the command you want to sent.

Code: Select all

using System.Data;
using System.Data.SqlClient;
...

SqlConnection sConn = new SqlConnection();
sConn.ConnectionString = "Server={server name};Database={database name};User Id={username};Password={password};Connection Timeout={timeout};";  //replace {...} with appropriate info
sConn.Open();

SqlCommand sComm = new SqlCommand("DELETE FROM myTable WHERE ID = {X}", sConn);  // or you can use "EXEC myStoredProcedure" instead of the "DELETE ..." command

sComm.ExecuteNonQuery();  // or int result = sComm.ExecuteScalar(); if you return a value from a stored procedure

sConn.Close();  // always close your connection when done...
My suggestion is that you don't add raw SQL in your Ranorex code, but instead use a Stored Procedure in SQL Server that does the work, and then you call the SP in your Ranorex code. This will allow you to just manage the SQL code in your SP instead of in the source of your system, meaning you don't have to recompile every time you need to make changes to your SQL statements.
Shortcuts usually aren't...