Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();