Visual Basic

1. Change the commandtype to CommandType.StoredProcedure.
   
2. Change "ExecQ" to "ProcedureName". Assign that to .CommandText.
   
3. Add a Parameter.WithValue for all 15 parameters. (Yes, 15 lines of code).
   

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

Public Shared Function GetAllMovies() As IEnumerable(Of MovieItem)
    Using context As New MovieContext()
        Dim results = From item In context.Movies
                      Where item.flv = 1
                      Order By item.MovieName
                      Select New MovieItem With 
                      {
                          .MovieId = item.MovieId,
                          .MovieName = item.MovieName,
                          ...
                      }

        Return results.AsEnumerable()
    End Using
End Function

Imports System.Data.Entity
Imports System.Data.Entity.ModelConfiguration.Conventions
Imports dataAccessLayer.Models

Namespace DAL

    Public Class MovieContext
        Inherits DbContext

        Public Sub New()
            MyBase.New("MovieContext")

            Database.SetInitializer(New CreateDatabaseIfNotExists(Of MovieContext))
            Entity.Database.SetInitializer(Of MovieContext)(Nothing)

        End Sub

        Public Property Movies As DbSet(Of Movies)
        Public Property Movies_flv As DbSet(Of Movies_flv)
        Public Property Movies_h264 As DbSet(Of Movies_h264)

        Protected Overrides Sub OnModelCreating( _
            ByVal modelBuilder As DbModelBuilder)

            modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()

        End Sub

    End Class

End Namespace

Public Shared Function load_movie_array( _
        ByVal pSortOrder As SortOrder,
        ByVal pType As MovieType) As IEnumerable(Of MovieItem)

        Using context As New MovieContext()
            Dim results =
                From item In context.Movies
                Where item.flv = 1
                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,
                    .movie_Parameters = item.movie_Parameters
            }

            Return results.AsEnumerable()

        End Using

    End Function

jkirkerx wrote:

I have to project the results into another object like a class or something, to transport it.

jkirkerx wrote:

I thought that's what the model was for

Public Shared Function GetAllFlvMovies() As IEnumerable(Of Movie)
    Using context As New MovieContext()
        Dim results = From m In context.Movies
                      Where item.flv = 1
                      Order By m.MovieName
                      Select m
 
        Return results.AsEnumerable()
    End Using
End Function

...
    Dim movies = From item In GetAllFlvMovies()
                 Select New MovieViewModel With
                 {
                     Id = item.MovieId,
                     Name = item.MovieName,
                     ...
                 }

    ... send the <code>movies</code> collection to whatever view you need ...

Dim m_count As Integer = shared_MovieInfo.get_index_count(shared_MovieInfo.MovieType.FLV)
Dim movies As IEnumerable(Of MovieItem)
movies = shared_MovieInfo.load_index_array(shared_MovieInfo.SortOrder.Name, shared_MovieInfo.MovieType.FLV)

For Each m As Object In movies
  Dim m_MovieName_Encoded As String = System.Web.HttpUtility.HtmlEncode(m.MovieName)
Next

Public Shared Function get_index_count( _
        ByVal pType As MovieType) As Integer

        Dim context As New MoviesContext()
        Dim pValue = (From item In context.Movies
                      Where item.flv = 1
                      Select item).Count()

        Return pValue

    End Function

jkirkerx wrote:

So I wrote this, not sure if it's right, because I get 0 back, and there's 4

jkirkerx wrote:

Do you think this is because the database was already existing, and it's related to Code First.

jkirkerx wrote:

On the previous function, I'm not sure if I'm getting data back or not, and not quite sure how to use the returned output.

Quote:

For Each m As Object In movies
Dim m_MovieName_Encoded As String = System.Web.HttpUtility.HtmlEncode(m.MovieName)
Next

jkirkerx wrote:

Count Function that I wrote.

Dave Kreskowiak wrote:

Uhhhh, what does this have to do with a Count or a list of movies?

Dave Kreskowiak wrote:

Do you think this is because the database was already existing, and it's related to Code First.

Dave Kreskowiak wrote:

Uhhhh, what does this have to do with a Count or a list of movies?

