bug in a table creation using smo

Question

 i am trying to create a simple table testtable using SQL Server Management Objects (SMO) in a winforms application.

Database db = srv.Databases["model"];
Table t = new Table(db,"testtable");

DataType dt = new DataType(SqlDataType.Int);
Column c = new Column(t, "ID", dt);
c.Nullable = false;
t.Columns.Add(c);

dt = new DataType(SqlDataType.VarChar, 100);
c = new Column(t, "Name", dt);
t.Columns.Add(c);

try
{
    Index i = new Index(t, "PK");
    IndexedColumn ic = new IndexedColumn(i, "ID");
    i.IndexedColumns.Add(ic);
    i.IndexKeyType = IndexKeyType.DriPrimaryKey;
    i.Create();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message.ToString());
}

but each time it shows

index Create failed for Index PK ? and throws an exception

Answer 1

Hello,

You first have to create the table, before you can create an index for it.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

sir,

does .. table t =new table(db,"testtable");

in a above code will not create a table or i am doing any code mistake?

Answer 3

Server srv = new Server("node39\\SQLExpress");

srv.ConnectionContext.LoginSecure = true;
            srv.ConnectionContext.Connect();

            try
            {

                Database db = srv.Databases["master"];
                Table t = new Table(db, "testtable");

                if (t != null)
                {
                  

                    DataType dt = new DataType(SqlDataType.Int);
                    Column c = new Column(t, "ID", dt);
                    c.Nullable = false;
                    c.IdentityIncrement = 1;
                    c.Identity = true;
                    t.Columns.Add(c);

                    dt = new DataType(SqlDataType.VarChar, 100);
                    c = new Column(t, "Name", dt);
                    t.Columns.Add(c);



                    Index i = new Index(t, "pk");
                    IndexedColumn ic = new IndexedColumn(i, "ID", true);
                    i.IndexedColumns.Add(ic);
                    i.IndexKeyType = IndexKeyType.DriPrimaryKey;
                    i.Create();
                }
                MessageBox.Show("table created successfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }

this is a complete code  that i am using. but it throws an exception message: index pk failed to create.

what possible mistake can it be ?

Answer 4

sir,

does .. table t =new table(db,"testtable");

in a above code will not create a table or i am doing any code mistake?

No, it does not create a persistent table, only a new table design in memory.

You have to call table.Create() to create the physical table in the database; equal as you do it for the index. An as long as the table don't exists, you can create an index for it.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 5

thanks sir.

i was missing.. table.create()..

problem is solved.