[C#] Excel to Datagridview to SQL (LINQ)

[Hikarim]

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:

 
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:

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";

[Hikarim]

i decided i will use datagridview and send that to sql after i've ported the data from excel to the datagridview

 private void btnTest_Click_1(object sender, EventArgs e)
        {
foreach (DataGridViewRow row in dataGWseznam.Rows)
            {
                string constring = @"I have a source in my project but modded for forum post";
                using (SqlConnection conn = new SqlConnection(constring))
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@DATE, @TIME, @TITLE)", conn))
                    {
                        cmd.Parameters.AddWithValue("@DATE", row.Cells["date"].Value);
                        cmd.Parameters.AddWithValue("@TIME", row.Cells["time"].Value);
                        cmd.Parameters.AddWithValue("@TITLE", row.Cells["title"].Value);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                }
            }
            MessageBox.Show("Records inserted.");
}

but i get a exception at conn.Open();

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

[Devsome]

Originally Posted by Hikarim

i decided i will use datagridview and send that to sql after i've ported the data from excel to the datagridview

 private void btnTest_Click_1(object sender, EventArgs e)
        {
foreach (DataGridViewRow row in dataGWseznam.Rows)
            {
                string constring = @"I have a source in my project but modded for forum post";
                using (SqlConnection conn = new SqlConnection(constring))
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@DATE, @TIME, @TITLE)", conn))
                    {
                        cmd.Parameters.AddWithValue("@DATE", row.Cells["date"].Value);
                        cmd.Parameters.AddWithValue("@TIME", row.Cells["time"].Value);
                        cmd.Parameters.AddWithValue("@TITLE", row.Cells["title"].Value);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                }
            }
            MessageBox.Show("Records inserted.");
}

but i get a exception at conn.Open();

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Try this

private void btnTest_Click_1(object sender, EventArgs e) {
	foreach (DataGridViewRow row in dataGWseznam.Rows) {
		string constring = @"yeah yeah your connect line";
		
		using (SqlConnection conn = new SqlConnection(constring))
		using (SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@DATE, @TIME, @TITLE)", conn)) {
			cmd.Parameters.AddWithValue("@DATE", row.Cells["date"].Value);
			cmd.Parameters.AddWithValue("@TIME", row.Cells["time"].Value);
			cmd.Parameters.AddWithValue("@TITLE", row.Cells["title"].Value);
			conn.Open();
			cmd.ExecuteNonQuery();
			conn.Close();
		}
	}
	MessageBox.Show("Records inserted.");
}

when this not work, check if in row.Cells are the right values

[Hikarim]

Originally Posted by Devsome

Try this

private void btnTest_Click_1(object sender, EventArgs e) {
	foreach (DataGridViewRow row in dataGWseznam.Rows) {
		string constring = @"yeah yeah your connect line";
		
		using (SqlConnection conn = new SqlConnection(constring))
		using (SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 VALUES(@DATE, @TIME, @TITLE)", conn)) {
			cmd.Parameters.AddWithValue("@DATE", row.Cells["date"].Value);
			cmd.Parameters.AddWithValue("@TIME", row.Cells["time"].Value);
			cmd.Parameters.AddWithValue("@TITLE", row.Cells["title"].Value);
			conn.Open();
			cmd.ExecuteNonQuery();
			conn.Close();
		}
	}
	MessageBox.Show("Records inserted.");
}

when this not work, check if in row.Cells are the right values

Okay, i found the problem, i had a double slash in my connection string,.. so now thats working, but i get a primary key violation (that it cant be null) how can i fix that? Because if i add something like this:
cmd.Parameters.AddWithValue("@id", 1012);
i get te key duplication error...

[Devsome]

Then use the auto_increment function, so you don't need to fill the ID.

The PRIMARY KEY constraint uniquely identifies each record in a database table.

[Hikarim]

Aaam, sorry for being a noob, but how should i do this? -can u give me a code example please?

[Devsome]

We need to take a look at your Table design.
It should look like

When your ID is auto_increment, you don't need:

cmd.Parameters.AddWithValue("@id", 1012);

or you are using a update query and the ID is for the where clause

[Hikarim]

The table in the database is like this:
ID int PRIMARYKEY
DATE varchar(50) notnull
TIME varchar(50) notnull
TITLE varchar(50) notnull

as for the dataTable, i get it like this:

 
DataTable dtExcelRecords = new DataTable();

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);

            

            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);
            }
            dataGWseznam.DataSource = dtExcelRecords;
        }