Re: SQL Parameterized Command versus Custom String

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Sam" <samf@xxxxxxxxxx> wrote in
news:u7k0jsSJKHA.1376@xxxxxxxxxxxxxxxxxxxx:

Hi Greg,

I am only new to C#
I would use the EXEC String
What do you mean by
Attaching the params is so much cleaner
Do you have a eg or link to eg

TIA

Samf


"Gregory A. Beamer" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx> wrote in
message news:Xns9C71998B0D0E2gbworld@xxxxxxxxxxxxxxxx
"henry.lee.jr@xxxxxxxxx" <henry.lee.jr@xxxxxxxxx> wrote in
news:3b006db4-0636-46eb-aefc-fc3a3c2ec369
@e34g2000vbm.googlegroups.com
:

Is there a way to see exactly what SQL the parameterized
command object is trying to execute so you can test it against SQL
Server?

SQL Profiler? That is the tool I would use in development. Just run
and watch the profiler spit out the incorrect SQL. ;-)

I don't like the way you are calling sprocs, however. Attaching the
params is so much cleaner than attempting to make an "exec" string.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************





string connString = ConfigurationManager
.ConnectionStrings["myConn"].ConnectionString;
string sql = "{stored proc name}";

SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(conn, sql);
command.CommandType = CommandType.StoredProcedure;

//Add parameters without EXEC (option 1)
command.AddWithValue("@paramname1", param1);

//Option 2 (short verison, consult help file for more
// explicit versions)
SqlParameter param = new SqlParameter("@paramname");
param.Vlaue = param2;

Calling in this way avoids the possibility of SQL injection and is much
smoother.

As for running a command, it is generally like this:

try
{
connection.Open();
//Run command here, one option is DataSet
adapter.Fill(myDataSet);
}
finally
{
connection.Dispose();
}

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
.

Relevant Pages

  • Re: Casting to VARCHAR(MAX)
    ... But when you do the EXEC, the statement the EXEC passes to SQL Server looks like ... So the string that gets put into #TEMP1 will be truncated to 8000 characters. ... that will force the whole expression concatenating the strings to return VARCHARand you would get the whole string inserted into the table. ...
    (microsoft.public.sqlserver.programming)
  • Re: Multi-line "Srchfor" Utility?
    ... specified on the command. ... for each EXEC CICS command, that literal is a bit string (arg 0 in the ... EXEC CICS GETMAIN, but beyond that I'd need to refer to the CICS Data ...
    (bit.listserv.ibm-main)
  • Re: Trying to insert textboxdata into database
    ... typed with a @ prefix in SQL server in Access? ... also another remark a textbox is returning a string so why convert a string ... Dim Projname, ProjSpon As String ... 'Create SQL Select query command ...
    (microsoft.public.dotnet.languages.vb)
  • Re: OfficeCodeBehind?
    ... The WHERE clause needs to be inserted into the SQL string definition in this ... ' Set up the command text: ... I am not sure though whether that will work directly on an SQL Server ...
    (microsoft.public.word.vba.customization)
  • Re: Insert Command
    ... Change your SQL Insert stmt to: ... then add three parameters to your command, one for each of the actual ... quoted string, and you won't have to worry about SQL Injection attacks from ... programmer helping programmers. ...
    (microsoft.public.dotnet.languages.vb)