Solved

SQL Management Objects (SMO) Attaching a database

Posted on 2013-12-11
10
795 Views
Last Modified: 2013-12-20
I'm having problems attaching a database using C# code. I have a little application in c sharp that I'm attempting to modify that will attach a database.  Currently, I require that it will work with Framework 3.5. I see the SMO has an attachdatabase method  however I don't think its available in the 3.5 framework. I assume that I could also use a ExecutenonQuery to execute a TSQL attach script but I'm unsure how this would work.

Currently the application uses the following references

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized.StringCollection;
using System.Data.SqlClient;
0
Comment
Question by:Roebbelen
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
Comment Utility
Hi,
to attach a database using T-SQL, you can either use the stored procedure: sp_attach
EXEC sp_attach_db @dbname = N'AdventureWorks2012', 
    @filename1 = 
N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf', 
    @filename2 = 
N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';

Open in new window

http://technet.microsoft.com/en-us/library/ms179877.aspx

or (as the above is depricated for the upcoming versions) use the CREATE DATABE with ATTACH option:
USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Open in new window

http://technet.microsoft.com/en-us/library/ms187858.aspx

Which SQL Server version (and edition) are you targeting?

HTH
Rainer
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
This is the code that I use to install the database that I use in my VB training on the students stations. No need to use SMO, simple SqlClient code calling a the system procedure to attach a database. Easy to translate to C#.
Dim cmd As New SqlCommand
cmd.Connection = New SqlConnection("Data Source=yourServerName;Integrated Security=True")
cmd.CommandText = "EXEC sp_attach_db @dbname = 'PhotoCat', " +
		 "@filename1 = N'C:\Cours VS2012\PhotoCat.mdf', " +
		 "@filename2 = N'C:\Cours VS2012\PhotoCat_log.ldf';"
cmd.ExecuteNonQuery()

Open in new window

0
 
LVL 44

Expert Comment

by:Rainer Jeschor
Comment Utility
@JamesBurger:
Shouldnt the connection string include the catalog "Master"? If the users default database is not set to master the above could not work as the stored procedure is part of the master db.
0
 

Author Comment

by:Roebbelen
Comment Utility
I am Targeting SQL Server Express 2008 R1.  The utility needs to work on most older machines. Hence the 3.5 Framework requirement. Also know that the existing utility works inside a windows form. It looks like the following code.
I want to be sure that I can pass it variables like txtboxLDF and txtboxMDF. I've tried to reference the sql command function however It would seem that I'm not referencing the correct namespace.
//Using Statements
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

// Namespace Defined    
namespace SQLServerDatabaseBackup
{
    public partial class frmMain : Form
    {
        Server srv;
        ServerConnection conn;

        public frmMain()
        {
            InitializeComponent();
        }
        //Load SQL Server Instances
        private void frmMain_Load(object sender, EventArgs e)
        {
RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
    foreach (String element in instances)
    {
        if (element == "MSSQLSERVER")
            lstLocalInstances.Items.Add(System.Environment.MachineName);
        else
            lstLocalInstances.Items.Add(System.Environment.MachineName + @"\" + element);
    }
}

            Thread threadGetNetworkInstances = new Thread(GetNetworkInstances);
            threadGetNetworkInstances.Start();
        }
        //Connect to Server
        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                ddlDatabase.Items.Clear();

                string sqlSErverInstance;

                if (this.tabServers.SelectedIndex == 0)
                {
                    sqlSErverInstance = lstLocalInstances.SelectedItem.ToString();
                }
                else
                {
                    sqlSErverInstance = lstNetworkInstances.SelectedItem.ToString();
                }

                if (chkWindowsAuthentication.Checked == true)
                {
                    conn = new ServerConnection();
                    conn.ServerInstance = sqlSErverInstance;
                }
                else
                {
                    conn = new ServerConnection(sqlSErverInstance, txtLogin.Text, txtPassword.Text);
                }
                srv = new Server(conn);

