Rajkm

Hi frends,

I am new to dotnet. I am developing an ASP.NET application using C#.

I want to perform database related commands like inserting, update and delete.

What is the best way to do these operations. I have many tables in the database.

For every insert, I want to develop common method to do insert(for any table, from any form).

Coz removing the same statements at many places.

As the different tables will have different fields. How can I do this

Pls reply me.'

Thanks in advance



Re: .NET Framework Data Access and Storage Inserting records, updating

Bar?? ?eker

If you don't want to write an individual update, insert clause for every data change, SqlCommandBuilder class will come in very handy.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

- Write your SQL Select statements

- Create the UPDATE, INSERT and DELETE statements by using SqlCommandBuilder

- Make your changes on your DataTable

- Use the enterprise library UpdateDataSet command to commit all your changes to the database

This example uses stored procedures instead of the SqlCommandBuilder:

http://www.codeplex.com/entlibcontrib/Wiki/View.aspx title=Extended%20SQL%20Data%20Access%20Block&referringTitle=Home





Re: .NET Framework Data Access and Storage Inserting records, updating

ahmedilyas

your probably better to use Stored Procedures as they are faster, securer and only require parameters to be given to it as the T-SQL is already on the database end, so your code looks cleaner and is easier to manage on both the application and database levels.






Re: .NET Framework Data Access and Storage Inserting records, updating

Baba urf Sivaji


U can approach stored procedures which provides security

Thank u
Baba





Re: .NET Framework Data Access and Storage Inserting records, updating

Avinob Roy

hii '

As sayed stored procedure is the best way as it provides security else u can also implement as making a command class and create methods in it to have being use to create ur commands in that and when ever u need that just create a object of it. but again remmember as the code will travel on the network it's not safe so if u consern about security dont go for it if u decide then u proceed like this

class command

{

public String insert(string tablename,string field1,string data)

{

string query ;

query ="insert into" + tablename + "(' + field1 +") values ( " + data + ")";

return query;

}

}

public usestring

{

String query;

command obj1=new command();

query= obj1.insert(xyz,pid,1001);

}

simmilarly u can go update delete comand and make a connection class and pass this string to that....!






Re: .NET Framework Data Access and Storage Inserting records, updating

timvw

Avinob Roy wrote:
string query ;
query ="insert into" + tablename + "(' + field1 +") values ( " + data + ")";

return query;


Talking about security, this is the easiest way to make something insecure...

At least use parameter binding to insert the data correctly into the query ( both for CommandText as StoredProcedure):

Code Snippet

cmd.CommandText = "INSERT INTO table (field1) VALUES (@data)";
cmd.Parameters.Add("@data", SqlDbType.String).Value = data;










Re: .NET Framework Data Access and Storage Inserting records, updating

Avinob Roy

Can u tell me the differnce what u have done and what i. the code u & i have written will form the query on clientside and not at the database . i dont think this hardly make ur code more secure it's just another way to write the code or i should say in a better way.






Re: .NET Framework Data Access and Storage Inserting records, updating

ahmedilyas

no, you are incorrect. it is securer using parameterized queries and it is in a better way.

using parameterized queries reduces the SQL Injection attacks as it will "SET" the value of a field to the parameter which has been supplied by a parameter rather than executing the entire SQL command where the user can attack the system.






Re: .NET Framework Data Access and Storage Inserting records, updating

Bar?? ?eker

Stored procedures faster and securer as opposed to what





Re: .NET Framework Data Access and Storage Inserting records, updating

ahmedilyas

Than executing a string concatinated query and having SQL compile it on the fly.






Re: .NET Framework Data Access and Storage Inserting records, updating

Matt Neerincx

Perhaps we should explain all the options with ADO.NET more clearly.

With ADO.NET you can perform INSERT statements several different ways:

1. Create a INSERT statement by concatenating a bunch of strings and execute it (using SqlCommand object).

2. Create a parameterized INSERT statement and execute it (using SqlCommand as well).

3. Use SqlDataAdapter and DataSet and let SqlDataAdapter automatically run INSERT for you.

For option 3 I am actually fudging a bit, to get SqlDataAdapter to do the INSERT for you, you need to provide it with some extra information (either manually or automatically using SqlCommandBuilder) so it knows how to do the INSERT.

Option #1 above is not recommended for security reasons as it allows for a SQL injection attack, so always avoid #1.

You can also create a stored procedure on the server side that performs the data operations for you, then use ADO.NET (either the SqlCommand directly or via SqlDataAdapter) to push the data to the stored procedure.

If you have to perform lots of inserts across lots of tables, this all becomes a bit unwieldy with ADO.NET, and so using the SqlDataAdapter and SqlCommandBuilder make this work much easier. SqlCommandBuilder automatically examines the table metadata and builds what it thinks is the proper INSERT statement on the fly. So note that SqlCommandBuilder is slightly less efficient because it has to fetch metadata from the table before creating the INSERT statement.

You should probably take a weekend and pick up a good book on ADO.NET and read over the options in more details before you get started coding to ensure you start off on the right track.






Re: .NET Framework Data Access and Storage Inserting records, updating

Bar?? ?eker

SqlCommandBuilder creates paramaterized queries, not concatenated.

If you have a good SELECT clause (avoid using *, get only the fields you need) it is pretty successful at creating the INSERT, DELETE and UPDATE statements for you.

The generated statements are cleaner if you use these options in bold:

Code Snippet

SqlDataAdapter sda = new SqlDataAdapter();

DbCommand cmd = db.GetSqlStringCommand(selectStatement);

sda.SelectCommand = (SqlCommand)cmd;

SqlCommandBuilder builder = new SqlCommandBuilder(sda);

builder.ConflictOption = ConflictOption.OverwriteChanges;

builder.SetAllValues = false;