Answered by:

Insert, update, select, delete from SQL with Visual Studio 2010

Question

I'm experienced in PHP but new to Visual Studio. Whilst I'm finding the experience very user-friendly so far, one thing I simply cannot get my head around is the way the database works. Even googling a solution doesn't get simple basic tutorials that are
easily inserted into applications.

I need a local database that is packaged within the application.

I have set up a dataset from server-based database, added it to my project etc, set up the database name, set up the table and some data.

I've tried 3-4 different methods to insert/select/delete/update it but nothing works and it makes no sense at all. I want to try to use normal SQL request formatting as used in PHP for getting data (ie select * from mytable where id='1' etc).

Has anyone got any really basic code that will work with every type of SQL query?

Answers

When you use SQL Server, then for best performance and support you should use SqlClient namespace and not OleDB.

For simple insert/update/delete command you can use SqlCommand, similar to your first example. Here a basic code snippet for run an INSERT statement. And note, for security reasons always use parameterized queries; don't create SQL statements dynamically,
because that SQL Injections can happen.

Saeid - I've checked out the video and whilst it shows how to create a database and make queries it doesn't seem to show any VB code. From PHP I know all the methods for querying the db table but I can't find an easy way to write a runtime command in VB.
Thanks

Olaf - I'm writing in VB.net. Below is some sample code that needs tweaking or some further explanation to deal with insert/select/update/delete. This seems to be a straightforward method and seems easier than sqlserver method but I can't work out what to
put in the data source bit, whether I need to add a resource for something and how to assign the username/password. I'm not sure if this is even a local internal database access code.

When you use SQL Server, then for best performance and support you should use SqlClient namespace and not OleDB.

For simple insert/update/delete command you can use SqlCommand, similar to your first example. Here a basic code snippet for run an INSERT statement. And note, for security reasons always use parameterized queries; don't create SQL statements dynamically,
because that SQL Injections can happen.

This is working great though hard to view properly in debug mode but that's a minor issue at my end.

I have a working insert/delete/update but I wonder if you can give me an example of a method to display a set of rows via looping through the results from a select query? Say, 'select surname from myTable where id<5'.

can give me an example of a method to display a set of rows via looping through the results from a select query? Say, 'select surname from myTable where id<5'.

Sure. In your first sample you used DataAdapter+DataSet; with this all rows from the result set will be transfered from SQL Server to the client into the DataSet, with that you can then work "offline" without an existing connection to SQL
Server, but for large results it will use a lot of memory on the client.

Other option is to use a DataReader, it works like a server cursor and you can process the data row by row.

The following example queries the names from the system view "sys.objects", which exists in every database, so you can run it without any modification. The printout is done to a Console.

Dan and Olaf - I can see the logic in your code and can obtain some data however with both your examples I get the following error on the Console.Writeline - 'Unable to cast object of type 'System.Int32' to type 'System.String''.

Not sure if that is an error on your part but since it happens with both code snippets I think it must be something to do with the way I've set things up initially. Any ideas?

'Unable to cast object of type 'System.Int32' to type 'System.String''.

I tested my code, of course, and for me it works. Are you using a different code? The easiest way to cast a data type to string is to use the .ToString() method. If you e.g. use GetInt32() instead of GetString(), then you can write it as

Thanks Olaf - I don't fully understand the workings of all this but I have managed to get the data to display by messing with the available options for the reader and reader.GetValue(0) etc seems to do the trick so all is great now.

Can you just confirm that using a local database within the application like this will store the data even when the application is closed then re-opened later on?

But note, you are using a "user instance" of SQL Server and the database file will be attached during runtime (see your connection string). During debugging a copy of the database file will be used (from "Debug" subfolder) and if you clean/rebuild
your solution, may a new copy of the database file MDF will we created for debugging purpose and this is may empty.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.