So to start off, I downloaded the correct AdventureWorks database for my version of SQL from the following CodePlex page.
I attached the database and wrote a short piece of code to execute SQLCMD from C#.
The SQL command I wanted to execute is as follows:
SELECT BusinessEntityID,AccountNumber,Name,PreferredVendorStatus,ActiveFlag,ModifiedDate FROM AdventureWorks2008R2.Purchasing.Vendor WHERE CreditRating >= 3
The command that needs to execute from C# is thus as follows:
SQLCMD -S DIRK-DELL\SQLSERVER -d AdventureWorks2008R2 -U sa -P xxx -Q "SELECT [BusinessEntityID],[AccountNumber],[Name],[PreferredVendorStatus],[ActiveFlag],[ModifiedDate] FROM AdventureWorks2008R2.Purchasing.Vendor WHERE [CreditRating] >= 3 " -s "," -o "c:\temp\VendorResults.csv"
The format is as follows:
SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\Yourfilename.csv”
Running this from the command prompt will create a CSV file at the path you specified. So putting this all together, fire up your Visual Studio and create the following method.
private void RunProcess()
string FileName = “SQLCMD”;
string Arguments = @”-S DIRK-DELL\SQLSERVER -d AdventureWorks2008R2 -U sa -P xxx -Q “”SELECT [BusinessEntityID],[AccountNumber],[Name],[PreferredVendorStatus],[ActiveFlag],[ModifiedDate] FROM AdventureWorks2008R2.Purchasing.Vendor WHERE [CreditRating] >= 3 “” -s “”,”” -o “”c:\temp\VendorResults.csv”””;
ProcessStartInfo proc = new ProcessStartInfo(FileName, Arguments);
proc.UseShellExecute = false;
proc.CreateNoWindow = true;
proc.WindowStyle = ProcessWindowStyle.Hidden;
Process p = new Process();
p.StartInfo = proc;
Running your application creates the CSV file in the path specified. Opening the file, you can see the results.
The uses for this are endless, and can extend the functionality of your applications tremendously with a few lines of simple C# code.
Reference: Pinal Dave
Latest posts by Dirk Strauss (see all)
- The Daily Six Pack: September 28, 2015 - 28 September, 2015 00:15:24
- The Daily Six Pack: September 25, 2015 - 25 September, 2015 00:15:05
- CloudBerry Backup Desktop Edition by CloudBerry Lab - 24 September, 2015 16:57:04
- Webucator – C# Online and Onsite Training Classes With Microsoft Certified Instructors - 23 September, 2015 20:34:25
- The Daily Six Pack: September 23, 2015 - 23 September, 2015 00:15:32