                foreach (Database db in srv.Databases)
                {
                    ddlDatabase.Items.Add(db.Name);
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
            btnDetachDB.Enabled = true; 
            btnAttachDB.Enabled = true; 
            btnBackupDB.Enabled = true;
            btnBrowseMDF.Enabled = true;
            btnBrowseLDF.Enabled = true;
            btnBrowse.Enabled = true;
            btnBackupDB.Enabled = true;
            btnBackupLog.Enabled = true;
            btnVerify.Enabled = true;
            btnRestore.Enabled = true;
           
        }
        //Browse to BAK File
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            string databaseName = this.ddlDatabase.SelectedItem.ToString();
            string installDataDir = srv.InstallDataDirectory.ToString();
            openFileDialog1.InitialDirectory = installDataDir+"\\backup\\"+databaseName+".bak";
            openFileDialog1.Filter = "bak files (*.bak)|*.txt|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = openFileDialog1.FileName.ToString();
            }
        }

        //Change Back-Up Path Name Change
        private void txtFileName_TextChanged(object sender, EventArgs e)
        {

        }
        //Button Backup DB
        private void btnBackupDB_Click(object sender, EventArgs e)
        {
            Backup bkp = new Backup();

            this.Cursor = Cursors.WaitCursor;
            this.dataGridView1.DataSource = string.Empty;
            try
            {
                string fileName = this.txtFileName.Text;
                string databaseName = this.ddlDatabase.SelectedItem.ToString();

                bkp.Action = BackupActionType.Database;
                bkp.Database = databaseName;
                bkp.Devices.AddDevice(fileName, DeviceType.File);
                bkp.Incremental = chkIncremental.Checked;
                this.progressBar1.Value = 0;
                this.progressBar1.Maximum = 100;
                this.progressBar1.Value = 10;

                bkp.PercentCompleteNotification = 10;
                bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                bkp.SqlBackup(srv);
                MessageBox.Show("Database Backed Up To: " + fileName);
            }
            
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = Cursors.Default;
                this.progressBar1.Value = 0;
            }
        }
        //Button Progress of Backup
        public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
        {
            this.progressBar1.Value = e.Percent;
        }
        //Button Start Restore
        private void btnRestore_Click(object sender, EventArgs e)
        {

            

            Restore res = new Restore();

            this.Cursor = Cursors.WaitCursor;
            this.dataGridView1.DataSource = string.Empty;

            try
            {
                string fileName = this.txtFileName.Text;
                string databaseName = this.ddlDatabase.SelectedItem.ToString();

                res.Database = databaseName;
                res.Action = RestoreActionType.Database;
                res.Devices.AddDevice(fileName, DeviceType.File);

                this.progressBar1.Value = 0;
                this.progressBar1.Maximum = 100;
                this.progressBar1.Value = 10;

                res.PercentCompleteNotification = 10;
                res.ReplaceDatabase = true;
                res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
                res.SqlRestore(srv);

                MessageBox.Show("Restore of " + databaseName + " Complete!", "Restore",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            catch (SmoException exSMO)
            {
                MessageBox.Show(exSMO.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = Cursors.Default;
                this.progressBar1.Value = 0;
            }
        }
        //Button Start Backup Log
        private void btnBackupLog_Click(object sender, EventArgs e)
        {
            Backup bkp = new Backup();

            Cursor = Cursors.WaitCursor;
            dataGridView1.DataSource = "";

            try
            {
                string strFileName = txtFileName.Text.ToString();
                string strDatabaseName = ddlDatabase.SelectedItem.ToString();

                bkp.Action = BackupActionType.Log;
                bkp.Database = strDatabaseName;

                bkp.Devices.AddDevice(strFileName, DeviceType.File);
                progressBar1.Value = 0;
                progressBar1.Maximum = 100;
                progressBar1.Value = 10;

                bkp.PercentCompleteNotification = 10;
                bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);

                bkp.SqlBackup(srv);
                MessageBox.Show("Log Backed Up To: " + strFileName, "");
            }
            catch (SmoException exSMO)
            {
                MessageBox.Show(exSMO.ToString());

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                Cursor = Cursors.Default;
                progressBar1.Value = 0;
            }
        }
        //Button Verify
        private void btnVerify_Click(object sender, EventArgs e)
        {
            Restore rest = new Restore();
            string fileName = this.txtFileName.Text;

            this.Cursor = Cursors.WaitCursor;
            this.dataGridView1.DataSource = string.Empty;

            try
            {
                rest.Devices.AddDevice(fileName, DeviceType.File);
                bool verifySuccessful = rest.SqlVerify(srv);

                if (verifySuccessful)
                {
                    MessageBox.Show("Backup Verified!", "");
                    DataTable dt = rest.ReadFileList(srv);
                    this.dataGridView1.DataSource = dt;
                }
                else
                {
                    MessageBox.Show("Backup NOT Verified!", "");
                }
            }
            catch (SmoException exSMO)
            {
                MessageBox.Show(exSMO.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        //MY INSERT FOR ATTACH AND DETACH FILES

        //Browse to MDF LOCATION File
        private void btnBrowseMDF_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            string installDataDir = srv.InstallDataDirectory.ToString();
            openFileDialog1.InitialDirectory = installDataDir+"\\data";
            openFileDialog1.Filter = "MDF files (*.MDF)|*.txt|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtMDF.Text = openFileDialog1.FileName.ToString();

            }
        }

        //Browse to LDF LOCATION File
        private void btnBrowseLDF_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            string installDataDir = srv.InstallDataDirectory.ToString();
            openFileDialog1.InitialDirectory = installDataDir+"\\data";
            openFileDialog1.Filter = "LDF files (*.LDF)|*.txt|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtLDF.Text = openFileDialog1.FileName.ToString();
            }
        }
        //Change MDF Path Name Change
        private void txtMDF_TextChanged(object sender, EventArgs e)
        {
   

        }
        //Button Detach CHANGE CODE TO DETACH
        private void btnDetachDB_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = "";
            {
                string databaseName = this.ddlDatabase.SelectedItem.ToString();

                DialogResult result = MessageBox.Show("Are you Sure you want to Detach " + databaseName, "Confirm Detach", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    srv.DetachDatabase(databaseName, false);
                    MessageBox.Show(databaseName + " has been detached at ");
                }
                else
                {
                    MessageBox.Show("Detach Canceled");
                }
            }
        }

        
       // END OF MY INSERT 
        
        
        delegate void SetMessageCallback(string text);
        //Network Instances L
        private void AddNetworkInstance(string text)
        {
            if (this.lstNetworkInstances.InvokeRequired)
            {
                SetMessageCallback d = new SetMessageCallback(AddNetworkInstance);
                this.BeginInvoke(d, new object[] { text });
            }
            else
            {
                this.lstNetworkInstances.Items.Add(text);
            }
        }
        //Get Network
        private void GetNetworkInstances()
        {
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);

