Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
340 views
in Technique[技术] by (71.8m points)

c# - Datatype returned varies based on data in table

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:-

  1. No data in table
  2. One record present in table

Questions

  1. Why is the datatype varying in these scenarios
  2. 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:

  1. How do I get the SqlDbType of a column in a table using ADO.NET?
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can look at reader.GetFieldType(0). For example:

    int i;
    switch (Type.GetTypeCode(reader.GetFieldType(0)))
    {
        case TypeCode.Int16: i = reader.GetInt16(0); break;
        case TypeCode.Int32: i = reader.GetInt32(0); break;
        // TODO: any other cases you need to handle
        default: throw new NotSupportedException();
    }

or perhaps simpler:

    int i = Convert.ToInt32(reader.GetValue(0));

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...