c#连接sqlserver数据库
Command对象
作用：向数据库发送SQL语句
封装连接对象和执行的SQL语句
对数据库执行具体的操作，提供增、删、改、查的方法
属性
CommandText 需要封装的SQL语句或存储过程名称
Connection Command对象使用的数据库连接对象
方法
ExecuteNonQuery() 执行增、删、改操作
ExecuteScalar() 返回单一结果的查询 只能返回数据集的第一行第一列 常常用来执行聚合函数
ExecuteReader() 返回只读数据列表的查询
ExecuteNonQuery() 方法使用要点
执行insert update delete类型的语句
执行返回受影响的行数、一般是大于0的整数，0说明没有影响 ，-1表示执行出错
执行增、删、改步骤总结
1 创建Connection对象
2 组合sql语句insert update delete
3 创建Command对象 并封装Connection和sql语句
4 打开连接
5 执行ExecuteNonQuery()方法,返回受影响的行数
6 关闭连接
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class Program { string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; SqlConnection conn = null; public void OpenDB() { conn = new SqlConnection(connString); if(conn.State==ConnectionState.Closed) { conn.Open(); } Console.WriteLine("数据库已连接"); } public void CloseDB() { if(conn.State==ConnectionState.Open) { conn.Close(); } Console.WriteLine("数据库已断开"); } public int Insert(string sql) { SqlCommand cmd = new SqlCommand(sql, conn);//简单写法 //SqlCommand cmd = new SqlCommand //{ // CommandText = sql, // Connection = conn //}; //ExecuteNonQuery()执行增 删 改 操作 int result = cmd.ExecuteNonQuery();//返回影响的行数 return result; } static void Main(string[] args) { string sql = "insert into student(id,name,age,teacherID) values(5,'小王',25,105)"; Program p = new Program(); p.OpenDB(); int insertResult = p.Insert(sql); Console.WriteLine("insertResult:{0}", insertResult); p.CloseDB(); Console.ReadKey(); } } }
读取结果集
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class Program { string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; public void QueryAllStudents(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { Console.WriteLine("id:{0} name:{1} age:{2} teacherID:{3}", (int)reader["id"], (string)reader["name"], (int)reader["age"], (int)reader["teacherID"]); } reader.Close();//关闭读取器 conn.Close(); } static void Main(string[] args) { string sql = "select * from student"; Program p = new Program(); p.QueryAllStudents(sql); Console.ReadKey(); } } }
读取多个结果集 在sql server中我们可以这样 select * from student; select * from teacher; 同时执行两条查询语句
在c# 中
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class Program { string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; public void QueryAllStudents(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { Console.WriteLine("id:{0} name:{1} age:{2} teacherID:{3}", (int)reader["id"], (string)reader["name"], (int)reader["age"], (int)reader["teacherID"]); } if(reader.NextResult()) { while(reader.Read()) { Console.WriteLine("id:{0} name:{1} major:{2}", reader["id"], reader["name"], reader["major"]); } } reader.Close();//关闭读取器 conn.Close(); } static void Main(string[] args) { string sql = "select * from student;select * from teacher"; Program p = new Program(); p.QueryAllStudents(sql); Console.ReadKey(); } } }
实现一个简单的SQL Helper类
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class SQLHelper { private static string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; public static int GetSingleQuery(string sql)//一般用于聚合函数的查询 { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); int result = (int)cmd.ExecuteScalar(); conn.Close(); return result; } public static int Update(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); int result = cmd.ExecuteNonQuery();//返回影响的行数 conn.Close(); return result; } public static SqlDataReader GetReader(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); //SqlDataReader reader = cmd.ExecuteReader(); //reader.Close(); //conn.Close(); //这样写会报阅读器关闭时尝试调用 Read 无效错误 //解决方法 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭读取器时自动关闭connection连接 return reader; } static void Main(string[] args) { //测试代码 //string sql = "select * from student"; //SqlDataReader reader = GetReader(sql); //while(reader.Read()) //{ // Console.WriteLine("id:{0} name:{1} age:{2} teacherID:{3}", (int)reader["id"], reader["name"].ToString(), (int)reader["age"], (int)reader["teacherID"]); //} //reader.Close();//关闭读取器 //string sql = "select count(*) from student"; //int result = GetSingleQuery(sql); //Console.WriteLine("result stu count:{0}", result); //string sql = "update student set name='小李' where name='小王'"; //int result = Update(sql); //Console.WriteLine("update result:{0}", result); Console.ReadKey(); } } }
将查询到的数据以对象的形式返回
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class Test { private static string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; public Student QueryStuByID(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); Student stu = null; if(reader.Read()) { stu = new Student { Id = (int)reader["id"], Name = reader["name"].ToString(), Age = (int)reader["age"], TeacherID = (int)reader["teacherID"] }; } reader.Close(); conn.Close(); return stu; } static void Main(string[] args) { //测试代码 //string sql = "select * from student where id=3"; //Test t = new Test(); //Student stu = t.QueryStuByID(sql); //if(stu!=null) //{ // Console.WriteLine("stu name:{0}", stu.Name); //} Console.ReadKey(); } } class Student { private int id; private string name; private int age; private int teacherID; public int Id { get => id; set => id = value; } public string Name { get => name; set => name = value; } public int Age { get => age; set => age = value; } public int TeacherID { get => teacherID; set => teacherID = value; } } }
将查询到的数据以对象集合的方式返回 和上述代码类似
连表查询
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PracticeFive { class Test { private static string connString = "server=DESKTOP-BQDF638;database=test_four;uid=sa;pwd=123456"; public List<StudentTeacher> UnionQuery(string sql)//联合查询 { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); List<StudentTeacher> lstStuTea = new List<StudentTeacher>(); while(reader.Read()) { StudentTeacher stuTea = new StudentTeacher(); stuTea.Stu = new Student { Name = reader["stuName"].ToString() }; stuTea.Tea = new Teacher { Name = reader["teaName"].ToString(), Major = reader["major"].ToString() }; lstStuTea.Add(stuTea); } reader.Close(); conn.Close(); return lstStuTea; } static void Main(string[] args) { //因为学生表和老师表都有name字段 计算机无法区分 所有取别名来区分 string sql = "select a.name as stuName,b.name as teaName,b.major from student a inner join teacher b on a.teacherID=b.id"; Test test = new Test(); List<StudentTeacher> lst = test.UnionQuery(sql); foreach(var item in lst) { Console.WriteLine("student name:{0} teacher name:{1} teacher major:{2}", item.Stu.Name, item.Tea.Name, item.Tea.Major); } Console.ReadKey(); } } class Student { private int id; private string name; private int age; private int teacherID; public int Id { get => id; set => id = value; } public string Name { get => name; set => name = value; } public int Age { get => age; set => age = value; } public int TeacherID { get => teacherID; set => teacherID = value; } } class Teacher { private int id; private string name; private string major; public int Id { get => id; set => id = value; } public string Name { get => name; set => name = value; } public string Major { get => major; set => major = value; } } class StudentTeacher//联合查询时使用 { private Student stu; private Teacher tea; internal Student Stu { get => stu; set => stu = value; } internal Teacher Tea { get => tea; set => tea = value; } } }
