protected override List<TableInfo> GetTables(DataConnection dataConnection)
{
CurrenSchema = dataConnection.Execute<string>("select current_schema from sysibm.sysdummy1");
var tables = ((DbConnection)dataConnection.Connection).GetSchema("Tables");
return
(
from t in tables.AsEnumerable()
where
new[] { "TABLE", "VIEW" }.Contains(t.Field<string>("TABLE_TYPE"))
let catalog = dataConnection.Connection.Database
let schema = t.Field<string>("TABLE_SCHEMA")
let name = t.Field<string>("TABLE_NAME")
where IncludedSchemas.Length != 0 || ExcludedSchemas.Length != 0 || schema == CurrenSchema
select new TableInfo
{
TableID = catalog + '.' + schema + '.' + name,
CatalogName = catalog,
SchemaName = schema,
TableName = name,
IsDefaultSchema = schema.IsNullOrEmpty(),
IsView = t.Field<string>("TABLE_TYPE") == "VIEW",
Description = t.Field<string>("REMARKS"),
}
).ToList();
}
protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
{
return
(
from pk in dataConnection.Query(
rd => new
{
id = dataConnection.Connection.Database + "." + rd.ToString(0) + "." + rd.ToString(1),
name = rd.ToString(2),
cols = rd.ToString(3).Split('+').Skip(1).ToArray(),
},@"
SELECT
TABSCHEMA,
TABNAME,
INDNAME,
COLNAMES
FROM
SYSCAT.INDEXES
WHERE
UNIQUERULE = 'P' AND " + GetSchemaFilter("TABSCHEMA"))
from col in pk.cols.Select((c,i) => new { c, i })
select new PrimaryKeyInfo
{
TableID = pk.id,
PrimaryKeyName = pk.name,
ColumnName = col.c,
Ordinal = col.i
}
).ToList();
}
protected override void Render(HtmlTextWriter writer)
{
string description = Page.Header.Description;
if (string.IsNullOrWhiteSpace(description) && SiteMap.CurrentNode != null)
{
description = SiteMap.CurrentNode.Description;
}
if (string.IsNullOrWhiteSpace(description))
{
using (DataConnection connection = new DataConnection())
{
description = connection.SitemapNavigator.CurrentPageNode.Description;
}
}
if (string.IsNullOrWhiteSpace(description)) return;
if (_element != null)
{
_element.Add(description);
string commonNs = string.Format(" xmlns=\"{0}\"", Namespaces.Xhtml );
string raw = _element.ToString().Replace(commonNs, "");
writer.Write(raw);
}
else
{
writer.WriteEncodedText(description);
}
}
protected override List<DataTypeInfo> GetDataTypes(DataConnection dataConnection)
{
var dts = base.GetDataTypes(dataConnection);
if (dts.All(dt => dt.ProviderDbType != 128))
{
dts.Add(new DataTypeInfo
{
TypeName = "image",
DataType = typeof(byte[]).FullName,
CreateFormat = "image({0})",
CreateParameters = "length",
ProviderDbType = 128
});
}
if (dts.All(dt => dt.ProviderDbType != 130))
{
dts.Add(new DataTypeInfo
{
TypeName = "text",
DataType = typeof(string).FullName,
CreateFormat = "text({0})",
CreateParameters = "length",
ProviderDbType = 130
});
}
return dts;
}
public void loadvisits(int studentID, DateTime minDate, DateTime maxDate)
{
min = minDate;
max = maxDate;
ID = studentID;
//clears the list box to enter new information
listBoxEditVisit.Items.Clear();
//TAB THIS
listBoxEditVisit.Items.Add("DATE".PadRight(15) + "\t" + "FIRST NAME".PadRight(30) + "\t" + "LAST NAME".PadRight(30) + "\t" + "ID".PadRight(12)+ "\t" + "TIME IN".PadRight(10)+"\t" + "TIME OUT".PadRight(10) + "\t" + "METHOD".PadRight(20) + "\t" + "TUTOR'S FIRST NAME".PadRight(30) + "TUTOR'S LAST NAME".PadRight(30)+ "\t" + "SUBJECT" + "\t" + "CATALOG" + "\t" + "SECTION");
//creates new dataconnection
DataConnection conn = new DataConnection();
SqlDataReader rd;
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'":"' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ","").Length !=8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + (int.Parse(rd["CLARION_ID"].ToString())).ToString("D8").PadRight(12) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + TIMEOUT.PadRight(10) + "\t" + rd["METHOD"].ToString().PadRight(20) + "\t" + rd["TUTORFIRSTNAME"].ToString().PadRight(30) + " " + rd["TUTORLASTNAME"].ToString().PadRight(30) + "\t" + rd["SUBJECT"].ToString().PadRight(5) + "\t" + ((rd["CATALOG"]).ToString()).PadRight(5) + "\t" + ((rd["SECTION"]).ToString()).PadRight(4));
}
}
rd.Close();
//closes connection
conn.Close();
if (studentID == 0)
{
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
// rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'" : "' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date, tutor_hour.time_out ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where tutor_hour.DATE<='" + maxDate + "' AND tutor_hour.DATE>='" + minDate+"' ");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ", "").Length != 8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + ("TUT" + int.Parse(rd["tutor_ID"].ToString()).ToString("D4").PadRight(10)) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + rd["TIME_OUT"].ToString().PadRight(10) + "\t" + "TUTOR");
}
}
rd.Close();
//closes connection
conn.Close();
}
}
/// <summary>
/// Returns a relative path a to a custom funtion call editor for the specified function.
/// </summary>
/// <param name="functionName">The function name</param>
/// <returns>A relative path to a custom call editor, or <value>null</value> if no custom editor was defined.</returns>
public static FunctionCallEditorSettings GetCustomEditorSettings(string functionName)
{
var settings = _customEditorSettings[functionName];
if (settings != null)
{
return settings;
}
using (var c = new DataConnection())
{
var mapping = c.Get<ICustomFunctionCallEditorMapping>().FirstOrDefault(e => e.FunctionName == functionName);
if (mapping != null)
{
return new FunctionCallEditorSettings
{
Url = UrlUtils.ResolvePublicUrl(mapping.CustomEditorPath),
Width = mapping.Width,
Height = mapping.Height
};
}
}
return null;
}
protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
{
return _primaryKeys = dataConnection.Query(
rd => new PrimaryKeyInfo
{
TableID = dataConnection.Connection.Database + "." + rd.ToString(0) + "." + rd.ToString(1),
PrimaryKeyName = rd.ToString(2),
ColumnName = rd.ToString(3),
Ordinal = Converter.ChangeTypeTo<int>(rd[4])
},@"
SELECT
col.TBCREATOR,
col.TBNAME,
idx.NAME,
col.NAME,
col.KEYSEQ
FROM
SYSIBM.SYSCOLUMNS col
JOIN SYSIBM.SYSINDEXES idx ON
col.TBCREATOR = idx.TBCREATOR AND
col.TBNAME = idx.TBNAME
WHERE
col.KEYSEQ > 0 AND idx.UNIQUERULE = 'P' AND " + GetSchemaFilter("col.TBCREATOR") + @"
ORDER BY
col.TBCREATOR, col.TBNAME, col.KEYSEQ")
.ToList();
}
protected override List<DataTypeInfo> GetDataTypes(DataConnection dataConnection)
{
return new List<DataTypeInfo>
{
new DataTypeInfo { TypeName = "XML", CreateParameters = null, DataType = "System.String" },
new DataTypeInfo { TypeName = "DECFLOAT", CreateParameters = "PRECISION", DataType = "System.Decimal" },
new DataTypeInfo { TypeName = "DBCLOB", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "CLOB", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "BLOB", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "LONG VARGRAPHIC", CreateParameters = null, DataType = "System.String" },
new DataTypeInfo { TypeName = "VARGRAPHIC", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "GRAPHIC", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "BIGINT", CreateParameters = null, DataType = "System.Int64" },
new DataTypeInfo { TypeName = "LONG VARCHAR FOR BIT DATA", CreateParameters = null, DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "VARCHAR () FOR BIT DATA", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "VARBIN", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "BINARY", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "CHAR () FOR BIT DATA", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
new DataTypeInfo { TypeName = "LONG VARCHAR", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "CHARACTER", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "CHAR", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "DECIMAL", CreateParameters = "PRECISION,SCALE", DataType = "System.Decimal" },
new DataTypeInfo { TypeName = "INTEGER", CreateParameters = null, DataType = "System.Int32" },
new DataTypeInfo { TypeName = "SMALLINT", CreateParameters = null, DataType = "System.Int16" },
new DataTypeInfo { TypeName = "REAL", CreateParameters = null, DataType = "System.Single" },
new DataTypeInfo { TypeName = "DOUBLE", CreateParameters = null, DataType = "System.Double" },
new DataTypeInfo { TypeName = "VARCHAR", CreateParameters = "LENGTH", DataType = "System.String" },
new DataTypeInfo { TypeName = "DATE", CreateParameters = null, DataType = "System.DateTime" },
new DataTypeInfo { TypeName = "TIME", CreateParameters = null, DataType = "System.TimeSpan" },
new DataTypeInfo { TypeName = "TIMESTAMP", CreateParameters = null, DataType = "System.DateTime" },
new DataTypeInfo { TypeName = "TIMESTMP", CreateParameters = null, DataType = "System.DateTime" },
new DataTypeInfo { TypeName = "ROWID", CreateParameters = "LENGTH", DataType = "System.Byte[]" },
};
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
var sql = @"
SELECT
TBCREATOR,
TBNAME,
NAME,
LENGTH,
SCALE,
NULLS,
CASE WHEN DEFAULT IN ('I', 'J') THEN 'Y' ELSE 'N' END,
COLNO,
COLTYPE,
REMARKS
FROM
SYSIBM.SYSCOLUMNS
WHERE
" + GetSchemaFilter("TBCREATOR");
return dataConnection.Query(
rd => new ColumnInfo
{
TableID = dataConnection.Connection.Database + "." + rd.GetString(0) + "." + rd.GetString(1),
Name = rd.ToString(2),
Length = Converter.ChangeTypeTo<int>(rd[3]),
Scale = Converter.ChangeTypeTo<int>(rd[4]),
IsNullable = rd.ToString(5) == "Y",
IsIdentity = rd.ToString(6) == "Y",
Ordinal = Converter.ChangeTypeTo<int>(rd[7]),
DataType = rd.ToString(8),
Description = rd.ToString(9),
},
sql).ToList();
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
var sql = @"
SELECT
table_catalog || '.' || table_schema || '.' || table_name as TableID,
column_name as Name,
is_nullable = 'YES' as IsNullable,
ordinal_position as Ordinal,
data_type as DataType,
character_maximum_length as Length,
COALESCE(numeric_precision, datetime_precision, interval_precision) as Precision,
numeric_scale as Scale,
is_identity = 'YES' OR COALESCE(column_default ~* 'nextval', false) as IsIdentity,
is_generated <> 'NEVER' as SkipOnInsert,
is_updatable = 'NO' as SkipOnUpdate
FROM
information_schema.columns";
if (ExcludedSchemas.Length == 0 || IncludedSchemas.Length == 0)
sql += @"
WHERE
table_schema NOT IN ('pg_catalog','information_schema')";
return dataConnection.Query<ColumnInfo>(sql).ToList();
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
var tcs = ((DbConnection)dataConnection.Connection).GetSchema("Columns");
return
(
from c in tcs.AsEnumerable()
join dt in DataTypes on c.Field<string>("COLUMN_DATA_TYPE") equals dt.TypeName
select new ColumnInfo
{
TableID = c.Field<string>("TABLE_CATALOG") + "." + c.Field<string>("TABLE_SCHEMA") + "." + c.Field<string>("TABLE_NAME"),
Name = c.Field<string>("COLUMN_NAME"),
DataType = dt.TypeName,
IsNullable = Converter.ChangeTypeTo<bool>(c["IS_NULLABLE"]),
Ordinal = Converter.ChangeTypeTo<int> (c["ORDINAL_POSITION"]),
Length = Converter.ChangeTypeTo<int> (c["COLUMN_SIZE"]),
Precision = Converter.ChangeTypeTo<int> (c["NUMERIC_PRECISION"]),
Scale = Converter.ChangeTypeTo<int> (c["NUMERIC_SCALE"]),
IsIdentity = false,
Description = c.Field<string>("DESCRIPTION"),
SkipOnInsert = Converter.ChangeTypeTo<bool>(c["IS_READONLY"]),
SkipOnUpdate = Converter.ChangeTypeTo<bool>(c["IS_READONLY"]),
}
).ToList();
}
public string GetEulaText(Guid eulaId, string userCulture)
{
using (DataConnection connection = new DataConnection())
{
return connection.Get<EULA>().Where(d => d.Id == eulaId).Select(d => d.Text).FirstOrDefault();
};
}
public string Rate(string score, string ratyId)
{
Guid ratyGuid = Guid.Parse(ratyId);
using (DataConnection conn = new DataConnection())
{
var ratyItem = conn.Get<Results>().Where(r => r.RatyId == ratyGuid).SingleOrDefault();
if (ratyItem != null)
{
ratyItem.Count += 1;
ratyItem.TotalValue = ratyItem.TotalValue + decimal.Parse(score, System.Globalization.CultureInfo.InvariantCulture);
conn.Update<Results>(ratyItem);
}
else
{
ratyItem = conn.CreateNew<Results>();
ratyItem.RatyId = ratyGuid;
ratyItem.Count = 1;
ratyItem.TotalValue = decimal.Parse(score, System.Globalization.CultureInfo.InvariantCulture);
conn.Add<Results>(ratyItem);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
var data = new { TotalValue = ratyItem.TotalValue, Count = ratyItem.Count };
SetCookie(ratyId);
return serializer.Serialize(data);
}
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
var cs = ((DbConnection)dataConnection.Connection).GetSchema("Columns");
return
(
from c in cs.AsEnumerable()
let tschema = c.Field<string>("TABLE_SCHEMA")
let schema = tschema == "sqlite_default_schema" ? "" : tschema
let dataType = c.Field<string>("DATA_TYPE")
select new ColumnInfo
{
TableID = c.Field<string>("TABLE_CATALOG") + "." + schema + "." + c.Field<string>("TABLE_NAME"),
Name = c.Field<string>("COLUMN_NAME"),
IsNullable = c.Field<bool> ("IS_NULLABLE"),
Ordinal = Converter.ChangeTypeTo<int>(c["ORDINAL_POSITION"]),
DataType = dataType,
Length = Converter.ChangeTypeTo<int>(c["CHARACTER_MAXIMUM_LENGTH"]),
Precision = Converter.ChangeTypeTo<int>(c["NUMERIC_PRECISION"]),
Scale = Converter.ChangeTypeTo<int>(c["NUMERIC_SCALE"]),
IsIdentity = c.Field<bool> ("AUTOINCREMENT"),
SkipOnInsert = dataType == "timestamp",
SkipOnUpdate = dataType == "timestamp",
}
).ToList();
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
var sql = @"
SELECT
TABSCHEMA,
TABNAME,
COLNAME,
LENGTH,
SCALE,
NULLS,
IDENTITY,
COLNO,
TYPENAME,
REMARKS
FROM
SYSCAT.COLUMNS
WHERE
" + GetSchemaFilter("TABSCHEMA");
return _columns = dataConnection.Query(
rd => new ColumnInfo
{
TableID = dataConnection.Connection.Database + "." + rd.GetString(0) + "." + rd.GetString(1),
Name = rd.ToString(2),
Length = Converter.ChangeTypeTo<long>(rd[3]),
Scale = Converter.ChangeTypeTo<int> (rd[4]),
IsNullable = rd.ToString(5) == "Y",
IsIdentity = rd.ToString(6) == "Y",
Ordinal = Converter.ChangeTypeTo<int>(rd[7]),
DataType = rd.ToString(8),
Description = rd.ToString(9),
},
sql).ToList();
}
protected override List<TableInfo> GetTables(DataConnection dataConnection)
{
var tables = ((DbConnection)dataConnection.Connection).GetSchema("Tables");
var views = ((DbConnection)dataConnection.Connection).GetSchema("Views");
return
(
from t in tables.AsEnumerable()
let catalog = t.Field<string>("TABLE_SCHEMA")
let name = t.Field<string>("TABLE_NAME")
select new TableInfo
{
TableID = catalog + ".." + name,
CatalogName = catalog,
SchemaName = "",
TableName = name,
IsDefaultSchema = true,
IsView = false,
}
).Concat(
from t in views.AsEnumerable()
let catalog = t.Field<string>("TABLE_SCHEMA")
let name = t.Field<string>("TABLE_NAME")
select new TableInfo
{
TableID = catalog + ".." + name,
CatalogName = catalog,
SchemaName = "",
TableName = name,
IsDefaultSchema = true,
IsView = true,
}
).ToList();
}
protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
{
var dbConnection = (DbConnection)dataConnection.Connection;
var pks = dbConnection.GetSchema("IndexColumns");
var idxs = dbConnection.GetSchema("Indexes");
return
(
from pk in pks. AsEnumerable()
join idx in idxs.AsEnumerable()
on
pk. Field<string>("INDEX_CATALOG") + "." +
pk. Field<string>("INDEX_SCHEMA") + "." +
pk. Field<string>("INDEX_NAME") + "." +
pk. Field<string>("TABLE_NAME")
equals
idx.Field<string>("INDEX_CATALOG") + "." +
idx.Field<string>("INDEX_SCHEMA") + "." +
idx.Field<string>("INDEX_NAME") + "." +
idx.Field<string>("TABLE_NAME")
where idx.Field<bool>("PRIMARY")
select new PrimaryKeyInfo
{
TableID = pk.Field<string>("INDEX_SCHEMA") + ".." + pk.Field<string>("TABLE_NAME"),
PrimaryKeyName = pk.Field<string>("INDEX_NAME"),
ColumnName = pk.Field<string>("COLUMN_NAME"),
Ordinal = pk.Field<int> ("ORDINAL_POSITION"),
}
).ToList();
}
protected override List<ColumnInfo> GetColumns(DataConnection dataConnection)
{
return dataConnection.Query<ColumnInfo>(@"
SELECT
TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID,
COLUMN_NAME as Name,
CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as IsNullable,
ORDINAL_POSITION as Ordinal,
c.DATA_TYPE as DataType,
CHARACTER_MAXIMUM_LENGTH as Length,
ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION) as [Precision],
NUMERIC_SCALE as Scale,
ISNULL(CONVERT(varchar(8000), x.Value), '') as [Description],
COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as IsIdentity,
CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as SkipOnInsert,
CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as SkipOnUpdate
FROM
INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN
sys.extended_properties x
ON
OBJECT_ID(TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME) = x.major_id AND
ORDINAL_POSITION = x.minor_id AND
x.name = 'MS_Description'")
.ToList();
}
protected override List<ForeingKeyInfo> GetForeignKeys(DataConnection dataConnection)
{
return dataConnection.Query<ForeingKeyInfo>(@"
SELECT
rc.CONSTRAINT_NAME as Name,
fk.TABLE_CATALOG + '.' + fk.TABLE_SCHEMA + '.' + fk.TABLE_NAME as ThisTableID,
fk.COLUMN_NAME as ThisColumn,
pk.TABLE_CATALOG + '.' + pk.TABLE_SCHEMA + '.' + pk.TABLE_NAME as OtherTableID,
pk.COLUMN_NAME as OtherColumn,
pk.ORDINAL_POSITION as Ordinal
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk
ON
fk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG AND
fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND
fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk
ON
pk.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG AND
pk.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA AND
pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
WHERE
fk.ORDINAL_POSITION = pk.ORDINAL_POSITION
ORDER BY
ThisTableID,
Ordinal")
.ToList();
}
///<Summary>Gets sum of all income-expenses for all previous years. asOfDate could be any date</Summary>
public static float RetainedEarningsAuto(object asOfDateObj)
{
DateTime asOfDate;
if(asOfDateObj.GetType()==typeof(string)) {
asOfDate=PIn.Date(asOfDateObj.ToString());
}
else if(asOfDateObj.GetType()==typeof(DateTime)) {
asOfDate=(DateTime)asOfDateObj;
}
else {
return 0;
}
DateTime firstOfYear=new DateTime(asOfDate.Year,1,1);
string command="SELECT SUM(CreditAmt), SUM(DebitAmt), AcctType "
+"FROM journalentry,account "
+"WHERE journalentry.AccountNum=account.AccountNum "
+"AND DateDisplayed < "+POut.Date(firstOfYear)
+" GROUP BY AcctType";
DataConnection dcon=new DataConnection();
DataTable table=dcon.GetTable(command);
float retVal=0;
for(int i=0;i<table.Rows.Count;i++) {
if(table.Rows[i][2].ToString()=="3"//income
|| table.Rows[i][2].ToString()=="4")//expense
{
retVal+=PIn.Float(table.Rows[i][0].ToString());//add credit
retVal-=PIn.Float(table.Rows[i][1].ToString());//subtract debit
//if it's an expense, we are subtracting (income-expense), but the signs cancel.
}
}
return retVal;
}
protected void Page_Load(object sender, EventArgs e)
{
var reportId = Guid.Parse(this.Request["id"].ToString());
var reportType = this.Request["type"].ToString();
var pageUrl = string.Empty;
using (DataConnection con = new DataConnection())
{
var report = con.Get<Composite.Forms.JotFormReport>().Where(r => r.Id == reportId).SingleOrDefault();
if (report != null)
{
switch (reportType)
{
case "grid": pageUrl = report.GridReportURL; break;
case "table": pageUrl = report.TableReportURL; break;
case "visual": pageUrl = report.VisualReportURL; break;
case "excel": pageUrl = report.ExcelReportURL; break;
case "csv": pageUrl = report.CsvReportURL; break;
default: break;
}
}
}
if (!string.IsNullOrWhiteSpace(pageUrl))
{
lPrintFrame.Text = string.Format(@"<iframe src=""{0}"" frameborder=""0"" style=""width:100%; height:100%; min-height:450px; border:none;"" ></iframe>", pageUrl);
}
else
{
lPrintFrame.Text = "<div style='padding: 20px'><h2>Report is not available</h2><p>The URL for this report has not been specified. To remedy this, edit this report and specify the JotForm Report URL.</p></div>";
}
}
/// <summary>
/// Constructor stores given connection into private variable
/// </summary>
/// <param name="connection"></param>
public DataConnectionWrapper(DataConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
connectionRef = connection;
}
public static string Pref(string prefName)
{
string command="SELECT ValueString FROM preference WHERE PrefName='"+POut.String(prefName)+"'";
DataConnection dcon=new DataConnection();
DataTable table=dcon.GetTable(command);
return table.Rows[0][0].ToString();
}
public override DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
{
DefaultSchema = dataConnection.Execute<string>("SELECT CURRENT_SCHEMA FROM DUMMY");
_databaseName = ((DbConnection)dataConnection.Connection).Database;
_dataSourceName = ((DbConnection) dataConnection.Connection).DataSource;
if (String.IsNullOrEmpty(_dataSourceName) || String.IsNullOrEmpty(_databaseName))
{
using (var reader = dataConnection.ExecuteReader(@"
SELECT
HOST,
KEY,
VALUE
FROM M_HOST_INFORMATION
WHERE KEY = 'sid'"))
{
if (reader.Reader.Read())
{
_dataSourceName = reader.Reader.GetString(0);
_databaseName = reader.Reader.GetString(2);
}
}
}
return base.GetSchema(dataConnection, options);
}
protected override List<PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection)
{
return dataConnection.Query(rd =>
{
var constraint = rd.IsDBNull(3) ? null : rd.GetString(3);
if (constraint != "PRIMARY KEY")
return null;
var schema = rd.GetString(0);
var tableName = rd.GetString(1);
var indexName = rd.GetString(2);
var columnName = rd.GetString(4);
var position = rd.GetInt32(5);
return new PrimaryKeyInfo
{
TableID = String.Concat(schema, '.', tableName),
ColumnName = columnName,
Ordinal = position,
PrimaryKeyName = indexName
};
}, @"
SELECT
SCHEMA_NAME,
TABLE_NAME,
INDEX_NAME,
CONSTRAINT,
COLUMN_NAME,
POSITION
FROM INDEX_COLUMNS")
.Where(x => x != null).ToList();
}
// Getting a product by id
public Product Get(Guid id)
{
using (var c = new DataConnection())
{
return c.Get<Product>().FirstOrDefault(p => p.Id == id);
}
}
public List<PackageDescriptor> GetPackageList(Guid InstallationId, string Culture)
{
using (DataConnection connection = new DataConnection())
{
return (
from package in connection.Get<Package>().AsEnumerable()
join media in connection.Get<IMediaFile>() on package.PackageFile equals media.KeyPath
select new PackageDescriptor()
{
Id = package.PackageId,
Name = package.Name,
GroupName = package.GroupName,
PackageVersion = package.PackageVersion,
MinCompositeVersionSupported = package.MinCompositeVersionSupported,
MaxCompositeVersionSupported = package.MaxCompositeVersionSupported,
Author = package.Author,
Description = package.Description,
TechicalDetails = package.TechnicalDetails,
EulaId = package.EULA,
ReadMoreUrl = package.ReadMoreUrl,
IsFree = true,
PackageFileDownloadUrl = new Uri(Context.Request.Url, UrlUtils.ResolvePublicUrl("Renderers/ShowMedia.ashx?id=" + media.Id)).OriginalString
}
).ToList();
}
}
protected override List<DataTypeInfo> GetDataTypes(DataConnection dataConnection)
{
var dts = ((DbConnection)dataConnection.Connection).GetSchema("DataTypes");
var dt = dts.AsEnumerable()
.Where(x=> x["ProviderDbType"] != DBNull.Value)
.Select(t => new DataTypeInfo
{
TypeName = t.Field<string>("TypeName"),
DataType = t.Field<string>("DataType"),
CreateFormat = t.Field<string>("CreateFormat"),
CreateParameters = t.Field<string>("CreateParameters"),
ProviderDbType = Converter.ChangeTypeTo<int>(t["ProviderDbType"]),
}).ToList();
return dt.GroupBy(x => new {x.TypeName, x.ProviderDbType}).Select(y =>
{
var x = y.First();
if (x.CreateFormat == null)
{
x.CreateFormat = x.TypeName;
if (x.CreateParameters != null)
{
x.CreateFormat += String.Concat('(',
String.Join(", ",
Enumerable.Range(0, x.CreateParameters.Split(',').Length)
.Select(i => String.Concat('{', i, '}'))),
')');
}
}
return x;
}).ToList();
}
// Getting the list of products
public IEnumerable<Product> Get()
{
using (var c = new DataConnection())
{
return c.Get<Product>().OrderBy(p => p.Title).ToList();
}
}
protected override List<ForeingKeyInfo> GetForeignKeys(DataConnection dataConnection)
{
const string baseSql = @"
SELECT
o.name as Name,
c.tableid as ThisTableID,
r.reftabid as OtherTableID,
COL_NAME(c.tableid, r.fokey{0}) as ThisColumn,
COL_NAME(r.reftabid, r.refkey{0}) as OtherColumn,
{0} as Ordinal
FROM
sysreferences r
JOIN sysconstraints c ON r.constrid = c.constrid
JOIN sysobjects o ON c.constrid = o.id
JOIN sysobjects o3 ON c.tableid = o3.id
LEFT JOIN sysobjects o2 ON r.reftabid = o2.id
JOIN sysreferences r2 ON r.constrid = r2.constrid
LEFT JOIN sysindexes i ON r2.indexid = i.indid AND r2.reftabid = i.id
WHERE
c.status = 64";
string sql = null;
for (var i = 1; i <= 16; i++)
{
if (sql != null)
sql += "\nUNION ALL";
sql += string.Format(baseSql, i);
}
sql = "SELECT * FROM (" + sql + ") as t WHERE ThisColumn IS NOT NULL";
return dataConnection.Query<ForeingKeyInfo>(sql).ToList();
}
static SQLiteTests()
{
LinqToDBForEFTools.Initialize();
DataConnection.TurnTraceSwitchOn();
}
protected override string PassValueSql(DataConnection dc) => "SELECT ID FROM {1} WHERE {0} = ?";
protected override string?PassNullSql(DataConnection dc, out int paramCount)
{
paramCount = 1;
return(null);
}
static InformixTools()
{
DataConnection.AddDataProvider(_informixDataProvider);
}
protected override List <ForeingKeyInfo> GetForeignKeys(DataConnection dataConnection)
{
var data = dataConnection.Query(
rd => new
{
name = rd[0],
thisTable = rd[1],
otherTable = rd[2],
thisColumns =
new[]
{
rd[3], rd[4], rd[5], rd[6], rd[7], rd[8], rd[9], rd[10], rd[11], rd[12], rd[13], rd[14], rd[15],
rd[16], rd[17], rd[18]
},
otherColumns =
new[]
{
rd[19], rd[20], rd[21], rd[22], rd[23], rd[24], rd[25], rd[26], rd[27], rd[28], rd[29], rd[30],
rd[31], rd[32], rd[33], rd[34]
}
}, @"
SELECT
pg_constraint.conname,
current_database() || '.' || this_schema.nspname || '.' || this_table.relname,
current_database() || '.' || other_schema.nspname || '.' || other_table.relname,
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[01]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[02]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[03]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[04]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[05]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[06]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[07]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[08]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[09]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[10]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[11]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[12]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[13]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[14]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[15]),
(select attname from pg_attribute where attrelid = pg_constraint.conrelid and attnum = pg_constraint.conkey[16]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[01]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[02]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[03]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[04]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[05]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[06]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[07]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[08]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[09]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[10]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[11]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[12]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[13]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[14]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[15]),
(select attname from pg_attribute where attrelid = pg_constraint.confrelid and attnum = pg_constraint.confkey[16])
FROM
pg_constraint
JOIN pg_class as this_table ON this_table.oid = pg_constraint.conrelid
JOIN pg_namespace as this_schema ON this_table.relnamespace = this_schema.oid
JOIN pg_class as other_table ON other_table.oid = pg_constraint.confrelid
JOIN pg_namespace as other_schema ON other_table.relnamespace = other_schema.oid
WHERE
pg_constraint.contype = 'f'")
.ToList();
return
((
from item in data
let name = Convert.ToString(item.name)
let thisTableID = Convert.ToString(item.thisTable)
let otherTableID = Convert.ToString(item.otherTable)
from col in item.thisColumns
.Zip(item.otherColumns, (thisColumn, otherColumn) => new { thisColumn, otherColumn })
.Select((cs, i) => new { cs.thisColumn, cs.otherColumn, ordinal = i })
where col.thisColumn != null && !(col.thisColumn is DBNull)
select new ForeingKeyInfo
{
Name = name,
ThisTableID = thisTableID,
OtherTableID = otherTableID,
ThisColumn = Convert.ToString(col.thisColumn),
OtherColumn = Convert.ToString(col.otherColumn),
Ordinal = col.ordinal
}
).ToList());
}
protected override void LoadProcedureTableSchema(DataConnection dataConnection, GetSchemaOptions options, ProcedureSchema procedure, string commandText,
List <TableSchema> tables)
{
CommandType commandType;
DataParameter[] parameters;
if (procedure.IsTableFunction)
{
commandText = "SELECT * FROM " + commandText + "(";
commandText += string.Join(",", procedure.Parameters.Select(p => (
p.SystemType == typeof(DateTime)
? "'" + DateTime.Now + "'"
: DefaultValue.GetValue(p.SystemType)) ?? "''"));
commandText += ")";
commandType = CommandType.Text;
parameters = new DataParameter[0];
}
else
{
commandType = CommandType.StoredProcedure;
parameters = HanaSchemaOptions != null
? (HanaSchemaOptions.GetStoredProcedureParameters(procedure) ??
GetStoredProcedureDataParameters(procedure))
: GetStoredProcedureDataParameters(procedure);
}
try
{
var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters, options);
procedure.IsLoaded = true;
if (st != null)
{
procedure.ResultTable = new TableSchema()
{
IsProcedureResult = true,
TypeName = ToValidName(procedure.ProcedureName + "Result"),
ForeignKeys = new List <ForeignKeySchema>(),
Columns = GetProcedureResultColumns(st, options)
};
foreach (var column in procedure.ResultTable.Columns)
{
column.Table = procedure.ResultTable;
}
procedure.SimilarTables =
(
from t in tables
where t.Columns.Count == procedure.ResultTable.Columns.Count
let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 })
where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType)
select t
).ToList();
}
}
catch (Exception ex)
{
procedure.ResultException = ex;
}
}
/// <summary>
/// Initializes source columns, marked with <see cref="ColumnAttribute.IsIdentity"/> with values.
/// If column had sequence name set using <see cref="SequenceNameAttribute"/> and <paramref name="useSequenceName"/> set to <c>true</c>, values from sequence used.
/// Otherwise column initialized with values, incremented by 1 starting with max value from database for this column plus 1.
/// </summary>
/// <typeparam name="T">Entity type.</typeparam>
/// <param name="source">Ordered list of entities to initialize.</param>
/// <param name="context">Data connection to use to retrieve sequence values of max used value for column.</param>
/// <param name="useSequenceName">Enables identity values retrieval from sequence for columns with sequence name specified in mapping using <see cref="SequenceNameAttribute"/>.</param>
/// <returns>Returns new collection of identity fields initialized or <paramref name="source"/> if entity had no identity columns.</returns>
public static IEnumerable <T> RetrieveIdentity <T>(
this IEnumerable <T> source,
DataConnection context,
bool useSequenceName = true)
{
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
IList <T>?sourceList = null;
var entityDescriptor = context.MappingSchema.GetEntityDescriptor(typeof(T));
foreach (var column in entityDescriptor.Columns)
{
if (column.IsIdentity)
{
sourceList ??= source.ToList();
if (sourceList.Count == 0)
{
return(sourceList);
}
var sequenceName = useSequenceName && column.SequenceName != null ? column.SequenceName.SequenceName : null;
if (sequenceName != null)
{
var sql = context.DataProvider.CreateSqlBuilder(context.MappingSchema).GetReserveSequenceValuesSql(sourceList.Count, sequenceName);
var sequences = context.Query <object>(sql).ToList();
for (var i = 0; i < sourceList.Count; i++)
{
var item = sourceList[i];
var value = Converter.ChangeType(sequences[i], column.MemberType);
column.MemberAccessor.SetValue(item !, value);
}
}
else
{
var sql = context.DataProvider.CreateSqlBuilder(context.MappingSchema).GetMaxValueSql(entityDescriptor, column);
var maxValue = context.Execute <object?>(sql);
if (maxValue == null || maxValue == DBNull.Value)
{
maxValue = 0;
}
var type = Type.GetTypeCode(maxValue.GetType());
foreach (var item in sourceList)
{
maxValue = type switch
{
TypeCode.Byte => (byte)maxValue + 1,
TypeCode.SByte => (sbyte)maxValue + 1,
TypeCode.Int16 => (short)maxValue + 1,
TypeCode.Int32 => (int)maxValue + 1,
TypeCode.Int64 => (long)maxValue + 1,
TypeCode.UInt16 => (ushort)maxValue + 1,
TypeCode.UInt32 => (uint)maxValue + 1,
TypeCode.UInt64 => (ulong)maxValue + 1,
TypeCode.Single => (float)maxValue + 1,
TypeCode.Decimal => (decimal)maxValue + 1,
_ => throw new NotImplementedException(),
};
var value = Converter.ChangeType(maxValue, column.MemberType);
column.MemberAccessor.SetValue(item !, value);
}
}
}
}
return(sourceList ?? source);
}
protected virtual BulkCopyRowsCopied ProviderSpecificCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(MultipleRowsCopy(dataConnection, options, source));
}
public LoginRepository(DataConnection dataConnection)
: base(dataConnection)
{
}
public EntryPlayerRepository(DataConnection dataConnection)
: base(dataConnection)
{
}
public static int PERSON_DELETE(this DataConnection dataConnection, int?PERSONID)
{
return(dataConnection.ExecuteProc("PERSON_DELETE",
new DataParameter("PERSONID", PERSONID, DataType.Int32)));
}
public void TypesTest([IncludeDataSources(CurrentProvider)] string context)
{
//IBM.Data.DB2.DB2Parameter p = null;
//p.
//new IBM.Data.DB2Types.DB2RowId();
dynamic int64Value = null;
dynamic int32Value = null;
dynamic int16Value = null;
DB2Tools.AfterInitialized(() =>
{
int64Value = DB2Types.DB2Int64.CreateInstance(1);
int32Value = DB2Types.DB2Int32.CreateInstance(2);
int16Value = DB2Types.DB2Int16.CreateInstance(3);
});
using (var conn = new DataConnection(context))
{
conn.Select(() => 1);
Assert.That(DB2Types.DB2Clob.CreateInstance(conn).IsNull, Is.True);
Assert.That(DB2Types.DB2Blob.CreateInstance(conn).IsNull, Is.True);
}
Assert.That(int64Value.Value, Is.TypeOf <long> ().And.EqualTo(1));
Assert.That(int32Value.Value, Is.TypeOf <int> ().And.EqualTo(2));
Assert.That(int16Value.Value, Is.TypeOf <short> ().And.EqualTo(3));
var decimalValue = DB2Types.DB2Decimal.CreateInstance(4);
var decimalValueAsDecimal = DB2Types.DB2DecimalFloat.CreateInstance(5m);
var decimalValueAsDouble = DB2Types.DB2DecimalFloat.CreateInstance(6.0);
var decimalValueAsLong = DB2Types.DB2DecimalFloat.CreateInstance(7);
var realValue = DB2Types.DB2Real.CreateInstance(8);
var real370Value = DB2Types.DB2Real370.CreateInstance(9);
var stringValue = DB2Types.DB2String.CreateInstance("1");
var clobValue = DB2Types.DB2Clob.CreateInstance("2");
var binaryValue = DB2Types.DB2Binary.CreateInstance(new byte[] { 1 });
var blobValue = DB2Types.DB2Blob.CreateInstance(new byte[] { 2 });
var dateValue = DB2Types.DB2Date.CreateInstance(new DateTime(2000, 1, 1));
var timeValue = DB2Types.DB2Time.CreateInstance(new TimeSpan(1, 1, 1));
if (DB2Types.DB2DateTime.Type != null)
{
var dateTimeValue1 = DB2Types.DB2DateTime.CreateInstance(new DateTime(2000, 1, 2));
var dateTimeValue2 = DB2Types.DB2DateTime.CreateInstance(new DateTime(2000, 1, 3).Ticks);
var timeStampValue = DB2Types.DB2DateTime.CreateInstance(new DateTime(2000, 1, 4));
Assert.That(dateTimeValue1.Value, Is.TypeOf <DateTime>().And.EqualTo(new DateTime(2000, 1, 2)));
Assert.That(dateTimeValue2.Value, Is.TypeOf <DateTime>().And.EqualTo(new DateTime(2000, 1, 3)));
Assert.That(timeStampValue.Value, Is.TypeOf <DateTime>().And.EqualTo(new DateTime(2000, 1, 4)));
}
Assert.That(decimalValue.Value, Is.TypeOf <decimal> ().And.EqualTo(4));
Assert.That(decimalValueAsDecimal.Value, Is.TypeOf <decimal> ().And.EqualTo(5));
Assert.That(decimalValueAsDouble.Value, Is.TypeOf <decimal> ().And.EqualTo(6));
Assert.That(decimalValueAsLong.Value, Is.TypeOf <decimal> ().And.EqualTo(7));
Assert.That(realValue.Value, Is.TypeOf <float> ().And.EqualTo(8));
Assert.That(real370Value.Value, Is.TypeOf <double> ().And.EqualTo(9));
Assert.That(stringValue.Value, Is.TypeOf <string> ().And.EqualTo("1"));
Assert.That(clobValue.Value, Is.TypeOf <string> ().And.EqualTo("2"));
Assert.That(binaryValue.Value, Is.TypeOf <byte[]> ().And.EqualTo(new byte[] { 1 }));
Assert.That(blobValue.Value, Is.TypeOf <byte[]> ().And.EqualTo(new byte[] { 2 }));
Assert.That(dateValue.Value, Is.TypeOf <DateTime>().And.EqualTo(new DateTime(2000, 1, 1)));
Assert.That(timeValue.Value, Is.TypeOf <TimeSpan>().And.EqualTo(new TimeSpan(1, 1, 1)));
DB2Tools.AfterInitialized(() =>
{
int64Value = DB2Types.DB2Int64.CreateInstance();
int32Value = DB2Types.DB2Int32.CreateInstance();
int16Value = DB2Types.DB2Int16.CreateInstance();
});
Assert.That(int64Value.IsNull, Is.True);
Assert.That(int32Value.IsNull, Is.True);
Assert.That(int16Value.IsNull, Is.True);
Assert.That(DB2Types.DB2Decimal.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2DecimalFloat.CreateInstance().IsNull, Is.False);
Assert.That(DB2Types.DB2Real.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2Real370.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2String.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2Binary.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2Date.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2Time.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2TimeStamp.CreateInstance().IsNull, Is.True);
Assert.That(DB2Types.DB2RowId.CreateInstance().IsNull, Is.True);
if (DB2Types.DB2DateTime.Type != null)
{
Assert.That(DB2Types.DB2DateTime.CreateInstance().IsNull, Is.True);
}
}
public override Task <int> MergeAsync <T>(DataConnection dataConnection, Expression <Func <T, bool> > deletePredicate, bool delete, IEnumerable <T> source,
string tableName, string databaseName, string schemaName, CancellationToken token)
{
return(new SqlServerMerge().MergeAsync(dataConnection, deletePredicate, delete, source, tableName, databaseName, schemaName, token));
}
protected override BasicMergeBuilder <TTarget, TSource> GetMergeBuilder <TTarget, TSource>(
DataConnection connection,
IMergeable <TTarget, TSource> merge)
{
return(new SqlServerMergeBuilder <TTarget, TSource>(connection, merge));
}
protected override void OnConnectionTypeCreated(Type connectionType)
{
DB2Types.ConnectionType = connectionType;
DB2Types.DB2Int64.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Int64", true);
DB2Types.DB2Int32.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Int32", true);
DB2Types.DB2Int16.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Int16", true);
DB2Types.DB2Decimal.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Decimal", true);
DB2Types.DB2DecimalFloat.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2DecimalFloat", true);
DB2Types.DB2Real.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Real", true);
DB2Types.DB2Real370.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Real370", true);
DB2Types.DB2Double.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Double", true);
DB2Types.DB2String.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2String", true);
DB2Types.DB2Clob.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Clob", true);
DB2Types.DB2Binary.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Binary", true);
DB2Types.DB2Blob.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Blob", true);
DB2Types.DB2Date.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Date", true);
DB2Types.DB2Time.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Time", true);
DB2Types.DB2TimeStamp.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2TimeStamp", true);
DB2Types.DB2Xml = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2Xml", true);
DB2Types.DB2RowId.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2RowId", true);
DB2Types.DB2DateTime.Type = connectionType.Assembly.GetType("IBM.Data.DB2Types.DB2DateTime", false);
SetProviderField(DB2Types.DB2Int64, typeof(Int64), "GetDB2Int64");
SetProviderField(DB2Types.DB2Int32, typeof(Int32), "GetDB2Int32");
SetProviderField(DB2Types.DB2Int16, typeof(Int16), "GetDB2Int16");
SetProviderField(DB2Types.DB2Decimal, typeof(Decimal), "GetDB2Decimal");
SetProviderField(DB2Types.DB2DecimalFloat, typeof(Decimal), "GetDB2DecimalFloat");
SetProviderField(DB2Types.DB2Real, typeof(Single), "GetDB2Real");
SetProviderField(DB2Types.DB2Real370, typeof(Single), "GetDB2Real370");
SetProviderField(DB2Types.DB2Double, typeof(Double), "GetDB2Double");
SetProviderField(DB2Types.DB2String, typeof(String), "GetDB2String");
SetProviderField(DB2Types.DB2Clob, typeof(String), "GetDB2Clob");
SetProviderField(DB2Types.DB2Binary, typeof(byte[]), "GetDB2Binary");
SetProviderField(DB2Types.DB2Blob, typeof(byte[]), "GetDB2Blob");
SetProviderField(DB2Types.DB2Date, typeof(DateTime), "GetDB2Date");
SetProviderField(DB2Types.DB2Time, typeof(TimeSpan), "GetDB2Time");
SetProviderField(DB2Types.DB2TimeStamp, typeof(DateTime), "GetDB2TimeStamp");
SetProviderField(DB2Types.DB2Xml, typeof(string), "GetDB2Xml");
SetProviderField(DB2Types.DB2RowId, typeof(byte[]), "GetDB2RowId");
MappingSchema.AddScalarType(DB2Types.DB2Int64, GetNullValue(DB2Types.DB2Int64), true, DataType.Int64);
MappingSchema.AddScalarType(DB2Types.DB2Int32, GetNullValue(DB2Types.DB2Int32), true, DataType.Int32);
MappingSchema.AddScalarType(DB2Types.DB2Int16, GetNullValue(DB2Types.DB2Int16), true, DataType.Int16);
MappingSchema.AddScalarType(DB2Types.DB2Decimal, GetNullValue(DB2Types.DB2Decimal), true, DataType.Decimal);
MappingSchema.AddScalarType(DB2Types.DB2DecimalFloat, GetNullValue(DB2Types.DB2DecimalFloat), true, DataType.Decimal);
MappingSchema.AddScalarType(DB2Types.DB2Real, GetNullValue(DB2Types.DB2Real), true, DataType.Single);
MappingSchema.AddScalarType(DB2Types.DB2Real370, GetNullValue(DB2Types.DB2Real370), true, DataType.Single);
MappingSchema.AddScalarType(DB2Types.DB2Double, GetNullValue(DB2Types.DB2Double), true, DataType.Double);
MappingSchema.AddScalarType(DB2Types.DB2String, GetNullValue(DB2Types.DB2String), true, DataType.NVarChar);
MappingSchema.AddScalarType(DB2Types.DB2Clob, GetNullValue(DB2Types.DB2Clob), true, DataType.NText);
MappingSchema.AddScalarType(DB2Types.DB2Binary, GetNullValue(DB2Types.DB2Binary), true, DataType.VarBinary);
MappingSchema.AddScalarType(DB2Types.DB2Blob, GetNullValue(DB2Types.DB2Blob), true, DataType.Blob);
MappingSchema.AddScalarType(DB2Types.DB2Date, GetNullValue(DB2Types.DB2Date), true, DataType.Date);
MappingSchema.AddScalarType(DB2Types.DB2Time, GetNullValue(DB2Types.DB2Time), true, DataType.Time);
MappingSchema.AddScalarType(DB2Types.DB2TimeStamp, GetNullValue(DB2Types.DB2TimeStamp), true, DataType.DateTime2);
MappingSchema.AddScalarType(DB2Types.DB2Xml, GetNullValue(DB2Types.DB2Xml), true, DataType.Xml);
MappingSchema.AddScalarType(DB2Types.DB2RowId, GetNullValue(DB2Types.DB2RowId), true, DataType.VarBinary);
_setBlob = GetSetParameter(connectionType, "DB2Parameter", "DB2Type", "DB2Type", "Blob");
if (DB2Types.DB2DateTime.IsSupported)
{
SetProviderField(DB2Types.DB2DateTime, typeof(DateTime), "GetDB2DateTime");
MappingSchema.AddScalarType(DB2Types.DB2DateTime, GetNullValue(DB2Types.DB2DateTime), true, DataType.DateTime);
}
if (DataConnection.TraceSwitch.TraceInfo)
{
DataConnection.WriteTraceLine(
DataReaderType.Assembly.FullName,
DataConnection.TraceSwitch.DisplayName);
DataConnection.WriteTraceLine(
DB2Types.DB2DateTime.IsSupported ? "DB2DateTime is supported." : "DB2DateTime is not supported.",
DataConnection.TraceSwitch.DisplayName);
}
DB2Tools.Initialized();
}
public override int Merge <T>(DataConnection dataConnection, Expression <Func <T, bool> > deletePredicate, bool delete, IEnumerable <T> source,
string tableName, string databaseName, string schemaName)
{
return(new SqlServerMerge().Merge(dataConnection, deletePredicate, delete, source, tableName, databaseName, schemaName));
}
public override void InitCommand(DataConnection dataConnection)
{
dataConnection.DisposeCommand();
base.InitCommand(dataConnection);
}
public override void InitCommand(DataConnection dataConnection, CommandType commandType, string commandText, DataParameter[] parameters)
{
dataConnection.DisposeCommand();
base.InitCommand(dataConnection, commandType, commandText, parameters);
}
protected override List <ProcedureInfo> GetProcedures(DataConnection dataConnection)
{
// because information schema doesn't contain information about function kind like aggregate or table function
// we need to query additional data from pg_proc
// in postgresql 11 pg_proc some requred columns changed, so we need to execute different queries for pre-11 and 11+ versions
var version = dataConnection.Query <int>("SHOW server_version_num").Single();
if (version < 110000)
{
return(dataConnection
.Query(rd =>
{
var catalog = rd.GetString(0);
var schema = rd.GetString(1);
var isTableResult = Converter.ChangeTypeTo <bool>(rd[7]);
return new ProcedureInfo()
{
ProcedureID = catalog + "." + schema + "." + rd.GetString(5),
CatalogName = catalog,
SchemaName = schema,
ProcedureName = rd.GetString(2),
// versions prior 11 doesn't support procedures but support functions with void return type
// still, we report them as function in metadata. Just without return parameter
IsFunction = rd.GetString(3) == "FUNCTION",
IsTableFunction = isTableResult,
IsAggregateFunction = Converter.ChangeTypeTo <bool>(rd[6]),
IsDefaultSchema = schema == "public",
ProcedureDefinition = Converter.ChangeTypeTo <string>(rd[4]),
// result of function has dynamic form and vary per call if function return type is 'record'
// only exception is function with out/inout parameters, where we know that record contains those parameters
IsResultDynamic = Converter.ChangeTypeTo <string>(rd[8]) == "record" && Converter.ChangeTypeTo <int>(rd[9]) == 0
};
}, @"
SELECT r.ROUTINE_CATALOG,
r.ROUTINE_SCHEMA,
r.ROUTINE_NAME,
r.ROUTINE_TYPE,
r.ROUTINE_DEFINITION,
r.SPECIFIC_NAME,
p.proisagg,
p.proretset,
r.DATA_TYPE,
outp.cnt
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN pg_catalog.pg_namespace n ON r.ROUTINE_SCHEMA = n.nspname
LEFT JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid AND r.SPECIFIC_NAME = p.proname || '_' || p.oid
LEFT JOIN (SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, COUNT(*) as cnt FROM INFORMATION_SCHEMA.parameters WHERE parameter_mode IN('OUT', 'INOUT') GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME) as outp
ON r.SPECIFIC_SCHEMA = outp.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = outp.SPECIFIC_NAME" )
.ToList());
}
else
{
return(dataConnection
.Query(rd =>
{
var catalog = rd.GetString(0);
var schema = rd.GetString(1);
var isTableResult = Converter.ChangeTypeTo <bool>(rd[6]);
var kind = Converter.ChangeTypeTo <char>(rd[5]);
return new ProcedureInfo()
{
ProcedureID = catalog + "." + schema + "." + rd.GetString(4),
CatalogName = catalog,
SchemaName = schema,
ProcedureName = rd.GetString(2),
// versions prior 11 doesn't support procedures but support functions with void return type
// still, we report them as function in metadata. Just without return parameter
IsFunction = kind != 'p',
IsTableFunction = isTableResult,
// this is only diffrence starting from v11
IsAggregateFunction = kind == 'a',
IsWindowFunction = kind == 'w',
IsDefaultSchema = schema == "public",
ProcedureDefinition = Converter.ChangeTypeTo <string>(rd[3]),
IsResultDynamic = Converter.ChangeTypeTo <string>(rd[7]) == "record" && Converter.ChangeTypeTo <int>(rd[8]) == 0
};
}, @"
SELECT r.ROUTINE_CATALOG,
r.ROUTINE_SCHEMA,
r.ROUTINE_NAME,
r.ROUTINE_DEFINITION,
r.SPECIFIC_NAME,
p.prokind,
p.proretset,
r.DATA_TYPE,
outp.cnt
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN pg_catalog.pg_namespace n ON r.ROUTINE_SCHEMA = n.nspname
LEFT JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid AND r.SPECIFIC_NAME = p.proname || '_' || p.oid
LEFT JOIN (SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, COUNT(*)as cnt FROM INFORMATION_SCHEMA.parameters WHERE parameter_mode IN('OUT', 'INOUT') GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME) as outp
ON r.SPECIFIC_SCHEMA = outp.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = outp.SPECIFIC_NAME" )
.ToList());
}
}
public void Test([DataSources(false, ProviderName.SQLiteMS, ProviderName.MySqlConnector, TestProvName.AllOracle12)]
string context)
{
using (var conn = new DataConnection(context))
{
var sp = conn.DataProvider.GetSchemaProvider();
var schemaName = TestUtils.GetSchemaName(conn);
var dbSchema = sp.GetSchema(conn, new GetSchemaOptions()
{
IncludedSchemas = schemaName != TestUtils.NO_SCHEMA_NAME ?new[] { schemaName } : null
});
var tableNames = new HashSet <string>();
foreach (var schemaTable in dbSchema.Tables)
{
var tableName = schemaTable.CatalogName + "." +
(schemaTable.IsDefaultSchema ? schemaTable.TableName : schemaTable.SchemaName + "." + schemaTable.TableName);
if (tableNames.Contains(tableName))
{
Assert.Fail("Not unique table " + tableName);
}
tableNames.Add(tableName);
var columnNames = new HashSet <string>();
foreach (var schemaColumn in schemaTable.Columns)
{
if (columnNames.Contains(schemaColumn.ColumnName))
{
Assert.Fail("Not unique column {0} for table {1}.{2}", schemaColumn.ColumnName, schemaTable.SchemaName, schemaTable.TableName);
}
columnNames.Add(schemaColumn.ColumnName);
}
}
//Get table from default schema and fall back to schema indifferent
TableSchema getTable(string name) =>
dbSchema.Tables.SingleOrDefault(t => t.IsDefaultSchema && t.TableName !.ToLower() == name)
?? dbSchema.Tables.SingleOrDefault(t => t.TableName !.ToLower() == name);
var table = getTable("parent");
Assert.That(table, Is.Not.Null);
Assert.That(table.Columns.Count(c => c.ColumnName != "_ID"), Is.EqualTo(2));
AssertType <Model.LinqDataTypes>(conn.MappingSchema, dbSchema);
AssertType <Model.Parent> (conn.MappingSchema, dbSchema);
if (context != ProviderName.AccessOdbc)
{
Assert.That(getTable("doctor").ForeignKeys.Count, Is.EqualTo(1));
}
else // no FK information for ACCESS ODBC
{
Assert.That(dbSchema.Tables.Single(t => t.TableName !.ToLower() == "doctor").ForeignKeys.Count, Is.EqualTo(0));
}
switch (context)
{
case ProviderName.SqlServer2000:
case ProviderName.SqlServer2005:
case ProviderName.SqlServer2008:
case ProviderName.SqlServer2012:
case ProviderName.SqlServer2014:
case TestProvName.SqlServer2016:
case ProviderName.SqlServer2017:
case TestProvName.SqlServer2019:
case TestProvName.SqlAzure:
{
var indexTable = dbSchema.Tables.Single(t => t.TableName == "IndexTable");
Assert.That(indexTable.ForeignKeys.Count, Is.EqualTo(1));
Assert.That(indexTable.ForeignKeys[0].ThisColumns.Count, Is.EqualTo(2));
}
break;
case ProviderName.Informix:
case ProviderName.InformixDB2:
{
var indexTable = dbSchema.Tables.First(t => t.TableName == "testunique");
Assert.That(indexTable.Columns.Count(c => c.IsPrimaryKey), Is.EqualTo(2));
Assert.That(indexTable.ForeignKeys.Count(), Is.EqualTo(2));
}
break;
}
switch (context)
{
case ProviderName.SqlServer2008:
case ProviderName.SqlServer2012:
case ProviderName.SqlServer2014:
case TestProvName.SqlServer2016:
case ProviderName.SqlServer2017:
case TestProvName.SqlServer2019:
case TestProvName.SqlAzure:
{
var tbl = dbSchema.Tables.Single(at => at.TableName == "AllTypes");
var col = tbl.Columns.First(c => c.ColumnName == "datetimeoffset3DataType");
Assert.That(col.DataType, Is.EqualTo(DataType.DateTimeOffset));
Assert.That(col.Length, Is.Null);
Assert.That(col.Precision, Is.EqualTo(3));
Assert.That(col.Scale, Is.Null);
}
break;
}
}
}
protected override List <TableInfo> GetTables(DataConnection dataConnection)
{
var sql = (@"
SELECT
t.table_catalog || '.' || t.table_schema || '.' || t.table_name as TableID,
t.table_catalog as CatalogName,
t.table_schema as SchemaName,
t.table_name as TableName,
t.table_schema = 'public' as IsDefaultSchema,
t.table_type = 'VIEW' as IsView,
(
SELECT pgd.description
FROM
pg_catalog.pg_statio_all_tables as st
JOIN pg_catalog.pg_description pgd ON pgd.objoid = st.relid
WHERE t.table_schema = st.schemaname AND t.table_name=st.relname
LIMIT 1
) as Description,
left(t.table_schema, 3) = 'pg_' OR t.table_schema = 'information_schema' as IsProviderSpecific
FROM
information_schema.tables t" );
if (ExcludedSchemas.Count == 0 && IncludedSchemas.Count == 0)
{
sql += @"
WHERE
table_schema NOT IN ('pg_catalog','information_schema')" ;
}
// materialized views supported starting from pgsql 9.3
var version = dataConnection.Query <int>("SHOW server_version_num").Single();
if (version >= 90300)
{
// materialized views are not exposed to information_schema
sql += @"
UNION ALL
SELECT
v.schemaname || '.' || v.matviewname as TableID,
NULL as CatalogName,
v.schemaname as SchemaName,
v.matviewname as TableName,
v.schemaname = 'public' as IsDefaultSchema,
true as IsView,
(
SELECT pgd.description
FROM pg_catalog.pg_class
INNER JOIN pg_catalog.pg_namespace ON pg_class.relnamespace = pg_namespace.oid
INNER JOIN pg_catalog.pg_description pgd ON pgd.objoid = pg_class.oid
WHERE pg_class.relkind = 'm' AND pgd.objsubid = 0 AND pg_namespace.nspname = v.schemaname AND pg_class.relname = v.matviewname
LIMIT 1
) as Description,
false as IsProviderSpecific
FROM pg_matviews v" ;
if (ExcludedSchemas.Count == 0 && IncludedSchemas.Count == 0)
{
sql += @"
WHERE
v.schemaname NOT IN ('pg_catalog','information_schema')" ;
}
}
return(dataConnection.Query <TableInfo>(sql).ToList());
}
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection)
{
var sql = @"
SELECT
c.table_catalog || '.' || c.table_schema || '.' || c.table_name as TableID,
c.column_name as Name,
c.is_nullable = 'YES' as IsNullable,
c.ordinal_position as Ordinal,
c.data_type as DataType,
c.character_maximum_length as Length,
COALESCE(
c.numeric_precision::integer,
c.datetime_precision::integer,
c.interval_precision::integer) as Precision,
c.numeric_scale as Scale,
c.is_identity = 'YES' OR COALESCE(c.column_default ~* 'nextval', false) as IsIdentity,
c.is_generated <> 'NEVER' as SkipOnInsert,
c.is_updatable = 'NO' as SkipOnUpdate,
(
SELECT pgd.description
FROM
pg_catalog.pg_statio_all_tables as st
JOIN pg_catalog.pg_description pgd ON pgd.objsubid = c.ordinal_position AND pgd.objoid = st.relid
WHERE c.table_schema = st.schemaname AND c.table_name=st.relname
LIMIT 1
) as Description
FROM
information_schema.columns as c" ;
if (ExcludedSchemas.Count == 0 || IncludedSchemas.Count == 0)
{
sql += @"
WHERE
table_schema NOT IN ('pg_catalog','information_schema')" ;
}
// materialized views supported starting from pgsql 9.3
var version = dataConnection.Query <int>("SHOW server_version_num").Single();
if (version >= 90300)
{
// materialized views are not exposed to information_schema
// NOTE: looks like IsNullable always true for mat.views (or I dunno where to look for it)
sql += @"
UNION ALL
SELECT
pg_namespace.nspname || '.' || pg_class.relname as TableID,
pg_attribute.attname as Name,
pg_attribute.attnotnull <> true as IsNullable,
pg_attribute.attnum as Ordinal,
pg_type.typname as DataType,
CASE WHEN pg_attribute.atttypmod = -1
THEN NULL
ELSE pg_attribute.atttypmod - 4
END as Length,
COALESCE(
information_schema._pg_numeric_precision(pg_type.oid, pg_attribute.atttypmod),
information_schema._pg_datetime_precision(pg_type.oid, pg_attribute.atttypmod))
as Precision,
information_schema._pg_numeric_scale(pg_type.oid, pg_attribute.atttypmod) as Scale,
false as IsIdentity,
true as SkipOnInsert,
true as SkipOnUpdate,
(
SELECT pgd.description
FROM pg_catalog.pg_class
INNER JOIN pg_catalog.pg_namespace ON pg_class.relnamespace = pg_namespace.oid
INNER JOIN pg_catalog.pg_description pgd ON pgd.objoid = pg_class.oid
WHERE pg_class.relkind = 'm' AND pgd.objsubid = pg_attribute.attnum AND pg_namespace.nspname = pg_namespace.nspname AND pg_class.relname = pg_class.relname
LIMIT 1
) as Description
FROM pg_catalog.pg_class
INNER JOIN pg_catalog.pg_namespace ON pg_class.relnamespace = pg_namespace.oid
INNER JOIN pg_catalog.pg_attribute ON pg_class.oid = pg_attribute.attrelid
INNER JOIN pg_catalog.pg_type ON pg_attribute.atttypid = pg_type.oid
WHERE pg_class.relkind = 'm' AND pg_attribute.attnum >= 1" ;
if (ExcludedSchemas.Count == 0 && IncludedSchemas.Count == 0)
{
sql += @" AND pg_namespace.nspname NOT IN ('pg_catalog','information_schema')";
}
}
return(dataConnection.Query <ColumnInfo>(sql).ToList());
}
public AppDbContext(DbContextOptions <AppDbContext> options, ICurrentUserInfo currentUser)
: base(options)
{
_currentUser = currentUser;
Linq2Db = options.CreateLinqToDbConnection();
}
protected override List <DataTypeInfo> GetDataTypes(DataConnection dataConnection)
{
var list = new[]
{
new DataTypeInfo {
TypeName = "name", DataType = typeof(string).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "oid", DataType = typeof(int).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "xid", DataType = typeof(int).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "smallint", DataType = typeof(short).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "int2", DataType = typeof(short).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "integer", DataType = typeof(int).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "int4", DataType = typeof(int).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "bigint", DataType = typeof(long).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "int8", DataType = typeof(long).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "real", DataType = typeof(float).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "float4", DataType = typeof(float).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "double precision", DataType = typeof(double).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "float8", DataType = typeof(double).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "boolean", DataType = typeof(bool).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "bool", DataType = typeof(bool).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "regproc", DataType = typeof(object).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "money", DataType = typeof(decimal).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "text", DataType = typeof(string).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "xml", DataType = typeof(string).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "bytea", DataType = typeof(byte[]).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "uuid", DataType = typeof(Guid).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "hstore", DataType = typeof(Dictionary <string, string>).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "json", DataType = typeof(string).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "jsonb", DataType = typeof(string).AssemblyQualifiedName
},
new DataTypeInfo {
TypeName = "character varying", DataType = typeof(string).AssemblyQualifiedName, CreateFormat = "character varying({0})", CreateParameters = "length"
},
new DataTypeInfo {
TypeName = "varchar", DataType = typeof(string).AssemblyQualifiedName, CreateFormat = "character varying({0})", CreateParameters = "length"
},
new DataTypeInfo {
TypeName = "character", DataType = typeof(string).AssemblyQualifiedName, CreateFormat = "character({0})", CreateParameters = "length"
},
new DataTypeInfo {
TypeName = "bpchar", DataType = typeof(string).AssemblyQualifiedName, CreateFormat = "character({0})", CreateParameters = "length"
},
new DataTypeInfo {
TypeName = "numeric", DataType = typeof(decimal).AssemblyQualifiedName, CreateFormat = "numeric({0},{1})", CreateParameters = "precision,scale"
},
new DataTypeInfo {
TypeName = "timestamptz", DataType = typeof(DateTimeOffset).AssemblyQualifiedName, CreateFormat = "timestamp ({0}) with time zone", CreateParameters = "precision"
},
new DataTypeInfo {
TypeName = "timestamp with time zone", DataType = typeof(DateTimeOffset).AssemblyQualifiedName, CreateFormat = "timestamp ({0}) with time zone", CreateParameters = "precision"
},
new DataTypeInfo {
TypeName = "timestamp", DataType = typeof(DateTime).AssemblyQualifiedName, CreateFormat = "timestamp ({0}) without time zone", CreateParameters = "precision"
},
new DataTypeInfo {
TypeName = "timestamp without time zone", DataType = typeof(DateTime).AssemblyQualifiedName, CreateFormat = "timestamp ({0}) without time zone", CreateParameters = "precision"
},
}.ToList();
var provider = (PostgreSQLDataProvider)dataConnection.DataProvider;
if (provider.NpgsqlInetType != null)
{
list.Add(new DataTypeInfo {
TypeName = "inet", DataType = provider.NpgsqlInetType.AssemblyQualifiedName
});
}
if (provider.NpgsqlInetType != null)
{
list.Add(new DataTypeInfo {
TypeName = "cidr", DataType = provider.NpgsqlInetType.AssemblyQualifiedName
});
}
if (provider.NpgsqlPointType != null)
{
list.Add(new DataTypeInfo {
TypeName = "point", DataType = provider.NpgsqlPointType.AssemblyQualifiedName
});
}
if (provider.NpgsqlLineType != null)
{
list.Add(new DataTypeInfo {
TypeName = "line", DataType = provider.NpgsqlLineType.AssemblyQualifiedName
});
}
if (provider.NpgsqlLSegType != null)
{
list.Add(new DataTypeInfo {
TypeName = "lseg", DataType = provider.NpgsqlLSegType.AssemblyQualifiedName
});
}
if (provider.NpgsqlBoxType != null)
{
list.Add(new DataTypeInfo {
TypeName = "box", DataType = provider.NpgsqlBoxType.AssemblyQualifiedName
});
}
if (provider.NpgsqlPathType != null)
{
list.Add(new DataTypeInfo {
TypeName = "path", DataType = provider.NpgsqlPathType.AssemblyQualifiedName
});
}
if (provider.NpgsqlPolygonType != null)
{
list.Add(new DataTypeInfo {
TypeName = "polygon", DataType = provider.NpgsqlPolygonType.AssemblyQualifiedName
});
}
if (provider.NpgsqlCircleType != null)
{
list.Add(new DataTypeInfo {
TypeName = "circle", DataType = provider.NpgsqlCircleType.AssemblyQualifiedName
});
}
if (provider.NpgsqlIntervalType != null)
{
list.Add(new DataTypeInfo {
TypeName = "interval", DataType = provider.NpgsqlIntervalType.AssemblyQualifiedName, CreateFormat = "interval({0})", CreateParameters = "precision"
});
}
if (provider.NpgsqlDateType != null)
{
list.Add(new DataTypeInfo {
TypeName = "date", DataType = provider.NpgsqlDateType.AssemblyQualifiedName
});
}
else
{
list.Add(new DataTypeInfo {
TypeName = "date", DataType = typeof(DateTime).AssemblyQualifiedName
});
}
if (provider.NpgsqlTimeType != null)
{
list.Add(new DataTypeInfo {
TypeName = "time with time zone", DataType = provider.NpgsqlTimeType.AssemblyQualifiedName, CreateFormat = "time ({0}) with time zone", CreateParameters = "precision"
});
}
else
{
list.Add(new DataTypeInfo {
TypeName = "time with time zone", DataType = typeof(DateTimeOffset).AssemblyQualifiedName, CreateFormat = "time ({0}) with time zone", CreateParameters = "precision"
});
}
if (provider.NpgsqlTimeType != null)
{
list.Add(new DataTypeInfo {
TypeName = "timetz", DataType = provider.NpgsqlTimeType.AssemblyQualifiedName, CreateFormat = "time ({0}) with time zone", CreateParameters = "precision"
});
}
else
{
list.Add(new DataTypeInfo {
TypeName = "timetz", DataType = typeof(DateTimeOffset).AssemblyQualifiedName, CreateFormat = "time ({0}) with time zone", CreateParameters = "precision"
});
}
if (provider.NpgsqlTimeTZType != null)
{
list.Add(new DataTypeInfo {
TypeName = "time without time zone", DataType = provider.NpgsqlTimeTZType.AssemblyQualifiedName, CreateFormat = "time ({0}) without time zone", CreateParameters = "precision"
});
}
else
{
list.Add(new DataTypeInfo {
TypeName = "time without time zone", DataType = typeof(TimeSpan).AssemblyQualifiedName, CreateFormat = "time ({0}) without time zone", CreateParameters = "precision"
});
}
if (provider.NpgsqlTimeTZType != null)
{
list.Add(new DataTypeInfo {
TypeName = "time", DataType = provider.NpgsqlTimeTZType.AssemblyQualifiedName, CreateFormat = "time ({0}) without time zone", CreateParameters = "precision"
});
}
else
{
list.Add(new DataTypeInfo {
TypeName = "time", DataType = typeof(TimeSpan).AssemblyQualifiedName, CreateFormat = "time ({0}) without time zone", CreateParameters = "precision"
});
}
list.Add(new DataTypeInfo {
TypeName = "macaddr", DataType = (provider.NpgsqlMacAddressType ?? typeof(PhysicalAddress)).AssemblyQualifiedName
});
list.Add(new DataTypeInfo {
TypeName = "macaddr8", DataType = (provider.NpgsqlMacAddressType ?? typeof(PhysicalAddress)).AssemblyQualifiedName
});
list.Add(new DataTypeInfo {
TypeName = "bit", DataType = (provider.BitStringType ?? typeof(BitArray)).AssemblyQualifiedName, CreateFormat = "bit({0})", CreateParameters = "size"
});
list.Add(new DataTypeInfo {
TypeName = "bit varying", DataType = (provider.BitStringType ?? typeof(BitArray)).AssemblyQualifiedName, CreateFormat = "bit varying({0})", CreateParameters = "size"
});
list.Add(new DataTypeInfo {
TypeName = "varbit", DataType = (provider.BitStringType ?? typeof(BitArray)).AssemblyQualifiedName, CreateFormat = "bit varying({0})", CreateParameters = "size"
});
return(list);
}
protected virtual BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
{
return(RowByRowCopy(dataConnection, options, source));
}
private IEnumerable <ProcedureParameterInfo> GetParametersForViews(DataConnection dataConnection)
{
if (SchemasFilter == null)
{
return(new List <ProcedureParameterInfo>());
}
var query = dataConnection.Query(rd =>
{
var schema = rd.GetString(0);
var view = rd.GetString(1);
var parameterName = rd.GetString(2);
var sqlDataType = rd.GetString(3);
var sqlDataTypeParts = sqlDataType.Split('(');
var dataType = sqlDataTypeParts[0];
var length = 0;
var scale = 0;
if (sqlDataTypeParts.Length == 2)
{
var infoStr = sqlDataTypeParts[1].Substring(0, sqlDataTypeParts[1].Length - 1);
var splited = infoStr.Split(',');
length = Convert.ToInt32(splited[0]);
if (splited.Length == 2)
{
scale = Convert.ToInt32(splited[1]);
}
}
var isMandatory = rd.GetBoolean(4);
var position = rd.GetInt32(5);
return(new ProcedureParameterInfo
{
ProcedureID = string.Concat(schema, '.', view),
DataType = dataType,
IsIn = isMandatory,
IsOut = false,
IsResult = false,
Length = length,
Ordinal = position,
ParameterName = parameterName,
Precision = length,
Scale = scale,
IsNullable = true
});
}, @"
SELECT
v.SCHEMA_NAME,
v.VIEW_NAME,
p.VARIABLE_NAME,
p.COLUMN_SQL_TYPE,
p.MANDATORY,
p.""ORDER""
FROM SYS.VIEWS AS v
JOIN _SYS_BI.BIMC_ALL_CUBES AS c ON c.VIEW_NAME = v.VIEW_NAME
JOIN _SYS_BI.BIMC_VARIABLE AS p ON c.CUBE_NAME = p.CUBE_NAME
WHERE c.CATALOG_NAME = p.CATALOG_NAME AND v.VIEW_TYPE = 'CALC' AND v.SCHEMA_NAME " + SchemasFilter + @"
ORDER BY v.VIEW_NAME, p.""ORDER""" );
return(query.ToList());
}
protected BulkCopyRowsCopied MultipleRowsCopy3 <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source, string from)
{
var helper = new MultipleRowsHelper <T>(dataConnection, options, false);
helper.StringBuilder
.AppendFormat("INSERT INTO {0}", helper.TableName).AppendLine()
.Append("(");
foreach (var column in helper.Columns)
{
helper.StringBuilder
.AppendLine()
.Append("\t")
.Append(helper.SqlBuilder.Convert(column.ColumnName, ConvertType.NameToQueryField))
.Append(",");
}
helper.StringBuilder.Length--;
helper.StringBuilder
.AppendLine()
.AppendLine(")")
.AppendLine("SELECT * FROM")
.Append("(");
helper.SetHeader();
foreach (var item in source)
{
helper.StringBuilder
.AppendLine()
.Append("\tSELECT ");
helper.BuildColumns(item);
helper.StringBuilder.Append(from);
helper.StringBuilder.Append(" UNION ALL");
helper.RowsCopied.RowsCopied++;
helper.CurrentCount++;
if (helper.CurrentCount >= helper.BatchSize || helper.Parameters.Count > 10000 || helper.StringBuilder.Length > 100000)
{
helper.StringBuilder.Length -= " UNION ALL".Length;
helper.StringBuilder
.AppendLine()
.Append(")");
if (!helper.Execute())
{
return(helper.RowsCopied);
}
}
}
if (helper.CurrentCount > 0)
{
helper.StringBuilder.Length -= " UNION ALL".Length;
helper.StringBuilder
.AppendLine()
.Append(")");
helper.Execute();
}
return(helper.RowsCopied);
}
protected override List <DataTypeInfo> GetDataTypes(DataConnection dataConnection)
{
var list = new[]
{
new DataTypeInfo {
TypeName = "name", DataType = typeof(string).FullName
},
new DataTypeInfo {
TypeName = "oid", DataType = typeof(int).FullName
},
new DataTypeInfo {
TypeName = "xid", DataType = typeof(int).FullName
},
new DataTypeInfo {
TypeName = "smallint", DataType = typeof(short).FullName
},
new DataTypeInfo {
TypeName = "integer", DataType = typeof(int).FullName
},
new DataTypeInfo {
TypeName = "bigint", DataType = typeof(long).FullName
},
new DataTypeInfo {
TypeName = "real", DataType = typeof(float).FullName
},
new DataTypeInfo {
TypeName = "double precision", DataType = typeof(double).FullName
},
new DataTypeInfo {
TypeName = "boolean", DataType = typeof(bool).FullName
},
new DataTypeInfo {
TypeName = "regproc", DataType = typeof(object).FullName
},
new DataTypeInfo {
TypeName = "money", DataType = typeof(decimal).FullName
},
new DataTypeInfo {
TypeName = "text", DataType = typeof(string).FullName
},
new DataTypeInfo {
TypeName = "xml", DataType = typeof(string).FullName
},
new DataTypeInfo {
TypeName = "date", DataType = typeof(DateTime).FullName
},
new DataTypeInfo {
TypeName = "bytea", DataType = typeof(byte[]).FullName
},
new DataTypeInfo {
TypeName = "uuid", DataType = typeof(Guid).FullName
},
new DataTypeInfo
{
TypeName = "character varying",
DataType = typeof(string).FullName,
CreateFormat = "character varying({0})",
CreateParameters = "length"
},
new DataTypeInfo
{
TypeName = "character",
DataType = typeof(string).FullName,
CreateFormat = "character({0})",
CreateParameters = "length"
},
new DataTypeInfo
{
TypeName = "numeric",
DataType = typeof(decimal).FullName,
CreateFormat = "numeric({0},{1})",
CreateParameters = "precision,scale"
},
new DataTypeInfo
{
TypeName = "timestamp with time zone",
DataType = typeof(DateTimeOffset).FullName,
CreateFormat = "timestamp ({0}) with time zone",
CreateParameters = "precision"
},
new DataTypeInfo
{
TypeName = "timestamp without time zone",
DataType = typeof(DateTime).FullName,
CreateFormat = "timestamp ({0}) without time zone",
CreateParameters = "precision"
}
}.ToList();
if (PostgreSQLTools.GetNpgsqlInetType() != null)
{
list.Add(new DataTypeInfo {
TypeName = "inet", DataType = PostgreSQLTools.GetNpgsqlInetType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlPointType() != null)
{
list.Add(new DataTypeInfo {
TypeName = "point", DataType = PostgreSQLTools.GetNpgsqlPointType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlLSegType() != null)
{
list.Add(new DataTypeInfo {
TypeName = "lseg", DataType = PostgreSQLTools.GetNpgsqlLSegType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlBoxType() != null)
{
list.Add(new DataTypeInfo {
TypeName = "box", DataType = PostgreSQLTools.GetNpgsqlBoxType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlPathType() != null)
{
list.Add(new DataTypeInfo {
TypeName = "path", DataType = PostgreSQLTools.GetNpgsqlPathType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlPolygonType() != null)
{
list.Add(new DataTypeInfo
{
TypeName = "polygon",
DataType = PostgreSQLTools.GetNpgsqlPolygonType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlCircleType() != null)
{
list.Add(new DataTypeInfo
{
TypeName = "circle",
DataType = PostgreSQLTools.GetNpgsqlCircleType().FullName
});
}
if (PostgreSQLTools.GetNpgsqlIntervalType() != null)
{
list.Add(new DataTypeInfo
{
TypeName = "interval",
DataType = PostgreSQLTools.GetNpgsqlIntervalType().FullName,
CreateFormat = "interval({0})",
CreateParameters = "precision"
});
}
if (PostgreSQLTools.GetNpgsqlTimeType() != null)
{
list.Add(new DataTypeInfo
{
TypeName = "time with time zone",
DataType = PostgreSQLTools.GetNpgsqlTimeType().FullName,
CreateFormat = "time with time zone({0})",
CreateParameters = "precision"
});
}
if (PostgreSQLTools.GetNpgsqlTimeTZType() != null)
{
list.Add(new DataTypeInfo
{
TypeName = "time without time zone",
DataType = PostgreSQLTools.GetNpgsqlTimeTZType().FullName,
CreateFormat = "time without time zone({0})",
CreateParameters = "precision"
});
}
list.Add(new DataTypeInfo
{
TypeName = "macaddr",
DataType = (PostgreSQLTools.GetNpgsqlMacAddressType() ?? typeof(PhysicalAddress)).FullName
});
list.Add(new DataTypeInfo
{
TypeName = "bit",
DataType = (PostgreSQLTools.GetBitStringType() ?? typeof(BitArray)).FullName,
CreateFormat = "bit({0})",
CreateParameters = "size"
});
return(list);
}
public static IEnumerable <PERSON_INSERT_OUTPUTPARAMETERResult> PERSON_INSERT_OUTPUTPARAMETER(this DataConnection dataConnection, string FIRSTNAME, string LASTNAME, string MIDDLENAME, char?GENDER, out int?PERSONID)
{
var ret = dataConnection.QueryProc <PERSON_INSERT_OUTPUTPARAMETERResult>("PERSON_INSERT_OUTPUTPARAMETER",
new DataParameter("FIRSTNAME", FIRSTNAME, DataType.NVarChar),
new DataParameter("LASTNAME", LASTNAME, DataType.NVarChar),
new DataParameter("MIDDLENAME", MIDDLENAME, DataType.NVarChar),
new DataParameter("GENDER", GENDER, DataType.NChar));
PERSONID = Converter.ChangeTypeTo <int?>(((IDbDataParameter)dataConnection.Command.Parameters["PERSONID"]).Value);
return(ret);
}
protected override List <ProcedureParameterInfo> GetProcedureParameters(DataConnection dataConnection, IEnumerable <ProcedureInfo> procedures, GetSchemaOptions options)
{
if (SchemasFilter == null)
{
return(new List <ProcedureParameterInfo>());
}
return(dataConnection.Query(rd =>
{
var schema = rd.GetString(0);
var procedure = rd.GetString(1);
var parameter = rd.GetString(2);
var dataType = rd.IsDBNull(3) ? null : rd.GetString(3);
var position = rd.GetInt32(4);
var paramType = rd.GetString(5);
var isResult = rd.GetBoolean(6);
var length = rd.GetInt32(7);
var scale = rd.GetInt32(8);
var isNullable = rd.GetString(9) == "TRUE";
return new ProcedureParameterInfo
{
ProcedureID = string.Concat(schema, '.', procedure),
DataType = dataType,
IsIn = paramType.Contains("IN"),
IsOut = paramType.Contains("OUT"),
IsResult = isResult,
Length = length,
Ordinal = position,
ParameterName = parameter,
Precision = length,
Scale = scale,
IsNullable = isNullable
};
}, @"
SELECT
SCHEMA_NAME,
PROCEDURE_NAME,
PARAMETER_NAME,
DATA_TYPE_NAME,
POSITION,
PARAMETER_TYPE,
0 AS IS_RESULT,
LENGTH,
SCALE,
IS_NULLABLE
FROM PROCEDURE_PARAMETERS
WHERE SCHEMA_NAME " + SchemasFilter + @"
UNION ALL
SELECT
SCHEMA_NAME,
FUNCTION_NAME AS PROCEDURE_NAME,
PARAMETER_NAME,
DATA_TYPE_NAME,
POSITION,
PARAMETER_TYPE,
CASE WHEN PARAMETER_TYPE = 'RETURN' THEN 1 ELSE 0 END AS IS_RESULT,
LENGTH,
SCALE,
IS_NULLABLE
FROM FUNCTION_PARAMETERS
WHERE NOT (PARAMETER_TYPE = 'RETURN' AND DATA_TYPE_NAME = 'TABLE_TYPE') AND SCHEMA_NAME " + SchemasFilter + @"
ORDER BY SCHEMA_NAME, PROCEDURE_NAME, POSITION" )
.ToList());
}
