/// <summary>
/// Возвращает интерфейс коммуникатора в зависимости от строки подключения
/// </summary>
private ICommunicator GetCommunicator()
{
if (_communicator == null)
{
// если коммуникатор еще не создан
ConnStrHelper connStrHelper = new ConnStrHelper(_connectionString);
// определяем протокол связи
switch (connStrHelper[1])
{
case "rs":
_communicator = new SerialCommunicator(connStrHelper[2],
Convert.ToInt32(connStrHelper[3]));
break;
case "tcp":
_communicator = new TcpCommunicator(connStrHelper[2],
Convert.ToInt32(connStrHelper[3]));
break;
default:
throw new InvalidOperationException(
string.Format("Протокол {0} не поддерживается.",
connStrHelper[1]));
}
return(_communicator);
}
else
{
// ссылка на ранее созданный интерфейс коммуникатора
return(_communicator);
}
}
public static void Import_Excel_File(string FilePath, string Extension, string TableName, string TablePk, string ProjIdField, string ProjIdValue)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConnStrHelper.Excel03ConString();
break;
case ".xlsx": //Excel 07
conStr = ConnStrHelper.Excel07ConString();
break;
default:
return; // not supported
}
conStr = String.Format(conStr, FilePath, "Yes");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
bool has_pj_id = string.IsNullOrEmpty(ProjIdField) ? false : true;
bool first_col = true;
string sql = "INSERT " +
(string.IsNullOrEmpty(TablePk) ? "" : string.Format(@"/*+ IGNORE_ROW_ON_DUPKEY_INDEX({0},{1}) */", TableName, TablePk)) +
" INTO " + TableName + " (";
// add proj_id field
if (has_pj_id)
{
sql += ProjIdField;
first_col = false;
}
foreach (DataColumn col in dt.Columns)
{
if (!first_col)
{
sql += ",";
}
else
{
first_col = false;
}
sql += col.ColumnName;
}
sql += ") VALUES (";
first_col = true;
// add proj_id field
if (has_pj_id)
{
sql += ":" + ProjIdField;
first_col = false;
}
foreach (DataColumn col in dt.Columns)
{
if (!first_col)
{
sql += ",";
}
else
{
first_col = false;
}
sql += ":" + col.ColumnName;
}
sql += ")";
using (OracleConnection conn = WebTools.GetIpmsConnection()) // will open connection too
{
OracleCommand cmd = new OracleCommand(sql, conn);
foreach (DataRow row in dt.Rows)
{
cmd.Parameters.Clear();
// add proj_id value
if (has_pj_id)
{
OracleParameter proj_par = new OracleParameter(ProjIdField, ProjIdValue);
cmd.Parameters.Add(proj_par);
}
foreach (DataColumn col in dt.Columns)
{
string col_val = row[col].ToString();
if (col.ColumnName.ToUpper().Contains("DATE") && !string.IsNullOrEmpty(col_val))
{
DateTime value;
if (DateTime.TryParse(col_val, out value))
{
col_val = value.ToString("dd-MMM-yyyy");
}
else
{
col_val = "";
}
}
OracleParameter parm = new OracleParameter(col.ColumnName, col_val);
cmd.Parameters.Add(parm);
}
cmd.ExecuteNonQuery();
}
cmd.Dispose();
}
//Bind Data to GridView
//GridView1.Caption = Path.GetFileName(FilePath);
//jointsGridView.DataSource = dt;
//jointsGridView.DataBind();
}
