|
Just noting that the code posted suggests that a loop should not be used, thus the question that derives from it is moot.
SQL (as most dbs implement it) will do queries and updates on sets. The loop is implicit in it. It is just a matter of structuring it.
|
|
|
|
|
I'm trying to convert this to Linq. I didn't see a OR in Linq, so I'm figuring you can do multiple Where stateements. And on the date, I'm not sure if I should calculate it in code and jsut submit the date, or if there is a way to use Linq to do Date Calcs.
" SELECT COUNT(*) " & _
" FROM CRM_MESSAGES " & _
" WHERE " & _
" MessageStatus='MESSAGE_COMPLETE' " & _
" OR MessageStatus='MESSAGE_PENDING' " & _
" OR MessageStatus='MESSAGE_FORWARD' " & _
" AND ContactDate >=DATEADD(day, -30, getdate()) "
So Far I've got this
Dim messages As IQueryable(Of CRM_MESSAGES)
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_COMPLETE")
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_PENDING")
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_FORWARD")
messages = messages.Where(Function(m) m.ContactDate >= m.ContactDate.Date.AddDays(-30))
|
|
|
|
|
Multiple Where statements is equivalent to an AND operator. For an Or operator, use VB's OrElse operator:
messages = messages.Where(Function(m) m.MessageStatus = "MESSAGE_COMPLETE" OrElse m.MessageStatus = "MESSAGE_PENDING" OrElse m.MessageStatus = "MESSAGE_FORWARD")
Your date comparison won't do anything - you're asking for all messages where the contact date is later than a date 30 days before the contact date, which is all of them. Use Date.Today.AddDays(-30) instead:
messages = messages.Where(Function(m) m.ContactDate >= Date.Today.AddDays(-30))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I would of never figured that out on the multiple where statements.
And the Date bombed, so I tried DateTime.Now.AddDays(-30), but changed to your example before testing it.
I wonder if this will now work without using IQueryable? I'll give it a spin. It didn't work
LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.
I'll go back to IQueryable and give it a try.
Dim pValue As Integer = 0
Using context As New CRMContext()
pValue = _
(From item In context.CRM_MESSAGES
Where item.MessageStatus = "MESSAGE_COMPLETE" _
OrElse item.MessageStatus = "MESSAGE_PENDING" _
OrElse item.MessageStatus = "MESSAGE_FORWARD" _
And item.ContactDate >= Date.Today.AddDays(-30)).Count()
End Using
Return pValue
|
|
|
|
|
DBFunctions for EF 6.0, Thanks!
Imports System.Data.Entity
Dim pValue As Integer = 0
Using context As New CRMContext()
pValue = _
(From item In context.CRM_MESSAGES
Where item.MessageStatus = "MESSAGE_COMPLETE" _
OrElse item.MessageStatus = "MESSAGE_PENDING" _
OrElse item.MessageStatus = "MESSAGE_FORWARD" _
And item.ContactDate >= DbFunctions.AddDays(DateTime.Now(), -30)).Count()
End Using
Return pValue
|
|
|
|
|
jkirkerx wrote: LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.
You might need to move the date outside of the filter:
Dim minDate As Date = Date.Today.AddDays(-30)
messages = messages.Where(Function(m) m.ContactDate >= minDate)
Alternatively, use the EntityFramework.SqlServer assembly, and the System.Data.Entity.SqlServer.SqlFunctions class[^]:
messages = messages.Where(Function(m) m.ContactDate >= SqlFunctions.DateAdd("day", -30, SqlFunctions.GetDate()))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So I thought I was getting this down now.
In my original code, I used 1 function to get the name, and another function to get the record results.
So I rewrote it Linq, But I'm not quite getting how it works.
So the first query called contributor returns a result that looks like SQL sort of, and says to expand to see the results.
The 2nd query does the same thing, and the pValue for the record count is non zero, and triggers an exception of non zero.
So I'm missing something on the concept here, and can't figure it out.
I shortened the sample for easier reading.
Public Shared Function load_movieContributor_array( _
ByVal pMovieID As Integer,
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As IEnumerable(Of MovieIndex)) As Integer
Dim pValue As Integer = 0
Dim context As New MoviesContext()
Dim contributor = _
(From m In context.Movies
Where m.MovieID = pMovieID
Select m.MovieContributor)
Dim movies As IQueryable(Of Movies) = context.Movies
movies = movies.Where(Function(m) m.MovieContributor = contributor)
movies = movies.OrderBy(Function(m) m.MovieName)
'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 MovieIndex With
{
.MovieID = item.MovieID,
'More columns .....
})
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
Public Shared Function load_movieContributor_array( _
ByVal pMovieID As Integer,
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As IEnumerable(Of MovieIndex)) As Integer
Dim pValue As Integer = 0
Dim context As New MoviesContext()
Dim cResults = _
(From m In context.Movies
Where m.MovieID = pMovieID
Select m.MovieContributor).SingleOrDefault()
Dim m_contributor As String = cResults
Dim movies As IQueryable(Of Movies) = context.Movies
movies = movies.Where(Function(m) m.MovieContributor = m_contributor)
movies = movies.OrderBy(Function(m) m.MovieName)
'Get a Single Page of Data
If (pPageIndex = 0) Then
movies = movies.Take(pPageSize + 1)
Else
movies = movies.Skip((pPageIndex + 1) * pPageSize).Take(pPageSize)
End If
|
|
|
|
|
Hi,
I have a question regarding SQL queries.
In my select statement in my stored procedure I will receive a table of information like this:
UserId
------
2
4
5
After this select statement I'd like to add a insert statement to add records For each of these UserIds (as UserIdFrom) with the other ones (as UserIdTo)
I want to Insert into a table information like following:
UserIdFrom UserIdTo
---------- --------
2 4
2 5
4 2
4 5
5 2
5 4
I would be grateful if someone help me Smile | 
SELECT UserId FROM Users
INSERT INTO myTable (UserIdFrom, UserIdTo) VALUES (@UserIdFrom, @UserIdTo)
|
|
|
|
|
Try something like this:
INSERT INTO myTable
(
UserIdFrom,
UserIdTo
)
SELECT
F.UserId,
T.UserId
FROM
Users As F
CROSS JOIN Users As T
WHERE
T.UserId != F.UserId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thank u it helped me a lot!
|
|
|
|
|
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 ?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
