Making an Insert Statement - Windows Vb.Net Application
I am making windows apps at vb.net. I have a client object that contains a save method. how can i generate an insert request?
I need to store an object in a relational database (SQL server). I need to know what is the correct way to do the insert, i.e. Inside the save method, I wrote a SQL statement to save the object. Is it correct?
thank
A simple SQL INSERT statement takes this basic form:
INSERT INTO [tablename] ( [column1], [column2], ... ) VALUES ( [value1], [value2], ...)
So, we obviously need to know about the database table in use: 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, as well as how properties will map to columns. For very simple objects, the names and types will only match. But in other cases, your class may itself contain a collection (or several), which means inserting data into multiple tables.
Once this is all defined, we still need two things: the connection information for the database (usually distilled into one line) and whether or not you are really concerned that the class instance may have been saved earlier, and in this case you want to create an UPDATE statement, not an INSERT.
Assuming you can answer all of this in a satisfactory way, your VB.Net code would look something like this (substituting the appropriate column, property, type, and connection information, of course):
Public Class Customer
Public Sub Save()
DAL.SaveCustomer(Me)
End Sub
' ...'
End Class
...
' 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
I know you've already heard that splitting your database code is the "right thing", but I thought you might also want some more specific reasons why you would like to structure your code like this:
- The connection string is kept in one place, so if your database server is moving, you only need to make one change. Even better if it's a native assembly or config file.
- If you are moving to a completely different type of database, you only need to change one file to update the program.
- If you have one developer or DBA who is particularly good at sql, you can let him do most of the maintenance on that part of the application.
- It makes the code for your "real" objects simpler, and therefore easier to spot when you make a logical design mistake.
- DAL code can eventually be reused if another application wants to talk to the same database.
- If you are using an ORM tool, most of the DAL code is written for you.
source to share
There are several questions here. First, where exactly do you store it? You say SQL, but it's SQL Server, SQL Express instance, local data cache (SQL CE 3.5), or save via web service to talk to your SQL SERVER. These different data sources have different connection options / requirements, and in the case of SQL CE, there are several other "gotchas" involved in SQL itself.
Second, are you sure you want to store the data in a relational data store like SQL Server? Think you could use XML, a data file (text, CSV, etc.), or even a custom binary type.
Since you are working on a Windows application, you have many options about where and how to save data. Until you know where you want to put the data, it will be difficult for us to help you with this.
source to share
I agree with Mike Hofer. Keeping your class looking for and keeping an object separate from your business classes is key to a flexible and robust design. This is the code you want to see in your GUI or Business Layer:
//Populate Customer Objects List with data
IList<Customer> customerList = new List<Customer>()
Customer newCustomer1 = new Customer();
newCustomer.Name = "New Name"
newCustomer.email ="abcd@abcd.com"
customerList.Add(newCustomer1)
//DAL calls
DataAccessClass dalClass = new DataAccessClass ();
dalClass.InsertCustomers(customerList);
Inside the DALClass there should be a method called InsertCustomers (IList clients) and it should have the following code:
Public Function InsertCustomers(ByVal objectList As IList(Of Customer)) As Integer
Dim command As IDbCommand = Nothing
Dim rowsAffected As Integer = 0
Dim connection As IDbConnection = New System.Data.SqlClient.SqlConnection(Me.ConnectionString)
Try
connection.Open
Dim e As IEnumerator = objectList.GetEnumerator
Do While e.MoveNext
command = connection.CreateCommand
command.CommandText = "insert into dbo.Customer(CustomerID,CustomerGUID,RegisterDate,Password,SiteID,Las"& _
"tName,FirstName,Email,Notes,BillingEqualsShipping,BillingLastName) values (@Cust"& _
"omerID,@CustomerGUID,@RegisterDate,@Password,@SiteID,@LastName,@FirstName,@Email"& _
",@Notes,@BillingEqualsShipping,@BillingLastName)"
System.Console.WriteLine("Executing Query: {0}", command.CommandText)
Dim paramCustomerID As IDbDataParameter = command.CreateParameter
paramCustomerID.ParameterName = "@CustomerID"
command.Parameters.Add(paramCustomerID)
Dim paramCustomerGUID As IDbDataParameter = command.CreateParameter
paramCustomerGUID.ParameterName = "@CustomerGUID"
command.Parameters.Add(paramCustomerGUID)
Dim paramRegisterDate As IDbDataParameter = command.CreateParameter
paramRegisterDate.ParameterName = "@RegisterDate"
command.Parameters.Add(paramRegisterDate)
Dim paramPassword As IDbDataParameter = command.CreateParameter
paramPassword.ParameterName = "@Password"
command.Parameters.Add(paramPassword)
Dim paramSiteID As IDbDataParameter = command.CreateParameter
paramSiteID.ParameterName = "@SiteID"
command.Parameters.Add(paramSiteID)
Dim paramLastName As IDbDataParameter = command.CreateParameter
paramLastName.ParameterName = "@LastName"
command.Parameters.Add(paramLastName)
Dim paramFirstName As IDbDataParameter = command.CreateParameter
paramFirstName.ParameterName = "@FirstName"
command.Parameters.Add(paramFirstName)
Dim paramEmail As IDbDataParameter = command.CreateParameter
paramEmail.ParameterName = "@Email"
command.Parameters.Add(paramEmail)
Dim paramNotes As IDbDataParameter = command.CreateParameter
paramNotes.ParameterName = "@Notes"
command.Parameters.Add(paramNotes)
Dim paramBillingEqualsShipping As IDbDataParameter = command.CreateParameter
paramBillingEqualsShipping.ParameterName = "@BillingEqualsShipping"
command.Parameters.Add(paramBillingEqualsShipping)
Dim paramBillingLastName As IDbDataParameter = command.CreateParameter
paramBillingLastName.ParameterName = "@BillingLastName"
command.Parameters.Add(paramBillingLastName)
Dim modelObject As Customer = CType(e.Current,Customer)
paramCustomerID.Value = modelObject.CustomerID
paramCustomerGUID.Value = modelObject.CustomerGUID
paramRegisterDate.Value = modelObject.RegisterDate
If IsNothing(modelObject.Password) Then
paramPassword.Value = System.DBNull.Value
Else
paramPassword.Value = modelObject.Password
End If
paramSiteID.Value = modelObject.SiteID
If IsNothing(modelObject.LastName) Then
paramLastName.Value = System.DBNull.Value
Else
paramLastName.Value = modelObject.LastName
End If
If IsNothing(modelObject.FirstName) Then
paramFirstName.Value = System.DBNull.Value
Else
paramFirstName.Value = modelObject.FirstName
End If
If IsNothing(modelObject.Email) Then
paramEmail.Value = System.DBNull.Value
Else
paramEmail.Value = modelObject.Email
End If
If IsNothing(modelObject.Notes) Then
paramNotes.Value = System.DBNull.Value
Else
paramNotes.Value = modelObject.Notes
End If
paramBillingEqualsShipping.Value = modelObject.BillingEqualsShipping
If IsNothing(modelObject.BillingLastName) Then
paramBillingLastName.Value = System.DBNull.Value
Else
paramBillingLastName.Value = modelObject.BillingLastName
End If
rowsAffected = (rowsAffected + command.ExecuteNonQuery)
Loop
Finally
connection.Close
CType(connection,System.IDisposable).Dispose
End Try
Return rowsAffected
End Function
It's hard to write DAL code by hand, but you will have complete control over your DAL, SQL and Mapping code, and changing any of them will be a breeze in the future.
If you don't feel like writing all the DAL code by hand, you can get a CodeGenerator like Orasis Mapping Studio to generate exactly the same code shown without writing anything. You just need to build your SQL in the tool, map properties to parameters and you're done. This will do the rest for you.
Good luck and happy DAL coding!
source to share
I'm with Stephen Ryton. There are many variables here, and many unanswered questions. If it's SQL, is it even a dialect of Microsoft SQL? Is it Oracle? MySQL? Something else?
Anyway, my personal preference is to avoid creating SQL in the application if possible and call the stored procedure even for inserts and updates. Then I pass the procedure arguments to the ADO.NET command object. I have this crazy idea in my head that SQL belongs to the database. Perhaps from all this time, I wasted debugging horribly written ASP code that concatenated SQL strings together in the Dot Com era. (Never again.)
If you think this is absolutely necessary, then implement the System.Text.StringBuilder class . Get to know him. Love it. Make him your best friend.
UPDATE: After seeing your answer, I can now see that you are working with SQL Server. It makes things so much better.
I would recommend splitting your SQL code into a separate class away from the real business class. Some may disagree with this, but it will keep the PURPOSE of the classes clear. (See Separation of concerns .)
You want your business object to handle the business logic and a separate class that handles the work of getting data to and from the database. That way, if you're having a problem with your serialization logic, you have a much better idea of where to look, and your chances of wanting business logic are greatly reduced. It also makes your application easier to understand.
A little effort in writing a few more classes has a HUGE payoff along the way.
But that's just my opinion.
source to share
Not really sure what the OP is asking.
You need to define exactly what you are doing in the Save method
- If you create a new record in the save method, you need to use the INSERT statement.
- If you are updating an existing record in the save method, you need to use the UPDATE statement.
"Save" usually means that both cases are handled by the procedure.
The best method would be to have (New or Insert) and (Update or Save).
Or perhaps there is one procedure that handles both.
source to share