如果要大量导入数据到数据库,如果每条数据都要和数据库建立-断开连接,那么将会非常费时。
建立数据库、表、类型、存储过程
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;
}
}