Jump to content
  • Advertisement
Sign in to follow this  
Synex

[.net] C# .NET DB Code

This topic is 4821 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm in the process of adding DB Access code into my engine, and i understand the idea of connections, commands, dataReaders etc, i was just wondering how everyone else lays out their data access code and if there are any good sources for source code of some C# .NET Database applications. There is a load of simple tutorials etc on the 'net but most often they only deal with simple connections and i need something a bit more complex. So anyone got any code/links? Cheers

Share this post


Link to post
Share on other sites
Advertisement
Using a typed dataset



Function fillOrder() As ProdOrders
Dim allOrders As ProdOrders
Dim orderData As OleDb.OleDbDataAdapter
orderData = New OleDb.OleDbDataAdapter
allOrders = New ProdOrders

orderData.SelectCommand = New OleDb.OleDbCommand
orderData.SelectCommand.CommandText = "SELECT * FROM orders"
orderData.SelectCommand.Connection = New OleDb.OleDbConnection
orderData.SelectCommand.Connection.ConnectionString = customerString
orderData.Fill(allOrders, "Orders")
orderData.SelectCommand.CommandText = "SELECT * FROM [Order Details]"
orderData.Fill(allOrders, "Order Details")
Return allOrders


End Function





This one fills a table, and gets the autogenterated PK and uses that to fill another table, also using paramaters. Also this one uses a transaction to fill it all at once.



Function addOrder(ByVal orders As ProdOrders)

'New Ole parts
Dim orderData As OleDb.OleDbDataAdapter
Dim parm As OleDb.OleDbParameter
Dim tran As System.Data.OleDb.OleDbTransaction

Try


orderData = New OleDb.OleDbDataAdapter
'setting params
orderData.InsertCommand = New OleDb.OleDbCommand
orderData.InsertCommand.CommandText = "INSERT into orders (CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"
orderData.InsertCommand.Connection = New OleDb.OleDbConnection
orderData.InsertCommand.Connection.ConnectionString = customerString
'Getting PK back from batabase
orderData.InsertCommand.CommandText &= " Select * from Orders where OrderID = @@Identity"
'Getting for first row only
orderData.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

'Starting a transaction
orderData.InsertCommand.Connection.Open()
tran = orderData.InsertCommand.Connection.BeginTransaction
orderData.InsertCommand.Transaction = tran



'Making parms
parm = New OleDb.OleDbParameter
parm.SourceColumn = "CustomerID"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "EmployeeID"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "OrderDate"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "RequiredDate"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShippedDate"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipVia"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "Freight"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipName"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipAddress"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipCity"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipRegion"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipPostalCode"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ShipCountry"
orderData.InsertCommand.Parameters.Add(parm)

'Setting to continue with updates
'orderData.ContinueUpdateOnError = True
'run update
orderData.Update(orders, "Orders")

'Clearing parms
orderData.InsertCommand.Parameters.Clear()
orderData.InsertCommand.UpdatedRowSource = UpdateRowSource.None

orderData.InsertCommand.CommandText = "INSERT into [Order Details] ( OrderID,ProductID,UnitPrice,Quantity,Discount)VALUES (?,?,?,?,?)"

' Set up the parameters for the insert command

parm = New OleDb.OleDbParameter
parm.SourceColumn = "OrderID"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "ProductID"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "UnitPrice"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "Quantity"
orderData.InsertCommand.Parameters.Add(parm)

parm = New OleDb.OleDbParameter
parm.SourceColumn = "Discount"
orderData.InsertCommand.Parameters.Add(parm)

orderData.Update(orders, "Order Details")

'finish transaction
orderData.InsertCommand.Transaction.Commit()


Catch ex As Exception
Throw ex


End Try




End Function



Share this post


Link to post
Share on other sites
Try to stay away from SQL in your app. Try to use (stored) procedures instead. It's safer and easier to maintain.

Please be more specific than "i need something a bit more complex."

If it's really complex consider MSDE or SQL Express instead of Access...

Cheers

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!