String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath("file.xlsx") + ";" +
"Extended Properties=Excel 12.0";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
SqlConnection sqlConnectionString = new SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;MultipleActiveResultSets=True;User ID=user;Password=password");
sqlConnectionString.Open();
SqlCommand cmd = new SqlCommand(sqlConnectionString);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO table1 VALUES (@ProjectID, @VersionID)";
cmd.Parameters.Add("@ProjectID", SqlDbType.NVarChar);
cmd.Parameters.Add("@VersionID", SqlDbType.NVarChar);
try
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = sConnectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [col1],[col2] FROM [Sheet1$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
int lineItems = 0;
while (dr.Read())
{
string strItemNum = dr["col1"].ToString().Trim();
string strPart = dr["col2"].ToString().Trim();
cmd.Parameters["@ProjectID"].Value = ProjectDropDown.SelectedValue;
cmd.Parameters["@VersionID"].Value = Label3.Text;
cmd.ExecuteNonQuery();
}
dr.Close();
}
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
sqlConnectionString.Close();
if (sqlConnectionString.State == ConnectionState.Closed)
sqlConnectionString.Open();
Open in new window