if (dt.Rows.Count > 0)
{
    foreach (DataRow dr in dt.Rows)
    {
        AddNetworkInstance(dr["Name"].ToString());
    }
}
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void label1_Click_1(object sender, EventArgs e)
        {

        }

        private void btnAttachDB_Click(object sender, EventArgs e)
        {
         //   String fileLDF= txtLDF
            string databaseName = this.ddlDatabase.SelectedItem.ToString();
            string fileMDF = this.txtMDF.Text;
              string fileLDF = this.txtLDF.Text;
            //srv.AttachDatabase(databaseName,StringCollectionFiles,Owner);
            



        }
        


    }
}

Open in new window

0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
@RainerJ

According to the documentation, if you do not specify the InitialCatalog (or Database), it defaults to an empty string. If you try it, you will set that this connects automatically to the master.

@Roebbelen

Although it seems simple, my solution does work.

I earn my living by giving Visual Basic .NET training in classrooms in many training centres and schools, as well as in-house is different companies. Have been doing so since 1994, and in .NET since it was out in 2002. I meet a lot of different environments. Most of the time, I set foot in the classroom 1 hour before the first day of the session and have about 30 minutes to set up my sample code and database in an unknown environment before the students start to arrive. The students use that same code to install the sample database on their stations once the session has begun. They reuse it to install it on their development server once they are back at work and want to have it along when they referenced the training manual.

This simple code or variations (setting the password and user name where Windows authentication was not enabled as an example) works everywhere, as long as the user has the proper rights. I started using it with framework 1.0 and still use it with framework 4.5.1. It worked with SQL Server 2005 and stills work with SQL Server 2012.

