?
Solved

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

Posted on 2008-10-12
7
Medium Priority
?
1,005 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

777 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