LINQ To SQL Samples - DataContext FunctionsOn This Page |
This sample uses CreateDatabase() to create a new database based on the NewCreateDB Schema in Mapping.cs, and DeleteDatabase() to delete the newly created database.
Public Sub LinqToSqlDataContext01()
' Create a temp folder to store the new created Database
Dim userTempFolder = Environment.GetEnvironmentVariable("SystemDrive") + "\LinqToSqlSamplesTemp"
Directory.CreateDirectory(userTempFolder)
Console.WriteLine("********** Create NewCreateDB ***********")
Dim userMDF = System.IO.Path.Combine(userTempFolder, "NewCreateDB.mdf")
Dim connStr = String.Format("Data Source=.\SQLEXPRESS;AttachDbFilename={0};Integrated Security=True;Connect Timeout=30;User Instance=True; Integrated Security = SSPI;", userMDF)
Dim newDB = New NewCreateDB(connStr)
Try
newDB.CreateDatabase()
Catch ex As SqlException
Console.WriteLine(ex.Message)
End Try
If (newDB.DatabaseExists() AndAlso File.Exists(Path.Combine(userTempFolder, "NewCreateDB.mdf"))) Then
Console.WriteLine("NewCreateDB is created")
Else
Console.WriteLine("Error: NewCreateDB is not successfully created")
Console.WriteLine()
Console.WriteLine("********* Insert data and query *********")
Dim newRow = New Person With {.PersonID = 1, .PersonName = "Peter", .Age = 28}
newDB.Persons.Add(newRow)
newDB.SubmitChanges()
Dim q = From x In newDB.Persons _
Select x
ObjectDumper.Write(q)
Console.WriteLine()
Console.WriteLine("************ Delete NewCreateDB **************")
newDB.DeleteDatabase()
If (File.Exists(Path.Combine(userTempFolder, "NewCreateDB.mdf"))) Then
Console.WriteLine("Error: NewCreateDB is not yet deleted")
Else
Console.WriteLine("NewCreateDB is deleted")
End If
' Delete the temp folder created for this testcase
Directory.Delete(userTempFolder)
End If
End Sub
Result:
********** Create NewCreateDB ***********
Database 'C:\LinqToSqlSamplesTemp\NewCreateDB.mdf' already exists. Choose a different database name.
NewCreateDB is created
This sample uses DatabaseExists() to verify whether a database exists or not.
Public Sub LinqToSqlDataContext02()
Console.WriteLine("*********** Verify Northwind DB exists ***********")
If (db.DatabaseExists()) Then
Console.WriteLine("Northwind DB exists")
Else
Console.WriteLine("Error: Northwind DB does not exist")
Console.WriteLine()
Console.WriteLine("********* Verify NewCreateDB does not exist **********")
Dim userTempFolder = Environment.GetEnvironmentVariable("Temp")
Dim userMDF = System.IO.Path.Combine(userTempFolder, "NewCreateDB.mdf")
Dim newDB = New NewCreateDB(userMDF)
If (newDB.DatabaseExists()) Then
Console.WriteLine("Error: NewCreateDB DB exists")
Else
Console.WriteLine("NewCreateDB DB does not exist")
End If
End If
End Sub
Result:
*********** Verify Northwind DB exists ***********
Error: Northwind DB does not exist
********* Verify NewCreateDB does not exist **********
NewCreateDB DB does not exist
This sample demonstrates that SubmitChanges() must be called in order to submit any update to the actual database.
Public Sub LinqToSql1DataContext03()
Dim cust = db.Customers.First(Function(c) c.CustomerID = "ALFKI")
Console.WriteLine("********** Original Customer CompanyName **********")
Dim q = From x In db.Customers _
Where x.CustomerID = "ALFKI" _
Select x.CompanyName
Console.WriteLine()
ObjectDumper.Write(q)
Console.WriteLine()
Console.WriteLine("*********** Update and call SubmitChanges() **********")
cust.CompanyName = "VB Programming Firm"
db.SubmitChanges()
Console.WriteLine()
ObjectDumper.Write(q)
Console.WriteLine()
Console.WriteLine("*********** Update and did not call SubmitChanges() **********")
cust.CompanyName = "LinqToSql Programming Firm"
Console.WriteLine()
ObjectDumper.Write(q)
Cleanup122() ' Restore previous database state
End Sub
Result:
********** Original Customer CompanyName **********
Alfreds Futterkiste
*********** Update and call SubmitChanges() **********
VB Programming Firm
*********** Update and did not call SubmitChanges() **********
VB Programming Firm
This sample uses CreateQuery() to create an IQueryable(Of T) out of Expression.
Public Sub LinqToSqlDataContext04()
Dim c1 = Expression.Parameter(GetType(Customer), "c")
Dim City As PropertyInfo = GetType(Customer).GetProperty("City")
Dim pred = Expression.Lambda(Of Func(Of Customer, Boolean))( _
Expression.Equal( _
Expression.Property(c1, City), Expression.Constant("Seattle") _
), c1)
Dim custs As IQueryable = db.Customers
Dim expr = Expression.Call(GetType(Queryable), "Where", _
New Type() {custs.ElementType}, custs.Expression, pred)
Dim q = db.Customers.AsQueryable().Provider.CreateQuery(Of Customer)(expr)
ObjectDumper.Write(q)
End Sub
Result:
CustomerID=WHITC CompanyName=White Clover Markets ContactName=Karl Jablonski ContactTitle=Owner Address=305 - 14th Ave. S. Suite 3B City=Seattle Region=WA PostalCode=98128 Country=USA Phone=(206) 555-4112 Fax=(206) 555-4115 Orders=... CustomerCustomerDemos=...
This sample uses Db.Log to turn off and turn on the database logging display.
Public Sub LinqToSqlDataContext05()
Console.WriteLine("**************** Turn off DB Log Display *****************")
db.Log = Nothing
Dim q = From c In db.Customers _
Where c.City = "London" _
Select c
ObjectDumper.Write(q)
Console.WriteLine()
Console.WriteLine("**************** Turn on DB Log Display *****************")
db.Log = Me.OutputStreamWriter
ObjectDumper.Write(q)
End Sub
Result:
**************** Turn off DB Log Display *****************
CustomerID=AROUT CompanyName=Around the Horn ContactName=Thomas Hardy ContactTitle=Sales Representative Address=120 Hanover Sq. City=London Region=null PostalCode=WA1 1DP Country=UK Phone=(171) 555-7788 Fax=(171) 555-6750 Orders=... CustomerCustomerDemos=...
CustomerID=BSBEV CompanyName=B's Beverages ContactName=Victoria Ashworth ContactTitle=Sales Representative Address=Fauntleroy Circus City=London Region=null PostalCode=EC2 5NT Country=UK Phone=(171) 555-1212 Fax=null Orders=... CustomerCustomerDemos=...
CustomerID=CONSH CompanyName=Consolidated Holdings ContactName=Elizabeth Brown ContactTitle=Sales Representative Address=Berkeley Gardens 12 Brewery City=London Region=null PostalCode=WX1 6LT Country=UK Phone=(171) 555-2282 Fax=(171) 555-9199 Orders=... CustomerCustomerDemos=...
CustomerID=EASTC CompanyName=Eastern Connection ContactName=Ann Devon ContactTitle=Sales Agent Address=35 King George City=London Region=null PostalCode=WX3 6FW Country=UK Phone=(171) 555-0297 Fax=(171) 555-3373 Orders=... CustomerCustomerDemos=...
CustomerID=NORTS CompanyName=North/South ContactName=Simon Crowther ContactTitle=Sales Associate Address=South House 300 Queensbridge City=London Region=null PostalCode=SW7 1RZ Country=UK Phone=(171) 555-7733 Fax=(171) 555-2530 Orders=... CustomerCustomerDemos=...
CustomerID=SEVES CompanyName=Seven Seas Imports ContactName=Hari Kumar ContactTitle=Sales Manager Address=90 Wadhurst Rd. City=London Region=null PostalCode=OX15 4NB Country=UK Phone=(171) 555-1717 Fax=(171) 555-5646 Orders=... CustomerCustomerDemos=...
**************** Turn on DB Log Display *****************
CustomerID=AROUT CompanyName=Around the Horn ContactName=Thomas Hardy ContactTitle=Sales Representative Address=120 Hanover Sq. City=London Region=null PostalCode=WA1 1DP Country=UK Phone=(171) 555-7788 Fax=(171) 555-6750 Orders=... CustomerCustomerDemos=...
CustomerID=BSBEV CompanyName=B's Beverages ContactName=Victoria Ashworth ContactTitle=Sales Representative Address=Fauntleroy Circus City=London Region=null PostalCode=EC2 5NT Country=UK Phone=(171) 555-1212 Fax=null Orders=... CustomerCustomerDemos=...
CustomerID=CONSH CompanyName=Consolidated Holdings ContactName=Elizabeth Brown ContactTitle=Sales Representative Address=Berkeley Gardens 12 Brewery City=London Region=null PostalCode=WX1 6LT Country=UK Phone=(171) 555-2282 Fax=(171) 555-9199 Orders=... CustomerCustomerDemos=...
CustomerID=EASTC CompanyName=Eastern Connection ContactName=Ann Devon ContactTitle=Sales Agent Address=35 King George City=London Region=null PostalCode=WX3 6FW Country=UK Phone=(171) 555-0297 Fax=(171) 555-3373 Orders=... CustomerCustomerDemos=...
CustomerID=NORTS CompanyName=North/South ContactName=Simon Crowther ContactTitle=Sales Associate Address=South House 300 Queensbridge City=London Region=null PostalCode=SW7 1RZ Country=UK Phone=(171) 555-7733 Fax=(171) 555-2530 Orders=... CustomerCustomerDemos=...
CustomerID=SEVES CompanyName=Seven Seas Imports ContactName=Hari Kumar ContactTitle=Sales Manager Address=90 Wadhurst Rd. City=London Region=null PostalCode=OX15 4NB Country=UK Phone=(171) 555-1717 Fax=(171) 555-5646 Orders=... CustomerCustomerDemos=...
