Sign in to follow this  
Synex

[.net] C# .NET DB Code

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
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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this