Create Database

Question

sonia sardana -13 Posting Whiz

17 Years Ago

hey frnds, I m creating the database at page load,I m able to do dat.I just want to do check that if the table is already existing,then do not create the table.below code create table-

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

public partial class _Default : System.Web.UI.Page 
{
    SqlCommand cmd;
    string Query;
    SqlConnection conn;

       protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "USE sonia";
             cmd=new SqlCommand (Query ,conn) ;
             cmd.ExecuteNonQuery ();
             Query = "CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";
             cmd = new SqlCommand(Query,conn);
             cmd.ExecuteNonQuery();
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }
}

.net microsoft:sql-server

2 Contributors
8 Replies
122 Views
4 Days Discussion Span
Latest Post 17 Years Ago Latest Post by sonia sardana

Dani AI

Generated 9 Months Ago

Short answer: yes — but do it from a server-level connection (not by opening a connection that names a DB that doesn't exist), and run the T‑SQL on SQL Server (via SqlCommand). As pointed out, checks like OBJECT_ID and DB_ID run on the server. The C# error you saw (Too many characters in character literal) happened because you tried to put raw T‑SQL into C# code without quoting it as a string (in C#, single quotes are for a single char, not a string).

Recommended workflow (safe and predictable)

Open a connection to the server/master catalog (for example, Database=master in the connection string).
Check existence using sys catalog views (or DB_ID/sys.databases) and only then issue CREATE DATABASE.
Close that connection and open a new connection that targets the newly created database to create tables.
Always use parameterized commands and proper exception handling around CREATE statements (permission errors, race conditions, file-location issues).

Example pattern (outline — adapt names/rights as needed):

using (var serverConn = new SqlConnection("Server=MyServer\\SQLEXPRESS;Integrated Security=true;Database=master"))
{
  serverConn.Open();
  // query sys.databases to see if DB exists, create if missing
  // then close, and open a new SqlConnection that uses Database=YourDbName
  // run table creation statements there
}

Troubleshooting & cautions

CREATE DATABASE requires appropriate SQL Server permissions and rights to write files. If using Integrated Security, the app pool/service account must have those rights.
Avoid creating DBs on every page load in production — use deployment-time provisioning, migrations (EF/SSDT) or a guarded admin/setup step instead. Programmatic creation during normal page requests can cause race conditions and security risks.
For checking/creating tables prefer schema-qualified names (dbo.Table) and use sys.tables or OBJECT_ID with the schema to avoid false negatives.

See Microsoft docs for the server functions and statements: DB_ID, OBJECT_ID and CREATE DATABASE for details (DB_ID, OBJECT_ID, CREATE DATABASE).

All 8 Replies

sknake 1,622 Senior Poster

17 Years Ago

Sonia: This is an SQL question you happen to have the query embedded in an ASP.NET application. Please post these question to the SQL forums in the future.

To answer your question:

IF OBJECT_ID('TestTable123', 'U') IS NULL
CREATE TABLE TestTable123
(
  Field varchar(10)
)

OBJECT_ID('<name>', 'U') returns the table id. If the value is null the table does not exist. 'U' is for table, 'FN' for function, etc.

sknake 1,622 Senior Poster

17 Years Ago

Because OBJECT_ID should be part of the query, not C# code.

Query = "IF OBJECT_ID('EmpInfo', 'U') IS NULL CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sonia sardana -13 Posting Whiz · Answer 1 · 2009-07-25T01:23:53+00:00

soory from the next time i rember,But sir its not working.

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

public partial class _Default : System.Web.UI.Page 
{
    SqlCommand cmd;
    string Query;
    SqlConnection conn;

       protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "USE sonia";
             cmd=new SqlCommand (Query ,conn) ;
             cmd.ExecuteNonQuery ();
               IF (OBJECT_ID('EmpInfo','U') is null )
                  {
                   Query = "CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";
                    cmd = new SqlCommand(Query,conn);
                    cmd.ExecuteNonQuery();
                 }
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }
}

ERROR- Too many characters in character literal int this line ( IF (OBJECT_ID('EmpInfo','U') is null ))

sonia sardana -13 Posting Whiz · Answer 2 · 2009-07-25T22:33:36+00:00

thx owesome...hey tell me one thing if i want to check that database already exists or not, 'U' for table,'F' for function ..wats for database????

sknake 1,622 Senior Poster Featured Poster · Answer 3 · 2009-07-25T23:09:53+00:00

OBJECT_ID() is for database objects as the name implies, and not the database itself. For that you will use DB_ID()

IF DB_ID('NAME') IS NULL
  Print 'Database does not exist'

You can call OBJECT_NAME() and DB_NAME() to get the name of the objects from the ID.
Please do not forget to mark this thread as solved if you have found a solution to your problem.

sonia sardana -13 Posting Whiz · Answer 4 · 2009-07-27T01:26:18+00:00

hello sir tell me one thing more,is it posible to create d datbase at page load in ASP.net....cz if no database exists..then how we open connection..& without opening connection,how can we craete database...

protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "IF DB_ID('Sonia') IS NULL CREATE DATABASE Sonia";
             cmd = new SqlCommand(Query, conn);
             cmd.ExecuteNonQuery();
             Query = "IF OBJECT_ID('EmpInfo', 'U') IS NULL CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";               
            cmd = new SqlCommand(Query,conn);
             cmd.ExecuteNonQuery();
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=Sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }

ERRORS- conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=Sonia;Integrated Security=true;");
Error is there no databse exists

sknake 1,622 Senior Poster Featured Poster · Answer 5 · 2009-07-27T08:46:54+00:00

How many questions are you going to ask in a single thread? Mark this as solved, start a new one with the proper subject, and I will be glad to help you

sonia sardana -13 Posting Whiz · Answer 6 · 2009-07-28T08:47:36+00:00

sonia sardana -13 Posting Whiz

17 Years Ago

sorry...

Create Database

Dani AI

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers