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;
	bIsOk = UploadFile(FileInputControl, strFolder, ref strFileName);
	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;
			strResult = proc.StandardOutput.ReadToEnd();
			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);
	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

Good luck!

Posted in asp.net, c#, sql | 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!

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


Posted in sql | 1 Comment »

Generating Business Objects

March 18th, 2008 admin

Once I was developing an application which was accessing a database. So the database had a number of tables (more than 10) for which I had to create business objects. Almost each table TableName had to have a class CTableName containing every field of the table. And also for every class I had to create a collection class CTableNameCollection. I didn't feel like doing that manually so I created an SQL script.

The script has its parameters:

SET @BOPrefix = 'C' - - Prefix of the business object
SET @TablePrefix = 'dt_' - - Table prefix if it has any
SET @TableName = 'dt_Customer' -- Table for wich we need to generate the business object
SET @CurrentNamespace = 'BusinessObjects' - - A namespace in which we are gonna create the business object
SET @ReplacePKWithID = 1 - - In case we have a primary key field named CustomerID but in the business oject we wanna replace it with 'ID'
SET @IDPropertyName = 'ID' - - The name of the field we are gonna use to rename ID column in the business object

I have attached the script to this post.

files: Generating Business Objects

Posted in c#, development, sql | No Comments »

Developing Data Layer: Creating Stored Procedures (MS SQL 2000)

March 14th, 2008 admin

Starting creating an application that is going to access a database the first thing I do is I am creating stored procedures. The sprocs have to be as simple as possible as I am not gonna implement any business logic within them. So all they should do is to insert, update, retrieve and delete information. To simplify the process of the creating that sprocs I have created a simple sql script wich creates them automatically. All I have to do now is to set the parameters of the script, press F5 in my Query Analyzer or SQL Management Studio, copy results of the execution from the message window, paste them into a new window, then probably modify the pasted script (if I need to change any specific settings) and finally press F5 again to create the sprocs.

The parameters I need to modify are:

SET @ProcedurePrefix = 'usp_' -- prefix of the sproc (kinda usp_GetEmployee)
SET @TablePrefix = '' -- table prefix, if a table has a prefix (dt_)
SET @TableName = 'dt_Employee'
SET @IncludeTabulation = 1 -- are we gonna generate paginating and sorting staff (see previous post)
SET @CreateSaveAndAddTogether = 0 -- there is an option to generate one sproc for inserting and updating

This script has its limitation though. Currently a table for wich we are going to generate sprocs has to have an integer primary key.

files: Generate Sprocs

Posted in development, sql | No Comments »

Pagination and sorting in MS SQL Server 2000

March 13th, 2008 admin

I have developed my own way of sorting and paging data without using dynamic queries. All I use is stored procedures with parameters:

@OrderByField varchar(50) = NULL, -- the name of the field to sort by
@OrderByAsc bit = 1, -- direction of sorting: 1 - ASCENDING, 0 - DESCENDING
@FirstRecordToDisplay int = NULL, -- (for pagination)
@NumberOfRecordsToDisplay int = NULL --(for pagination)

as you see all the parameters have default values - so if we do not pass any of them the sproc will return all the data without considering pagination or sorting.

Of course it would be much easier if MS SQL 2000 could understand the syntax:

SELECT Field1, Field2, Fieldn FROM MyTable LIMIT @FirstRecordToDisplay, @FirstRecordToDisplay + @NumberOfRecordsToDisplay ORDER BY @OrderByField (CASE WHEN @OrderByAsc = 1 THEN ASC ELSE DESC END)

but unfortunately it doesn't. So I am gonna show another way to solve this problem.

I have a table Employee, which I am going to use for the example. I need to write a procedure retrieving all the rows of the table considering pagination and sorting.

Structure of the table:

EmployeeID int IDENTITY(1, 1) PRIMARY KEY,
FromDate datetime,
FirstName varchar(50),
LastName varchar(50),
EmailAddress varchar(255)

So please see the text of the procedure below:

CREATE PROCEDURE usp_GetAllEmployees
-- parameters for pagination and sorting
@OrderByField varchar(50) = NULL,
@OrderByAsc bit = 1,
@FirstRecordToDisplay int = NULL,
@NumberOfRecordsToDisplay int = NULL
-- end of the parameters for pagination and sorting
DECLARE @TotalCount int -- this variable we are gonna use later to see the total number of the records in the table
-- here we need to create a temporary table from which we will be getting a final recordset
IF OBJECT_ID('tempdb..#tmpGetEmployee') IS NOT NULL DROP TABLE #tmpGetEmployee
-- the temporary table has all the fields of the base table and one extra-field wich is auto-increment and a primary key
-- we are gonna use that field for sorting and paging data
CREATE TABLE #tmpGetEmployee
EmployeeID int,
FromDate datetime,
FirstName varchar(50),
LastName varchar(50),
EmailAddress varchar(255)
-- then the temporary table is being filled from the base one with sorted data
INSERT #tmpGetEmployee(EmployeeID, FromDate, FirstName, LastName, EmailAddress)
SELECT EmployeeID, EmployeeFromDate, FirstName, LastName, EmailAddress
FROM [Employee]
CASE WHEN @OrderByField = 'EmployeeID' THEN EmployeeID ELSE NULL END,
CASE WHEN @OrderByField = 'FromDate' THEN FromDate ELSE NULL END,
CASE WHEN @OrderByField = 'FirstName' THEN FirstName ELSE NULL END,
CASE WHEN @OrderByField = 'LastName' THEN LastName ELSE NULL END,
CASE WHEN @OrderByField = 'EmailAddress' THEN EmailAddress ELSE NULL END
-- now the temporary table contains all the sorted data from the base table
-- but the direction of sorting has not been considered
-- that is what we gonna do
-- getting total number of the records
SET @TotalCount = @@ROWCOUNT
-- coefficient of direction (asc -> @k = 1; desc - > @k = -1)
DECLARE @k smallint
-- the variable that should store tmpGetID value from the table #tmpGetEmployee -
-- which record from this table should be displayed first depending on the sorting direction
DECLARE @NewFirstRecord int
IF @OrderByAsc = 0
SET @k = -1
-- if the direction is ASC we should display the data starting from the bottom of the table
SET @NewFirstRecord = @TotalCount - ISNULL(@FirstRecordToDisplay, 1) + 1
SET @k = 1
SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
-- final recordset - a bit confusing, but it works!
EmployeeID, FromDate, FirstName, LastName, EmailAddress
FROM #tmpGetEmployee
WHERE (@FirstRecordToDisplay IS NULL OR (tmpGetID - @NewFirstRecord) * @k >= 0 )
AND (@NumberOfRecordsToDisplay IS NULL OR (@NewFirstRecord + @k * @NumberOfRecordsToDisplay - tmpGetID) * @k > 0)
ORDER BY @k * tmpGetID

Good luck!

files: usp_GetAllEmployees

Posted in development, sql | No Comments »