using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace TestConsole { /// <summary> /// 测试类 /// </summary> [Serializable] public class TestDemo { /// <summary> /// 数据库连接字符串 /// </summary> private readonly string connectionString = "server=.;database=TestDB;uid=sa;pwd=123456"; /// <summary> /// 从数据库中查询所有记录 /// </summary> /// <returns></returns> private DataTable GetTableFromDatabase() { using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open(); SqlDataAdapter sda = new SqlDataAdapter("select Id,Name,ValueA,ValueB from TestTable", conn); DataSet ds = new DataSet(); sda.Fill(ds); DataTable dbTable = null; if (ds != null) { dbTable = ds.Tables[0]; } return dbTable; } catch (Exception e) { throw e; } } } /// <summary> /// 修改 /// </summary> /// <param name="value"></param> /// <param name="nameValue"></param> private void Update(int value, string nameValue) { string updateSql = "update TestTable set ValueA=" + value + " where Name='" + nameValue + "'"; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { try { conn.Open(); int rows = cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException e) { conn.Close(); throw e; } } } } /// <summary> /// 从内存获取DataTable /// </summary> /// <returns></returns> private DataTable GetTableFromMemory() { DataTable dt = new DataTable("TestTable"); dt.Columns.Add("Id", typeof(System.Int32)); dt.Columns.Add("Name", typeof(System.String)); dt.Columns.Add("ValueA", typeof(System.Int32)); dt.Columns.Add("ValueB", typeof(System.String)); DataRow row; row = dt.NewRow(); row["Id"] = 1; row["Name"] = "ItemA"; row["ValueA"] = 20; dt.Rows.Add(row); row = dt.NewRow(); row["Id"] = 67; row["Name"] = "ItemC"; row["ValueA"] = 90; dt.Rows.Add(row); return dt; } /// <summary> /// 测试方法 /// </summary> public void TestMethod() { String nameValue = "ItemC"; //从内存获取DataTable,并找到值为ItemC的记录 DataTable memoryTable = GetTableFromMemory(); DataRow[] memoryArr = memoryTable.Select("Name='" + nameValue + "'"); int valueA = 0; int.TryParse(memoryArr[0]["ValueA"].ToString(), out valueA); //将内存表中Name=ItemC的ValueA更新到数据表中 Update(valueA, nameValue); //将数据库中的ValueB数据栏及其对应的值添加到内存表中 DataTable dbTable = GetTableFromDatabase(); if (dbTable != null && dbTable.Rows.Count > 0) { //遍历数据表中的记录 foreach (DataRow dr in dbTable.Rows) { DataRow[] tempDR = memoryTable.Select("Name='" + dr["Name"] + "'"); if (tempDR.Length > 0) { tempDR[0]["ValueB"] = dr["ValueB"]; } else { DataRow row = memoryTable.NewRow(); row["Id"] = dr["Id"]; row["Name"] = dr["Name"]; row["ValueA"] = dr["ValueA"]; row["ValueB"] = dr["ValueB"]; memoryTable.Rows.Add(row); } } } } } }