Note: Please vote and give your feedback on this series so that future efforts can be improved / aligned with your expectation. Thanks!
In this article, we will cover insert operation and impact of .dbml on web.config. Frankly, figuring out insert operation took some time; it is bit tricky as compared to SqlDataSource. I hope this explanation will be useful.
In part III, we saw how to work with GridView itemtemplate to enable delete option. Here we will add one more button to the collection of buttons [
as shown in figure 13 –part III]. We will add insert code in the click event of the Insert button as shown in figure 1
Figure 1
Add the following code in btnInsert click event as shown below in figure 2
Figure 2
As shown in figure 2 - step 1, this is a btnInsert click event. We defined lstNewCustomer ListDictonary from System.Collections.Specialized namespace. In figure 2 - step 2, we got four placeholders mapped against the column name of the customer table. [Refer to figure 1 –part 3 for the schema of customer table].In figure 2 - step 3, we got the values for these columns from grid view footer row. Step 3 will add a new record via Grid View footer row. [
This is a good explanation on “Use the Grid View to insert a new record”]. Although
this post is based on SqlDataSource, conceptually, the process of working with LinqDataSource is identical. Replace the code in Button1_Click event of this post with the one shown in figure 2 above.
Another good resource on how to insert a new record using Grid View can be found here
In figure 2 -step 4, we will add input values to ListDictonary lstNewCustomer. Be careful with the ListDictonary key-value collection where key is the column name and value will be the column value to insert.
lstNewCustomer ListDictonary will be used to insert a record through LinqDataSource as LinqDataSource1.Insert(lstNewCustomer) and then we will rebind the Grid View as GridView1.DataBind(). After the record is inserted and Grid View repopulated, we will clear the input textboxes as shown in figure 2 -step 5. Figure 3 will walk you through the insert record lifecycle
Figure 3
As shown above, figure 3.A represents an empty Grid View footer row. 3. B is the row with values ready for insertion. Click on Insert button to insert this record. This will trigger the btnInsert click event as shown in figure 2. As a result of this event, a record will be inserted in customer table.
3. C is the updated Grid View with record inserted in the underlying table. Also, we have cleared the footer row after successful insertion of a record as a result of code shown in figure 2 – step 5.
With this we covered how to edit, update, delete and insert a record using GridView and LinqDataSource. Now is the time to focus on web.config and figure out the underlying database connection plumbing. Did you think about how LINQ to SQL is really talking to a customer table? Where is the database connection string? If yes, you are heading in right direction. If not, this is the time to do so.
Well the code explained from part I through this article will work on your local box / laptop or any machine on which integrated security option is set to true. Refer to figure 4 for more details
Figure 4:
This connectionstring is a result of drag-n-drop we did in
part I. [dragging a customer table on .dbml page from server explorer]. If you open the [filename].designer.cs file under your .dbml file – you will find a code like one shown in figure 6.
Figure 5:
As shown in figure 5, .dbml file is named as Customer.dbml . The code in figure 6 is from Customer.designer.cs file
Figure 6:
Compare figure 4 with 6 and focus on connectionstring property in figure 4 with figure 6.1 [underlined code]. This is how LinqDataSource is talking to the underlying database. Code in figure 6.2 explains how the customer table is mapped against the partial class customer.
This code will fail where integrated security is set to false and needs username and password to connect to the database. This is a gotcha that can be fixed by adding username and password to the connection string instead of using integrated security.
Ton load of ORM code is generated by compiler for free. If you really want to explore the power of LinqDataSource, start digging through this auto generated code. You will find many hidden secretes that no book or article will explain. I am on my way there, and will report back any interesting findings.
Till then have a great, productive programming!
