How to add several tables tables on c# code?

Question

Creating an application and can't figure out how I can create multiple tables in an sql database by clicking on a form? I have 2 connection. 1 connection for create database, 2 connection for create table in database

public partial class Form1 : Form
{
    static string constring = ConfigurationManager.ConnectionStrings["Test.Properties.Settings.Setting"].ConnectionString;
    SqlConnection connstringsql = new SqlConnection(constring);

    //for create table in database
    static string create =
ConfigurationManager.ConnectionStrings["Test.Properties.Settings.Setting1"].ConnectionString;
    SqlConnection connscreate = new SqlConnection(create);

    public Form1()
    {
        InitializeComponent();
    }


    private void button1_Click(object sender, EventArgs e)
    {

        //create table
        connscreate.Open();
        string sqlqueryone = "CREATE TABLE test";
        SqlCommand sqlcomm = new SqlCommand(sqlqueryone, connscreate);
        connscreate.Close();
    }

When I click on the button in the base to create several tables.

What's wrong with the code that you have? What is it doing or not doing? — rory.ap
– rory.ap, Commented Jun 26, 2019 at 12:14
Do not reuse instances of SqlConnection; this brings nothing but headaches. Create them as needed and keep them in using blocks with as small a scope as reasonable (ditto for SqlCommand). An SqlConnection represents a handle to a pooled connection; you do not need to optimize their creation. Also, obviously, your CREATE TABLE statement as given is invalid; a table must have at least one column. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 26, 2019 at 12:17
Generally speaking, you don't want to have your application create table(s). This is something that you would want to do as part of the setup/deployment of an application. — Tony Abrams
– Tony Abrams, Commented Jun 26, 2019 at 12:20

GuidoG · Accepted Answer · 2019-06-26 14:47:17Z

Creating tables in code is not standard and I have no clue why you would want to do that.
But your code needs much improvement, I would start with making a class for all your database operations.
I will code a small example to get you going

public partial class MyDatabase
{
    private string _connectionString;

    public MyDatabase(string connectionString)
    {
         _connectionString = connectionString;
    }

    public void ExecuteSql(string sqlStatement)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
             conn.Open();
             using (SqlCommand command = new SqlCommand(sqlStatement))
             {
                  command.Connection = conn;
                  command.ExecuteNonQuery();
             }
        }
    }
}

and now you can use this as often as you need it, for example

private void button1_Click(object sender, EventArgs e)
{
    MyDatabase db = new MyDatabase("your connection string");

    db.ExecuteSql("create table test1 (id int, name varchar(10))");
    db.ExecuteSql("create table test2 (id int, name varchar(10))");
    ....
}

This is not a complete solution, there are other things you need to solve.
For example before creating a table you need to check if it not already exists
This example is intended to get you on a better way of doing things then you are doing now

Saeid Amini · Accepted Answer · 2019-06-26 13:12:29Z

0

    private void createDatabase_Click(object sender, EventArgs e)
    {
        string constring = "Data Source =HALLR3C04;User ID=sa;Password=123456;";
        SqlConnection connstringsql = new SqlConnection(constring);

        //create table
        connstringsql.Open();
        string sqlqueryone = "CREATE DATABASE " + textBoxDatabaseName.Text;
        SqlCommand sqlcomm = new SqlCommand(sqlqueryone, connstringsql);
        sqlcomm.ExecuteNonQuery();
        connstringsql.Close();

        listBoxDatabases.Items.Add(textBoxDatabaseName.Text);
    }

    private void createTable_Click(object sender, EventArgs e)
    {
        string constring = $"Data Source =HALLR3C04;User ID=sa;Initial Catalog = {listBoxDatabases.SelectedItem.ToString()};Password=123456;";
        SqlConnection connstringsql = new SqlConnection(constring);

        //create table
        connstringsql.Open();
        string sqlqueryone = $@"create table {textBoxTableName.Text}
                                (
                                    Id int IDENTITY PRIMARY KEY,
                                    Name VARCHAR(50)
                                ) ";
        SqlCommand sqlcomm = new SqlCommand(sqlqueryone, connstringsql);
        sqlcomm.ExecuteNonQuery();
        connstringsql.Close();
    }

answered Jun 26, 2019 at 13:12

Saeid Amini

1,3055 gold badges20 silver badges27 bronze badges

2 Comments

GuidoG Over a year ago

always put SqlConnection and SqlCommand inside a using. Never teach bad idea's to people that are still learning

Sean Lange Over a year ago

This doesn't handle the connection or command objects properly and it is promoting sql injection. It also needs at least a rudimentary check that the table doesn't exist or this is going to fail.

Collectives™ on Stack Overflow

How to add several tables tables on c# code?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related