public void AddCommandParameter(EPField p, EnumParameterStyle style)
{
DbParameter pam = cmd.CreateParameter();
pam.ParameterName = p.GetParameterNameForCommand(style);
pam.DbType = ValueConvertor.OleDbTypeToDbType(p.OleDbType);
pam.Size = p.DataSize;
pam.Value = p.Value;
cmd.Parameters.Add(pam);
}
public string ReceiveData(DataTable tblSrc, bool bSilent)
{
string error = string.Empty;
try
{
if (TableName.Length > 0 && _fields != null && connect != null && tblSrc != null)
{
if (_fields.Count > 0 && tblSrc.Columns.Count >= _fields.Count)
{
if (tblSrc.Rows.Count > 0)
{
bool bNeedUpdate = false;
bool bNeedUpdateRun = false;
int i;
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
bNeedUpdate = true;
break;
}
}
EnumParameterStyle pStyle = connect.ConnectionObject.ParameterStyle;
string n1 = connect.ConnectionObject.NameDelimiterBegin;
string n2 = connect.ConnectionObject.NameDelimiterEnd;
string sInsert = StringUtility.FormatInvString(
"INSERT INTO {0}{1}{2} ({0}{3}{2}",
n1, TableName, n2, _fields[0].Name);
string sValues = _fields[0].GetParameterName(pStyle);
string sUpdate = StringUtility.FormatInvString(
"UPDATE {0}{1}{2} SET ",
n1, TableName, n2);
string sExist;
if (connect.ConnectionObject.IsJet || connect.ConnectionObject.IsMSSQL)
{
sExist = StringUtility.FormatInvString(
"SELECT TOP 1 1 FROM {0}{1}{2} WHERE ",
n1, TableName, n2);
}
else
{
sExist = StringUtility.FormatInvString(
"SELECT 1 FROM {0}{1}{2} WHERE ",
n1, TableName, n2);
}
string sWhere = "";
int k = 0, n = 0;
if (bNeedUpdate)
{
if (_fields[0].Indexed)
{
sWhere = StringUtility.FormatInvString(
"{0}{1}{2}={3}", n1, _fields[0].Name, n2, _fields[0].GetParameterName(pStyle));
}
else
{
sUpdate += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[0].Name, n2, _fields[0].GetParameterName(pStyle));
k++;
}
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
if (n == 0)
{
sExist += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
else
{
sExist += StringUtility.FormatInvString(" AND {0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
n++;
}
}
}
for (i = 1; i < _fields.Count; i++)
{
sInsert += StringUtility.FormatInvString(",{0}{1}{2}", n1, _fields[i].Name, n2);
sValues += "," + _fields[i].GetParameterName(pStyle);
if (bNeedUpdate)
{
if (_fields[i].Indexed)
{
if (sWhere.Length == 0)
{
sWhere = StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
else
{
sWhere += StringUtility.FormatInvString(" AND {0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
}
else
{
if (k == 0)
{
sUpdate += StringUtility.FormatInvString("{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
else
{
sUpdate += StringUtility.FormatInvString(",{0}{1}{2}={3}", n1, _fields[i].Name, n2, _fields[i].GetParameterName(pStyle));
}
k++;
}
}
}
if (bNeedUpdate && k > 0)
{
bNeedUpdateRun = true;
}
sInsert += ") VALUES (";
sInsert += sValues;
sInsert += ")";
//
connect.ConnectionObject.Open();
//
if (connect.ConnectionObject.IsMSSQL)
{
dbWrapper dbHelp = new dbWrapper();
dbHelp.CreateCommand(connect.ConnectionObject);
string sHelp = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT {0} ON", TableName);
dbHelp.SetCommandText(sHelp);
dbHelp.ExecuteNonQuery();
}
//
dbWrapper dbUpdate = null;
dbWrapper dbExists = null;
dbWrapper dbInsert = new dbWrapper();
dbInsert.CreateCommand(connect.ConnectionObject);
dbInsert.SetCommandText(sInsert);
//
object vExist;
bool bInsert;
//
for (i = 0; i < _fields.Count; i++)
{
dbInsert.AddCommandParameter(_fields[i], pStyle);
}
//
if (bNeedUpdate)
{
if (bNeedUpdateRun)
{
dbUpdate = new dbWrapper();
dbUpdate.CreateCommand(connect.ConnectionObject);
dbUpdate.SetCommandText(StringUtility.FormatInvString("{0} WHERE {1}", sUpdate, sWhere));
}
dbExists = new dbWrapper();
dbExists.CreateCommand(connect.ConnectionObject);
dbExists.SetCommandText(sExist);
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
dbExists.AddCommandParameter(_fields[i], pStyle);
}
else
{
if (bNeedUpdateRun)
{
dbUpdate.AddCommandParameter(_fields[i], pStyle);
}
}
}
if (bNeedUpdateRun)
{
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
dbUpdate.AddCommandParameter(_fields[i], pStyle);
}
}
}
}
for (int m = 0; m < tblSrc.Rows.Count; m++)
{
bInsert = true;
if (bNeedUpdate)
{
k = 0;
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
dbExists.SetParameterValue(k, tblSrc.Rows[m][i]);
k++;
}
}
vExist = dbExists.ExecuteScalar();
if (vExist != null && vExist != System.DBNull.Value)
{
bInsert = false;
}
if (!bInsert)
{
if (bNeedUpdateRun)
{
k = 0;
for (i = 0; i < _fields.Count; i++)
{
if (!_fields[i].Indexed)
{
if (dbUpdate.Parameters[k].DbType == System.Data.DbType.Binary)
{
object v = tblSrc.Rows[m][i];
if (v == null || v == System.DBNull.Value)
{
dbUpdate.Parameters[k].Value = System.DBNull.Value;
}
else
{
string s = v.ToString();
byte[] bs = StringUtility.StringToBytes(s);
dbUpdate.Parameters[k].Value = bs;
}
}
else
{
dbUpdate.Parameters[k].Value = tblSrc.Rows[m][i];
}
k++;
}
}
for (i = 0; i < _fields.Count; i++)
{
if (_fields[i].Indexed)
{
if (dbUpdate.Parameters[k].DbType == System.Data.DbType.Binary)
{
object v = tblSrc.Rows[m][i];
if (v == null || v == System.DBNull.Value)
{
dbUpdate.Parameters[k].Value = System.DBNull.Value;
}
else
{
string s = v.ToString();
byte[] bs = StringUtility.StringToBytes(s);
dbUpdate.Parameters[k].Value = bs;
}
}
else
{
dbUpdate.Parameters[k].Value = tblSrc.Rows[m][i];
}
k++;
}
}
dbUpdate.ExecuteNonQuery();
}
}
}
if (bInsert)
{
for (i = 0; i < _fields.Count; i++)
{
object v = tblSrc.Rows[m][i];
if (v == null || v == System.DBNull.Value)
{
dbInsert.Parameters[i].Value = System.DBNull.Value;
}
else
{
if (dbInsert.Parameters[i].DbType == System.Data.DbType.Binary)
{
string s = v.ToString();
byte[] bs = StringUtility.StringToBytes(s);
dbInsert.Parameters[i].Value = bs;
}
else
{
bool b;
object v2 = VPLUtil.ConvertObject(v, EPField.ToSystemType(dbInsert.Parameters[i].DbType), out b);
dbInsert.Parameters[i].Value = v2;
}
}
}
dbInsert.ExecuteNonQuery();
}
}
if (connect.ConnectionObject.IsMSSQL)
{
dbWrapper dbHelp = new dbWrapper();
dbHelp.CreateCommand(connect.ConnectionObject);
string sHelp = string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT {0} OFF", TableName);
dbHelp.SetCommandText(sHelp);
dbHelp.ExecuteNonQuery();
}
connect.ConnectionObject.Close();
//
}
}
}
}
catch (Exception er)
{
error = ExceptionLimnorDatabase.FormExceptionText(er, "Error saving data to database");
TraceLogClass.TraceLog.ShowMessageBox = !bSilent;
TraceLogClass.TraceLog.Log(er);
}
finally
{
connect.ConnectionObject.Close();
}
return(error);
}
public override string Execute()
{
string sMsg = string.Empty;
SetError(sMsg);
ResetAffectedRows();
SQLNoneQuery sql = base.ExecutionCommand;
ConnectionItem connect = Connection;
DbTransaction transaction = Transaction;
if (sql != null && connect != null)
{
DbCommand cmd = connect.ConnectionObject.CreateCommand();
if (transaction != null)
{
cmd.Transaction = transaction;
}
bool bClosed = !connect.ConnectionObject.Opened;
if (bClosed)
{
connect.ConnectionObject.Open();
}
if (connect.ConnectionObject.Opened)
{
try
{
int i;
EnumParameterStyle pstyle = connect.ParameterStyle;
cmd.CommandText = sql.SQL;
if (sql.CommandType == enmNonQueryType.StoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
int nCount = ParameterCount;
for (i = 0; i < nCount; i++)
{
DbParameter pam = cmd.CreateParameter();
if (pstyle == EnumParameterStyle.LeadingQuestionMark)
{
if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase))
{
pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i].Substring(1));
}
else if (sql.Param_Name[i].StartsWith("?", StringComparison.OrdinalIgnoreCase))
{
pam.ParameterName = sql.Param_Name[i];
}
else
{
pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i]);
}
}
else
{
if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase))
{
pam.ParameterName = sql.Param_Name[i];
}
else
{
pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "@{0}", sql.Param_Name[i]);
}
}
pam.DbType = ValueConvertor.OleDbTypeToDbType(sql.Param_OleDbType[i]);
pam.Direction = this.Param_Directions[i];
pam.Size = EPField.FieldDataSize(sql.Param_OleDbType[i], this.Param_DataSize[i]);
pam.Value = ValueConvertor.ConvertByOleDbType(sql.Parameters[i].Value, sql.Param_OleDbType[i]);
cmd.Parameters.Add(pam);
}
cmd.Prepare();
DbDataReader dr = cmd.ExecuteReader();
_dataset = new DataSet("Results");
int n = 1;
while (true)
{
DataTable tbl = new DataTable(string.Format("Table{0}", n));
for (i = 0; i < dr.FieldCount; i++)
{
DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
tbl.Columns.Add(dc);
}
_dataset.Tables.Add(tbl);
while (dr.Read())
{
object[] vs = new object[dr.FieldCount];
for (int k = 0; k < dr.FieldCount; k++)
{
vs[k] = dr.GetValue(k);
}
tbl.Rows.Add(vs);
}
n++;
if (!dr.NextResult())
{
break;
}
}
dr.Close();
if (bClosed)
{
closeConnections();
}
for (i = 0; i < nCount; i++)
{
ParameterDirection pt = cmd.Parameters[i].Direction;
if (pt != ParameterDirection.Input)
{
sql.Parameters[i].Value = cmd.Parameters[i].Value;
}
}
FireExecuteFinish();
}
catch (Exception er)
{
if (transaction != null)
{
transaction.Rollback();
transaction.Dispose();
ResetTransaction();
throw;
}
else
{
sMsg = ExceptionLimnorDatabase.FormExceptionText(er);
}
}
finally
{
if (bClosed)
{
if (connect.ConnectionObject.State != ConnectionState.Closed)
{
connect.ConnectionObject.Close();
}
}
}
}
else
{
sMsg = "Database connection not set";
}
}
else
{
sMsg = "SQL statement not set";
}
if (!string.IsNullOrEmpty(sMsg))
{
SetError(sMsg);
}
return(sMsg);
}
public virtual string Execute()
{
string sMsg = string.Empty;
SetError(sMsg);
_affectedRows = 0;
if (_sql != null && connect != null)
{
DbCommand cmd = connect.ConnectionObject.CreateCommand();
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
bool bClosed = !connect.ConnectionObject.Opened;
if (bClosed)
{
connect.ConnectionObject.Open();
}
if (connect.ConnectionObject.Opened)
{
try
{
int i;
EnumParameterStyle pstyle = connect.ParameterStyle;
FieldList pmMap = new FieldList();
string s;
int nCount;
s = _sql.GetSQLStatement(pmMap, pstyle);
if (pstyle == EnumParameterStyle.QuestionMark)
{
nCount = pmMap.Count;
}
else
{
nCount = _sql.ParamCount;
}
connect.Log("Command:{0}, params:{1}", s, nCount);
cmd.CommandText = s;
cmd.CommandType = CommandType.Text;
for (i = 0; i < nCount; i++)
{
DbParameter pam = cmd.CreateParameter();
EPField f;
if (pstyle == EnumParameterStyle.QuestionMark)
{
f = pmMap[i];
pam.ParameterName = "@P" + i.ToString();
}
else
{
f = _sql.Parameters[i];
pam.ParameterName = ParameterList.GetParameterName(pstyle, _sql.Parameters[i].Name);
}
pam.DbType = ValueConvertor.OleDbTypeToDbType(f.OleDbType);
pam.Size = f.DataSize;
OleDbParameter op = pam as OleDbParameter;
if (op != null && f.OleDbType == OleDbType.DBTimeStamp)
{
if (f.Value != null && f.Value != DBNull.Value)
{
DateTime dt = (DateTime)(f.Value);
dt = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
f.SetValue(dt);
}
}
connect.Log("param{0}:{1},{2},{3}. Value type:{4}. Value:{5}", i, pam.ParameterName, pam.DbType, pam.Size, f.OleDbType, f.Value);
if (EPField.IsDatetime(f.OleDbType))
{
if (f.IsNullOrEmpty)
{
pam.Value = System.DBNull.Value;
}
else
{
object dt0 = ValueConvertor.ConvertByOleDbType(f.Value, f.OleDbType);
try
{
DateTime dt = (DateTime)dt0;
if (dt.Ticks == 0)
{
pam.Value = System.DBNull.Value;
}
else
{
pam.Value = dt;
}
}
catch
{
pam.Value = dt0;
}
}
}
else
{
pam.Value = ValueConvertor.ConvertByOleDbType(f.Value, f.OleDbType);
}
cmd.Parameters.Add(pam);
}
_affectedRows = cmd.ExecuteNonQuery();
connect.Log("Affected rows:{0}", _affectedRows);
if (bClosed)
{
closeConnections();
}
if (this.ExecuteFinish != null)
{
ExecuteFinish(this, EventArgs.Empty);
}
if (_webPage != null && !string.IsNullOrEmpty(_name))
{
_webPage.SetServerComponentName(_name);
}
}
catch (Exception er)
{
if (_transaction != null)
{
_transaction.Rollback();
_transaction.Dispose();
_transaction = null;
connect.Log("rollback with error {0}", er.Message);
throw;
}
else
{
sMsg = ExceptionLimnorDatabase.FormExceptionText(er);
}
}
finally
{
if (bClosed)
{
if (connect.ConnectionObject.State != ConnectionState.Closed)
{
connect.ConnectionObject.Close();
}
}
}
}
else
{
sMsg = "Database connection not set";
}
}
else
{
sMsg = "SQL statement not set";
}
if (!string.IsNullOrEmpty(sMsg))
{
SetError(sMsg);
if (connect != null)
{
connect.Log("Error executing EasyUpdator.Execute. {0}", sMsg);
}
}
return(sMsg);
}
/// <summary>
/// replace parameters with ? and collect non-unique parameter names in paramMap
/// </summary>
/// <param name="sSQL">the SQL string to parse, it can be any part of SQL</param>
/// <param name="paramList">for validation. parameter names must be in this list</param>
/// <param name="paramMap">this list is filled with the non-unique parameter names</param>
/// <returns>the SQL string with parameters replaced with ?</returns>
public static string MapParameters(string sSQL, EnumParameterStyle paramStyle, string sepStart, string sepEnd, FieldList paramList, FieldList paramMap, ParameterList psOld)
{
if (sSQL == null)
{
sSQL = "";
}
string S = sSQL;
if (S.Length > 0)
{
string name;
EPField fld;
int n = SQLParser.FindParameterIndex(S, 0, sepStart, sepEnd, out ParseError);
while (n >= 0)
{
int i = SQLParser.FindNameEnd(S, n);
name = S.Substring(n, i - n);
name = name.Trim();
if (paramStyle == EnumParameterStyle.QuestionMark)
{
S = string.Format(CultureInfo.InvariantCulture, "{0}? {1}", S.Substring(0, n), S.Substring(i));
n++;
}
else if (paramStyle == EnumParameterStyle.LeadingQuestionMark)
{
string pname = ParameterList.GetParameterName(paramStyle, name);
S = string.Format(CultureInfo.InvariantCulture, "{0}{1} {2}", S.Substring(0, n), pname, S.Substring(i));
n += pname.Length + 1;
}
else
{
n += name.Length + 1;
}
fld = paramList[name];
if (fld == null)
{
EPField f0 = null;
if (psOld != null && psOld.Count > 0)
{
f0 = psOld[name];
}
if (f0 == null)
{
fld = new EPField(paramList.Count, name);
}
else
{
fld = f0.Clone() as EPField;
fld.Index = paramList.Count;
}
paramList.Add(fld);
}
if (paramStyle == EnumParameterStyle.QuestionMark)
{
//non-unique name is added
paramMap.AddFieldDup(fld);
}
n = SQLParser.FindParameterIndex(S, n, sepStart, sepEnd, out ParseError);
}
}
return(S);
}
