Simple MSSQL connection and login

Heres your chance to share your own tutorials with the community. Just post them on here. If your lucky they may even be posted on the main site.
8 posts Page 1 of 1
Contributors
User avatar
TADS
VIP - Donator
VIP - Donator
Posts: 45
Joined: Sun Jan 22, 2012 9:06 pm

Simple MSSQL connection and login
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.
Image

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....

Image

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
You do not have the required permissions to view the files attached to this post.
Last edited by TADS on Sat Aug 03, 2013 9:43 am, edited 1 time in total.
Oval Racer Series Games - https://ovalgames.co.uk/
User avatar
pip
VIP - Donator
VIP - Donator
Posts: 156
Joined: Tue Jul 12, 2011 3:13 am

Re: Simple MSSQL connection and login
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.
<a href="http://www.points2shop.com/s/xbox_point ... 5082"><img src="http://points2shop.com/images/promotion ... ricoxg.gif" border="0"/></a>
User avatar
dualz
VIP - Donator
VIP - Donator
Posts: 29
Joined: Mon Jul 15, 2013 9:47 pm

Re: Simple MSSQL connection and login
dualz
ill post up a addition to this or a new blog item how to install mssql server and management studio cooll;
Dualz
Co-Owner TADStools Network
  • Microsoft Systems Engineer
    Microsoft Software Engineer
    Cisco Certified Network Engineer
    Programming Languages: Delphi, Python, Cron, PHP, HTML, ASP.Net, Cold Fustion, VB.net, C, C++, C#, XAML, IOS, Android, Java, ASM
User avatar
TADS
VIP - Donator
VIP - Donator
Posts: 45
Joined: Sun Jan 22, 2012 9:06 pm

Re: Simple MSSQL connection and login
TADS
Microsoft SQL Server Management Studio Express


http://www.microsoft.com/en-gb/download ... px?id=8961
Oval Racer Series Games - https://ovalgames.co.uk/
User avatar
pip
VIP - Donator
VIP - Donator
Posts: 156
Joined: Tue Jul 12, 2011 3:13 am

Re: Simple MSSQL connection and login
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.
<a href="http://www.points2shop.com/s/xbox_point ... 5082"><img src="http://points2shop.com/images/promotion ... ricoxg.gif" border="0"/></a>
User avatar
TADS
VIP - Donator
VIP - Donator
Posts: 45
Joined: Sun Jan 22, 2012 9:06 pm

Re: Simple MSSQL connection and login
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?
Oval Racer Series Games - https://ovalgames.co.uk/
User avatar
pip
VIP - Donator
VIP - Donator
Posts: 156
Joined: Tue Jul 12, 2011 3:13 am

Re: Simple MSSQL connection and login
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.
<a href="http://www.points2shop.com/s/xbox_point ... 5082"><img src="http://points2shop.com/images/promotion ... ricoxg.gif" border="0"/></a>
User avatar
TADS
VIP - Donator
VIP - Donator
Posts: 45
Joined: Sun Jan 22, 2012 9:06 pm

Re: Simple MSSQL connection and login
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!
Oval Racer Series Games - https://ovalgames.co.uk/
8 posts Page 1 of 1
Return to “Tutorials”