返回的数据类型变化的基础上表中的数据 [英] Datatype returned varies based on data in table

问题描述

我有两个列的表 - [security_role_name]和security_role_cd。数据类型security_role_cd为smallint在Security_Role表。

我有以下的数据选择逻辑。返回的数据类型变化基于数据的情景： -

1. 在表中没有数据
2. 在一个记录present表

问题

1. 为什么数据类型在这些情况下不同的
2. 如何纠正

注意：目前我使用在try..catch 来满足这种情况下

code

 私人诠释GetNextRoleID（SqlConnection的连接）
    {
        诠释？ newRoleID = NULL;
        //字符串的CommandText =SELECT（MAX（security_role_cd））AS [NewRoleID] FROM Security_Role;
        字符串的CommandText =SELECT TOP 1 security_role_cd AS [NewRoleID] FROM Security_Role ORDER BY security_role_cd DESC;
        SqlCommand的命令=新的SqlCommand（CommandText中，连接）;
        command.CommandType = System.Data.CommandType.Text;
        SqlDataReader的读卡器= Command.ExecuteReader却（）;
        如果（reader.HasRows）
        {
            而（reader.Read（））
            {
                如果（！reader.IsDBNull（0））
                {
                    // newRoleID = Convert.ToInt32（（reader.GetInt16（0））+ 1）;

                    尝试
                    {
                        newRoleID = Convert.ToInt32（reader.GetInt16（0））+ 1;
                    }
                    抓住
                    {
                        INT结果=（reader.GetInt32（0））;
                        newRoleID =结果+ 1;
                    }
                }
            }
        }
        reader.Close（）;

        如果（newRoleID == NULL）
        {
            newRoleID = 1;
        }

        返程（Convert.ToInt32（newRoleID））;

    }
 

参考文献：

1. <一个href="http://stackoverflow.com/questions/459572/how-do-i-get-the-sqldbtype-of-a-column-in-a-table-using-ado-net?rq=1">How使用ADO.NET做，我得到一个列的SqlDbType在一个表中？

解决方案

您可以看看 reader.GetFieldType（0）。例如：

  INT I;
    开关（Type.GetType code（reader.GetFieldType（0）））
    {
        案件类型code.Int16：I = reader.GetInt16（0）;打破;
        案件类型code.Int32：I = reader.GetInt32（0）;打破;
        // TODO：你需要处理其他案件
        默认：抛出新NotSupportedException异常（）;
    }
 

或者是简单的：

  INT I = Convert.ToInt32（reader.GetValue（0））;
 

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?

解决方案

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));

这篇关于返回的数据类型变化的基础上表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！