using System; using System.Data.SqlClient; namespace ConsoleApp { /// <summary> /// 使用C#直接修改表结构(添加列,删除列)【MS SQL SEVER】 /// https://www.cnblogs.com/LifeDecidesHappiness/p/15411540.html /// LDH @ 2021-10-15 /// </summary> internal class Program { public static void Main() { Console.Title = "使用C#直接修改表结构(添加列,删除列)【MS SQL SEVER】"; // 添加列 AddColumnIntoTableInMsSqlServer(); // 删除列 //DeleteColumnFromTableInMsSqlServer(); Console.ReadKey(); } /// <summary> /// 使用C#直接修改表结构(添加列)【MS SQL SEVER】 /// </summary> public static void AddColumnIntoTableInMsSqlServer() { var tableName = "CollegeInfo"; // 要插入列的表名 var columnName = "CollegeArea"; // 要插入列的列名 var columnControl = "nvarchar(100) null"; // 要插入列的类型限制 // 添加列 var sqlStrAddColumn = $"alter table {tableName} add {columnName} {columnControl}"; // 数据库连接字符串 var conStr = "Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=123456"; using (var con = new SqlConnection(conStr)) { con.Open(); using (var cmd = new SqlCommand(sqlStrAddColumn, con)) { try { cmd.ExecuteNonQuery(); Console.WriteLine("【修改表结构】添加列 成功!"); } catch (Exception e) { Console.WriteLine(e); Console.WriteLine("【修改表结构】添加列 失败!"); throw; } } } } /// <summary> /// 使用C#直接修改表结构(删除列)【MS SQL SEVER】 /// </summary> public static void DeleteColumnFromTableInMsSqlServer() { var tableName = "CollegeInfo"; // 要插入列的表名 var columnName = "CollegeArea"; // 要插入列的列名 // 删除列 var sqlStrDeleteColumn = $"alter table {tableName} drop column {columnName}"; // 数据库连接字符串 var conStr = "Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=123456"; using (var con = new SqlConnection(conStr)) { con.Open(); using (var cmd = new SqlCommand(sqlStrDeleteColumn, con)) { try { cmd.ExecuteNonQuery(); Console.WriteLine("【修改表结构】删除列 成功!"); } catch (Exception e) { Console.WriteLine(e); Console.WriteLine("【修改表结构】删除列 失败!"); throw; } } } } } }
/* SQL-建表CollegeInfo语句 */ USE TestDB; GO IF EXISTS (SELECT * FROM sys.tables WHERE name = 'CollegeInfo') BEGIN DROP TABLE CollegeInfo; END; CREATE TABLE CollegeInfo ( Name NVARCHAR(100) NOT NULL, Type NVARCHAR(10) NOT NULL, [Year] NVARCHAR(4) NOT NULL DEFAULT '2021', Sponsor NVARCHAR(100) NOT NULL, Remark NVARCHAR(MAX) NOT NULL ); SELECT Name, Type, Year, Sponsor, Remark FROM dbo.CollegeInfo;