|
So I got my experimental code in VB working now, and a basic query.
So I'm looking at my original code, in which I sort of pieced together a query.
e.g.
Dim query As String = _
"SELECT * FROM MOVIEINFO "
Select Case p_Type
Case "FLV"
query += " WHERE flv = 1 "
Case "H264"
query += " WHERE h264 = 1 "
End Select
Select Case p_Sort
Case "NAME"
query += " ORDER BY MovieName "
Case "TYPE"
query += " ORDER BY MovieType "
End Select
Now I'm seeing if I can do the same thing in Linq
So this is my simple query. I'm not quite sure how to sort of do the same thing in the sample below.
I'm aware that I can load all the records into pResults, and then do another query to further isolate the results, but that doesn't seem efficient. I really just want to get the records of type, and a start and stop range. In other words, this is a indexed page of movies, and you click the page number to show those results. I already have the start and stop values.
Any input that is sound would be appreciated, Thanks
pResults =
(From item In context.Movies
Where item.flv = True
Order By item.MovieName
Select New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
}).AsEnumerable
|
|
|
|
|
Dim movies As IQueryable(Of Movie) = context.Movies
Select Case p_Type
Case "FLV"
movies = movies.Where(Function(m) m.flv)
Case "H264"
movies = movies.Where(Function(m) m.h264)
End Select
Select Case p_Sort
Case "NAME"
movies = movies.OrderBy(Function(m) m.MovieName)
Case "TYPE"
movies = movies.OrderBy(Function(m) m.MovieType)
End Select
movies = movies.Skip(pageIndex * pageSize).Take(pageSize)
pResults = movies.AsEnumerable().Select(Function(item) New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
})
Assuming MovieItem isn't an entity type, you'll need the AsEnumerable call before the Select call.
Everything prior to the AsEnumerable call will be translated into a SQL query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It works like a charm. I figured out that Movies was my Model, and imported the Namespace for it.
So I did some tweaks to get the record selection right, records displayed.
I'm still stuck on using the context, instead of Dimensioning it, but the base of what I project into gets disposed before I can generate the HTML. So that's another subject.
This SQL Linq is quite interesting, and seems to offer everything hand typed queries offer. It's fast.
I'm stoked on my Data Access Layer DLL, Seems to be working. I know the model works now.
If I can convert my asp.net webforms app program over to this, and be able to automatically create a database, and seed it with default values, and then if I change the database in one spot, and I can propagate the changes throughout the program, then I'm done.
All I have to do is adopt the BootStrap CSS Concept used in MVC, and I can sell this thing finally.
Thanks for taking the time to school me on this. I owe you the price of tuition for it. - Street Outlaws
And Here's what I got.
Public Shared Function load_index_array( _
ByVal pType As MovieType,
ByVal pSortOrder As SortOrder,
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As IEnumerable(Of MovieItem)) As Integer
Dim pValue As Integer = 0
Dim context As New MoviesContext()
Dim movies As IQueryable(Of Movies) = context.Movies
'Sort Order
Select Case pSortOrder
Case SortOrder.Name
movies = movies.OrderBy(Function(m) m.MovieName)
Case SortOrder.Type
movies = movies.OrderBy(Function(m) m.MovieType)
Case SortOrder.Time
movies = movies.OrderBy(Function(m) m.MovieTime)
Case SortOrder.Lastest
movies = movies.OrderByDescending(Function(m) m.MoviePostDate)
Case SortOrder.Early
movies = movies.OrderBy(Function(m) m.MoviePostDate)
End Select
'Filter By Type
Select Case pType
Case MovieType.FLV
movies = movies.Where(Function(m) m.flv)
Case MovieType.H264
movies = movies.Where(Function(m) m.h264)
End Select
'Get a Single Page of Data
If (pPageIndex = 1) Then
movies = movies.Take(pPageSize)
Else
movies = movies.Skip((pPageIndex - 1) * pPageSize).Take(pPageSize)
End If
pResults =
movies.AsEnumerable().Select(Function(item) New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
}).AsEnumerable
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
Hello,
On reporting services is the ability to view reports on multiple rendering format except for PPT / PPTX.
Looking at the net there Aspose.Slides the tool does good apparently, but that is not free. So I saw that there was the possibility of developing its own rendering extension
Microsoft explains here but it is not complete and accurate: https://msdn.microsoft.com/en-US/library/ms154018%28v=sql.120%29.aspx[^]
So I would have the top hand if anybody know how.
thank you
modified 6-Jul-15 5:45am.
|
|
|
|
|
|
Hello forum,
I'm using Visual Studio 2013 and MSSQL 2012. In my VB.net-application, I'm using a typed dataset to read data from MSSQL and write back additions, changes and deletions. I built Stored Procedures for the 4 commands (Select, Update, Delete and Insert), and assigned them to the 4 commands in the tableadapter.
All works fine; all 4 command do their job in my app.
BUT:
under some (well defined) circumstances the SPs return a returnvalue of 1. This returnvalue signals overlapping of the pending data and the old data in the table. Thus, the SPs do enforce that the time intervals in the table do not overlap. It works, but it works silently: "illegal" inputs are rejected, but without notice.
So, I'd like to get access to this returnvalue, in order to show a message like "Your pending data overlap existing intervals and can therefore not be saved".
How can i access the returncode of SPs used in TableAdaptermanager?
|
|
|
|
|
Behold, the power of a cup of coffee 
Step 1:
Raiserror in the SP with a severity of 11 or higher. 10 and lower ist considered a warning and won't throw an exception in vb.net.
IF @intConflictingRows = 0
BEGIN
INSERT INTO [dbo].[tbl] (Fields) VALUES (@Values);
SELECT Fields FROM tbl WHERE (ID = SCOPE_IDENTITY())
END
ELSE
BEGIN
RAISERROR('Error: New interval overlapping existing data!', 11, 1)
END
Step 2:
Wrap the TableAdapterManager.UpdateAll in a Try-Catch-block, and catch all exception with a Messagebox.
Try
If Me.Validate() Then
MyBindingsource.EndEdit()
TableAdapterManager.UpdateAll(myDataset)
Return True
End If
Return False
Catch ex As Exception
MessageBox.Show(ex.Message, "Error while saving!")
Return False
End Try
modified 3-Jul-15 8:45am.
|
|
|
|
|
Does any one know of a query builder tool that can be used on Oracle databases and integrated into a WPF application. There seems to be a squillion SQL Server tools out there but not many Oracle ones. I specifically DON'T want a DB management tool.
I need the users to be able to browse views, then build reasonably complex queries and view the results.
[edit] This looks promising EasyQuery[^] [/edit]
Never underestimate the power of human stupidity
RAH
modified 30-Jun-15 20:44pm.
|
|
|
|
|
Do you need a graphical tool or just a text tool?
|
|
|
|
|
I have an C# Windows Forms application (let's call it "APP-A") that I use on a weekly basis. I've been using APP-A for a while and recently needed to add the ability to store data into a MS-SQL database.
So I added EntityFramework 6.1.3 to my project (using NuGet) and using Code First, I quickly added my model classes and a dbcontext class, targeting my local SQLEXPRESS instance.
All works, life is good, the data I need to store is in the DB, success!
Next, I needed to move my SQL DB to one of our MSSQL servers, so I changed the connection string in App.config and ran the application, expecting the DB would be created on the server.
The DB was not created so I manually created the DB on the server, then ran the application and it won't connect to the server DB. I then changed the connection string back to point to my local SQLEXPRESS, and the application again works perfectly.
Frustrated, I created a brand new C# console app (lets call it "APP-B") and added EF 6.1.3 to it, then copied my models and dbcontext classes and connection string from APP-A to APP-B and attempted to connect to the DB on the server, which APP-B did without a problem.
I then deleted the DB from the server, re-ran APP-B and the database was recreated correctly on the server.
I changed APP-B connection string to point to my local SQLEXPRESS and it connected to the local DB fine, then I changed the APP-B connection string to point at the server and it again works fine.
So now I have two projects, APP-A which will connect to my local SQLEXPRESS but not the server, and APP-B which can access BOTH the server DB and the local SQLEXPRESS DB.
I cannot emphasize this enough: both APP-A and APP-B contain the same models and dbcontext classes as well as the same connection strings.
Today I removed and reinstalled EF 6.1.3 from APP-A and I still get the same results - APP-A can access the local SQLEXPRESS DB, but not the server DB.
Has anyone ever seen this behavior before?
Sincerely,
-Mark
mamiller@rhsnet.org
modified 25-Jun-15 15:53pm.
|
|
|
|
|
SOLVED!
The problem was caused by WHERE my APP-A project was located.
At my work we have shared user folders that are hosted on the network, and that is where my APP-A project was located.
The problem with that is that these shared folders are not trusted by our SQL servers, so when my application ran from that location the SQL server would reject the connection.
I just moved my project to my local C: drive, rebuilt it and ran it and viola! it connects to the SQL server database without issue.
My APP-B project was created on my local C: drive which is why it was able to access both the local SQLEXPRESS and the SQL server databases without issue.
Sincerely,
-Mark
mamiller@rhsnet.org
|
|
|
|
|
i want to stop some peoples in my organization to access SQL server through SQL managment Studio.
|
|
|
|
|
You can't. You can't know how they're accessing it and it shouldn't matter anyway; either they have access or they don't.
|
|
|
|
|
How can you say this ? i know they are accessing through SQL management studio.what you think if we can change the port or any other method ?
|
|
|
|
|
Azam Niaz Ch. wrote: i know they are accessing through SQL management studio
How?
|
|
|
|
|
Just FYI, it looks like the Management Studio executable identifies itself as something similar to "Microsoft SQL Server Management Studio" or "Microsoft SQL Server Management Studio - Query" when it connects to SQL Server. This shows up in the program_name column of the dynamic management view sys.dm_exec_sessions.
Scott
|
|
|
|
|
Which means nearly nothing; you can set the applications' name in the connectionstring. It would not be hard to create a console-app to have me execute SQL with the same or no name at all.
If someone has access, he has access. Disabling the tool he/she uses simply means changing tool to achieve the same.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Forget the client tool, SSMS is just a UI and can be replaced by a number of tools. You need to control access to your database/servers using the standard security setting built into the database and server applications.
Assuming you are system admin for the database you can start setting login requirements, creating schemas and other standard practices.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The quickish and simple way:
(1) remove SSMS from their computers
(2) restrict access to the server so that they can only access views and cannot perform any updates, inserts or deletes or do anything else for that matter - views only, that's a simple way of providing more security.
Notes: given (2), (1) is not strictly necessary, however the only people using SSMS should be the DBAs so it is probably best to get people out of the habit of having access to it(I know... if people download Toad they will have similar features available).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: the only people using SSMS should be the DBAs
NNnnnnnnooooo, don't make the developers use VS for Ghus sake. We have a bunch of DBA's who are so divorced from the developers I have never even met one. They only look after production and accept scripts from us for changes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am getting the following error when I am running the stored procedure.
Msg 217, Level 16, State 1, Procedure rptClassInformation_sel, Line 26. Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
When I remove the following part, it is not throwing that error. Can you please let me know where am I missing, am I making something wrong, is it related to Line 26 as in the message.
WITH CTE AS
(
SELECT FILTER.GroupKey,
FILTER.ClassScheduleId,
SUM(SlotCount) AS SlotCount
FROM #SlotInformation INFO
INNER JOIN #Base FILTER
ON FILTER.ClassScheduleId = INFO.ClassScheduleId
AND (
@Level = 'Class'
OR FILTER.UseInAggs = 1
)
WHERE SlotPlanTypeId IN (@SLOTPLAN_ECEAP, @SLOTPLAN_ECEAPSPED)
GROUP BY
FILTER.GroupKey,
FILTER.ClassScheduleId
)
INSERT INTO #Results
(GroupKey, Identifier, Name, Value)
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting 8 or more hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay >= 8 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey
UNION ALL
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting at least 4 hours but fewer than 8 hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay >= 4 AND WEEK.AverageHoursPerDay < 8 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey
UNION ALL
SELECT WEEK.GroupKey,
'NIEER' AS Identifier,
'Number of ECEAP slots in ECEAP classes meeting fewer than 4 hours per day' AS Name,
SUM(CASE WHEN WEEK.AverageHoursPerDay < 4 THEN CTE.SlotCount ELSE 0 END) AS Value
FROM #WeeklyOverview WEEK
INNER JOIN CTE
ON CTE.GroupKey = WEEK.GroupKey
AND CTE.ClassScheduleId = WEEK.ClassScheduleId
GROUP BY
WEEK.GroupKey;
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 22-Jun-15 12:59pm.
|
|
|
|
|
A common table expression can only be used in the statement which immediately follows its declaration. In your code, that's the SELECT * FROM CTE; statement.
If you want to re-use it in the INSERT INTO statement, then you have to re-state it:
WITH CTE As
(
...
)
SELECT * FROM CTE;
WITH CTE As
(
...
)
INSERT INTO #Results
...
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am sorry but that was not the issue even without putting the select statement error was coming. I put that select statement in process of debugging it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi all 
Currently now I used MS SQL Server Management Studio 2008.
I have an existing table with data already store on that table.
Table Name :
Application
Column :
Categories (nvarchar(1), null)
Sequence (nvarchar(1), null)
Detail (nvarchar(150), null)
I would like to ask how to add primary key to column Categories and Sequence ? Do I need to use alter query?
Thank you in advance !
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
