csv file uploaded but cannot be imported into sql

[farehan]

Hi. I want to ask about this code. I'm trying to upload csv file and import into sql. but this code doesnt working.

protected void LinkButton1_Click(object sender, EventArgs e)
    {
        Response.Redirect("login.aspx");
    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        string filePath = FileUpload1.PostedFile.FileName;
        string filename = Path.GetFileName(filePath);
        string ext = Path.GetExtension(filename);
        string contenttype = String.Empty;

        switch (ext)
        {
            case ".xls":
                contenttype = "application/vnd.ms-excel";
                break;
            case ".xlsx":
                contenttype = "application/vnd.ms-excel";
                break;
            case ".csv":
                contenttype = "application/vnd.ms-excel";
                break;
        }
        if (contenttype != String.Empty)
        {

            Stream fs = FileUpload1.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

            //insert the file into database
            string strQuery = "insert into tblFiles(Name, ContentType, Data)" +
               " values (@Name, @ContentType, @Data)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-excel";
            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
            InsertUpdateData(cmd);
            //Response.Write("<script>alert('" + "Berjaya" + "');</script>");
            lblMessage.ForeColor = System.Drawing.Color.Yellow;
            lblMessage.Text = "File Uploaded Successfully";
        }
        else
        {
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = "File format not recognised." +
              " Upload Excel formats";
        }

        DataTable dt = new DataTable();
        string line = null;
        int i = 0;

        using (StreamReader sr = File.OpenText(Server.MapPath(".") + ".csv"))
        {
            while ((line = sr.ReadLine()) != null)
            {
                string[] data = line.Split(',');
                if (data.Length > 0)
                {
                    if (i == 0)
                    {
                        foreach (object item in data)
                        {
                            dt.Columns.Add(new DataColumn());
                        }
                        i++;
                    }
                    DataRow row = dt.NewRow();
                    row.ItemArray = data;
                    dt.Rows.Add(row);
                }
            }
            SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["sstemkhdrnConnectionString"]);
            con.Open();

            SqlBulkCopy s = new SqlBulkCopy(con);
            s.DestinationTableName = "Pelajar";
            s.WriteToServer(dt);
            s.Close();
            con.Close();
        }
    }

I've also try another code to import csv into sql after upload. with this code, I can upload the file but cannot import into sql.

protected void Button4_Click(object sender, EventArgs e)
    {
        string filePath = FileUpload1.PostedFile.FileName;
        string filename = Path.GetFileName(filePath);
        string ext = Path.GetExtension(filename);
        string contenttype = String.Empty;

        switch (ext)
        {
            case ".xls":
                contenttype = "application/vnd.ms-excel";
                break;
            case ".xlsx":
                contenttype = "application/vnd.ms-excel";
                break;
            case ".csv":
                contenttype = "application/vnd.ms-excel";
                break;
        }
        if (contenttype != String.Empty)
        {

            Stream fs = FileUpload1.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

            //insert the file into database
            string strQuery = "insert into tblFiles(Name, ContentType, Data)" +
               " values (@Name, @ContentType, @Data)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-excel";
            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
            InsertUpdateData(cmd);
            //Response.Write("<script>alert('" + "Berjaya" + "');</script>");
            lblMessage.ForeColor = System.Drawing.Color.Yellow;
            lblMessage.Text = "File Uploaded Successfully";
        }
        else
        {
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = "File format not recognised." +
              " Upload Excel formats";
        }
    protected void ReadRec()
    {
        StreamReader Sr = new StreamReader(Server.MapPath(".") + "sample.txt");
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        string s;

        // do your connection stuff here: 
        myConn = myDB.OpenDB();
        while (!Sr.EndOfStream)
        {
            s = Sr.ReadLine();
            cmd.CommandText = "INSERT INTO Pelajar (ukPelajar, namaPelajar, emelPelajar, programPelajar) VALUES(" + s.Split(';')[1] + ", " + s.Split(';')[2] + ", " + s.Split(';')[3] + ", " + s.Split(';')[4] + ")";
            cmd.ExecuteNonQuery();
        }
        Sr.Close();
        cmd.Connection.Close();
        myDB.CloseDB();
    }

Can somebody please correct the code? Thanks

[simongh2]

When you say import into sql, do you mean the contents of the file, or the actual file?

Both these examples appear to save the file to the DB just fine. To read the contents though is slightly trickier. If you're reading Excel files, you'll need to save the uploaded file to disk, then use ODBC to open it. Google how to do that. Delete the uploaded file when you're done to keep things tidy.

Alternatively, find a component that can read excel files. Most of these can load a stream, so you wouldn't need to save the upload to disk.

Simon