c#SQL Server数据存取操作

悠悠 2023-06-08 06:47 16阅读 0赞

### 取数据 ###

*  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);
            }
        }
    }

![在这里插入图片描述][20191017151351865.png]

## ![在这里插入图片描述][20191017151409193.png] ##

### 存数据 ###

//仅当使用了列列表并且 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();

![1571279876284.png][]  
![在这里插入图片描述][20191017151103656.png]

--------------------

### 删除数据 ###

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

![在这里插入图片描述][20191017151118237.png]

![在这里插入图片描述][20191017151133588.png]

--------------------

### 更新数据 ###

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

![在这里插入图片描述][20191017151150696.png]

![在这里插入图片描述][20191017151252466.png]

[20191017151351865.png]: /images/20230531/f39580b75989496594db8b60354b55eb.png
[20191017151409193.png]: /images/20230531/f862b7d19eb3485ba9ba1a470b018c69.png
[1571279876284.png]: /images/20230531/fd2f4303bfeb4c86a2f103aaf6ab5006.png
[20191017151103656.png]: /images/20230531/10865c40c6f2446185668b0af1b16b33.png
[20191017151118237.png]: /images/20230531/978ba8f66def48128ce015e56569c822.png
[20191017151133588.png]: /images/20230531/062381f7231d48278147ff5e4cceffa6.png
[20191017151150696.png]: /images/20230531/491052bb04244f4eadaa467c48b2b303.png
[20191017151252466.png]: /images/20230531/a2ccd71337f3448ab4eeaeb5a0a9699e.png