Create a SQL Database using Powershell and SQLClient

First, I am by far no expert when it comes to knowing all the ins and outs of SQL Server.  What I do know is this, you can create a database, create and populate a table and then render the information in a variety of ways using Powershell.


$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.SqlClient.SqlConnection
$con.ConnectionString="Data Source=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery

You can do the same thing with Odbc and OleDb as well.

Here's the OleDb example:

$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.OleDb.OleDbConnection
$con.ConnectionString="Provider=SQLOLEDB;Data Source=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.OleDb.OleDbCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery



And when using Odbc:

$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.Odbc.OdbcConnection
$con.ConnectionString="Driver={SQL Server};Server=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.Odbc.OdbcCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery

Tomorrow, I'll show you how to create and populate the table.