Just started working with LINQ. Nifty stuff! But after you spend all that time creating a data structure, why do it again in SQL? Seems kind of redundant, especially with a nicely laid out DBML file staring at you! Why not have your system do it automatically! Here's how...

Step 1: Create a new class and paste in the following...

****START****
Imports System.Data.Linq

Public Class CreateSQLfromLINQ

    Inherits DataContext
    Public My_LINQ_table_1 As Table(Of My_LINQ_table_1)
    Public My_LINQ_table_2 As Table(Of My_LINQ_table_2)
    Public My_LINQ_table_3 As Table(Of My_LINQ_table_3)
    Public My_LINQ_table_4 As Table(Of My_LINQ_table_4)
    Public My_LINQ_table_5 As Table(Of My_LINQ_table_5)
    'you need to add one line per table

    Public Sub New(ByVal connection As String)
        MyBase.New(connection)
    End Sub

End Class
****STOP****

Step 2: Create a new ASPX page called DefaultPage.aspx. Add a single button
<asp:Button runat="server" ID="cmdGO" Text="Create Database" />

Step 3: In the ASPX code behind, add the following...

****START****

Imports CreateSQLfromLINQ

Partial Class DefaultPage
    Inherits System.Web.UI.Page

    Protected Sub cmdGO_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdGO.Click
       Dim mySQLconnectionString as String = "Data Source=MySQLserver;Initial Catalog=LINQdatabase;Integrated Security=True"
       Dim db As New CreateSQLfromLINQ(mySQLconnectionString)
        db.CreateDatabase()
    End Sub

End Class

****STOP****

Step 4: Compile and run. When the page loads, click the button. Done!

Here's what's going on.

In the DBML designer of Visual Studio, you have already created the classes (tables) and properties (fields) of your data structure. In code behind, the DBML has created partial class table structures for you.

All you need to do is tell your custom class what these "tables" are called. Hence the reference to

Public My_LINQ_table_1 As Table(Of My_LINQ_table_1)

You need to remember to use names that you specified in the "source" property of the DBML class object. Otherwise you will have problems.

the call "db.CreateDatabase()" is part of the LINQ DataContext object.

Once invoked, it will use the "InitialCatalog" name in your connection string as the SQL database name.

The SQL database location will default to where ever your instance of SQL is installed. If you don't want the DB there, just detach it after creation, move the files, re-attach. Simple.

I did have a problem on my machine with SQL "named pipes" and "remote connections" being disabled. So make sure these are enabled before you do this. You can always turn them off later.

Have fun!

Be sure to comment and rate!

:)