using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data.Common;
using System.Web;
using System.Security;
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
//private string connstr;
private DataSet custDS;
private object parsedCustomerID;
private object txtCustomerID;
[SerializableAttribute]
[ComVisibleAttribute(true)]
public class ArgumentNullException : ArgumentException
{
private DataSet custDS;
public ArgumentNullException()
{
}
public ArgumentNullException(string message) : base(message)
{
}
}
public Form1()
{
InitializeComponent();
}
public object ConfigurationManager { get; private set; }
public DataSet Dataset { get; private set; }
private void btnScriviDb_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
xlApp
= new Excel.
Application();
xlWorkBook = xlApp.Workbooks.Open(@"c:\excel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
for (rCnt = 1; rCnt <= rw; rCnt++)
{
for (cCnt = 1; cCnt <= cl; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
string connection = "User ID=nomeutente;Password=password;";
connection += "database=BaseDati;server=server;";
SqlConnection objSqlConnection
= new SqlConnection
(connection
); // Apre la connessione
objSqlConnection.Open();
// Crea un DataSet (una cache) per memorizzare i dati da elaborare
DataSet objDataSet
= new DataSet
(str
); // Ora bisogna riempire la tabella, creando un
// comando SELECT
string cmd = string.Format("INSERT INTO rrPersone (nome,cognome,indirizzo, citta, provincia, cap, telefono) VALUES ('{0}','{1}','{2}', '{4}','{5}','{6}');", dataGridView1.Rows[rCnt].Cells[0].Value, dataGridView1.Rows[rCnt].Cells[1].Value, dataGridView1.Rows[rCnt].Cells[2].Value), dataGridView1.Rows[rCnt].Cells[3].Value, dataGridView1.Rows[rCnt].Cells[4].Value, dataGridView1.Rows[rCnt].Cells[5].Value), dataGridView1.Rows[rCnt].Cells[6].Value);
SqlCommand objOrderCommand
= new SqlCommand
(strOrders, objSqlConnection
); // e un DataAdapter nel quale mappare la tabella
SqlDataAdapter objOrdAdapter
= new SqlDataAdapter
(); SqlDataAdapter adapter
= new SqlDataAdapter
();
// connettere il SqlCommand e l'SqlDataAdapter
objOrdAdapter.SelectCommand = objOrderCommand;
// e riempire il DataSet precedentemente creato
objOrdAdapter.Fill(objDataSet);
// Crea due DataColumn... si useranno più tardi...
//DataColumn objDCParent;
// DataColumn objDCChild;
DataTable objOrdTable = objDataSet.Tables["rrPersone"];
DataTableMapping mapping =
adapter.TableMappings.Add("Table", "rrRaccolta");
mapping.ColumnMappings.Add("idRaccolta", "idNome");
mapping.ColumnMappings.Add("idCognome", "idCitta");
mapping.ColumnMappings.Add("idProvincia", "idCap");
mapping.ColumnMappings.Add("idTelefono");
DataTable table
= new DataTable
(); table.
Columns.
Add("idRaccolta",
typeof(int));
// Imposta le modifiche
string strUpDate = "UPDATE Orders SET ShipName = " + "'" + str + "'";
strUpDate += " WHERE OrderID = " + str;
string objRow = str;
// Crea il comando SQL appropriato per l'aggiornamento
objOrdAdapter.
UpdateCommand = new SqlCommand
(strUpDate, objSqlConnection
); // Esegue l'aggiornamento
//objRow.Insert(objDataSet, "rrRaccolta");
objOrdAdapter.Update(objDataSet, "rrRaccolta");
break;
// chiude la connessione al database
objSqlConnection.Close();
string strAnswer = Console.ReadLine();
}
};
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
}
}
