I have a table with two column - [security_role_name] and security_role_cd . Datatype for security_role_cd is smallint in Security_Role table.
I have following data selection logic. The datatype returned varies based on the scenario of data:-
- No data in table
- One record present in table
Questions
- Why is the datatype varying in these scenarios
- How to correct it
Note: Currently i am using try..catch
to meet this scenario
CODE
private int GetNextRoleID(SqlConnection connection)
{
int? newRoleID = null;
//string commandText = "SELECT (MAX(security_role_cd)) AS [NewRoleID] FROM Security_Role ";
string commandText = "SELECT TOP 1 security_role_cd AS [NewRoleID] FROM Security_Role ORDER BY security_role_cd DESC";
SqlCommand command = new SqlCommand(commandText, connection);
command.CommandType = System.Data.CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
//newRoleID = Convert.ToInt32((reader.GetInt16(0)) + 1);
try
{
newRoleID = Convert.ToInt32(reader.GetInt16(0)) + 1;
}
catch
{
int result = (reader.GetInt32(0));
newRoleID = result + 1;
}
}
}
}
reader.Close();
if (newRoleID == null)
{
newRoleID = 1;
}
return (Convert.ToInt32(newRoleID));
}
REFERENCE:
- How do I get the SqlDbType of a column in a table using ADO.NET?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…