-2

I'm trying to open a connection to a DB and then insert a record into a table. At the moment it's just a simple localDB, I have looked at opening the connection with the sqlclient namespace methods. 

SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;
AttachDbFilename=C:\\FILE\\PATH\\EXAMPLE\\TechMVCDB.mdf;Integrated Security=True;
Connect Timeout=30");

I'm not certain that my connection string is even correct, I got it directly from the connection string box when you click on your database in the server explorer panel. I added a breakpoint in the code after the connection was opened and a select all statement was executed : 

SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\FILE\\PATH\\EXAMPLE\\TechMVCDB.mdf;Integrated Security=True;Connect Timeout=30");
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "SELECT * FROM Table ORDER BY Id";
            SqlDataReader rdr = com.ExecuteReader();

I then get this error "System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'Table'.'" Table was just simply the name of the table was it also treating it as a keyword?

After this I changed the tablename to TechTester and ran it again, it ran with no errors and seemed to get the correct field amount of 4 id,sequence,direction,time it didn't seem to get the inserted test data.

I've also looked at using the Entity framework and implemented the very beginnings of it so I have my entity model class setup but nothing more. Is this the direction I should actually go with? How would I access the entity database?

My question is How do I best open a connection to a local db in asp.net-MVC using C#?

CandiedMango
  • 279
  • 4
  • 15
  • Whatever you use (Entity Framework or direct ADO.NET commands) you should store the connectionstring in the config file of your application. Do not hard code it inside your sources. – Steve Dec 17 '17 at 16:44
  • @Steve At the moment I'm just hardcoding whilst I test, does the connection string look correct? – CandiedMango Dec 17 '17 at 17:08
  • Always add "Database=LogicalNameForYourDB" to avoid problems with AttachDbFileName (Trying to attach a database already attached or something like that) However if you want to use EF the connectionstring is a bit different See http://connectionstrings.com – Steve Dec 17 '17 at 17:12
  • Possible duplicate of [Oracle and SQL Server reserved keywords](https://stackoverflow.com/questions/16769799/oracle-and-sql-server-reserved-keywords) – mjwills Dec 17 '17 at 20:31

1 Answers1

2

Table is one of the SQL Server reserved keywords.

Dmitry Pavlov
  • 30,789
  • 8
  • 97
  • 121