Solved

ADO.NET Entity Framework - how to execute a "Delete from" SQL statement?

Posted on 2010-08-15
20
3,320 Views
Last Modified: 2013-12-17
Hi,

I am trying to work out how to execute the command "delete from <table>" using the ADO.NET entity framework.

I have attached the code below but I get the following error.

System.Data.EntitySqlException was unhandled
  Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
  Source="System.Data.Entity"
  Column=8
  ErrorContext="keyword 'FROM'"
  ErrorDescription="The query syntax is not valid."

Any ideas on how to fix this

Thanks,

Ward
mydb = new PIDEntities();

            System.Data.Common.DbConnection my_connection = mydb.Connection;

            my_connection.Open();

            System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();

            cmd.CommandText = "delete from PidTable";

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

            my_connection.Close();

Open in new window

0
Comment
Question by:whorsfall
  • 6
  • 6
  • 5
  • +1
20 Comments
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33439866
Hi,

 Delete statement is used to delete records from table and Drop is used to drop table from database.

Regards,
VSS

mydb = new PIDEntities();
            System.Data.Common.DbConnection my_connection = mydb.Connection;
            my_connection.Open();
            System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();
            cmd.CommandText = "DROP TABLE PidTable";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            my_connection.Close();

Open in new window

0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33439870
Hi,

And if you are using delete some records from table use code.

Regards,
VSS

mydb = new PIDEntities();
            System.Data.Common.DbConnection my_connection = mydb.Connection;
            my_connection.Open();
            System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();
            cmd.CommandText = "DELETE FROM PidTable WHERE name = 'Vikram'";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            my_connection.Close();

Open in new window

0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33439887
0
 

Author Comment

by:whorsfall
ID: 33440003
Hi,

Thanks I tried the example you posted - my objective is btw is to delete all the records (clear it out)  but not drop the table. So the SQL I would like to get to work is: " DELETE FROM PidTable" or "DELETE * FROM PidTable

Anyway the SQL statement is valid as far as SQL management studio goes.


System.Data.EntitySqlException was unhandled
  Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
  Source="System.Data.Entity"
  Column=8
  ErrorContext="keyword 'FROM'"
  ErrorDescription="The query syntax is not valid."
  Line=1

0
 
LVL 4

Expert Comment

by:nativ
ID: 33440237
Have you tried simply "Delete PidTable"
0
 

Author Comment

by:whorsfall
ID: 33440259
Hi,

Thanks for the suggestion "Delete PidTable" returns:

"The query syntax is not valid., near identifier 'PidTable', line 1, column 8."

Ward
0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33441147
Hi,

I have some questions to ask you:

(1) Can I have details about 1st line in code mydb = new PIDEntities(); ?

(2) You are using database that is not having that particular table PidTable. Also check case of table's name.

(3) Try your code at line 5 as:

[code]
cmd.CommandText = "DELETE *  FROM PidTable;'";
[/code]

Regards,
VSS

0
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33441151
Hi,

