Set Up
A free Developer edition of Microsoft SQL server is available from the Microsoft site.
As Microsoft change their site and methods regularly, it is probably best to follow their instructions.
It is worth getting the Microsoft SQL Management Studio at the same time as it does make a lot of the set up a lot easier.
The following will assume you have Visual Studio 2019 (Developer edition), SQL Server Express and the SQL Server Management Studio installed.
For simplicity, it is assumed that you are logged in with Windows Authentication, if not, you will need to add username and password to the database to allow access.
Also, ensure that you have set the System Administrator password in SQLServer.
My First Database
Just to be clear, this is not setting up a production database, and certainly not one ready to face a hostile world.
Start the SQL Server Management Studio (SSMS from here).
The object explorer on the left-hand side shows what it can see.
Select the Databases node. If this is your first database, you will probably find that there is just a System Databases node inside.
You can create a database simply by right clicking on Databases and using the defaults.
This is not how databases should be created, you should have a plan written down and decide where all the files should be located. You should also consider how big the database will be initially and how the database is going to grow. Does the database need to have an upper size limit and when it grows, how much will it grow by. These can have a dramatic effect on performance (and also identify when there is a problem).
However, for this case, the defaults will be fine. Add your user as a SQLServer user via permissions – we will use Integrated Security later – this means your login is used to log in to the database.
My First Table
Efficient table design is important, you should as part of your design have considered what data you are storing, and how the data will accessed, updated and possibly deleted.
There is a process called data normalisation – for efficient storage, you should only store the information once, however it might be that in normal usage, you need certain pieces of data together, so it might be worth keeping them in the same table.
This is just about setting up a simple table, so we will initially use the SSMS to create the table.
If you click on the node for the database you created earlier it will display various nodes such as Tables, Views, Programmability etc.
Clicking on the Tables node will show you the System Tables node and possibly a File Tables node. Right click on the Tables node and select Tables.
The first data column will be an ID or Identity column. This will be an Integer (sufficient for this example). In the properties you want it not to be able to be Null, and you need to scroll down to the Identity Specification. Set that to Yes and then you can set the Is Identity field to Yes.
An Identity column is automatically filled with a value that is incremented (by default by one – there is a setting for that). It can be used to Identify the row.
The next column (Name) is a VARCHAR. This will default to 50, but 20 will be sufficient. Set that to not Null as well.
The last column is Description. This is a Text column (the contents of Text columns are not stored in the database table, there is a managed table that holds the text information. This makes the text column type efficient to store large amounts of text but at the disadvantage that the database has to be accessed at least twice, – first to get the original row that contains the reference, and second to get the text).
Viewing your data
For this, you can create a new Query Window in SSMS.
The following SQL statement will select all the contents of the table SimpleNameDescription owned by DBO in the database Testdatabase.
select * from Testdatabase.dbo.SimpleNameDescription;
Disappointingly there is no data in the database at the moment.
That is easily fixed
My First Row
Open another Query window and use the following SQL statement:
insert into [dbo].[SimpleNameDescription] ([name],[description]) values ('First one','This is the first record');
This inserts a record into the database table, setting the fields name and description to have the values listed. Note there is no mention of the ID field. The database, table and field names are enclosed in square brackets to identify they are database, table and field names.
Run the select statement from earlier (that is why a second query window was used for the insert).
Note the ID is 1. When you insert the next record, the ID will be 2. This is the great advantage of using an identity, it handles the value itself.
Using the same insert statement, add a couple of additional rows and then use the select statement to view your handiwork.
Changing a row
You have added a number of rows, but you made a mistake on one of them, how do you fix it?
SQL statements can have a Where Clause, this can be used to identify a subset of the rows in the table. Now luckily (or by good design) we have a column that identifies the rows, so we can update a specific row.
UPDATE [dbo].[SimpleNameDescription] SET [Description] = 'new description' WHERE id = 2;
This will change the description on the row with ID of 2.
Deleting a row
Add a row using:
insert into [dbo].[SimpleNameDescription] ([name],[description]) values ('rubbish','This is rubbish');
Now as it says, this is rubbish, so you want to remove it. Use the select statement above to find the ID number
Open another query window
And add the following:
DELETE FROM [dbo].[SimpleNameDescription] WHERE id = 3;
The value 3 needs to be replaced with the ID of the row of the row you want to delete.
Run the SQL and then run the select. That row has gone. Note, that if you run the delete without the Where clause, it will delete everything and unless you have backed it up, it is gone for good. Be warned.
Programmability
Stored procedures
It is possible to build incredibly complicated systems using the SQL statements, but it has a number of security and maintenance issues. This is where Stored Procedures come in handy. These are SQL statements that can be reused.
This is a simple Stored Procedure:
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Test @ID int
AS
BEGIN
select * from Testdatabase.dbo.SimpleNameDescription where id=@id;
END
GO
To execute the stored procedure open another query window:
GO
[dbo].Test 2;
GO
Accessing the database in C#
The following assumes you have logged in via Windows Authentication and your Windows Login has access to the database.
First you need to determine your SQL log in string.
string connectionString= "Server=localhost\\SQLExpress;database=Testdatabase;Integrated Security=true;;";
The SQL server name is your machine (localhost) and is called SQLExpress. The database is named, and it states that it will use Integrated Security. The latter hands off the heavy lifting of user names and passwords to the Windows Login. If you need to use username and passwords, replace the Integrated security with
user Id=UserName; Password=Secret;
The overall code is:
{
Console.WriteLine("Connecting...");
string connectionString= "Server=localhost\\SQLExpress;database=Testdatabase;Integrated Security=true;;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = " SELECT * FROM [dbo].[SimpleNameDescription]";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} - {reader.GetString(2)}");
}
reader.Close();
}
Console.Write("Press any key");
Console.ReadKey();
}
This will display all the rows in the table SimpleNameDescription.
Now that is fine if you want to execute non selective command, but what if you want to only read for a particular ID?
SQL Command Parameters
Though it is possible to cobble together your SQL statement, it is better to use parameters.
{
Console.WriteLine("Connecting...");
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = " SELECT * FROM [dbo].[SimpleNameDescription] where id=@id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@ID", id);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} - {reader.GetString(2)}");
}
reader.Close();
}
}
Of course, as mentioned above, it is better to use stored procedures.
It is important to set the CommandType, otherwise the stored procedure will not be able to see the
{
Console.WriteLine("Connecting...");
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "[dbo].[Test]";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ID", id);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} - {reader.GetString(2)}");
}
reader.Close();
}
}
References
https://www.microsoft.com/en-gb/sql-server/sql-server-downloads
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=netframework-4.7.2
https://www.connectionstrings.com/sqlconnection/
https://stackoverflow.com/questions/12220865/connecting-to-local-sql-server-database-using-c-sharp
