Updating the Database using dataadapter's update method vs commands in sql server 2008

Question

Hi friends,

I have been using Data adapters update method for saving the changes to Database.usually I populate the table to the dataset and change occurred in the dataset is communicated back to the database using data adapters update method. for that I use SqlCommand builder which generate automatically all necessary commands such as DELETE,UPDATE,INSERT.

but Now I want to use stored procedures and triggers in the server side too. which seems to require separate DELETE,UPDATE,INSERT commands at the server side.  how I can use all these commands at a single  button click (here I want a sample C# code) ie  like dataadapter's update method it has to take care all operation happened in the dataset since it was updated last time . so that I can write trigger for these commands at the server side . 

I do not know which is the best approach 

Regards

Iqbal

---

itismeiqbal

Answer 1

Hi Iqbal,

You could write the Update record stored procedure at the Sql sever side and map this stored procedure with the SqlAdapter’s member method then you could use this method to trigger the stored procedure which you created in the Sql Server.

Please refer to the following steps:

1. Open the properties panel with the target Adapter in your <name>.xsd.
2. Expand the UpdateCommand node
3. Change the CommandType to StoredProcedure.
4. Select the stored procedure which you created at the SQL Server side from CommandText.

Also here is a screenshot for you:

Hope this could help you.

Best Regards,

---

Tony Xiao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Tony Xiao,

After long time I am getting a reply from you. happy to here you again

here , to use a TableAdapter , the computer in which I develop the code should have SQL server too . yes I have that . and during the development of the code I have to connect to the server through the visual studio IDI,which will generate  some connection parameter which is specific to the DataBase (in this case it is SQL server 2008 with some password and Login ID and sql authentication ) in the same system. and  when I create the executable file for this developed code  and install in some other PC which is connected to a server through the LAN , will the code work, if the server parameters such as password and login and instance name differ from the server in which I developed the code. because of this doubt only I develops code  which is linked to the server only programmatic-ally using connection string which   can be changed for any user login or password etc.

By doubt may be mundane or very elementary ,

hope I get a clarification 

Regards

Iqbal

---

itismeiqbal

Answer 3

Yes this will work.

It is suggested to store the connection string information on your app/web configuration file so that when your server information changes, you simple need to change that information on configuration file. To do that,

Step One

You can use the connectionStrings section inapp/ web.config file as below.

<connectionStrings>
<add name="myConnectionString" 
	connectionString="server=localhost;
	database=myDb;uid=myUser;
	password=myPass;" />
</connectionStrings>

Step Two

Read the connection string as below,

string connStr = ConfigurationManager.ConnectionStrings
		["myConnectionString"].ConnectionString;

Step Three

Assign the connection string from configuration file to your Table Adopter.

string connStr = ConfigurationManager.ConnectionStrings
		["myConnectionString"].ConnectionString;
myTableAdapter.Connection.ConnectionString = connStr;

NB.

1. ConfigurationManager should be accessible if you have reference to System.Configuration.dll assembly and you have using System.Configuration namespace.

---

Lingaraj Mishra