[RESOLVED] How to put SQL database on a server and how to access it?
Hi, I have many questions regarding SQL databases. I'm very new to this subject and I need to know how to upload (or create) a database on a server and where I can find the connection string. First of all, a database on a server can be accessed with any program with the correct connection string, right? Also, does it update in real-time? (I'm thinking of using Azure but I don't know the quality of the free trial.) And this is some example code I made to manage tables in a local database. Is there anything wrong with it or anything I may need to add? Like I said, I'm new to this and don't really know where to begin. Thanks in advance!
~Nic
vb Code:
Imports System.Data.SqlClient
Public Class Form1
Dim connString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""MY_LOCAL_FILE_PATH\Database1.mdf"";Integrated Security=True"
Dim myConnection As SqlConnection = New SqlConnection(connString)
Dim dr As SqlDataReader
Private Sub CreateTable(TName As String)
myConnection.Open()
Dim command As String = $"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='{TName}' AND xtype='U')
CREATE TABLE [dbo].[{TName}]
(
intRecID Int IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(50) NULL,
Create_Date Smalldatetime DEFAULT GETDATE()
)"
Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
cmd.ExecuteNonQuery()
myConnection.Close()
End Sub
Private Sub AddEntry(TName As String, FName As String, LName As String, Email As String)
myConnection.Open()
Dim command As String = $"INSERT INTO {TName} (FirstName, LastName, Email) VALUES ('{FName}', '{LName}', '{Email}')"
Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
cmd.ExecuteNonQuery()
myConnection.Close()
End Sub
Private Function GetEntry(TName As String, Column As String, ColumnValue As String, ColumnNeeded As String) As String
myConnection.Open()
Dim command As String = $"SELECT {ColumnNeeded} FROM {TName} WHERE ({Column} = '{ColumnValue}')"
Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
dr = cmd.ExecuteReader
Dim str As String = ""
While dr.Read
str += dr(0).ToString
End While
myConnection.Close()
Return str
End Function
Private Sub ClearTable(TName As String)
myConnection.Open()
Dim command As String = $"DELETE FROM {TName}"
Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
cmd.ExecuteNonQuery()
myConnection.Close()
End Sub
Private Sub DeleteTable(TName As String)
myConnection.Open()
Dim command As String = $"DROP TABLE {TName}"
Dim cmd As SqlCommand = New SqlCommand(command, myConnection)
cmd.ExecuteNonQuery()
myConnection.Close()
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
CreateTable("T1")
ClearTable("T1")
MsgBox(GetEntry("T1", "FirstName", "Dave", "Email")) ' This retrieves Dave's email address.
End Sub
End Class
Re: How to put SQL database on a server and how to access it?
You install SQL Server on a Windows machine in the same way you do any other software. If you don't want to pay for a SQL Server license then you can get SQL Server Express for free. It has some limitations but is still very powerful.
To connect to a SQL Server database, you create a SqlConnection object with an appropriate connection string and call Open. The connection string specifies where the SQL Server instance is, which database to open, which user to connect as and various other things. You can find more information about connection strings at www.connectionstrings.com.
The connection string in your current code is specifying that it should connection to a LocalDB instance. LocalDB is a stripped-down database engine intended for development use. When you deploy, you would change that connection string to specify the instance the end user needs to connect to. That's why connection strings are often stored in a config file rather than being hard-coded. That allows the freedom to change connection details after deployment.
Your connection string is also specifying that, rather than being permanently attached to a SQL Server instance, your database is contained in a local MDF data file that must be attached on demand. That is OK for a single-user application but if you need multiple clients to connect to the same database then you need to attach a database to the instance. That way everyone can always access it, rather than attaching their own database each time they run. There are a few different ways to create such a database and many of them are done via SQL Server Management Studio. You would then use the Initial Catalog attribute in your connection string rather than AttachDbFilename.
Yes, just like any other RDBMS, changes made by an application to a SQL Server database will be immediately visible to any other clients.
Re: How to put SQL database on a server and how to access it?
Quote:
Originally Posted by
NinjaNic
First of all, a database on a server can be accessed with any program with the correct connection string, right?
If you mean a server that is within the same network then yes, but if you mean from another network (such as over the internet) then it is such a massive security risk (not just for the database) that most hosting providers wont allow it.
If it is a different network, the best option is probably to create a web service on the server... your program then connects to the web service (using specific parameters, rather than generating SQL as you do above), which is what actually works with the database and returns data etc as appropriate.
Re: How to put SQL database on a server and how to access it?
Thank you very much to both of you for the detailed answers!
So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
Only people in my network can access the databases, and my computer/the server must be on and running, correct?
Re: How to put SQL database on a server and how to access it?
Quote:
So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
Only people in my network can access the databases, and my computer/the server must be on and running, correct?
That's a very broad question, it's like asking how does Visual Basic work and what's the difference between VB 2008 Express and VB 2015 pro.
And yes your machine and the server must be running. That would be a neat trick if they didn't. :)
Re: How to put SQL database on a server and how to access it?
Quote:
Originally Posted by
NinjaNic
So, how does SQL Server Express work, and what is the difference from SQL Server Management Studio?
SQL Server is the broad name for a range of Microsoft database technologies. At the core is the SQL Server RDBMS. SQL Server Express is a cut-down edition of that. It provides most of the features that most small applications require and at no cost. If your needs are greater, e.g. replication, then you need to go for one of the paid-for editions. Management Studio is an application for managing SQL Server databases and the like. SSMS can connect to an instance of SQL Server or SQL Server Express and let you add, edit or remove databases attached to it. There is also an Express edition of SSMS, which can connect to any SQL Server instance but, again, provides fewer features.
Re: How to put SQL database on a server and how to access it?
With the new edition of SQL Server (2016) SSMS can now be downloaded as a separate installation and installed with out needing an SQL Server license. It is updated on a different schedule than the core database components also (about every month or 2)
Re: How to put SQL database on a server and how to access it?
if you install mssql (and express), you need to make specific settings after installation to be able to connect to the instance over the network.
your code is in its basic correct but you should use parameterized queries instead of these interpolated strings. most likely you wont create the table from code but deploy a prepared empty database. and you should certainly not use this "GetEntry" of your code to get the values you want.
to read data you should also look into dataadapters that will deliver a normal DataTable.
If you use a DataReader like in your GetEntry, you must check if it .HasRows before you do a .Read.
If you plan a multiuser environment you need Transactions and handle exceptions.
Re: How to put SQL database on a server and how to access it?
Thanks all. Do I need to have SSMS if I have SQL Server Express? (As opposed to not having anything.)
Quote:
to read data you should also look into dataadapters that will deliver a normal DataTable.
If you use a DataReader like in your GetEntry, you must check if it .HasRows before you do a .Read.
If you plan a multiuser environment you need Transactions and handle exceptions.
Thank you, but can you give me some examples of this, or at least point me toward the right direction?
Thanks again,
~Nic
Re: How to put SQL database on a server and how to access it?
Quote:
Originally Posted by
NinjaNic
Thanks all. Do I need to have SSMS if I have SQL Server Express? (As opposed to not having anything.)
You need SSMS if you want a GUI to manage your database(s). For instance, if you want to restore a database backup or execute SQL scripts to create your initial database then doing so in SSMS is an easy way to do that. If you don't want your users to have to do that as part of your deployment though, you can use SQLCMD instead, which is the SQL Server commandline utility. You can then prepare a batch file to setup the database and just have the user run that or even have it run automatically by your installer.
Re: How to put SQL database on a server and how to access it?
Quote:
If you don't want your users to have to do that as part of your deployment though, you can use SQLCMD instead
Hi, and thanks for that information! I've been trying to do that now. The server works on my own computer, and I can connect to the database, but my other computer in the same wifi will not connect to it. I've tried messing around with the connection strings to no avail.
Right now I have:
Server=Toshiba-Nic\SQLEXPRESS;Database=NameAndEmail;Trusted_Connection=True;
And I'm getting error 26 (error locating server/instance).
This looks right, right? What's going on?
Thanks!
~Nic
EDIT:
I realized that I needed to allow a ton of stuff through the firewall. I found a detailed tutorial here. Manual link:
www.sqlshack.com/how-to-connect-to-a-remote-sql-server
It works now! Thanks, everyone who helped.
