Re: update command in sqladapter - C# Discussion Boards

OriginalGriff6-Mar-14 4:39

6-Mar-14 4:39

The @name stuff is a parameter, and it allows you to transfer information from your C# code to SQL without converting it to string, or concatenating strings. This is important, because not using parameters can be very dangerous.
Suppose you have a string you want to update against an integer id:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("UPDATE myTable SET myColumn1='" + myNewString + "' WHERE Id=" + myId.ToString(), con))
        {
        com.ExecuteNonQuery();
        }
    }

Will work, but it leaves you wide open to something called SQL Injection Attacks, where I can use the value in the string to add commands to the UPDATE: if the myNewString variable contains

Hello';DROP TABLE myTable;--

Then what gets passed to SQl is a comannd string:

SQL

UPDATE myTable SET myColumn='Hello';DROP TABLE myTable;--' WHERE Id=666

Which as far as SQL is concerned is two valid SQL commands and a comment.
It updates every row in your table to "Hello", and then deletes the table.

Parametrized queries are a way to avoid that.

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("UPDATE myTable SET myColumn1=@MYSTRING WHERE Id=@ID", con))
        {
        com.Parameters.AddWithValue("@ID", myId);
        com.Parameters.AddWithValue("@MYSTRING", myNewString);
        com.ExecuteNonQuery();
        }
    }

Passes the same info to SQL, but the string value is now passed as a parameter and not interpreted as an SQL command at all.

It is a very, very good idea to use parameters at all times - it prevents other problems as well!

[edit]Typos - loads of typos... Blush | :O

[/edit]

Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)

Richard Deeming6-Mar-14 5:05

Richard Deeming

6-Mar-14 5:05

Have you just given your email address to a Danish member[^] who joined 11 years ago and has never participated since? D'Oh! | :doh:

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

OriginalGriff6-Mar-14 5:10

6-Mar-14 5:10

Sh*t d*mn b*gg*r f*ck.

Moneyzz Sharma6-Mar-14 5:35

Moneyzz Sharma

6-Mar-14 5:35

Very very thankful for your so clear explanation Smile | :)

Also please explain what here AND operations are doing, i will be very thankful to you Smile | :)

OriginalGriff6-Mar-14 6:29

6-Mar-14 6:29

AND is what is called a boolean operator, along with OR and it is used to combine two "tests" to make a bigger, more powerful test.

AND is true is both the test on the left and the text on the right are both true: so

Object is round AND object is blue

is true only for blue balls - it is false for red balls, blue cubes, green pyramids, ...

OR if true if one or the other of the left and right tests are true, and false only if they are both false:

Object is round OR object is blue

is true for blue balls, red balls, and blue cubes, but it is false for green pyramids.

In the case of SQL, you could use to to limit the number of rows SELECTED, or updated:

SQL

SELECT * FROM MyTable WHERE ID = 666 AND dateEntered > '2014-01-01'

Would only return rows from me this year.

Make sense?

Moneyzz Sharma6-Mar-14 8:14

Moneyzz Sharma

6-Mar-14 8:14

I completely agree with what you are saying but here i mean to ask that

UPDATE [empdata] SET [name] = @p1, [id] = @p2, [salary] = @p3 WHERE (([name] = @p4) AND ([id] = @p5) AND ([salary] = @p6))

here what @p4, @p5 , @p6 would constitute?
what if i changed all the three values i.e. name, id and salary, then how would it know which row am i asking to change?

OriginalGriff6-Mar-14 8:27

Translating an "error event" to a managed exception

6-Mar-14 8:27

OK...combine the two comments I've given you and you have the answer.

Let's go back a stage or so, and look at a simple WHERE clause.

SQL

SELECT * FROM MyTable WHERE MyColumn=Value

The WHERE clause is "MyCoumn=Value" and it evaluates to a boolean: true or false
If it is true then the row is SELECTed (in this case, it could be UPDATEd in a different statement, such as yours) If it is false then it isn't.

The simple boolean test here is in three parts: "X = Y" where

X   is saying which column of the row is to be compared
=   is saying the the left and right sides must be the same
Y   is the value to compare against the column

Working this back to your UPDATE, you have a three part WHERE clause, each part being of this form, and converted to a more complex test by AND to ensure that all three conditions must be true in order to update the row.

Each part is of the same form as my example above: "X = Y"

[name] = @P4
[id] = @P5
[salary] = @P6

If you change the names, it will check against different columns.
The part on the right is just the identifier of the parameter - it matches the name you use in your C# code to supply the actual value - look back at the first comment I made and it should be clear.

Bernhard Hiller5-Mar-14 4:17

Bernhard Hiller

5-Mar-14 4:17

