What I write inside loop to display similar item code on datagridview and insert different itemcode on database SQL server

Question

0.00/5 (No votes)

See more:

, +

Problem

SQL Server Database(2014) Items Table

ItemCode(pk) ItemName

001 mouse

002 keyboard

003 Headphone

On File Excel sheet 2010

ItemCode ItemName

001 mouse

002 keyboard

004 screen

005 Ram

Actually i need when import excel file insert different items code that not exist

on sql server database and Exist Items On Database and Found on Excel not insert but display on datagridview .

according to my case insert itemcodes 004,005 on table Items.

and show 001,002 in grid view as exist items .

my function as below

What I have tried:

public static void ImportFromExcelToDataBase()  
    {  
       Datatable dt = ShowExcelData();  
       DataTable dtItems = GetSqlItems();  
         
            for (int i = 0; i < dt.Rows.Count; i++)  
            {  
                //what i write here   
                // if itemcode exist on excel exist on sql server database  
                then display similar items exist on database and excel as 001,002 on datagridview  
                //else   
                 // do insert data  
                string Insert = "Insert Into Values (" + data + ")";  
                DataAccess.ExecuteNonQuery(Insert);  
            }  
                 
    }  
 public DataTable ShowExcelData()  
        {  
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);  
  
            OleDbConnection con = new OleDbConnection(connectionString);  
  
  
            con.Open();  
            DataTable dt = new DataTable();  
  
            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  
            string SheetName = dt.Rows[0]["TABLE_NAME"].ToString();  
  
  
            OleDbCommand com = new OleDbCommand();  
            com.Connection = con;  
             
            com.CommandText = @"SELECT  [ItemCode],[ItemsName],[ItemAddress] FROM  [" + SheetName + "] ";  
            OleDbDataAdapter oledbda = new OleDbDataAdapter();  
            oledbda.SelectCommand = com;  
            DataSet ds = new DataSet();  
            oledbda.Fill(ds);  
            dt = ds.Tables[0];  
            con.Close();  
            return dt;  
  
  
        }  
dt = ShowExcelData();  
  
 public DataTable GetSqlItems()  
        {  
            string GetItems = @"select ItemCode,ItemsName,ItemAddress from Items";  
  
  
           DataTable tbGetItems = DataAccess.ExecuteDataTable(GetItems );  
            return tbGetItems ;  
        }  
dtItems = GetSqlItems();

Posted 25-Sep-18 14:47pm

ahmed_sa

Updated 26-Sep-18 1:13am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Salman622 · Answer 1 · 2018-09-26T01:14:00

to achieved the desire functionality follow below steps

create a type in sql like this should be same as your excel file format :

SQL

create type type_ItemTable as table
(
	ItemCode nvarchar(50),
	ItemName nvarchar(100)
)

then create a procedure in sql as below

SQL

create Procedure sp_InsertBulkItemNotExist
(
	@Details type_ItemTable ReadOnly
)
as
begin

	--Insert All Item in Temporary table
	Select * into #Temp from ItemTable

	--Insert All item from Excel Data which ItemCode doesn't exists
	Insert into ItemTable(ItemCode,ItemName)
	Select ItemCode,ItemName from @Details where ItemCode Not in
	(Select ItemCode from ItemTable)

	--returns all Item that are exists in ItemTable
	Select * from @Details where ItemCode in (Select ItemCode from #Temp)
end

then in you showexceldata()

C#

public DataTable ShowExcelData()  
        {  
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);  
            OleDbConnection con = new OleDbConnection(connectionString);  
            con.Open();  
            DataTable dt = new DataTable();  
            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
            string SheetName = dt.Rows[0]["TABLE_NAME"].ToString();  
            OleDbCommand com = new OleDbCommand();  
            com.Connection = con;  
            com.CommandText = @"SELECT  [ItemCode],[ItemsName] FROM  [" + SheetName + "] ";  
            OleDbDataAdapter oledbda = new OleDbDataAdapter();  
            oledbda.SelectCommand = com;  
            DataTable dtReturn = new DataTable();  
            oledbda.Fill(dtReturn); 
			//dtReturn Columns should be same as of created type in sql
            con.Close();  
			SqlConnection con = new SqlConnection("YourConnectionstring");
			SqlCommand cmd = new SqlCommand("sp_InsertBulkItemNotExist", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Details", dtReturn);
            cmd.CommandTimeout = 0;
			SqlDataAdapter adap = new SqlDataAdapter(cmd, con);
			dt=new DataTable();
			adap.Fill(dt);
            return dt;  
        }

then at time of Importing Excel Sheet
just call ShowExcelData() as

C#

DataTable dt=ShowExcelData();

after execution of above state all new items will be inserted into ItemTable and already exist Item will return in dt