How to select data from textbox

Question

I have made a database using model-first approach. I have three tables: Books with properties BookID, Title. Authors with properties AuthorID, Name, Surname. AuthorBook table in order to orginise many-to-many connection with properties AuthorBookID, AuthorID, BookID. All these tables have some data. I have a textbox where I want to type a title of a book or a name or a surname of author of this book and the selected (by stored procedure) items will be shown in DataGrid. I've made a stored procedure which succesfully does this function in SQL Management Studio and in script in Visual Studio. But I don't know how to make it work by code and how to use it correctly.

Xaml:

<Canvas>
    <TextBox x:Name="SearchBox" Width="193" Height="32" Canvas.Left="38" Canvas.Top="38"></TextBox>
    <Button Name="SearchButton" Click="SearchButton_OnClick" Content="Search" Canvas.Left="204" Canvas.Top="80" Height="30" Width="64"></Button>
    <DataGrid x:Name="Info" ItemsSource="{Binding Path=Authorslist1}" Background="Honeydew" Canvas.Top="200" Canvas.Left="30" Width="259" Height="50" ></DataGrid>
</Canvas>

C#:

 public partial class UserPage : Window
{
    Model1Container db1 = new Model1Container();
    private MainWindow mv = new MainWindow();

    public UserPage(Model1Container d, MainWindow m)
    {
        InitializeComponent();
        DataContext = this;
        db1 = d;
        mv = m;
    }


    private void SearchButton_OnClick(object sender, RoutedEventArgs e)
    {


    }

Script:

CREATE PROCEDURE [dbo].AuthorProcedure1 @name nvarchar(50) AS SELECT BookSet.Title, AuthorSet.Name, AuthorSet.Surname FROM [dbo].AuthorBookSet JOIN BookSet ON BookSet.BookID=AuthorBookSet.BookID JOIN AuthorSet ON AuthorSet.AuthorID=AuthorBookSet.AuthorID WHERE Title=@name GO

If your models are properly configured with navigation properties that is a pretty simple LINQ query (no need for stored proc) But if you must: http://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first — Steve Greene, Dec 28 '16 at 17:50
@SteveGreene I haven't found approptiate solution for myself there, unfortunately. and yes, I have yo use stored procedures — David Shepard, Dec 28 '16 at 19:15
What have you tried? Did you make a model of the fields your stored proc returns? Did you compose the call var result = context.Database.SqlQuery("AuthorProcedure1", bookTitle).ToList(); ? — Steve Greene, Dec 28 '16 at 20:07
@SteveGreene I tried var result = context.Database.SqlQuery("AuthorProcedure1", bookTitle).ToList(); and there's an error: Procedure or function 'AuthorProcedure1' expects parameter '@name', which was not supplied. I also tried to write SearchBob.Text insted of Title in parameter, but it didn't work either — David Shepard, Dec 28 '16 at 20:18
yeah, you need to set the parameter first: var bookTitle = new SqlParameter("@name", "Book Title to Find"); — Steve Greene, Dec 28 '16 at 20:21
@SteveGreene if i write like this, it doesn't work, the error is the same var bookTitle = new SqlParameter("@name", SearchBox.Text); var result = db1.Database.SqlQuery("AuthorProcedure1", bookTitle).ToList(); or if i write just Title in parameter after "AuthorProcedure1" — David Shepard, Dec 28 '16 at 20:27
My bad, you need the parms in the query: var result = context.Database.SqlQuery("AuthorProcedure1 @name", bookTitle).ToList(); — Steve Greene, Dec 28 '16 at 20:30
@SteveGreene oh, there's the error i've already had once "read the data module is not compatible with the specified "Model1.Book". Member of the type "BookID" does not have the corresponding column of the same name in the data reading module" that's odd because if i execute the procedure in visual studio (the script), it suggests me to enter the title and then gives me the result that I expect, so when it doesn't come to code, it works correctly... — David Shepard, Dec 28 '16 at 20:36

Steve Greene · Answer 1 · 2016-12-28T21:50:57.407

1

Ok, summarizing the comments:

1) Create a model to hold the results (or use anonymous type):

public class BookViewModel
{
    public string Title { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
}

2) Setup the parameter and call the proc:

var bookTitle = new SqlParameter("@name", txtSearch.Text);
var result = context.Database.SqlQuery<BookViewModel>("AuthorProcedure1 @name", bookTitle).ToList();

edited Dec 28 '16 at 21:50

answered Dec 28 '16 at 21:12

Steve Greene

12,029
1
33
54

It works, thank you! But it seems that stored procedure here does..nothing? Is it somehow possible to get both titles and author like it is said in procedure? and how it's done in procedure in script and to do it not by linq but with procedure – David Shepard Dec 28 '16 at 21:34
What do you mean? This is calling the stored procedure and returning an object with all 3 properties. Use ADO.NET if you don't want the ORM aspects... – Steve Greene Dec 28 '16 at 21:41
then how to get in datagrid, for example, the result of this procedure after all - both a title and an author? – David Shepard Dec 28 '16 at 21:46
Just [bind](https://msdn.microsoft.com/en-us/library/jj574514(v=vs.113).aspx) to it. Actually the FirstOrDefault doesn't make sense for a grid - use the ToList() version. – Steve Greene Dec 28 '16 at 21:50
I can wtire like this and get only titles var bookTitle = new SqlParameter("@name", SearchBox.Text); var result = db1.Database.SqlQuery("AuthorProcedure1 @name", bookTitle).ToList(); Info.ItemsSource = (from s in result group s by s.Title into res select new { MyTitle = res.Key }).ToList(); - it's shown in datagrid through binding And how to get authors? – David Shepard Dec 28 '16 at 22:07

score 0 · Answer 2 · answered Dec 29 '16 at 11:30

I can write like this and get only titles:

var bookTitle = new SqlParameter("@name", SearchBox.Text);
var result = db1.Database.SqlQuery<BookViewModel>("AuthorProcedure1 @name", bookTitle).ToList();
Info.ItemsSource = (from s in result group s by s.Title into res select new { MyTitle = res.Key }).ToList();

it's shown in datagrid through binding. And how to get authors?

Set the ItemsSource property of the DataGrid to the List<BookViewModel>. This list will contain the authors that were returned from your stored procedure:

var result = db1.Database.SqlQuery<BookViewModel>("AuthorProcedure1 @name", bookTitle).ToList();
Info.ItemsSource = result;

<DataGrid x:Name="Info" AutoGenerateColumns="False" Background="Honeydew" Canvas.Top="200" Canvas.Left="30" 
                  Width="259" Height="50" >
    <DataGrid.Columns>
        <DataGridTextColumn Header="Title" Binding="{Binding Title}" />
        <DataGridTextColumn Header="Name" Binding="{Binding Name}" />
        <DataGridTextColumn Header="Surname" Binding="{Binding Surname}" />
    </DataGrid.Columns>
</DataGrid>

score 0 · Answer 3 · answered Dec 29 '16 at 17:35

0

Here's the solution:

var bookTitle = new SqlParameter("@name", SearchBox.Text); var result = db1.Database.SqlQuery<BookAuthorViewModel>("AuthorProcedure1 @name", bookTitle).ToList();
Info.ItemsSource = result;

answered Dec 29 '16 at 17:35

David Shepard

181
2
10

How to select data from textbox

3 Answers3