Remove that single quotation ( ' ) in code, that is by my mistake.

Regards,
VSS
0
 
LVL 14

Expert Comment

by:systan
ID: 33442883
This don't take a long time;

//open this when form loads *suggestion
mydb = new PIDEntities();
System.Data.Common.DbConnection my_connection = mydb.Connection;
my_connection.Open();
//open this when form loads *suggestion

System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();

//correct usage is with *, but filter it, that enable's not to delete all the records
//
cmd.CommandText = "delete * from PidTable";
//
//The DELETE FROM are only applied to other NamedSql's like MySQL,  in MsSQL you must input a value

cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

//close this when form close/unload
my_connection.Close();
//close this when form close/unload


BUT,  To tell you frankly,  you have a unpleasant code, the way you code is unprofessional
Do it in a nice neat way
Use professional standard way of coding;
http://www.experts-exchange.com/blogs/systan/B_2913-Basic-SqlCommand-using-Insert-Update-Delete.html



http://www.experts-exchange.com/blogs/systan/B_2891-using-dataadapter-as-a-source-commands.html



There are saying's,  that there are different ways to kill a chicken,  but the problem is the code is not a chicken.

Open in new window

0
 

Author Comment

by:whorsfall
ID: 33443958
Hi,

Thanks for the feedback. Ok by trail an error I was able to get it to work but hopefully somebody can explain it. So I discovered it by accident I guess plus what other were telling me here.

In my sample below I have three function 1 which does not work and the other 2 that do.

It seems if I get back a connection object of: {System.Data.SqlClient.SqlConnection} then all is well
it works.

However if I get a connection object of type: {System.Data.EntityClient.EntityConnection}
Then no matter what SQL I pass it fails. (This was the type I thought I should be using).

Anyway hopefully somebody can give me an better explination then that would be great.

Big that to all the suggestions so far that has been great.

Ward.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Entity;

using System.Data.EntityClient;

using System.Data;

using System.Data.SqlClient;



// --------------------------------------- App.Config ---------------------------------------





// <?xml version="1.0" encoding="utf-8"?>

// <configuration>  

// <connectionStrings><add name="PIDEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=localhost\SQLEXPRESS;Initial Catalog=CarDB;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

// </configuration>



// --------------------------------------- SQL Scripts to generate Table --------------------



/* SQL Scripts to generate table..

 * 

USE [CarDB]

GO



SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[PidTable](

	[PID1] [numeric](8, 4) NULL

) ON [PRIMARY]



GO

*/



/* Exception Generated from: fnDelete_Records_Entity()

 

 System.Data.EntitySqlException was unhandled

  Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."

  Source="System.Data.Entity"

  Column=8

  ErrorContext="keyword 'FROM'"

  ErrorDescription="The query syntax is not valid."

  Line=1

  StackTrace:

       at System.Data.Common.EntitySql.CqlParser.yyerror(String s)

       at System.Data.Common.EntitySql.CqlParser.yyparse()

       at System.Data.Common.EntitySql.CqlParser.Parse(String query)

       at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions)

       at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)

       at System.Data.EntityClient.EntityCommand.MakeCommandTree()

       at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()

       at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)

       at System.Data.EntityClient.EntityCommand.GetCommandDefinition()

       at System.Data.EntityClient.EntityCommand.InnerPrepare()

       at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)

       at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)

       at DBTest1.Program.fnDelete_Records_Entity() in D:\Ward\Documents\Visual Studio 2008\Projects\DBTest1\DBTest1\Program.cs:line 84

       at DBTest1.Program.Main(String[] args) in D:\Ward\Documents\Visual Studio 2008\Projects\DBTest1\DBTest1\Program.cs:line 108

       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: 



 */

namespace DBTest1

{

    class Program

    {

        // Reference: http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework



        static void ExecuteSql(System.Data.Objects.ObjectContext c, string sql)

        {

            var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;



            // Object type returned below (conn): {System.Data.SqlClient.SqlConnection}

            System.Data.Common.DbConnection conn = entityConnection.StoreConnection;



            ConnectionState initialState = conn.State;

            try

            {

                if (initialState != ConnectionState.Open)

                    conn.Open();  // open connection if not already open 

                using (System.Data.Common.DbCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    cmd.ExecuteNonQuery();

                }

            }

            finally

            {

                if (initialState != ConnectionState.Open)

                    conn.Close(); // only close connection if not initially open 

            }

        } 





        static void fnDelete_Records_Entity()

        {



            //ObjectContext context = new PIDEntities();

           

            System.Data.Objects.ObjectContext mydb;

            mydb = new PIDEntities();



            // Object Type returned below (my_connection): {System.Data.EntityClient.EntityConnection}

            System.Data.Common.DbConnection my_connection = mydb.Connection; 

           

            my_connection.Open();

            System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();

            cmd.CommandText = "DELETE FROM PidTable;";

            cmd.CommandType = CommandType.Text;            

            cmd.ExecuteNonQuery();

            my_connection.Close();

        }



        static void fnDelete_Records_ADO()

        {

            // Object type returned (myConn): {System.Data.SqlClient.SqlConnection}

            SqlConnection myConn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=CarDB;Integrated Security=True;database=CarDB");

            string str = "DELETE FROM PidTable;";

            SqlCommand myCommand = new SqlCommand(str, myConn);

            myConn.Open();

            myCommand.ExecuteNonQuery();



        }



        static void fnDelete_Test_Entity2()

        {

            System.Data.Objects.ObjectContext mydb;

            mydb = new PIDEntities();

            ExecuteSql(mydb,"DELETE FROM PidTable;");

        }



        static void Main(string[] args)

        {

           fnDelete_Records_Entity(); // Generates exception see above.

           fnDelete_Records_ADO();  // Works

           fnDelete_Test_Entity2(); // Works

        }

    }

}

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 14

Expert Comment

by:systan
ID: 33445238
Your .Net Framework is not supported.
EntityConnection Class uses Framework 4.0 and you may have a lower version installed.
0
 
LVL 14

Expert Comment

by:systan
ID: 33445376
If you have .Net framework 4 or 3.5 sp1
http://msdn.microsoft.com/en-us/library/bb738561.aspx
Open/Read the links <In This Section>
0
 

Author Comment

by:whorsfall
ID: 33446947
Hi,

I am using Visual Sudio 2008. So can I assume that these features that I am trying to use
are not available using the {System.Data.EntityClient.EntityConnection}?

Thefore I have to use: {System.Data.SqlClient.SqlConnection}

Thanks,

Ward.
0
 
LVL 4

Accepted Solution

by:
nativ earned 250 total points
ID: 33447424
The fnDelete_Records_ADO works as that is the normal way of executing a SQL query on the database. This execute the string as you would run it on in SSMS.
The issue is that you decided to use EntityClient for working with the database. I cannot claim to know much on the topic.
If you want to use EntityClientConnection then i would think you should use System.Data.EntityClient.EntityCommand and not System.Data.Common.DbCommand

0
 
LVL 4

Expert Comment

by:nativ
ID: 33447519
The EntityFramework is supported in 3.5 SP1, so that should not be your issue.
Why do you want to use the EntityFramework? You want to work with a Concept Data model?
Is that level of abstraction needed in your application?
0
 
LVL 14

Assisted Solution

by:systan
systan earned 250 total points
ID: 33450639
If you have a .Net Framework 3.5/4.0 and same area of troubleshooting error's, meaning you have a corrupted Framework on EntityFramework, but do you really need that stuff, why use a plain standard database connection, you can look at the links that I submitted then compare your code to mine.

Other aspect of your coding, why are you following m$ samples? Opening the connection inside the function while reading writing a records, I did tell you to open your connection when form loads, close connection when form unload/closed.
Here's the link again;
http://www.experts-exchange.com/blogs/systan/B_2913-Basic-SqlCommand-using-Insert-Update-Delete.html
http://www.experts-exchange.com/blogs/systan/B_2891-using-dataadapter-as-a-source-commands.html

I'm sure your .Net Framework supports that.
0
 

Author Comment

by:whorsfall
ID: 33455269
Hey guys big thanks I am going to go through this in detail so I can award multiple points.
Awsome all the help I have got so far.


I realize my code was not the "best practices"  as I like to break down the sample into its simpilist form. Then I will do things like open the connection when the form loads etc. and so on.

So big thanks while I digest...

Ward.

0
 
LVL 14

Expert Comment

by:systan
ID: 33457938
Atlast you knew it.
0
 
LVL 14

Expert Comment

by:systan
ID: 33469976
Are we finished here?
0
 

Author Closing Comment

by:whorsfall
ID: 33471530
Hi,

Big thanks for all the help - it was excellent, and thanks for your patience with me here as I wanted to get to a more root cause which I got to in the end :)


Ward.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now