/// <summary> /// Scalar Value: illustrates how to retrieve scalar (single value) data from database. /// </summary> /// <param name="employeeId"></param> /// <returns></returns> ///Utils.GenerationCommandType.ScalarValue public static string GetFirstNameByEmployeeId(int employeeId) { if (employeeId < GetIdMinValue) { throw (new ArgumentOutOfRangeException("employeeId")); } // Execute SQL Command SqlCommand sqlCmd = new SqlCommand(); DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@employeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId); DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReturnVal", SqlDbType.NVarChar, 10, ParameterDirection.Output, null); DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETEMPLOYEEFIRSTNAME_BY_EMPLOYEEID); //executing the command DatabaseUtility.ExecuteScalarCmd(sqlCmd); string returnValue = (string)sqlCmd.Parameters["@ReturnVal"].Value; return(returnValue); }
/// <summary> /// sample custom method for get /// returns a collection for records of DataTable type /// Generic Data Table: illustrates how to retrieve a collection of data from database and populate it to a .NET generic DataTable instance. /// </summary> /// <param name="reportsTo"></param> /// <returns></returns> ///Utils.GenerationCommandType.GenericDataTable public static DataTable GetEmployeesByReportsTo(int reportsTo) { DataSet ds = new DataSet(); ds.Locale = System.Globalization.CultureInfo.CurrentCulture; using (SqlConnection cn = new SqlConnection(ConnectionStringManager.DefaultDBConnectionString)) { //sql command SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = cn; DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo); DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO); //adapter SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = sqlCmd; adapter.Fill(ds); } return(ds.Tables[0]); }
public static Boss GetEmployeeBossByEmployeeId(int employeeId) { if (employeeId < GetIdMinValue) { throw (new ArgumentOutOfRangeException("employeeId")); } // Execute SQL Command SqlCommand sqlCmd = new SqlCommand(); DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@employeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId); DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETEMPLOYEEBOSSBYEMPLOYEEID); GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateBossCollectionFromReader); CustomCollection <Boss> objCollection = ((CustomCollection <Boss>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test)); if (objCollection.Count > 0) { return(objCollection[0]); } else { return(null); } }