Unable to Update A Table Schema Progmatically

[bushman]

Hello,

I have been struggling trying to update the schema of a database. All
I am trying to do is set the primary key of a table. However, I will
set the column, but it does not set it. When I open the DB in the
viewer the PK has not been set. I am using the following code. Is
there a special way I need to send the update sice I'm dealing with
the schema and not just data? I am developing in VS 2008/Win7 x64
Ultimate/.Net 3.5 SP1/C# -- The database is MS Sql (*.mdf) 2005.

I ran a few tests and I am able to add rows to my table successfully,
so I am wondering, coudl there possibly be something with my
connection string that is preventing me from updating the schema?

string strCon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""" +
DBPathAndName + @""";Integrated Security=True;Connect Timeout=30;User
Instance=True";

DataTable table = new DataTable();

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM ROOM",
strCon);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.FillSchema(table, SchemaType.Source);
dataAdapter.Fill(table);

try
{
DataColumn[] cols = { table.Columns["ROOM_ID"] };
table.PrimaryKey = cols;

dataAdapter.Update(table);

dataAdapter.Dispose();
commandBuilder.Dispose();
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}

 

[Arne Vajhøj]

I have been struggling trying to update the schema of a database. All
I am trying to do is set the primary key of a table. However, I will
set the column, but it does not set it. When I open the DB in the
viewer the PK has not been set. I am using the following code. Is
there a special way I need to send the update sice I'm dealing with
the schema and not just data? I am developing in VS 2008/Win7 x64
Ultimate/.Net 3.5 SP1/C# -- The database is MS Sql (*.mdf) 2005.

I ran a few tests and I am able to add rows to my table successfully,
so I am wondering, coudl there possibly be something with my
connection string that is preventing me from updating the schema?

string strCon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""" +
DBPathAndName + @""";Integrated Security=True;Connect Timeout=30;User
Instance=True";

DataTable table = new DataTable();

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM ROOM",
strCon);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.FillSchema(table, SchemaType.Source);
dataAdapter.Fill(table);

try
{
DataColumn[] cols = { table.Columns["ROOM_ID"] };
table.PrimaryKey = cols;

dataAdapter.Update(table);

dataAdapter.Dispose();
commandBuilder.Dispose();
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}

When I read the documentation for *DataAdapter, then it
seems very clear to me that it is just updating rows
not table structure.

You need to use SqlCommand and ALTER TABLE to do that.

Arne