Do you use ODBC or OLEDB or another API?

N2IT

When you set up connection strings to applications from the databases which API do you use?

I'm pretty green however I have always used ODBC, but recently I have been using OLEDB and I like it so much better. When used with VBA the flexibility it so much greater. I build prototypes using Access and Excel a lot and now I am able to add additional interactive features to the dashboards, etc.

NotHackingYou

If connecting to SQL Server, my order of preference is SQLNCLI, OLE, ODBC. Don't know if Access supports SQLNCLI, but anything is better than JET.

N2IT

I apologize I connect to a SQL server database all the time. If I use access I am passing queries through to the real database

I usually use Excel however if the data set returns starts to get over a million rows then I have to leverage Access, that's the other reason why I use Access.

I've never heard of or used SQLNCLI. Can you tell me more about it, why you like it? I really like OLE now to be honest, but I am always up for suggestions.

NotHackingYou

I primarily use SQLNCLI (SQL Server Native Client) because it is the new standard. My understanding is that SQLOLEDB has been deprecated and won't be supported after SQL Server 2012. SQLNCLI is 'supposedly' faster and has 'more features', though I've never tested the performance difference myself or seen a table comparing feature-by-feature. I would use OLE or ODBC only if I needed to be able to easily change from, say, SQL Server or Oracle or another RDBMS.

This MSDN article might help clear things up.

N2IT

I've read a few articles this one I have no though. Thanks for following up I appreciate it.

N2IT

Carl I find that the native client is limited when working with VBA Excel and Access. I like using OLE, or ADO when working with databases and returning to Access or Excel. I find the querying in VBA makes more sense and I am able to build message boxes to pass variable tot he where clause in the VBA. Are you able to do that with SQLNCLI using C#?

NotHackingYou

Yes, but it's important to use parameterized queries, especially if you are using input from the user. This helps prevent SQL injection attacks. C# provides a special class to help with this, called SqlParamater. So you could do something like this:

//you have accepted intId as an input parameter and already sanitized it at some level to ensure it is an integer

//Normally you would get this from config or another resource, not hard code it
string connectionString = "Provider=SQLNCLI;Server=Server\Instance;Database=DBName;Uid=UserName;Pwd=Password;"


//set up the connection
using (SqlConnection conn = new SqlConnection(connectionString))
{

//set up the command object
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM customers WHERE id = @id";

//set up the parameter
SqlParameter idParam = new SqlParameter();
idParam.ParameterName = "id";
idParam.Value = intId;

//add the parameter to the command
command.Parameters.Add(idParam);

//execute the query and get the results
//Choose how you want to execute and retrieve your results here


}

N2IT

Carl sorry for the late reply.

I am reviewing your code I think I understand the premise.

In VBA you would declare the dimension then set it. In the select I would ' variable ' then build a message box after the SQL was scripted. Calling the the variable from the message box.

So when the procedure is ran the message box fires and then you have your input parameter.

NotHackingYou

That is a common way to do it, but what happens if, rather than a search parameter, the user inputs a single quote to terminate your SQL, types in some SQL of their own and then a comment char to terminate the rest of your SQL? This is called a SQL Injection attack.

This the reason for parameterization. It is much like encoding user input on a web site. Once the SQL is parameterized, the user's input is treated exactly as you specify.

In this case, you want to treat whatever is input as a string and not allow it to escape from the string context to the SQL command context. A SQLParameter object accomplishes this for you. This might not be a concern for you depending on your user base.

Try this SQL out to demonstrate:

CREATE TABLE #myTable(
num int,
name varchar(max)
)


CREATE TABLE #protectedData(
ssn varchar(max)
)


INSERT INTO #myTable (num, name)
VALUES (1, 'Foo')


INSERT INTO #protectedData (ssn)
VALUES('xxx-xx-xxxx')


SELECT * FROM #myTable
SELECT * FROM #protectedData


--Your intended query. The user tyes foo
SELECT * FROM #myTable WHERE name = 'foo'

Result:


--Now instead of foo the user types ' SELECT * FROM #protectedData --
SELECT * FROM #myTable WHERE name = 'foo ' SELECT * FROM #protectedData--'

A much scarier result:

(Edited to add images)