コード例 #1
0
		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();
		}
コード例 #2
0
		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();
		}
コード例 #3
0
            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);
                }
            }
コード例 #4
0
		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;
		}
コード例 #5
0
ファイル: frmEditList.cs プロジェクト: pelolep/CIS411
        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();
            }
        }
コード例 #6
0
        /// <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;
        }
コード例 #7
0
		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();
		}
コード例 #8
0
		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[]"   },
			};
		}
コード例 #9
0
        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();
        }
コード例 #10
0
        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();
        }
コード例 #11
0
        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();
        }
コード例 #12
0
ファイル: Packages.cs プロジェクト: JustAndrei/C1-Packages
 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();
     };
 }
コード例 #13
0
ファイル: Raty.cs プロジェクト: JustAndrei/C1-Packages
		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);
				}
			
		}
コード例 #14
0
        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();
        }
コード例 #15
0
        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();
        }
コード例 #16
0
		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();
		}
コード例 #17
0
		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();
		}
コード例 #18
0
 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();
 }
コード例 #19
0
 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();
 }
コード例 #20
0
ファイル: GetData.cs プロジェクト: nampn/ODental
 ///<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;
 }
コード例 #21
0
ファイル: Report.aspx.cs プロジェクト: JustAndrei/C1-Packages
	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>";
		}
	}
コード例 #22
0
        /// <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;
        }
コード例 #23
0
ファイル: GetData.cs プロジェクト: nampn/ODental
 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();
 }
コード例 #24
0
		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);
		}
コード例 #25
0
		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();
		}
コード例 #26
0
 // Getting a product by id
 public Product Get(Guid id)
 {
     using (var c = new DataConnection())
     {
         return c.Get<Product>().FirstOrDefault(p => p.Id == id);
     }
 }
コード例 #27
0
ファイル: Packages.cs プロジェクト: JustAndrei/C1-Packages
 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();
     }
 }
コード例 #28
0
		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();
		}
コード例 #29
0
 // Getting the list of products
 public IEnumerable<Product> Get()
 {
     using (var c = new DataConnection())
     {
         return c.Get<Product>().OrderBy(p => p.Title).ToList();
     }
 }
コード例 #30
0
        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();
        }
コード例 #31
0
 static SQLiteTests()
 {
     LinqToDBForEFTools.Initialize();
     DataConnection.TurnTraceSwitchOn();
 }
コード例 #32
0
 protected override string  PassValueSql(DataConnection dc) => "SELECT ID FROM {1} WHERE {0} = ?";
コード例 #33
0
 protected override string?PassNullSql(DataConnection dc, out int paramCount)
 {
     paramCount = 1;
     return(null);
 }
コード例 #34
0
ファイル: InformixTools.cs プロジェクト: ywscr/linq2db
 static InformixTools()
 {
     DataConnection.AddDataProvider(_informixDataProvider);
 }
コード例 #35
0
        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());
        }
コード例 #36
0
        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;
            }
        }
コード例 #37
0
ファイル: DataExtensions.cs プロジェクト: zxd60/linq2db
        /// <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);
        }
コード例 #38
0
 protected virtual BulkCopyRowsCopied ProviderSpecificCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
 {
     return(MultipleRowsCopy(dataConnection, options, source));
 }
コード例 #39
0
 public LoginRepository(DataConnection dataConnection)
     : base(dataConnection)
 {
 }
コード例 #40
0
 public EntryPlayerRepository(DataConnection dataConnection)
     : base(dataConnection)
 {
 }
コード例 #41
0
 public static int PERSON_DELETE(this DataConnection dataConnection, int?PERSONID)
 {
     return(dataConnection.ExecuteProc("PERSON_DELETE",
                                       new DataParameter("PERSONID", PERSONID, DataType.Int32)));
 }
コード例 #42
0
ファイル: DB2Tests.cs プロジェクト: ywscr/linq2db
        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);
            }
        }
コード例 #43
0
 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));
 }
コード例 #44
0
 protected override BasicMergeBuilder <TTarget, TSource> GetMergeBuilder <TTarget, TSource>(
     DataConnection connection,
     IMergeable <TTarget, TSource> merge)
 {
     return(new SqlServerMergeBuilder <TTarget, TSource>(connection, merge));
 }
コード例 #45
0
        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();
        }
コード例 #46
0
 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));
 }
コード例 #47
0
ファイル: DB2DataProvider.cs プロジェクト: zuiwanting/linq2db
 public override void InitCommand(DataConnection dataConnection)
 {
     dataConnection.DisposeCommand();
     base.InitCommand(dataConnection);
 }
コード例 #48
0
 public override void InitCommand(DataConnection dataConnection, CommandType commandType, string commandText, DataParameter[] parameters)
 {
     dataConnection.DisposeCommand();
     base.InitCommand(dataConnection, commandType, commandText, parameters);
 }
コード例 #49
0
        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());
            }
        }
コード例 #50
0
        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;
                }
            }
        }
コード例 #51
0
        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());
        }
コード例 #52
0
        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());
        }
コード例 #53
0
 public AppDbContext(DbContextOptions <AppDbContext> options, ICurrentUserInfo currentUser)
     : base(options)
 {
     _currentUser = currentUser;
     Linq2Db      = options.CreateLinqToDbConnection();
 }
コード例 #54
0
        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);
        }
コード例 #55
0
 protected virtual BulkCopyRowsCopied MultipleRowsCopy <T>(DataConnection dataConnection, BulkCopyOptions options, IEnumerable <T> source)
 {
     return(RowByRowCopy(dataConnection, options, source));
 }
コード例 #56
0
        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());
        }
コード例 #57
0
        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);
        }
コード例 #58
0
        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);
        }
コード例 #59
0
        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);
        }
コード例 #60
0
        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());
        }