Import multiple rows from excel file to database using for loop in Windows Application

PRA
 
on Nov 09, 2020 04:14 AM
614 Views

Hi!

I want import list data from excel file, but inserted only first rows instead all rows. name must change others parameters are const. 

namespace FromExcelImport
{
    public partial class Form1 : Form
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["popul"].ConnectionString);
        private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        DataTable impdata = new DataTable();
        public Form1()
        {
            InitializeComponent();
        }

        private void openDlg_FileOk(object sender, CancelEventArgs e)
        {
            string filePath = openDlg.FileName;
            string extension = Path.GetExtension(filePath);
            string header = rbHeaderYes.Checked ? "YES" : "NO";
            string conStr, sheetName;

            conStr = string.Empty;
            switch (extension)
            {

                case ".xls": //Excel 97-03
                    conStr = string.Format(Excel03ConString, filePath, header);
                    break;

                case ".xlsx": //Excel 07
                    conStr = string.Format(Excel07ConString, filePath, header);
                    break;
            }

            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    con.Close();
                }
            }

            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = con.CreateCommand())
                {
                    using (OleDbDataAdapter d = new OleDbDataAdapter())
                    {
                        // DataTable dt = new DataTable();
                        cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                        con.Open();
                        d.SelectCommand = cmd;
                        d.Fill(impdata);
                        con.Close();
                    }
                }
                MessageBox.Show("Excel file selected!");
            }
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            openDlg.ShowDialog();
        }

        public void insertStud(string Id, string Name, string Fac, string Spec, int Course)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into Ptable(IdStd, Name, Faculty, Special, Course)values('" + Id + "', N'" + Name + "','" + Fac + "','" + Spec + "'," + Course + ")";
            conn.Open();
            try
            {
                cmd.ExecuteReader();
            }
            catch { };
            conn.Close();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (impdata.Rows.Count > 0)
            {
                for (int i = 0; i < impdata.Rows.Count; i++)
                {
                    long tickss = DateTime.Now.Ticks;
                    byte[] bytess = BitConverter.GetBytes(tickss + i);
                    string id = Convert.ToBase64String(bytess).Replace('+', '_').Replace('/', '-').TrimEnd('=');
                    insertStud(id, impdata.Rows[0][0].ToString(), "01", "2790101", 1);
                }
                MessageBox.Show("Data inserted!");
            }
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 09, 2020 04:40 AM
PRA says:
insertStud(id, impdata.Rows[0][0].ToString(), "01", "2790101", 1);

Replace with below code.

insertStud(id, impdata.Rows[i][0].ToString(), "01", "2790101", 1);