C#操作sqlServer数据库

港控/mmm° 2022-05-19 09:05 267阅读 0赞

工具：vs2015  、SqlServer

**数据库的连接及打开关闭**

1.  VS2015建立一个c\#的控制台应用程序，必须要包含两个using 指令：using System.Data;using System.Data.SqlClient;（SqlClient是因为我选用的数据库为sqlserver,若连接的数据库是mysql则用MySqlClient）
2.  建立一个连接数据库对象: SqlConnection conn = new SqlConnection();
3.  设置SqlConnection.ConnectionString属性：获取或设置用于打开 SQL Server 数据库的字符串。如conn.ConnectionString = "Server=服务器名;DataBase=数据库名;Trusted\_Connection=SSPI";（其中Trusted\_Connection为**false时**，连接中指定用户 ID 和密码。 当**true**，当前的 Windows 帐户凭据用于身份验证这是Windows身份验证登陆的；可识别的值为**true**， **false**， **yes**， **no**，和**sspi**（强烈建议），这等同于**true**。）这些字段的含义即对应值的要求都可以在官方文档上查到
4.  打开数据库：SqlConnection.Open();（使用SqlConnection.ConnectionString所指定的属性设置打开数据库连接）要查看是否打开可以打印SqlConnection.State属性，会返回一个 **ConnectionState** 枚举（Open表示打开，Closed表示关闭等具体可查看官方文档），用于指示 **SqlConnection** 的状态。
5.  关闭数据库：SqlConnection.Close();

**执行数据库语言增、删、改、查**

1.  定义一个String类型的sql语句：sql\_insert = "insert into Table\_1(uid,name) values(@UID,@NAME)";(要传的参数前面要加@字符，sql语句可以写增删改查，这里以插入数据为例)
2.  创建一个SqlCommand对象执行sql语句：SqlCommand cmd\_insert = new SqlCommand(sql\_insert, conn);（参数为索要执行的sql语句和已连接的数据库）
3.  为参数赋值：定义SqlParameter对象qlParameter para1 = new SqlParameter("@UID", "100");（参数为参数名和值）
4.  将赋值后的参数写入： cmd\_insert.Parameters.Add(para1);（SqlCommand.ExecuteNonQuery（）可以返回执行 Transact-SQL 语句受影响的行数。所以可以通过这个返回值查看sql语句是否执行）
5.  为参数赋值和写入参数可写到一起，用SqlCommand.Parameters.AddWithValue("@NAME","Bit");
6.  查询执行完后要接收查询到的数据,用SqlDataReader对象接收，如 SqlDataReader reader = sqlComm.ExecuteReader();
7.  读取数据，用SqlDataReader.Read()方法循环读取，Read()方法会让 SqlDataReader 前进到下一条记录（具体使用看下面代码）

**调用存储过程**

Sqlserver management创建存储过程

数据库->可编程性->存储过程->存储过程->写见下图->执行

