best provider for connection to SQL ?

Hi ,
any one can suggest me wich is the best provider connection I can use
for my program to connect to an MSSQL 2000, currently I use the
"Microsoft ADO OLEDB for SQL" .
Thanks,
Paolol
OLEDB will give you more functionality than ODBC so that's a
good choice. You want to look at the latest (or at least one
of the later ones) version of MDAC to have more stability.
-Sue
On Fri, 21 Oct 2005 16:18:00 +0200, paolol
<paolol@._nospamma_salsan.net> wrote:

>Hi ,
>any one can suggest me wich is the best provider connection I can use
>for my program to connect to an MSSQL 2000, currently I use the
>"Microsoft ADO OLEDB for SQL" .
>Thanks,
>Paolol

best provider for connection to SQL ?

Hi ,
any one can suggest me wich is the best provider connection I can use
for my program to connect to an MSSQL 2000, currently I use the
"Microsoft ADO OLEDB for SQL" .
Thanks,
PaololOLEDB will give you more functionality than ODBC so that's a
good choice. You want to look at the latest (or at least one
of the later ones) version of MDAC to have more stability.
-Sue
On Fri, 21 Oct 2005 16:18:00 +0200, paolol
<paolol@._nospamma_salsan.net> wrote:

>Hi ,
>any one can suggest me wich is the best provider connection I can use
>for my program to connect to an MSSQL 2000, currently I use the
>"Microsoft ADO OLEDB for SQL" .
>Thanks,
>Paololsql

Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best practice advice for efficient SQL connection code

Hi,

I have an application which is similar to the following example

Private Sub Start()
For a as int16 = 1 to 300
lstResults.items.add(GetPriceFromItem(a))
Next
End Sub

Private Function GetPriceFromItem(byval item as int16) as String
'Connect to SQL
'Execute "SELECT Price FROM Table WHERE Item='" & item.tostring & "'"
'Close Database connection
'Return Price
End Function

I want to know if there is a more efficeint way of doing this, i.e. i'm concerned that the routine creates 300 SqlConnection instances, 300 open/closes and 300 queries

Would a better way be to connect to SQL once, get the entire table then do the 300 "lookups" locally somehow, perhaps put it all into a DataTable, but can you query a datatable in this way, or could you suggest another control.

Best Regards

Ben

You might try one SQL statement which returns all of your needed records in one resultset, with a query like this:

SELECT Price, Item FROM Table WHERE Item BETWEEN 1 AND 300

(I suggest changing the data type of your Item column to integer.)|||

If lstResults is a listbox, then I would use tmorton's SELECT statement with a SqlDataSource control to fill the listbox instead of coding it. Unless of course, you want all of the items, then just don't put anything in the WHERE clause at all.

if lstResults is just a list, then use tmorton's SELECT statement with a datareader to fill the list all at once.

|||

Hi,

I just used lstResults to simplyfy my example, in the actual application these queries form part actually a DataTable which is built on the fly.

Most of the columns are populated with values coming from the Ebay API, then for the last column I take the value of column 0 which is ItemID and lookup to a SQL DB (approx 300 records)

Then datatable is bounded to a datagridview

|||

In that case, use the datareader and tmorton's SELECT statement, but you will need to reverse your logic. Read each record from the SQL Database, then find the row in the datatable that it corresponds to (if any).

Or, you can use a datareader, and stuff the result into a collection/dictionary, then iterate through the datatable, and use the itemID to retrieve the value from the collection/dictionary.

|||

Hi, yes thats the idea that I had. But what is a collection/dictionary?

|||

dim x as new collection

x.add("value1","key1")

x.add("value2","key2")

x.add("MyValue","Mykey")

debug.print x("key1") -- prints value1
debug.print x("Mykey") -- prints MyValue
debug.print x("key2") -- prints value2

A collection/dictionary is basically a key/value pair that allows you to store the value into an object and then quickly retrieve the value based on the key. Most implementations use a hashed key AND/OR binary tree structure so that retrieving the value is pretty fast, much faster than say iterating through an array looking for a key. Just be careful when you retrieve values from the collection as the default collection requires a string key. If you ask for a numeric key, then it'll act more like an array and give you back the nth entry in the collection rather than the value of that key.
So...
debug.print x(1) -- will retrieve the first value
debug.print x(cstr(1)) -- will retrieve the value that has a key of "1"

A dictionary is very similiar, as it stores and retrieves keys and values. In .NET dictionaries are a generic form of collection as far as I know, but with a few different methods, so the following will still work:
dim x as new generic.dictionary(Of string,string)
x.add("value","key")
debug.print x("key")

But you can't retrieve things by index like you can in a collection, so the following will NOT work:
debug.print x(1)

I think dictionaries are a bit faster than collections too.

|||

Hi

You could process just one sql statement (much more efficient use of the query engine) by using the IN statement eg.

select * from table

where tablefield IN ("a", "b", "c")

Hope this helps

Chris Seary

Best method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

be notified when connections = 5

