Lately I've been working a lot more with databases. It's been quite fun and extremely interesting to me. SQL is an amazing programming language in itself. The ability to understand databases is an invaluable task. How your data gets stored, how your columns get created, how the data flows through your applications into your database, and the list goes on and on.

The primary database I work with at the moment is Microsoft SQL. Having the ability to run T-SQL queries in PowerShell is a must-have for me. There is a PowerShell module called the SQL Server PowerShell Module that comes with rich cmdlets and features. I was curious to look a little deeper under the hood and see how I could interact with SQL using .NET.

Today we're going to do just that. Let's get started.

Prerequisites

1. SQL Express. This is actually an incredibly easy pre-req. SQL Express is a free version of Microsoft SQL Server that you can easily install on your Windows 10 machine. To install SQL Express, go to: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

2. SSMS - SQL Server Management Studio is a UI that you can use to interact with your SQL server. This is optional. https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

It's a simple .EXE that needs to be installed. You can choose the basic installation.

Now that we have SQL Express installed, I'll go ahead and open up my SQL Server in SSMS.

Next we'll need to open up our favorite code editor (mine is VSCode) so we can start writing our code.

Now that we have our editor up, let's get coding.

The first line we will write is a variable to initiate a new object. This object will be to use the SqlConnection class.

$dbConn = New-Object -TypeName System.Data.SqlClient.SqlConnection

The second line will be to call the ConnectString property so we can input our connection string to our SQL server. If you're running SQL locally like I am, yours will look like the below.

<$dbConn.ConnectionString = "Server=localhost\SQLEXPRESS;Trusted_Connection=True;"

Now let's call the Open() method so we can open up a connection to our SQL server.

$dbConn.Open()

Now that we have an open connection to our SQL server, let's write our variable with a SQL query to create a new database.

$createDB = "CREATE DATABASE mikesdb;"

Now that we have our query written, let's use the SqlCommand class in the System.Data.SqlClient namespace so that we can execute our command.

$command = New-Object -Type System.Data.SqlClient.SqlCommand

Now we can call out connection with the Connection property.

$command.Connection = $dbConn

We now have a connection to our SQL server and the query we want to run. We're now ready to execute that query using the CommandText property.

$command.CommandText = $createDB
$reader = $command.ExecuteReader()

Let's go ahead and run it!

We now have our database created! We can also go ahead and create a new table. We'll need to close out connection because we already have a Reader open. We can use the Close() method for this.

$dbConnect.Close()

$dbConn.ConnectionString = "Server=localhost\SQLEXPRESS;Trusted_Connection=True;"
$dbConn.Open()

$createTable = "use mikesdb; CREATE TABLE mikestable (ID int, firstname varchar(255), lastname varchar(255),)"
$command.CommandText = $createTable
$reader = $command.ExecuteReader()

Your new table and columns have been successfully created!