I am creating a wrapper for a ThirdParty component. That component offers an event in case of an error:

public delegate bool ErrorOccurred(string id, string message);

Of course, I'd prefer a custom exception, and wrote a class for that.
But there is a catch: I must return true to ThirdParty to indicate that we took care of the error, otherwise ThirdParty is likely to crash...
I subscribed to that event, and in the handler I try to do:

protected bool Session_ErrorOccurred(string id, string message)
{
    throw new SASException(id, message);
    return true;
}

... but that doesn't work, of course.
I thought of throwing in a different thread (ThirdParty is multi-threaded anyway), but with

protected bool Session_ErrorOccurred(string id, string message)
{
    Thread t = new Thread(() => ThrowSASException(id, message));
    t.Start();
    return true;
}
private void ThrowSASException(string id, string message)
{
    throw new SASException(id, message);
}

the exception gets swallowed somewhere in nirwana.

What do you suggest?

Re: Translating an "error event" to a managed exception

Ravi Bhavnani5-Mar-14 4:25

Ravi Bhavnani

5-Mar-14 4:25

Perhaps your wrapper code could raise another event indicating an error occured and return true? That event's handler can choose to do whatever it wants (e.g. throw an exception). Not sure if this is a solution, but it may be worth trying.

/ravi

Re: Translating an "error event" to a managed exception

OriginalGriff5-Mar-14 4:38

Re: Translating an "error event" to a managed exception

5-Mar-14 4:38

That's nasty!

Damn good idea, but nasty all the same...don't think there is a tidy solution for this, so it gets my vote.

Ravi Bhavnani5-Mar-14 4:46

Ravi Bhavnani

5-Mar-14 4:46

OriginalGriff wrote:
That's nasty!

Agreed.

OK then, I suggest not throwing an exception and instead simply having the consumer of the wrapper subscribe to the wrapper's OnError() handler.

/ravi

Re: Translating an "error event" to a managed exception

OriginalGriff5-Mar-14 5:13

Re: Translating an "error event" to a managed exception

5-Mar-14 5:13

Trouble is...I'm not sure that even with an event the return will be executed at all times...if the exception isn't caught by anything, I think the app will crash. And I'm pretty sure that the exception will be immediately executed on the same thread and will pop the stack all the way back to the exception handler without ever hitting the instruction after the one raising the event...

This is nastier than I thought...I'm not sure there is any way to do this, short of a timer which is truly horrible!

BobJanova5-Mar-14 5:46

BobJanova

5-Mar-14 5:46

Griff is correct, this won't work – event handlers are called in the same thread, so if you re-fire an event and a delegate down the line throws an exception, your own handler code won't complete and you'll never return true to the library code.

This kind of thing is why it's usually considered a Bad Idea (TM) to throw exceptions in event handlers.

Re: Translating an "error event" to a managed exception

Shameel5-Mar-14 5:07

Shameel

5-Mar-14 5:07

I agree with Ravi's solution, it's not perfect but I can't think of a better solution.

If you have the source code of the Third Party component or if there is a way to get them to change the component for you, you can ask them to include a parameter to indicate if the error was handled rather than returning true/false. Something like this:

protected void Session_ErrorOccurred(string id, string message, bool handled)
{
    handled = true;
    throw new SASException(id, message);
}

This signature still violates the Framework Design Guidelines for Events. It should ideally be like this:

protected void Session_ErrorOccurred(object sender, ErrorEventArgs args)
{
    args.handled = true;
    throw new SASException(args.id, args.message);
}

modified 5-Mar-14 11:14am.

Re: Translating an "error event" to a managed exception

BobJanova5-Mar-14 5:53

BobJanova

5-Mar-14 5:53

This could get really ugly. You must allow the method to complete, so the library gets its result back, but you'd like the managed code to die with an exception as soon as an error occurs.

What is the exact conditions under which this delegate can get called? Is it that any call to third party library code could case OnError to be called, and then your code will continue afterwards?

I don't really see an alternative to

Library.CallToSomething();
if(ErrorWasRaised) throw new MyException("...");

Library.CallToSomethingElse();
if(ErrorWasRaised) throw new MyException("...");

... where ErrorWasRaised is instance state that's assigned from your OnError handler.

The only thing I can think of is to hide the ugliness in a utility method with a lambda:

void CallErrorHandlingLibraryCode(Action code) {
 string errorId = null, errorMessage = null;
 OnErrorHandler errorLogger = (id, message) => {
  errorId = id; errorMessage = message;
  return true;
 };
 Library.OnError += errorLogger;
 try {
  code();
  if(null != errorId)
   throw new SASException(errorId, errorMessage);
 } finally {
  Library.OnError -= errorLogger;
 }
}

Then your calling code looks like