I would like to be notified of when my database connection hit 5. That way I know when I ned to upgrade from msde to standard edition. I have the server email ready but not sure how to moniter the connections.Create an alert in performance monitor.|||sounds streight forward thanks.|||The limitation on MSDE is on 5 concurrently running processes. You can periodically check DBCC CONCURRENCYVIOLATION (sp?) to see if you have hit that limit. So long as you keep the queries short, you could concievably keep 20 or 30 people happy enough with the application.|||it is one of those situations where I really do not know how many users I will get at a time. That is why I want to have a way to moniter it. If I have to by the full version fine, if I can hold of awhile great.|||The concurrent workload governor looks at concurrent batches - not connections. Knowing that, I would enable the logging using the DBCC CONCURRENCYVIOLATION(STARTLOG) command and/or use active transactions in the performance monitor.|||ok, great. thanks for the info.

BDNull error...not expected!

I have a connection (SqlConnection1) established through the GUI. Here's the
code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Create and initialise the command object
Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
'State that the type of this command is a stored procedure
cmd.CommandType = CommandType.StoredProcedure
'Create the input paramater
cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
cmd.Parameters("@.BizName").Value = TextBox1.Text
'Create the output paramater
cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
'Enusre the connection is open
If (cmd.Connection.State <> ConnectionState.Open) Then
cmd.Connection.Open()
End If
'Execute the command object
cmd.ExecuteNonQuery()
'Assign the returned value of the output paramater
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
'Close the connection
cmd.Connection.Close()
End Sub
This code allows a textbox (textbox1) to fill an input paramater and
displays the contents of the returned value from the output paramater in a
textbox (textbox2). It works fine with s imple select statement. But with a
stored procedure i get this error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
type 'String' is not valid.
Source Error:
Line 63:
Line 64: 'Assign the returned value of the output paramater
Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
The code for the stored procedure:
CREATE PROCEDURE GetAddress
@.BizName varchar,
@.BizAddress varchar output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Where did i go wrong?
Thanks for any insights.
NabYour Stored Procedure is returning a NULL value in the @.BizAddress output
parameter. You need to assign the value to an Object and check it for
DBNull.value before converting to string:
Dim o As Object
o = cmd.Parameters("@.BizAddress").Value
If (o Is Nothing OrElse o Is DBNull.Value) Then
TextBox2.Text = ""
Else
TextBox2.Text = Convert.ToString(o)
EndIf
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||A NULL @.BizAddress value will be returned when no data is found and this
cannot be converted to a .Net string data type. You can check for NULL
using DbNull.Value:
If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
MessageBox.Show("BizName not found")
Else
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
End If
Also, you need specify varchar(50) in your stored procedure parameter
declaration. The default length is 1.
CREATE PROCEDURE GetAddress
@.BizName varchar(50),
@.BizAddress varchar (50) output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||Nab wrote:
> I have a connection (SqlConnection1) established through the GUI.
> Here's the code:
>
You really should post these client-side questions to a more appropriate
newsgroup. Here are some suggestions:
microsoft.public.dotnet.languages.vb.data
microsoft.public.dotnet.framework.adonet
More below:

> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
>
<snip>
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
<snip>
> Exception Details: System.InvalidCastException: Cast from type
> 'DBNull' to type 'String' is not valid.
>
<snip>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
Always, always, ALWAYS set the length of your parameters:
@.BizName varchar(50),
@.BizAddress varchar(50) output
Do not depend on the default values,

> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
From online help:
If the ParameterDirection is output, and execution of the associated
SqlCommand does not return a value, the SqlParameter contains a null value.
So I would guess that the value of @.BizName is not getting set. Use SQL
Profiler to verify this.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks Dan. Stating the size in the stored procedure did the trick. Cheers.
Nab
"Dan Guzman" wrote:

> A NULL @.BizAddress value will be returned when no data is found and this
> cannot be converted to a .Net string data type. You can check for NULL
> using DbNull.Value:
> If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
> MessageBox.Show("BizName not found")
> Else
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> End If
> Also, you need specify varchar(50) in your stored procedure parameter
> declaration. The default length is 1.
> CREATE PROCEDURE GetAddress
> @.BizName varchar(50),
> @.BizAddress varchar (50) output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>
>

BDE Connection via ODBC to MS SQL Server fails

Hi,

I have a strange problem accessing a MS SQL Database from my network.
I'm using an application that is based on BDE which uses ODBC to connect to a MS SQL DB.
When I tried to connect from the network with BDE,I got this error message:

BDE Error : 13059
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

On the server side I'm using W2003 EE ,MS SQL 2000 Standard with SP4, BDE5.11.
On the client side it's an XP SP2 with MS SQL Client installed, same BDE 5.11
When I create an alias using ODBC and mapping to my server, it connects and the communication with the Database Server succeds:
TESTS COMPLETED SUCCESSFULLY!
When I'm trying to connect using BDE Admin and double clicking on the + sign of the alias I created usind ODBC admin I get the message I listed above.
Any help would be appreciated,

Thanks.May check this http://support.plato.com/kb/tip.asp?psid=23252 link fyi.|||Thank you for replying me...in the meantime i solved the problem.
It was the incorrect settings in the registry for BDE. After erasing the keys from the registry and having reinstalled the BDE everything worked fine.
There are some files used by BDE when connecting to SQL server which are not installed when using Delphi 5 or later :(

However thank you for the usefull link.

best regards,
Sanyi|||Glad the issue is resolved and appreciate your feedback on posting the solution, that helps.