Breadcrumbs

[TADS]

**EDIT** This tutorial dose not show you how to set up a MSSQL server, you must already have a working MSSQL server to follow this tutorial. There is a tutorial coming from #dualz on how to set up a mssql server with server management studio.


Evening all i have sat here and wrote this all night you you.... i know its a VERY long tutorial but still lo... Hope you all like it.

Log into your sql with Microsoft SQL Server Management Studio
Create new database, For this will will call it logintest
so in the field Database Name: put in logintest

createdatabase.jpg

Then hit ok at the bottom.
Now you have a database lets make a table....
Expand your new database and right click on "tables" and chose New Table.
We will add 5 rows... id, username, password, online and showing (i always put a showing in all my dataabses)

so do this to add your new fileds....

Column Name = id
Data_Type = Int
Allow Nulls = untick

While you are on your id column scroll down in the column properties and expand Identity Specification and change (is identity) to yes, This will make your field auto increment. then right click on the id column and set as primary key

next row....

Column Name = username
Data_Type = Nvarchar(50)
Allow Nulls = untick

next row....

Column Name = password
Data_Type = Nvarchar(MAX)
Allow Nulls = untick

next row....

Column Name = online
Data_Type = Int
Allow Nulls = tick

and the last row.

Column Name = showing
Data_Type = Int
Allow Nulls = tick

Now we have made our table right click the tab at the top and click save table1 and save as... users

now you can close that tab at the top and expand your "tables" and you will find "dbo.users" in there
You have just finished creating your database!!!!

NOW ON TO SOME CODE!!!!


so we open Visual Studio 2010

Create new Windows Forms Application project and name it what you like but i am going to name mine logintest to match the database, im leaving mine on Framework 2.0 for this tutorial.

Now we need to add a few controls to the form...

2 labels 2 textbox and 2 buttons.


make sure you have the controls set out how i have in the image above...
The textbox next to the lable "username:" change the name of the textbox to "usrtxtbox" do the same for the password text box except name it"pwdtxtbox"
name the Register Button "BtnRegister" and the login button "BtnLogin", cool now lets do some coding!
First thing i want you to do is Add a new class and call it "globaldata.vb"
Right click globaldata.vb and View Code.
Above Public Class globaldata add a refference to sqlclient like so...

Code: Select all

Imports System.Data.SqlClient

now Right click form1.vb and View Code.
same as above add a refference to this time globaldata as this is where we will store all of our data and variables
so above Public Class put...

Code: Select all

Imports logintest.globaldata

now we need to create some connect strings to connect tot he database. so in globaldata we add this....
inside Plublic class globaldata we add a Region and inside the region we add...

Code: Select all

Public Shared connectionString As String = "server=server ip; database=logintest;user id=your user id for server; password=your server password;"

with your server details in.

Reason we use public shared is we can call this connectionstring from any form/class in our application!
We will just add one more variable to store out username in...

Code: Select all

Public Shared username As String = Nothing

so our golbaldata.vb will look like this.

Code: Select all

Imports System.Data.SqlClient

Public Class globaldata
#Region "variables"
    Public Shared connectionString As String = "server=server ip; database=logintest;user id=your user id for server; password=your server password;"
    Public Shared username As String = Nothing
#End Region
End Class

Now we are going to add some code in to MD5 Hash our password.
add a new refference to Cryptography at the top of your globaldata class

Code: Select all

Imports System.Security.Cryptography

under Public Shared username As String = Nothing we will add 5 more shared variables

Code: Select all

 Public Shared bytestohash() As Byte
    Public Shared md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider
    Public Shared results As String = Nothing
    Public Shared newpass As String = Nothing
    Public Shared infomsg As String = Nothing

now we will make a new shared function to hash our password.... First make a new region... MD5 Hash

Code: Select all

