protected void ImportExcel()
{
var tableModel = Model.Table;
var logicModel = Model.Logic;
var tableName = String.Format("#{0}", tableModel.Name.Trim());
var dataSet = ExcelUtil.ConvertToDataSet(fuImport.FileBytes);
var dataTable = dataSet.Tables[tableName];
var queryGen = new QueryGenerator(DataContext, logicModel);
var connectionString = GetConnectionString();
using (var connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var transaction = connection.BeginTransaction())
{
var insertQuery = queryGen.InsertQuery();
using (var command = new SqlCommand(insertQuery, connection, transaction))
{
foreach (var columnModel in tableModel.Columns)
{
var columnName = queryGen.AllColumns[columnModel.Name.Trim()];
var paramName = queryGen.AllColumnsParams[columnName];
var dbType = queryGen.DbTypes[columnName];
var param = command.CreateParameter();
param.ParameterName = paramName;
param.SqlDbType = dbType;
param.IsNullable = true;
command.Parameters.Add(param);
}
foreach (DataRow dataRow in dataTable.Rows)
{
foreach (var columnModel in tableModel.Columns)
{
var dataColumnName = String.Format("#{0}", columnModel.Name.Trim());
var columnName = queryGen.AllColumns[columnModel.Name.Trim()];
var columnValue = dataRow[dataColumnName];
var paramName = queryGen.AllColumnsParams[columnName];
var sqlParam = command.Parameters[paramName];
sqlParam.Value = GetCorrectValue(columnValue, sqlParam.SqlDbType);
}
command.ExecuteNonQuery();
}
try
{
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
}
}