store procedure or sql command
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
Using a stored procedure is generally considered better. The SQL Server can create an execution plan which speeds the execution up. You also get built in protection against SQL injection attacks. Whatever you do don't use an SQL string statement like this (without parameters):
string sql = "Select * from Table where name = " + idTextBox.Text;
as a hacker can type in SQL to the text box
idTextBoxand manipulate your DB, this is called SQL injection. Because Stored Procedures use parameters anyway, they are protected against this. Finally, I'd suggest looking at the following: LinqToSql, The Entity Framework, NHibernate. These map C# objects to relational databases, you will find that this removes the need for a lot of boilerplate code. It looks like you have a new development, so using these technologies will probably be better, but YMMV. -
Using a stored procedure is generally considered better. The SQL Server can create an execution plan which speeds the execution up. You also get built in protection against SQL injection attacks. Whatever you do don't use an SQL string statement like this (without parameters):
string sql = "Select * from Table where name = " + idTextBox.Text;
as a hacker can type in SQL to the text box
idTextBoxand manipulate your DB, this is called SQL injection. Because Stored Procedures use parameters anyway, they are protected against this. Finally, I'd suggest looking at the following: LinqToSql, The Entity Framework, NHibernate. These map C# objects to relational databases, you will find that this removes the need for a lot of boilerplate code. It looks like you have a new development, so using these technologies will probably be better, but YMMV.string sql = "Select * from Table where name = " + idTextBox.Text;when i have an update, i was using the parameter that they are protected against SQL injection i asked this question for example initial datagrid view in form laod, so what is your idea? -
string sql = "Select * from Table where name = " + idTextBox.Text;when i have an update, i was using the parameter that they are protected against SQL injection i asked this question for example initial datagrid view in form laod, so what is your idea?This is still wide open to a SQL Injection attack. Just because it's a select statement, doesn't stop it from having a SQL Injection attack attached to it.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
string sql = "Select * from Table where name = " + idTextBox.Text;when i have an update, i was using the parameter that they are protected against SQL injection i asked this question for example initial datagrid view in form laod, so what is your idea?Try this:
idTextBox.Text = "Jones;DROP TABLE Table";
string sql = "Select * from Table where name = " + idTextBox.Text;Or better still, don't try it - just think about it. Can the user type that into idTextBox? Oh yes! And what happens to your database? X|
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
Try this:
idTextBox.Text = "Jones;DROP TABLE Table";
string sql = "Select * from Table where name = " + idTextBox.Text;Or better still, don't try it - just think about it. Can the user type that into idTextBox? Oh yes! And what happens to your database? X|
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
I have a confession to make, sometimes I open your profile just to see the picture :)
-
I have a confession to make, sometimes I open your profile just to see the picture :)
:laugh: I'm not sure whether to be flattered that you like it, or disturbed that you felt a need to say it! :-D
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
:laugh: I'm not sure whether to be flattered that you like it, or disturbed that you felt a need to say it! :-D
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
That's exactly the effect I was looking for :)
-
That's exactly the effect I was looking for :)
Glad to have been of assistance then!
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
- I haven't written a stored procedure since I started using ADO.net. Before that, with ODBC, I had to write stored procedures in order to have transactions -- that was the only reason we used stored procedures. 1) Stored procedures are not inherently faster than SQL in code.[^] 2) Stored procedures offer some benefits, but I doubt most people capitalize on them (I wouldn't). 3) If you use stored procedures, you still need to have SQL (EXEC statements basically) in code anyway. 4) Most people write stored procedures simply because someone else said they should, not because they know what they're doing. 5) A properly-developed Data Access Layer may remove any need for stored procedures. If there is a need, then stored procedures may be used by the DAL and such details will be hidden from the user. 6) Stored procedures are fragile. 6.1) I have had stored procedures "just disappear" (that was with SQL Server 6, I doubt the problem still exists, but it makes me nervous). 6.2) Stored procedures are too easy to change in the field. All code changes should be in source control, properly built, and deployed to the field in a controlled manner. SQL in code accomplishes that. As mentioned, you absolutely must use parameterized statements. I think one reason people insist on stored procedures is because they force the developer to use parameters. But inline SQL can (and should) use parameters and a well-disciplined developer will have no trouble doing so. I have written several families of classes that make data access with parameters a no-brainer. Here[^]'s one.
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
Always Stored Procedure because it will provide protection from SQL injection attacks, it enable conditional and procedural logic, it provide better management of Compilation and storing or Execution plan it enable of security model and stored procedure code is a lot more readable :laugh: :laugh:
modified on Wednesday, October 27, 2010 2:35 AM
-
Always Stored Procedure because it will provide protection from SQL injection attacks, it enable conditional and procedural logic, it provide better management of Compilation and storing or Execution plan it enable of security model and stored procedure code is a lot more readable :laugh: :laugh:
modified on Wednesday, October 27, 2010 2:35 AM
RaviRanjankr wrote:
it will provide protection from SQL injection attacks
Not true.
RaviRanjankr wrote:
it enable conditional and procedural logic, it provide better management of Compilation and storing or Execution plan it enable of security model and Static stored procedure code is a lot more readable
True.
RaviRanjankr wrote:
:laugh: :laugh:
Uh... :-O :zzz: :-\ :rolleyes: :thumbsup::thumbsdown:
-
- I haven't written a stored procedure since I started using ADO.net. Before that, with ODBC, I had to write stored procedures in order to have transactions -- that was the only reason we used stored procedures. 1) Stored procedures are not inherently faster than SQL in code.[^] 2) Stored procedures offer some benefits, but I doubt most people capitalize on them (I wouldn't). 3) If you use stored procedures, you still need to have SQL (EXEC statements basically) in code anyway. 4) Most people write stored procedures simply because someone else said they should, not because they know what they're doing. 5) A properly-developed Data Access Layer may remove any need for stored procedures. If there is a need, then stored procedures may be used by the DAL and such details will be hidden from the user. 6) Stored procedures are fragile. 6.1) I have had stored procedures "just disappear" (that was with SQL Server 6, I doubt the problem still exists, but it makes me nervous). 6.2) Stored procedures are too easy to change in the field. All code changes should be in source control, properly built, and deployed to the field in a controlled manner. SQL in code accomplishes that. As mentioned, you absolutely must use parameterized statements. I think one reason people insist on stored procedures is because they force the developer to use parameters. But inline SQL can (and should) use parameters and a well-disciplined developer will have no trouble doing so. I have written several families of classes that make data access with parameters a no-brainer. Here[^]'s one.
Stored procedures can help reduce network traffic, as the EXEC statements are typically shorter than the statements in the SP's. Naturally, this depends on if network traffic is an issue at all (e.g., if the SQL runs on the same machine as the C#, it wouldn't be an issue... it also wouldn't be an issue of the data being transferred dwarfs the SQL code size). DLL's are just as fragile as stored procedures. At my previous job, we had to work with a system that had hundreds of DLL's and OCX's. The systems constantly got out of sync. SP's have one advantage in this respect... you fix them in one location (the database) and they are fixed everywhere. That's kinda nice (at least for systems where there is a primary database server). The other advantage/disadvantage is that you can see the stored procedure code, while it is difficult to see DLL code (without, say, Reflector). That means that you can easily check if the wrong code is in use, but it also means its more tempting for others to modify it on the spot without bothering with version control.
PIEBALDconsult wrote:
I have had stored procedures "just disappear"
SP's should be kept under version control. Sadly, the tools to do this are not excellent. Though, I remember a tool from Redgate under development that is supposed to help with this.
PIEBALDconsult wrote:
Stored procedures are too easy to change in the field.
Shhh, don't tell this to Microsoft... they might think making things "too easy" is a bad idea. ;P Depending on the scenario, this may actually be a good thing. Rather than trying to deploy the code to each computer that uses it, you can simply update the SP's on the server and you're done. This also brings up security concerns. It can be difficult or impossible to control security if you allow the clients to run arbitrary SQL statements. If you have stored procedures, you can control exactly what executes on the server. This is a very important point, as it may be the single greatest advantage stored procedures have over plain SQL code.
PIEBALDconsult wrote:
one reason people insist on stored procedures is because they force the developer to use parameters
string firstName = "'; DROP TABLE Whatever; --";
string cmd = "EXEC SelectData '" + firstName + "'";Both stored procedures and plain SQL are susceptible to SQL injection.
-
Stored procedures can help reduce network traffic, as the EXEC statements are typically shorter than the statements in the SP's. Naturally, this depends on if network traffic is an issue at all (e.g., if the SQL runs on the same machine as the C#, it wouldn't be an issue... it also wouldn't be an issue of the data being transferred dwarfs the SQL code size). DLL's are just as fragile as stored procedures. At my previous job, we had to work with a system that had hundreds of DLL's and OCX's. The systems constantly got out of sync. SP's have one advantage in this respect... you fix them in one location (the database) and they are fixed everywhere. That's kinda nice (at least for systems where there is a primary database server). The other advantage/disadvantage is that you can see the stored procedure code, while it is difficult to see DLL code (without, say, Reflector). That means that you can easily check if the wrong code is in use, but it also means its more tempting for others to modify it on the spot without bothering with version control.
PIEBALDconsult wrote:
I have had stored procedures "just disappear"
SP's should be kept under version control. Sadly, the tools to do this are not excellent. Though, I remember a tool from Redgate under development that is supposed to help with this.
PIEBALDconsult wrote:
Stored procedures are too easy to change in the field.
Shhh, don't tell this to Microsoft... they might think making things "too easy" is a bad idea. ;P Depending on the scenario, this may actually be a good thing. Rather than trying to deploy the code to each computer that uses it, you can simply update the SP's on the server and you're done. This also brings up security concerns. It can be difficult or impossible to control security if you allow the clients to run arbitrary SQL statements. If you have stored procedures, you can control exactly what executes on the server. This is a very important point, as it may be the single greatest advantage stored procedures have over plain SQL code.
PIEBALDconsult wrote:
one reason people insist on stored procedures is because they force the developer to use parameters
string firstName = "'; DROP TABLE Whatever; --";
string cmd = "EXEC SelectData '" + firstName + "'";Both stored procedures and plain SQL are susceptible to SQL injection.
aspdotnetdev wrote:
Stored procedures can help reduce network traffic
Yes, but mine never go across a network so that argument has no weight for me.
aspdotnetdev wrote:
you fix them in one location (the database) and they are fixed everywhere
Not if you have many clients each with his own database. And you should be putting the code in a version control system, that makes at least two places.
aspdotnetdev wrote:
It can be difficult or impossible to control security if you allow the clients to run arbitrary SQL statements.
That's what a DAL is for.
aspdotnetdev wrote:
If you have stored procedures, you can control exactly what executes on the server.
You can. But do you? It's much easier with compiled code.
aspdotnetdev wrote:
Both stored procedures and plain SQL are susceptible to SQL injection.
Huh, OK.
-
i have a project in c# code and 3 leyer; and all of data base code are in data leyer, which method is better? 1- send sql string statementto data base for select or update? 2 - using stored procedure? in databases with larg scale wich is better? pleas help me?!
use store procedures in database because if you have any problem, you only needs change the proc to resolve