And it works with connection strings that you build with concatenations such as what you are trying to achieve. I simplified it for my answer (real code is always more complex than the theory), but here is what the real code that I use looks like (sorry for the French). The main thing missing from that piece of real code is the definition of PhotoData.ServeurSQL, a constant defined somewhere else and that is most often the only thing that needs to be set when I arrive in a new environment.

	Function InitSQL() As Boolean

		'Attache la base de données au serveur au besoin
		'
		'Retourne False si une erreur se produit

		Const dossierBD As String = "C:\Users\Public\Data\Cours VS2012\"													'Répertoire où est installée la base de données SQL
		Const dossierInstalle As String = JBFI.Photo.PhotoData.DossierData & "BD SQL Server 2012\"	'Répertoire où est installée la copie originale de la base de données SQL
		Const BDSqlBase As String = "PhotoCat.mdf"
		Const BDSqlLogBase As String = "PhotoCat_log.ldf"

		Dim cmd As New SqlCommand("Select * From sysdatabases Where name like 'PhotoCat'", New SqlConnection("Data Source=" & JBFI.Photo.PhotoData.ServeurSQL & ";Integrated Security=True"))

		InitSQL = False	'Empêche un avertissement de compilation quand les conditions de compilation ne sont pas respectées

		'Déplace la base de données dans un répertoire accessible par SQL Serveur.
		'La sécurité accrue depuis Windows Vista implique généralement un répertoire Public
		If Not System.IO.Directory.Exists(dossierBD) Then
			System.IO.Directory.CreateDirectory(dossierBD)
		End If

		'Copie les fichiers au besoin
		If Not System.IO.File.Exists(dossierBD & BDSqlBase) Then

			If MessageBox.Show("Vous vous apprêtez à copier les fichiers de données dans le dossier " & dossierBD & ". Vous devrez les détruire manuellement une fois que vous aurez fini de travailler avec l'application d'exemple.", Application.ProductName, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = DialogResult.Cancel Then
				MessageBox.Show("Vous devrez relancer PhotoCat en utilisant une configuration différente que ""BD SQL Server"" ou ""BD SQL sans SMO"".", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
				System.IO.Directory.Delete(New DirectoryInfo(dossierBD).Parent.FullName, True)	 'Détruit le répertoire créé plus haut
				Return Nothing
			End If

			Try

				If Not System.IO.File.Exists(dossierBD & BDSqlBase) Then
					Debug.WriteLine(dossierBD & BDSqlBase)
					System.IO.File.Copy(dossierInstalle & BDSqlBase, dossierBD & BDSqlBase)
					System.IO.File.Copy(dossierInstalle & BDSqlLogBase, dossierBD & BDSqlLogBase)
				End If

			Catch ex As Exception

				MessageBox.Show("Problème lors de la copie des fichiers suivants dans le dossier " & dossierBD & " :" & Environment.NewLine & Environment.NewLine & _
				 BDSqlBase & Environment.NewLine & _
				 BDSqlLogBase & Environment.NewLine & Environment.NewLine & _
				 "Un message d'erreur avec les détails suit.")
				JBFI.JBLib.Erreur.ErrMsg(ex, JBFI.JBLib.Erreur.ErrMsgAction.Ecran, "Dossier de destination : " & dossierBD)
				Return Nothing

			End Try

		End If

		'Attache la base de données au serveur au besoin

		Try

			cmd.Connection.Open()

			''Réinitialise pour tester pendant le développement
			'cmd.CommandText = "EXEC sp_detach_db 'PhotoCat'"
			'cmd.ExecuteNonQuery()
			'cmd.CommandText = "Select * From sysdatabases Where name like 'PhotoCat'"
			'Stop

			'Make sure that the database is attached to the server

			If cmd.ExecuteScalar() Is Nothing Then

				'BD non présente sur le serveur. On demande confirmation pour l'attacher.
				If MessageBox.Show("Vous vous apprêtez à attacher la base de données d'exemple (PhotoCat) au serveur " & JBFI.Photo.PhotoData.ServeurSQL & "." & Environment.NewLine &
				 "Vous devrez la détacher manuellement une fois que vous aurez fini de travailler avec l'application d'exemple." & Environment.NewLine &
				 Environment.NewLine &
				 "Si le nom de serveur mentionné ci-dessus ne correspond pas au vôtre, annulez tout et spécifier le nom du serveur utilisé dans la variable ServeurSQL, définie dans le fichier PhotoData du projet Photo.", Application.ProductName, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = DialogResult.Cancel Then
					MessageBox.Show("Vous devrez relancer PhotoCat en utilisant une configuration différente que ""BD SQL Server"".", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
					Return False
				End If

				Try

					'Attache la BD au serveur
					cmd.CommandText = "EXEC sp_attach_db @dbname = 'PhotoCat', " &
					 "@filename1 = N'" & dossierBD & "PhotoCat.mdf', " &
					 "@filename2 = N'" & dossierBD & "PhotoCat_log.ldf';"
					cmd.ExecuteNonQuery()

				Catch ex As SqlException

					MessageBox.Show("Incapable d'attacher les fichiers de base de données à cause de l'erreur suivante : " & Environment.NewLine & Environment.NewLine & _
													ex.Message & Environment.NewLine & Environment.NewLine & _
													"L'application va s'arrêter en mode débogage et vous offrir des solutions pour corriger ce problème.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Stop)
					Stop
					'**********************************************************************************************************
					'  Si vous recevez une erreur du type "Directory lookup for the file <Path>\PhotoCat.mdf failed", il
					'	 est possible que le répertoire où sont localisés les fichiers de base de données ne soit pas accessible
					'	 pour SQL Server. Certains répertoires comme Mes Documents ne sont pas accessibles pour le serveur, qui
					'  n'a pas les droits de l'utilisateurs courant, mais un login propre à lui.
					'  Vous pouvez spécifier un autre répertoire dans la déclaration de la constante dossierBD
					'  au début de cette méthode. Vous devez spécifier un chemin d'accès absolu. Les chemins d'accès
					'  relatifs ne sont pas acceptables pour SQL Server.
					'
					'  Si vous recevez une erreur "The database 'PhotoCat' cannot be opened because it is version 706", vous
					'  utilisez une version de SQL Server antérieure à 2012. Changez la constante dossierInstalle au début
					'  de cette méthode de "BD SQL Server 2012" à "BD SQL Server 2005". La base de données en format 2005
					'  fonctionne dans toutes les versions de SQL Server depuis 2005. Elle n'a cependant pas été testée 
					'  complètement sous cette version-ci de l'application. Si vous avez des problèmes à l'utiliser, 
					'  téléchargez les exemples antérieurs à ceux de Visual Studio 2012.
					'
					'**********************************************************************************************************

					Return False

				End Try


				Debug.WriteLine("PhotoCat : Base de données attachée au serveur.")

			End If

		Catch ex As Exception
			'************************************************************************************************************************
			'Une erreur ici est habituellement causée par un mauvais nom de serveur. Pour corriger, activez le menu contextuel (clic
			'du bouton droit) sur la variable ServeurSQL dans la ligne qui suit, allez à sa définition et ajustez le nom du serveur
			'pour celui utilisé dans votre environnement.
			'************************************************************************************************************************
			PhotoData.ServeurSQL = PhotoData.ServeurSQL	'Inutile, mais aide à la navigation si on a besoin de régler le nom du serveur au début du cours.
			Throw
			Return False

		Finally
			If cmd.Connection.State = Data.ConnectionState.Open Then
				cmd.Connection.Close()
			End If

		End Try

		My.Settings.PhotoCatConnectionString = PhotoData.ConSqlClientSqlServer	'Ajustement nécessaire pour LINQ to ADO, parce que la ConnectionString change en fonction de la salle de cours

		Return True

		End Function

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
If you try it, you will set that this connects automatically to the master.
As previously indicated, that is only true if the users Logins default database is master.  This is not always the case.

Try changing your default database fom master and you will see what we mean.
0
 

Author Comment

by:Roebbelen
Comment Utility
I'm guessing I'm close. I have the following, Yet I'm getting an unhandled exception error


    //button Attach database
        private void btnAttachDB_Click(object sender, EventArgs e)
        {
            //   String fileLDF= txtLDF
            string txtDBName = this.txtDBName.Text;
            string fileMDF = this.txtMDF.Text;
            string fileLDF = this.txtLDF.Text;
            
          //  MessageBox.Show("No SQL Command");
            //var sqlcmd = new SqlCommand()
           
            string sql = String.Format(@" Use Master Exec sp_attach db @dbname='{0}',@Filename1=N'{1}',@Filename2=N'{2}';", txtDBName, fileMDF, fileLDF);
//string ckAttached = String.Format("select * from sys.databases where  Name like '{0},", txtDBName);
          
SqlConnection conn2 = new SqlConnection(String.Format("Data Source={0}; Initial Catalog=Master; Integrated Security=SSPI",srv)); // SRV is defined in method above see previous code
         
               SqlCommand command = new SqlCommand(sql, conn2);
              // SqlCommand dbisAttached = new SqlCommand(ckAttached, conn2);
               conn2.Open();
               command.ExecuteNonQuery(); 
             
             

           
                      //  MessageBox.Show(txtDBName+"Attached");

            //srv.AttachDatabase(databaseName,StringCollectionFiles,Owner);


        }

Open in new window

    //button Attach database
        private void btnAttachDB_Click(object sender, EventArgs e)
        {
            //   String fileLDF= txtLDF
            string txtDBName = this.txtDBName.Text;
            string fileMDF = this.txtMDF.Text;
            string fileLDF = this.txtLDF.Text;
            
          //  MessageBox.Show("No SQL Command");
            //var sqlcmd = new SqlCommand()
            

          //  string sql = String.Format("Create Database test4 ON (Name ='test4_Data',FILENAME='C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.ILVL\\MSSQL\\DATA\\Test4.mdf') LOG ON (NAME='test4_log', FILENAME='C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.ILVL\\MSSQL\\DATA\\Test41_log.ldf') FOR ATTACH With Enable_Broker", txtDBName, fileMDF, fileLDF);
            string sql = String.Format(@" Use Master Exec sp_attach db @dbname='{0}',@Filename1=N'{1}',@Filename2=N'{2}';", txtDBName, fileMDF, fileLDF);
            //string ckAttached = String.Format("select * from sys.databases where  Name like '{0},", txtDBName);
          SqlConnection conn2 = new SqlConnection(String.Format("Data Source={0}; Initial Catalog=Master; Integrated Security=SSPI",srv));
         
               SqlCommand command = new SqlCommand(sql, conn2);
              // SqlCommand dbisAttached = new SqlCommand(ckAttached, conn2);
               conn2.Open();
               command.ExecuteNonQuery(); 
             
             

           
                      //  MessageBox.Show(txtDBName+"Attached");

            //srv.AttachDatabase(databaseName,StringCollectionFiles,Owner);


        }

Open in new window

0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
The best thing to do, when you get an unhandler exception error is... to handle exceptions.

Exceptions should be handled in many places in an application, but specially when you are dealing with the external world (the server is extern to your application) or dealing with possible problems with securities and permissions (which is also the case with a database server).

Put a Try...Catch statement around your code, and see what you get in the exception:

Try
    conn2.Open();
    command.ExecuteNonQuery();
Catch ex As SqlException
    Debug.WriteLine(ex.ToString)
Catch ex As Exception
     Debug.WriteLine(ex.ToString)
End Try

Your Output window will give you information about the exception. You might want to put breakpoints on the 2 Debug.WriteLine commands so that you can get more information about what ex holds through the debugger.

If the problem is with the connection to the database, you will get in in the first Catch.
If it is with something else, you will get it in the second Catch.
If it is not catched, then it does not come from the 2 lines between Try and Catch. Include more of the code.

Once you have more precise about what the exception is, it is easier to find a solution.
0
 

Author Closing Comment

by:Roebbelen
Comment Utility
Thanks for the help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I have no idea why you selected my comment as the solution.  I suggest you post a comment in CS to have this changed.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

6 Experts available now in Live!

Get 1:1 Help Now