Powershell Day 19

Day 19 Sql Server with .Net

Using .Net programming is  we can connect to sql server, its similar with any windows programming (vb/vc/.net). where we uses connection and connection string. So the script written using .net will work for any windows programming.

.Net process would be very lengthy and for a single sql command we have to write series of commands. but we can use the template to do the connection string and once you are good with sqlconnection procedure you can pass parameter for commands.

.Net has following Classes used to programming sql server.

>>Connection pen connection

>>Command :Sql command

>> DataAdopter : for result

>>DataSet: Result container

All these four classes are integrated with each other and depends on one another. We should follow the same sequence to work.

1. Connection: the class connection is used to connect to the sql server, here you have to specify which sql server are you trying to connect. So need to create an object of connection to open the connection.

$sqlconnection =new-object system.data.sqlclient.sqlconnection

                    Now you got a connection object.  Assign the connection string for this connection, pass the values which sql server you want to connect to.

$sqlconnection.connectionstring=”Server=<serverName\instanceName>;database=master;Integrated Security=True”

Where

             server: is server name, if you have instance use it with “\” –backslash.

                                                             Database: default database

                                                             Security: here you want default integrated security.

You can check the member of this variable

PS C:\> $sqlconnection |gm

Here you can see connectionstring property

1. Command: This is the very important piece of object where you will specify what command you want to execute on the server. You can specify the path/file which you want to execute at the server.

                         $Sqlcmd=new-object system.data.sqlclient.sqlcommand

                         $sqlcmd=get-content c:\query1.txt

Now you can integrate the command with connection, means you will tell command that this command will run on this connection/sql server

$sqlcmd.connection=$sqlconnection

1. DataAdapter: Now the command is assigned, next need to create an object to store the data of that command. Ie. DataApapter

$SqlDataAdapter=new-object system.data.sqlclient.SqlDataAdapter

Associate that adapter with the command.

$sqldataAdapter.selectcommand=$sqlcmd

1. 4.       dataset : Finally get the output on database, ie fill the dataset with adopter

                         $dataset=new-object system.data.dataset

                         $sqlDataAdapter.fill ($dataset)

$dataset.table[0].select()

$

DONE….. we have to create four objects to make it work.

So we can write any query on C:\query1.txt file and run that command on the particular sql server. Ie. Create an .ps1(powershell) file for connection to sql server and another for query.

Run the .ps1 to run any query.

Also you can pass the parameter to the .ps1 file to manipulate the connection string and other parameter

*to execute the same command using sqlps S SQLSERVER:\> Invoke-sqlcmd –Database Master –Query C:\Query1.txt

About these ads