Insert data into SQL Server database from an Excel file

Question

I'm trying to insert data to database using excel file. This code works fine for me. But I used this with windows form application. How can I change this code to WCF? I need to open Excel file using Windows Forms application and then pass the value to WCF service to insert data into the database. How can I do this?

private void button1_Click(object sender, EventArgs e)
{
    OpenFileDialog opn = new OpenFileDialog();
    opn.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

    if (opn.ShowDialog() == DialogResult.Cancel)
        return;

    try { 
        FileStream strm = new FileStream(opn.FileName, FileMode.Open);
        IExcelDataReader excldr = ExcelReaderFactory.CreateOpenXmlReader(strm);

        DataSet rslt = excldr.AsDataSet();

        DataClasses1DataContext conn = new DataClasses1DataContext();

        foreach (DataTable table in rslt.Tables)
        {
            foreach (DataRow dr in table.Rows)
            {
                tblExcel addTbl = new tblExcel()
                                  {
                                    SID = Convert.ToString(dr[0]),
                                    Name = Convert.ToString(dr[1]),
                                    Address = Convert.ToString(dr[2])
                                  };
                conn.tblExcels.InsertOnSubmit(addTbl);
            }
        }

        conn.SubmitChanges();

        excldr.Close();
        strm.Close();

        MessageBox.Show("successfully");
    }
    catch (IOException x)
    {
        MessageBox.Show(x.Message);
    }
}

Answer 1

Here are 2 options for you to consider.

private void button3_Click(object sender, EventArgs e)
{
    System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls;Extended Properties=Excel 8.0;");

    ExcelConnection.Open();

    string expr = "SELECT * FROM [Sheet1$]";
    OleDbCommand objCmdSelect = new OleDbCommand(expr, ExcelConnection);
    OleDbDataReader objDR = null;
    SqlConnection SQLconn = new SqlConnection();
    string ConnString = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
    SQLconn.ConnectionString = ConnString;
    SQLconn.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLconn))
    {

        bulkCopy.DestinationTableName = "tblTest";

        try
        {
            objDR = objCmdSelect.ExecuteReader();
            bulkCopy.WriteToServer(objDR);
            ExcelConnection.Close();

            //objDR.Close()
            SQLconn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

}

ALSO

private void button4_Click(object sender, EventArgs e)
{
    BindGrid();
}

protected void BindGrid()
{
    string path = "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls";
    string jet = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
    OleDbConnection conn = new OleDbConnection(jet);
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
    DataTable dt = new DataTable();
    da.Fill(dt);

    dataGridView1.DataSource = dt;
    BulkUpload();
}

    protected void BulkUpload()
    {
        DataTable dt = (DataTable)dataGridView1.DataSource;
        string connection = "Data Source=excel-pc;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";

        using (var conn = new SqlConnection(connection))
        {
            List<string> errors = new List<string>();
            try{

                conn.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.ColumnMappings.Add(0, "Fname");
                    bulkCopy.ColumnMappings.Add(1, "Lname");
                    bulkCopy.ColumnMappings.Add(2, "Age");

                    bulkCopy.BatchSize = 10000;
                    bulkCopy.DestinationTableName = "Import_List";
                    bulkCopy.WriteToServer(dt.CreateDataReader());
                }
            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
            }
        }
    }

Remember, you need these at the top.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;

Answer 2

Here is how you can create a WCF service.

Assumption made: Let’s say you have a list of tblExcel objects that you want to send to a WCF service from you WinForm client application shown in your question.

Step 1: Create a new Class Library project and name it ExcelDataService In this project create a new folder called “DataContracts” and under this folder create a new class with the following definition:

