How do I know if a column in a datatable is a required field or cannot be null?

I have a datatable with so many fields. And most of them are required. I want to loop through all the fields and if the field is required, I will message the user. This will prevent me from hardcoding the field check, and less coding in my part.
stylelyfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ronak PatelCommented:
Hi stylelyf,

You can check the database column is nullable or not by below SQL query.

SELECT name, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID('tablename')

Open in new window


You have to replace "tablename" with your table name.
The "is_nullable" is a boolean column, so it will return 0 or 1 where 0 means the column is required / can't be null.

This query will return all the columns present in the table. You can loop through the columns by checking "is_nullable" value.

Hope this helps.

Regards,
Ronak
0
käµfm³d 👽Commented:
Check the AllowDbNull property of the column.
0
stylelyfAuthor Commented:
Thanks. But let me add more information.

Private Function GetData(ByVal spLoader As String)

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("mainconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.Fill(datatable)


		' Refresh grid.
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try


		For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

	End Function

Open in new window


The parameter spLoader is a stored procedure, passed by the caller. So I will not know how many fields, or what are the fields.

The last loop

For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

Open in new window



is what I tried but I was wrong, allowdbnull was meant for something else.

Is there a way I can loop these unknown datatable fields and check if each is required?
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

käµfm³d 👽Commented:
What do you mean by "required"?
0
stylelyfAuthor Commented:
It means it cannot be null.

This means if the field cannot be null, I'm going to colorize that column to red to let the user know its a required column field. Because given a hundred fields, its easier to spot the field by the user even if the column is on the last ordinal, and the user wants to edit on the datagridview on a tabular manner, like excel.
0
Ronak PatelCommented:
Hi stylelyf,

Sorry I misunderstood your requirement.

You have to fill schema for getting schema information for the data table.
Added one line of code: adapter.FillSchema(datatable, SchemaType.Mapped)

Please refer below code:

Private Function GetData(ByVal spLoader As String)

		connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("mainconnectionstring").ConnectionString)
		If connection.State = ConnectionState.Open Then
			connection.Close()
		End If

		connection.Open()

		command = New SqlCommand(spLoader, connection)
		command.CommandType = CommandType.StoredProcedure
		command.CommandTimeout = 0
		adapter = New SqlDataAdapter(command)
		Dim cb As New SqlCommandBuilder(adapter)
		adapter.UpdateCommand = cb.GetUpdateCommand(True)
		adapter.DeleteCommand = cb.GetDeleteCommand(True)
		adapter.InsertCommand = cb.GetInsertCommand(True)

		datatable = New DataTable()
		adapter.FillSchema(datatable, SchemaType.Mapped)
		adapter.Fill(datatable)

		' Refresh grid.
		BindingSource1.DataSource = datatable
		DataGridView.DataSource = BindingSource1.DataSource
		Try
			DataGridView.Columns("recid").Visible = False
		Catch ex As Exception
			'
		End Try


		For Each column As DataColumn In DirectCast(DataGridView.DataSource, DataTable).Columns
			If Not column.AllowDBNull Then
				DataGridView.Columns(column.ColumnName).DefaultCellStyle.BackColor = Color.Red
			End If
		Next

	End Function

Open in new window


Hope this helps.

Regards,
Ronak
0
stylelyfAuthor Commented:
Thanks, let me check and inform you when I get back next day.
0
stylelyfAuthor Commented:
I added the FillSchema() and tried a few code to dig the required property.

I added

Dim schema As DataTable = connection.GetSchema()
		For Each row As DataRow In schema.Rows
			For Each col As DataColumn In schema.Columns
				'col. where?
			Next
		Next

Open in new window



But I can't find it nowhere. How do I get it? (by using fillschema?)
0
Ronak PatelCommented:
Hi stylelyf,

Just refer the code block in my previous comment.

You just need to Fill Schema when you Fill the DataTable from DataAdapter, then the rest of your code will work fine.

To check the nullable column just check column.AllowDBNull, for columns which are not nullable it will be false.


Hope this helps.

Regards,
Ronak
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stylelyfAuthor Commented:
Thanks! Saves me a lot of overtime hours which I could have spent more productively elsewhere.
0
Ronak PatelCommented:
You're welcome !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.