[.net] C# .NET DB Code
Author
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
Using a typed dataset
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 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 FunctionThis 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
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
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
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement