// <summary>
// Calls Clear() on the OleDbParameterCollection and performs
// a check to make sure it succeeds. If it does not succeed
// an exception will be thrown.
// </summary>
public static void SafeClear(this OleDbParameterCollection @this)
{
// Call the method.
@this.Clear();
// Do the sanity check
if (@this.Count != 0)
{
throw new Exception(@"OleDbParameterCollection.Clear() failed.");
}
}
// <Snippet1>
public void CreateParamCollection(OleDbCommand command)
{
OleDbParameterCollection paramCollection = command.Parameters;
paramCollection.Add("@CategoryName", OleDbType.Char);
paramCollection.Add("@Description", OleDbType.Char);
paramCollection.Add("@Picture", OleDbType.Binary);
string parameterNames = "";
for (int i = 0; i < paramCollection.Count; i++)
{
parameterNames += paramCollection[i].ToString() + "\n";
}
Console.WriteLine(parameterNames);
paramCollection.Clear();
}
protected void ApplyParameters()
{
if (_paramList == null)
{
return;
}
if (_dataAdpater == null)
{
return;
}
OleDbParameterCollection CommandParams = _dataAdpater.SelectCommand.Parameters;
CommandParams.Clear();
int i;
for (i = 0; i < _paramList.Count; i++)
{
CommandParams.Add(new OleDbParameter("@P" + i, _paramList[i]));
}
}
/// <summary>
/// 写数据到Excel。
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="filePath">Excel导出路径</param>
/// <param name="excelVersion">excel版本，为ExcelVersion类型</param>
/// <param name="pHDR">第一行是否标题，为HDRType类型</param>
public static void SetData(DataTable dtSource, string filePath, string excelVersion, string pHDR)
{
//数据源为空
if (dtSource == null)
{
throw new Exception("无数据可导");
}
//保存路径为空
if (string.IsNullOrEmpty(filePath))
{
throw new Exception("未设置Excel保存路径");
}
//删除文件
if (File.Exists(filePath))
{
File.Delete(filePath);
}
//链接字符串
string connectionString = string.Format(m_excelVersion[excelVersion], filePath, pHDR);
// 连接Excel
using (OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open();
//导入数据
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
//构建列 格式如：Name VarChar，CreateDate Date
string colList = CreateExcelColums(dtSource);
//构建插入SQL语句
//格式如 "INSERT INTO TABLE [tablename](col1,col2,col3) VALUES(@col1,@col2,@col3)";
StringBuilder sbColumNames = new StringBuilder();
StringBuilder sbColumValues = new StringBuilder();
foreach (DataColumn dc in dtSource.Columns)
{
sbColumNames.AppendFormat(",[{0}]", dc.ColumnName);
sbColumValues.AppendFormat(",@{0}", dc.ColumnName);
}
//去掉多余的逗号
sbColumNames.Remove(0, 1);
sbColumValues.Remove(0, 1);
//当数据量超过每页最大数据量时，自动分页
int totalRows = dtSource.Rows.Count;//总数据量
int pageIndex = 0;
//开始插入数据 do...while循环是为了处理分页逻辑
do
{
//计算此轮插入的数据量
int insertRows = m_maxSheelSize - 1;
//如果总数据量没有达到容量
if (totalRows < insertRows)
{
insertRows = totalRows;
}
string tableName = dtSource.TableName + pageIndex;
if (pageIndex == 0)
{
tableName = "Sheet1";
}
//创建表框架
StringBuilder sbCom = new StringBuilder();
sbCom.Append("CREATE TABLE [");
sbCom.Append(tableName);
sbCom.Append("](");
sbCom.Append(colList);
sbCom.Append(")");
command.CommandText = sbCom.ToString();
//try
//{
command.ExecuteNonQuery();
//}
//catch
//{
// //如果使用Create语句创建失败则直接创建Excel文件
// CreateExcelFile(filePath, excelVersion, command.CommandText);
//}
//插入数据
sbCom = new StringBuilder();
sbCom.AppendFormat("INSERT INTO [{0}]({1}) VALUES({2})",
tableName, sbColumNames.ToString(), sbColumValues.ToString());
int startIndex = pageIndex * (m_maxSheelSize - 1);
int endIndex = pageIndex * (m_maxSheelSize - 1) + insertRows;
for (int i = startIndex; i < endIndex; i++)
{
DataRow drData = dtSource.Rows[i];
OleDbParameterCollection dbParam = command.Parameters;
dbParam.Clear();
foreach (DataColumn dc in dtSource.Columns)
{
dbParam.Add(new OleDbParameter("@" + dc.ColumnName, GetOleDbTypeByDataColumn(dc)));
dbParam["@" + dc.ColumnName].Value = drData[dc.ColumnName];
}
command.CommandText = sbCom.ToString();
command.ExecuteNonQuery();
}
//计算剩余数据量
totalRows = totalRows - insertRows;
pageIndex++;
} while (totalRows > 0);
}//end of using OleDbCommand
}// end of using OleDbConnection
}
public void OleDbParameterCollection_MultipleScenarios_Success()
{
OleDbParameterCollection opc = command.Parameters;
Assert.True(opc.Count == 0);
Assert.False(opc.IsReadOnly);
Assert.False(opc.IsFixedSize);
Assert.False(opc.IsSynchronized);
Assert.Throws <IndexOutOfRangeException>(() => opc[0].ParameterName);
Assert.Throws <IndexOutOfRangeException>(() => opc["@p1"].ParameterName);
Assert.Throws <ArgumentNullException>(() => opc.Add(null));
opc.Add((object)new OleDbParameter());
opc.Add((object)new OleDbParameter());
Collections.IEnumerator enm = opc.GetEnumerator();
Assert.True(enm.MoveNext());
Assert.Equal("Parameter1", ((OleDbParameter)enm.Current).ParameterName);
Assert.True(enm.MoveNext());
Assert.Equal("Parameter2", ((OleDbParameter)enm.Current).ParameterName);
opc.Add(new OleDbParameter(null, null));
opc.Add(null, OleDbType.Integer, 0, null);
Assert.Equal("Parameter4", opc["Parameter4"].ParameterName);
opc.Add(new OleDbParameter("Parameter5", OleDbType.LongVarWChar, 20));
opc.Add(new OleDbParameter(null, OleDbType.WChar, 20, "a"));
opc.RemoveAt(opc[3].ParameterName);
Assert.Equal(-1, opc.IndexOf(null));
Assert.False(opc.Contains(null));
Assert.Throws <IndexOutOfRangeException>(() => opc.RemoveAt(null));
OleDbParameter p = opc[0];
Assert.Throws <ArgumentException>(() => opc.Add((object)p));
Assert.Throws <ArgumentException>(() => new OleDbCommand().Parameters.Add(p));
Assert.Throws <ArgumentNullException>(() => opc.Remove(null));
string pname = p.ParameterName;
p.ParameterName = pname;
p.ParameterName = pname.ToUpper();
p.ParameterName = pname.ToLower();
p.ParameterName = "@p1";
p.ParameterName = pname;
opc.Clear();
opc.Add(p);
opc.Clear();
opc.AddWithValue("@p1", null);
Assert.Equal(-1, opc.IndexOf(p.ParameterName));
opc[0] = p;
Assert.Equal(0, opc.IndexOf(p.ParameterName));
Assert.True(opc.Contains(p.ParameterName));
Assert.True(opc.Contains(opc[0]));
opc[0] = p;
opc[p.ParameterName] = new OleDbParameter(p.ParameterName, null);
opc[p.ParameterName] = (OleDbParameter)OleDbFactory.Instance.CreateParameter();
opc.RemoveAt(0);
new OleDbCommand().Parameters.Clear();
new OleDbCommand().Parameters.CopyTo(new object[0], 0);
Assert.False(new OleDbCommand().Parameters.GetEnumerator().MoveNext());
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Add(0));
Assert.Throws <ArgumentNullException>(() => new OleDbCommand().Parameters.AddRange(null));
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Insert(0, 0));
Assert.Throws <InvalidCastException>(() => new OleDbCommand().Parameters.Remove(0));
Assert.Throws <ArgumentException>(() => opc.Remove(new OleDbParameter()));
}
public void ClearParameters()
{
ParameterCollection.Clear();
}
public void ParameterCollection_Clear()
{
oleDbParameterCollection.Clear();
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="dgvPara"></param>
public static void ExportExcel(DataGridView dgvPara)
{
//int intColIndex = 1; //列序号
int intRowCount = dgvPara.RowCount; //行数
int intColCount = dgvPara.ColumnCount; //列数
//object[,] objData; //保存DataGridView中的数据
string strFileName = "";
try
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.DefaultExt = "";
dlg.Filter = "Excel|*.xls";
//dlg.InitialDirectory = Directory.GetCurrentDirectory();
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
strFileName = dlg.FileName;
if (strFileName.Trim() == " ")
{
return;
}
Environment.CurrentDirectory = System.Windows.Forms.Application.StartupPath;
if (System.IO.File.Exists(strFileName))
{
System.IO.File.Delete(strFileName);
}
int iColCountv = 0;
for (int i = 0; i < dgvPara.Columns.Count; i++)
{
if (dgvPara.Columns[i].Visible == true)
{
iColCountv++;
}
}
//判断：如果行数或者列数有问题，则不予导出
if (intRowCount == 0)
{
MessageBox.Show("列表中行数为零!", "提示");
return;
}
if (intColCount == 0)
{
MessageBox.Show("列表中列数为零!", "提示");
return;
}
if (intRowCount > 65536)
{
MessageBox.Show("数据记录不能超过65536条!", "提示");
return;
}
if (intColCount > 255)
{
MessageBox.Show("列数不能大于255!", "提示");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("输出失败，可能的原因是：" + ex.ToString(), "提示");
return;
}
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=Excel 8.0";
OleDbConnection objConn = new OleDbConnection(connString);
try
{
StringBuilder stringBuilder = new StringBuilder();
//先查看此Excel中是否有相关Table，如果有的话就删除，然后导入新的。
//生成创建表的脚本
stringBuilder.Append("CREATE TABLE ");
stringBuilder.Append("Sheet1" + " ( ");
foreach (DataGridViewColumn col in dgvPara.Columns)
{
if (col.Visible == false)
{
continue;
}
stringBuilder.Append(string.Format("[{0}] {1},", col.HeaderText, "VarChar"));
}
if (stringBuilder.Length > 0)
{
stringBuilder = stringBuilder.Remove(stringBuilder.Length - 1, 1);
}
stringBuilder.Append(")");
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
//插入新表
objCmd.CommandText = stringBuilder.ToString();
objConn.Open();
//插入新表
objCmd.ExecuteNonQuery();
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("INSERT INTO ");
stringBuilder.Append("Sheet1 ( ");
//先插入标头
foreach (DataGridViewColumn col in dgvPara.Columns)
{
if (col.Visible == false)
{
continue;
}
stringBuilder.Append("[" + col.HeaderText + "],");
}
if (stringBuilder.Length > 0)
{
stringBuilder = stringBuilder.Remove(stringBuilder.Length - 1, 1);
}
stringBuilder.Append(") values (");
foreach (DataGridViewColumn col in dgvPara.Columns)
{
if (col.Visible == false)
{
continue;
}
stringBuilder.Append("@" + col.HeaderText.Replace(" ", "").Replace("%", "") + ",");
}
if (stringBuilder.Length > 0)
{
stringBuilder = stringBuilder.Remove(stringBuilder.Length - 1, 1);
}
stringBuilder.Append(")");
//建立插入动作的Command
objCmd.CommandText = stringBuilder.ToString();
OleDbParameterCollection oleParam = objCmd.Parameters;
oleParam.Clear();
foreach (DataGridViewColumn col in dgvPara.Columns)
{
if (col.Visible == false)
{
continue;
}
//此处是本版本改进中最实用的地方
oleParam.Add(new OleDbParameter("@" + col.HeaderText, OleDbType.VarChar));
}
//遍历DataTable将数据插入新建的Excel文件中
int l = 0;
foreach (DataGridViewRow row in dgvPara.Rows)
{
l = 0;
for (int i = 0; i < dgvPara.Columns.Count; i++)
{
if (dgvPara.Columns[i].Visible == false)
{
continue;
}
oleParam[l].Value = Convert.ToString(row.Cells[i].Value);
l++;
}
objCmd.ExecuteNonQuery();
}
//objConn.Close();
//objConn.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("输出失败，可能的原因是：" + ex.ToString(), "提示");
return;
}
finally
{
objConn.Close();
objConn.Dispose();
}
MessageBox.Show("输出成功！", "提示");
}
public void DataTableToExcel(DataTable dt, string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strConn);
string strCom = "select * from [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = myConn;
DataSet newds = new DataSet();
myCommand.Fill(newds, "Table1");
StringBuilder sb = new StringBuilder();
OleDbParameterCollection param = objCmd.Parameters;
DataRow row;
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
if (newds.Tables["Table1"].Rows.Count >= 1)
{
sb.Append("update [Sheet1$] set ");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append(newds.Tables["Table1"].Columns[i].ColumnName + "=@" + newds.Tables["Table1"].Columns[i].ColumnName + ",");
}
else
{
sb.Append(newds.Tables["Table1"].Columns[i].ColumnName + "=@" + newds.Tables["Table1"].Columns[i].ColumnName + "");
}
}
//建立插入动作的Command
objCmd.CommandText = sb.ToString();
for (int i = 0; i < cols; i++)
{
param.Add(new OleDbParameter("@" + newds.Tables["Table1"].Columns[i].ColumnName, OleDbType.VarChar));
}
//遍历DataTable将数据插入新建的Excel文件中
for (int j = 0; j < newds.Tables["Table1"].Rows.Count; j++)
{
row = dt.Rows[j];
for (int i = 0; i < param.Count; i++)
{
param[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
}
sb.Remove(0, sb.Length);
param.Clear();
sb.Append("insert into ");
sb.Append("[Sheet1$]" + "(");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append(newds.Tables["Table1"].Columns[i].ColumnName + ",");
}
else
{
sb.Append(newds.Tables["Table1"].Columns[i].ColumnName + ") values(");
}
}
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append("@" + newds.Tables["Table1"].Columns[i].ColumnName + ",");
}
else
{
sb.Append("@" + newds.Tables["Table1"].Columns[i].ColumnName + ")");
}
}
//建立插入动作的Command
objCmd.CommandText = sb.ToString();
for (int i = 0; i < cols; i++)
{
param.Add(new OleDbParameter("@" + newds.Tables["Table1"].Columns[i].ColumnName, OleDbType.VarChar));
}
//遍历DataTable将数据插入新建的Excel文件中
for (int j = newds.Tables["Table1"].Rows.Count; j < dt.Rows.Count; j++)
{
row = dt.Rows[j];
for (int i = 0; i < param.Count; i++)
{
param[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
myConn.Close();
}
public string DataTableToExcel(DataSet dstables, string excelPath)
{
string connString = string.Format(ConnectionString, excelPath);
using (OleDbConnection objConn = new OleDbConnection(connString))
{
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objConn.Open();
foreach (DataTable dt in dstables.Tables)
{
if (dt == null)
{
return("DataTable不能为空");
}
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
StringBuilder sb;
if (rows == 0)
{
return("没有数据");
}
sb = new StringBuilder();
//生成创建表的脚本
sb.Append("CREATE TABLE ");
sb.Append(dt.TableName + " ( ");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append(string.Format("{0} varchar,", dt.Columns[i].ColumnName));
}
else
{
sb.Append(string.Format("{0} varchar)", dt.Columns[i].ColumnName));
}
}
try
{
objCmd.CommandText = sb.ToString();
objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
return("在Excel中创建表失败，错误信息：" + e.Message);
}
#region 生成插入数据脚本
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO ");
sb.Append(dt.TableName + "( ");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append(dt.Columns[i].ColumnName + ",");
}
else
{
sb.Append(dt.Columns[i].ColumnName + ") values (");
}
}
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
{
sb.Append("@" + dt.Columns[i].ColumnName + ",");
}
else
{
sb.Append("@" + dt.Columns[i].ColumnName + ")");
}
}
#endregion
//建立插入动作的Command
objCmd.CommandText = sb.ToString();
OleDbParameterCollection param = objCmd.Parameters;
//遍历DataTable将数据插入新建的Excel文件中
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < cols; i++)
{
param.Add(new OleDbParameter(dt.Columns[i].ColumnName, row[i].ToString()));
}
objCmd.ExecuteNonQuery();
param.Clear();
}
}
objConn.Close();
}
return("数据已成功导入Excel");
}
public void ExecuteNonQuery(String command, CommandType commandType, OleDbParameterCollection Parameters, OleDbTransaction trans)
{
if ((i_cn == null) || (i_cn.State == ConnectionState.Closed))
{
closeDB();
openDB();
}
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
OleDbCommand cmd = new OleDbCommand(command, i_cn);
cmd.CommandType = commandType;
cmd.CommandTimeout = i_timeout;
String debug = "";
try
{
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (OleDbParameter par in Parameters)
{
cmd.Parameters.Add(par.ParameterName, par.OleDbType, par.Size).Value = par.Value;
cmd.CommandText = cmd.CommandText.Replace(par.ParameterName, "?");
}
}
if (trans != null)
{
cmd.Transaction = trans;
}
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.SQL = " + cmd.CommandText);
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters " + cmd.Parameters.Count);
foreach (OleDbParameter p in cmd.Parameters)
{
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters[" + p.ParameterName + "] = " + p.Value);
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
cmd = null;
}
}
public void Insert2(String insertSQL, OleDbParameterCollection Parameters)
{
if ((i_cn == null) || (i_cn.State == ConnectionState.Closed))
{
closeDB();
openDB();
}
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
DebugLog(PluginLogType.Debug, "insertSQL.SQL = " + insertSQL);
OleDbCommand cmd = new OleDbCommand(insertSQL, i_cn);
OleDbDataReader dr = null;
try
{
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (OleDbParameter par in Parameters)
{
cmd.Parameters.Add(par.ParameterName, par.OleDbType, par.Size).Value = par.Value;
}
}
DebugLog(PluginLogType.Debug, "Insert2.Parameters " + cmd.Parameters.Count);
foreach (OleDbParameter p in cmd.Parameters)
{
DebugLog(PluginLogType.Debug, "Insert2.Parameters[" + p.ParameterName + "] = " + p.Value);
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
if (dr != null)
{
dr.Close();
}
cmd = null;
}
}
public void Insert(String insertSQL, OleDbParameterCollection Parameters)
{
OleDbConnection conn = new OleDbConnection(i_ConnectionString);
conn.Open();
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
OleDbCommand cmd = null;
OleDbDataReader dr = null;
try
{
cmd = new OleDbCommand(insertSQL, conn);
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (OleDbParameter par in Parameters)
{
cmd.Parameters.Add(par.ParameterName, par.OleDbType, par.Size).Value = par.Value;
}
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
if (dr != null)
{
dr.Close();
}
if (conn != null)
{
conn.Close();
}
if (conn != null)
{
conn.Dispose();
}
cmd = null;
}
}
public void LimparParametros()
{
parameterCollection.Clear();
}
public override void Clear()
{
_vfpParamCollection.Clear();
_oleDbParameterCollection.Clear();
}
public override void ProcessDeploy(String cacheId, PluginConnectorBaseDeployPackage package, Dictionary <String, Object> config, List <PluginConnectorBaseDeployPackageMapping> fieldMapping)
{
if (!CheckInputConfig(config, true, Log))
{
return;
}
DirectoryInfo importDir = null;;
try
{
importDir = new DirectoryInfo(Path.Combine(config["import_folder"].ToString(), "out"));
if (!importDir.Exists)
{
importDir.Create();
}
}
catch (Exception ex)
{
Log2(this, PluginLogType.Error, 0, 0, "Erro ao criar o diretório de importação (" + config["import_folder"].ToString() + "\\Out\\)", ex.Message);
return;
}
FileInfo f = new FileInfo(Path.Combine(importDir.FullName, "export" + DateTime.Now.ToString("yyyyMMddHHmmss-ffffff") + ".xlsx"));
if (!f.Directory.Exists)
{
f.Directory.Create();
}
String table = "Export " + DateTime.Now.ToString("HHmmss");
String lock_column = (config.ContainsKey("lock_column") ? config["lock_column"].ToString().ToLower() : null);
String locked_value = (config.ContainsKey("locked_value") ? config["locked_value"].ToString().ToLower() : null);
String unlocked_value = (config.ContainsKey("unlocked_value") ? config["unlocked_value"].ToString().ToLower() : null);
OdbcDB db = null;
StringBuilder processLog = new StringBuilder();
PluginLogType logType = PluginLogType.Information;
try
{
List <String> columnNames = new List <String>();
foreach (PluginConnectorBaseDeployPackageMapping m in fieldMapping)
{
if (!columnNames.Contains(m.dataName))
{
columnNames.Add(m.dataName);
}
}
if ((!String.IsNullOrEmpty(lock_column)) && (!columnNames.Contains(lock_column)))
{
columnNames.Add(lock_column);
}
db = new OdbcDB(f);
db.createAndOpenDB(table, columnNames);
List <String> prop = new List <String>();
String login = package.login;
//Resgata a restutura da tabela de destino
DataTable dtInsertSchema = db.GetSchema(table);
table = dtInsertSchema.TableName;
//Monta o where
OleDbParameterCollection par = OdbcDB.GetSqlParameterObject();
//Monta todos os campos que serão inseridos/atualizados
Dictionary <String, String> data = new Dictionary <String, String>();
foreach (DataColumn dc in dtInsertSchema.Columns)
{
if (!data.ContainsKey(dc.ColumnName.ToLower()))
{
data.Add(dc.ColumnName.ToLower(), null);
}
}
if (data.ContainsKey("locked"))
{
data["locked"] = (package.locked || package.temp_locked ? "1" : "0");
}
DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "lock_column = " + (String.IsNullOrEmpty(lock_column) ? "empty" : lock_column), "");
DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "locked_value = " + (String.IsNullOrEmpty(locked_value) ? "empty" : locked_value), "");
DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "unlocked_value = " + (String.IsNullOrEmpty(unlocked_value) ? "empty" : unlocked_value), "");
if ((lock_column != null) && (data.ContainsKey(lock_column)))
{
if ((package.locked || package.temp_locked) && (!String.IsNullOrEmpty(locked_value)))
{
data[lock_column] = locked_value;
}
else if ((!package.locked && !package.temp_locked) && (!String.IsNullOrEmpty(unlocked_value)))
{
data[lock_column] = unlocked_value;
}
else
{
data[lock_column] = (package.locked || package.temp_locked ? "1" : "0");
}
DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "data[lock_column] = " + data[lock_column], "");
}
String password_column = "";
foreach (PluginConnectorBaseDeployPackageMapping m in fieldMapping)
{
if (m.isPassword && data.ContainsKey(m.dataName))
{
data[m.dataName] = package.password;
password_column = m.dataName;
}
else if (m.isLogin && data.ContainsKey(m.dataName))
{
data[m.dataName] = package.login;
}
else if (m.isName && data.ContainsKey(m.dataName))
{
data[m.dataName] = package.fullName.fullName;
}
}
/*if (login_column != null && data.ContainsKey(login_column))
* data[login_column] = package.login;
*
* if (password_column != null && data.ContainsKey(password_column))
* data[password_column] = package.password;
*
* if (name_column != null && data.ContainsKey(name_column))
* data[name_column] = package.fullName.fullName;*/
foreach (PluginConnectorBasePackageData dt in package.importsPluginData)
{
if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null)
{
data[dt.dataName.ToLower()] = dt.dataValue;
//DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "1. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, "");
#if DEBUG
processLog.AppendLine("1. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue);
#endif
}
}
foreach (PluginConnectorBasePackageData dt in package.pluginData)
{
if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null)
{
data[dt.dataName.ToLower()] = dt.dataValue;
//DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "2. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, "");
#if DEBUG
processLog.AppendLine("2. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue);
#endif
}
}
foreach (PluginConnectorBasePackageData dt in package.properties)
{
if (data.ContainsKey(dt.dataName.ToLower()) && data[dt.dataName.ToLower()] == null)
{
data[dt.dataName.ToLower()] = dt.dataValue;
//DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "3. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue, "");
#if DEBUG
processLog.AppendLine("3. data[" + dt.dataName.ToLower() + "] = " + dt.dataValue);
#endif
}
}
foreach (String k in data.Keys)
{
//DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "4. data[" + k + "] = " + data[k], "");
#if DEBUG
processLog.AppendLine("4. data[" + k + "] = " + data[k]);
#endif
}
LogEvent dbExecLog = new LogEvent(delegate(Object sender, PluginLogType type, String text)
{
processLog.AppendLine(text);
});
//Não existe, cria
if ((data.ContainsKey(password_column)) && (package.password == ""))
{
package.password = IAM.Password.RandomPassword.Generate(16);
data[password_column] = package.password;
processLog.AppendLine("User not found in AD and IAM Password not found in properties list, creating a random password (" + package.password + ")");
}
//Limpa os parâmetros
par.Clear();
List <String> c1 = new List <string>();
List <String> c2 = new List <string>();
foreach (DataColumn dc in dtInsertSchema.Columns)
{
if (data.ContainsKey(dc.ColumnName.ToLower()))
{
if (!String.IsNullOrWhiteSpace(data[dc.ColumnName.ToLower()]))
{
if (dc.DataType.Equals(typeof(String)))
{
String txt = Corte((data[dc.ColumnName.ToLower()] != null ? data[dc.ColumnName.ToLower()] : ""), dc.MaxLength);
par.Add("@" + dc.ColumnName, GetDBType(dc.DataType), txt.Length).Value = txt;
}
else
{
par.Add("@" + dc.ColumnName, GetDBType(dc.DataType)).Value = data[dc.ColumnName.ToLower()];
}
c1.Add(dc.ColumnName);
c2.Add("@" + dc.ColumnName);
}
}
}
foreach (OleDbParameter p in par)
{
//DebugLog(this, PluginLogType.Debug, package.entityId, package.identityId, "6. par[" + p.ParameterName + "] = " + p.Value, "");
#if DEBUG
processLog.AppendLine("6. par[" + p.ParameterName + "] = " + p.Value);
#endif
}
String insert = "insert into [" + table + "] (" + String.Join(",", c1) + ") values (" + String.Join(",", c2) + ")";
db.OnLog += dbExecLog;
db.ExecuteNonQuery(insert, CommandType.Text, par);
db.OnLog -= dbExecLog;
NotityChangeUser(this, package.entityId);
processLog.AppendLine("User added");
/*
* //Executa as ações do RBAC
* if ((package.pluginAction != null) && (package.pluginAction.Count > 0))
* {
* foreach (PluginConnectorBaseDeployPackageAction act in package.pluginAction)
* try
* {
* switch (act.actionKey.ToLower())
* {
* case "procedure":
* String sql1 = act.actionValue.Replace("{login}", package.login).Replace("{full_name}", package.fullName.fullName);
* db.ExecuteNonQuery(sql1, CommandType.StoredProcedure, null);
* break;
*
* case "sql":
* String sql2 = act.actionValue.Replace("{login}", package.login).Replace("{full_name}", package.fullName.fullName);
* db.ExecuteNonQuery(sql2, CommandType.Text, null);
* break;
*
* default:
* processLog.AppendLine("Action not recognized: " + act.actionKey);
* Log2(this, PluginLogType.Warning, package.entityId, package.identityId, "Action not recognized: " + act.actionKey, "");
* break;
* }
* }
* catch (Exception ex)
* {
* processLog.AppendLine("Error on execute action (" + act.actionKey + "): " + ex.Message);
* Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on execute action (" + act.actionKey + "): " + ex.Message, "");
* }
* }*/
}
catch (Exception ex)
{
logType = PluginLogType.Error;
processLog.AppendLine("Error on process deploy: " + ex.Message);
String debugInfo = JSON.Serialize2(new { package = package, fieldMapping = fieldMapping });
if (package.password != "")
{
debugInfo = debugInfo.Replace(package.password, "Replaced for user security");
}
Log2(this, PluginLogType.Error, package.entityId, package.identityId, "Error on process deploy: " + ex.Message, debugInfo);
}
finally
{
if (db != null)
{
db.Dispose();
}
Log2(this, logType, package.entityId, package.identityId, "Deploy executed", processLog.ToString());
processLog.Clear();
processLog = null;
}
}
