EF 4.1 Add Table Index on DropCreateDatabaseAlways

Posted on 2011-10-22
Last Modified: 2012-05-12
On creating a new database, I want to execute some SQL commands, after the database is created, but before it is used for CRUD.
The SQL to run is 'CREATE UNIQUE NONCLUSTERED INDEX [ByUserName] ON [dbo].[Members] ([Username] ASC)'
which requires no parameters.  This makes the Members table, UserName column to have a unique ascending index.

Where do I stick the code?
My best guess, which doesn't work is:
            string sql = "CREATE UNIQUE NONCLUSTERED INDEX [ByUserName] ON [dbo].[Members] ([Username] ASC)";
But it want some parameters.
Question by:brotherbill999
    LVL 10

    Accepted Solution

    still not sure what you want...but you can put a trigger on a the table...and insert from the .net that will launch that trigger statement which is a stored proc basically

    you can use the following statement to insert, update, delete from that table once it hits the table the trigger will laucnh and you  can do wahtever you want from the sql side...
    Dim SQLConn1 As SqlConnection = New SqlConnection()
                Dim cmd1 As SqlCommand
                Dim SQLStr1 As String
                Dim Err1 As String
                SQLConn1.ConnectionString = "Network Library=DBMSSOCN; Data Source=xx.xx.xx.xx; Initial Catalog=sddasdas; Trusted_Connection=true;User ID=; Password="
                SQLStr1 = ("Update Campaign set time_sent_range = @new_time_sent_range " & _
                              "Where TC_ID = @tc_id and Campaign_name = @camp_name")
                cmd1 = New SqlCommand(SQLStr1, SQLConn1)
                cmd1.Parameters.AddWithValue("@new_time_sent_range", new_time_sent_range)
                cmd1.Parameters.AddWithValue("@tc_id", tc_id)
                cmd1.Parameters.AddWithValue("@camp_name", campaign_name)
                    Err1 = cmd1.ExecuteNonQuery()
                End Try

    Open in new window


    Author Comment

    Thanks for your attempt.
    The problem is that Entity Framework 4.1 creates the database from scratch.
    What I want is to know where to insert the code to alter the table.
    Am using MVC 3.

    For now, I manually run a script that alters the table, but think there must be a better way.
    LVL 19

    Expert Comment

    by:Amandeep Singh Bhullar
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now