VB.NET Windows Application: Reading from SQL Server database

A

anarchy-rabbit

Well-Known Member
Joined
May 17, 2009
Messages
113
Reaction score
0
Hi,

I'm busy with a varsity project. Now I have my database, and what I want to do is read a whole record from the Client table where ID = variable1. I want to put this entire record data into a module, so that the module is filled with the client's details.

I have search google for the last few hours, but I'm not getting far. Anyone got a good link or something to help me out please?

Thanks
 
Last edited:
Create a sqldatareader and then fill the object :) so something like this

urg this will be C#

while(reader.Read()) { Module module = new Module { Id = reader[0].ToString(), Name = reader[1].ToString() }

This syntax is ****ed though but you will get the idea or add me on skype and I can help you... pm me for details
 
Sorry, my questions was a bit vague. Is there not maybe an easier way to do this (without a reader), as I think I'm already connected to the db. I have searched for the row using a stored procedure and the following code:

Code: 
If Microsoft.VisualBasic.IsNumeric(txtSearch.Text) Then
                SP_SearchAccountNo_SEL_ClientTableAdapter.Fill(CinemaDirectDataSet.SP_SearchAccountNo_SEL_Client, txtSearch.Text)
                ClientDataGridView.DataSource = SP_SearchAccountNo_SEL_ClientBindingSource
            End If

Now some of the info is displayed on the data grid view of the Client. But I want all the info(not just what is displayed in the data grid view), however I can retrieve what is in the data grid view wiht something along the following code:
Code: 
Client.ID = ClientDataGridView.CurrentRow.Cells(0).Value

Maybe if I create another stored procedure to retrieve all the Client info, I could maybe use something like
Code: 
SP_SearchAccountNo_SEL_ClientBindingSource(0)             //0 for the first column or maybe "Client_ID" not sure which is more correct
 
Last edited:
holy ****ball! look at that code!

to run a ****ing query?! thank god i chose python.
 
well... learning anything in computing is fanshnastic. so good luck. i would suggest fetching whatever your fetching in a loop. for, while, do, take your pick.
 
VB isn't my language of choice so here's my 2c in C#.

Personally I hate table adapters for simpler programs so unless you're required to use them as part of your course, you can get away with using ADO.NET data access objects directly.

Code: 
String procName = ""; //name of the stored procedure
String paramName = ""; //name of the stored procedure parameter name
Int32 paramValue = 12345; //value for the parameter, can be of any type but typically primitive
String connStr = ""; //connection string to your db, depends on your setup

using (SqlConnection conn = new SqlConnection(connStr))
{
  conn.Open();
  using (SqlCommand cmd = new SqlCommand(procName, conn)
  {
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter(paramName, paramValue));
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
         Console.WriteLine(reader.GetString(0)); //Do whatever you need to with each row, just reading the first column as a string here and printing to console
      }
    }
  }
}

In case you were wondering and haven't been told about using statements, they're all about resource management and ensuring that whatever resources are locked by a specific object are freed when you're finished with those objects. By putting an object into a using clause, the Dispose() method on that object is called automatically ensuring that the object's resources are freed as that object requires. This is somewhat equivalent to

Code: 
SqlConnection conn = new SqlConnection(connStr);
try
{
  //Use the SqlConnection object
}
finally
{
  conn.Dispose();
  conn = null;
}

For interest sake the implementation of SqlConnection.Dispose() according to the bible (MSDN) points to the actual connection being closed as part of the tasks performed by this method which is why I didn't explicitly close the connection.

Ideally if you're performing lots of queries on the database (and because it's a Windows client application) you'd only open one connection to the database instead of a new one every time you need to query something (i.e. open the connection when the program starts and close it when it terminates, normally or abnormally). If you're using a file database (e.g. Access, MSSQLCE) that may require concurrent access from multiple applications this single-connection approach probably wouldn't work but decent server databases (i.e. MSSQLServer, MySQL, Oracle etc.) will handle it just fine.
 
Realise now that I digressed a little :) I do hope it was of some use.

Basically all you need to do is define your VB module with properties for all the fields in your row (or columns in your table, more accurately) and then create an instance of that module and set each property to the respective database column using the various GetXyz() methods on the SqlDataReader. If you don't have a unique constraint (or primary key) on the ID column of the table then I would also check how many results you get from the query and maybe raise an error if you get more than one?
 
Also table adaptors are a crap idea anything that .net does for you is a crap idea :) I prefer having a repository maybe ClientRepository with two methods

1. GetById
2. GetAll

You will have one stored procedure

1. sp_ClientSelect

This will look simaler to this

