Uploading and executing an SQL Script File using ASP.Net and osql

April 11th, 2008 admin Posted in asp.net, c#, sql | No Comments »

Now I am gonna tell how to upload a file containg T-SQL script using ASP.Net and execute entire of the file using osql utility tool.
Note: This example is using the UploadFile method from the previous article

 
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.IO;
using System.Diagnostics;    
 
// some code goes here
// ..................
// ...................    
 
string strResult = string.Empty;
// first of all the file should be uploaded
// let's use the UploadFile procedure from the previous article
string strFileName = string.Empty;
string strBaseFolder = "c:\uploads";
string strFolder = Path.Combine(strBaseFolder, Guid.NewGuid().ToString());
bool bIsOk;
try
{
	bIsOk = UploadFile(FileInputControl, strFolder, ref strFileName);
}
catch
{
	bIsOk = false;
}    
 
if (bIsOk)
{
	// getting full file path
	string strFilePath = Path.Combine(strFolder, strFileName);    
 
	// here we should set connection parameters (i.e. they could be taken from web.config)
	string strServerName = "", strDBName = "", strUserName = "", strPassword = "";    
 
	// executing entire script
	using (Process proc = new Process())
	{
		proc.StartInfo.FileName = "osql";
		proc.StartInfo.Arguments = string.Format("/S {0} /U {1} /P {2} /d {3} /i {4}",
			strServerName, strUserName, strPassword, strDBName, strFilePath);
		proc.StartInfo.UseShellExecute = false;
		proc.StartInfo.RedirectStandardOutput = true;
		proc.StartInfo.RedirectStandardError = true;
		proc.StartInfo.CreateNoWindow = true;
		try
		{
			proc.Start();
			proc.WaitForExit();
			strResult = proc.StandardOutput.ReadToEnd();
			proc.Close();
		}
		catch
		{
			strResult = @"The script can not be executed.
                                           Make sure there is osql utility on the server.";
		}
	}    
 
	// now lets delete the file as we don't want it to be saved on the server
	Directory.Delete(strFolder, true);
}
else
{
	strResult = "The file cannot be uploaded to the server";
}    
 
// now we could display strResult variable containg information about script execution
// i.e. using Response.Write method
Page.Response.Write(strResult);

Good luck!

Leave a Reply

You must be logged in to post a comment.