public bool LoadData(Connection conn)
{
try
{
this.Text = this.Text + " - " + conn.ConnectionString;
qry = new EasyQuery();
Connection objCn = new Connection();
objCn.NameDelimiterStyle = conn.NameDelimiterStyle;
objCn.DatabaseType = conn.DatabaseType;
objCn.ConnectionString = conn.ConnectionString;
qry.DatabaseConnection = new ConnectionItem(objCn);
qry.Description = "Table editor";
//
schema.dbCon = objCn;
schema.LoadSchema();
loadTables();
btCancel.Enabled = false;
return(true);
}
catch (Exception er)
{
FormLog.NotifyException(true, er);
}
finally
{
}
return(false);
}
public bool LoadAccess(string sPath, string sDBPass, string sUser, string sUserPass) //,out string errStr)
{
try
{
sOriginalPath = sPath;
this.Text = this.Text + " - " + System.IO.Path.GetFileName(sPath);
sBackupPath = sPath + ".BACKUP";
System.IO.File.Copy(sOriginalPath, sBackupPath, true);
sConnectionString = ConnectionStringSelector.MakeAccessConnectionString(sOriginalPath, false, true, sDBPass, sUser, sUserPass);
ConnectionStringSelector.InsertValue(ref sConnectionString, "Password", sUserPass);
ConnectionStringSelector.InsertValue(ref sConnectionString, "Jet OLEDB:Database Password", sDBPass);
qry = new EasyQuery();
Connection objCn = new Connection();
objCn.DatabaseType = typeof(OleDbConnection);
objCn.ConnectionString = sConnectionString;
qry.DatabaseConnection = new ConnectionItem(objCn);
qry.Description = "Table editor";
//
objCn.SetCredential(sUser, sUserPass, sDBPass);
schema.dbCon = objCn;
schema.LoadSchema();
loadTables();
return(true);
}
catch (Exception er)
{
FormLog.NotifyException(true, er);
}
finally
{
}
return(false);
}
public bool LoadData(EasyQuery qry)
{
listBox1.Items.Clear();
if (qry != null)
{
FieldList fl = qry.Fields;
if (fl != null)
{
int n;
for (int i = 0; i < fl.Count; i++)
{
if (EPField.IsDatetime(fl[i].OleDbType))
{
n = listBox1.Items.Add(fl[i]);
if (fl[i].OleDbType == System.Data.OleDb.OleDbType.DBTimeStamp)
{
listBox1.SelectedIndex = n;
chkTS.Checked = true;
}
}
}
}
}
return(listBox1.Items.Count > 0);
}
public void LoadData(QueryParser q)
{
int i;
//qParser = q;
qry = (EasyQuery)q.query.Clone();
//parse group by
groupFields = new FieldsParser();
//=============================================
string s;
string sGroupBy = qry.GroupBy;
if (!string.IsNullOrEmpty(sGroupBy))
{
sGroupBy = sGroupBy.Trim();
if (sGroupBy.StartsWith("GROUP BY ", StringComparison.OrdinalIgnoreCase))
{
sGroupBy = sGroupBy.Substring(9);
sGroupBy = sGroupBy.Trim();
}
i = FieldsParser.FindStringIndex(sGroupBy, ",", 0, q.Sep1, q.Sep2);
while (sGroupBy.Length > 0)
{
if (i < 0)
{
s = sGroupBy;
sGroupBy = "";
}
else
{
s = sGroupBy.Substring(0, i);
sGroupBy = sGroupBy.Substring(i + 1);
sGroupBy = sGroupBy.Trim();
}
s = s.Trim();
groupFields.AddField(new QryField(s, q.Sep1, q.Sep2));
i = FieldsParser.FindStringIndex(sGroupBy, ",", 0, q.Sep1, q.Sep2);
}
}
//=============================================
ListViewItem item;
listView1.Items.Clear();
FieldList fl = qry.Fields;
for (i = 0; i < fl.Count; i++)
{
item = new ListViewItem(fl[i].FieldText);
if (groupFields.FindFieldByText(fl[i].FieldText) != null)
{
item.SubItems.Add("Group by");
}
else
{
item.SubItems.Add("");
}
listView1.Items.Add(item);
}
}
public static void LogMessage(bool showMessage, string message, params object[] values)
{
if (showMessage)
{
ShowMessage(message, values);
}
EasyQuery.LogMessage2(message, values);
}
public DatabaseQuery(IDatabaseAccess owner)
{
_owner = owner;
_query = owner.QueryDef;
if (_query != null)
{
_query.ForReadOnly = true;
}
}
private int nDeltaDragMove = 3; //in pixels
public dlgDatabase()
{
qry = new EasyQuery();
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
dlgDatabase_Resize(null, null);
}
public void LoadQuery(IDataQueryUser owner)
{
_owner = owner;
_easyQry = new EasyQuery();
_easyQry.ConnectionID = _owner.ConnectionID;
_easyQry.ForReadOnly = true;
_easyQry.SQL = new SQLStatement(_owner.SqlString);
_easyQry.Description = "Database lookup";
_easyQry.SqlChanged += new EventHandler(_easyQry_SqlChanged);
propertyGrid1.SelectedObject = _easyQry;
_easyQry.SetLoaded();
}
public void loadData()
{
const string TABLENAME = "Links";
//
WebDataEditorLookupDB wd = WebLookup;
//
ds.Tables.Clear();
ds.Tables.Add(TABLENAME);
//
ds.Tables[0].Columns.Add();
ds.Tables[0].Columns.Add();
//
ds.Tables[0].Columns[0].Caption = "Fields to be Updated";
ds.Tables[0].Columns[0].ColumnName = "Destination";
ds.Tables[0].Columns[0].DataType = typeof(string);
ds.Tables[0].Columns[0].MaxLength = 120;
ds.Tables[0].Columns[0].ReadOnly = true;
//
ds.Tables[0].Columns[1].Caption = "Source Fields";
ds.Tables[0].Columns[1].ColumnName = "Source";
ds.Tables[0].Columns[1].DataType = typeof(string);
ds.Tables[0].Columns[1].MaxLength = 120;
//
//
dataGridView1.DataSource = ds;
dataGridView1.DataMember = TABLENAME;
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AllowUserToDeleteRows = false;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
//
cbx2 = new System.Windows.Forms.ComboBox();
cbx2.Parent = dataGridView1;
cbx2.Visible = false;
cbx2.Left = 0;
cbx2.SelectedIndexChanged += new EventHandler(cbx2_SelectedIndexChanged);
//
_webQuery = new EasyQuery();
_webQuery.ConnectionID = wd.ConnectionID;
_webQuery.ForReadOnly = true;
_webQuery.SQL = new SQLStatement(wd.SqlString);
_webQuery.Description = "Database lookup";
_webQuery.SqlChanged += new EventHandler(_webQuery_SqlChanged);
propertyGrid1.SelectedObject = _webQuery;
_webQuery.SetLoaded();
//
loadWebDataFields();
}
private void _master_DataFilled(object sender, EventArgs e)
{
if (CurrentDataTable == null)
{
EasyQuery.LogMessage2("{0} - Master data filled. {0} does not exist. Execute Query.", TableName);
Query();
}
else
{
EasyQuery.LogMessage2("{0} - Master data filled. {0} exist. Execute OnBindDataSource.", TableName);
OnBindDataSource();
}
OnEmptyRecord(this, EventArgs.Empty);
}
public void LoadData(QueryParser qp)
{
qParser = qp;
oldQuery = (EasyQuery)qp.query.Clone();
loadOperators();
if (oldQuery.Parameters == null)
{
oldParameters = null;
}
else
{
oldParameters = (ParameterList)oldQuery.Parameters.Clone();
}
qParser.parameters = oldQuery.Parameters;
cbxTable.Items.Clear();
cbxValTable.Items.Clear();
int i;
DatabaseSchema schema = qParser.GetSchema();
for (i = 0; i < qParser.query.T.Count; i++)
{
qParser.query.T[i].LoadSchema(schema);
cbxTable.Items.Add(qParser.query.T[i]);
cbxValTable.Items.Add(qParser.query.T[i]);
}
if (cbxTable.Items.Count > 0)
{
cbxTable.SelectedIndex = 0;
cbxValTable.SelectedIndex = 0;
}
if (qParser.query.Where == null)
{
qParser.query.Where = "";
}
txtWhere.Text = qParser.query.Where;
txtWhere.ForeColor = System.Drawing.Color.Black;
enableButtons();
enableValueUI();
bChanged = false;
}
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)
{
IDataBinder dg = context.Instance as IDataBinder;
if (dg != null)
{
//field list editing
dlgPropFields dlg1 = dg.GetFieldsDialog();
if (dlg1 != null)
{
if (edSvc.ShowDialog(dlg1) == System.Windows.Forms.DialogResult.OK)
{
dg.SetFieldsAfterFieldsDialog(dlg1.fields);
}
}
}
else
{
EasyQuery qry = context.Instance as EasyQuery;
if (qry != null)
{
//field list editing
dlgPropFields dlg1 = qry.GetFieldsDialog();
if (dlg1 != null)
{
if (edSvc.ShowDialog(dlg1) == System.Windows.Forms.DialogResult.OK)
{
value = dlg1.fields;
}
}
}
}
}
}
return(value);
}
public void LoadData(EasyQuery qry)
{
query = (EasyQuery)qry.Clone();
fields = query.Fields;
ListViewItem v;
for (int i = 0; i < fields.Count; i++)
{
v = new ListViewItem(fields[i].Name);
v.SubItems.Add(EPField.TypeString(fields[i].OleDbType));
v.SubItems.Add(fields[i].IsIdentity.ToString());
v.SubItems.Add(fields[i].IsFile.ToString());
v.SubItems.Add(fields[i].ColumnWidth.ToString());
v.SubItems.Add(fields[i].Visible.ToString());
v.SubItems.Add(fields[i].ReadOnly.ToString());
listView1.Items.Add(v);
}
if (listView1.Items.Count > 0)
{
listView1.Items[0].Selected = true;
}
}
public bool LoadData(EasyQuery qry, string displayField)
{
try
{
bool bLoaded = false;
qry.ResetCanChangeDataSet(true);
qry.Query();
bLoaded = true;
ds = qry.DataStorage;
if (ds != null && ds.Tables.Count > 0 && bLoaded)
{
if (ds.Tables[0].Columns.Count > 0)
{
int fldIdx = 0;
this.DataSource = ds;
if (!string.IsNullOrEmpty(displayField))
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (string.Compare(displayField, ds.Tables[0].Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase) == 0)
{
fldIdx = i;
break;
}
}
}
this.DisplayMember = string.Format(System.Globalization.CultureInfo.InvariantCulture,
"{0}.{1}", ds.Tables[0].TableName, ds.Tables[0].Columns[fldIdx].ColumnName);
return(true);
}
}
}
catch (Exception er)
{
FormLog.NotifyException(true, er);
}
return(false);
}
public override object EditValue(System.ComponentModel.ITypeDescriptorContext context, IServiceProvider provider, object value)
{
if (context != null && context.Instance != null && provider != null)
{
IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
if (edSvc != null)
{
EasyQuery qry = value as EasyQuery;
if (qry == null)
{
qry = new EasyQuery();
}
bool bOK = qry.IsConnectionReady;
if (!bOK)
{
DlgConnectionManager dlgC = new DlgConnectionManager();
if (edSvc.ShowDialog(dlgC) == System.Windows.Forms.DialogResult.OK)
{
qry.DatabaseConnection = dlgC.SelectedConnection;
bOK = true;
}
}
if (bOK)
{
dlgQueryBuilder dlg = new dlgQueryBuilder();
QueryParser qp = new QueryParser();
qp.LoadData(qry);
dlg.LoadData(qp);
if (edSvc.ShowDialog(dlg) == System.Windows.Forms.DialogResult.OK)
{
qp.checkQueryreadOnly(qp.query);
value = qp.query;
}
}
}
}
return(value);
}
public DTSQuery()
{
_qry = new EasyQuery();
}
public void CopyFrom(EasyQuery query)
{
_qry.CopyFrom(query);
}
private void btQueryBuilder_Click(object sender, System.EventArgs e)
{
acceptByMouse();
EasyQuery qry = objRet.SourceQuery;
if (qry == null)
{
qry = new EasyQuery();
qry.DatabaseConnection = dbConn;
if (_tables == null)
{
_tables = new TableAliasList();
for (int i = 0; i < lstTable.Items.Count; i++)
{
TableAlias t = new TableAlias((string)(lstTable.Items[i]), string.Empty);
_tables.AddTable(t);
}
}
qry.T = _tables;
FieldList lst = objRet.GetFields();
if (lst != null)
{
FieldList fl = (FieldList)lst.Clone();
for (int i = 0; i < fl.Count; i++)
{
fl[i].FieldText = fl[i].Name;
if (lst[i].FromTableName != null)
{
if (lst[i].FromTableName.Length > 0)
{
if (!qry.T.IsTableIncluded(lst[i].FromTableName))
{
TableAlias t = new TableAlias(lst[i].FromTableName, "");
t.srcType = enumRecSource.Table;
qry.T.AddTable(t);
}
}
}
}
qry.Fields = fl;
}
string sl = objRet.Filter;
if (sl == null)
{
sl = "";
}
if (sl.Length > 0)
{
if (sl.StartsWith("FROM ", StringComparison.OrdinalIgnoreCase))
{
sl = sl.Substring(5);
sl = sl.Trim();
qry.From = sl;
}
else
{
qry.From = objRet.TableName;
if (sl.StartsWith("WHERE ", StringComparison.OrdinalIgnoreCase))
{
sl = sl.Substring(6);
sl = sl.Trim();
}
qry.Where = sl;
}
}
else
{
qry.From = objRet.TableName;
}
}
qry.DatabaseConnection = dbConn;
QueryParser qp = new QueryParser();
if (qp.BuildQuery(qry, this))
{
string filter = "";
bool bOK = false;
objRet.SourceQuery = qp.query;
qry = qp.query;
if (!qry.Fields.FromSingleTable() || objRet.CommandType == enmNonQueryType.Delete)
{
filter = qry.From;
if (filter.Length > 0)
{
filter = string.Format(CultureInfo.InvariantCulture, "{0}{1}", QueryParser.SQL_From(), filter);
}
}
if (qry.Where.Length > 0)
{
filter = string.Format(CultureInfo.InvariantCulture, "{0}{1}{2}", filter, QueryParser.SQL_Where(), qry.Where);
}
if (objRet.CommandType == enmNonQueryType.Delete)
{
if (qry.From.IndexOf(objRet.TableName, StringComparison.OrdinalIgnoreCase) < 0)
{
MessageBox.Show(this, string.Format(System.Globalization.CultureInfo.InvariantCulture, "Table [{0}] not found in [{1}]", objRet.TableName, qry.From), this.Text, System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Exclamation);
}
else
{
objRet.MultiRow = true;
txtWhere.Text = filter;
objRet.SetFilter(filter);
bOK = true;
}
}
else
{
filter = adjustFilter(filter);
txtWhere.Text = filter;
objRet.SetFilter(filter);
objRet.ResetSQL();
bOK = true;
}
if (bOK)
{
showSQL();
}
}
}
protected override void OnBindDataSource()
{
try
{
EasyQuery.LogMessage2("{0} - EasyDetailsGrid.OnBindDataSource starts...", TableName);
this.DataSource = null;
if (QueryDef.DataStorage != null && QueryDef.DataStorage.Tables.Count > 0 &&
_master != null && _master.DataStorage != null)
{
if (QueryDef.DataStorage.Tables[TableName] != null && _master.DataStorage.Tables[_master.TableName] != null)
{
if (string.IsNullOrEmpty(_relname))
{
_relname = string.Format(System.Globalization.CultureInfo.InvariantCulture, "rel{0}", (UInt32)(Guid.NewGuid().GetHashCode()));
}
bool bExist = false;
foreach (DataRelation d0 in _master.DataStorage.Relations)
{
if (string.CompareOrdinal(d0.RelationName, _relname) == 0)
{
bExist = true;
break;
}
}
if (!bExist)
{
DataColumn[] masterColumns = _master.GetColumnsByNames(MasterKeyColumns);
DataColumn[] detailColumns = this.GetColumnsByNames(DetailsKeyColumns);
if (masterColumns.Length == 0 || detailColumns.Length == 0)
{
}
else
{
for (int i = 0; i < masterColumns.Length; i++)
{
masterColumns[i].ReadOnly = true;
}
for (int i = 0; i < detailColumns.Length; i++)
{
detailColumns[i].ReadOnly = true;
}
DataRelation dr = new DataRelation(_relname, masterColumns, detailColumns);
foreach (DataRelation dr0 in _master.DataStorage.Relations)
{
if (string.Compare(_relname, dr0.RelationName, StringComparison.Ordinal) == 0)
{
_master.DataStorage.Relations.Remove(dr0);
break;
}
}
_master.DataStorage.EnforceConstraints = false;
_master.DataStorage.Relations.Add(dr);
bExist = true;
}
}
if (bExist)
{
BindSource.DataSource = _master.MasterSource;
BindSource.DataMember = _relname;
BindSource.AllowNew = !ReadOnly;
}
else
{
DataTable tbl = QueryDef.DataStorage.Tables[TableName];
if (tbl == null)
{
base.Query();
tbl = QueryDef.DataStorage.Tables[TableName];
}
if (tbl != null)
{
BindSource.DataSource = _master.DataStorage;
BindSource.DataMember = TableName;
}
}
base.DataSource = BindSource;
if (BindingContext != null)
{
BindingManagerBase bm = BindingContext[BindSource];
if (bm != null)
{
QueryDef.SetBindingContext(bm);
}
}
OnFillData();
}
}
}
catch (Exception e)
{
FormLog.NotifyException(ShowErrorMessage, e, "{0} - EasyDetailsGrid.OnBindDataSource", TableName);
}
EasyQuery.LogMessage2("{0} - EasyDetailsGrid.OnBindDataSource ends---", TableName);
}
/// <summary>
/// setup a database lookup for the field
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btLookup_Click(object sender, System.EventArgs e)
{
if (listView1.SelectedItems.Count > 0)
{
item = listView1.SelectedItems[0];
if (item != null)
{
//build the query for the database lookup
EasyQuery qry = null;
DataEditorLookupDB lk = fields[item.Index].editor as DataEditorLookupDB;
if (lk == null)
{
lk = new DataEditorLookupDB();
}
if (lk.Query != null)
{
qry = lk.Query;
}
if (qry == null)
{
qry = new EasyQuery();
}
//edit the query
EasyQuery q = EasyQuery.Edit(qry, this);
if (q != null)
{
lk.Query = q;
fields[item.Index].editor = lk;
txtLookup.Text = lk.ToString();
FieldList srcfields = lk.Query.Fields; //lookup fields provides the source fields
if (srcfields != null && srcfields.Count > 0)
{
if (ownerPerformer == null || srcfields.Count == 1)
{
//choose the field name that the value of this source field will map to
dlgSelectString dlg = new dlgSelectString();
for (int i = 0; i < fields.Count; i++)
{
dlg.LoadData(fields[i].Name);
}
dlg.Text = "Select field to update";
dlg.SetSel(lk.ValueField);
if (dlg.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
lk.ValueField = dlg.sRet;
}
}
else
{
if (lk.valuesMaps == null)
{
lk.valuesMaps = new DataBind();
}
lk.valuesMaps.SourceFields = new string[srcfields.Count];
for (int i = 0; i < srcfields.Count; i++)
{
lk.valuesMaps.SourceFields[i] = srcfields[i].Name;
}
dlgPropDataLink dlg2 = new dlgPropDataLink();
dlg2.LoadData(lk.valuesMaps);
}
}
}
}
}
}
public QueryWrapper(EasyQuery query)
{
_query = query;
}
public void CreateCommand(EasyQuery qry)
{
query = qry;
}
public void SetQuery(EasyQuery query)
{
_query = query;
}
public void CreateActionPhpScript(string objectName, string methodName, StringCollection code, StringCollection parameters, string returnReceiver)
{
if (string.CompareOrdinal(methodName, "StartWithParameterValues") == 0)
{
if (!phpSupported())
{
return;
}
if (DataDestination != null && DataDestination.DatabaseDestination != null && DataDestination.DatabaseDestination.Fields.Count > 0)
{
if (this.PhpTimeoutInMinutes > 0)
{
addCode(code, "if($this->{0}->PhpTimeoutInMinutes>0) ini_set('max_execution_time', $this->{0}->PhpTimeoutInMinutes * 60);\r\n", this.Site.Name);
}
string tblVar = string.Format(CultureInfo.InvariantCulture, "tbl{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
if (DataSource.SourceType == EnumDataSource.Database)
{
EasyQuery qry = DataSource.DatabaseSource.QueryDef;
if (qry != null)
{
//===fetch data==================================================================
string mySql = string.Format(CultureInfo.InvariantCulture, "mysql{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = new JsonSourceMySql();\r\n", mySql);
//
addCode(code, "\t${0}->SetCredential($this->{1}->sourceCredential);\r\n", mySql, this.Site.Name);
addCode(code, "\t${0}->SetDebug($this->DEBUG);\r\n", mySql);
string sql = qry.SqlQuery;
//sqlParams contains parameter names orderred by the appearance in sql, may duplicate
string[] sqlParams = EasyQuery.GetParameterNames(sql, qry.NameDelimiterBegin, qry.NameDelimiterEnd);
if (this.Parameters != null)
{
for (int i = 0; i < this.Parameters.Count; i++)
{
sql = sql.Replace(this.Parameters[i].Name, "?");
}
}
string sqlVar = string.Format(CultureInfo.InvariantCulture, "sql{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = \"{1} \";\r\n", sqlVar, sql);
addCode(code, "\t${0} = new JsonDataTable();\r\n", tblVar);
addCode(code, "\t${0}->TableName = '{1}';\r\n", tblVar, qry.TableName);
string psVar = string.Format(CultureInfo.InvariantCulture, "ps{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = array();\r\n", psVar);
if (qry.Parameters != null)
{
DbParameterList dbps = new DbParameterList(qry.Parameters);
for (int i = 0; i < sqlParams.Length; i++)
{
int k = dbps.GetIndex(sqlParams[i]);
if (k < 0)
{
throw new ExceptionLimnorDatabase("Query Parameter [{0}] not found", sqlParams[i]);
}
DbCommandParam dp = dbps[k];
string pVar = string.Format(CultureInfo.InvariantCulture, "p{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = new SqlClientParameter();\r\n", pVar);
addCode(code, "\t${0}->name = '{1}';\r\n", pVar, dp.Name);
addCode(code, "\t${0}->type = '{1}';\r\n", pVar, ValueConvertor.OleDbTypeToPhpMySqlType(dp.Type));
addCode(code, "\t${0}->value = {1};\r\n", pVar, parameters[k]);
addCode(code, "\t${0}[] = ${1};\r\n//\r\n", psVar, pVar);
}
}
addCode(code, "\t${0}->GetData(${1},${2},${3});\r\n", mySql, tblVar, sqlVar, psVar);
addCode(code, "\t$this->{0}->ErrorMessage = ${1}->errorMessage;\r\n", this.Site.Name, mySql);
//===data fetched on tblVar=============================================================
}
}
else if (DataSource.SourceType == EnumDataSource.TextFile)
{
string tblName = string.Format(CultureInfo.InvariantCulture, "srcTbl{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
string txtFileVar = string.Format(CultureInfo.InvariantCulture, "txtFile{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "${0} = new DataSourceTextFile();\r\n", txtFileVar);
addCode(code, "\t${0} = new JsonDataTable();\r\n", tblVar);
addCode(code, "\t${0}->TableName = '{1}';\r\n", tblVar, tblName);
//
if (this.DataSource.TextSource.Delimiter == enumSourceTextDelimiter.Comma)
{
addCode(code, "\t${0}->delimiter=',';\r\n", txtFileVar);
}
else if (this.DataSource.TextSource.Delimiter == enumSourceTextDelimiter.TAB)
{
addCode(code, "\t${0}->delimiter='\\t';\r\n", txtFileVar);
}
if (this.DataSource.TextSource.HasHeader)
{
addCode(code, "\t${0}->hasHeader=true;\r\n", txtFileVar);
}
else
{
addCode(code, "\t${0}->hasHeader=false;\r\n", txtFileVar);
}
IPropertyValueLink fnl = this.DataSource.TextSource.GetPropertyLink("Filename");
if (fnl != null && fnl.IsValueLinkSet())
{
addCode(code, "\t${0}->filepath={1};\r\n", txtFileVar, fnl.GetPhpScriptReferenceCode(code));
}
else
{
addCode(code, "\t${0}->filepath='{1}';\r\n", txtFileVar, this.DataSource.TextSource.Filename);
}
//
addCode(code, "\t${0}->GetData(${1});\r\n", txtFileVar, tblVar);
addCode(code, "\t$this->{0}->ErrorMessage = ${1}->errorMessage;\r\n", this.Site.Name, txtFileVar);
addCode(code, "\tif(strlen(${0}->errorMessage) > 0)\r\n", txtFileVar);
code.Add("\t{\r\n");
code.Add("\t\tif($this->DEBUG)\r\n\t\t{\r\n");
addCode(code, "\t\t\techo 'Error reading text file: '.${0}->filepath.' Error message:'.${0}->errorMessage;\r\n", txtFileVar);
code.Add("\t\t\treturn;\r\n");
code.Add("\t\t}\r\n");
code.Add("\t}\r\n");
}
//
string mySqlTVar = string.Format(CultureInfo.InvariantCulture, "mySqlT{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = new JsonSourceMySql();\r\n", mySqlTVar);
addCode(code, "\t${0}->SetCredential($this->{1}->destinationCredential);\r\n", mySqlTVar, this.Site.Name);
addCode(code, "\t${0}->SetDebug($this->DEBUG);\r\n", mySqlTVar);
//
StringBuilder sbSql = new StringBuilder();
StringBuilder sbSqlValues = new StringBuilder();
sbSql.Append("Insert into ");
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterBegin);
sbSql.Append(DataDestination.DatabaseDestination.TableName);
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterEnd);
sbSql.Append(" (");
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterBegin);
sbSql.Append(DataDestination.DatabaseDestination.Fields[0].Name);
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterEnd);
sbSqlValues.Append("?");
for (int i = 1; i < DataDestination.DatabaseDestination.Fields.Count; i++)
{
sbSql.Append(",");
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterBegin);
sbSql.Append(DataDestination.DatabaseDestination.Fields[i].Name);
sbSql.Append(DataDestination.DatabaseDestination.DatabaseConnection.NameDelimiterEnd);
sbSqlValues.Append(",?");
}
sbSql.Append(") values (");
sbSql.Append(sbSqlValues.ToString());
sbSql.Append(")");
//
string myIVar = string.Format(CultureInfo.InvariantCulture, "mySqlI{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0} = ${1}->GetMySqli();\r\n", myIVar, mySqlTVar);
string statementVar = string.Format(CultureInfo.InvariantCulture, "state{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\t${0}=${1}->prepareStatement(${2},\"{3}\");\r\n", statementVar, mySqlTVar, myIVar, sbSql.ToString());
//
string paramsVar = string.Format(CultureInfo.InvariantCulture, "params{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
//
code.Add("\t//loop through rows of $tbl\r\n");
string idxVar = string.Format(CultureInfo.InvariantCulture, "idx{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
string rowVar = string.Format(CultureInfo.InvariantCulture, "row{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
addCode(code, "\tforeach (${0}->Rows as ${1} => ${2})\r\n", tblVar, idxVar, rowVar);
code.Add("\t{\r\n");
addCode(code, "\t\t${0}=array();\r\n", paramsVar);
string paramVar = string.Format(CultureInfo.InvariantCulture, "param{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
for (int i = 0; i < DataDestination.DatabaseDestination.Fields.Count; i++)
{
addCode(code, "\t\t${0}=new SqlClientParameter();\r\n", paramVar);
addCode(code, "\t\t${0}->name = '{1}';\r\n", paramVar, DataDestination.DatabaseDestination.Fields[i].Name);
addCode(code, "\t\t${0}->type = '{1}';\r\n", paramVar, ValueConvertor.OleDbTypeToPhpMySqlType(DataDestination.DatabaseDestination.Fields[i].OleDbType));
addCode(code, "\t\t${0}->value = ${1}->ItemArray[{2}];\r\n", paramVar, rowVar, i);
addCode(code, "\t\t${0}[] = ${1};\r\n//\r\n", paramsVar, paramVar);
}
addCode(code, "\t\t${0}->executeStatement(${1},${2}, ${3});\r\n", mySqlTVar, myIVar, statementVar, paramsVar);
//
code.Add("\t}\r\n");
//
addCode(code, "\t${0}->free_result();\r\n", statementVar);
addCode(code, "\t${0}->close();\r\n", statementVar);
addCode(code, "\t${0}->close();\r\n", myIVar);
}
//
code.Add(string.Format(CultureInfo.InvariantCulture, "\t$this->SetServerComponentName('{0}');\r\n", this.Site.Name));
}
}
public DataQuery()
{
_qry = new EasyQuery();
}
public static void SetLogFilePath(string logFileName)
{
EasyQuery.SetLogFilePath(logFileName);
}
public void CreateActionPhpScript(string objectName, string methodName, StringCollection code, StringCollection parameters, string returnReceiver)
{
string connName = GetConnectionCodeName();
if (string.CompareOrdinal(methodName, "ExecuteWithParameterValues") == 0)
{
if (_sql != null && connect != null)
{
code.Add("$GLOBALS[\"debugError\"] = '';\r\n");
code.Add("$msql = new JsonSourceMySql();\r\n");
code.Add(string.Format(CultureInfo.InvariantCulture, "$msql->SetCredential($this->{0});\r\n", connName));
code.Add("$msql->SetDebug($this->DEBUG);\r\n");
//
string sql;
string[] sqlParams;
List <string> inputParams = new List <string>();
List <string> outputParams = new List <string>();
if (this.IsStoredProc)
{
StringBuilder sb = new StringBuilder();
sb.Append("call ");
sb.Append(_sql.SQL);
sb.Append("(");
DatabaseExecuter de = this as DatabaseExecuter;
DbParameterList ps = de.Parameters;
if (ps != null && ps.Count > 0)
{
ParameterDirection[] pds = de.Param_Directions;
for (int i = 0; i < ps.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
ParameterDirection pd = ParameterDirection.Input;
if (pds != null && pds.Length > i)
{
pd = pds[i];
}
switch (pd)
{
case ParameterDirection.Input:
inputParams.Add(ps[i].Name);
sb.Append(" ? ");
break;
case ParameterDirection.InputOutput:
outputParams.Add(ps[i].Name);
sb.Append(string.Format(CultureInfo.InvariantCulture, " @{0} ", ps[i].Name));
break;
case ParameterDirection.Output:
outputParams.Add(ps[i].Name);
sb.Append(string.Format(CultureInfo.InvariantCulture, " @{0} ", ps[i].Name));
break;
}
}
sqlParams = inputParams.ToArray();
}
else
{
sqlParams = new string[] { };
}
sb.Append(")");
sql = sb.ToString();
}
else
{
sql = _sql.SQL;
sqlParams = EasyQuery.GetParameterNames(sql, connect.NameDelimiterBegin, connect.NameDelimiterEnd);
if (sqlParams != null)
{
for (int i = 0; i < sqlParams.Length; i++)
{
sql = sql.Replace(sqlParams[i], "?");
}
}
}
code.Add(string.Format(CultureInfo.InvariantCulture, "$sql = \"{0} \";\r\n", sql));
code.Add("$ps = array();\r\n");
if (this.Parameters != null)
{
for (int i = 0; i < sqlParams.Length; i++)
{
int k = this.Parameters.GetIndex(sqlParams[i]);
if (k < 0)
{
throw new ExceptionLimnorDatabase("Query Parameter [{0}] not found", sqlParams[i]);
}
DbCommandParam dp = this.Parameters[k];
code.Add("$p = new SqlClientParameter();\r\n");
code.Add(string.Format(CultureInfo.InvariantCulture, "$p->name = '{0}';\r\n", dp.Name));
code.Add(string.Format(CultureInfo.InvariantCulture, "$p->type = '{0}';\r\n", ValueConvertor.OleDbTypeToPhpMySqlType(dp.Type)));
code.Add(string.Format(CultureInfo.InvariantCulture, "$p->value = {0};\r\n", parameters[k]));
code.Add("$ps[] = $p;\r\n//\r\n");
}
}
if (outputParams.Count > 0)
{
StringBuilder sb2 = new StringBuilder();
sb2.Append("SELECT ");
for (int i = 0; i < outputParams.Count; i++)
{
if (i > 0)
{
sb2.Append(",");
}
sb2.Append(string.Format(CultureInfo.InvariantCulture, "@{0} AS {0} ", outputParams[i]));
}
string s2 = string.Format(CultureInfo.InvariantCulture, "$qry{0}", Guid.NewGuid().GetHashCode().ToString("x", CultureInfo.InvariantCulture));
code.Add(string.Format(CultureInfo.InvariantCulture, "{0} = \"{1}\";\r\n", s2, sb2.ToString()));
code.Add(string.Format(CultureInfo.InvariantCulture, "$this->{0}->ExecuteWithOutputs($sql,{1},$ps,$msql);\r\n", this.Site.Name, s2));
for (int i = 0; i < outputParams.Count; i++)
{
code.Add(string.Format(CultureInfo.InvariantCulture, "$this->{0}->{1}=$this->{0}->outputValues['{1}'];\r\n", this.Site.Name, outputParams[i]));
}
}
else
{
code.Add(string.Format(CultureInfo.InvariantCulture, "$this->{0}->ExecuteNonQuery($sql,$ps,$msql);\r\n", this.Site.Name));
}
if (!string.IsNullOrEmpty(returnReceiver))
{
code.Add(string.Format(CultureInfo.InvariantCulture,
"{0}=$this->{1}->errorMessage;\r\n", returnReceiver, this.Site.Name));
}
code.Add(string.Format(CultureInfo.InvariantCulture, "$this->SetServerComponentName('{0}');\r\n", this.Site.Name));
}
}
}
public void SetQuery(EasyQuery query)
{
_query = query;
QueryDatabase();
}
