How to upload Access(.mdb) table data to SQL Server with C#

I have a .mdb file inside which there are some tables. I want to upload one of the table's data into my SQL server's database table.

My SQL Server's database table name: Tab1

Access database table name: Customer

By the following C# code it is possible to transfer the data from access to sql server.

string str = "INSERT INTO Tab1(name,addr,NetworkID,UnitName) SELECT * FROM OPENROWSET";
str += "('Microsoft.Jet.OLEDB.4.0','E:\\Attn\\Upload_acss.mdb';'admin';'',Customer)";
SqlCommand sqlcom = new SqlCommand(str, connection);

   try
   {
        connection.Open();// connection is a SqlConnection type object which created before
        sqlcom.ExecuteNonQuery();
        connection.Close();
 

        Label1.ForeColor = System.Drawing.Color.Green;
        Label1.Text = "Inserted...";
   }
   catch (Exception ex) { Response.Write(ex.ToString()); }

Check to see if a table exists in SQL Server using C#

To do database related task we may sometime need to check if the sql table exists or not in the SQL Database. By the following code we can simply check this::

        string tblnm = 'your-table-name';
        string str = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + tblnm + "'";
        SqlCommand myCommand = new SqlCommand(str, connection);
        SqlDataReader myReader = null;
        int count = 0;

        try
        {
            connection.Open();
            myReader = myCommand.ExecuteReader();
            while (myReader.Read())           
                count++;
           
            myReader.Close();
            connection.Close();
        }
        catch (Exception ex) { Response.Write(ex.ToString()); }
        if (count == 0)
            Label1.Text = "Table doesn't exists";
        else
            Label1.Text = "Table exists";

// by returning true/false based on count u can do your job also

We can check the table exists or not in another way.......

Suppose we want to count the row number of a table if that table exists. we can do so by following  way::

string tblnm = 'your-table-name';
string sql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tblnm + "]'))";
sql += " SELECT count(*) FROM " + tblnm;

connection.Open();
SqlCommand sqlcom = new SqlCommand(sql, connection);
SqlDataReader dr = sqlcom.ExecuteReader();
int c = -1;
while (dr.Read())
{
    c = (int)dr.GetValue(0);
}
connection.Close();
 
if (c != -1)
     Label2.Text = c.ToString()+" rows";
else
     Label2.Text = "table not exists";

Upload Excel file to SQL Server from Local to Remote PC

Sometimes we may need to upload data files from user end to Server side. The following program will perform that task.

Here is the .aspx file code :

Here is the .cs file code :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class DataTransfer : System.Web.UI.Page
{

    protected HtmlInputFile myInputFile;
    SqlConnection connection = null;
    static string fileNameWithLocation = “”;

    protected void Page_Init(object sender, EventArgs e)
    {

       connection = new SqlConnection(“server=’SERVERNAME’;uid= ‘USERID’;” +    

        “pwd=’PASSWORD’;database=DATABASE″);
    }

    protected void Page_Load(object sender, EventArgs e)

    {
    }

    protected void upButton_Click(object sender, EventArgs e)
    {
        HttpPostedFile myFile = FileUpload1.PostedFile;
        if (myFile.FileName != “” && myFile.ContentLength > 0)
        {
           String ServerFileName = Path.GetFileName(myFile.FileName);
           fileNameWithLocation = “C:\\” + ServerFileName;
           myFile.SaveAs(fileNameWithLocation);

           SendDataToTable( );
           DeleteXLSFile( );
        }
        else
        {
           lblTransferMsg.ForeColor = System.Drawing.Color.Red;
           lblTransferMsg.Text = “Select a File !!!”;
        }
    }

    private void DeleteXLSFile( )
    {
       try
       {
          FileInfo theFile = new FileInfo(“C:\\a.xls”);
          if (theFile.Exists)
            File.Delete(“C:\\a.xls”);
          else
             throw new FileNotFoundException();
       }
       catch (FileNotFoundException fnfe)
       {
          Response.Write(fnfe.Message);
       }
       catch (Exception ex)
       {
           Response.Write(ex.Message);
       }
  }

  private void SendDataToTable( )
  {
     DeleteDataFromTable( );
     String strSQL = “”;
     strSQL = “Insert into ReportTable Select * FROM OPENROWSET”;
     strSQL += “(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=” + fileNameWithLocation + “;”;
     strSQL += “HDR = YES ‘,’SELECT * FROM [a$]‘)”;

    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );

       lblTransferMsg.ForeColor = System.Drawing.Color.Green;
       lblTransferMsg.Text = “Transfer success”;
    }
    catch (Exception ex) { Response.Write(ex.ToString()); }

 }

 private void DeleteDataFromTable( )
  {
    String strSQL = “”;
    strSQL = “DELETE FROM ReportTable”;
    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );
   }
   catch (Exception ex) { Response.Write(ex.ToString( )); }
}

//before uploading u have to create the table