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

April 11th, 2008 admin

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!

Posted in asp.net, c#, sql | No Comments »

Uploading a file using ASP.Net

April 10th, 2008 admin

I know there are loads of articles related to uploading a file usin ASP.Net application. But still there is my method of doing that. Usually while creating a web application all my pages extend one base class but not the Page class. I always create some base class which extends the Page, so I could add some basic functionality and it and use it on any page I want. So the UploadFile procedure would on the methods of my base page class.

 
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;    
 
public class MyPage : Page
{
..........
    // strUploadFolder - full path to the folder in which we are gonna store the file (i.e c:\savehere)
    // strFileName - return parameter, that will store upaded file's name
    // function returns true if uploading has been successfull, otherwise we get false
    protected bool UploadFile(HtmlInputFile fileinput, string strUploadFolder, ref string strFileName)
    {
        HttpPostedFile myFile = fileinput.PostedFile;
        int nFileLen = myFile.ContentLength;
        strFileName = string.Empty;
        if (nFileLen > 0)
        {
            byte[] myData = new byte[nFileLen];
            myFile.InputStream.Read(myData, 0, nFileLen);
            strFileName = Path.GetFileName(myFile.FileName);    
 
            try
            {
                if (!Directory.Exists(strUploadFolder))
	       Directory.CreateDirectory(strUploadFolder);
                using (FileStream newFile = new FileStream(Path.Combine(strUploadFolder, strFileName),
                                       FileMode.Create))
	   {
	       newFile.Write(myData, 0, myData.Length);
	       newFile.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        else
            return false;
    }
..........
}

Posted in asp.net, c#, development | No Comments »

Adding a new column to a MS SQL Table safely

April 8th, 2008 admin

How could we check if a column exists and add it to a table if it doesn't? Very easily!

 
IF NOT EXISTS
	(SELECT * FROM syscolumns
	WHERE id = OBJECT_ID(N'[MyTable]') AND name = 'MyColumn')
             ALTER TABLE dbo.MyTable ADD MyColumn INT

Enjoy!

Posted in sql | 1 Comment »