记录一下数据库中null读取到c# DataReader 与DataScalar中判断问题

当数据库中字段为null时，页面类型为DateTime时因不能为空而无法赋值，

可以通过定义DateTime? 与 Nullable<DateTime>可以存取空值解决。

从数据库读取值赋值给model属性时，为了防止Convert.ToDateTime时因数据库为null出错，

可以用 Convert.IsDBNull() 、Is DBNull.Value  、 ==DBNull.Value 进行判断解决。

下面就DataReader 、DataScalar 从数据库中未读到值，null值 ，一般值与 null 和 DBNull.Value进行比较

看了测试结果应该对上述判断应该有了一个比较清楚的认识了。

 Reader[0] 与 Null

SELECT  NULL FROM DUAL   False
SELECT  NULL FROM DUAL WHERE 1<>1  Empty 空行 if(Reader.Read())
SELECT  SYSDATE FROM DUAL   False

 Reader[0] 与 DBNull

SELECT  NULL FROM DUAL   True
SELECT  NULL FROM DUAL WHERE 1<>1   Empty 空行  if(Reader.Read())
SELECT  SYSDATE FROM DUAL   False

======================================================

 Scalar 与 Null

SELECT  NULL FROM DUAL   False
SELECT  NULL FROM DUAL WHERE 1<>1   True
SELECT  SYSDATE FROM DUAL   False
 
 Scalar 与 DbNull

SELECT  NULL FROM DUAL   True
SELECT  NULL FROM DUAL WHERE 1<>1   False
SELECT  SYSDATE FROM DUAL   False

总结：

  数据库中的null值对应 DBNull.Value 而空行 用DataReader读取时用if(reader.Read())过滤过了，

  用DataScalar时必须用null进行判断。

   

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Data.OracleClient;
using System.Text;
using System.Windows.Forms;

namespace PowerTest
{
    public partial class Form5 : Form
    {
        string connectionString = "Data Source=xxxxxx;User Id=xxxxx; Password=xxxxxxxxx;";
         string cmdText,cmdText2,cmdText3;
        public Form5()
        {
            InitializeComponent();
            
              cmdText = "SELECT  NULL FROM DUAL";
              cmdText2 = "SELECT  NULL FROM DUAL WHERE 1<>1";
              cmdText3 = "SELECT  SYSDATE FROM DUAL";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            msg.Text = "\r\n Reader Test With Null\r\n";
            TestReaderWithNull(); 

            msg.Text += "\r\n\r\n  Reader Test With DBNull \r\n";
            TestReaderWithDBNull(); 
        }

        private void button2_Click(object sender, EventArgs e)
        {
            msg.Text = "\r\n Scalar Test With Null \r\n";
            TestScalarWithNull();
            msg.Text += "\r\n \r\n Scalar Test With DBNull \r\n";
            TestScalarWithDBNull();
        }
        public void TestReaderWithNull()
        {
            msg.Text += "\r\n" + cmdText + "   " + OracleDataReaderWithNull(cmdText);
            msg.Text += "\r\n" + cmdText2 + "   " + OracleDataReaderWithNull(cmdText2);
            msg.Text += "\r\n" + cmdText3 + "   " + OracleDataReaderWithNull(cmdText3);
        }

        public void TestReaderWithDBNull()
        { 

            msg.Text += "\r\n" + cmdText + "   " + OracleDataReaderWithDBNull(cmdText);
            msg.Text += "\r\n" + cmdText2 + "   " + OracleDataReaderWithDBNull(cmdText2);
            msg.Text += "\r\n" + cmdText3 + "   " + OracleDataReaderWithDBNull(cmdText3);
        }


        public string OracleDataReaderWithNull(string cmdText)
        {
            string result;
            OracleConnection con = new OracleConnection(connectionString);
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = cmdText;

            OracleDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            { 
                if (reader[0] == null)
                    result = "true";
                else
                    result = "false";
            }
            else
            {
                result= "Empty";
            }
            reader.Close();
            con.Close();
            return result.ToString();

        }


        public string OracleDataReaderWithDBNull(string cmdText)
        {
            string result;
            OracleConnection con = new OracleConnection(connectionString);
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = cmdText;

            OracleDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
               result = Convert.IsDBNull(reader[0]).ToString();
            }
            else
            {
                result = "Empty";
            }
            reader.Close();
            con.Close();
            return result.ToString();

        }
 
        public string OracleDataScalarWithNull(string cmdText)
        {
            bool result;
            OracleConnection con = new OracleConnection(connectionString);
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = cmdText;

            object scalar = cmd.ExecuteScalar(); 

            if (scalar == null)
                result = true;
            else
                result = false;

            con.Close();

            return result.ToString();
        }

        public string OracleDataScalarWithDBNull(string cmdText)
        {
            bool result;
            OracleConnection con = new OracleConnection(connectionString);
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = cmdText;

            object scalar = cmd.ExecuteScalar();

            result = Convert.IsDBNull(scalar);
            con.Close();

            return result.ToString();
        }


        public void TestScalarWithNull()
        { 
            msg.Text += "\r\n" + cmdText + "   " + OracleDataScalarWithNull(cmdText);
            msg.Text += "\r\n" + cmdText2 + "   " + OracleDataScalarWithNull(cmdText2);
            msg.Text += "\r\n" + cmdText3 + "   " + OracleDataScalarWithNull(cmdText3);
        }
        public void TestScalarWithDBNull()
        {
            msg.Text += "\r\n" + cmdText + "   " + OracleDataScalarWithDBNull(cmdText);
            msg.Text += "\r\n" + cmdText2 + "   " + OracleDataScalarWithDBNull(cmdText2);
            msg.Text += "\r\n" + cmdText3 + "   " + OracleDataScalarWithDBNull(cmdText3);
        }




    }
}