Understanding LINQ SubmitChanges

Question

Greetings again mates,

My understand of Linq to SQL had improved a lot.

Once I understand what LINQ code is doing, I can generally rewrite the code to more "normal" .net code with real sql query.

I still have issues figuring out some linq to sql code.

For instance, I am having problem understanding what the following code is doing, specifically this:

AddressData insertAddress = new AddressData();

If one of you experts can help with the code below, I have several that written this way. This will really provide a basic blueprint for rewriting the rest of them.

            if (curAddress.Count() == 0)  
            {  
                // add the new address to the database  
                AddressData insertAddress = new AddressData();  
                insertAddress.Address = pInfo.StreetNumber.ToString() + " " + pInfo.StreetName + " " + pInfo.StreetType;  
                insertAddress.City = pInfo.CityCode;  
                insertAddress.State = "KS";  
                insertAddress.Zip = pInfo.ZipCode;  
                insertAddress.Toilets = (byte)pInfo.Baths;  
                insertAddress.WaterAcctNo = WaterAccountNo;  
                insertAddress.Replaced128 = insertAddress.Replaced160 = 0;  
                insertAddress.YearBuilt = (short)pInfo.YearBuilt;  
                if (installation.Checked == true) { insertAddress.Installation = true; insertAddress.Pin = pInfo.Pin; }  
                if (mailing.Checked == true) { insertAddress.Mailing = true; } else { insertAddress.Mailing = false; }  
                insertAddress.ReturnOnly = false;  
                cityLinq.AddressDatas.InsertOnSubmit(insertAddress);  
                cityLinq.SubmitChanges();  
                Session["iID"] = Session["mID"] = cityLinq.AddressDatas.SingleOrDefault(a => a.Address == pInfo.StreetNumber + " " + pInfo.StreetName + " " + pInfo.StreetType &&  
                    a.City == pInfo.CityCode && a.Zip == pInfo.ZipCode).AUTOID.ToString();  

As always, many thanks for your help in advance.

Answer 1

Okay I had a few minutes, here is a VB.NET version done in a Console project, the code in Program Main goes in a Button Click event.

Public Enum StreetType  
    Residential  
    Commercial  
End Enum  

---

Public Class Address  
    Public Property Id() As Integer  
    Public Property StreetNumber() As Integer  
    Public Property StreetName() As String  
    Public Property StreetType() As StreetType  
    Public ReadOnly Property StreetFull() As String  
        Get  
            Return $"{StreetNumber} {StreetName} {StreetType}"  
        End Get  
    End Property  
    Public Property City() As String  
    Public Property State() As String  
    Public Property ZipCode() As String  
    Public Property Toilets() As Integer  
    Public Property Replaced128() As Integer  
    Public Property YearBuilt() As Integer  
  
End Class  

---

Data operation

Public Class SqlOperations  
    Private Const ConnectionString As String = "TODO"  
  
    Public Shared Function Insert(ByVal address As Address) As (success As Boolean, exception As Exception)  
        Dim statement = <SQL>  
                            INSERT INTO myTable(Address,Toilets)     
                            VALUES(@address,@toilets);   
                            SELECT CAST(scope_identity() AS int);  
                        </SQL>.Value  
  
        Using cn = New SqlConnection(ConnectionString)  
            Using cmd = New SqlCommand With {.Connection = cn, .CommandText = statement}  
  
                cmd.Parameters.Add("@address", SqlDbType.NChar).Value = address.StreetFull  
                cmd.Parameters.Add("@toilets", SqlDbType.Int).Value = address.Toilets  
  
                Try  
                    cn.Open()  
  
                    address.Id = Convert.ToInt32(cmd.ExecuteScalar())  
                    Return (True, Nothing)  
                Catch ex As Exception  
                    Return (False, ex)  
                End Try  
            End Using  
        End Using  
    End Function  
End Class  

Calling code

Module Program  
    Sub Main(args As String())  
  
        Dim address As New Address() With {  
                .StreetNumber = 111,  
                .StreetName = "Pine St",  
                .StreetType = StreetType.Commercial,  
                .Toilets = 2  
                }  
  
        Dim result As (success As Boolean, exception As Exception) = SqlOperations.Insert(address)  
  
        If result.success Then  
            ' Insert successful and the Id is populated  
        Else  
            ' failed, examine exception  
        End If  
    End Sub  
  
End Module  

Answer 2

Ok got it.

Sorry, I assumed that anytime your code cannot be converted using the C# to vb.net converter and it doesn't work, it is because of error.

I apologize for that assumption.

When I tried to select a tag, I looked for vb tag but could not.

The code I posted is vb.

In any case, I am truly grateful for your assistance.

There is a very high level of elegance to your code.

Answer 3

Oh my goodness!

God bless you for your kindness.

Thank you so very much.

Even though it is done in Console program, I believe it works with my webform.

Many thanks @Karen Payne MVP .

Answer 4

Here are some recommendations (using C#11 so if using a lower version some adjustments are needed).

• DO NOT use AddWithValue, see the following
• Write data operations in a class as shown below
• Use exception handling as shown below

In regards to not knowing types for parameters, in SSMS, select the table and script table as an insert statement which shows the types and even easier, look at the table definition for the type. So Toilets seems that it would be defined as an int not byte

Since you did not provide the model, here is one to consider

public class Address  
{  
    public int Id { get; set; }  
    public int StreetNumber { get; set; }  
    public string StreetName { get; set; }  
    public StreetType StreetType { get; set; }  
    public string StreetFull => $"{StreetNumber} {StreetName} {StreetType}";  
    public string City { get; set; }  
    public string State { get; set; }  
    public string ZipCode { get; set; }  
    public int Toilets { get; set; }  
    public int Replaced128 { get; set; }  
    public int YearBuilt { get; set; }  
  
}  
  
public enum StreetType  
{  
    Residential,  
    Commercial  
}  

Slimmed down data operations

public class SqlOperations  
{  
    private const string ConnectionString = "TODO";  
  
    public static (bool success, Exception exception) Insert(Address address)  
    {  
        var statement = """  
            INSERT INTO myTable(Address,Toilets)   
            VALUES(@address,@toilets);  
            SELECT CAST(scope_identity() AS int);  
        """;  
  
        using var cn = new SqlConnection(ConnectionString);  
        using var cmd = new SqlCommand  
        {  
            Connection = cn,  
            CommandText = statement  
        };  
  
        cmd.Parameters.Add("@address", SqlDbType.NChar).Value = address.StreetFull;  
        cmd.Parameters.Add("@toilets", SqlDbType.Int).Value = address.Toilets;  
  
        try  
        {  
            cn.Open();  
  
            address.Id = Convert.ToInt32(cmd.ExecuteScalar());  
            return (true, null)!;  
        }  
        catch (Exception ex)  
        {  
            return (false, ex);  
        }  
    }  
}  

Slimmed down call

private void InsertButton_Click(object sender, EventArgs e)  
{  
    Address address = new()  
    {  
        StreetNumber = 111,   
        StreetName = "Pine St",   
        StreetType = StreetType.Commercial,   
        Toilets = 2  
    };  
  
    var (success, exception) = SqlOperations.Insert(address);  
    if (success)  
    {  
        // Insert successful and the Id is populated  
    }  
    else  
    {  
        // failed, examine exception  
    }  
}  

Answer 5

@Karen Payne MVP , thank you so much.

Your solution is really best suited for what I am trying to do.

There appears to be an error though with public class SQLOperations and InsertButton_Click()

As you can tell from my code, I am using vb and I am trying to convert your code to vb those two classes could not be converted.