Why won't my query execute in my code, while it executes just fine in normal sql?

Question

I've run into a problem while making a project for school, we are supposed to import data from a .txt file to our C# database. I thought I had it figured out but my "insert" lines weren't inserting data to my tables. So, in the end, I tried to insert only 1 line with all values written in, and it still won't insert the data into the database.

I tried the "New query" option by right clicking on my table and copy-pasted the insert line from my code, and that worked just fine, so I don't know why the line in the code isn't working.

class Program
{
    static void Main(string[] args)
    {
        string connectionString = @"Data Source= (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|BazaPRO2.mdf;Integrated Security=True;Connect Timeout=30";
        SqlConnection dataConnection = new SqlConnection(connectionString);
        string q;
        dataConnection.Open();
        q = "INSERT INTO Sola(SolaID,Naziv,Naslov,Kraj,Posta,Telefon,Eposta) VALUES(1,'Test','Test','Test',1000,'Test','Test')";
        SqlCommand dataCommand = new SqlCommand(q, dataConnection);

        try
        {
            dataCommand.ExecuteNonQuery();
            Console.WriteLine("Success");
            dataConnection.Close();
        }
        catch { Console.WriteLine("Fail"); }
    }
}

I tried pasting the executenonquery line in a try block, and it DOES write "Success" on my screen, but the insert line does NOT execute.

@AviMeltser Either this is auto-commit mode, or no transaction is started. So I doubt this is the problem. — Fildor, May 27 '19 at 12:18
@SlickKitten: How do you confirm rows are persisted apart form "Success" output? — Fildor, May 27 '19 at 12:19
Might benefit from checking working examples and also use parameters instead of plain text - https://stackoverflow.com/questions/12939501/insert-into-c-sharp-with-sqlcommand — Avi Meltser, May 27 '19 at 12:19
@AviMeltser While that's true, first things first. No need to care about fortifying the code before it actually works at all. — Fildor, May 27 '19 at 12:20
`ExecuteNonQuery` returns the number of rows affected. Check that and if you get one then you are probably looking at the wrong database when you check to see if the record was inserted. — Crowcoder, May 27 '19 at 12:22
@SlickKitten you haven't disposed the SqlCommand object. Perhaps that's part of the problem. I'd wrap any disposable db related object in a using{} — Avi Meltser, May 27 '19 at 12:22
The insert almost certainly does execute. The way you're verifying it's happening, on the other hand, is another matter. In particular, `AttachDbFileName` may not [work like you think it does](https://blogs.sentryone.com/aaronbertrand/bad-habits-attachdbfilename/). — Jeroen Mostert, May 27 '19 at 12:26
@JeroenMostert No `User Instance=true` in this connection string. — Ian Kemp, May 27 '19 at 13:47
@IanKemp: hence my "may", because I've never actually used `AttachDbFileName` myself. I can imagine it's still easy to get it wrong when it comes to telling Management Studio/another application what database we're talking about, for purpose of checking. (I actually have no idea how `AttachDbFileName` works *at all* in the connection string; I prefer permanent databases.) — Jeroen Mostert, May 27 '19 at 13:51

Legion · Accepted Answer · 2019-05-27T15:01:05.697

2

Check the return value of dataCommand.ExecuteNonQuery(); (integer Value) if it return -1 something went wrong (for example a transaction rollback). If 0 no rows were affected.

int return_value = dataCommand.ExecuteNonQuery();
if(return_value > 0)
   //goood :)
else
   //something wrong :(

EDIT:

Btw is better dispose the commands after using them like below:

string connectionString = @"Data Source= (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|BazaPRO2.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection dataConnection = new SqlConnection(connectionString);
dataConnection.Open();
string q = "INSERT INTO Sola(SolaID,Naziv,Naslov,Kraj,Posta,Telefon,Eposta) VALUES(1,'Test','Test','Test',1000,'Test','Test')";
using(SqlCommand dataCommand = new SqlCommand(q, dataConnection))
{
    try
    {
        dataCommand.ExecuteNonQuery();
        Console.WriteLine("Success");
    }
    catch { Console.WriteLine("Fail"); }
}
dataConnection.Close();

EDIT2: Considering questions in the comments.

What are you saying writing |DataDirectory| is "search in the application path for that dasabase", if you're debugging your application it means that it search the dabase in the output debug folder... If you wont that you should target a database out of your appliation directory with a relative/absolute path (look AppDomain.SetData method) or copy your database in your application directory... is hard answer you without knowing your goal :)

To be more specific, before initialize your SqlConnection call the following code:

AppDomain.SetData("DataDirectory", "C:\\TEST\\");

To set your |DataDirectory| pointing at your database path.

edited May 27 '19 at 15:01

answered May 27 '19 at 13:33

Legion

760
6
23

`using` is still the best – AgentFire May 27 '19 at 13:45
2

You can't have a rollback without a transaction, and there isn't one here. Any error that would cause an implicit rollback for this single statement also results in an `SqlException`, which means you'd never get to this check. This block of code would really only execute if the `INSERT` was eaten by an `INSTEAD OF` trigger that affected no rows (and then, arguably, that would be by design). To sum up: for a single `INSERT` it makes no real sense to check the result of `ExecuteNonQuery` (which is good, as people would forget that all the time). – Jeroen Mostert May 27 '19 at 13:49
Thanks for the explanation :) But the msdn comment in the answer is clear _If no statements are detected that contribute to the count, the return value is -1. If a rollback occurs, the return value is also -1._ Even a trigger can generate an insert rollback, we cannot know his database structure, so test the returnvalue of an insert i think is still a good idea :) (btw i'm not sure if a trigger error generate an exception or a -1... i should try) – Legion May 27 '19 at 13:54
1

It's possible for a trigger to "silently" roll back the statement without also doing a `RAISERROR` to signal to the client that the statement was rolled back, and if you're counting on a trigger like that checking the value may make sense. It'd arguably make a lot more sense to make the trigger produce an error, though, as you have no way of knowing *why* it rolled back otherwise. I wouldn't use this as a reason to insert checks like this everywhere, especially since they won't work if `SET NOCOUNT ON` is used (which is common). It won't hurt in this case, but it probably won't help either. – Jeroen Mostert May 27 '19 at 14:04
@Legion So i've tried checking the int value, and it comes out as 1, then i put the dataCommand in an using block as you said, still nothing inserted in my database. The weird thing i tried now is putting an absolute path to my database, so the whole path except of just BazaPRO2.mdf, and when i did that, the insert line actually worked. The problem is if i leave the absolute path in, the line won't work when i hand over my assigment. – SlickKitten May 27 '19 at 14:22
@SlickKitten Sounds very weird... a mad idea, try to search in your project folder/app executable folder 'BazaPRO2.mdf', is possible that you create the database if not present and when you use relative path you're working on a database in the wrong location? – Legion May 27 '19 at 14:27
@Legion Ok, so i've found 2x BazaPRO2.mdf files...One is in the solution folder itself, and one is in the bin/debug folder, so I think that is the problem, but still I have no idea how to fix it. – SlickKitten May 27 '19 at 14:46
Added details in the answer. – Legion May 27 '19 at 15:03

Why won't my query execute in my code, while it executes just fine in normal sql?

1 Answers1