StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A simple INSERT statement for SQL takes this basic form:</p> <pre><code>INSERT INTO [tablename] ( [column1], [column2], ... ) VALUES ( [value1], [value2], ...) </code></pre> <p>So, we obviously need to know about the database table you are using: what columns it has. We also need to know about the class: what properties it has. Finally, we need to know about the data types for the table columns and class properties, and how the properties will map to the columns. For very simple objects the names and types will just line up. But in other cases your class may itself contain a collection (or several) that would mean inserting data into more than one table.</p> <p>After all this is determined, we still need two things: connection information for the database (usually distilled down into a single <a href="http://www.connectionstrings.com/" rel="nofollow noreferrer">connection string</a>) and whether or not you are concerned that your class instance may have been saved previously, in which case you want to build an UPDATE statement rather than INSERT.</p> <p>Assuming you can answer all of that in a satisfactory manner, your VB.Net code will look something like this (of course substituting your specific column, property, type, and connection information where appropriate):</p> <pre><code>Public Class Customer Public Sub Save() DAL.SaveCustomer(Me) End Sub ' ...' End Class </code></pre> <p>.</p> <pre><code>' a VB Module is a C# static class' Public Module DAL Private ConnString As String = "Your connection string here" Public Sub SaveCustomer(ByVal TheCustomer As Customer) Dim sql As String = "" & _ "INSERT INTO [MyTable] (" & _ "[column1], [column2], ..." & _ ") VALUES (" & _ "@Column1, @Column2, ... )" Using cn As New SqlConnection(ConnString), _ cmd As New SqlCommand(sql, cn) cmd.Parameters.Add("@column1", SqlDbTypes.VarChar, 50).Value = TheCustomer.Property1 cmd.Parameters.Add("@column2", SqlDbTypes.VarChar, 1000).Value = TheCustomer.Property2 cn.Open() cmd.ExecuteNonQuery() End Using End Sub End Module </code></pre> <p>I know you've already heard that separating out your database code is the "right thing to do"tm, but I thought you might also want some more specific reasons why you would want to structure your code this way:</p> <ul> <li>Your connection string is kept in one place, so if your database server moves you only need to make one change. Even better if this is it's own assembly or config file.</li> <li>If you ever move to a completely different database type you only need to change one file to update the program.</li> <li>If you have one developer or a DBA who is especially good with sql, you can let him do most of the maintenance on this part of the app.</li> <li>It makes the code for your "real" objects simpler, and therefore easier to spot when you make a logical design error.</li> <li>The DAL code might eventually be re-usable if another application wants to talk to the same database.</li> <li>If you use an ORM tool most of the DAL code is written for you.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload