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 :open 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.
- 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.
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
- 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.
Now you can integrate the command with connection, means you will tell command that this command will run on this connection/sql server
- DataAdapter: Now the command is assigned, next need to create an object to store the data of that command. Ie. DataApapter
Associate that adapter with the command.
- 4. dataset : Finally get the output on database, ie fill the dataset with adopter
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 :PS SQLSERVER:\> Invoke-sqlcmd –Database Master –Query C:\Query1.txt