c#SQL Server数据存取操作

取数据

• SqlConnection：连接数据库
• Open：打开数据库
• SqlCommand：进行数据库操作，增删改查
• SqlDataReader：提供一种从 SQL Server 数据库读取行的只进流的方式
• ExecuteReader：执行查找语句，返回的是一个结果集
• HasRows：判断数据库是否含有数据
• Read：读取数据
• Close：关闭数据库

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace SqlFirstTest
{
    class Program
    {
        public void Test(string sql, string connString)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();    //打开数据库  
                Console.WriteLine("打开数据库连接成功");
                
                /******以下增删改语句添加位置******/
                
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader dr;   //创建DataReader对象  
                dr = cmd.ExecuteReader();    //执行查询  
                if (dr.HasRows)  //判断数据库中是否含有数据
                {
                    Console.WriteLine("含有数据并读取：");
                    while (dr.Read())
                    {
                        Console.Write(dr[0].ToString() + ",");
                        Console.Write(dr[1].ToString() + ",");
                        Console.Write(dr[2].ToString() + ",");
                        Console.Write(dr[3].ToString() + ",");
                        Console.Write(dr[6].ToString() + ",");
                        Console.Write(dr[7].ToString() + "," + "\n");
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }             
            finally
            {
                conn.Close();   //关闭数据库
            }
            Console.ReadLine();
        }
        static void Main(string[] args)
        {
            Program p = new Program();
           string connString = "Data Source = DESKTOP-QKCTBUB; Initial Catalog = CaterIndustryData; User = sa; Password = root";
            string sql = "select * from UserInfo";
            p.Test(sql, connString);
        }
    }
}

存数据

//仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表'CaterIndustryData.dbo...
//以下set语句是解决方法
set IDENTITY_INSERT [CaterIndustryData].[dbo].[UserInfo] on
insert into ...
set IDENTITY_INSERT [CaterIndustryData].[dbo].[UserInfo] off;

string mysql = "set IDENTITY_INSERT [CaterIndustryData].[dbo].[UserInfo] on                      insert into [CaterIndustryData].[dbo].[UserInfo] ([UserID],[UserName],[LogUserName],[Pwd],[LastLogTime],[LastLogIP],[DelFalg],[SubTime]) values ('6','saa','saa','saa','','','0','')            set IDENTITY_INSERT [CaterIndustryData].[dbo].[UserInfo] off";
SqlCommand scmd = new SqlCommand(mysql, conn);
scmd.ExecuteNonQuery();

---

删除数据

string mysql = "delete from [CaterIndustryData].[dbo].[UserInfo] where UserID = '6'";
SqlCommand scmd = new SqlCommand(mysql, conn);
scmd.ExecuteNonQuery();

---

更新数据

string mysql = "update [CaterIndustryData].[dbo].[UserInfo] set SubTime = '2019-10-17' where UserID=5";
SqlCommand scmd = new SqlCommand(mysql, conn);
scmd.ExecuteNonQuery();