private DataTable GetTabelaExcel(string arquivoExcel) { arquivoExcel = txtArquivoExcel.Text; DataTable dt = new DataTable(); try { //pega a extensão do arquivo string Ext = Path.GetExtension(arquivoExcel); string connectionString = ""; //verifica a versão do Excel pela extensão if (Ext == ".xls") { //para o Excel 97-03 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + arquivoExcel + "; Extended Properties = 'Excel 8.0;HDR=YES'"; } else if (Ext == ".xlsx") { //para o Excel 07 e superior connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + arquivoExcel + "; Extended Properties = 'Excel 8.0;HDR=YES'"; } OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); cmd.Connection = conn; conn.Open(); DataTable dtSchema; dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string nomePlanilha = dtSchema.Rows[0]["TABLE_NAME"].ToString(); conn.Close(); //le todos os dados da planilha para o Data Table conn.Open(); cmd.CommandText = "SELECT `RA ALUNO`, `ALUNO NOME`,`NASCIMENTO`,`IDADE DO ALUNO`,`SEXO`,`GRAU_INSTRUCAO`,`RUA`,`NUMERO`,`COMPLEMENTO`,`BAIRRO`,`ESTADO`,`CIDADE`,`CEP`,`TELEFONE 1`,`IDENTIDADE`,`CPF`,`E-MAIL`,`CARTEIRA TRABALHO`,`NOME DO PAI`,`TELEFONE DO PAI`,`NOME MÃE`,`TELEFONE MÃE`,`NOME CURSO`,`CODIGO TURMA`,`STATUS`,`TELEFONE ALUNO 2` from [" + nomePlanilha + "] WHERE (`RA ALUNO` <> '')"; dataAdapter.SelectCommand = cmd; dataAdapter.Fill(dt); conn.Close(); string comando = "INSERT INTO Aluno (raAluno,nomeAluno, dataNas,idade,sexo,grau_instrucao,rua,numero,complemento,bairro,estado,cidade,cep,telefone1,identidade,cpf,email,carteira_de_trabalho,nomePai,telefonePai,nomeMae,telefoneMae,nomeCurso,codTurma,statusAluno,telefoneAluno2) VALUES "; for (int i = 0; i < dt.Rows.Count; i++) //Linha { comando += "("; for (int j = 0; j < 26; j++) //Coluna { string dado = dt.Rows[i][j].ToString(); //Valor comando += (j < 25) ? "'" + dado + "'," : "'" + dado + "'"; } comando += (i < (dt.Rows.Count - 1)) ? ")," : ");"; } ClassConexaoBd conexaoBd = new ClassConexaoBd(); conexaoBd.Conectar(); conexaoBd.ExecutarComandosSql(comando); Console.WriteLine(dataAdapter.ToString()); } catch (Exception ex) { throw ex; } return(dt); }
static void Main() { OleDbConnection connection = new OleDbConnection("Provider=sqloledb;Data Source=(local);Initial Catalog=Northwind;" + "Integrated Security=SSPI"); OleDbDataAdapter da = CreateCustomerAdapter(connection); Console.WriteLine(da.ToString()); connection.Dispose(); Console.ReadLine(); }
private bool readDB(string sqlcmd) { bool op_result = false; using (OleDbDataAdapter adapter = new OleDbDataAdapter(sqlcmd, conn)) { try { Console.WriteLine(adapter.ToString()); Console.WriteLine(adapter.GetFillParameters()); } catch (Exception e) { Console.WriteLine(e); } } return(op_result); }
public void run() { Exception exp = null; OleDbDataAdapter oleDBda = new OleDbDataAdapter(); try { BeginCase("ToString"); Compare(oleDBda.ToString(), typeof(OleDbDataAdapter).ToString()); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } }
public Data() { string fname = MyVariables.filePath; string sheet = MyVariables.sheetName; // COMMENTED OUT SO THAT CODE WILL COMPILE, WORKING ON THIS SECTION string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fname + ";Extended Properties='Excel 8.0;HDR=YES;';"; OleDbConnection con = new OleDbConnection(constr); OleDbCommand oconn = new OleDbCommand($"select * from [{sheet}$]", con); con.Open(); OleDbDataAdapter sda = new OleDbDataAdapter(oconn); DataTable data = new DataTable(); sda.Fill(data); dataGridView1.ItemsSource = sda.ToString(); }