数据库空检查 [英] Null check from database

查看：81
本文介绍了数据库空检查的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想检查数据库中的列是否为空，如果为空

<pre lang="midl">dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                    dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));




如果不为空:

dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));



我尝试这样的东西(完整代码):


dataContracteClienti.Rows.Clear();
           Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId AS ContracteActive, sum(ContractItems.Payment) AS Total_Platit, Now() AS DataCurenta from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
               + "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId ORDER BY Contracts.StartDate DESC";
           Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());
           DataTable Table = new DataTable();
           Program.Connection.FillDataTable(Table, true);


           foreach (DataRow Row in Table.Rows)
           {

               DiferentaData DiferentaDePlataLaTermen = new DiferentaData(Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));
               DiferentaData DiferentaDePlataAzi = new DiferentaData(Convert.ToDateTime(Row["DataCurenta"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));

               if ( Convert.ToInt32(DiferentaDePlataAzi.ToString()) > Convert.ToInt32(DiferentaDePlataLaTermen.ToString()))
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionBackColor = Color.LightBlue;
               }

               if (Row["ContracteActive"] == DBNull.Value)
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else if (String.IsNullOrEmpty(Row["ContracteActive"].ToString()))
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));

               }




来自C#的异常:

Input string was not in a correct format.Couldn''t store <> in ContracteActive Column.  Expected type is Int32.




如果我将数据库中的行设置为文本"是可以的，如果是数字"则不起作用.必须为int，因为这是来自其他表索引的ID.

解决方案

您的问题不是很清楚.您是说"ContractActive"列是文本，但您希望它是int的，因为它是外键吗?数据库表是如何构造的?

顺便说一句，您不应该为此使用内联SQL，因为它不会给SQL Server提供优化和缓存执行的机会.要检查列的值是否为NULL ，那么最好在比较中使用System.DbNull.Value.例如:

If (row["ColumnName"] == System.DbNull.Value) ...


I whant to check if a column in a database is null, If so

<pre lang="midl">dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                    dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));




If is not null:

dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));



I try someting like this (full code):


dataContracteClienti.Rows.Clear();
           Program.Connection.CommandText = "select Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId AS ContracteActive, sum(ContractItems.Payment) AS Total_Platit, Now() AS DataCurenta from Contracts INNER JOIN ContractItems ON Contracts.ContractId = ContractItems.ContractId WHERE ClientID=@ClientID "
               + "GROUP BY Contracts.ContractId, Contracts.StartDate, Contracts.EndDate, Contracts.Procent, ContractItems.DeletedId ORDER BY Contracts.StartDate DESC";
           Program.Connection.AddParameter("@ClientID", cboNumeClient.SelectedValue.ToString());
           DataTable Table = new DataTable();
           Program.Connection.FillDataTable(Table, true);


           foreach (DataRow Row in Table.Rows)
           {

               DiferentaData DiferentaDePlataLaTermen = new DiferentaData(Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));
               DiferentaData DiferentaDePlataAzi = new DiferentaData(Convert.ToDateTime(Row["DataCurenta"].ToString()), Convert.ToDateTime(Row["StartDate"].ToString()));

               if ( Convert.ToInt32(DiferentaDePlataAzi.ToString()) > Convert.ToInt32(DiferentaDePlataLaTermen.ToString()))
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionForeColor = Color.OrangeRed;
                   dataContracteClienti.DefaultCellStyle.SelectionBackColor = Color.LightBlue;
               }

               if (Row["ContracteActive"] == DBNull.Value)
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else if (String.IsNullOrEmpty(Row["ContracteActive"].ToString()))
               {
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()), (Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataAzi.ToString()) + 1) + Convert.ToDecimal(Row["Total_Platit"].ToString()), ((Convert.ToDecimal(Row["Total_Platit"].ToString()) * (Convert.ToDecimal(Row["Procent"].ToString()) / 100)) * (Convert.ToDecimal(DiferentaDePlataLaTermen.ToString()) + 1)) + Convert.ToDecimal(Row["Total_Platit"].ToString()));
               }
               else
               {
                   dataContracteClienti.DefaultCellStyle.ForeColor = Color.Gray;
                   dataContracteClienti.Rows.Add(Row["ContractId"].ToString(), Convert.ToDateTime(Row["StartDate"].ToString()), Convert.ToDateTime(Row["EndDate"].ToString()), Convert.ToDecimal(Row["Total_Platit"].ToString()));

               }




Exception from C#:

Input string was not in a correct format.Couldn''t store <> in ContracteActive Column.  Expected type is Int32.




If i set the row in the database to Text is OK, if is Number don''t work. Must be int, because is the ID from a nother table index.

解决方案

Your question is not very clear. Are you saying the column "ContractActive" is text but you expected it to be int because it is a foreign key? How are the database tables constructed?

BTW, you should not be using inline SQL for this since it does not give SQL Server the chance to optimize and cache the executions.


Didn''t go through all the code but if you want to check if a column has a value of NULL then it''s best to use System.DbNull.Value in the comparison. For example:

If (row["ColumnName"] == System.DbNull.Value) ...


这篇关于数据库空检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