CallErrorHandlingLibraryCode(() => Library.CallToSomething());
CallErrorHandlingLibraryCode(() => Library.CallToSomethingElse());

... which is arguably better looking depending on your taste in lambdas.

Exceptions are exceptional

Ennis Ray Lynch, Jr.5-Mar-14 8:58

Ennis Ray Lynch, Jr.

5-Mar-14 8:58

If you are able to catch the exception and understand when it could occur then you shouldn't throw it. What you are trying to do is notify on the event. If you were throwing an exception then it would be fine to now return true. I know, seems counter-intuitive since all of the MS sample code is littered with throw exceptions every where but if you design with that in mind, your world can be a much better place. I have a lot of third-party libraries were I would kill for the ability to not have an exception.

Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting.

"And they, since they Were not the one dead, turned to their affairs" -- Robert Frost

"All users always want Excel" --Ennis Lynch

Re: Translating an "error event" to a managed exception

jschell5-Mar-14 9:41

jschell

5-Mar-14 9:41

The concept is wrong.

An exception must propagate the call stack of a thread.

Presumably what you want is the following

public void Call3rdparty()
{
   try
   {
       Component.ErrorOccurred = MyErrorMethod;
       Component.CallMethod()
   }
   catch(SASException e)
   {

   }
}

The problem with the above is that there is no assurance that MyErrorMethod will be called in the same thread as Call3rdparty

This means that the call semantics are implicitly asynchronous and it must be handled that way.

I believe there are some handy classes in C#/Net that allows one to create a async call but it isn't that hard to write one yourself using a monitor (or whatever it is called), timeout and appropriate attributes. If creating it yourself
- Create a thread to call the method -
- Set a monitor, with timeout
- If thread returns then set a flag, and set monitor
- If error method is called set attribute that indicates error
- Once monitor clears (excluding timeout) then check attribute for error, if there is error then throw exception

Re: Translating an "error event" to a managed exception

Bernhard Hiller5-Mar-14 20:17

Bernhard Hiller

5-Mar-14 20:17

You are right. Thinking of the problem after my post, I came to a similar reasoning.
The error occurs somewhere in ThirdParty - though that process was somehow started through our code, it is quite un-coupled from it: we instantiate a class of their .Net SDK, call a Start method, which then communicates with ThirdParty server (that is on a different computer, could also be hosted in ThirdParty's cloud). On the server, the processing is started in a new thread, and the call to Start returns. When an error occurs in that new processing thread on the server, where could it be thrown to - it always travels the CallStack "upwards"? The Start method has long returned! Consequently, ThirdParty must use an event to communicate "downwards" to an "Observer". Hence I'll create a .Net event and raise that - then it is wrapped properly.

Please help me. don't working save button

bayaa4-Mar-14 23:03

bayaa

4-Mar-14 23:03

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
SqlConnection sc=new SqlConnection (@"Data Source=.\SQLEXPRESS; AttachDbFilename=phonebook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
SqlCommand cmd;
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void btnSave_Click(object sender, EventArgs e)
{
try
{
sc.Open();
cmd = new SqlCommand("Insert into emails(id, emails, pbid) values('" + txtId.Text + "','" + txtEm.Text + "','" + txtPbid.Text + "')",sc);
cmd.ExecuteNonQuery();
MessageBox.Show("Update Successfull to the database");
sc.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

}
}
}

Richard Deeming4-Mar-14 23:13

Richard Deeming

4-Mar-14 23:13

Start by fixing the SQL Injection vulnerability[^] in your code:

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        sc.Open();
        
        using (SqlCommand cmd = new SqlCommand("INSERT INTO emails (id, emails, pbid) VALUES (@id, @emails, @pbid)"))
        {
            cmd.Parameters.AddWithValue("@id", txtId.Text);
            cmd.Parameters.AddWithValue("@emails", txtEm.Text);
            cmd.Parameters.AddWithValue("@pbid", txtPbid.Text);
            cmd.ExecuteNonQuery();
            
            MessageBox.Show("Update Successfull to the database");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        System.Diagnostics.Debug.WriteLine(ex);
    }
    finally
    {
        sc.Close();
    }
}

If it still doesn't work, you'll need to post the error message. Look in the debug output in Visual Studio to get the full details.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Message Closed

6-Mar-14 3:15

FesAlex

6-Mar-14 3:15

Message Closed

Pete O'Hanlon6-Mar-14 3:32

Pete O'Hanlon

6-Mar-14 3:32

You do know that you need to reply to the original poster don't you? Richard doesn't need your help and the OP won't be informed that you have posted a reply because it's not against them.

thatraja6-Mar-14 3:59

thatraja

6-Mar-14 3:59

He's a spammer. The link from his signature & profile page

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute