-5

It's may be I'm doing something wrong but don't know why I'm getting such issue.
I'm using string.Format which took around 130 parameters my code look like as below 

string query = string.Format(@"Insert into TB_LN_CASES (
                col1, 
                col2,
                col3,
                col4,
                ...
                ...
                col129,
                col130) Values ({0},{1},{2},{3}.....{129})", 
                col1.ToString(),
                col2.ToString(),
                col3.ToString(),
                col4.ToString(),
                ...
                ...
                col130.ToString());

The output which I'm getting in string is like 

Insert into TB_LN_CASES (col1, col2,col3,col4,
                    ...
                    ...col129,col130) Values (abc,efd,gr,y,t,ui,u,re,re

String is incomplete, don't know what is the reason behind this or is there any alternative to do this, please suggest

Is there any max length constraint for string.Format ?

Improve this question
22
  • 1
    what is such issue here? Commented May 5, 2016 at 5:10
  • 1
    You are most definitely doing something wrong. The thing you are doing wrong is using string.Format to create an insert statement, instead of using parameterized statements. This is a major security hazard since it's an open door for sql injection attacks Commented May 5, 2016 at 5:26
  • 1
    btw, string.format already calls ToString() on every parameter it gets, so that's redundant. Commented May 5, 2016 at 5:35
  • 1
    @Co.Aden: check the MSDN please (msdn.microsoft.com/en-us/library/…). You are limited by the length of string. Again, super long statements won't be executable in your database anyway! Commented May 5, 2016 at 5:36
  • 2
    Be aware that contrary o what people like Zohar say there are good reasons to avoid parameters on insert statements. The main one: Insert can insert many rows.... but you are still limited to around 1000 parameters. Less than 10 lines in your example, while the insert statement could have 1000 rows in one statement or more. SQL injection is a total non issue with a little planning on the parameter processing. I am generally in favor of using parameters, but contrary to certain near religious zealot attitudes, one has to accept that there are cases it makes sense not to use them. Commented May 5, 2016 at 5:45

4 Answers 4

Reset to default
1

That is so totally not a string.format issue that it is not funny.

Please consider doing a little basic debugging yourself.

Values (abc,efd,gr,y,t,ui,u,re,re

This is not valid SQL. See, string values have to be in paranthesis of some sort ('abc' instad of abc).

Simply speaking your (btw, the old string.format syntax is hard to read - learn to use $"" strings, the new syntax for formatting in .NET 6.0) generated SQL is bad and you never considered this a SQL error.

Now, for the length issue - that is no, there is no sensible limit that you would reach. There is one, but it is LONG (not sure about the string limit - 2 gigabytes RAM?). It is likely you have a serious presentation issue (as in: The string is there, you just do not see it, like in the debugger, which may limit the output length).

I would reformat that to use he new $"{paramname}" syntax - it is a LOT easier to debug once you hit 10 or 20 parameters.

Please also note: The ToString calls on all those parameters are surplus (default call anyway).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

If you are building up a SQL query, I would strongly recommend you use parameters in your query.

Here is an example:

string strQuery = "Insert into TB_LN_CASES (col1, ...) VALUES (@columnOneVariable, ...)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.AddWithValue("@columnOneVariable", "yourValue");

At the moment you are very vulnerable to SQL injection.

To answer your question about the limit on String.Format(), please read here

Improve this answer

11 Comments

ok, i'll do that but main thing is, is their any max length constrain with string.Format()
Not that I am aware of. Take a read here, might be useful. stackoverflow.com/questions/140468/…
Actually there is - but it will not bite you. I think you are limited to around 1 billion characters. Things get ugly way before that, though - and in terms of SQL... that would really blow anything sensible out of the water as one statement anyway.
@TomTom Thanks for the info. Am I correct in saying, the limit, for in memory objects/variables, is 2GB, as per the article I have suggested above?
I think yes. For strings (remember, 2 bytes per char) that runs around 1 billion chars. Now, I have seen SQL Statements I Would assume to be longer than what most people here see (including multi megabyte insert statements).... but from there to the string limit.... worlds of difference. Also note: for the pure string part, consider using a stringbuilder (and it's AppendFormat method) as it may be more efficient internally. Heavy string manipulation with strings generally creates a ton of garbage (though I am not sure about a single string.format).
|
0

try this:

using (SqlConnection connection = new SqlConnection(this.connectionString))
{
    connection.Open();
    SqlCommand cmd = connection.CreateCommand();
    cmd.CommandText = @"Insert into TB_LN_CASES (col1, col2,col3, ..) 
                        Values (@value1, @value2, @value3, ..) ";
    cmd.Parameters.Add(new SqlParameter("@value1", value1));
    cmd.Parameters.Add(new SqlParameter("@value2", value2));
    cmd.Parameters.Add(new SqlParameter("@value3", value3));

    cmd.ExecuteNonQuery();
}
Improve this answer

1 Comment

I will -1 this because there are good reasons not to use parameters in this - mostly that you can make a LOT of rows in one SQL insert but are limited to around 1000 parameters. Also it does work around the question and never even bothers to answer it (which is a pure sql issue).
-2 
string.Format("@"Insert into TB_LN_CASES{0},{1},{2}", col0,col1);
Improve this answer

2 Comments

what my question is, is there any constrain with string.Format() I don't want to correct my query by any other way
Check your answer. It doesn't address the question and fails anyway as the built statement is non-SQL compliant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.