Solved

c# and sql

Posted on 2006-11-07
17
187 Views
Last Modified: 2010-04-16
I have c# 2005 and sql 2000. I am able to open through a wizard the sql views and see the results. I want to be able to pull the data that are grabbed from the views into my application.

I really did not want to write a command.commandtext = "select....."

statement. Is there a way to call the query as itself from sql instead of actually having to rewrite the query in c#?
0
Comment
Question by:bman9111
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 15

Expert Comment

by:ozymandias
ID: 17891247
"call the query as itself" ?

I'm not clear.
The only way to get the data is via a command and that command must have some text.
The command text can be any sql such a selecting from a table, selecting from a view, calling a stored porcedure or user function, but you have to execute a command somewhere.
0
 
LVL 28

Expert Comment

by:strickdd
ID: 17891504
the best way to do this is either

A) Use a table adapter to the View

B) command.commandtext = "SELECT * FROM ViewName"
0
 
LVL 8

Author Comment

by:bman9111
ID: 17891788
I guess i am not wanting to have to take a view I did in sql 2000 and have to retype it and redo the syntax for it in order for my application to grab the data.


Is there a way to just be able to call a view without rewritting it?

0
 
LVL 28

Expert Comment

by:strickdd
ID: 17891894
you select from the view itself. A view is created dynamically in SQL as the data updates. It acts as a table would and you can select from it just as if it were a table.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 17892557

Yes, you can create a SQL View(which will act like a data table), or you can create a stored procedure that returns a query result.

SQL Views become very important when working with highly-normalized databases.

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 17892558

Yes, you can create a SQL View(which will act like a data table), or you can create a stored procedure that returns a query result.

SQL Views become very important when working with highly-normalized databases.

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 17892559

Yes, you can create a SQL View(which will act like a data table), or you can create a stored procedure that returns a query result.

SQL Views become very important when working with highly-normalized databases.

0
 
LVL 28

Expert Comment

by:strickdd
ID: 17892653
I'm glad to see you are consistent with your responses...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Author Comment

by:bman9111
ID: 17892703
so basically what ever view I created in sql I need to take the code and insert it into a commandtext = "select.....what ever i wrote in sql"

correct?
0
 
LVL 8

Author Comment

by:bman9111
ID: 17893329
let me restate this...
I have a view that is called table1view

how can I call this in c#?
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17893445
select * from tableview1
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17893447
...that is the CommandText of the Command.
You call it the same way you would in SQL.
0
 
LVL 8

Author Comment

by:bman9111
ID: 17895093
I have a query called tableview1 and when I try to do a select * from tableview1 I get an invalid object error.

I tried other views but the only thing I can query is the tables.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 17895817

Are you using Oracle?  In oracle, after you create a view, you need to create a public synonymn as well.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 17895841
nevermind...   "I have c# 2005 and sql 2000"

I suspect the db account that you are using to access the database has been granted specific access to various tables.

You should be sure that the db account that you are using to access the database has the data_reader role so you can query any object.


Using the account that you are using for your application, execute the following queries:


-- User Tables
Select * from systemobjects where xtype='U'

-- User Views
Select * from systemobjects where xtype='V'

0
 
LVL 8

Author Comment

by:bman9111
ID: 17898923
I tried that

commandtext = "Select * from systemobjects where xtype='V'"

and got an error:

"Invalid object name 'systemobjects'"

this is the same error I get trying to call the other view.

also for the heck of it I tried.

Select * from queryname where xtype='V'
0
 
LVL 22

Accepted Solution

by:
_TAD_ earned 125 total points
ID: 17904341

systemobjects is a SQL Server table that contains all of the objects needed in order for SQL server to function.

In short, systemobjects *definately* exists and you simply don't have access to it.  


Your problem is security.  The user that you are using to access SQL Server simply does not have sufficient privledges
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Library to convert HTML to PDF. 8 55
getting id from database 5 36
Different Delete Messages 7 37
Allow space in this pattern 2 46
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

943 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

5 Experts available now in Live!

Get 1:1 Help Now