Regarding using the SQL Server stored procedure... What is the best way (Prefferd) to use, put all sql commnads as a stored procedure, and when they are needed just to call the stored procedure (View code 1)? or just to put a complex sql syntax - commnads
- in a stored procedure, and all other simple sql commnads (Like: select * from Table1) to call as below (Code 2):
Code 1:
using (SqlConnection conn = new SqlConnection(Tools.GeneralConnectionString))
{
SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch
{
conn.Close();
}
}
Code 2:
using (SqlConnection conn = new SqlConnection(Tools.GeneralConnectionString))
{
SqlCommand cmd = new SqlCommand("select * from Table1", conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch
{
conn.Close();
}
}
Depends on what you want. We use stored procedure to insert and update & to do complex select statements (like search queries). But for simple select queries, we use queries directly. Both have their own advantages & based on your need, you can use either.
For example, in complex search queries, we may create a temp table and save search result rows to it. In this case, stored proc should be much more ideal. But if you want to select 10 to 15 rows based on user input values (on the fly), then direct query
should be easier to handle.
Please mark as answered if this answer helped you.
Shankar
Marked as answer by Bader on May 02, 2012 11:59 AM
Regarding using the SQL Server stored procedure... What is the best way (Prefferd) to use, put all sql commnads as a stored procedure, and when they are needed just to call the stored procedure (View code 1)? or just to put a complex sql syntax - commnads - in
a stored procedure, and all other simple sql commnads
the preferred would be to use stored procedures for most cases...
even if you use sql queries directly into asp.net code... even then u should avoid to pass parameters inside query as a string... like this
"select * from tableName where columnname='" + txtName.Text + "'"
this can lead to sql injection attacks (refer this)
The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:
They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.
You can create a stored procedure once, store it in the database, and call it any number of times in your program.You can perform an operation that requires hundreds of lines of T-SQL code through a single
statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
Placing SQL in your code:
While stored procedures offer a number of benefits, placing SQL directly in your application code isn't necessarily wrong. One excellent example is SQL that is generated on the fly. Its dynamic nature negates many of the advantages
of a stored procedure.
That is, you cannot create and cache an execution plan since it's different each time it's called. In this scenario, you face assembling the SQL in your code or using the T-SQL exec command (or the sp_executesql system stored procedure).
The difference with this scenario is where the code is executed (i.e., the server), so the final decision will depend on your environment.
Another situation in which you might have to decide between constructing dynamic T-SQL in your application or on the database server is the passing of data to the stored procedure. The dynamic nature of the database call implies
that you must base your decision on data values and so forth. For instance, do you want to pass data values to a backend stored procedure, or use the values in the application code to construct the T-SQL string?
Marked as answer by Bader on May 02, 2012 11:58 AM
Bader
Member
10 Points
84 Posts
Asp.net and SQL server - Stored procedure
May 02, 2012 11:10 AM|LINK
Hi,
Regarding using the SQL Server stored procedure... What is the best way (Prefferd) to use, put all sql commnads as a stored procedure, and when they are needed just to call the stored procedure (View code 1)? or just to put a complex sql syntax - commnads - in a stored procedure, and all other simple sql commnads (Like: select * from Table1) to call as below (Code 2):
Code 1:
using (SqlConnection conn = new SqlConnection(Tools.GeneralConnectionString)) { SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn); try { conn.Open(); cmd.ExecuteNonQuery(); } catch { conn.Close(); } }Code 2:
using (SqlConnection conn = new SqlConnection(Tools.GeneralConnectionString)) { SqlCommand cmd = new SqlCommand("select * from Table1", conn); try { conn.Open(); cmd.ExecuteNonQuery(); } catch { conn.Close(); } }Regards,
Bader
Shankar_ss
Participant
1270 Points
279 Posts
Re: Asp.net and SQL server - Stored procedure
May 02, 2012 11:18 AM|LINK
Depends on what you want. We use stored procedure to insert and update & to do complex select statements (like search queries). But for simple select queries, we use queries directly. Both have their own advantages & based on your need, you can use either.
For example, in complex search queries, we may create a temp table and save search result rows to it. In this case, stored proc should be much more ideal. But if you want to select 10 to 15 rows based on user input values (on the fly), then direct query should be easier to handle.
Shankar
kedarrkulkar...
All-Star
34545 Points
5554 Posts
Re: Asp.net and SQL server - Stored procedure
May 02, 2012 11:21 AM|LINK
the preferred would be to use stored procedures for most cases...
even if you use sql queries directly into asp.net code... even then u should avoid to pass parameters inside query as a string... like this
"select * from tableName where columnname='" + txtName.Text + "'"
this can lead to sql injection attacks (refer this)
http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET
in either cases, parameterised query is essential
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
mishra.bhupe...
Participant
1596 Points
378 Posts
Re: Asp.net and SQL server - Stored procedure
May 02, 2012 11:24 AM|LINK
http://www.techrepublic.com/article/determine-when-to-use-stored-procedures-vs-sql-in-the-code/5766837
The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:
You can create a stored procedure once, store it in the database, and call it any number of times in your program.You can perform an operation that requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
Placing SQL in your code:
While stored procedures offer a number of benefits, placing SQL directly in your application code isn't necessarily wrong. One excellent example is SQL that is generated on the fly. Its dynamic nature negates many of the advantages of a stored procedure.
That is, you cannot create and cache an execution plan since it's different each time it's called. In this scenario, you face assembling the SQL in your code or using the T-SQL exec command (or the sp_executesql system stored procedure). The difference with this scenario is where the code is executed (i.e., the server), so the final decision will depend on your environment.
Another situation in which you might have to decide between constructing dynamic T-SQL in your application or on the database server is the passing of data to the stored procedure. The dynamic nature of the database call implies that you must base your decision on data values and so forth. For instance, do you want to pass data values to a backend stored procedure, or use the values in the application code to construct the T-SQL string?