将excel数据上传到sql [英] Upload excel data into sql

问题描述

我有以下主表（MasterTable）

ID StatusID GenderID TypeID createdBy

1 2 1 2 1



我有以下优秀数据



状态性别类型

指定男性个人

WIP女性公司





如何将日期上传到表格中（MasterTable）



子目录



1）状态大师



ID描述

1已分配

2 WIP



2）GenderMaster



ID Desc

1男

2女



）Type Master



ID Desc

1个人

2公司





请帮助..

Hi,

Iam having master table(MasterTable) below
ID StatusID GenderID TypeID createdBy
1 2 1 2 1

Iam having excel data as below

Status Gender Type
Assigned Male Individual
WIP Female Corporate


How do I upload the date into table(MasterTable)

Subtables

1)Status master

ID Desc
1 Assigned
2 WIP

2)GenderMaster

ID Desc
1 Male
2 Female

3)Type Master

ID Desc
1 Individual
2 Corporate


Please help..

推荐答案

将excel数据读取到数据表。

现在，是的你可以创建一个sp-

Read the excel data to a datatable.
Now, you can create a sp like-

CREATE PROC YourProc
(
   @Status VARCHAR(50),
   @Gender VARCHAR(50),
   @Type VARCHAR(50)
)
AS
BEGIN
   --Get Status ID
   DECLARE @StatusID INT 
   IF(EXISTS(SELECT ID FROM StatusMaster WHERE Desc=@Status))
   BEGIN
     --check if the status already exists then get the id
     SELECT @StatusID=ID FROM StatusMaster WHERE Desc=@Status
   END
   ELSE
   BEGIN
     --else insert a new entry and get the inserted id
     INSERT INTO StatusMaster VALUES(@Status)
     SELECT @StatusID = IDENT_CURRENT(‘StatusMaster’)
   END

   --Get Gender ID
   DECLARE @GenderID INT 
   IF(EXISTS(SELECT ID FROM GenderMaster WHERE Desc=@Gender))
   BEGIN
     SELECT @GenderID =ID FROM GenderMaster WHERE Desc=@Gender
   END
   ELSE
   BEGIN
     INSERT INTO GenderMaster VALUES(@Gender)
     SELECT @GenderID = IDENT_CURRENT(‘GenderMaster’)
   END

   --Get Type ID
   DECLARE @TypeID INT 
   IF(EXISTS(SELECT ID FROM TypeMaster WHERE Desc=@Type))
   BEGIN
     SELECT @TypeID =ID FROM TypeMaster WHERE Desc=@Type
   END
   ELSE
   BEGIN
     INSERT INTO TypeMaster VALUES(@Type)
     SELECT @TypeID = IDENT_CURRENT(‘TypeMaster’)
   END

   --insert data to master table
   INSERT INTO MasterTable (StatusID, GenderID, TypeID, createdBy)
   VALUES(@StatusID,@GenderID,@TypeID,'')
END

为数据表的每一行打电话。



希望，它会有所帮助：）

更新：

根据OP的要求，这里是ado.net的代码。

Call this for each row of your datatable.

Hope, it helps :)
Update:
As per OP's request here is the code for ado.net.

foreach(DataRow dr in yourDatatable.Rows)
{
    using (SqlConnection conn = new SqlConnection("Server=YourSQLServerInstanceName;DataBase=YourDBName;UserId=YourUserId;Password=YourPassword")) // replace with your connection string
    {
       conn.Open();
       SqlCommand cmd  = new SqlCommand("YourProc", conn);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(new SqlParameter("@Status", dr["Status"].ToString()));
       cmd.Parameters.Add(new SqlParameter("@Gender", dr["Gender"].ToString()));
       cmd.Parameters.Add(new SqlParameter("@Type", dr["Type"].ToString()));
       cmd.ExecuteNonQuery();
    }
}

这应该可以胜任。如果没有，请告诉我:)）

This should do your job. If not, please let me know :)

这篇关于将excel数据上传到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！