Question data from excel to sql database?

mheonyae

New member
Joined
Feb 3, 2016
Messages
1
Programming Experience
Beginner
WEll i have an excel file that i want to read and display in datagridview and i managed to finish that, now im asking for some help with the part to export the data to a SQL database...


can someone show me an easy example how to export that data to sql?


this is how i got it from excel to datagridview:
C#:
public void importExcel(string path, string sheetName) {
            var excel = new LinqToExcel.ExcelQueryFactory(path);
            excel.ReadOnly = true;
            var companies = from a in excel.Worksheet(sheetName) select a;
            var columnNames = excel.GetColumnNames(sheetName);




            DataTable dtExcelRecords = new DataTable();


            foreach (var columnName in columnNames)
            {
                dtExcelRecords.Columns.Add(columnName);
            }
            foreach (var row in companies)
            {
                DataRow dr = dtExcelRecords.NewRow();
                foreach (var columnName in columnNames)
                {
                    dr[columnName] = row[columnName];
                }
                dtExcelRecords.Rows.Add(dr);
            }
            dataGWlist.DataSource = dtExcelRecords;
}


i know i ahve to make the sql connection etc,
but the question is can i read throught the excel file directly and upload data or do i have to save it to an array or something and then manually insert each row/cell ?


right now i have something like this:
C#:
DataContext db = new DataContext(@""+connection);


 Monitoring monitor = new Monitoring();


            string[] arrayAddr = new string[dataGWseznam.Rows.Count];
            for (int i = 0; i < stCol; i++)
            {
                arrayAddr[i] = Convert.ToString(dataGWseznam.Columns[i].HeaderText);


            }




 int colcount=dataGWseznam.Columns.Count;
            int rowcount=dataGWseznam.Columns.Count;
            string[,] array2D = new string[rowcount, colcount];
            for (int x = 0; x < colcount; x++)
            {
                for (int i = 0; i < rowcount; i++)
                {
                    array2D[x, i] = Convert.ToString(dataGWseznam.Rows[x].Cells[i].Value);
                    db.ExecuteCommand("INSERT into Monitoring ("+arrayAddr[x]+") VALUES ('" + array2D[x, i] + "');");
                    //string msg = string.Format("{0} ", array2D[x, i]);
                    //MessageBox.Show(msg);
                
                
                }
            }




                //Save changes to Database.
                db.SubmitChanges();


            lblStatusSql.Text = "Finished";
 
Back
Top Bottom