none
Cannot write data to a custom View using LINQ to SQL RRS feed

  • Question

  • I am trying to write data to a View that I set up in SQL Server 2005 Std in a web form I created in Visual Studio 2008 Pro (using VB behind the scenes), and I am in need of some help.  I am able to write to a single table using the following code:

    Public strConn As String = "data source=[Server]; Initial Catalog=Contact_Management; User ID=[user] Password=[password]; Persist Security Info=True;packet size=4096"

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

       Dim cmd As New SqlCommand("INSERT INTO Customers (CustomerID, FirstName, LastName)VALUES('" & txtCustomerID.Text & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')", New SqlConnection(strConn))
       cmd.Connection.Open()
       cmd.ExecuteNonQuery()
       cmd.Connection.Close()

       LoadData()

    End Sub

    I followed an online tutorial to get to this point, so you may recognize some of those variables.  In the above example, I am accepting input into textboxes called txtCustomerID, txtFirstName and txtLastName and writing them to their respective columns CustomerID, FirstName and LastName in the Customers table.  I'm doing a few more things below the SQL command, but I don't think they are relevant to this issue.

    The problem is that I have created a view called Customer_Views, which includes approximately 10 tables and I want to write input directly to the view I created using one big SQL command instead of writing 10 separate SQL commands, and I assume this can be done with a View.

    For another section of the same website, I created a LINQ to SQL Class that connects to the Customer_Views and I am hoping to use a similar connection to write data to my View as I have to display data from the View.  The code I am using to display data from the view is as follows:

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim db As New Contact_ManagementDBDataContext()

        Dim q = From b In db.Customer_Views _
                Where b.FirstName.Contains(txtSearch.Text.Trim()) Or _
                      b.LastName.Contains(txtSearch.Text.Trim()) Or _
                      b.AddressLine1.Contains(txtSearch.Text.Trim()) Or _
                      b.CustomerID.Contains(txtSearch.Text.Trim()) Or _
                      b.Email.Contains(txtSearch.Text.Trim()) Or _
                      b.Email2.Contains(txtSearch.Text.Trim()) Or _
                      b.Email3.Contains(txtSearch.Text.Trim()) Or _
                      b.Fax.Contains(txtSearch.Text.Trim()) Or _
                      b.LocID.Contains(txtSearch.Text.Trim()) Or _
                      b.Phone.Contains(txtSearch.Text.Trim()) Or _
                      b.Phone2.Contains(txtSearch.Text.Trim()) Or _
                      b.Phone3.Contains(txtSearch.Text.Trim()) _
                Select b

        lv.DataSource = q
        lv.DataBind()


    End Sub

    Using LINQ to SQL is such a clean way to manage data, I would much prefer to be able to manage data to my View using this method than by manually establishing a connection to the SQL server as in the top example.  Can someone help me write data to my custom View using my LINQ to SQL connection (Contact_ManagementDBDataContect())?  It would be much appreciated.

    Thanks in advance,

    Tyler
    Tuesday, June 16, 2009 3:57 PM

All replies

  • Take a look at "instead of"-triggers:
    http://msdn.microsoft.com/en-us/library/ms175089.aspx


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Wednesday, June 17, 2009 1:54 PM
    Answerer
  • Your first code snippet is not only messy, but it makes you vulnerable to a SQL injection attack.

    You can do the same thing more easily and safely with LINQ to SQL as follows:

    var c = new Customer
    {
      CustomerID = txtCustomerID.Text,
      FirstName = txtFirstName.Text,
      LastName = txtLastName,Text
    }
    db.Customers.InsertOnSubmit (c);
    db.SubmitChanges();

    Joe

    Write LINQ queries interactively - www.linqpad.net
    Thursday, June 18, 2009 12:28 AM
    Answerer
  • Thank you both for the input.

    Joe, you're method for inserting the data is certainly more efficient and more secure.  Thanks for the tip.

    KristoferA, you were correct about the INSTEAD OF trigger.  After reading more about it, the trigger is what allows you to update a view's underlying tables.  Thanks again.  The problem I am now facing is that when I execute an insert command into the view, the underlying tables are being updadted, but the view is not.  Do you know what setting I need to change in order for the view to update with the tables?

    Tyler
    Friday, June 19, 2009 11:46 PM