Solved

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

Posted on 2008-10-12
7
887 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Call windows 10 virtual keyboard from windows forms app 2 50
Problem with SelectList in .NET MVC application 1 27
How to read text with RegEx... 2 25
abstract class C# 1 31
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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