Porting Visual Studio .mdf Database File to SQL Server – ASP.NET

There is some confusing information out there on how to get your development database from visual studio into SQL Server so that you can run an ASP.NET website through IIS.

I’ve spent a whole evening getting this sorted and now am writing down explicit instructions on how I did it so that I (and hopefully you don’t have to spend time doing this again).

Prerequisites:

A database file in .mdf format.

IIS (Tested on IIS 5)

SQL Sever

SQL Server Management Studio Express (Free)

Download Link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en

1. In your web.config file, change the connection string to the following, replacing the database name and connection string name:

2. Attach the mdf file to SQL server by right clicking the databases node, selecting attach, and pointing to the .mdf file from Visual Studio. This will be deleted later, bear with me.

3. Create a back up of that attached database by right clicking the database name, going to tasks, then back up.

4. Back up as prompted and make a note of the back up location. The back up will create a back up file in format .bak.

5. Delete the attached data base.

6. Create a NEW (as opposed to attached) database in SQL Server management studio express by right clicking databases node and selecting new database.

7. Give the new database the same name as your .mdf file, excluding the extension.

8. You are given the opportunity to assign owners. Play around a bit here. I selected all available options here, which through an error, but some owners stuck anyway, so don’t worry too much here ( a security expert may say other wise! ). Roles are added via queries in a minute so this might not even be necessary.

9. Back in the tree view, click the new database name node, go to tasks again, and select restore.

10. Point to the recently created back up file and restore.

11. Now for some trickery pokery. We need to configure security for SQL server. In SQL Server management studio express, you can execute queries against your database. Type in the following queries one at a time only replacing machineName and databaseName. This will be the name of the windows pc you are installing the application on.

EXEC sp_grantlogin ‘machineName\ASPNET’

USE databaseName

EXEC sp_addrolemember ‘dbowner’, ‘machineName\ASPNET’

USE databaseName

EXEC sp_grantlogin ‘machineName\ASPNET’

That’s it. With some luck you should be good to go here!

Advertisements

March 25, 2008 at 2:16 am Leave a comment

Sending E-mail From C# Class Using G-mail

Here is a complete C# class that can be used to send mail using a g-mail (Google mail) account. This has been tested as working today.

I’ve used the web.config file to get authentication parameters and smtp server, but these can be replaced directly with strings if you like. Those strings will be:

SmtpUser: Your gmail user name. SmtpPassword: Your gmail password. SmtpClient: smtp.gmail.com

Also worth noting is that the port used is 587 instead of 465. If 587 doesn’t work, try 465.

The Code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Net.Mail;
using System.Net;

///
/// Summary description for MailSender
///
public class MailSender
{

    // Constructor #1 for general text only messages
    ///
    /// Constructor for basic email sending, does not required that IsHtml bool is specified.
    ///
    ///
    ///
    ///
    ///
    public MailSender(string from, string to, string subject, string body)
    {
        m_from = from;
        m_to = to;
        m_subject = subject;
        m_body = body;

        SendMailGmail(m_from, m_to, m_subject, m_body, false);
    }

    // Constructor #2 with ability to specify isHtml as true for sending HTML email
    ///
    /// Constructor with optional IsHtml parameter for sending of HTML email.
    ///
    ///
    ///
    ///
    ///
    ///
    public MailSender(string from, string to, string subject, string body, bool isHtml )
    {
        m_from = from;
        m_to = to;
        m_subject = subject;
        m_body = body;

        SendMailGmail(m_from, m_to, m_subject, m_body, isHtml);
    }

    ///
    /// Send email method configured for use with gmail.
    ///
    /// From
    /// To
    /// Subject
    /// Body text
    /// IsHtml
    private void SendMailGmail(string from, string to, string subject, string body, bool isHtml)
    {
        // Code from: http://www.andreas-kraus.net/blog/aspnet-20-aka-systemnetmail-with-gmail/

        System.Net.Mail.MailMessage message = new MailMessage(from, to, subject, body);
        message.Priority = MailPriority.High;
        message.IsBodyHtml = isHtml;

        SmtpClient smtp = new SmtpClient();
        smtp.Host = m_smtpClient;

        smtp.Credentials = new System.Net.NetworkCredential(m_smtpUserName, m_smtpPassWord);
        smtp.EnableSsl = true;
        // Not the usual port but works
        smtp.Port = 587;
        smtp.Timeout = 10;

        try
        {
            smtp.Send(message);
        }
        catch (Exception ex)
        {
            // Send error but pass false to prevent looping error emails
            ErrorLogger errorLogger = new ErrorLogger(ex, false);
        }
    }

    private string m_from = "";
    private string m_to = "";
    private string m_subject = "";
    private string m_body = "";

    private string m_smtpClient = ConfigurationManager.AppSettings["SmtpClient"];
    private string m_smtpUserName = ConfigurationManager.AppSettings["SmtpUser"];
    private string m_smtpPassWord = ConfigurationManager.AppSettings["SmtpPassword"];
}

March 24, 2008 at 8:28 pm 1 comment

Newer Posts


Categories

  • Blogroll

  • Feeds