CREATE PROCEDURE sp_ClientSelect (
@ClientId int = null
)
SELECT Column1,Column2,Whatever FROM Client WHERE ClientId = ISNULL(@ClientId,ClientId)

Then you will have in the repository methods

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure
command.CommandText = "sp_ClientSelect"
// To get all details
command.Parameters.AddWithValue("@ClientId",null)
// To get one clients details
command.Parameters.AddWithValue("@ClientId", clientId)

The method returning all the clients will return a IList<Client> and the one that returns one client will just return Client

In the ui you will then do DataGridView1.DataSource = new ClientRepository().GetAll();

and

DataGridView1.DataSource = new ClientRepository().GetById(pass in the clients id);

Thats it



Again C# code but will convert it once my windows 7 virtual pc is installed
 
Or you could create a strongly typed DataSet that matches the schema of the table you are reading from. Then use SqlDataAdpater to fill the DataSet. Very quick and easy. Very poor performance for XML serialization.
 
LOL... for beginners, using SQL Data Readers can be pretty intimidating, and seem long-winded and unncessary.

DataSets are much easier to create (drag and drop from DB) and in fact make use of DataReaders internally - the code is just autogenerated for you by VS. And the performance is pretty decent, except if you plan to XML serialize the DataSet. Binary serialization is very fast though. What's the problem with strongly typed datasets? I've used them before. Definitely not the best .Net/VS components Microsoft created, but not the worst either.

In any case, I don't write my own Data Access logic anymore, I make use of popular frameworks/libraries/APIs that are used and tested by thousands/millions of other developers. Re-inventing the wheel will teach you a lot about how the wheel works, should work, should not work, breaks, brakes, etc, so if you've done it once already, it's not necessary to do it a 2nd time. However, I do recommend doing it at least once.
 
Last edited:
For sure, I also prefer using things like NHibernate (and also recently Fluent) however I would advise steering clear of datasets built into Visual Studio. I've worked on a project that used them and then Microsoft decided that they didn't like their implementation anymore and they changed it, breaking much of our data access layer. I think that any developer getting into .NET development should have a good understanding of ADO.NET and there's no better way of doing that than manually coding out the data access layer using SqlClient (or vendor-specific) namespaces. Also, it forces you to start reading the documentation and understanding the .NET framework instead of just how to use the damn IDE :)
 
This is somewhat similar to the previous post, it's late and I'm going to sleep :)

What I'm saying is ORMs like nHibernate (and even better using Fluent) are definitely tools to have at your disposal but that I would advise steering clear of VS datasets from colleagues experience being burnt in the past. Also, for a developer starting out it's good to have an understanding of ADO.NET as the de-facto Microsoft data access technology. It might be more intimidating than dragging and dropping stuff onto a designer but it does force you to start reading the documentation and understanding some of the basics of .NET instead of just being an "IDE rider". As far as anarchy's project is concerned, I would personally recommend against getting into nHibernate right now: I've just implemented it on a really simple project and it takes quite a bit of time to get running and useful especially when it's all still new to you. Their documentation isn't quite up to the level that newbies need :)
 
Last edited:
anarchy-rabbit said:
Hi,

I'm busy with a varsity project. Now I have my database, and what I want to do is read a whole record from the Client table where ID = variable1. I want to put this entire record data into a module, so that the module is filled with the client's details.

I have search google for the last few hours, but I'm not getting far. Anyone got a good link or something to help me out please?

Thanks
Click to expand...

You should sue your varsity for not teaching you how to do research properly. There's ton's of articles and tutorials explaining exactly what you need to do. If all else fails, microsoft.com has brilliant examples for you including code you could use and videos you can watch on the topic.

Apologies if I sound nasty. But it's a fundamental skill you need to learn, because if you can't Google properly you'll never be able to find anything you're looking for.
 
oh here we go again the ORM awesomeness :) ORM's just add extra overhead................ Yes it makes development time shorter but at what performance cost, I don't mean your mom's website because NHibernate will be fine for that. I mean large scale enterprise applications that require realtime data.

Also NHibernate does not really play well with WCF and Serialization, I personally prefer a repository pattern with stored procedures as it makes maintenance easier.
 
jeanres said:
oh here we go again the ORM awesomeness :) ORM's just add extra overhead................ Yes it makes development time shorter but at what performance cost, I don't mean your mom's website because NHibernate will be fine for that. I mean large scale enterprise applications that require realtime data.

Also NHibernate does not really play well with WCF and Serialization, I personally prefer a repository pattern with stored procedures as it makes maintenance easier.
Click to expand...

To each his own...
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X