How to add column and description and column type to sql server using c#

Question

I have a list of fields with descriptions and types that needs to be added to a table.

Eg. SomeField/SomeDescription/Decimal

How would I go about adding this columns with descrption and type to a sql server 2012 db using c#? I know how to add the column via sql, but I need to be able to do it in c#

Answer 1

Heres the solution I came up with.
Basically I added the columns using sql (i.e. Alter table etc...)
Then I called an existing sql stored procedure: sp_addextendedproperty

string[] allLines = File.ReadAllLines(@"C:\Table.csv");

            var query = from line in allLines
                        let data = line.Split(',')
                        select new
                        {
                            FieldName = data[0],
                            Description = data[1]
                        };


            try
            {
                string connection = "my connection string";
                using (SqlConnection conn = new SqlConnection(connection))///add your connection string
                {                    
                    SqlCommand command = new SqlCommand();
                    conn.Open();

                    using (SqlTransaction trans = conn.BeginTransaction())
                    {
                        command.Connection = conn;
                        command.Transaction = trans;

                        foreach (var item in query)
                        {
                            String sql = string.Format("ALTER TABLE MyTable ADD {0} Decimal(18,6)", item.FieldName.ToString());
                            command.CommandText = sql;
                            command.ExecuteNonQuery();
                        }

                        trans.Commit();
                    }


                    conn.Close();
                    conn.Open();
                    SqlTransaction transaction = conn.BeginTransaction();

                    using (SqlCommand cmd = new SqlCommand("sp_addextendedproperty", conn, transaction))
                    {


                            cmd.CommandType = CommandType.StoredProcedure;


                            foreach (var item in query)
                            {
                                cmd.Parameters.Clear();

                                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = "MS_Description";
                                cmd.Parameters.Add("@value", SqlDbType.VarChar).Value = item.Description.ToString();
                                cmd.Parameters.Add("@level0type", SqlDbType.VarChar).Value = "SCHEMA";
                                cmd.Parameters.Add("@level0name", SqlDbType.VarChar).Value = "dbo";
                                cmd.Parameters.Add("@level1type", SqlDbType.VarChar).Value = "TABLE";
                                cmd.Parameters.Add("@level1name", SqlDbType.VarChar).Value = MyTable;
                                cmd.Parameters.Add("@level2type", SqlDbType.VarChar).Value = "COLUMN";
                                cmd.Parameters.Add("@level2name", SqlDbType.VarChar).Value = item.FieldName.ToString();


                                cmd.ExecuteNonQuery();
                            }

                            transaction.Commit();

                    }                    
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }   

Answer 2

Something like that:

string connection = "your connection string";
try
{
  using(SqlConnection conn = new SqlConnection(connection))///add your connection string
  {
    String sql="ALter Table MyTable add SomeField Decimal(18,3)";
    conn.Open();
    using( SqlCommand command = new SqlCommand(sql,conn))
    {
      command.ExecuteNonQuery();
    }
  }
}
catch (Ecxeption ex)
{
 MessageBox.Show(ex.Message);
}