如何使用c#向sql server添加列、描述和列类型
本文关键字:描述 类型 添加 server 何使用 sql | 更新日期: 2023-09-27 18:08:22
我有一个包含描述和类型的字段列表,需要添加到表中。
。SomeField SomeDescription/十进制
如何使用c#将具有描述和类型的列添加到sql server 2012数据库中?我知道如何通过sql添加列,但我需要能够做到这一点在c#
这是我想到的解决方案。
基本上,我使用sql(即Alter table等…)添加列
然后调用一个现有的sql存储过程: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);
}
像这样:
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);
}