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

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

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

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

Hope this helps.

James

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

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

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]);
                                }

Select allOpen in new window