Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    11

    Unanswered: database still 'exists' after deletion

    hi

    Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
    then it says the database still exists, even it is physically destroied.

    ------Here is the errors:
    System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
    at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
    n breakConnection)

    ------The evidence that the database doesn't exist physically:
    Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

    ------The code:
    /*
    * C# code to programmically create
    * database and table. It also inserts
    * data into the table.
    */

    using System;
    using System.Collections.Generic;
    using System.Text;

    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;

    namespace riskWizard
    {
    public class RiskWizard
    {
    // Sql
    private string connectionString;
    private SqlConnection connection;
    private SqlCommand command;

    // Database
    private string databaseName;
    private string currDatabasePath;
    private string database_mdf;
    private string database_ldf;

    public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
    {
    this.databaseName = databaseName;
    this.currDatabasePath = currDatabasePath;
    this.database_mdf = database_mdf;
    this.database_ldf = database_ldf;
    }

    private void executeSql(string sql)
    {
    // Create a connection
    connection = new SqlConnection(connectionString);

    // Open the connection.
    if (connection.State == ConnectionState.Open)
    connection.Close();

    connection.ConnectionString = connectionString;
    connection.Open();

    command = new SqlCommand(sql, connection);
    try
    {
    command.ExecuteNonQuery();
    }
    catch (SqlException e)
    {
    Console.WriteLine(e.ToString());
    }
    }

    public void createDatabase()
    {
    string database_data = databaseName + "_data";
    string database_log = databaseName + "_log";

    connectionString
    = "Data Source=.\\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

    string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
    + "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
    + "maxsize=5,filegrowth=10%)log on"
    + "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
    + "maxsize=20,filegrowth=1)";

    executeSql(sql);
    }

    public void dropDatabase()
    {
    connectionString
    = "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

    string sql = "DROP DATABASE " + databaseName;

    executeSql(sql);
    }

    // Create table.
    public void createTable(string tableName)
    {
    connectionString
    = "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

    string sql = "CREATE TABLE " + tableName +
    "(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
    "name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

    executeSql(sql);
    }

    // Insert data
    public void insertData(string tableName)
    {
    string sql;

    connectionString
    = "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

    sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
    "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
    executeSql(sql);

    sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
    "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
    executeSql(sql);

    sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
    "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
    executeSql(sql);

    sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
    "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
    executeSql(sql);
    }

    public static void Main(String[] argv)
    {
    string databaseName = "riskDatabase";
    string currDatabasePath = "E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data";
    // Need to be more flexible.
    string database_mdf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\ \App_Data\\riskDatabase.mdf'";
    string database_ldf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\ \App_Data\\riskDatabase.ldf'";

    RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
    riskWizard.createDatabase();
    riskWizard.createTable("userTable");
    riskWizard.insertData("userTable");
    //riskWizard.dropDatabase();
    }
    }
    }

  2. #2
    Join Date
    Apr 2006
    Posts
    11

    Have someone tried to create a database then drop it?

    Have someone tried to create a database then drop it in runtime?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •