数据库操作(增删改)
删除 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace ConsoleApplication1 { class Program4 { static void Main4(string[] args) { //用户输入要删除的数据主键值 Console.WriteLine("请输入要删除的代号:"); string code = Console.ReadLine(); //判断该数据存不存在 SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from Info where Code='"+code+"'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { //说明该数据存在 Console.WriteLine("查到该数据,是否要执行删除操作,如果要删除请输入:1"); int sc = Convert.ToInt32(Console.ReadLine()); if (sc == 1) { //删除 dr.Close(); //关闭读取器 cmd.CommandText = "delete from Info where Code='"+code+"'"; cmd.ExecuteNonQuery(); Console.WriteLine("删除成功!"); } else { //不删除 dr.Read(); string sex = Convert.ToBoolean(dr[2])?"男":"女"; string nation = MinZu(dr[3].ToString()); string str = "代号:"+dr[0]+"\t姓名:"+dr[1]+"\t性别:"+sex+"\t民族:"+nation+"\t生日:"+dr[4]; Console.WriteLine(str); } } else { //数据不存在 Console.WriteLine("输入的代号错误!"); } conn.Close(); Console.ReadLine(); } static string MinZu(string code) { string name=""; SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select Name from Nation where Code = '" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); name = dr[0].ToString(); } conn.Close(); return name; } } } 添加 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace ConsoleApplication1 { class Program3 { static void Main3(string[] args) { //让用户输入要添加的内容 Console.WriteLine("请输入要添加的代号:"); string code = Console.ReadLine(); Console.WriteLine("请输入姓名:"); string name = Console.ReadLine(); Console.WriteLine("请输入性别:"); bool sex = Console.ReadLine()=="男"?true:false; Console.WriteLine("请输入民族:"); string nation = Console.ReadLine(); Console.WriteLine("请输入生日:"); string birthday = Console.ReadLine(); string nationcode = "n001"; //将民族名称转为名族代号 SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select Code from Nation where Name = '"+nation+"'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); nationcode = dr[0].ToString(); } conn.Close(); //往Info表添加数据 cmd.CommandText = "insert into Info values('"+code+"','"+name+"','"+sex+"','"+nationcode+"','"+birthday+"')"; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); Console.WriteLine("添加成功!"); Console.ReadLine(); } } } 修改 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace ConsoleApplication1 { class Program2 { static void Main2(string[] args) { //造连接字符串 string connstring = "server=.;database=mydb;user=sa;pwd=123"; //造连接对象 SqlConnection conn = new SqlConnection(connstring); //造命令对象 SqlCommand cmd = conn.CreateCommand(); //给SQL语句 cmd.CommandText = "update Info set Name='张三' where Code='p002'"; //打开连接 conn.Open(); //执行SQL语句 int n = cmd.ExecuteNonQuery(); if (n > 0) { Console.WriteLine("添加成功!"); } else { Console.WriteLine("添加失败!"); } //关闭连接 conn.Close(); Console.ReadLine(); } } }