Retrieve sql server information.

[Tjoppie]

Hi all,

I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this?

Thanks

Rudi Groenewald

[mendhak]

For SQL Server Version, you can run this query against sql server:

SELECT @@VERSION

I am not sure what you mean by "current sql server user" and "etc"

[Tjoppie]

By current sql server user I mean the SCURRENT_USER() command. How will my code look if I wanted the version on a textbox on a windows form?

Thanks

Rudi

[mendhak]

You can use the ExecuteScalar method of the SQLConnection class that will return a single value to you in a string.

Read:
http://msdn.microsoft.com/library/en...calartopic.asp

[Tjoppie]

thanks

[Tjoppie]

Please excuse my stupidity, but I'm still lost. What I wanna do is the following:

In my application, I will refer numerous times to sql server variables and static information. So what I want to do is create a class that can give me that static information.

One of these values I will use alot is the current user logged on at the sql server ("CURRENT_USER()" Command)

How will I go about to create this public class to get this value, is it possible that you could give me an example?

I've got like, a class already to specify system colors and stuff that exists out of the following:

Imports System.Data.SqlClient
 
Module JMSGlobals
    Public JMSColor = Color.FromArgb(149, 178, 198)
    Public JMSConnection As New SqlConnection
    Public JMSColorLblsBack = Color.FromArgb(57, 106, 105)
 
End Module

Is it possible to now make values like the current sql server user and sql server version available in the same class?

One other function that I'd like to add but I don't know how is eg: I've got a table (Tbl_Employees) on sql server which is my employees master. Now to get information about the current user and make it public in that same class as above. I was thinking on using something like "select ManagerName from Tbl_employees where Login = CURRENT_USER()"

and then make that value that's returned public. How will I do that?

Thanks alot, I really need to get this one figured out.

Regards,
Rudi Groenewald

[mendhak]

When you say that you will refer to the information numerous times, will it be the same information? In other words, will there always be just ONE user logged in to the SQL Server database in the duration of your application run?

If what I just asked is true, then you can create a class quite similar to your coloring class, with the difference being that you query the database in the constructor (Sub New) of your class and hold the values in Public Properties. This way you can just keep referring to the object's properties without having to requery the database continously.

If, on the other hand, this information is dynamic, then you can make functions which return strings, instead of public properties in your class.

(Btw, your coloring "class" isn't really a class, it is a module, I just thought I should point that out)

So which one of the above two is it? And do you have a problem creating a class?

[mendhak]

Is it possible to now make values like the current sql server user and sql server version available in the same class?

One other function that I'd like to add but I don't know how is eg: I've got a table (Tbl_Employees) on sql server which is my employees master. Now to get information about the current user and make it public in that same class as above. I was thinking on using something like "select ManagerName from Tbl_employees where Login = CURRENT_USER()"

and then make that value that's returned public. How will I do that?

This will be very similar to the technique you implement for the post above. However, I have to question your architecture, and as to why you're using CURRENT_USER. Pardon me if it's a little too obvious to you.

[Tjoppie]

Hi Mendhak,

Yes ur right, There will only be one user logged in for the duration of the application's run, as the application will be open one instance per user. (will be open on numerous pc's, but only one user) so only one user will be "logged in" on my application. (Hope that makes sense)

This information will stay static, so yes, I'll would like it to be returned as static data. Even data that I get from the "Employee Master" like the user's manager's name, user's Shift he work's on's color, that kind of stuff. This information stays static in the database, but gets inserted into tables as the user uses the application. for eg: When the user does a purchasing transaction, the current user's login name gets inserted in the table. then I would use this class / module, to insert the current username. or I would filter records in a datagrid using the current username so that the user only sees his own records.. If that makes it a bit clearer to you.

Mostly, I would refer to the user's company employee number. so this is static aswell. I would get this value from the employees table in sql server, and refer to it each time I would filter records, insert new records into different tables, etc etc.

Thanks alot for all the help, please ask some more questions if you need to.