[DataContract]
public class ExcelData
{
    [DataMember]
    public string Sid { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public string Address { get; set; } 
}

Note: tblExcel is renamed as ExcelData, the definition of the class is the same you posted in original question.

Step 2: Create another folder named “ServiceContracts”under ExcelDataService project and create a new interface with the following definition

[ServiceContract]
public interface IExcelDataService
{
    [OperationContract]
    bool SaveData(List<ExceData> data);
}

Step 3: Next create another folder and name it “Services” and create a new class with the following definition

public class ExcelDataService : IExcelDataService
{
    public bool SaveData(List<ExceData> data)
    {

        // Showing the code how to save into SQL is beyond this question.
        // In the data object you have the list of excel data objects that you can save into the sql server
        // you can use Enterprise Library Data block or ADO.Net to save this data into the SQL Server.

    }
}

Step 4a: Now in the visual studio solution add a new project and name it ExcelDataServiceConsoleHostManager, set the type of project as Console Application. In the Main method write the following code:

using (ServiceHost host = new ServiceHost(typeof(ExcelDataService)))
{
     PrintEndpoints(host.Description);
     host.Open();
     Console.WriteLine("Service(s) are up and running... Press Enter key to exit!");
     Console.ReadLine();
}

Step 4b: Add another static method with the following definition:

 static void PrintEndpoints(ServiceDescription desc)
        {
            Console.WriteLine(desc.Name);
            foreach (ServiceEndpoint nextEndpoint in desc.Endpoints)
            {
                Console.WriteLine();
                Console.WriteLine(nextEndpoint.Address);
            }
        }

Step 5: In the App.config file of this project add the following configuration:

<system.serviceModel>
    <services>
      <service name="ExcelDataService.Services.ExcelDataService">
        <endpoint address="net.tcp://localhost:8887/ExcelDataService/"
                   binding="netTcpBinding"
                   contract="ExcelDataService. ServiceContracts.IExcelDataService"
                  ></endpoint>
      </service>
    </services>
  </system.serviceModel>

Make sure all the references are added into the project. Once build is successful hit F5 key to start the Excel Data Service Console Host manager.

Next step is modifying your client application:

Step 6: Add a reference of “ExcelDataService.dll” in your client application. Create a new class with the following definition:

public class ExcelDataServiceClient : ClientBase<IExcelDataService>
{
    public bool SaveData(List<ExcelData> excelData)
    {
        base.Channel.SaveData(excelData);
    }
}

Step 7: Add app.config file into your client (if already not added) and paste the following configuration

  <system.serviceModel>
    <client>
      <endpoint address="net.tcp://localhost:8887/ExcelDataService/"
                 binding="netTcpBinding"
                 contract="ExcelDataService. ServiceContracts.IExcelDataService"></endpoint>
    </client>
  </system.serviceModel>

Save all files and resolve any references (WCF uses System.ServiceModel.dll).

Step 8: Next create a new instance of ExcelDataServiceClient class and call its instance method SaveData.

I would wrap the call from the client into a try-catch block to catch any exception.

Edit 2: To send a file to WCF service we have

Request class that client will use to send the file...

 [DataContract]
 public class UploadFileRequest
 {
       public string FileName { get; set; }
       public string Path { get; set; }
       public byte[] FileContents { get; set; }
 }

and the response class that service will send back:

[DataContract]
public class UploadFileResponse
{
     public string Message { get; set; }
}

add another method to the IExcelDataService interface:

 [OperationContract]
 UploadFileResponse UploadFile(UploadFileRequest request);

and its implementation in the ExcelDataService class:

public UploadFileResponse UploadFile(UploadFileRequest request)
{
// In the request object you have the file as byte array that can be used here.             
}

On the client side add this method in the ExcelDataServiceClient class

public string UploadFile(byte[] fileContent, string fileName = "", string filePath = "")
{
    UploadFileRequest request = new UploadFileRequest()
    {
         FileContents = fileContent, FileName = fileName, Path = filePath
    };

    UploadFileResponse response =  base.Channel.UploadFile(request);

    return response.Message;
}

Next use the instance of this client class to call UploadFile method and pass in the parameters.

Hope this helps!