Dima's notes

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 »

ASP.Net QueryString Builder

March 25th, 2008 admin

I was trying to find any classes allowing easily modify querystring. I have found a couple of good ones but not ideal for me. So I have created my own class. It has a number of static functions which could be called without creating an instance of the class - if you need to use some basic stuff. Here it is:

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Collections;
using System.Collections.Specialized;
 
namespace ClassLibrary
{
    public class QueryStringBuilder
    {
        protected NameValueCollection m_QueryString;
 
        public QueryStringBuilder(NameValueCollection nvcQueryString)
        {
            m_QueryString = new NameValueCollection(nvcQueryString);
        }
 
        // static function returning a new querystring with a new parameter
        public static NameValueCollection QSWithParameter(NameValueCollection nvcCurrentQS,
                                                      string strParameterName, string strParameterValue)
        {
            NameValueCollection nvcQS = new NameValueCollection(nvcCurrentQS);
 
            for (int i = 0; i < nvcQS.Count; i++)
            {
                if (nvcQS.GetKey(i) == null)
                {
                    nvcQS.Remove(null);
                    break;
                }
            }
            if (nvcQS.GetValues(strParameterName) == null)
                nvcQS.Add(strParameterName, strParameterValue);
            else
                nvcQS[strParameterName] = strParameterValue;
            return nvcQS;
        }
 
        // function returning a new querystring with a new parameter
        public NameValueCollection QSWithParameter(string strParameterName, string strParameterValue)
        {
            return QSWithParameter(m_QueryString, strParameterName, strParameterValue);
        }
 
        // static function returning a querystring without a parameter
        public static NameValueCollection QSWithoutParameter(NameValueCollection nvcCurrentQS,
                                                                               string strParameterName)
        {
            NameValueCollection nvcQS = new NameValueCollection(nvcCurrentQS);
            if (nvcQS.GetValues(strParameterName) != null)
                nvcQS.Remove(strParameterName);
            return nvcQS;
        }
 
        // function returning a querystring without a parameter
        public NameValueCollection QSWithoutParameter(string strParameterName)
        {
            return QSWithoutParameter(m_QueryString, strParameterName);
        }
 
        // static function returning url with a querystring
        public static string UrlWithQueryString(NameValueCollection nvcQueryString, string strUrl)
        {
            int nQS = strUrl.IndexOf("?");
            if (nQS > -1)
                strUrl = strUrl.Substring(0, nQS);
            if (nvcQueryString.Count > 0)
            {
                for (int i = 0; i < nvcQueryString.Count; i++)
                {
                    strUrl += String.Format("{0}{1}={2}", i == 0 ? "?" : "&", nvcQueryString.Keys[i],
                                                                                    nvcQueryString[i]);
                }
            }
            return strUrl;
        }
 
        // static function returning RawUrl with a querystring
        public static string UrlWithQueryString(NameValueCollection nvcQueryString)
        {
            return UrlWithQueryString(nvcQueryString, HttpContext.Current.Request.RawUrl);
        }
 
        // function returning url with a current querystring
        public string UrlWithQueryString(string strUrl)
        {
            return UrlWithQueryString(m_QueryString, strUrl);
        }
 
        // function returning RawUrl with a current querystring
        public string UrlWithQueryString()
        {
            return UrlWithQueryString(m_QueryString);
        }
 
        // procedure removing a parameter from a current querystring
        public void RemoveQSParameter(string strParameterName)
        {
            m_QueryString = QSWithoutParameter(strParameterName);
        }
 
        // procedure adding a parameter to a current querystring
        public void SetQSParameter(string strParameterName, string strParameterValue)
        {
            m_QueryString = QSWithParameter(strParameterName, strParameterValue);
        }
    }

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

ASP.Net 3.5. Where?

March 22nd, 2008 admin

Just found a funny article explaining why after installing .Net 3.5 we can't select ASP.Net 3.5 under a ASP.Net version tab in IIS while setting up a site. I thought the answer was obvious - nobody had said about changing a version of ASP.Net - it is still 2

Posted in asp.net | No Comments »

Coding web forms html

March 21st, 2008 admin

Coding client html of web-forms is not the easiest and not the most interesting part of developing a web-application. There are loads of ways of doing that. But the question is - which one is the right one? The html code should be semantyc, accessible and obvious for other developers. So I tried googling and found this article that seemed very interesting to me. How that could've been more obvious that fieldsets and labels should be used for developing web-forms? And by using CSS we could make the form look in many different ways!

Have a look at the following example:

 
<fieldset class="form-fieldset">
    <label class="textbox surname">
        <span>Surname</span>
<input name="surname" id="surname" type="text" />
    </label>
</fieldset>

Now with a help of CSS we could access any element of this form. Very flexible. We could use as many fieldsets as we could wish. We could even have fieldsets inside fieldsets.

Alright. Later I started thinking how I could use that for simplify the way of creating asp.net web forms. So I created a class FieldSet

 
    public class FieldSet : HtmlGenericControl
    {
        public FieldSet()
            : base("fieldset")
        { }
 
        public FieldSet(string strLegend)
            : this()
        {
            HtmlGenericControl legend = new HtmlGenericControl("legend");
            Controls.Add(legend);
            legend.InnerText = strLegend;
        }
 
        // as an example I have created this simple method which allows add a new input text to the fieldset
        public HtmlInputText AddInputText(string strLabel, string strCSSClass)
        {
            HtmlInputText txt = new HtmlInputText();
            AddControl(strLabel, txt, strCSSClass);
            return txt;
        }
 
        public HtmlInputText AddInputText(string strLabel)
        {
            return AddInputText(strLabel, null);
        }
 
        // this method is used for adding any web contol to the fieldset
        public void AddControl(string strLabel, Control cntrl, string strCSSClass)
        {
            HtmlGenericControl label = new HtmlGenericControl("label");
            Controls.Add(label);
            label.Controls.Add(new LiteralControl("<span>" + strLabel + "</span>"));
            if (strCSSClass != null)
                label.Attributes["class"] = strCSSClass;
 
            label.Controls.Add(cntrl);
            cntrl.ID = ID + "_cntrl_" + Controls.Count.ToString();
            label.Attributes["for"] = cntrl.ClientID;
        }
 
        public void AddControl(string strLabel, Control cntrl)
        {
            AddControl(strLabel, cntrl, null);
        }
    }

So now while developing another asp.net web-form I just could write the code:

 
.....
FieldSet fs = new FieldSet("Editing surname");
Controls.Add(fs);
HtmlInputText txtSurname = fs.AddInputText("Surname:", "surname");
.....

See? Just three lines of code.

Posted in asp.net, development, html | No Comments »

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
AS
SET NOCOUNT ON
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
(
tmpGetID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
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]
ORDER BY
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
BEGIN
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
END
ELSE
BEGIN
SET @k = 1
SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
END-- final recordset - a bit confusing, but it works!
SELECT
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 | 2 Comments »

Hello world!

March 13th, 2008 admin

Hi all, here I am starting writing my notes that can be related to anything - .net developing, php developing, sql-quering, or even my personal life.

Posted in Uncategorized | No Comments »