How To Modify This Code So That It Also Tests For NULL

Hi,


I'm trying to extend the usage of the code below. This method returns a variable of type SQLParameter and what I want is to accomodate even a check for a null in the parameter. This is a code which I got from one of my co-workers who isn't anymore working here. Any help would be appreciated. I do not want to throw an exception here when a field's type doesn't fall into one of the categories below. The method where this methos is called is the first part of my below code. Any help would be appreciated.





Part 1





private void AddCommandParameters(SqlCommand Comm, string sProcName, ArrayList aParams)


{


string sSQL = "sp_helptext @objname = '" + sProcName + "'";


SqlDataReader dr = this.GetDataReader(sSQL);


int i = 0;





if(dr.Read())


{


while(dr.Read())


{


//CLOSING PARENTHESIS


//When at the closing paren of the stored procedure's variable


//declaration, quit adding parameters.


if(dr[0].ToString().Trim() == ")")


{


break;


}


//ENDING PARENTHESIS


//When at the closing paren of the stored procedure's variable


//declaration, do nothing and skip to next record.


else if(dr[0].ToString().Trim() == "(")


{


//Nada


}


//PARAMETERS


//Add all the parameters listed in the stored procedure's variable


//declaration.


else


{


string [] sParamDef = dr[0].ToString().Trim().Split(Convert.ToChar(" "));


int cs = sParamDef.Length;


string st = sParamDef[0].ToString().Trim();


SQLParameter s = GetSQLParameter(sParamDef[0].ToString().Trim());


Comm.Parameters.Add(sParamDef[0], s.Type, s.Size);


Comm.Parameters[sParamDef[0]].Value = aParams[i];


i++;


}


}


}


else


{


throw(new Exception("Stored Procedure: " + sProcName + " NOT FOUND."));


}


dr.Close();


}








Method being called





private SQLParameter GetSQLParameter(string sVarTypeDec)


{


SQLParameter oParameter;





//VARCHAR


if(sVarTypeDec.IndexOf("varchar") >= 0)


{


int iOpen = sVarTypeDec.IndexOf("(");


int iClose = sVarTypeDec.IndexOf(")");


int iSize = Convert.ToInt16(sVarTypeDec.Substring(iOpen + 1, iClose - iOpen - 1));


oParameter = new SQLParameter(SqlDbType.VarChar, iSize);


}


//CHAR


else if(sVarTypeDec.IndexOf("char") >= 0)


{


int iOpen = sVarTypeDec.IndexOf("(");


int iClose = sVarTypeDec.IndexOf(")");


int iSize = Convert.ToInt16(sVarTypeDec.Substring(iOpen + 1, iClose - iOpen - 1));


oParameter = new SQLParameter(SqlDbType.Char, iSize);


}


//SMALLDATETIME


else if(sVarTypeDec.IndexOf("smalldatetime") >= 0)


{


int iSize = 4;


oParameter = new SQLParameter(SqlDbType.SmallDateTime, iSize);


}


//DATETIME


else if(sVarTypeDec.IndexOf("datetime") >= 0)


{


int iSize = 8;


oParameter = new SQLParameter(SqlDbType.DateTime, iSize);


}


//FLOAT


else if(sVarTypeDec.IndexOf("float") >= 0)


{


int iSize = 8;


oParameter = new SQLParameter(SqlDbType.Float, iSize);


}


//REAL


else if(sVarTypeDec.IndexOf("real") >= 0)


{


int iSize = 4;


oParameter = new SQLParameter(SqlDbType.Real, iSize);


}


//SMALLMONEY


else if(sVarTypeDec.IndexOf("smallmoney") >= 0)


{


int iSize = 4;


oParameter = new SQLParameter(SqlDbType.SmallMoney, iSize);


}


//MONEY


else if(sVarTypeDec.IndexOf("money") >= 0)


{


int iSize = 8;


oParameter = new SQLParameter(SqlDbType.Money, iSize);


}


//BIT


else if(sVarTypeDec.IndexOf("bit") >= 0)


{


int iSize = 1;


oParameter = new SQLParameter(SqlDbType.Bit, iSize);


}


//TINYINT


else if(sVarTypeDec.IndexOf("tinyint") >= 0)


{


int iSize = 1;


oParameter = new SQLParameter(SqlDbType.TinyInt, iSize);


}


//SMALLINT


else if(sVarTypeDec.IndexOf("smallint") >= 0)


{


int iSize = 2;


oParameter = new SQLParameter(SqlDbType.SmallInt, iSize);


}


//BIGINT


else if(sVarTypeDec.IndexOf("bigint") >= 0)


{


int iSize = 8;


oParameter = new SQLParameter(SqlDbType.BigInt, iSize);


}


//INT


else if(sVarTypeDec.IndexOf("int") >= 0)


{


int iSize = 4;


oParameter = new SQLParameter(SqlDbType.Int, iSize);


}


else


{


throw(new Exception("Parameter type not supported."));


}





return oParameter;


}











Thanks,