public List <T> ExecuteList <T>(string sql, IDataMapper <T> rowMapper,
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();
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)
{
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
logger.Write(Severity.Error, ex.ToString());
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>
/// Load complete model information
/// </summary>
/// <param name="modelname"></param>
internal void LoadCompleteModelInformation()
{
if (string.IsNullOrEmpty(this.Model_Name))
{
return;
}
IDbConnection cnn = DatabaceFactory.GetPISConnection();
IDbCommand cmd = null;
IDbDataParameter dp = null;
DbDataAdapter da = null;
StringBuilder sql = new StringBuilder();
//Frank 其它語系待處理
sql.Clear();
sql.AppendLine(this.GetModelSQL());
//Model in different Langs
sql.AppendLine(";");
sql.AppendLine(this.GetModel_LangSQL());
//Model literatures
sql.AppendLine(";");
sql.AppendLine(this.GetLiteraturesSQL());
//Model Features
sql.AppendLine(";");
sql.AppendLine(this.GetFeaturesSQL());
//Model Specs
sql.AppendLine(";");
sql.AppendLine(this.GetModelSpecsSQL());
cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, cnn);
dp = cmd.CreateParameter();
dp.ParameterName = "model_name";
dp.Value = this.Model_Name;
cmd.Parameters.Add(dp);
da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataSet DsModel = new DataSet();
da.Fill(DsModel);
//=====================Model=========================================
if (DsModel.Tables[0] != null)
{
this.WriteToAttributeModel(DsModel.Tables[0]);
}
//=====================Model_Langs=========================================
//Frank 其它語系待處理
if (DsModel.Tables[1] != null)
{
this.WriteToAttributeModel_Lang(DsModel.Tables[1]);
}
//=====================Literatures=====================================
if (DsModel.Tables[2] != null)
{
this.WriteToAttributeLiteratures(DsModel.Tables[2]);
}
//======================Features==========================================
if (DsModel.Tables[3] != null)
{
this.WriteToAttributeFeatures(DsModel.Tables[3]);
}
//======================Features==========================================
if (DsModel.Tables[4] != null)
{
this.WriteToAttributeModelSpecs(DsModel.Tables[4]);
}
}
private void MigrateExistingData(Func <string, bool> shouldApplySql, DbConnection con, ICheckNotifier notifier, DiscoveredTable tbl)
{
string from = _colToNuke.GetRuntimeName(LoadStage.PostLoad);
string to = _newANOColumnInfo.GetRuntimeName(LoadStage.PostLoad);
//create an empty table for the anonymised data
using (DbCommand cmdCreateTempMap = DatabaseCommandHelper.GetCommand(
string.Format("SELECT top 0 {0},{1} into TempANOMap from {2}", from, to, tbl.GetFullyQualifiedName()),
con))
{
if (!shouldApplySql(cmdCreateTempMap.CommandText))
{
throw new Exception("User decided not to create the TempANOMap table");
}
cmdCreateTempMap.ExecuteNonQuery();
}
try
{
using (DataTable dt = new DataTable())
{
//get the existing data
using (DbCommand cmdGetExistingData =
DatabaseCommandHelper.GetCommand(
string.Format("SELECT {0},{1} from {2}", from, to, tbl.GetFullyQualifiedName()), con))
{
using (DbDataAdapter da = DatabaseCommandHelper.GetDataAdapter(cmdGetExistingData))
{
da.Fill(dt);//into memory
//transform it in memory
ANOTransformer transformer = new ANOTransformer(_toConformTo, new FromCheckNotifierToDataLoadEventListener(notifier));
transformer.Transform(dt, dt.Columns[0], dt.Columns[1]);
var tempAnoMapTbl = tbl.Database.ExpectTable("TempANOMap");
using (var insert = tempAnoMapTbl.BeginBulkInsert())
{
insert.Upload(dt);
}
}
}
}
//create an empty table for the anonymised data
using (DbCommand cmdUpdateMainTable = DatabaseCommandHelper.GetCommand(
string.Format(
"UPDATE source set source.{1} = map.{1} from {2} source join TempANOMap map on source.{0}=map.{0}",
from, to, tbl.GetFullyQualifiedName()), con))
{
if (!shouldApplySql(cmdUpdateMainTable.CommandText))
{
throw new Exception("User decided not to perform update on table");
}
cmdUpdateMainTable.ExecuteNonQuery();
}
}
finally
{
//always drop the temp anomap
using (DbCommand dropMappingTable = DatabaseCommandHelper.GetCommand("DROP TABLE TempANOMap", con))
dropMappingTable.ExecuteNonQuery();
}
}
//Get data to Users dataset tabke
public DataTable GetUsersTable()
{
_usersDataAdapter.Fill(_usersTable);
return(_usersTable);
}
/* 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;
}
}
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.spCONTRACT_TYPES_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 vwCONTRACT_TYPES_DOCUMENTS " + ControlChars.CrLf
+ " where CONTRACT_TYPE_ID = @CONTRACT_TYPE_ID" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@CONTRACT_TYPE_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwCONTRACT_TYPES_DOCUMENTS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
row["STATUS_ID"] = L10n.Term(".document_status_dom.", row["STATUS_ID"]);
}
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();
}
}
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();
}
}
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();
}
}
public void DataAdapter_TableMappings(DbDataAdapter dbDA)
{
Exception exp = null;
IDbDataAdapter Ida = (IDbDataAdapter)dbDA;
IDbCommand ICmd = Ida.SelectCommand;
IDbConnection IConn = ICmd.Connection;
IConn.ConnectionString = MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString;
IConn.Open();
ICmd.Transaction = IConn.BeginTransaction();
//--- Default value ---
try
{
BeginCase("TableMappings Default value");
Compare(dbDA.TableMappings.Count, 0);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
//init dataset
DataSet ds = new DataSet();
ds.Tables.Add("CustTable");
ds.Tables.Add("EmplTable");
//load the tables
dbDA.TableMappings.Add("Table", "CustTable");
ICmd.CommandText = "SELECT CustomerID, CompanyName, City, Country, Phone FROM Customers where CustomerID in ('GH100','GH200','GH300','GH400','GH500','GH600','GH700')";
dbDA.Fill(ds);
dbDA.TableMappings.Clear();
dbDA.TableMappings.Add("Table", "EmplTable");
ICmd.CommandText = " SELECT EmployeeID, LastName, FirstName, Title FROM Employees where EmployeeID > 0";
dbDA.Fill(ds);
try
{
BeginCase("TableMappings.Count");
Compare(ds.Tables.Count, 2);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Customers rows count");
Compare(ds.Tables["CustTable"].Rows.Count > 0, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Employees rows count");
Compare(ds.Tables["EmplTable"].Rows.Count > 0, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Employees Columns");
Compare(ds.Tables["EmplTable"].Columns.IndexOf("EmployeeID") >= 0, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Customer Columns");
Compare(ds.Tables["CustTable"].Columns.IndexOf("CustomerID") >= 0, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
//Another checking
if (ConnectedDataProvider.GetDbType() != DataBaseServer.DB2)
{
BeginCase("Check table mappings with stored procedure of another owner");
try
{
DataSet ds1 = new DataSet();
ds1.Tables.Add("EmplTable");
dbDA.TableMappings.Clear();
dbDA.TableMappings.Add("Table", "EmplTable");
ICmd.CommandType = CommandType.StoredProcedure;
switch (ConnectedDataProvider.GetDbType(ConnectedDataProvider.ConnectionString))
{
case MonoTests.System.Data.Utils.DataBaseServer.Oracle:
// On ORACLE, the Scheam is the user is the owner.
ICmd.CommandText = "GHTDB.GH_DUMMY";
break;
case MonoTests.System.Data.Utils.DataBaseServer.PostgreSQL:
ICmd.CommandText = "public.gh_dummy";
break;
default:
ICmd.CommandText = "mainsoft.GH_DUMMY";
break;
}
// investigate the SP parameters
OracleCommandBuilder.DeriveParameters((OracleCommand)ICmd);
Compare(2, ICmd.Parameters.Count);
// add one numeric parameter, and Fill
ICmd.Parameters.Clear();
ICmd.Parameters.Add(new OracleParameter("EmployeeIDPrm", 1));
((OracleCommand)ICmd).Parameters.Add(new OracleParameter("result", OracleType.Cursor)).Direction = ParameterDirection.Output;
dbDA.Fill(ds1);
Compare(ds1.Tables.Count, 1);
Compare(ds1.Tables[0].Rows.Count > 0, true);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
//
((IDbDataAdapter)dbDA).SelectCommand.Transaction.Commit();
//close connection
if (((IDbDataAdapter)dbDA).SelectCommand.Connection.State != ConnectionState.Closed)
{
((IDbDataAdapter)dbDA).SelectCommand.Connection.Close();
}
}
private void LoadGraph()
{
try
{
int timeout = 10000;
while (!IsHandleCreated && timeout > 0)
{
timeout -= 100;
Thread.Sleep(100);
if (timeout <= 0)
{
throw new TimeoutException(
"Window Handle was not created on AggregateGraph control after 10 seconds of calling LoadGraph!");
}
}
this.Invoke(new MethodInvoker(() =>
{
lblLoadStage.Visible = true;
lblLoadStage.Text = "Generating Query...";
}));
AggregateContinuousDateAxis axis = AggregateConfiguration.GetAxisIfAny();
AggregateBuilder builder = GetQueryBuilder(AggregateConfiguration);
UpdateQueryViewerScreenWithQuery(builder.SQL);
var countColumn = builder.SelectColumns.FirstOrDefault(c => c.IColumn is AggregateCountColumn);
var server =
AggregateConfiguration.Catalogue.GetDistinctLiveDatabaseServer(
DataAccessContext.InternalDataProcessing, true);
this.Invoke(new MethodInvoker(() => { lblLoadStage.Text = "Connecting To Server..."; }));
using (var con = server.GetConnection())
{
con.Open();
_cmd = server.GetCommand(builder.SQL, con);
_cmd.CommandTimeout = Timeout;
_dt = new DataTable();
this.Invoke(new MethodInvoker(() => { lblLoadStage.Text = "Executing Query..."; }));
DbDataAdapter adapter = server.GetDataAdapter(_cmd);
adapter.Fill(_dt);
_cmd = null;
//trim all leading/trailing whitespace from column
foreach (DataColumn c in _dt.Columns)
{
c.ColumnName = c.ColumnName.Trim();
}
if (_dt.Rows.Count == 0)
{
throw new Exception("Query Returned No Rows");
}
AggregateConfiguration.AdjustGraphDataTable(_dt);
//setup the heatmap if there is a pivot
if (_dt.Columns.Count > 2 && AggregateConfiguration.PivotOnDimensionID != null)
{
this.Invoke(new MethodInvoker(() => heatmapUI.SetDataTable(_dt)));
}
else
{
this.Invoke(new MethodInvoker(() => heatmapUI.Clear()));
}
if (GraphTableRetrieved != null)
{
GraphTableRetrieved(this, _dt);
}
if (_dt.Columns.Count < 2)
{
throw new NotSupportedException("Aggregates must have 2 columns at least");
}
//Invoke onto main UI thread so we can setup the chart
this.Invoke(new MethodInvoker(() =>
{
PopulateGraphResults(countColumn, axis);
Done = true;
}));
}
Invoke(new MethodInvoker(() => { lblLoadStage.Text = "Crashed"; }));
ShowHeatmapTab(heatmapUI.HasDataTable());
}
catch (Exception e)
{
//don't bother if it is closing / closed
if (IsDisposed)
{
return;
}
Crashed = true;
Exception = e;
ragSmiley1.SetVisible(true);
ragSmiley1.Fatal(e);
AbortLoadGraph();
SetToolbarButtonsEnabled(true);
Done = true;
}
}
override internal void LoadForTable()
{
IDbConnection cn = null;
try
{
string query = "select * from information_schema.columns where table_catalog = '" +
this.Table.Database.Name + "' and table_schema = '" + this.Table.Schema +
"' and table_name = '" + this.Table.Name + "' order by ordinal_position";
cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
DataTable metaData = new DataTable();
DbDataAdapter adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(metaData);
metaData.Columns["udt_name"].ColumnName = "TYPE_NAME";
metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE";
if (metaData.Columns.Contains("TYPE_NAME"))
{
f_TypeName = metaData.Columns["TYPE_NAME"];
}
if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
{
f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
}
PopulateArray(metaData);
// IsAutoKey logic
query = @"SELECT a.attname AS column_name, substring(pg_get_expr(ad.adbin, c.oid) " +
@"FROM '[\'""]+(.+?)[\'""]+') AS seq_name " +
"FROM pg_class c, pg_namespace n, pg_attribute a, pg_attrdef ad " +
"WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " +
"AND c.relnamespace = n.oid " +
"AND a.attrelid = c.oid AND a.atthasdef = true " +
"AND ad.adrelid = c.oid AND ad.adnum = a.attnum " +
@"AND pg_get_expr(ad.adbin, c.oid) LIKE 'nextval(%'";
DataTable seqData = new DataTable();
adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
adapter.Fill(seqData);
DataRowCollection rows = seqData.Rows;
if (rows.Count > 0)
{
string colName;
for (int i = 0; i < rows.Count; i++)
{
colName = rows[i]["column_name"] as string;
PostgreSQLColumn col = this[colName] as PostgreSQLColumn;
col._isAutoKey = true;
// col.AutoKeyText = col.Default.Replace("nextval", "currval").Replace("\"", "\"\"");
query = "SELECT min_value, increment_by FROM \"" + rows[i]["seq_name"] + "\"";
adapter = PostgreSQLDatabases.CreateAdapter(query, cn);
DataTable autokeyData = new DataTable();
adapter.Fill(autokeyData);
Int64 a;
a = (Int64)autokeyData.Rows[0]["min_value"];
col._autoInc = Convert.ToInt32(a);
a = (Int64)autokeyData.Rows[0]["increment_by"];
col._autoSeed = Convert.ToInt32(a);
}
}
cn.Close();
}
catch
{
if (cn != null)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
}
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 LoadTableDescriptions()
{
try
{
//string query = @"SELECT TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + this.Database.Name + "'";
string query = @"SELECT TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + this.Database.Name + "' AND Table_type = 'BASE TABLE'";
DataTable metaData = new DataTable();
DbDataAdapter adapter = MySqlDatabases.CreateAdapter(query, this.dbRoot.ConnectionString);
adapter.Fill(metaData);
if (this.Database.Tables.Count > 0)
{
Table t = this.Database.Tables[0] as Table;
if (!t._row.Table.Columns.Contains("DESCRIPTION"))
{
t._row.Table.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
this.f_Description = t._row.Table.Columns["DESCRIPTION"];
}
if (!t._row.Table.Columns.Contains("TABLE_SCHEMA"))
{
t._row.Table.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
this.f_Schema = t._row.Table.Columns["TABLE_SCHEMA"];
}
if (!t._row.Table.Columns.Contains("DATE_CREATED"))
{
t._row.Table.Columns.Add("DATE_CREATED", Type.GetType("System.DateTime"));
this.f_DateCreated = t._row.Table.Columns["DATE_CREATED"];
}
if (!t._row.Table.Columns.Contains("DATE_MODIFIED"))
{
t._row.Table.Columns.Add("DATE_MODIFIED", Type.GetType("System.DateTime"));
this.f_DateModified = t._row.Table.Columns["DATE_MODIFIED"];
}
}
if (metaData.Rows.Count > 0)
{
foreach (DataRow row in metaData.Rows)
{
Table t = this[row["TABLE_NAME"] as string] as Table;
t._row["DESCRIPTION"] = row["TABLE_COMMENT"] as string;
//t._row["TABLE_SCHEMA"] = this.Database.Name;
if (row["CREATE_TIME"] != DBNull.Value)
{
t._row["DATE_CREATED"] = (DateTime)row["CREATE_TIME"];
}
if (row["UPDATE_TIME"] != DBNull.Value)
{
t._row["DATE_MODIFIED"] = (DateTime)row["UPDATE_TIME"];
}
}
}
}
catch {}
}
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 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;
}
}
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();
}
}
public override bool Run()
{
DbConnection db = Get();
if (db == null)
{
return(false);
}
WriteInfo("Connecting ...");
using (db)
{
try { db.Open(); }
catch (Exception e)
{
WriteError(e.Message);
return(false);
}
using (DbCommand cmd = db.CreateCommand())
{
cmd.CommandText = Query;
if (IsQuery(Query))
{
WriteInfo("Detecting Query Type");
// Query
cmd.CommandType = CommandType.Text;
using (DataTable dt = new DataTable()
{
TableName = Query
})
{
using (DbDataAdapter adapter = DbProviderFactories.GetFactory(db).CreateDataAdapter())
{
adapter.SelectCommand = cmd;
adapter.Fill(dt);
}
CommandTable table = new CommandTable(dt);
switch (QueryOutFormat)
{
case EFormat.Console:
{
WriteTable(table);
break;
}
case EFormat.Txt:
{
File.WriteAllText(QueryOutFile.FullName, table.Output());
break;
}
case EFormat.Xml:
{
dt.WriteXml(QueryOutFile.FullName, XmlWriteMode.WriteSchema, true);
break;
}
}
if (QueryOutFormat != EFormat.Console)
{
QueryOutFile.Refresh();
WriteInfo("OutFile Size: ", QueryOutFile.Length.ToString(), QueryOutFile.Length <= 0 ? ConsoleColor.Red : ConsoleColor.Green);
}
WriteInfo("Rows : ", dt.Rows.Count.ToString(), dt.Rows.Count <= 0 ? ConsoleColor.Red : ConsoleColor.Green);
WriteInfo("Columns : ", dt.Columns.Count.ToString(), dt.Columns.Count <= 0 ? ConsoleColor.Red : ConsoleColor.Green);
}
}
else
{
// Non query
WriteInfo("Detecting Non-Query Type");
int x = cmd.ExecuteNonQuery();
WriteInfo("Affected rows", x.ToString(), x <= 0 ? ConsoleColor.Red : ConsoleColor.Green);
}
WriteInfo("Disconnecting");
db.Close();
}
}
return(true);
}
public DataTable getDataTable(String queryString, params dynamic[] args)
{
DataTable table = null;
queryString = queryString.Replace("\r", " ").Replace("\n", "").Replace("\t", " ");
Console.WriteLine("Select cmd: " + queryString);
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
dataSet = new DataSet();
if (dataAdapter == null)
{
if (this.tableName == null)
{
SetTableName(queryString);
}
tableStruct = RetrieveTableStructure(this.tableName);
if (tableStruct.Columns.Count == 0)
{
return(null);
}
dataAdapter = provider.CreateDataAdapter();
dataAdapter.SelectCommand = BuildSelectCommand(provider, con, queryString, args);;
dataAdapter.DeleteCommand = BuildDeleteCommand(provider, con);
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.InsertCommand = BuildInsertCommand(provider, con);
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.AcceptChangesDuringUpdate = true;
dataAdapter.ContinueUpdateOnError = false;
}
if (con.ConnectionString != dataAdapter.SelectCommand.Connection.ConnectionString)
{
dataAdapter.SelectCommand = BuildSelectCommand(provider, con, queryString, args);;
dataAdapter.DeleteCommand = BuildDeleteCommand(provider, con);
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.InsertCommand = BuildInsertCommand(provider, con);
}
if (args.Length > 0)
{
dataAdapter.SelectCommand.Parameters.Clear();
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
dataAdapter.SelectCommand.Parameters.Add(par);
}
}
try
{
con.Open();
//BuildTableMapping(dataAdapter);
dataAdapter.Fill(dataSet, this.tableName);
table = dataSet.Tables[this.tableName];
dataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
if (identityColumn != null)
{
if (table.Columns.Contains(identityColumn))
{
table.PrimaryKey = new DataColumn[] { table.Columns[identityColumn] };
table.Columns[identityColumn].AllowDBNull = true;
}
else
{
throw new Exception(String.Format("Tabela {0}, nu contine coloana Identity ={1}", tableName, identityColumn));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(table);
}
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 vwPROJECT_TASKS_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);
// 11/13/2005 Paul. Convert the term here so that sorting will apply.
// 11/26/2005 Paul. List conversion now occurs in the dynamic column of the grid.
/*
* foreach(DataRow row in dt.Rows)
* {
* row["STATUS"] = L10n.Term(".project_task_status_options.", row["STATUS"]);
* }
*/
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 List <SIListModel> FillSISSearchGrid(SIListModel model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <SIListModel> lstData = new List <SIListModel>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetServiceIssuePaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter paramC = cmd.CreateParameter();
paramC.ParameterName = "Criteria";
paramC.Value = model.Criteria;
cmd.Parameters.Add(paramC);
DbParameter paramPN = cmd.CreateParameter();
paramPN.ParameterName = "PageNumber";
paramPN.Value = model.PageNumber;
cmd.Parameters.Add(paramPN);
DbParameter paramNOR = cmd.CreateParameter();
paramNOR.ParameterName = "NumberOfRows";
paramNOR.Value = model.NumberOfRows;
cmd.Parameters.Add(paramNOR);
DbParameter param5 = cmd.CreateParameter();
param5.ParameterName = "SortBy";
param5.Value = model.SortBy;
cmd.Parameters.Add(param5);
DbParameter param6 = cmd.CreateParameter();
param6.ParameterName = "OrderBy";
param6.Value = model.OrderBy;
cmd.Parameters.Add(param6);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
SIListModel usm = new SIListModel();
usm.IssueID = int.Parse(dr["IssueID"].ToString());
usm.CausingIssueID = int.Parse(dr["CausingIssueID"].ToString());
usm.CausingIssue = dr["CausingIssue"].ToString();
usm.ServiceIssueID = int.Parse(dr["ServiceIssueID"].ToString());
usm.ServiceIssue = dr["ServiceIssue"].ToString();
usm.Issue = dr["Issue"].ToString();
usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString());
lstData.Add(usm);
}
db.Dispose();
return(lstData.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
//Get data to Messages dataset table
public DataTable GetMessagesTable()
{
_messagesDataAdapter.Fill(_messagesTable);
return(_messagesTable);
}
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();
}
}
/* this version uses the custom Tripous DataProvider classes */
private void button2_Click(object sender, EventArgs e)
{
// 1. Where clause preparation.
// ===============================================================
/* prepare a Params Dictionary to be passed to DbCommand.Parameters */
Dictionary <string, object> Params = new Dictionary <string, object>();
/* TradeDate
* Tripous DataProviders use the GlobalPrefix which defaults to : for all ADO.NET Data Providers. */
string Where = "where TRADE.TRADE_DATE >= :TradeDate " + Environment.NewLine;
Params.Add("TradeDate", edtDate.Value.ToString("yyyy-MM-dd"));
/* Customer */
if (!string.IsNullOrEmpty(edtCustomer.Text))
{
Where += " and TRADER.NAME like :Customer " + Environment.NewLine;
string Customer = edtCustomer.Text.Trim();
if (!Customer.EndsWith("%"))
{
Customer += "%";
}
Params.Add("Customer", Customer);
}
/* Total */
if (!string.IsNullOrEmpty(edtTotal.Text))
{
double Total = 0;
if (double.TryParse(edtTotal.Text, out Total))
{
Where += " and TRADE.TOTAL_VALUE >= :Total " + Environment.NewLine;
Params.Add("Total", Total);
}
}
/* displays the SELECT sql statement */
//MessageBox.Show(SelectSql + Where);
// 2. Command execution
// ===============================================================
DataTable table = new DataTable();
using (DbConnection Con = provider.CreateConnection(cs))
{
Con.ConnectionString = cs;
Con.Open();
/* creates the DbCommand, parses SQL, creates DbParameters, and assigns values */
DbCommand Cmd = provider.CreateCommand(Con, SelectSql + Where, Params);
using (DbDataAdapter adapter = provider.CreateAdapter())
{
adapter.SelectCommand = Cmd;
adapter.Fill(table);
}
}
Grid.DataSource = table;
/* the whole Command execution could be written in a single line as */
//Grid.DataSource = provider.Select(cs, SelectSql + Where, Params);
}
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);
}
/// <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;
}
}
/// <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);
}
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());
}
}
}
/* Demonstrates how to use selectable and non-selectable stored procedures */
private void btnExecStoreProc_Click(object sender, EventArgs e)
{
try
{
using (DbConnection Con = factory.CreateConnection())
{
Con.ConnectionString = cs;
Con.Open();
DbCommand Cmd = Con.CreateCommand();
/* by default CommandType is Text, that is any DDL or DML statement */
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = Con;
DbParameter Param;
/* AddNumbers is a NON-selectable stored proc that adds two numbers passed as parameters.
* The sum is returned as an output parameter.
* DbParameter.Direction property controls the direction (in/out/in-out) of a parameter */
Cmd.Parameters.Clear();
Cmd.CommandText = "AddNumbers";
Param = factory.CreateParameter();
Param.ParameterName = "@NumberA";
Param.Direction = ParameterDirection.Input;
Param.Value = 2;
Cmd.Parameters.Add(Param);
Param = factory.CreateParameter();
Param.ParameterName = "@NumberB";
Param.Direction = ParameterDirection.Input;
Param.Value = 3;
Cmd.Parameters.Add(Param);
Param = factory.CreateParameter();
Param.ParameterName = "@Result";
Param.Direction = ParameterDirection.Output;
Param.Value = 0;
Cmd.Parameters.Add(Param);
/* non-selectable stored procs are executed by using the DbCommand.ExecuteScalar() */
Cmd.ExecuteScalar();
/* read and display the output parameter after stored proc execution */
string ProcResult = Cmd.Parameters["@Result"].Value.ToString();
MessageBox.Show(ProcResult);
/* SelectTestTable is a selectable stored proc that accepts a single input parameter */
Cmd.Parameters.Clear();
Cmd.CommandText = "SelectTestTable";
Param = factory.CreateParameter();
Param.ParameterName = "@ID";
Param.Direction = ParameterDirection.Input;
Param.Value = 1;
Cmd.Parameters.Add(Param);
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = Cmd;
/* selectable stored procs are executed by using the DbDataAdapter.Fill() */
DataTable Table = new DataTable();
adapter.Fill(Table);
Grid.DataSource = Table;
Pager.SelectedTab = tabGrid;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>The query.</summary>
/// <param name="sql">The sql.</param>
/// <returns></returns>
public DataTable Query(string sql)
{
DbDataAdapter adapter = null;
DbCommand cmd = null;
DataTable dt = null;
QueryBatch batch = new QueryBatch();
Query query = new Query(sql);
if (string.IsNullOrEmpty(sql))
{
return(null);
}
if (_dataTables.ContainsKey(sql))
{
return(_dataTables[sql]);
}
try
{
if (_dbConnection == null || _dbConnection.State != ConnectionState.Open)
{
_dbConnection = Services.Settings.GetOpenConnection();
}
string dataSetName = sql;
query.Result = new DataSet(dataSetName);
batch.Clear();
batch.Add(query);
adapter = Services.Settings.ProviderFactory.CreateDataAdapter();
cmd = _dbConnection.CreateCommand();
cmd.CommandText = query.Sql;
cmd.CommandType = CommandType.Text;
adapter.SelectCommand = cmd;
adapter.Fill(query.Result);
}
// catch (Exception exp)
// {
// throw;
// }
finally
{
if (adapter != null)
{
adapter.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
if (query.Result.Tables.Count > 0)
{
dt = query.Result.Tables[0];
_dataTables[sql] = dt;
}
return(dt);
}
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);
}