c#数据库: 9.删除和添加新字段/数据更新

先把原来数据表的sexy字段删除,然后重新在添加字段sexy,如果添加成功,sexy列的随机内容会更新.原数据表如下:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace StudentAlter
{internal class Program{static void Main(string[] args){Random random = new Random();string connectionString =                                                  //数据库连接字符串"Data Source=.\\SQLExpress;Database=aq;Trusted_Connection=true;";SqlConnection connection = new SqlConnection(connectionString);//创建数据库连接实例connection.Open();                                                         //打开数据库连接Console.WriteLine("数据库aq连接成功！");string sql = "ALTER TABLE staq_info DROP COLUMN sexy;";using (SqlCommand command = new SqlCommand(sql, connection)){command.ExecuteNonQuery();Console.WriteLine("字段sexy已删除。");}Console.ReadKey();//ALTER TABLE staq_info DROP COLUMN age, DROP COLUMN grade;//为表staq_info增加sexy字段SqlCommand cmd = new SqlCommand();                    //创建数据查询类实例cmd.Connection = connection;cmd.CommandText = "ALTER TABLE staq_info ADD sexy varchar(2)";cmd.ExecuteNonQuery();                                //执行添加sex字段SQL语句cmd.Dispose();//利用数据集对所有学生信息的sex字段赋值SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM staq_info","Data Source=.\\SQLExpress;Database=aq;Trusted_Connection=true;");DataSet dataSet = new DataSet();                                    //创建数据集adapter.Fill(dataSet);                                              //填充数据集int columnCount = dataSet.Tables[0].Columns.Count-1; //获取表总列数减1,for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)//i表格第几行{dataSet.Tables[0].Rows[i][columnCount] = random.Next(2) == 0 ? "男" : "女";//修改性别值。[columnCount] 中的值代表在第几列添加（初始是第0列）,本例是最后一行}SqlCommandBuilder builder = new SqlCommandBuilder(adapter);    //将数据集更新以便与数据库协调adapter.Update(dataSet);                                   //更新数据集到数据库                                                Console.WriteLine("tj成功！");Console.ReadKey();}}
}

本实例代码主要实现了向数据表中添加新字段。首先利用SqlConnection对象连接学生数据库，然后通过SqlCommand对象执行向学生信息表中增加性别字段的SQL语句，接着通过SqlDataAdapter对象查询修改后的学生表记录。此时所有记录的性别字段都为空，先将查询结果填充到数据集，然后通过逐行为数据集表中最后一列赋值，实现为性别字段赋值，接着创建SqlCommandBuilder协调数据集更新与数据库连接，最后调用SqlDataAdapter对象的Update方法实现将数据集的修改更新到数据库中。

运行C#代码:

然后在SQL Server Management Studio Management Studio 查看更新(先删除后添加)后的结果(先刷新):最后一列对比不同.

--------------------------------------- --------------------------------------

综合测试代码,删除和添加新字段及数据更新:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace StudentAlter
{internal class Program{static void Main(string[] args){ Random random = new Random();string connectionString =                                                  //数据库连接字符串"Data Source=.\\SQLExpress;Database=aq;Trusted_Connection=true;";SqlConnection connection = new SqlConnection(connectionString);//创建数据库连接实例connection.Open();                                                         //打开数据库连接Console.WriteLine("数据库aq连接成功！");string sql = "ALTER TABLE staq_info DROP COLUMN sexy;";try {using (SqlCommand command = new SqlCommand(sql, connection)){command.ExecuteNonQuery();Console.WriteLine("字段sexy已删除。");}} catch (Exception) { };string sq2 = "ALTER TABLE staq_info DROP COLUMN sexy1;";try{using (SqlCommand command = new SqlCommand(sq2, connection)){command.ExecuteNonQuery();Console.WriteLine("字段sexy1已删除。");}}catch (Exception) { };Console.ReadKey();// 为表staq_info增加age字段  try { using (SqlCommand cmd1 = new SqlCommand("ALTER TABLE staq_info ADD age int", connection)){cmd1.ExecuteNonQuery();}}catch (Exception) { };try {// 为表staq_info增加sexy字段  using (SqlCommand cmd = new SqlCommand("ALTER TABLE staq_info ADD sexy varchar(2)", connection)){cmd.ExecuteNonQuery();}}catch (Exception) { };SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM staq_info","Data Source=.\\SQLExpress;Database=aq;Trusted_Connection=true;");DataSet dataSet = new DataSet();                                    //创建数据集adapter.Fill(dataSet);                                              //填充数据集int columnCount = dataSet.Tables[0].Columns.Count-1; //获取表总列数减1,for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)//i表格第几行{dataSet.Tables[0].Rows[i][2] = random.Next(1, 5);//更新第2列(grade)的内容dataSet.Tables[0].Rows[i][columnCount-1] = random.Next(20,25);//更新倒数第2列的内容dataSet.Tables[0].Rows[i][columnCount] = random.Next(2) == 0 ? "男" : "女";//修改性别值。[columnCount] 中的值代表在第几列添加（初始是第0列）,本例是最后一行}SqlCommandBuilder builder = new SqlCommandBuilder(adapter);    //将数据集更新以便与数据库协调adapter.Update(dataSet);                                   //更新数据集到数据库                                                Console.WriteLine("tj成功！");Console.ReadKey();}}
}

运行C#代码: