Solved

Connect to SQL Server Function Using C# Data Grid View

Posted on 2012-03-24
8
1,159 Views
Last Modified: 2012-08-13
OK, so I'm pretty new to C# development, and I have an issue that I am just not understanding.

I have a SQL Server 2005 compatible DB with a series of tables. The ones that matter are the Users, Compatibility, and Suppliers tables. There is also a view, which pulls the aforementioned tables' data into a View that means something to a program. Finally, I have a function that displays the exact same thing as the view, except that it takes in a variable that filters the data by user (that variable is "@aUser"). The reason I have both a view and a function that display the exact same thing is because I am trying to get it to cooperate with a C# program that I am developing in VS 2010.

Connecting to the DB, viewing the tables, and displaying the view all work perfectly using a  DataGridView. My problem comes when I try to use the function. I have no idea how to pass the variable into the function using the DataGridView, along with the BindingSource and the TableAdapter that accompany it.

I read about using the filter property in the BindingSource, but that seems to be only for views and table queries. To that effect, I have no idea how to use a C# variable with the filter property of the BindingSource correctly.

I have no problem using a view or a table query instead of a function, but I also don't know how to use that filter property properly. Without either the proper way to pass a variable to the SQL function in the DataGridView, or the proper way to set up the filter property, my program is at a stand still.

My question is this: How do you pass a C# variable to a SQL Server DB function that is displayed in a DataGridView?

If you don't know that or you think that it's easier/more efficient to use the filter property, then I would love to know what too.

Many thanks in advance for the help and any advice is greatly appreciated.
0
Comment
Question by:Sirjacksinurbox
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 37761688
If I'm reading your question correctly.... you cannot pass a variable (input parameter) to a view.
You need to create a stored procedure.

create procedure GetMyData
@inputvar varchar(50)
as
select.....
<view code here>
where aField = @inputvar

Then you can create your sqlconnection and sqlcommand (the stored procedure) and supply the input parameter from a control on the page or a querystring value or....

http://www.codeproject.com/Articles/23998/Calling-SQL-Server-stored-procedures-from-Microsof
0
 
LVL 2

Author Comment

by:Sirjacksinurbox
ID: 37761791
@QPR:
Alright, I will look in to that.

But how do you point a sqlconnection to a datagridview?
0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 37761857
Are you using visual studio?
Easiest thing to do is to create your stored procedure, then open VS and drag a datagrid onto the form then follow the wizard, it will guide you through pointing to the DB, selecting your SP and it will detect that the SP requires a parameter and ask you where to get the parameter from
0
 
LVL 2

Author Comment

by:Sirjacksinurbox
ID: 37761870
Oh I see, good to know.

That definitely clears it up. What confuses me is that it doesn't detect that the Table-Valued Function that I programmed requires a variable. I will set up a stored proc and report back.

Thank you for the information.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:Sirjacksinurbox
ID: 37761945
Alright, everything went well with hooking up the stored proc, and when I preview the data, I can specify the variable to plug into it. It works correctly in that fashion and displays the correct data.

But when the time comes for it to work in the program, I cannot find out where to plug in the variable/parameter it needs to display the correct data. Is this area where it requests it labeled Filter?

Once again, thank you for answering my questions. As soon as I get it working correctly, you are the person who is getting all of the points.
0
 
LVL 29

Expert Comment

by:QPR
ID: 37761949
Where will your variable come from in the program?
0
 
LVL 2

Author Comment

by:Sirjacksinurbox
ID: 37761965
It can either come from a variables class (Variables.cs) or it can come from the form that calls the datagridview. That second variable is inside a private void method, inside of a public partial class form.

The variable name can either be userName or assignedUser, depending on where I call it from.
0
 
LVL 2

Author Closing Comment

by:Sirjacksinurbox
ID: 37779775
The answer wasn't exactly what I was looking for, but it got me thinking enough to find the solution.

I ended up using LINQ instead, which is a much more streamlined way of getting exactly what I was looking for.

Thanks again.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now