/// <summary>
/// load from outside
/// </summary>
/// <param name="sql"></param>
public void LoadData(SQLNoneQuery sql)
{
if (editState != EnumEditState.NotLoaded)
{
throw new ExceptionLimnorDatabase("Calling LoadData at the state of {0}", editState);
}
dbConn = sql.Connection;
//load table names into lstTable
QueryParser._UseLowerCase = sql.Connection.ConnectionObject.LowerCaseSqlKeywords;
System.Data.DataTable tbl = sql.Connection.ConnectionObject.GetTables();
if (tbl != null)
{
for (int i = 0; i < tbl.Rows.Count; i++)
{
if (tbl.Rows[i]["Table_Name"] != null)
{
string s = tbl.Rows[i]["Table_Name"].ToString();
int n = lstTable.Items.Add(s);
if (string.Compare(s, sql.TableName, StringComparison.OrdinalIgnoreCase) == 0)
{
lstTable.SelectedIndex = n;
}
}
}
}
objRet = ((SQLNoneQuery)sql.Clone());
int k = (int)(objRet.CommandType);
if (k >= 0 && k < cbxType.Items.Count)
{
cbxType.SelectedIndex = k;
}
txtWhere.Text = objRet.Filter;
txtSQL.Text = objRet.SQL;
btQueryBuilder.Enabled = (objRet.CommandType != enmNonQueryType.Insert);
if (!string.IsNullOrEmpty(objRet.TableName))
{
for (int i = 0; i < lstTable.Items.Count; i++)
{
if (string.Compare(objRet.TableName, lstTable.Items[i].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
{
lstTable.SelectedIndex = i;
break;
}
}
}
else
{
if (lstTable.Items.Count > 0)
{
lstTable.SelectedIndex = 0;
}
}
editState = EnumEditState.Ready;
lstTable_SelectedIndexChanged(this, null);
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
if (context != null && context.Instance != null && provider != null)
{
IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
if (edSvc != null)
{
EasyUpdator eu = context.Instance as EasyUpdator;
if (eu == null)
{
object pointer;
IClassId ic = context.Instance as IClassId;
if (ic != null)
{
pointer = ic.ObjectInstance;
}
else
{
pointer = context.Instance;
}
if (pointer != null)
{
eu = VPLUtil.GetObject(pointer) as EasyUpdator;
}
}
if (eu != null)
{
SQLNoneQuery sq = value as SQLNoneQuery;
if (sq == null)
{
sq = new SQLNoneQuery();
}
sq.SetConnection(eu.DatabaseConnection);
bool bOK = eu.DatabaseConnection.ConnectionObject.IsConnectionReady;
if (!bOK)
{
DlgConnectionManager dlgC = new DlgConnectionManager();
dlgC.UseProjectScope = true;
if (edSvc.ShowDialog(dlgC) == System.Windows.Forms.DialogResult.OK)
{
eu.DatabaseConnection = dlgC.SelectedConnection;
sq.SetConnection(dlgC.SelectedConnection);
bOK = true;
}
}
if (bOK)
{
dlgPropSQLNonQuery dlg = new dlgPropSQLNonQuery();
dlg.LoadData(sq);
if (edSvc.ShowDialog(dlg) == System.Windows.Forms.DialogResult.OK)
{
value = dlg.objRet;
}
}
}
}
}
return(value);
}
bool parseSQLNoneQuery(string sSQL, SQLNoneQuery sql)
{
string sMsg;
if (string.IsNullOrEmpty(sql.TableName) && objRet != null)
{
sql.TableName = objRet.TableName;
}
sql.SetConnection(dbConn);
bool b = sql.parseSQLNoneQuery(this, sSQL, out sMsg);
txtInfo.Text = sMsg;
return(b);
}
/// <summary>
/// load SQLNoneQuery from cache
/// </summary>
/// <returns>true:loaded objRet from cache; false current objRet is already good</returns>
private bool loadFromCache()
{
enmNonQueryType qt = enmNonQueryType.Insert;
if (cbxType.SelectedIndex >= 0)
{
qt = (enmNonQueryType)(cbxType.SelectedIndex);
}
if (dt != null)
{
if (objRet != null)
{
if (objRet.CommandType == qt && string.Compare(objRet.TableName, dt.TableName, StringComparison.OrdinalIgnoreCase) == 0)
{
saveTocCache();
return(false);
}
}
SQLNoneQuery nq = null;
if (_commandCache != null)
{
Dictionary <string, SQLNoneQuery> c;
if (_commandCache.TryGetValue(qt, out c))
{
c.TryGetValue(dt.TableName, out nq);
}
}
if (nq == null)
{
nq = new SQLNoneQuery();
nq.TableName = dt.TableName;
nq.CommandType = qt;
objRet = nq;
saveTocCache();
}
else
{
objRet = nq;
}
}
return(true);
}
private bool checkSyntax()
{
if (bSQLChanged)
{
QueryParser._UseLowerCase = dbConn.LowerCaseSqlKeywords;
SQLNoneQuery sql = new SQLNoneQuery();
sql.SetConnection(dbConn);
ParameterList ps = objRet.ParseParams();
if (ps != null)
{
sql.SetParameters(ps);
}
if (parseSQLNoneQuery(txtSQL.Text, sql))
{
string tblName = objRet.TableName;
objRet = sql;
if (string.IsNullOrEmpty(objRet.TableName))
{
objRet.TableName = tblName;
}
enmNonQueryType qt = enmNonQueryType.Insert;
if (cbxType.SelectedIndex >= 0)
{
qt = (enmNonQueryType)(cbxType.SelectedIndex);
}
if (qt != objRet.CommandType)
{
cbxType.SelectedIndex = (int)qt;
}
for (int i = 0; i < lstTable.Items.Count; i++)
{
if (string.Compare(objRet.TableName, lstTable.Items[i].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
{
if (i != lstTable.SelectedIndex)
{
lstTable.SelectedIndex = i;
EnumEditState st = editState;
editState = EnumEditState.Ready;
lstTable_SelectedIndexChanged(this, EventArgs.Empty);
editState = st;
}
break;
}
}
setFieldTypes();
saveTocCache();
if (ds != null)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
ds.Tables[0].Rows[j][1] = false;
ds.Tables[0].Rows[j][2] = "";
}
for (int i = 0; i < objRet.FieldCount; i++)
{
EPField f = objRet.GetField(i);
if (f != null)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
string fn = ds.Tables[0].Rows[j][0].ToString();
if (string.Compare(f.Name, fn, StringComparison.OrdinalIgnoreCase) == 0)
{
ds.Tables[0].Rows[j][1] = true;
ds.Tables[0].Rows[j][2] = f.FieldText;
break;
}
}
}
}
}
return(true);
}
else
{
txtSQL.ForeColor = System.Drawing.Color.Red;
return(false);
}
}
return(true);
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
if (context != null && context.Instance != null && provider != null)
{
IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
if (edSvc != null)
{
DatabaseExecuter der = context.Instance as DatabaseExecuter;
if (der != null)
{
string sText = der.ExecutionCommand.SQL;
ExecParameter[] ps = new ExecParameter[der.ExecutionCommand.ParamCount];
for (int i = 0; i < der.ExecutionCommand.ParamCount; i++)
{
ExecParameter p = new ExecParameter();
p.Name = der.ExecutionCommand.Param_Name[i];
p.Type = der.ExecutionCommand.Param_OleDbType[i];
p.DataSize = der.Param_DataSize[i];
p.Direction = der.Param_Directions[i];
ps[i] = p;
}
DialogDbCommand dlg = new DialogDbCommand();
dlg.LoadData(sText, der.IsStoredProc, ps);
if (edSvc.ShowDialog(dlg) == System.Windows.Forms.DialogResult.OK)
{
SQLNoneQuery sq = new SQLNoneQuery();
sq.CommandType = enmNonQueryType.StoredProcedure;
sq.SQL = dlg.RetSQL;
ps = dlg.RetParameters;
string[] names = new string[dlg.RetParameters.Length];
OleDbType[] types = new OleDbType[dlg.RetParameters.Length];
int[] sizes = new int[dlg.RetParameters.Length];
ParameterDirection[] pds = new ParameterDirection[dlg.RetParameters.Length];
//
for (int i = 0; i < dlg.RetParameters.Length; i++)
{
names[i] = dlg.RetParameters[i].Name;
types[i] = dlg.RetParameters[i].Type;
sizes[i] = dlg.RetParameters[i].DataSize;
pds[i] = dlg.RetParameters[i].Direction;
}
//
sq.Param_Name = names;
sq.Param_OleDbType = types;
der.ExecutionCommand = sq;
DbParameterList pl = new DbParameterList(sq.Parameters);
der.SetParameterList(pl);
der.Param_DataSize = sizes;
der.Param_Directions = pds;
if (dlg.IsStoredProc)
{
sq.CommandType = enmNonQueryType.StoredProcedure;
}
else
{
sq.CommandType = enmNonQueryType.Script;
}
value = sq;
}
}
}
}
return(value);
}
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);
}