[mendhak]

What you are describing is something similar to what most of us call a Data Access Layer. It is basically a class or a bunch of classes which you create to perform your database operations for you. The only thing you 'do' from your boundary class is to simply call the function or method.

So this is a good approach that you are taking. There are of course 99 different variations you can take to this approach, but you'll have to find out which one is best for you, and you will realize that as you code.

Now, you can start off (I'm showing you a very very basic DAL) with creating a class that represents your employee, give it some public properties like Employee Number, Manager ID, Address, Gender, Shift, Favorite Cold Drink (Default that to Mountain Dew ) and so on.

In this class, you can create your methods and functions to perform the database operations and querying, which would also set your properties to be accessed later.

How familiar are you with OOP?

[Tjoppie]

uhhh... OOP? Sorry for the stupidity, I've only started studying mcad and mcsd last week, so please bare with me.

Allright. How would I start this off?

All the values, should use my connection called JMSConnection as stated in the public module JMSGlobals as so:

Module JMSGlobals
    Public JMSColor = Color.FromArgb(149, 178, 198)
    Public JMSConnection As New SqlConnection
    Public JMSColorLblsBack = Color.FromArgb(57, 106, 105)
 
End Module

now the connection string for that connection gets set on the login form as follows:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
 
        If (RdoSQLAuth.Checked = True) Then
 
            JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;User Id=" & Me.txtUsername.Text & ";Password=" & Me.txtPassword.Text & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Me.DialogResult = DialogResult.OK
            'Me.Close()
 
        ElseIf (RdoNTAuth.Checked = True) Then
            JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Me.DialogResult = DialogResult.OK
            'Me.Close()
        End If
 
    End Sub

Right... Now I started out my class as:

Class JMSServerEmployees
 
    Dim EmployeeID As String
    Dim login As String
End Class

Great start isnt it? hehe... I'm already lost...

I'm open for some learning...

Thanks alot for the help thusfar.

\\mendhak scores some more reputation points on this one... hehe

[mendhak]

1. OOP = Object Oriented Programming

2. OK, I see where you are going with this. You want your connection object to remain visible to the entire project, in all forms.

So far, what you have done is to create the Employee class. Fine. But your database code is in your forms, which is... not good. What you need to do is to expand this class more:

Class JMSServerEmployees
 
    Dim EmployeeID As String
    Dim login As String
 
 
Public Function DoDatabaseLogin(ByVal strServerName As String, ByVal strUserName As String, ByVal strPassword As String, ByVal NtAuth As Boolean) As DialogResult
 
If NtAuth Then
JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName  & ";Initial Catalog=MSTRIBOLOGY;User Id=" & strUserName & ";Password=" & strPassword & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Return DialogResult.OK
 
Else
  JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Return DialogResult.OK
End If
 
End Function
 
End Class

I haven't edited all the code above, btw, that's for you to do...

Then you can call this function from the login button click:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
 
        If (RdoSQLAuth.Checked = True) Then
MyEmpObj.DoDataBaseLogin(Me.txtUserName.Text, Me.txtPassword.Text.... blah blah)
        ElseIf (RdoNTAuth.Checked = True) Then
        End If
 
    End Sub

[Tjoppie]

1. Aah ok I see, Oop... Why didn't I think of that.
2. Yup, want the connection string public, cause there's gonna be alotta forms, and some users login via sql server authentication and some via nt authentication.

I'm trying that code you've given me now... will get back in a sec. How would I do that class on the sql server variables?

Thanks for all the help thusfar. I battled a bit this morning cause I added more projects to my solution, (class library for all my forms) so that I don't end up with a 10 mb exe file.

[mendhak]

SQL Server Variables? Care to explain?

Oh, and to add to your confusion, store your connection string in the application .CONFIG file.

[Tjoppie]

lol... Yeah , that kinda get me over the edge of confusion. How would I save it in the config file?

Sql Server variables: The informatin that I wanted in the beginning, like the current username, The favourite pet name, mountain dew soft drink, shift color... that stuff.

btw, this is how far I've gotten now.

My class's name I've given where I will put all my classes and modules and functions and stuff that's got to do is called ServerOperations.

Public Class ServerOperations
    Public Function DoDatabaseLogin(ByVal strServerName As String, ByVal strUserName As String, ByVal strPassword As String, ByVal NtAuth As Boolean) As DialogResult
 
        If NtAuth = False Then
            JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName & ";Initial Catalog=MSTRIBOLOGY;User Id=" & strUserName & ";Password=" & strPassword & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Return DialogResult.OK
 
        Else
            JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";"
            Return DialogResult.OK
        End If
 
    End Function
 
End Class

Now on the click event of my login form's Login Button I've changed the code to this:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
        Dim LoginOp As New Object
 
        Try
            If (RdoSQLAuth.Checked = True) Then
                LoginOp.DoDatabaseLogin(Me.txtJMSServer, Me.txtUsername, Me.txtPassword, False)
                Me.DialogResult = DialogResult.OK
 
            ElseIf (RdoNTAuth.Checked = True) Then
                LoginOp.DoDatabaseLogin(Me.txtJMSServer, , , False)
                Me.DialogResult = DialogResult.OK
            End If
 
        Catch ex As Exception
 
        End Try
 
    End Sub

And just guess what.... Nothing happens when I click on the button now... grr... any ideas?

Thanks

Rudi

[mendhak]

Dim LoginOp As New Object

should be

Dim LoginOp As New ServerOperations

[Tjoppie]

oooooh... I c...doh..I feel so sheepish.

Ok... Roit. I've got that now.... the config thingy? and now we can move on to the sql server values?

Thanks for all the help thusfar... You're helping alot!

Rudi

[mendhak]

In the application config file, add this:

<add key="MyConnectionString" value="Yourconnectionstring blah blah" />

And in your code, wherever you want to retrieve the value, use:

System.Configuration.COnfigurationSettings.AppSettings("MyConnectionString")

[Tjoppie]

Ah ok, Thanks, I got that.

Now.... How would I go to work to retrieve information from sql server?

Thanks
Rudi

[mendhak]

Do you know any ADO.NET?

[Tjoppie]

A little bit, but I'm sure if I get a few hints, I'll be able to manage. I used ado in ms access... but that's about it.

[mendhak]

http://samples.gotdotnet.com/quickst...soverview.aspx

[Tjoppie]

Ok... I'm looking through that now...ummm yeah...

Yeah, I'm looking through it now.
Hmmm... yeah... looks good yeah....

[mendhak]

Take your time. It'll be very useful for all your future apps. Well, most of them anyways. Post again when you get stuck somewhere.

[Tjoppie]

Hi Mendhak....

Last night I tried to get only the current username from sql server, but failed miserably. Could you give me an example of how to get something from the server into that class I'm trying?

Thanks
Rudi

[mendhak]

Show your code!

Did you use a statement like this?

SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName

[Tjoppie]

Hi Mendhak,

This is my class which I want to retrieve my values from:

Imports System.Data.SqlClient
Imports System.Drawing
 
Public Module JMSGlobals
    Public JMSColor = Color.FromArgb(149, 178, 198)
    Public JMSConnection As New SqlConnection
    Public JMSColorLblsBack = Color.FromArgb(57, 106, 105)
 
    Public Function CurrentUserName()
        Dim myDataReader As SqlDataReader
        Dim CurrentSQLUser As SqlCommand
 
 
        JMSConnection = JMSClasses.JMSConnection
        JMSConnection.ConnectionString = JMSClasses.JMSConnection.ConnectionString
        CurrentSQLUser = New SqlCommand("SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName", JMSConnection)
        JMSConnection.Open()
        myDataReader = CurrentSQLUser.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
End Module

It's called JMSGlobals.vb

This is how I referred to this code:

Private Sub FrmMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
               Me.lblWelcomeName.Text = JMSClasses.CurrentUserName

What am I doing wrong?

Thanks
Rudi

[mendhak]

Don't use ExecuteReader. Use ExecuteScalar.

Also, in your function CurrentUserName, you need to specify a return type (string) and a return value.

[Tjoppie]

I get index out of range exception when I changed my code to the following:

Imports System.Data.SqlClient
Imports System.Drawing
 
Public Module JMSGlobals
    Public JMSColor = Color.FromArgb(149, 178, 198)
    Public JMSConnection As New SqlConnection
    Public JMSColorLblsBack = Color.FromArgb(57, 106, 105)
 
    Public Function CurrentUserName()
        Dim myDataReader As SqlDataReader
        Dim RetrieveSQLUser As SqlCommand
        Dim CurrentSQLUser As String
 
        JMSConnection = JMSClasses.JMSConnection
        JMSConnection.ConnectionString = JMSClasses.JMSConnection.ConnectionString
        RetrieveSQLUser = New SqlCommand("SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName", JMSConnection)
        JMSConnection.Open()
        myDataReader = RetrieveSQLUser.ExecuteScalar(CommandBehavior.CloseConnection)
        CurrentSQLUser = myDataReader.Read
    End Function
End Module

[Tjoppie]

Anyone have any idea? I know I've almost got it. If I can just get this datareader to work......... puh puh puh puh puh please

[Tjoppie]

Hey,

Ok... I've done the following, but I have the following questions:

Public Function CurrentUserName()
            Try
                Dim myDataReader As SqlDataReader
                Dim RetrieveSQLUser As SqlCommand
                Dim CurrentSQLUser As String
 
                JMSConnection = JMSClasses.JMSGlobals.JMSConnection
                JMSConnection.ConnectionString = JMSClasses.JMSGlobals.JMSConnection.ConnectionString
                RetrieveSQLUser.CommandText = "Select SUSER_SNAME() AS SQLLogin"
                JMSConnection.Open()
                RetrieveSQLUser.ExecuteScalar()
                JMSConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
 
 
        End Function

When running this code, I get like, a msgbox with nothing in it... and the value isnt displayed on the form.

#1: How do I specify the return type and return value?
#2: What am I doing wrong in my code?

Thanks

Rudi

[Tjoppie]

Hi all,

Ok I never thought it would get down to this, but now I'm begging:
I changed my code to the following, but still no luck:

Imports System.Data.SqlClient
Imports System.Drawing
Namespace JMSGlobals
    Public Module JMSGlobals
        Public JMSColor = Color.FromArgb(149, 178, 198)
        Public JMSConnection As New SqlConnection
        Public JMSColorLblsBack = Color.FromArgb(57, 106, 105)
 
        Public Function CurrentUserName()
            Try
                Dim myDataReader As SqlDataReader
                Dim RetrieveSQLUser As New SqlCommand
                Dim CurrentSQLUser As String
                Dim SQLString As String
                Dim JMSConnection As New SqlConnection
                SQLString = "Select SUSER_SNAME() As SQLLogin"
 
                RetrieveSQLUser.Connection = JMSClasses.JMSGlobals.JMSConnection
                RetrieveSQLUser.Connection.Open()
                RetrieveSQLUser.CommandText = SQLString
                RetrieveSQLUser.ExecuteScalar().ToString()
                RetrieveSQLUser.Connection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Function
    End Module
End Namespace

I still can't get the value to get retrieved. The application runs like normal, but nothing happens (the value isnt inserted in the label)

Please Help!

[Tjoppie]

Hi all,

Ok This is mind boggling...

I took the code that I've got in my class I'm creating. Pasted that in the form's code, referred to that to show in the label's text value, call the function on the form's load event......... and it works... it retrieves the value.

now WHY OH WHY, does it work when the codes' inside the form, but when I refer to a publically available class, it doesn't get the value......

Someone PLEASE help me.