Consulta con conexión DSNLess a MySql desde C#

using System.Data.Odbc;


string odbcconnstr = "Driver={MySQL ODBC 3.51 Driver};Server=servidor;Database=base;User=usuario;Password=password;Port=puerto;Option=4";

OdbcConnection conn;

conn = new OdbcConnection(odbcconnstr);
conn.Open();
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from tabla";
OdbcDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
   // codigo                   
}

conn.Close();

Exportar una tabla de SQL a Excel

Para obtener el string de conexión, ver este post

Sub ExportarSQL_A_Excel(sProducto, sPeriodo)
 Set conn = CreateObject("ADODB.Connection") 

 sNombreArch = "C:\temp\archivo.xlsx"
 
 conn.open "Provider=SQLNCLI.1;Persist Security Info=False;User ID=usuario;Password=password;Initial Catalog=nombreBase;Data Source=servidor\instancia"
  
 Set objCommand = CreateObject("ADODB.Command")
 Set objCommand.ActiveConnection = conn
 objCommand.CommandTimeout = 6000
 
  
 objCommand.CommandText = "SELECT * FROM tabla where condicion"
 
 Set rs = CreateObject("ADODB.recordset")
 Set rs = objCommand.Execute()
 
 
 Dim e
 Dim wb
 Dim sheet
 Dim sNombreArch
  
 Set e = CreateObject("Excel.Application")
  
 e.Workbooks.Add
 Set wb = e.ActiveWorkbook 
 Set sheet = wb.ActiveSheet
 
 nCantColumnas = rs.fields.count
 
 'Agrego los nombres de columnas
 For i = 0 to nCantColumnas - 1
  sheet.Range(Chr(64 + i + 1) & 1) = rs.fields(i).name
 Next
 
 sheet.Range("A2").CopyFromRecordset rs
  
 Set sheet = Nothing
 wb.SaveAs sNombreArch
 wb.Close
 Set wb = Nothing
 
 e.Quit
 Set e = Nothing
End Sub

Crear conexión DSNLess

This may not be as big of a deal for many of you that are strictly using the Reporting features of SMS 2003 but if you are looking to do more advanced reporting from external data sources you might want to create a DSNless connection. Normally you'll need to either create a DSN on a web server to get a SQL connection working or modify your global.asa file. I don't do either of these. Here's what to do (this example will assume you want to build a connection string to a SQL server):

1. Create a file called "connect.udl" on your desktop or wherever.

2. Right-Click it and select "Properties".

3. On the 'Provider' tab select "Microsoft OLE DB Provider for SQL Server"

4. Go to the 'Connection' tab and fill out the Server name, user name and password which you wish to create the connection with (service accounts only please and local SQL accounts are better to use for security purposes) and the database to access. Click the check box for "Allow saving password". I'm using the SA account for the purposes of this demonstration but I suggest that you do not!

5. Click 'Test Connection' and you should see a "Test Connection Succeeded" message if all the server and credential information has been filled out successfully.

6. Click 'OK' to save the information.

7. Open the connect.udl file with notepad and you should see the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=SAPassword;Persist Security Info=True;User ID=SA;Initial Catalog=Master;Data Source=SQLSERVER



Now you have built a successful connection string to use in an ASP page or VBScript to connect to a SQL server with. An example of connecting to an SMS database and grabbing all the records in the System_DATA table could be (in VBScript):

ConnStr = "SQLOLEDB.1;Password=SAPassword;Persist Security Info=True;User ID=SA;Initial Catalog=Master;Data Source=SQLSERVER"
SQL = "Select * From System_DATA"
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open Connstr
objRS.CursorLocation = 3
objRS.Open SQL, objConn, 1, 1

This example might help more for most when creating external ASP pages than VBScripts, but you get the idea.



Fuente: http://www.myitforum.com/articles/19/view.asp?id=8600