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

Recommended Answers

All 7 Replies

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

dynamically meaning u want to create at run time right?

of course u can

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

use somthing like this

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

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

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();
   }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.