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#
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);
}
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);
}
