Cant save or update my SQL Server tables using vb.net

Question

I am a complete beginner to .net and am confused at some basic things. Please help.

1. First of all the table I create and populate (by right clicking tables in server explorer) disappear once I restart the computer. how do I keep them.

2. Is there any better place/interface to type SQL queries in vb.net than the command prompt.

In the following code:

Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open( )
' Create a data adapter object and set its SELECT command.
Dim strSelect As String = _
"SELECT * FROM Categories"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, cn)


' Load a data set.
Dim ds As DataSet = New DataSet( )
da.Fill(ds, "Categories")

This far the code runs fine but just to gain better understanding, I would like to ask that while data from SQL Server database was saved into da in accordance to the query, why do we need to save/transfer it in the dataset object ds.

Is there any additional benefit of SqlCommand over SqlDataAdapter besides speed?

Dim autogen As New SqlCommandBuilder(da)

Dim dt As DataTable = ds.Tables("Categories")

' Modify one of the records.
Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'")(0)
row("Description") = "Milk and stuff"

gives an error when I use it with

da.Update(ds, "Categories")

regarding dt.select not returning any value.

1. What is the way out?

Answer 1

to answer your questions :

The tables you create with the server explorer are IN MEMORY. Same goes for dataset, they are in-memory representation of your table. As for your 2nd example, the DS you use isnt filled when you try to get the DT. hence why the DT is empty.

If your starting, I would suggest you go look into Linq-to-Sql (http://msdn.microsoft.com/en-us/library/bb425822.aspx) for a more up-to-date way of doing sql in .net ( I think its 4.0 framework)

As for the 2nd point, I'd say normally you should use store procedure for most of your sql commands .. the sqlcommand is use like this

Try
  Cmd = New SqlClient.SqlCommand("st_InventoryStatus_Or_AnyStoreProcName_Or_ASqlQuery")
  Cmd.CommandTimeout = 300 'not really needed'
  Cmd.CommandType = CommandType.StoredProcedure 'you can type CommandType.Text here to use directly your "Select * from Category"'
  Cmd.Parameters.Clear() 'just to be sure its empty, its not mandatory'
  Cmd.Parameters.Add("@idCategory", SqlDbType.Int).Value = myCategory.Id 'here are the parameters of your store proc, or of your query ("select * from Category where Category.id = @Id")'


Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Information)
End Try