{SELECT 
    [Extent1].[MovieID] AS [MovieID], 
    [Extent1].[MovieName] AS [MovieName], 
    [Extent1].[MovieType] AS [MovieType], 
    [Extent1].[MovieDeliveryType] AS [MovieDeliveryType], 
    [Extent1].[MoviePath] AS [MoviePath], 
    [Extent1].[MovieURL] AS [MovieURL], 
    [Extent1].[MoviePostage] AS [MoviePostage], 
    [Extent1].[MovieThumbnail] AS [MovieThumbnail], 
    [Extent1].[MovieLarge] AS [MovieLarge], 
    [Extent1].[MoviePlaceHolder] AS [MoviePlaceHolder], 
    [Extent1].[MovieBitRate] AS [MovieBitRate], 
    [Extent1].[MovieTime] AS [MovieTime], 
    [Extent1].[MovieSize] AS [MovieSize], 
    [Extent1].[MovieDescription] AS [MovieDescription], 
    [Extent1].[MovieCount] AS [MovieCount], 
    [Extent1].[MovieContributor] AS [MovieContributor], 
    [Extent1].[MoviePostDate] AS [MoviePostDate], 
    [Extent1].[MovieIcon] AS [MovieIcon], 
    [Extent1].[movie_Parameters] AS [movie_Parameters]
    FROM [dbo].[MOVIEINFO] AS [Extent1]
    WHERE 1 = [Extent1].[flv]
    ORDER BY [Extent1].[MovieName] ASC}

Public Sub New()
            MyBase.New("Data Source=XXXXXXXX; Initial Catalog=XXXXXXX; User ID=XXXXXXX; Password=XXXXX;")

            Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString

            Database.SetInitializer(New CreateDatabaseIfNotExists(Of MoviesContext))
            Entity.Database.SetInitializer(Of MoviesContext)(Nothing)

        End Sub

Public Shared Function load_index_array( _
        ByVal pSortOrder As SortOrder,
        ByVal pType As MovieType,
        ByRef pResults As IEnumerable(Of MovieItem)) As Integer

        Dim context As New MoviesContext()
        pResults =
            (From item In context.Movies
            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,
                .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

        Return pResults.Count()

    End Function

    public class MyContext : DbContext
    {
        public MyContext()
        {
        }

        public MyContext(string connectionStringName)
            : base(connectionStringName)
        {
        }

        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();

            // Call IPrepareForSave on entities that support it...

            return base.SaveChanges();
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            ConfigureModel(modelBuilder);
            base.OnModelCreating(modelBuilder);
        }

        private void ConfigureModel(DbModelBuilder modelBuilder)
        {
            // Explicitly declare some relationships...
            modelBuilder.Configurations.Add(new SomethingConfiguration());
            modelBuilder.Configurations.Add(new SomethingElseConfiguration());
            modelBuilder.Configurations.Add(new SomeOtherTableConfiguration());
            ...
        }

        public DbSet<sometype> SomeTypes { get; set; }
        public DbSet<othertype> OtherTypes { get; set; }
        public DbSet<contact> Contacts { get; set; }
        ...
    }
}
<div class="signature"><a href="http://www.codeproject.com/scrapbook/ForumGuidelines.asp">A guide to posting questions on CodeProject</a><br><br>Click this: <a href="http://weblogs.asp.net/bleroy/asking-questions-is-a-skill">Asking questions is a skill</a>.
Seriously, do it.<br>
<font color="Blue">Dave Kreskowiak</font></div>

jkirkerx wrote:

I used ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
as the conn string in my new in context. But I changed to your version, You must have a good reason for it; I'll learn down the as to why.

jkirkerx wrote:

Is it good practice to create a new context and just pass it along? with using. I think that would get disposed as well.

Public Shared Function load_movie_record( _
        ByVal p_MovieID As Integer,
        ByRef pResults As MovieRecord) As Integer

Dim context As New MoviesContext()
  pResults =
  ( _
  From m In context.Movies
  Join f In context.Movies_flv On m.MovieID Equals f.movieID
  Join h In context.Movies_h264 On m.MovieID Equals h.movieID
  Where m.MovieID = p_MovieID
  Select New MovieRecord With {
     .MovieID = m.MovieID,
     .MovieName = m.MovieName,
     .MovieType = m.MovieType,
     .flvID = f.ID,
     .flvMovieID = f.movieID,
     .flvMovieName = f.flv_movieName,
     .h264ID = f.ID,
     .h264MovieID = h.movieID,
     .h264MovieName = h.h264_movieName,
  }).SingleOrDefault()