USE [testDB]
    GO
    /****** Object:  StoredProcedure [dbo].[testInsert]    Script Date: 2018/7/25 10:06:44 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Batch submitted through debugger: SQLQuery17.sql|7|0|C:\Users\ibit\Documents\SQL Server Management Studio\SQLQuery17.sql
    ALTER PROCEDURE [dbo].[testInsert]
        @Name varchar(30),
    	@Uid int
    AS
        INSERT INTO  Table_1(name,uid)VALUES(@Name,@Uid)

1.  创建一个SqlCommand 的对象：SqlCommand cmd = new SqlCommand("存储过程名字",conn)）（赋第一个参数为存储过程名字，第二个为数据库连接对象）
2.  设置SqlCommand.CommandType的属性: cmd.CommandType = CommandType.StoredProcedure;(StoredProcedure表示执行的是存储过程)
3.  给存储过程要传的参数赋值：SqlParameter para = new SqlParameter("@NAME", SqlDbType.VarChar, 20);（第一个为参数名字记得加@，第二个为参数类型，第三个为长度，如果参数类型为int，那么第三个参数我们就不需要写）  para .Value = "Bit"（为参数赋值）
4.  将参数添加的SqlCommand对象： cmd.Parameters.Add(para);

**批量写入数据**

1.  在内存中创建一个数据表对象： DataTable dt = new DataTable();
2.  为数据表添加列DataTable.Columns.AddRange(new DataColumn\[\])（注意这里的列一定要跟你数据库里的列字段，类型一致，SqlBulkCopy不是根据表的ColumnName来匹配的，而是根据ColumnIndex匹配，也就是说你的表 字段必须跟数据库的表字段完全一致(Index的排序要跟数据表的一样)。就算你该字段不打算给他插入值，也要建个DataColumn。包括自增ID.不需要给他值就好了，否则会出错）
3.  创建SqlBulkCopy对象用于批量操作：SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);（参数为SqlConnection 类型，即要操作的数据库）
4.  设置SqlBulkCopy.DestinationTableName属性，即批量操作的表名，如bulkCopy.DestinationTableName = "Table\_1";
5.  设置SqlBulkCopy.BatchSize属性，即批量操作的行数，如bulkCopy.BatchSize = dt.Rows.Count;（也就是内存中创建的表的行数）
6.  循环创建要写入的数据：创建一个DataRow对象，并使其结构与内存中表结构相同，为这行记录赋值，赋值的时候DataRow对象实际上是个数组，0代表你之前创建的DataTable 对象的第一列，为每列附上值就可以了，最后将附好值的这一行加到DataTable对象上（具体写法见下面代码）
7.  将内存表中的数据写到服务器上的目标表中，用SqlBulkCopy.WriteToServer (DataTable)方法

附：（感觉方法还需要封装以下）

using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace A006
    {
        class Program
        {
    
            //连接数据库
            public void Conn(SqlConnection conn)
            {
                //获取或设置用于打开 SQL Server 数据库的字符串
                conn.ConnectionString = "Server=DESKTOP-8VD7LNG;DataBase=testDB;Trusted_Connection=SSPI";
    
                try
                {
                    //打开数据库
                    conn.Open();
                    //打印数据库连接状态
                    Console.WriteLine(conn.State);
    
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("数据库打开失败！");
                    Console.WriteLine(ex.Message);
                }
    
            }
    
            //insert
            public void Insert(SqlConnection conn)
            {
               
                String sql_insert = "insert into Table_1(uid,name) values(@UID,@NAME)";
    
                SqlCommand cmd_insert = new SqlCommand(sql_insert, conn);
                SqlParameter para1 = new SqlParameter("@UID", "106");
                cmd_insert.Parameters.Add(para1);
                SqlParameter para2 = new SqlParameter("@NAME", "Bit106");
                cmd_insert.Parameters.Add(para2);
    
                //对连接执行 Transact-SQL 语句并返回受影响的行数
                int res_1 = cmd_insert.ExecuteNonQuery();
                Console.WriteLine(res_1);
            }
    
            //update
            public void update(SqlConnection conn)
            { 
                string sql_update = "update Table_1 set name=@NAME where id=@ID;";
                SqlCommand cmd_update = new SqlCommand(sql_update, conn);
                cmd_update.Parameters.AddWithValue("@ID", "3");
                cmd_update.Parameters.AddWithValue("@NAME", "Bit100");
                int res_2 = cmd_update.ExecuteNonQuery();
                Console.WriteLine(res_2);
            }
    
            //delete
            public void delete(SqlConnection conn)
            {
                string sql_delete = "DELETE FROM Table_1 WHERE name=@NAME;";
                SqlCommand cmd_delete = new SqlCommand(sql_delete, conn);
                cmd_delete.Parameters.AddWithValue("@NAME", "Bit106");
                int res_3 = cmd_delete.ExecuteNonQuery();
                Console.WriteLine(res_3);
            }
    
            //select 
            public void select(SqlConnection conn)
            {
                //定义查询语句
                String sql = "select * from Table_1";
                SqlCommand sqlComm = new SqlCommand(sql, conn);
                //接收查询到的sql数据
                SqlDataReader reader = sqlComm.ExecuteReader();
                //读取数据
                while (reader.Read())
                {
                    //打印
                    Console.WriteLine(reader["uid"].ToString());
                    Console.WriteLine(reader["name"].ToString());
                }
                reader.Close();
            }
    
            //调用存储过程
            public void procedure(SqlConnection conn)
            {
                SqlCommand cmd = new SqlCommand("testInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;//告知执行存储过程
                //传参数
                cmd.Parameters.AddWithValue("@Uid", "106");
                cmd.Parameters.AddWithValue("@Name", "Bit106");
                int res = cmd.ExecuteNonQuery();
                Console.WriteLine(res);
    
            }
    
            //批量写入
            public void insertBulk(SqlConnection conn)
            {
                DataTable dt = GetTableSchema();
            
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                //获取服务器上目标表的名称
                bulkCopy.DestinationTableName = "Table_1";
                bulkCopy.BatchSize = dt.Rows.Count;
    
                for (int i = 0,j=107; i < 100; i++,j++)
                {
                    //创建与该表结构相同的行
                    DataRow dr = dt.NewRow();
                    dr[1] = j;
                    dr[2] = "Bit" + j;
                    dt.Rows.Add(dr);
                }
                if (dt != null && dt.Rows.Count != 0)
                {
                    try
                    {
                        //将内存中数据表的记录写到服务器上的目标表中
                        bulkCopy.WriteToServer(dt);
                    }
                    catch(Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                   
                }
                //Console.WriteLine(string.Format("插入{0}条记录", 100));
             }
            static DataTable GetTableSchema()
            {
                //内存中建一个数据表
                DataTable dt = new DataTable();
                //获取该数据表的列
                dt.Columns.AddRange(new DataColumn[] {
            new DataColumn("id",typeof(int)),
            new DataColumn("uid",typeof(int)),
            new DataColumn("name",typeof(string))});
                return dt;
            }
    
    
            static void Main(string[] args)
            {
                //建立一个连接数据库的对象
                SqlConnection conn = new SqlConnection();
                Program p = new Program();
                p.Conn(conn);
                //查
                // p.select(conn);
                //增
                //p.Insert(conn);
                //改
                //p.update(conn);
                //删
                //p.delete(conn);
    
                //调用存储过程
                //p.procedure(conn);
    
                //批量写入
                p.insertBulk(conn);
    
                conn.Close();
                conn.Dispose();
    
                Console.ReadLine();
            }
        }
    }