传DataTable作为参数调用存储过程，实现批量操作(数据插入和更新)

如果要大量导入数据到数据库，如果每条数据都要和数据库建立-断开连接，那么将会非常费时。

建立数据库、表、类型、存储过程

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('UserInfos') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
 CREATE TABLE dbo.UserInfos
 (
  UserID INT PRIMARY KEY IDENTITY(10001,1) NOT NULL,
  UserName VARCHAR(20) NOT NULL,
  UserPass VARCHAR(20) NOT NULL,
  Sex BIT NULL,
  Age SMALLINT NULL,
  CONSTRAINT PK_Users_UserID (UserID)
 )
END
IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'User' AND is_user_defined = 1)
BEGIN
 CREATE TYPE User AS TABLE
 (
  UserName VARCHAR(20) NOT NULL,
  UserPass VARCHAR(20) NOT NULL,
  Sex BIT NULL,
  Age SMALLINT NULL
 )
END
GO

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('UserBatchInCome_sp') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
    DROP PROCEDURE dbo.UserBatchInCome_sp
END
GO
CREATE PROCEDURE dbo.UserBatchInCome_sp
(
    @User UserTable READONLY
)
AS
	--匹配原表中的数据，数据库存在学生则修改
	update dbo.UserInfos set UserPass = '1111111',Sex = '男' where UserName in (SELECT UserName FROM @User )
	--数据库不存在则新增
	INSERT INTO dbo.UserInfos(UserName,UserPass,Sex,Age) SELECT UserName,UserPass,Sex,Age FROM @User where UserName not in (SELECT UserName FROM dbo.UserInfos)
	--将传入的datatable数据新增到数据库中
	INSERT INTO dbo.UserInfos(UserName,UserPass,Sex,Age) SELECT UserName,UserPass,Sex,Age FROM @User

 public void fnInsertSingleUser(DataTable userdt)
     {
         try
         {
             SqlConnection cn=new SqlConnection(CONN);
            SqlCommand cmd= cn.CreateCommand();
             cmd.CommandType= CommandType.StoredProcedure;
             cmd.CommandText=@"dbo.UserBatchInCome_sp";
             SqlParameter p= cmd.Parameters.AddWithValue("@User", userdt);

             DataSet ds =new DataSet();
            SqlDataAdapter da=new SqlDataAdapter(cmd);
            da.Fill(ds);
         }
         catch (Exception ex)
       {
           throw ex;
        }
     }