C# - Create SQL Server table programmatically

Question

I am trying to create a SQL Server table programmatically. Here is the code.

using (SqlConnection con = new SqlConnection(conStr))
{

    try
    {
        //
        // Open the SqlConnection.
        //
        con.Open();
        //
        // The following code uses an SqlCommand based on the SqlConnection.
        //
        using (SqlCommand command = new SqlCommand("CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime);", con))
            command.ExecuteNonQuery();

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

When I'm running this application second time I'm getting an exception:

"There is already an object named 'Customer' in the database"

but when I check database I don't see such a table.
Here is my connection string.

<connectionStrings>
  <add name ="AutoRepairSqlProvider" connectionString=
     "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AutoRepairDatabase.mdf;
     Integrated Security=True;User Instance=True"/>
</connectionStrings>

When I am running select query; I am getting results from existing tables so I think connection string should be OK. Hope you'll see the problem :/

That's not possible ;) You're checking the incorrect database or the table is there and you don't see for some reason. — Claudio Redi
– Claudio Redi, Commented Oct 25, 2013 at 11:51
I'm checking correct database, because i can read data from it — user2412672
– user2412672, Commented Oct 25, 2013 at 11:53

shA.t · Accepted Answer · 2017-08-27 05:09:48Z

12

You haven't mentioned the Initial catalog name in the connection string. Give your database name as Initial Catalog name.

<add name ="AutoRepairSqlProvider" connectionString=
     "Data Source=.\SQLEXPRESS; Initial Catalog=MyDatabase; AttachDbFilename=|DataDirectory|\AutoRepairDatabase.mdf;
     Integrated Security=True;User Instance=True"/>

edited Aug 27, 2017 at 5:09

shA.t

17k5 gold badges59 silver badges121 bronze badges

answered Oct 25, 2013 at 11:54

Kurubaran

8,9625 gold badges47 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ramesh Rajendran Over a year ago

oh ! The database name was not their in him connection string . But how to show this error message There is already an object named 'Customer' in the database ??

Damien Over a year ago

@Ramesh - You've probably created 'Customer' in the 'Master' database.

S.N · Accepted Answer · 2019-06-25 07:45:59Z

9

First, check whether the table exists or not. Accordingly, create table if doesn't exist.

var commandStr= "If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime)";

using (SqlCommand command = new SqlCommand(commandStr, con))
command.ExecuteNonQuery();

edited Jun 25, 2019 at 7:45

answered Oct 25, 2013 at 12:00

S.N

5,1685 gold badges34 silver badges51 bronze badges

Comments

Giannis Paraskevopoulos · Accepted Answer · 2013-10-25 11:55:03Z

4

For managing DataBase Objects in SQL Server i would suggest using Server Management Objects

answered Oct 25, 2013 at 11:55

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

2 Comments

Fandango68 Over a year ago

This should have been the proper and accepted answer. Everything else above is ancient technology

NoChance Over a year ago

@Fandango68, but if you want to give your application to a user, and have initial data populated, then you have to create database artifacts as part of your application.

Ramesh Rajendran · Accepted Answer · 2013-10-25 11:53:38Z

1

Try this

Check if table have there , and drop the table , then create

using (SqlCommand command = new SqlCommand("IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#Customer%')
DROP TABLE #Customer CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime);", con))

answered Oct 25, 2013 at 11:53

Ramesh Rajendran

38.9k49 gold badges159 silver badges241 bronze badges

Comments

Dejan · Accepted Answer · 2015-12-23 14:59:09Z

1

If you don't like remembering SQL syntax, using Mig# you can simply:

var schema = new DbSchema(ConnectionString, DbPlatform.SqlServer2014);
schema.Alter(db => db.CreateTable("Customer")
     .WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
     .WithNotNullableColumn("First_Name", DbType.String).OfSize(50)
     .WithNotNullableColumn("Last_Name", DbType.String).OfSize(50)
     ...);

If you are not sure if it already exists, call DropIfExists before:

db.Tables["Customers"].DropIfExists();

answered Dec 23, 2015 at 14:59

Dejan

10.5k8 gold badges84 silver badges129 bronze badges

Comments

Nagendra Kumar Elli · Accepted Answer · 2015-12-21 05:59:33Z

0

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlCommend
{
    class sqlcreateapp
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnection conn = new SqlConnection("Data source=USER-PC; Database=Emp123;User Id=sa;Password=sa123");
                SqlCommand cmd = new SqlCommand("create table <Table Name>(empno int,empname varchar(50),salary money);", conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Created Successfully...");
                conn.Close();
            }
            catch(Exception e)
            {
                Console.WriteLine("exception occured while creating table:" + e.Message + "\t" + e.GetType());
            }
            Console.ReadKey();
        }
    }
}

answered Dec 21, 2015 at 5:59

Nagendra Kumar Elli

791 silver badge1 bronze badge

1 Comment

arpan.r Over a year ago

Please add some more details

Senjuti Mahapatra · Accepted Answer · 2016-12-23 06:32:23Z

0

Try this:

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True");
    try
    {
        cn.Open();
        SqlCommand cmd = new SqlCommand("create table Employee (empno int,empname varchar(50),salary money);", cn);
        cmd.ExecuteNonQuery();
        lblAlert.Text = "SucessFully Connected";
        cn.Close();
    }
    catch (Exception eq)
    {
        lblAlert.Text = eq.ToString();
    }
}

edited Dec 23, 2016 at 6:32

Senjuti Mahapatra

2,6004 gold badges29 silver badges41 bronze badges

answered Dec 23, 2016 at 5:42

Parmar Jigar

1

Collectives™ on Stack Overflow

C# - Create SQL Server table programmatically

7 Answers 7

2 Comments

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related