To read csv file and store in database

[Arjun_Sarankulu]

I was succesful in reading csv file and writing to txt file
But am facing problem while writing to database.
Can any one help me for the same
Thanks for your support

[sandeepparekh9]

provide some code

[Arjun_Sarankulu]

Below is the code working fine

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.SqlClient;
using System.Xml;
using System.Configuration;
using System.IO;

namespace Test1
{

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Uploadbutton_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
            filenametextBox.Text = openFileDialog1.FileName;

        }

        private void save_to_DBbutton_Click(object sender, EventArgs e)
        {

            if (openFileDialog1.FileName == "")
            {
                MessageBox.Show("File Not Selected");
            }
            SqlConnection connection_string = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);


            string filepath = openFileDialog1.FileName;

            StreamReader sr = new StreamReader(filepath);

            string line = sr.ReadLine();

            string[] value = line.Split(',');

            DataTable dt = new DataTable();

            DataRow row;

            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while (!sr.EndOfStream)
            {
                value = sr.ReadLine().Split(',');

                if (value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();

                    row.ItemArray = value;

                    dt.Rows.Add(row);
                }
            }
            SqlBulkCopy bc = new SqlBulkCopy(connection_string.ConnectionString, SqlBulkCopyOptions.TableLock);

            bc.DestinationTableName = System.Configuration.ConfigurationManager.AppSettings["table_name"];

            bc.BatchSize = dt.Rows.Count;

            connection_string.Open();

            bc.WriteToServer(dt);

            bc.Close();

            connection_string.Close();

            MessageBox.Show("Data dumped to database succesfully");

            this.Close();

        }
    }
}

I just want to ask can i create table dynamically
Thank sandeep for putting your valuable time

[sandeepparekh9]

dynamically meaning u want to create at run time right?

of course u can

[Arjun_Sarankulu]

Can anyone help me to create table dynamically in the above code

[sandeepparekh9]

use somthing like this

SqlCommand objCmd = new SqlCommand("Create Table TestTable(ID Int,Name Varchar(50))", objConn);

[Ashrafdar]

hello guys, this is ashraf i want to upload the csv files into the server can any one tell me the steps for this.

[Cameronsmith63]

1) read csv, create odbc connection to treat the csv as if it was a table itself. Store to datagrid if you want(for viewing purposes)

string tempPath = @"path";
      string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
      OdbcConnection conn = new OdbcConnection(strConn);
      OdbcDataAdapter da = new OdbcDataAdapter("Select [Terminal Number], Status from [" + file + "] where " + Status + "", conn);
      conn.Open();
      da.Fill(dt);
      dgvData.DataSource = dt;
      dgvData.Columns[1].DefaultCellStyle.Format = "T";
      conn.Close();

2) Do a bulk insert

private void Bulk1(object sender, EventArgs e)//Does a bulk copy into reply table, updates download table with today's date
   {
      string tempPath = @"path";
      string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
      OdbcConnection conn = new OdbcConnection(strConn);
      dt.Clear();
      OdbcDataAdapter da = new OdbcDataAdapter("Select [Terminal Number], Status from [" + file + "] where Status = 'VERIFIED'", conn);
      conn.Open();
      da.Fill(dt);
      dgvData.DataSource = dt;
      dgvData.Columns[1].DefaultCellStyle.Format = "T";
      conn.Close();
      conn.Dispose();
      thisConnection = new SqlConnection(@"Network Library = DBMSSOCN; Data Source="SERVER"; Initial Catalog=RemoteDownload; integrated security = true");
      thisConnection.Open();
      //String Release = SWRelease.Text; used with a odbc adapter and sql command
      String Release = SWRel.Text;
      SqlCommand thisCommand = thisConnection.CreateCommand();
      thisCommand.CommandText = "truncate table "TABLE"";
      thisReader = thisCommand.ExecuteReader();
      thisReader.Close();
      
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(thisConnection))
      {
         bulkCopy.DestinationTableName = ""TABLE"";
         
         try
            {  // Write from the source to the destination.
               bulkCopy.WriteToServer(dt);
            }
            
         catch (Exception ex)
            {
               Console.WriteLine(ex.Message);
               thisConnection.Close();
               Console.ReadLine();
               }
            
         finally
               {
               thisConnection.Close();
               }
            thisReader.Close();
            thisConnection.Close();
         }
         thisCommand.CommandText = "update "TABLE" set "COLUMN" = '" + currentYear + "-" + currentMonth + "-" + currentDay + " 00:00:00.000' where "COLUMN" is null and "COLUMN" is null and "COLUMN" in (Select "COLUMN" from TABLE where Status = 'VERIFIED')";
         thisConnection.Open();
         thisReader = thisCommand.ExecuteReader();
         thisReader.Close();
         thisConnection.Close();
   }