public ActionResult importData()
{
var fileName = string.Format("{0}dataimport.xls", Server.MapPath("/"));
Response.Write(fileName);
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var adapter = new OleDbDataAdapter("SELECT * FROM [Warehouse$]", connectionString);
DataTable dt = new DataTable();
adapter.Fill(dt);
sql.warehouses.DeleteAllOnSubmit(from w in sql.warehouses select w);
sql.SubmitChanges();
foreach (DataRow row in dt.Rows)
{
var warehouse = new warehouse();
warehouse.code = row["Warehouse"].ToString();
warehouse.description = row["Description"].ToString();
sql.warehouses.InsertOnSubmit(warehouse);
}
sql.SubmitChanges();
sql.terms.DeleteAllOnSubmit(from t in sql.terms select t);
sql.SubmitChanges();
try
{
adapter = new OleDbDataAdapter("SELECT * FROM [Terms Code$]", connectionString);
dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var terms = (from t in sql.terms where t.code == row["Terms"].ToString() select t);
if (terms.Count() == 0)
{
var term = new term();
term.code = row["Terms"].ToString();
term.description = row["Description"].ToString();
sql.terms.InsertOnSubmit(term);
sql.SubmitChanges();
}
}
}
catch (Exception ex)
{
Response.Write("TERMS = "+ ex.Message + "</br>");
}
try
{
sql.client_types.DeleteAllOnSubmit(from ct in sql.client_types select ct);
sql.SubmitChanges();
adapter = new OleDbDataAdapter("SELECT * FROM [Cutomer Type$]", connectionString);
dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var client_types = (from ct in sql.client_types where ct.type == Convert.ToInt16(row["Cust Type"]) select ct);
if (client_types.Count() == 0)
{
var client_type = new client_type();
client_type.type = Convert.ToInt16(row["Cust Type"]);
client_type.description = row["Description"].ToString();
sql.client_types.InsertOnSubmit(client_type);
sql.SubmitChanges();
}
}
}
catch (Exception ex)
{
Response.Write("CLIENT TYPES = " + ex.Message + "</br>");
}
try
{
sql.states.DeleteAllOnSubmit(from s in sql.states select s);
sql.SubmitChanges();
adapter = new OleDbDataAdapter("SELECT * FROM [State Code$]", connectionString);
dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var states = (from s in sql.states where s.code == Convert.ToInt16(row["code"]) select s);
if (states.Count() == 0)
{
var state = new state();
state.code = Convert.ToInt16(row["Code"]);
state.description = row["Description"].ToString();
sql.states.InsertOnSubmit(state);
sql.SubmitChanges();
}
}
}
catch (Exception ex)
{
Response.Write("CLIENT TYPES = " + ex.Message + "</br>");
}
return View();
}
partial void Updatestate(state instance);
partial void Deletestate(state instance);
partial void Insertstate(state instance);