EF 4.1 Add Table Index on DropCreateDatabaseAlways

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.
Who is Participating?
GlobaLevelConnect With a Mentor ProgrammerCommented:
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

brotherbill999Author Commented:
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.
Amandeep Singh BhullarCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.