In this section, we will learn how to use SqlDataSource to handle the database operations like insertion, updating, and deleting records. The operations are performed in the following steps.
Create a Web Site:
First create a web site in visual studio 2005 by using the option of New web site from the file menu as shown in the figure.
Design the Default.aspx page:
Now we will put some input boxes and button on the default.aspx page which will used to insert the data in the database as shown in the following figure.
As we can see, we have put two text boxes for entering Name and description and a button to save this entered information in the database.
Now create a test database file in which we store the information as shown in the figure. The database file will be created in the App_Data folder.
Now go to the server explorer and in the Data connections right click Mydatabase.mdf and select the option of modify connection as shown in figure
After selecting this option the following screen will appear.
Press the Test Connection and you will get the Test Connection Succeed Message. Now create the table as shown in the figure by selecting the Add new table.
Now create the table fields as shown in the below figure. There are three fields Id as primary key, name and description as varchar in this table. Remember that while primary key is selected, Change the property of “Is Identity” to Yes as encircle in the given figure.
At this point, we have created the form and database table for saving the input from the form. Now we will make the connection from the form to database using SqlDatasource. Now drag the SqlDatasource from the toolbox and click on Configure Data Source which will open the following screen. Select the MyDatabase.mdf in the connection and press the next button.
After pressing the next button, you will see the following screen. Press next again.
Now click the * and press the Advanced option. Now check the Generate, Insert…. Option and press ok and next and at the next screen press finish.
Now change the view of Default.aspx to source. Here you can see the Insert parameters as shown in the following figure
Now change this field to Form Fields as we need to take input from the text boxes which are form fields as shown in the following figure.
Now you can see that we have replaced the lines with form parameters. Now go to design view and double click the save button. In its handler write the following line as shown in figure.
It means that we on pressing the save button, the insert method of sqlDatasource will call and data will store in the database. Now run the application and enter name and description and press the save button as shown in the figure.
Now check the database table, the value is saved now as shown in the figure.
For editing and deleting we can so the same procedure and instead of calling SqlDataSource1.Insert(), we will call SqlDataSource1.Update() in the button handler. This time we also add new textbox in the form which take id as input also to tell the database which row, it should update and for deletion, we can use SqlDataSource1.Delete(). This is explained in the section below.
Now we are going to place a new text box on the form which takes id from the user and a search button which will search the results on the basis of given id in this new text box as shown in the figure.
To get the results on the basis of give id, we need to again configure the sqldataSource as shown in the following figure.
As you can see, In the wizard, we click on where which open a new form from which we have selected the id in column and source as control then textbox “txtId” as control id and press Add to add this parameter and then press ok and finish the wizard. In this step, we have added a new parameter to sql data source to tell that now fetch the results on the basis of given id.
Now we will write the code in the event handler of select button as shown in the following figure.
This code set the text boxes of name and description with the results which we get on the basis of given id now run the application and write a valid id in the text box and press select. The results will be displayed in the text fields.
At this point, we have done with insertion and selection, now we perform the update operation. Now add a new button of Update in the form as shown in the following figure.
Now change the view to source view, where you will see the update parameters now change these parameters to form parameters as shown in the following figure as we do with the insert parameters.
After it, double click the update button and write the following line in its event handler to handle the update operation
Now run the program by given a valid id into id text box and enter some information in the name and description which you want to modify for the given id and press the update button, the information will be updated now against the given id.
Now we perform the final operation delete. Put another button delete on the form and switch to source view. Change the parameter for delete as we do for insert and update as shown in the following figure.
Now go to design view, double click the delete button and add the following line in its event handler to delete the record.
Now run the program and test the delete functionality.
There is another way of updating and deleting the record and which is using of grid view control as explain in the give section.
Place a grid view control on the form and set its data source to sqlDatasource1 and check the enable editing and deleting check boxes as shown in the following figure.
In this tutorial, we have learnt how to connect to database using SQL data source to perform the database functions like insert, update and delete.
