How to alter a table using SMO

Question

I'm using SMO to create table dynamically in Sqlserver 2008.now I would like to add new columns for existing table using SMO, but I'm trying to execute the code the finally getting run time exception as "alter fail to table".My code snippet as

 public static bool Altertable(DynamicTable dynamicTable)
        {

            if (myDatabase.Tables.Contains(dynamicTable.Name))
            {
                Table myalterEmpTable = myDatabase.Tables[dynamicTable.Name];

                var collist = new List<Column>();

                foreach (var item in dynamicTable.Columns)
                {
                    Column col = myalterEmpTable.Columns[item.Name];
                    if (col == null)
                    {
                        Column cols = new Column(myalterEmpTable,  item.Name , DataType.UserDefinedDataType(item.Type));
                        cols.Nullable = item.IsNullable;
                        cols.Default = "";
                        collist.Add(cols);
                    }
                }
/*Here I'm getting newly added columns only*/


                foreach (var item in collist)
                {
                    myalterEmpTable.Columns.Add(item);
                }
                try
                {

                   myalterEmpTable.Alter();
                }
                catch (SmoException ex)
                {

                    throw ex;
                }
}
}

Finally I'm getting exception as "alter fail to table".even googled also no help ..So my question is, what is the best way to do this using C#?

You may have problem with datatype, Just ensure the you have that datatype or try with other built in datatypes. — Rajasekar Gunasekaran, Feb 22 '13 at 16:51

score 1 · Accepted Answer · answered Feb 24 '13 at 03:02

Thank you for your help..

I solved it as changed my database connection string initialization part.then it's working fine..

 string connns = ConfigurationManager.AppSettings["conn"];
                SqlConnection sqlConn = new SqlConnection(ConfigurationManager.AppSettings["conn"]);
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connns);
                string password = builder.Password;
                string userid = builder.UserID;
                ServerConnection smoConn = new ServerConnection();
                smoConn.ServerInstance = sqlConn.DataSource;
                instanceServer = new Server(smoConn);
                instanceServer.ConnectionContext.LoginSecure = false;
                instanceServer.ConnectionContext.Login = userid;
                instanceServer.ConnectionContext.Password = password;
                projectDatabase = instanceServer.Databases[sqlConn.Database];

How to alter a table using SMO

1 Answers1