protected void BindGrid()
{
plcDayRows.Controls.Clear();
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwACTIVITIES_List " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
DateTime dtDATE_START = new DateTime(Math.Max(1753, dtCurrentDate.Year), dtCurrentDate.Month, dtCurrentDate.Day, 0, 0, 0);
DateTime dtDATE_END = dtDATE_START.AddDays(1);
// 11/27/2006 Paul. Make sure to filter relationship data based on team access rights.
Security.Filter(cmd, "Calls", "list");
// 01/16/2007 Paul. Use AppendParameter so that duplicate ASSIGNED_USER_ID can be avoided.
// 01/19/2007 Paul. Fix AppendParamenter. @ should not be used in field name.
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID");
cmd.CommandText += " and ( DATE_START >= @DATE_START and DATE_START < @DATE_END" + ControlChars.CrLf;
cmd.CommandText += " or DATE_END >= @DATE_START and DATE_END < @DATE_END" + ControlChars.CrLf;
cmd.CommandText += " or DATE_START < @DATE_START and DATE_END > @DATE_END" + ControlChars.CrLf;
cmd.CommandText += " ) " + ControlChars.CrLf;
cmd.CommandText += " order by DATE_START asc, NAME asc " + ControlChars.CrLf;
// 03/19/2007 Paul. Need to query activities based on server time.
Sql.AddParameter(cmd, "@DATE_START", T10n.ToServerTime(dtDATE_START));
Sql.AddParameter(cmd, "@DATE_END", T10n.ToServerTime(dtDATE_END));
if (bDebug)
{
RegisterClientScriptBlock("vwACTIVITIES_List", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 07/24/2005 Paul. Since this is not a dynamic grid, we must convert the status manually.
foreach (DataRow row in dt.Rows)
{
switch (Sql.ToString(row["ACTIVITY_TYPE"]))
{
case "Calls": row["STATUS"] = L10n.Term("Call") + " " + L10n.Term(".call_status_dom.", row["STATUS"]); break;
case "Meetings": row["STATUS"] = L10n.Term("Meeting") + " " + L10n.Term(".meeting_status_dom.", row["STATUS"]); break;
}
}
int nHourMin = 8;
int nHourMax = 18;
if (dt.Rows.Count > 0)
{
DateTime dtMin = Sql.ToDateTime(dt.Rows[0]["DATE_START"]);
DateTime dtMax = Sql.ToDateTime(dt.Rows[dt.Rows.Count - 1]["DATE_END"]);
nHourMin = Math.Min(dtMin.Hour, nHourMin);
nHourMax = Math.Max(dtMax.Hour, nHourMax);
}
CultureInfo ciEnglish = CultureInfo.CreateSpecificCulture("en-US");
for (int iHour = nHourMin; iHour <= nHourMax; iHour++)
{
DataView vwMain = new DataView(dt);
DateTime dtHOUR_START = new DateTime(dtCurrentDate.Year, dtCurrentDate.Month, dtCurrentDate.Day, iHour, 0, 0);
DateTime dtHOUR_END = dtHOUR_START.AddHours(1);
DateTime dtHOUR_START_ServerTime = T10n.ToServerTime(dtHOUR_START);
DateTime dtHOUR_END_ServerTime = T10n.ToServerTime(dtHOUR_END);
// 09/27/2005 Paul. System.Data.DataColumn.Expression documentation has description how to define dates and strings.
// 01/21/2006 Paul. Brazilian culture is having a problem with date formats. Try using the european format yyyy/MM/dd HH:mm:ss.
// 06/09/2006 Paul. Fix so that a 1 hour meeting does not span two hours. DATE_END should not allow DATE_END = HOUR_START.
// 06/13/2006 Paul. Italian has a problem with the time separator. Use the value from the culture from CalendarControl.SqlDateTimeFormat.
// 06/14/2006 Paul. The Italian problem was that it was using the culture separator, but DataView only supports the en-US format.
string sHOUR_START_ServerTime = dtHOUR_START_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
string sHOUR_END_ServerTime = dtHOUR_END_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
vwMain.RowFilter = " DATE_START >= #" + sHOUR_START_ServerTime + "# and DATE_START < #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_END > #" + sHOUR_START_ServerTime + "# and DATE_END <= #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_START < #" + sHOUR_START_ServerTime + "# and DATE_END > #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf;
#if DEBUG
// RegisterClientScriptBlock("vwACTIVITIES_List" + dtHOUR_START.ToOADate().ToString(), Sql.EscapeJavaScript(vwMain.RowFilter));
#endif
DayRow ctlDayRow = LoadControl("DayRow.ascx") as DayRow;
// 06/09/2006 Paul. Add to controls list before bindging.
plcDayRows.Controls.Add(ctlDayRow);
ctlDayRow.Command = new CommandEventHandler(Page_Command);
ctlDayRow.DATE_START = dtHOUR_START;
//ctlDayRow.DATE_END = dtHOUR_END;
ctlDayRow.DataSource = vwMain;
// 06/09/2006 Paul. Need to bind after specifying the data source.
ctlDayRow.DataBind();
//ctlDayRow.DATE_START = new DateTime(dtCurrentDate.Year, dtCurrentDate.Month, dtCurrentDate.Day, iHour, 0, 0);
//ctlDayRow.DATE_END = ctlDayRow.DATE_START.AddHours(1);
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
/// <summary>
/// Gets a DbDataAdapter with Standard update behavior.
/// </summary>
/// <returns>A <see cref="DbDataAdapter"/>.</returns>
/// <seealso cref="DbDataAdapter"/>
public DbDataAdapter GetDataAdapter()
{
return(dbProvider.CreateDataAdapter());
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gDOCUMENT_ID = Sql.ToGuid(txtDOCUMENT_ID.Value);
if (!Sql.IsEmptyGuid(gDOCUMENT_ID))
{
try
{
SqlProcs.spCONTRACTS_DOCUMENTS_Update(gID, gDOCUMENT_ID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTRACTS_DOCUMENTS " + ControlChars.CrLf
+ " where CONTRACT_ID = @CONTRACT_ID" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@CONTRACT_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwCONTRACTS_DOCUMENTS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
public static DbDataAdapter NewAdapter()
{
return(DbProviderFactory.CreateDataAdapter());
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
this.ValidateEditViewFields(m_sMODULE + ".EditView");
this.ValidateEditViewFields(m_sMODULE + ".EditAddress");
this.ValidateEditViewFields(m_sMODULE + ".EditDescription");
if (Page.IsValid)
{
string sCUSTOM_MODULE = "LEADS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
DataRow rowCurrent = null;
DataTable dtCurrent = new DataTable();
if (!Sql.IsEmptyGuid(gID))
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwLEADS_Edit" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, m_sMODULE, "edit");
Sql.AppendParameter(cmd, gID, "ID", false);
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtCurrent);
if (dtCurrent.Rows.Count > 0)
{
rowCurrent = dtCurrent.Rows[0];
}
else
{
// 11/19/2007 Paul. If the record is not found, clear the ID so that the record cannot be updated.
// It is possible that the record exists, but that ACL rules prevent it from being selected.
gID = Guid.Empty;
}
}
}
}
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
// 04/24/2006 Paul. Upgrade to SugarCRM 4.2 Schema.
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
// 12/29/2007 Paul. TEAM_ID is now in the stored procedure.
SqlProcs.spLEADS_Update
(ref gID
, new DynamicControl(this, rowCurrent, "ASSIGNED_USER_ID").ID
, new DynamicControl(this, rowCurrent, "SALUTATION").SelectedValue
, new DynamicControl(this, rowCurrent, "FIRST_NAME").Text
, new DynamicControl(this, rowCurrent, "LAST_NAME").Text
, new DynamicControl(this, rowCurrent, "TITLE").Text
, new DynamicControl(this, rowCurrent, "REFERED_BY").Text
, new DynamicControl(this, rowCurrent, "LEAD_SOURCE").SelectedValue
, new DynamicControl(this, rowCurrent, "LEAD_SOURCE_DESCRIPTION").Text
, new DynamicControl(this, rowCurrent, "STATUS").SelectedValue
, new DynamicControl(this, rowCurrent, "STATUS_DESCRIPTION").Text
, new DynamicControl(this, rowCurrent, "DEPARTMENT").Text
, Guid.Empty // 06/24/2005. REPORTS_TO_ID is not used in version 3.0.
, new DynamicControl(this, rowCurrent, "DO_NOT_CALL").Checked
, new DynamicControl(this, rowCurrent, "PHONE_HOME").Text
, new DynamicControl(this, rowCurrent, "PHONE_MOBILE").Text
, new DynamicControl(this, rowCurrent, "PHONE_WORK").Text
, new DynamicControl(this, rowCurrent, "PHONE_OTHER").Text
, new DynamicControl(this, rowCurrent, "PHONE_FAX").Text
, new DynamicControl(this, rowCurrent, "EMAIL1").Text
, new DynamicControl(this, rowCurrent, "EMAIL2").Text
, new DynamicControl(this, rowCurrent, "EMAIL_OPT_OUT").Checked
, new DynamicControl(this, rowCurrent, "INVALID_EMAIL").Checked
, new DynamicControl(this, rowCurrent, "PRIMARY_ADDRESS_STREET").Text
, new DynamicControl(this, rowCurrent, "PRIMARY_ADDRESS_CITY").Text
, new DynamicControl(this, rowCurrent, "PRIMARY_ADDRESS_STATE").Text
, new DynamicControl(this, rowCurrent, "PRIMARY_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, rowCurrent, "PRIMARY_ADDRESS_COUNTRY").Text
, new DynamicControl(this, rowCurrent, "ALT_ADDRESS_STREET").Text
, new DynamicControl(this, rowCurrent, "ALT_ADDRESS_CITY").Text
, new DynamicControl(this, rowCurrent, "ALT_ADDRESS_STATE").Text
, new DynamicControl(this, rowCurrent, "ALT_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, rowCurrent, "ALT_ADDRESS_COUNTRY").Text
, new DynamicControl(this, rowCurrent, "DESCRIPTION").Text
, new DynamicControl(this, rowCurrent, "ACCOUNT_NAME").Text
, new DynamicControl(this, rowCurrent, "CAMPAIGN_ID").ID
, new DynamicControl(this, rowCurrent, "TEAM_ID").ID
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(".moduleList." + m_sMODULE));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "view") >= 0);
if (!this.Visible)
{
return;
}
try
{
gID = Sql.ToGuid(Request["ID"]);
// 11/28/2005 Paul. We must always populate the table, otherwise it will disappear during event processing.
//if ( !IsPostBack )
{
if (!Sql.IsEmptyGuid(gID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwEMAILS_Edit" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, m_sMODULE, "view");
Sql.AppendParameter(cmd, gID, "ID", false);
con.Open();
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
ctlModuleHeader.Title = Sql.ToString(rdr["NAME"]);
SetPageTitle(L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
Utils.UpdateTracker(Page, m_sMODULE, gID, ctlModuleHeader.Title);
this.AppendDetailViewFields(m_sMODULE + ".DetailView", tblMain, rdr);
// 11/17/2005 Paul. Archived emails allow editing of the Date & Time Sent.
string sEMAIL_TYPE = Sql.ToString(rdr["TYPE"]).ToLower();
ctlModuleHeader.EnableModuleLabel = false;
switch (sEMAIL_TYPE)
{
case "archived":
ctlModuleHeader.Title = L10n.Term("Emails.LBL_ARCHIVED_MODULE_NAME") + ":" + ctlModuleHeader.Title;
Response.Redirect("view.aspx?ID=" + gID.ToString());
break;
case "inbound":
// 06/28/2007 Paul. Inbound emails should not automatically go to edit mode.
ctlModuleHeader.Title = L10n.Term("Emails.LBL_INBOUND_TITLE") + ":" + ctlModuleHeader.Title;
break;
case "out":
ctlModuleHeader.Title = L10n.Term("Emails.LBL_LIST_FORM_SENT_TITLE") + ":" + ctlModuleHeader.Title;
Response.Redirect("view.aspx?ID=" + gID.ToString());
break;
case "sent":
ctlModuleHeader.Title = L10n.Term("Emails.LBL_LIST_FORM_SENT_TITLE") + ":" + ctlModuleHeader.Title;
Response.Redirect("view.aspx?ID=" + gID.ToString());
break;
case "campaign":
// 01/13/2008 Paul. Campaign emails should be treated the same as outbound emails.
ctlModuleHeader.Title = L10n.Term("Emails.LBL_LIST_FORM_SENT_TITLE") + ":" + ctlModuleHeader.Title;
Response.Redirect("view.aspx?ID=" + gID.ToString());
break;
default:
sEMAIL_TYPE = "draft";
ctlModuleHeader.Title = L10n.Term("Emails.LBL_COMPOSE_MODULE_NAME") + ":" + ctlModuleHeader.Title;
// 01/21/2006 Paul. Draft messages go directly to edit mode.
Response.Redirect("edit.aspx?ID=" + gID.ToString());
break;
}
}
else
{
// 11/25/2006 Paul. If item is not visible, then don't show its sub panel either.
plcSubPanel.Visible = false;
ctlInboundButtons.DisableAll();
ctlInboundButtons.ErrorText = L10n.Term("ACL.LBL_NO_ACCESS");
}
}
}
sSQL = "select * " + ControlChars.CrLf
+ " from vwEMAILS_Attachments" + ControlChars.CrLf
+ " where EMAIL_ID = @EMAIL_ID" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@EMAIL_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwEMAILS_Attachments", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
ctlAttachments.DataSource = dt.DefaultView;
ctlAttachments.DataBind();
}
}
}
}
}
}
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlInboundButtons.ErrorText = ex.Message;
}
}
/* this version uses an ADO.NET DbProviderFactory and follows the standard procedure
* in order to execute a parameterized query */
private void button1_Click(object sender, EventArgs e)
{
using (DbConnection Con = factory.CreateConnection())
{
Con.ConnectionString = cs;
Con.Open();
DbCommand Cmd = Con.CreateCommand();
// 1. Where clause preparation.
// ===============================================================
/* TradeDate */
string Where = "where TRADE.TRADE_DATE >= ? " + Environment.NewLine;
DbParameter Param = factory.CreateParameter();
Param.ParameterName = "TradeDate";
Param.Value = edtDate.Value;
Cmd.Parameters.Add(Param);
/* Customer */
if (!string.IsNullOrEmpty(edtCustomer.Text))
{
Where += " and TRADER.NAME like ? " + Environment.NewLine;
string Customer = edtCustomer.Text.Trim();
if (!Customer.EndsWith("%"))
{
Customer += "%";
}
Param = factory.CreateParameter();
Param.ParameterName = "Customer";
Param.Value = Customer;
Cmd.Parameters.Add(Param);
}
/* Total */
if (!string.IsNullOrEmpty(edtTotal.Text))
{
double Total = 0;
if (double.TryParse(edtTotal.Text, out Total))
{
Where += " and TRADE.TOTAL_VALUE >= ? " + Environment.NewLine;
}
Param = factory.CreateParameter();
Param.ParameterName = "Total";
Param.Value = Total;
Cmd.Parameters.Add(Param);
}
Cmd.CommandText = SelectSql + Where;
// 2. Command execution
// ===============================================================
DataTable table = new DataTable();
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = Cmd;
adapter.Fill(table);
}
Grid.DataSource = table;
}
}
public DataSet ExecuteDataSet(DbCommandWrapper cmdWrapper,
bool isStoredProc, string cmdText)
{
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd.Connection = conn;
cmd = conn.CreateCommand();
cmdWrapper.Command = cmd;
if (isStoredProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandText = cmdText;
dataSet = new DataSet();
foreach (ParameterClause param in cmdWrapper.Parameters)
{
AddParameter(cmd, param);
}
da.SelectCommand = cmd;
da.Fill(dataSet);
}
catch (Exception ex)
{
if (da != null)
{
da.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
logger.Write(Severity.Error, ex.ToString());
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
return(dataSet);
}
public List <T> ExecuteList <T>(DbCommandWrapper cmdWrapper, bool isStoredProc,
string sql, IDataMapper <T> dataMapper, int index, int size)
{
List <T> list = new List <T>();
IDataReader rdr = null;
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd = conn.CreateCommand();
cmdWrapper.Command = cmd;
cmd.Connection = conn;
if (isStoredProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandText = sql;
dataSet = new DataSet();
da.SelectCommand = cmd;
da.Fill(dataSet, index, size, typeof(T).ToString());
foreach (ParameterClause param in cmdWrapper.Parameters)
{
AddParameter(cmd, param);
}
rdr = dataSet.Tables[typeof(T).ToString()].CreateDataReader();
while (rdr.Read())
{
list.Add(dataMapper.Map(rdr));
}
}
catch (Exception ex)
{
logger.Write(Severity.Error, ex.ToString());
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
}
return(list);
}
private static List <object[]> GetObjectArrayFromSQL(string strQuery, string strConnection, string strProvider, DataTable dt, string[] operators, out SqlDataRecord record, bool addLevel)
{
record = null;
List <object[]> listObjects = new List <object[]>();
DbProviderFactory factory = DbProviderFactories.GetFactory(strProvider);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object (i.e. SqlConnection ...)
if (connection != null)
{
connection.ConnectionString = strConnection;
try
{
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
selectCommand.CommandText = strQuery;
if (dap != null)
{
DbDataReader reader = selectCommand.ExecuteReader();
List <SqlMetaData> outputColumns = new List <SqlMetaData>();
object[] recordObjectStart = new object[reader.FieldCount];
//only if data is available
if (reader.Read())
{
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObjectStart[iCol] = (object)reader[iCol];
if (iCol >= operators.Length)
{
DataColumn col = new DataColumn(reader.GetName(iCol), reader.GetFieldType(iCol));
SqlMetaData outputColumn;
if (col.DataType == typeof(Int32) || col.DataType == typeof(Int64) || col.DataType == typeof(DateTime))
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType));
}
else
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
}
outputColumns.Add(outputColumn);
//Check if column name already exists
if (!dt.Columns.Contains(col.ColumnName))
{
dt.Columns.Add(col);
}
else
{
throw new Exception("Column name '" + col.ColumnName + "' already exists. Use an alias instead.");
}
}
}
}
listObjects.Add(recordObjectStart);
//add level column for multiple skyline algorithms
if (addLevel)
{
SqlMetaData outputColumnLevel = new SqlMetaData("level", TypeConverter.ToSqlDbType(typeof(Int32)));
outputColumns.Add(outputColumnLevel);
}
record = new SqlDataRecord(outputColumns.ToArray());
//Now save all records to array (Profiling: faster than working with the reader in the algorithms)
while (reader.Read())
{
object[] recordObject = new object[reader.FieldCount];
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObject[iCol] = (object)reader[iCol];
}
listObjects.Add(recordObject);
}
reader.Close();
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(listObjects);
}
/// <summary>
/// Gets a DbDataAdapter with Standard update behavior.
/// </summary>
/// <returns>A <see cref="DbDataAdapter"/>.</returns>
/// <seealso cref="DbDataAdapter"/>
public DbDataAdapter GetDataAdapter()
{
return(DbProviderFactory.CreateDataAdapter());
}
private void AddInvitee(Guid gUSER_ID)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
string sFULL_NAME = String.Empty;
string sINVITEE_TYPE = String.Empty;
sSQL = "select * " + ControlChars.CrLf
+ " from vwINVITEES" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ID", gUSER_ID);
#if DEBUG
Page.RegisterClientScriptBlock("vwINVITEES", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
sFULL_NAME = Sql.ToString(rdr["FULL_NAME"]);
sINVITEE_TYPE = Sql.ToString(rdr["INVITEE_TYPE"]);
}
}
}
sSQL = "select * " + ControlChars.CrLf
+ " from vwACTIVITIES_List " + ControlChars.CrLf
+ " where ASSIGNED_USER_ID = @ASSIGNED_USER_ID " + ControlChars.CrLf
+ " and ( DATE_START >= @DATE_START and DATE_START < @DATE_END" + ControlChars.CrLf
+ " or DATE_END >= @DATE_START and DATE_END < @DATE_END" + ControlChars.CrLf
+ " or DATE_START < @DATE_START and DATE_END > @DATE_END" + ControlChars.CrLf
+ " ) " + ControlChars.CrLf
+ " order by DATE_START asc, NAME asc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ASSIGNED_USER_ID", gUSER_ID);
Sql.AddParameter(cmd, "@DATE_START", T10n.ToServerTime(dtSCHEDULE_START));
Sql.AddParameter(cmd, "@DATE_END", T10n.ToServerTime(dtSCHEDULE_END));
#if DEBUG
Page.RegisterClientScriptBlock("vwACTIVITIES_List", Sql.ClientScriptBlock(cmd));
#endif
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
HtmlTableRow rowInvitee = new HtmlTableRow();
tblSchedule.Rows.Add(rowInvitee);
rowInvitee.Attributes.Add("class", "schedulerAttendeeRow");
HtmlTableCell cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
cellInvitee.Attributes.Add("class", "schedulerAttendeeCell");
Literal litFULL_NAME = new Literal();
Image imgInvitee = new Image();
cellInvitee.Controls.Add(imgInvitee);
cellInvitee.Controls.Add(litFULL_NAME);
imgInvitee.Width = 16;
imgInvitee.Height = 16;
imgInvitee.ImageAlign = ImageAlign.AbsMiddle;
imgInvitee.ImageUrl = Session["themeURL"] + "images/" + sINVITEE_TYPE + ".gif";
litFULL_NAME.Text = sFULL_NAME;
if (dt.Rows.Count > 0)
{
DataView vwMain = new DataView(dt);
CultureInfo ciEnglish = CultureInfo.CreateSpecificCulture("en-US");
for (DateTime dtHOUR_START = dtSCHEDULE_START; dtHOUR_START < dtSCHEDULE_END; dtHOUR_START = dtHOUR_START.AddMinutes(15))
{
DateTime dtHOUR_END = dtHOUR_START.AddMinutes(15);
DateTime dtHOUR_START_ServerTime = T10n.ToServerTime(dtHOUR_START);
DateTime dtHOUR_END_ServerTime = T10n.ToServerTime(dtHOUR_END);
// 09/27/2005 Paul. System.Data.DataColumn.Expression documentation has description how to define dates and strings.
// 08/08/2006 Paul. Use the same ServerTime logic as DayGrid.ascx.cs to solve date formatting issues on international systems.
string sHOUR_START_ServerTime = dtHOUR_START_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
string sHOUR_END_ServerTime = dtHOUR_END_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
vwMain.RowFilter = " DATE_START >= #" + sHOUR_START_ServerTime + "# and DATE_START < #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_END > #" + sHOUR_START_ServerTime + "# and DATE_END <= #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_START < #" + sHOUR_START_ServerTime + "# and DATE_END > #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf;
#if DEBUG
// Page.RegisterClientScriptBlock("vwACTIVITIES_List" + dtHOUR_START.ToOADate().ToString(), Sql.EscapeJavaScript(vwMain.RowFilter));
#endif
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
if (dtHOUR_START == dtDATE_END)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellEndTime");
}
else if (dtHOUR_START == dtDATE_START)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellStartTime");
}
else
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellHour");
}
if (vwMain.Count > 0)
{
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #aa4d4d");
}
else
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #4d5eaa");
}
}
else
{
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #ffffff");
}
}
}
}
else
{
for (DateTime dtHOUR_START = dtSCHEDULE_START; dtHOUR_START < dtSCHEDULE_END; dtHOUR_START = dtHOUR_START.AddMinutes(15))
{
DateTime dtHOUR_END = dtHOUR_START.AddMinutes(15);
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
if (dtHOUR_START == dtDATE_END)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellEndTime");
}
else if (dtHOUR_START == dtDATE_START)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellStartTime");
}
else
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellHour");
}
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #ffffff");
}
}
}
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
cellInvitee.Attributes.Add("class", "schedulerAttendeeDeleteCell");
ImageButton btnDelete = new ImageButton();
Literal litSpace = new Literal();
LinkButton lnkDelete = new LinkButton();
btnDelete.CommandName = "Invitees.Delete";
lnkDelete.CommandName = "Invitees.Delete";
btnDelete.CommandArgument = gUSER_ID.ToString();
lnkDelete.CommandArgument = gUSER_ID.ToString();
btnDelete.Command += new CommandEventHandler(this.Page_Command);
lnkDelete.Command += new CommandEventHandler(this.Page_Command);
btnDelete.CssClass = "listViewTdToolsS1";
lnkDelete.CssClass = "listViewTdToolsS1";
Guid gID = Sql.ToGuid(Request["ID"]);
if (Sql.IsEmptyGuid(gID))
{
btnDelete.AlternateText = L10n.Term(".LNK_REMOVE");
lnkDelete.Text = L10n.Term(".LNK_REMOVE");
}
else
{
btnDelete.AlternateText = L10n.Term(".LNK_DELETE");
lnkDelete.Text = L10n.Term(".LNK_DELETE");
}
litSpace.Text = " ";
btnDelete.ImageUrl = Session["themeURL"] + "images/delete_inline.gif";
btnDelete.BorderWidth = 0;
btnDelete.Width = 12;
btnDelete.Height = 12;
btnDelete.ImageAlign = ImageAlign.AbsMiddle;
cellInvitee.Controls.Add(btnDelete);
cellInvitee.Controls.Add(litSpace);
cellInvitee.Controls.Add(lnkDelete);
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
}
}
/// <summary>
/// proxy
/// </summary>
public override DbDataAdapter CreateDataAdapter()
{
return(tail.CreateDataAdapter());
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Bugs.LBL_LIST_FORM_TITLE"));
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwBUGS_List" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, "Bugs", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "SQLCode", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 08/01/2005 Paul. Convert the term here so that sorting will apply.
foreach (DataRow row in dt.Rows)
{
// 08/17/2005 Paul. Don't convert if NULL.
row["STATUS"] = L10n.Term(".bug_status_dom.", row["STATUS"]);
row["TYPE"] = L10n.Term(".bug_type_dom.", row["TYPE"]);
row["PRIORITY"] = L10n.Term(".bug_priority_dom.", row["PRIORITY"]);
}
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
Page.DataBind();
}
}
/// <summary>
/// 获取数据源
/// </summary>
/// <returns></returns>
protected virtual object GetDataSource()
{
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings[this.Connection.GetTextValue()];
if (setting == null)
{
return(null);
}
DbProviderFactory dbFactory = Utility.CreateDbProviderFactory(setting.ProviderName);
if (dbFactory == null)
{
return(null);
}
object result = null;
using (DbConnection dbConnection = dbFactory.CreateConnection())
{
dbConnection.ConnectionString = setting.ConnectionString;
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = this.CommandType == null ? System.Data.CommandType.Text : (System.Data.CommandType)Utility.ConvertTo(this.CommandType.GetTextValue(), typeof(System.Data.CommandType));
dbCommand.CommandText = this.CommandText.GetTextValue();
if (this.Parameters.Count > 0)
{
string format = this.ParameterFormat == null ? "@p{0}" : this.ParameterFormat.GetTextValue();
List <object> expParams = new List <object>();
for (int i = 0; i < this.Parameters.Count; i++)
{
IExpression exp = this.Parameters[i];
DbParameter dbParameter = dbFactory.CreateParameter();
object value = exp.GetValue();
dbParameter.ParameterName = string.IsNullOrEmpty(format) ? "?" : string.Format(format, i);
dbParameter.DbType = Utility.GetObjectDbType(value);
dbParameter.Value = value;
dbCommand.Parameters.Add(dbParameter);
}
}
using (DbDataAdapter dbAdapter = dbFactory.CreateDataAdapter())
{
dbAdapter.SelectCommand = dbCommand;
DataTable table = new DataTable();
dbAdapter.Fill(table);
if (this.RowIndex != null)
{
//只获取其中的某行数据
int row = Utility.ConverToInt32(this.RowIndex.GetTextValue());
if (table.Rows.Count > row)
{
result = table.Rows[row];
}
}
else
{
result = table;
}
}
}
}
return(result);
}
static void testFactory(DbProviderFactory factory)
{
StringBuilder sb;
int i = 0;
sb = new System.Text.StringBuilder();
try {
//factory.
var dbcsb = factory.CreateConnectionStringBuilder();
sb.Append(factory.GetType().Name + ": Keys = ");
foreach (string akey in dbcsb.Keys)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(akey);
//dbcsb.Add(akey, "my_" + akey);
i++;
}
if (i > 0)
{
sb.AppendLine();
Trace.WriteLine(sb.ToString());
Trace.WriteLine("Sample connection-string: " + dbcsb.ConnectionString);
//Logger.log(sb.ToString());
}
else
{
Trace.WriteLine("no keys");
}
if (factory.GetType().Equals(typeof(System.Data.SqlClient.SqlClientFactory)))
{
dbcsb.Clear();
dbcsb.Add("User ID", "operator");
dbcsb.Add("Password", "operator");
dbcsb.Add("Application Name", Assembly.GetEntryAssembly().GetName().Name);
dbcsb.Add("Workstation ID", Environment.MachineName);
dbcsb.Add("Data Source", "colt-sql");
dbcsb.Add("Initial Catalog", "checkweigh_data_dev");
dbcsb.Add("Persist Security Info", true);
dbcsb.Add("Integrated Security", true);
Trace.WriteLine("her");
}
testConnection(factory, dbcsb.ConnectionString);
var x1 = factory.CreateCommand();
//x1.
var x2 = factory.CreateCommandBuilder();
var x4 = factory.CreateConnectionStringBuilder();
var x5 = factory.CreateDataAdapter();
if (factory.CanCreateDataSourceEnumerator)
{
var x6 = factory.CreateDataSourceEnumerator();
Trace.WriteLine("do-enumerator here");
}
var x7 = factory.CreateParameter();
} catch (Exception ex) {
// Set the connection to null if it was created.
//if (connection != null) {
// connection = null;
//}
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
/// <param name="connection">The database connection</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure, DbConnection connection)
{
//if we can't find the factory, we can't use the data adapter
if (_factory == null)
{
return;
}
var executionName = procedure.Name;
if (!string.IsNullOrEmpty(procedure.Package))
{
executionName = procedure.Package + "." + procedure.Name;
}
//for Oracle, sprocs with REF CURSORs indicate it returns something.
if (_isOracle && !procedure.Arguments.Any(a => a.DatabaseDataType == "REF CURSOR"))
{
return;
}
using (var resultSet = new DataSet {
Locale = CultureInfo.InvariantCulture
})
{
using (var command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = executionName;
command.CommandTimeout = 5;
command.CommandType = CommandType.StoredProcedure;
AddParameters(procedure, command);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var tx = connection.BeginTransaction())
{
command.Transaction = tx;
using (DbDataAdapter adapter = _factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
try
{
adapter.FillSchema(resultSet, SchemaType.Source);
}
catch (DbException exception)
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
catch (Exception exception) //for exceptions that don't derive from DbException
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
}
tx.Rollback();
}
}
UpdateProcedure(procedure, resultSet);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Contacts.LBL_LIST_FORM_TITLE"));
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
using (IDbCommand cmd = con.CreateCommand())
{
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dtCombined = new DataTable())
{
// 12/19/2006 Paul. As much as we would like to combine the threee separate queries into
// a single query using a union, we cannot because the Security.Filter rules must be applied separately.
// We simply combine three DataTables as quickly and efficiently as possible.
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Contacts' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwCONTACTS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Contacts", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwCONTACTS_EmailList", Sql.ClientScriptBlock(cmd));
}
da.Fill(dtCombined);
cmd.Parameters.Clear();
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Leads' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwLEADS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Leads", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwLEADS_EmailList", Sql.ClientScriptBlock(cmd));
}
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
DataRow rowNew = dtCombined.NewRow();
//rowNew.ItemArray = row.ItemArray;
// 12/19/2006 Paul. Using the ItemArray would certainly be faster,
// but someone may accidentally modify one of the columns of the three views,
// so we shall be safe and check each column before setting its value.
foreach (DataColumn col in dt.Columns)
{
if (dtCombined.Columns.Contains(col.ColumnName))
{
rowNew[col.ColumnName] = row[col.ColumnName];
}
}
dtCombined.Rows.Add(rowNew);
}
}
cmd.Parameters.Clear();
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Prospects' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwPROSPECTS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Prospects", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwPROSPECTS_EmailList", Sql.ClientScriptBlock(cmd));
}
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
DataRow rowNew = dtCombined.NewRow();
//rowNew.ItemArray = row.ItemArray;
// 12/19/2006 Paul. Using the ItemArray would certainly be faster,
// but someone may accidentally modify one of the columns of the three views,
// so we shall be safe and check each column before setting its value.
foreach (DataColumn col in dt.Columns)
{
if (dtCombined.Columns.Contains(col.ColumnName))
{
rowNew[col.ColumnName] = row[col.ColumnName];
}
}
dtCombined.Rows.Add(rowNew);
}
}
vwMain = dtCombined.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
Page.DataBind();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwACCOUNTS_ACTIVITIES " + ControlChars.CrLf
+ " where ACCOUNT_ID = @ACCOUNT_ID " + ControlChars.CrLf
+ " order by DATE_DUE desc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ACCOUNT_ID", gID);
#if DEBUG
Page.RegisterClientScriptBlock("vwACCOUNTS_ACTIVITIES", Sql.ClientScriptBlock(cmd));
#endif
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 11/26/2005 Paul. Convert the term here so that sorting will apply.
foreach (DataRow row in dt.Rows)
{
// 11/26/2005 Paul. Status is translated differently for each type.
switch (Sql.ToString(row["ACTIVITY_TYPE"]))
{
// 07/15/2006 Paul. Translation of Call status remains here because it is more complex than the standard list translation.
case "Calls": row["STATUS"] = L10n.Term(".call_direction_dom.", row["DIRECTION"]) + " " + L10n.Term(".call_status_dom.", row["STATUS"]); break;
//case "Meetings": row["STATUS"] = L10n.Term("Meeting") + " " + L10n.Term(".meeting_status_dom.", row["STATUS"]); break;
//case "Tasks" : row["STATUS"] = L10n.Term("Task" ) + " " + L10n.Term(".task_status_dom." , row["STATUS"]); break;
}
}
vwOpen = new DataView(dt);
vwOpen.RowFilter = "IS_OPEN = 1";
grdOpen.DataSource = vwOpen;
vwHistory = new DataView(dt);
vwHistory.RowFilter = "IS_OPEN = 0";
grdHistory.DataSource = vwHistory;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdOpen.SortColumn = "DATE_DUE";
grdOpen.SortOrder = "desc";
grdOpen.ApplySort();
grdOpen.DataBind();
grdHistory.SortColumn = "DATE_MODIFIED";
grdHistory.SortOrder = "desc";
grdHistory.ApplySort();
grdHistory.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
/// <summary>
/// Update.
/// </summary>
/// <param name="database"></param>
/// <param name="tables"></param>
/// <param name="tableNames"></param>
/// <param name="identity"></param>
/// <param name="transaction"></param>
private static void Update(Database database, DataTable[] tables, string[] tableNames, string identity, DbTransaction transaction)
{
#region Validations
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (tables == null)
{
throw new ArgumentNullException("ds");
}
if (tableNames == null)
{
throw new ArgumentNullException("tableNames");
}
if (tables.Length != tableNames.Length)
{
throw new ArgumentException("tables and tableNames parameters cannot differ in size");
}
#endregion
// retrieve connection
string cnName = GetDbFactory(database);
// retrieve connection
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[cnName];
DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);
// create adapter
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
// create commands
for (int i = 0; i < tableNames.Length; ++i)
{
using (DbCommand select = factory.CreateCommand())
{
string tableName = tableNames[i];
select.CommandText = string.Format("SELECT * FROM {0}", tableName);
select.Connection = transaction.Connection;
select.Transaction = transaction;
using (DbCommandBuilder builder = factory.CreateCommandBuilder())
{
// get table
DataTable table = tables[i];
adapter.SelectCommand = select;
builder.DataAdapter = adapter;
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
if (!string.IsNullOrEmpty(identity))
{
GenerateIdentityUpdate(factory, table, adapter, identity);
}
// update
adapter.Update(table);
}
}
}
}
}
/// <summary>
/// Executes the <paramref name = "sql" /> query.
/// </summary>
/// <param name = "sql">The SQL to execute.</param>
public void ExecuteQuery(string sql)
{
ValidateState();
DbConnection dbConnection = null;
DbDataAdapter adapter = null;
_command = null;
Query query;
// In the case of connection errors the error messages were getting lost, provide a default batch object:
Batch = new QueryBatch(sql);
try
{
IsBusy = true;
dbConnection = _factory.CreateConnection();
dbConnection.ConnectionString = _connectionString;
dbConnection.Open();
Messages = string.Empty;
SubscribeToMessages(dbConnection);
if (_enableQueryBatching)
{
Batch = QueryBatch.Parse(sql);
}
else
{
Batch = new QueryBatch(sql);
}
Batch.StartTime = DateTime.Now;
adapter = _factory.CreateDataAdapter();
_command = dbConnection.CreateCommand();
_command.CommandType = CommandType.Text;
SetCommandTimeout(_command, _commandTimeout);
adapter.SelectCommand = _command;
int queryCount = Batch.Queries.Count;
for (int i = 0; i < queryCount; i++)
{
query = Batch.Queries[i];
_command.CommandText = query.Sql;
query.Result = new DataSet("Batch " + (i + 1));
query.StartTime = DateTime.Now;
adapter.Fill(query.Result);
query.EndTime = DateTime.Now;
OnBatchProgress(new BatchProgressEventArgs(query, queryCount, i + 1));
}
}
catch (DbException dbException)
{
HandleBatchException(dbException);
}
finally
{
if (Batch != null)
{
Batch.EndTime = DateTime.Now;
}
if (adapter != null)
{
adapter.Dispose();
}
if (_command != null)
{
_command.Dispose();
}
IsBusy = false;
UnsubscribeFromMessages(dbConnection);
}
if (Batch != null)
{
Batch.Messages = Messages;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
nACLACCESS_Export = Security.GetUserAccess(m_sMODULE, "export");
try
{
sMODULE_NAME = Sql.ToString(Request["MODULE_NAME"]);
ctlListHeaderMySaved.Title = ".saved_reports_dom." + sMODULE_NAME;
ctlListHeaderPublished.Title = ".published_reports_dom." + sMODULE_NAME;
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwREPORTS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AppendParameter(cmd, sMODULE_NAME, "MODULE_NAME");
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 06/18/2006 Paul. Translate the report type.
foreach (DataRow row in dt.Rows)
{
row["REPORT_TYPE"] = L10n.Term(".dom_report_types.", row["REPORT_TYPE"]);
}
vwMySaved = new DataView(dt);
vwMySaved.RowFilter = "PUBLISHED = 0 and ASSIGNED_USER_ID = '" + Security.USER_ID.ToString() + "'";
grdMySaved.DataSource = vwMySaved;
if (!IsPostBack)
{
grdMySaved.SortColumn = "NAME";
grdMySaved.SortOrder = "asc";
grdMySaved.ApplySort();
grdMySaved.DataBind();
}
vwPublished = new DataView(dt);
// 05/18/2006 Paul. Lets include unassigned so that they don't get lost.
vwPublished.RowFilter = "PUBLISHED = 1 or ASSIGNED_USER_ID is null";
grdPublished.DataSource = vwPublished;
if (!IsPostBack)
{
grdPublished.SortColumn = "NAME";
grdPublished.SortOrder = "asc";
grdPublished.ApplySort();
grdPublished.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
this.ValidateEditViewFields(m_sMODULE + ".EditView");
string sCATEGORY = new DynamicControl(this, "CATEGORY").Text;
if (Page.IsValid && !Sql.IsEmptyString(sCATEGORY))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
DataRow rowCurrent = null;
DataTable dtCurrent = new DataTable();
if (!Sql.IsEmptyGuid(gID))
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwFORUMS_Edit" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, m_sMODULE, "edit");
Sql.AppendParameter(cmd, gID, "ID", false);
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtCurrent);
if (dtCurrent.Rows.Count > 0)
{
rowCurrent = dtCurrent.Rows[0];
}
else
{
// 11/19/2007 Paul. If the record is not found, clear the ID so that the record cannot be updated.
// It is possible that the record exists, but that ACL rules prevent it from being selected.
gID = Guid.Empty;
}
}
}
}
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
// 12/29/2007 Paul. TEAM_ID is now in the stored procedure.
SqlProcs.spFORUMS_Update
(ref gID
, new DynamicControl(this, "TITLE").Text
, new DynamicControl(this, "CATEGORY").Text
, new DynamicControl(this, "DESCRIPTION").Text
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Terminology.LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = SplendidCRM.Security.IS_ADMIN;
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwTERMINOLOGY_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 01/12/2006 Paul. ctlSearch.LANGUAGE is not working.
if (!IsPostBack)
{
Sql.AppendParameter(cmd, L10N.NormalizeCulture(L10n.NAME), 10, Sql.SqlFilterMode.Exact, "LANG"); //ctlSearch.LANGUAGE = L10n.NAME;
}
else
{
ctlSearch.SqlSearchClause(cmd);
}
if (ctlSearch.GLOBAL_TERMS)
{
cmd.CommandText += " and MODULE_NAME is null" + ControlChars.CrLf;
}
if (!ctlSearch.INCLUDE_LISTS)
{
cmd.CommandText += " and LIST_NAME is null" + ControlChars.CrLf;
}
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
try
{
Guid gID = Sql.ToGuid(Request["ID"]);
// 11/28/2005 Paul. We must always populate the table, otherwise it will disappear during event processing.
//if ( !IsPostBack )
{
if (!Sql.IsEmptyGuid(gID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwTHREADS_POSTS" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/27/2006 Paul. Make sure to filter relationship data based on team access rights.
Security.Filter(cmd, m_sMODULE, "list");
cmd.CommandText += " and THREAD_ID = @THREAD_ID" + ControlChars.CrLf;
cmd.CommandText += " order by DATE_ENTERED asc " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@THREAD_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwTHREADS_POSTS", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow rdr in dt.Rows)
{
PostView ctlPost = LoadControl("PostView.ascx") as PostView;
plcPosts.Controls.Add(ctlPost);
ctlPost.POST_ID = Sql.ToGuid(rdr["ID"]);
ctlPost.TITLE = Sql.ToString(rdr["TITLE"]);
ctlPost.CREATED_BY = Sql.ToString(rdr["CREATED_BY"]);
ctlPost.DATE_ENTERED = Sql.ToString(rdr["DATE_ENTERED"]);
ctlPost.MODIFIED_BY = Sql.ToString(rdr["MODIFIED_BY"]);
ctlPost.DATE_MODIFIED = Sql.ToString(rdr["DATE_MODIFIED"]);
ctlPost.DESCRIPTION = Sql.ToString(rdr["DESCRIPTION_HTML"]);
if (Sql.ToDateTime(rdr["DATE_ENTERED"]) != Sql.ToDateTime(rdr["DATE_MODIFIED"]))
{
ctlPost.Modified = true;
}
Guid gCREATED_BY_ID = Sql.ToGuid(rdr["CREATED_BY_ID"]);
ctlPost.ShowEdit = Security.IS_ADMIN || gCREATED_BY_ID == Security.USER_ID;
ctlPost.ShowDelete = Security.IS_ADMIN || gCREATED_BY_ID == Security.USER_ID;
}
}
}
}
}
}
}
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTACTS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
int nACLACCESS = Security.GetUserAccess(m_sMODULE, "list");
if (nACLACCESS == ACL_ACCESS.OWNER)
{
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
}
ctlSearch.SqlSearchClause(cmd);
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
if (this.IsMobile && grdMain.Columns.Count > 0)
{
grdMain.Columns[0].Visible = false;
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwQUOTES_List " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, m_sMODULE, "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
public static TableQueryResponseModel PageQueryTable(TableQueryCriteria queryCriteria, TableQueryResponseModel queryResult)
{
if (queryCriteria.PageNumber < 1)
{
queryCriteria.PageNumber = 1;
}
queryResult.PageNumber = queryCriteria.PageNumber;
queryResult.PageSize = ConfigHelper.PageSize;
var countSql = CountSqlBuilder(queryCriteria);
var pageSql = PageSqlBuilder(queryCriteria);
#region query code
#region db server
string serverName = queryCriteria.DBServer;
string dbName = queryCriteria.DBName;
var dbConnList = ConfigHelper.GetConnectionStrings();
if (dbConnList == null || !dbConnList.ContainsKey(serverName))
{
throw new Exception("connectionStrings不存在的数据库服务器名称:" + serverName);
}
ConnectionStringSettings connSettings = dbConnList[serverName];
SqlConnectionStringBuilder cnBuilder = new SqlConnectionStringBuilder(connSettings.ConnectionString);
if (cnBuilder.InitialCatalog != dbName)
{
cnBuilder.InitialCatalog = dbName;
}
#endregion
DbProviderFactory factory = null;
DbConnection conn = null;
DbCommand command = null;
try
{
factory = DbProviderFactories.GetFactory(connSettings.ProviderName);
conn = factory.CreateConnection();
conn.ConnectionString = cnBuilder.ConnectionString;
conn.Open();
command = conn.CreateCommand();
command.CommandType = CommandType.Text;
#region read table
//读取总记录数
command.CommandText = countSql;
queryResult.TotalCount = (int)command.ExecuteScalar();
//分页读取
command.CommandText = pageSql;
using (DbDataAdapter reader = factory.CreateDataAdapter())
{
reader.SelectCommand = command;
DataSet ds = new DataSet();
reader.Fill(ds);
reader.Dispose();
queryResult.Result = ds.Tables[0];
}
//移除rowNumber列
queryResult.Result.Columns.Remove("rowNumber");
#endregion
}
finally
{
#region dispose
if (command != null)
{
command.Dispose();
command = null;
}
if (conn != null)
{
conn.Close();
conn = null;
}
#endregion
}
#endregion
return(queryResult);
}
public List <T> ExecuteList <T>(string sql, IDataMapper <T> rowMapper,
int index, int size)
{
if (string.IsNullOrEmpty(sql))
{
throw new Exception("Sql is empty");
}
if (rowMapper == null)
{
throw new Exception("Mapper is null");
}
List <T> list = new List <T>();
IDataReader rdr = null;
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
dataSet = new DataSet();
da.SelectCommand = cmd;
da.Fill(dataSet, index, size, typeof(T).ToString());
rdr = dataSet.Tables[typeof(T).ToString()].CreateDataReader();
while (rdr.Read())
{
list.Add(rowMapper.Map(rdr));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
}
return(list);
}
/// <summary>
/// Returns a datatable with the tuples from the SQL statement
/// The sql will be resolved into pieces, in order to call the Skyline algorithms without MSSQL CLR
/// </summary>
/// <param name="strPrefSQL"></param>
/// <param name="strategy"></param>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetResults(String strPrefSQL, SkylineStrategy strategy, PrefSQLModel model, bool ShowInternalAttributes)
{
DataTable dt = new DataTable();
//Default Parameter
string strQuery = "";
string strOperators = "";
int numberOfRecords = 0;
string[] parameter = null;
//Native SQL algorithm is already a valid SQL statement
//Trim prefSQL because of queries starting wit empty characters " SELECT ...."
if (strPrefSQL.Trim().StartsWith("SELECT", true, null))
{
if (model == null || !model.HasSkylineSample)
{
//If query doesn't need skyline calculation (i.e. query without preference clause) --> set algorithm to nativeSQL
strategy = new SkylineSQL();
}
else
{
throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support
}
}
else
{
//Determine parameter only with skyline of clause and not with weihtedsum clause
DetermineParameters(strPrefSQL, out parameter, out strQuery, out strOperators, out numberOfRecords);
}
try
{
if (model != null && model.Ranking.Count > 0)
{
SPRanking ranking = new SPRanking();
ranking.Provider = DriverString;
ranking.ConnectionString = ConnectionString;
string strSelectExtremas = "";
string strRankingWeights = "";
string strRankingExpressions = "";
string strColumnNames = "";
// Set the decimal seperator, because prefSQL double values are always with decimal separator "."
NumberFormatInfo format = new NumberFormatInfo();
format.NumberDecimalSeparator = ".";
foreach (RankingModel rankingModel in model.Ranking)
{
strSelectExtremas += rankingModel.SelectExtrema + ";";
strRankingWeights += rankingModel.Weight.ToString(format) + ";";
strRankingExpressions += rankingModel.Expression + ";";
strColumnNames += rankingModel.FullColumnName.Replace(".", "_") + ";";
}
strSelectExtremas = strSelectExtremas.TrimEnd(';');
strRankingWeights = strRankingWeights.TrimEnd(';');
strRankingExpressions = strRankingExpressions.TrimEnd(';');
dt = ranking.GetRankingTable(strQuery, strSelectExtremas, strRankingWeights, strRankingExpressions, ShowInternalAttributes, strColumnNames);
}
else if (strategy.IsNative())
{
if (model == null || !model.HasSkylineSample)
{
//Native SQL
//Generic database provider
//Create the provider factory from the namespace provider, you could create any other provider factory.. for Oracle, MySql, etc...
DbProviderFactory factory = DbProviderFactories.GetFactory(DriverString);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object, in this case, SqlConnection ...
if (connection != null)
{
connection.ConnectionString = ConnectionString;
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = strPrefSQL;
DbDataAdapter db = factory.CreateDataAdapter();
if (db != null)
{
db.SelectCommand = command;
db.Fill(dt);
}
}
}
else
{
throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support
}
}
else
{
if (strategy.SupportImplicitPreference() == false && model.ContainsOpenPreference)
{
throw new Exception(strategy.GetType() + " does not support implicit preferences!");
}
if (strategy.SupportIncomparable() == false && model.WithIncomparable)
{
throw new Exception(strategy.GetType() + " does not support incomparale tuples");
}
//Set the database provider
strategy.Provider = DriverString;
strategy.ConnectionString = ConnectionString;
strategy.Cardinality = Cardinality;
strategy.WindowHandling = WindowHandling;
strategy.RecordAmountLimit = numberOfRecords;
strategy.HasIncomparablePreferences = model.WithIncomparable;
strategy.AdditionParameters = parameter;
strategy.SortType = (int)model.Ordering;
if (!model.HasSkylineSample)
{
dt = strategy.GetSkylineTable(strQuery, strOperators);
TimeInMilliseconds = strategy.TimeMilliseconds;
NumberOfComparisons = strategy.NumberOfComparisons;
NumberOfMoves = strategy.NumberOfMoves;
}
else
{
var skylineSample = new SkylineSampling
{
SubsetCount = model.SkylineSampleCount,
SubsetDimension = model.SkylineSampleDimension,
SelectedStrategy = strategy
};
dt = skylineSample.GetSkylineTable(strQuery, strOperators);
TimeInMilliseconds = skylineSample.TimeMilliseconds;
//NumberOfOperations = skylineSample.NumberOfOperations;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
throw;
}
return(dt);
}
