|
Stored Procedure
===================
create procedure dbo.myProcAS
selet * from customers
go
=====================
running this
exec myProc
Parameter Stored Procedure
=============================
create procedure dbo.MyProc
@CustNumber int
AS
SELECT * from customer
WHERE CustomerID = @CustNumber
go
=============================
running this
exec myProc 1001
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
very Simple
--Query without parameter
create proc procedure_name
as
Select * from table_name
--conditional query
create proc procedure_name
as
begin
Select * from table_name
End
Rakesh Jha
|
|
|
|
|
I would like to write a simple inventory application in C# to keep track of my dvds, music and books. While I'll use this application, I am more interested in it for the opportunity to familiarize myself with accessing a database in C#. However, my knowledge about databases, particularly accessing them in C#, is limited.
I am looking for suggestions on a good way to set up this application and databases. Tutorials, code snippets or full blown applications that access a database in a way similar to what I would be doing are all helpful.
Thanks for any help / suggestions you can give.
|
|
|
|
|
Andrew Stampor wrote: I am looking for suggestions on a good way to set up this application and databases.
I typed "ADO.NET Tutorial" into a search engine and got this: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx[^] There are more tutorials out there if you care to look. There are also excellent books on the subject.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thanks for your help!
I've been looking through the example at the site you suggested and I am having a little trouble getting it to run. I've installed "Microsoft SQL Sever 2005" and am trying to run the code from lesson 2 (http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx). When it gets to the "conn.Open()" call, I get an exception, which says:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I have attempted to resolve this but am not sure what needs to be done. I cannot find a setting that specifically allows or disallows remote connections.
|
|
|
|
|
It means you are trying to access the SQL Server over a network connection. By default, for security, the SQL Server 2005 only permits local connections (Previously TCP/IP was also turned on).
If your SQL Server is installed on the same machine as your application is running then change the Data Source value in the connection string to (local), with the brackets, instead of the machine name, an IP address or localhost.
If your SQL Server is running on a different machine to your application then allow it to accept incoming network connections (Open the SQL Server Configuration Manager). Find "Client Protocols", and in the right side Enable "TCP/IP". If you take this option you may wish to check the properties of the TCP/IP connection and ensure the port is locked down on your internet connection's firewall.
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
-- modified at 17:16 Sunday 11th June, 2006
|
|
|
|
|
Hi all,
Here's where I am stuck:
I am planning to have a sitemapath or a treeview of available reports for users in SQL reporting services 2005. I am pretty sure it is not an easy job. I am thinking of using frames here. Let's say the left frame shows the treeview and the right frame contains the reports page. Assume, there are three folders (Folder A, B, & C) containing few reports each. I want these folders and the corresponding reports(preferable) to be present in the left frame as treeview. And when I click on the folder from the left frame, it should bring up the corresponding Report Manager page showing the folder contents in the right frame.
any help or ideas would be greatly appreciated!
|
|
|
|
|
I've got a dynamic SQL query that is generated inside a SP, and is run using the
EXEC (@SQL)
or
EXEC sp_executesql @strSQL
This has worked fine until now, where we are now using a database for unicode charachters.
All fixed code stored procedures return data correctly in the unicode format, however, all queries run using EXEC / sp_executesql, return '?' instead of unicode charachters.
This is a cause of some serious issues, and i'm really hoping someone knows if there is a fix for it!
Cheers
Tris
|
|
|
|
|
I am working with a datagrid in c#/asp.net. Within the datagrid I have to display fields from 10 different tables. I am wondering if my thinking of the syntax is correct.
First, the user enters the part number (Parts.PartNumber)which is actually a varchar. Then it finds the Part.ID. This Part.ID is what most things are going to inner join. Right now I have it just displaying the cost:
Select cost from costs c INNER JOIN Parts p ON c.PartID = p.Id where p.PartNumber = @PartID
But I want it to also display SubParts.Quantity where SubParts.PartID = Parts.ID. So would I set it up like this?
Select cost from costs c, quantity from SubParts sp (INNER JOIN Parts p ON c.PartID = p.ID AND INNER JOIN sp.PartID = p.ID) where p.PartNumber = @PartID
I've got a bunch of these so if someone can point me in the right direction much thanks!
|
|
|
|
|
Personally, I don't use the "JOIN" keywords very often. You can do a typical join like this:
SELECT cost FROM costs as c, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID
Then your second statement would look like this:
SELECT cost, quantity FROM costs as c, SubPars as sp, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID AND sp.PartID=p.ID
Basically you just need to make sure that whatever you would put in the "ON" portion, you include in the "WHERE" portion.
If you want to keep the "JOIN" keywords, I believe it would have to look like this:
SELECT cost, quantity FROM costs as c INNER JOIN parts as p ON c.PartID=p.ID INNER JOIN SubParts as sp ON sp.PartID=p.ID WHERE p.PartNumber=@PartID
|
|
|
|
|
Okay, elaborating on the second statement...I have two tables that I have to pull the field 'description' (I had nothing to do with the table structures !). So would you say
SELECT cost, quantity, description, description from costs as c, SubParts as sp, WeightType as wt, CostType as ct, parts as p WHERE c.PartID = p.ID AND p.PartNumber = @PartID AND sp.PartID=p.ID...(and the other joins for the descriptions).
Since description is in there twice, is that a legal statement since the number of select fields matches the number of tables listed? Just curious.
|
|
|
|
|
I think you will have to use the table aliases and then give aliases to the field names. So if description is from the part table and the sub part table it would look like this:
SELECT cost, quantity, p.description as PartDesc, sp.description as SubPartDesc, ... (etc.)
|
|
|
|
|
SELECT c.cost, sp.quantity
FROM costs c
INNER JOIN Parts p ON c.PartID = p.ID
INNER JOIN SubParts sp ON p.ID = sp.PartID
WHERE p.PartNumber = @PartID
|
|
|
|
|
We use replication between our respective sites to keep certain data synchronized between our SQL servers. In order to use SQL's reporting service as a tool to notify us of replication failures, we had to update our main server (the publisher) to SP4.
After doing this our client app crashes (along with all the other clients) as soon as a certain form in our app is updated.
What can cause this? Memory leaks? Table locks?
Any ideas?
Thanks!
you can't forget something you never knew...
|
|
|
|
|
evilnoodle wrote: After doing this our client app crashes (along with all the other clients) as soon as a certain form in our app is updated.
Too vague - What is the error message? What was the app doing at the time?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
The form has functionality to insert or update data, the control source is a combination of two tables. Upon exit of the form (upon update of the DB) the app hangs, but on all client PC's.
you can't forget something you never knew...
|
|
|
|
|
I have a function which is interacting with SQL.
In the function i am opening the connection and closing it in the finally block.
When i execute that function it opens a connection with SQL that's fine.
Now if i call that functuion say 10 times 10 connections will be opened to SQL which is correct, but that should not be. Soon the SQL pool size will be filled and application(APS.Net) will get timeout exception.
That function will be used a lot of time in the application.
Is there a way that i can use a single connection to SQL for that application
Cause the problem that we are facing is that pool gets filled in a couple of hours and why the connection is not destroyed once i close it in FINALLY block.
Any help will be welcomed.
|
|
|
|
|
Use a SqlConnection object at the class level. Create a property accessor for the SqlConnection object, and in the 'get' portion of the property, only create a new connection if the existing SqlConnection object is null.
|
|
|
|
|
That is what i am doing when before opening the connection in the function.
|
|
|
|
|
Hi,
I think the easiest way to use a single connection for the whole application is to maintain the connection within the Global.asax.
There are several methods which will be called if your application starts or stops. Myself, I used this method several times when facing the same problem like you, and it worked fine for me.
I hope this helps a bit (if not, don't hesitate to ask again)
Regards
Sebastian
|
|
|
|
|
The best practices for ADO.NET suggest you aquire late and release early. You should create a connection only when you need it and release it as soon as you are done with it. You should not hold it for the life of the application.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I believe you need to Dispose() and set null in the finally block to insure it is returned to the pool. Close() is not enough.
As Colin pointed out, best practices recommend createing the connection as late as possible, and destroying it as soon as possible. You only need to keep it around across multiple operations if you are executing a multi-step transaction.Last modified: Thursday, June 08, 2006 10:01:36 AM --
|
|
|
|
|
I m connecting to SQL only when needed and closing and disposing ASAP.
When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection.
But as soon as i closes the application the pool goes to 0
I have tried with different options (Closing and keeping the connection open)
Dim scon As New SqlConnection(ConnectionString)
Dim da As New SqlDataAdapter("select * from Table", scon)
Dim ds As New DataSet
Try
da.Fill(ds)
Return ds
Catch ex As Exception
Finally
'da.Dispose()
'scon.Close()
'scon.Dispose()
End Try
what can be the reason.
|
|
|
|
|
nitin_ion wrote: When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection
But as soon as i closes the application the pool goes to 0
Looks like its working correctly to me. On the first use, the connection pool is initialized to the minimum (2) and stays there (since you are only using one connection. The pool is not destroyed until your app exits... How are you determining the pool's connection count?
You can cause connections not in use to be removed by setting the "Connection Lifetime" parameter in your connection string to the number of seconds after which the connection should be reclaimed (NET2.0)
It defaults to 0 which meens infinite lifetime.
|
|
|
|
|
Ok, just 1 Q
as i am using asp.net and i am not using Global.asaz for connectionstring and also i am using web services which interacts with the same database.
So if multiple users are using the web pages and at the same time webservice is also quering database then how many connections should be open.
In oor case if 5-7 users are using and web services are working the the connections to Db goes upto 100+ and
at the login page it starts 11+ connections and we are just checking userid and password that's it.
What can be the reason
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
