C# 数据库操作

using System;
using System.Text;
using System.Data.SqlClient;
namespace App{
    class MyClass{
        public static void Main(string[] args){
            string str="Data Source=.;Initial Catalog=mlh;Integrated Security=True";
            using ( SqlConnection con = new SqlConnection(str)) {
                con.Open();
                string sql = "select * from student";
                using(SqlCommand cmd = new SqlCommand(sql,con)){
                    Console.WriteLine(cmd.ExecuteNonQuery());
                    Console.WriteLine(cmd.CommandText);
                    //Console.WriteLine(cmd.ExecuteReader());
                    SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()){
                        Console.WriteLine(reader["id"]);//读取id
                        Console.WriteLine(reader["name"]);//读取name字段
                    }
                }
            }
        }
    }
}

 dotnet core webapi 操作数据返回json格式数据；

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;

namespace webapi.Controllers
{
    [Route("api/[controller]")]
        [ApiController]
            public class ValuesController : ControllerBase
        {
            // GET api/values
            [HttpGet]
                public List<Dictionary<string,int>> Get(){
                    string str= "Data Source=.;Initial Catalog=mlh;Integrated Security=True";
                    SqlConnection con = new SqlConnection(str);
                    con.Open();
                    string query ="select * from student";
                    SqlCommand cmd = new SqlCommand(query,con);
                    SqlDataReader reader =cmd.ExecuteReader();
                    List<Dictionary<string,int>> list = new List<Dictionary<string,int>>();
                    while(reader.Read()){
                        Dictionary<string,int> dict = new Dictionary<string,int>();
                        dict.Add("xuehao",(int)reader["xuehao"]);
                        list.Add(dict);
                    }
                    con.Close();
                    return list;
                }

            // GET api/values/5
            [HttpGet("{id}")]
                public ActionResult<string> Get(int id)
                {
                    return "valueasdjas";
                }

            // POST api/values
            [HttpPost]
                public void Post([FromBody] string value)
                {
                }

            // PUT api/values/5
            [HttpPut("{id}")]
                public void Put(int id, [FromBody] string value)
                {
                }

            // DELETE api/values/5
            [HttpDelete("{id}")]
                public void Delete(int id)
                {
                }
        }
}

返回结果如下：

 由于泛型对数据格式要求严格 ，所以下面有Hashtable集合添加不同类型的数据

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;

namespace webapi.Controllers
{
    [Route("api/[controller]")]
        [ApiController]
            public class ValuesController : ControllerBase
        {
            // GET api/values
            [HttpGet]
                public List<Hashtable> Get(){
                    string str= "Data Source=.;Initial Catalog=mlh;Integrated Security=True";
                    SqlConnection con = new SqlConnection(str);
                    con.Open();
                    string query ="select * from student";
                    SqlCommand cmd = new SqlCommand(query,con);
                    SqlDataReader reader =cmd.ExecuteReader();
                    List<Hashtable> list = new List<Hashtable>();
                    while(reader.Read()){
                        Hashtable dict = new Hashtable();
                        dict.Add("xuehao",reader["xuehao"]);
                        dict.Add("name",reader["name"]);
                        list.Add(dict);
                    }
                    con.Close();
                    return list;
                }

            // GET api/values/5
            [HttpGet("{id}")]
                public ActionResult<string> Get(int id)
                {
                    return "valueasdjas";
                }

            // POST api/values
            [HttpPost]
                public void Post([FromBody] string value)
                {
                }

            // PUT api/values/5
            [HttpPut("{id}")]
                public void Put(int id, [FromBody] string value)
                {
                }

            // DELETE api/values/5
            [HttpDelete("{id}")]
                public void Delete(int id)
                {
                }
        }
}

返回如下：

 

DataSet ||SqlDataAdapter 方法

 

using System;
using System.Data;
using System.Data.SqlClient;
namespace App{
    class MyClass{
        public static void Main(string[] args){
            string str= "Data Source=.;Initial Catalog=mlh;Integrated Security=True";
            using(SqlConnection con = new SqlConnection(str)){
                con.Open();
                string query = "select * from student";
                SqlCommand cmd = new SqlCommand(query,con);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds, "table");
                //return ds.Tables["table"];//返回的是一个DataTable
                //Console.WriteLine(ds.Tables["table"]);
                DataTable table = ds.Tables["table"];
                Console.WriteLine(table.Rows.Count);
                for(int i=0;i<table.Rows.Count;i++){
                    Console.WriteLine(table.Rows[i]["name"]);
                }
                con.Close();
            }
        }
    }
}