#Region "MD5 Hash"
    Public Shared Function Md5pass(ByVal stringtohash As String) As String
        bytestohash = System.Text.Encoding.ASCII.GetBytes(stringtohash)
        bytestohash = MD5.ComputeHash(bytestohash)
        For Each b As Byte In bytestohash
            results += b.ToString("X2")
        Next
            results.ToString.ToUpper()
            newpass = results.ToLower
        Return results
    End Function
#End Region

We are not going to go over what that dose atall.... this was a simple login to sql server.

ok so we are going to make a connection function to sql server.... add a new region called sql create account and inside that we write our function

Code: Select all

#Region "sql create account"
    ''' <summary>
    ''' To Create a new account please enter the details.
    ''' </summary>
    ''' <param name="Cname">Please enter your username text filed</param>
    ''' <param name="Cpass">Please enter your password text filed Use md5new(password filed) for your password.</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function createnewaccount(ByVal Cname As String, ByVal Cpass As String)
        Try
           Md5pass(Cpass)
            Dim sqlConn As New SqlConnection(connectionString)
            Dim sqlComm As New SqlCommand()
            sqlComm = sqlConn.CreateCommand()
            sqlComm.CommandText = "INSERT INTO dbo.users (username, password,online,showing) VALUES ('" & Cname & "', '" & newpass & "','0','1')"
            sqlConn.Open()
            sqlComm.ExecuteNonQuery()
            sqlConn.Close()
            username = Cname
            infomsg = "Account has been sucessfully created for" & vbCrLf & Cname
            MessageBox.Show(infomsg, "Account Created", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            infomsg = "There has been a error, Details are below." & vbCrLf & ex.ToString
            MsgBox(infomsg)
        End Try
        Return username
    End Function
#End Region

Now we need to add some login code!

start of by adding a new region called sql login

and a new function for loginaccouunt.

Code: Select all

 Public Shared Function loginaccount(ByVal Cname As String, ByVal Cpass As String)

    End Function

now inside here we need to clear some variables incase you enter the wrong details in first time around.... so add this inside your loginaccount function...

Code: Select all

Try   ' dont forget to add the Try Catch methord.....
            results = Nothing
            newpass = Nothing

now we need to md5 our password that we have typed in the box so add this under the newpass = nothing line....

Code: Select all

    Md5pass(Cpass)

ok so we have a md5 password and now we can start to add in our connectionquery..... add this few lines of code under the mdspass line...

Code: Select all

            Dim con As SqlConnection = New SqlConnection(connectionString)
            Dim returnedval As Object  

ok so we have made our connection query we now need to run the login query... add this block of code under the last lline we entered in....

Code: Select all

  cmd.CommandType = CommandType.Text

                cmd.CommandText = "SELECT id, username FROM dbo.users WHERE username='" + Cname + "' AND password='" + newpass + "'"
                con.Open()
                returnedval = cmd.ExecuteScalar
                If Not returnedval = 0 Then
                    infomsg = "Welcome back!" & vbCrLf & Cname ' we use this so we can set custom messages.
                    MessageBox.Show(infomsg, "Loged In", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    infomsg = "There has been a error, Details are below." & vbCrLf & "login Wrong" ' we use this so we can set custom messages.
                    MsgBox(infomsg)
                End If
            End Using
            con.Close()
        Catch ex As Exception
            infomsg = "There has been a error, Details are below." & vbCrLf & ex.ToString ' we use this so we can set custom messages.
            MsgBox(infomsg)
        End Try
        Return username
    End Function
#End Region

FULL code for globaldata.vb Below

Code: Select all

Imports System.Data.SqlClient
Imports System.Security.Cryptography

Public Class globaldata
#Region "variables"
    Public Shared connectionString As String =  "server=server ip; database=logintest;user id=your user id for server; password=your server password;"
    Public Shared username As String = Nothing
    Public Shared bytestohash() As Byte
    Public Shared md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider
    Public Shared results As String = Nothing
    Public Shared newpass As String = Nothing
    Public Shared infomsg As String = Nothing
#End Region

#Region "MD5 Hash"
    Public Shared Function Md5pass(ByVal stringtohash As String) As String
        bytestohash = System.Text.Encoding.ASCII.GetBytes(stringtohash)
        bytestohash = MD5.ComputeHash(bytestohash)
        For Each b As Byte In bytestohash
            results += b.ToString("X2")
        Next
        results.ToString.ToUpper()
        newpass = results.ToLower
        Return newpass
    End Function
#End Region

#Region "sql create account"
    ''' <summary>
    ''' To Create a new account please enter the details.
    ''' </summary>
    ''' <param name="Cname">Please enter your username text filed</param>
    ''' <param name="Cpass">Please enter your password text filed Use md5new(password filed) for your password.</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function createnewaccount(ByVal Cname As String, ByVal Cpass As String)
        Try
            Md5pass(Cpass)
            Dim sqlConn As New SqlConnection(connectionString)
            Dim sqlComm As New SqlCommand()
            sqlComm = sqlConn.CreateCommand()
            sqlComm.CommandText = "INSERT INTO dbo.users (username, password,online,showing) VALUES ('" & Cname & "', '" & newpass & "','0','1')"
            sqlConn.Open()
            sqlComm.ExecuteNonQuery()
            sqlConn.Close()
            username = Cname
            infomsg = "Account has been sucessfully created for" & vbCrLf & Cname
            MessageBox.Show(infomsg, "Account Created", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            infomsg = "There has been a error, Details are below." & vbCrLf & ex.ToString
            MsgBox(infomsg)
        End Try
        Return username
    End Function
#End Region

#Region "sql login"

    Public Shared Function loginaccount(ByVal Cname As String, ByVal Cpass As String)

        Try
            results = Nothing
            newpass = Nothing
            Md5pass(Cpass)
            databack.ListView1.Items.Clear()
            Dim con As SqlConnection = New SqlConnection(connectionString)
            Dim returnedval As Object

            Using cmd As SqlCommand = con.CreateCommand()
                cmd.CommandType = CommandType.Text

                cmd.CommandText = "SELECT id, username FROM dbo.users WHERE username='" + Cname + "' AND password='" + newpass + "'"
                con.Open()
                returnedval = cmd.ExecuteScalar
                If Not returnedval = 0 Then
                    infomsg = "Welcome back!" & vbCrLf & Cname ' we use this so we can set custom messages.
                    MessageBox.Show(infomsg, "Loged In", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    getlistedusers()
                Else
                    infomsg = "There has been a error, Details are below." & vbCrLf & "login Wrong" ' we use this so we can set custom messages.
                    MsgBox(infomsg)

                End If

            End Using
            con.Close()
        Catch ex As Exception
            infomsg = "There has been a error, Details are below." & vbCrLf & ex.ToString ' we use this so we can set custom messages.
            MsgBox(infomsg)
        End Try



        Return username
    End Function
#End Region

#Region "get all users"

    Public Shared Sub getlistedusers()

        Try
            databack.ListView1.Items.Clear()
            Dim con As SqlConnection = New SqlConnection(connectionString)
            con.Open()

            Using cmd As SqlCommand = con.CreateCommand()
                cmd.CommandType = CommandType.Text

                cmd.CommandText = "SELECT id, username FROM dbo.users"
                Using dr As SqlDataReader = cmd.ExecuteReader()

                    While dr.Read()
                        Dim li As ListViewItem
                        li = databack.ListView1.Items.Add(dr.GetValue(0).ToString)
                        li.SubItems.Add(dr.GetString(1))
                    End While
                    databack.Show()
                End Using
            End Using
            con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

#End Region


End Class

And thats it.... and you can use that on your form as follows...

Code: Select all

Imports logintest.globaldata

Public Class Form1

    Private Sub BtnRegister_Click(sender As System.Object, e As System.EventArgs) Handles BtnRegister.Click
        createnewaccount(usrtxtbox.Text, pwdtxtbox.Text)
    End Sub

    Private Sub BtnLogin_Click(sender As System.Object, e As System.EventArgs) Handles BtnLogin.Click
        loginaccount(usrtxtbox.Text, pwdtxtbox.Text)
    End Sub
End Class

you can now create a user and login to the database using MSSQL..... but why stop here... how do you know you have connected to the database apart from me telling you that you have using the messahe box.... so lets go and add some code to list all users when you get a correct login.....

go back to your globaldata.vb and add this function to the bottom of the class file... dont worrie there will be errors untill we create a new form....

Code: Select all

#Region "get all users"

    Public Shared Sub getlistedusers()

        Try
            databack.ListView1.Items.Clear()
            Dim con As SqlConnection = New SqlConnection(connectionString)
            con.Open()

            Using cmd As SqlCommand = con.CreateCommand()
                cmd.CommandType = CommandType.Text

                cmd.CommandText = "SELECT id, username FROM dbo.users"
                Using dr As SqlDataReader = cmd.ExecuteReader()

                    While dr.Read()
                        Dim li As ListViewItem
                        li = databack.ListView1.Items.Add(dr.GetValue(0).ToString)
                        li.SubItems.Add(dr.GetString(1))
                    End While
                    databack.Show()
                End Using
            End Using
            con.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

#End Region

this will list all users and display then in a listview on databack.vb form... now create a new form called databack...

Add 1 listview on it...

Change view type to Details, and gridlines to True

click colletions on columns and add 2 columns change the text in the first one to ID and the second to Username... should look like this....



hit ok and we are all donje with this form... close it...

go back to globaldata.vb and on line 77 or there abouts... inside loginaccount under

Code: Select all

If Not returnedval = 0 Then
                    infomsg = "Welcome back!" & vbCrLf & Cname ' we use this so we can set custom messages.
                    MessageBox.Show(infomsg, "Loged In", MessageBoxButtons.OK, MessageBoxIcon.Information)

Add

Code: Select all

   getlistedusers()

That will generate the users from the MSSQL database and show them on the listview.
Hope you all liked this VERY LONG tutorial
Our next one will bee adding to this tutorial and adding in Service Broker to show online and offline users and show them coming online in real time...

i have put 1 credit on this tutorial full code download just so the lazy ones do read the tutorial and not just download the code...


This file is hosted off-site.

Thanks
tads

tadsname.jpg

[pip]

#TADS This is a useful tutorial, but you think you could provide link for the download to Microsoft sql manager or whatever I could not seem to find it, and although many of us don't use this stuff it is something i been looking for, and into so thanks alot! +Rep Keep up the good work.

[dualz]

ill post up a addition to this or a new blog item how to install mssql server and management studio cooll;

[TADS]

Microsoft SQL Server Management Studio Express


http://www.microsoft.com/en-gb/download ... px?id=8961

[pip]

@ #TADS Thanks so i was following this tutorial and i don't get how to connect it with this bit

Code: Select all

#Region "variables"
    Public Shared connectionString As String = "server=server ip; database=logintest;user id=your user id for server; password=your server password;"
    Public Shared username As String = Nothing
#End Region

Can you further explain how to set that up or make that in the manager, and get those details please that part was a bit unexplained and broad like way open.

[TADS]

Morning #pip

That piece of code you are querying is just the connection string to the MSSQL database...

Have you connected to MSSQL with the management Studio and already created your database?

[pip]

@ #TADS Well that's the thing you kind of left it open... like hard to follow i can't even figure out if i did make one or not maybe put a video on how to set up the program, so it is easier for others? cause then there is video then we could follow this tutorial easier.

[TADS]

sorry #pip This tutorial is about a simple connection to MSSQL, This tutorial do not go though settings up a MSSQL server, i am sorry tho i should have put that you must know how to use MSSQL before doing this tutorial. i will edit the post!