Solved

How can I get the maximum length of a column from a DataRow?

Posted on 2008-10-12
7
836 Views
1 Endorsement
Last Modified: 2012-05-05
I need to obtain the maximum permitted length of a string  column value so that I can check prior to assigning the value and truncate the string if it exceeds the permitted length in order to avoid an SQL error.

I guess I need to access a MaxLength property of some description?  Please see the attached pseudocode to see what I mean...

Chris Bray
DataRow row = tempTable.NewRow();

                                row.ItemArray = tempTable.Rows[0].ItemArray;
 

                                // TODO - check the string length and truncate if it exceeds maximum permitted column length

                                if (newName.Length > row[nameField].MaxLength) // NOT a valid call

                                {

                                    newName = newName.Substring(1, row[nameField].MaxLength - 1); // NOT a valid call

                                }

                                // Adjust the field value

                                row[nameField] = newName;

Open in new window

1
Comment
Question by:chrisbray
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22696836
If you are passing the string value to the database as a stored procedure input parameter and the parameter length is same as the database table column length and the value supplied by the application is bigger than the DB column value then the value given to the parameter value will be truncated to the length of the input parameter

example
DB column is varchar(100)
SP parameter is @In VarChar(100)

you supply a string of length 200 then only the first 100 characters will be passed to the @IN variable in the Stored Procedure
0
 
LVL 7

Expert Comment

by:lucky_james
ID: 22696886
you can use datacolumn class to know the details about that column, even the type. But the boundary values may not be visible from that class.

check out:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2950722&SiteID=1

for datacolumn:
http://msdn.microsoft.com/en-us/library/system.data.datacolumn(VS.71).aspx
0
 
LVL 7

Expert Comment

by:lucky_james
ID: 22696890
As ragi as told, your parameters will get truncated. But to know the limit beforehand, i dont think its straightforward.

Hope this helps.

James
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22696960
As Lucky James said that its not straightforward to get the limit of the columns but its a step closer to the database - defining the parameter length equal to the column value and its the most efficient way of what you intend to do
0
 
LVL 3

Author Comment

by:chrisbray
ID: 22697788
Hi lucky_james:, ragi0017:

Thanks for your input.  I am aware that it is not easy, 'cos I couldn't do it straight off :-)

lucky_james, neithr of your links offered a solution in this instance.  

In this scenario I need to know from the DataRow,  as that is all that I have.  Basically I am generically duplicating a record and then changing the name programatically to 'Copy of Name' or  'Copy 2 of Name'  until the name is unique.  

This is normally fine, but if someone gives an excessively long name to something it can exceed the available column width which is what we found in testing. I one case have more than doubled the column width in the database to 50 characters to allow for it as a temporary measure, but it is still possible for someone to exceed the length either with a long name or multiple duplications e.g.  'Copy of Copy of Copy of Copy of Copy of Copy of RatherLongNameString'.

Whilst it is unlikely that this will happen in the real world, I don't like taking chances.  Moreover, I cannot assume a width of 50 characters throughout because this is a generic routine and I have no idea what the table will be called or what columns it will have or what order they will be in.... or critically what size the text column will be.  The name of the column is passed to the method, which is the starting point.

Crhis Bray
0
 
LVL 7

Accepted Solution

by:
lucky_james earned 500 total points
ID: 22700504
Hi Chris,
          Thanks a lot for your reply.
I have had stated in my previous post, alongwith the links, that the links will help you out to know the types but not the boundary conditions.....

Anyhow, you can try using fillschema to fetch the details from db.

for details, check out:
http://forums.asp.net/p/306971/306971.aspx


Let me know, if you face any issues.


James
0
 
LVL 3

Author Comment

by:chrisbray
ID: 22705674
Hi James,

Using FillSchema works well, but has the side effect that for some reason it seems to think that the ID field is constrained to be unique even though that field is populated by the database.  The solution therefore is to simply set the value of the ID column to zero which is bound to be unique, and let the database correct that.

Working code based on your suggestion follows in case that link no longer works at some point in the future when someone else is looking for this answer.

Chris Bray

                                // Get the specified row

                                adapter.Fill(tempTable);
 
 

                                // Duplicate the record

                                DataRow row = tempTable.NewRow();

                                row.ItemArray = tempTable.Rows[0].ItemArray;
 

                                adapter.FillSchema(tempTable, SchemaType.Source);

                                int maxLength = tempTable.Columns[nameField].MaxLength;
 

                                // Calculate the new Name for the copied row

                                int version = 1;

                                string newName = string.Format("Copy{0} of {1}", version == 1 ? "" : " "+version.ToString(), row[nameField]);
 

                                // check the string length and truncate if it exceeds maximum permitted column length

                                if (newName.Length > maxLength)

                                {

                                    newName = newName.Substring(0, maxLength - 1);

                                }
 

                                // Check to see if it already exists - if so keep incrementing the calculated name until it is unique

                                while (GetDependencyCount(tableName, nameField, string.Format("'{0}'", newName)) > 0)

	                            {

                                   version++;

            	                   newName = string.Format("Copy{0} of {1}", version == 1 ? "" : version.ToString(), row[nameField]);      

                                    // check the string length and truncate if it exceeds maximum permitted column length

                                    if (newName.Length > maxLength)

                                    {

                                        newName = newName.Substring(0, maxLength - 1);

                                    }

	                            }                                
 

                                // Adjust the name field value

                                row[nameField] = newName;
 

                                // Adjust rows that identify the user

                                row[idField] = 0;

                                row["EnteredBy"] = userName; 

                                row["EnteredOn"] = DateTime.Now;

                                row["LastModifiedBy"] = "";

                                row["LastModifiedOn"] = DBNull.Value;
 

                                tempTable.Rows.Add(row);
 

                                // Update the table

                                if (adapter2.Update(tempTable) > 0)

                                {

                                    returnId = Convert.ToInt64(tempTable.Rows[1][idField]);

                                }

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now