Enviar

MailMessage theMailMessage = new MailMessage("from@email.com", "to@email.com");
theMailMessage.Body = "body email message here";
theMailMessage.Attachments.Add(new Attachment("pathToEmailAttachment"));
theMailMessage.Subject = "Subject here";
SmtpClient theClient = new SmtpClient("IP.Address.Of.Smtp");
theClient.UseDefaultCredentials = false;

System.Net.NetworkCredential theCredential = new System.Net.NetworkCredential("user@name.com", "password");

theClient.Credentials = theCredential;

theClient.Send(theMailMessage);

Primeiro dia do Mês

Exemplo:

Minimum ( {sp_rpt_listagem_Mapa_Fact;1.datai} )

Último dia do Mês (novas formas)

Uma forma:

• Global datetimevar firstDate:= datetime(year(CurrentDateTime), month(CurrentDateTime),1,0,0,0) ;
• Global datetimevar lastMonth:= dateadd("m",+0,firstDate); lastMonth:=dateadd("d",-1, lastMonth);

Outra forma:

If {table.date} in LastFullMonth Then
{table.date}
Else
0
Its Returns the Value if the date falls on any date from the previous month.
if today is 19/07/2007 its returns 31/07/2007

Último dia do Mês

Calculating the first of a target month is done differently depending on whether the target month is the current month or the month of a date field. If you are looking for the current month you can use: Minimum ( MonthToDate )This will give you the first day of the current month in any report. Not everyone realizes this but you can take the Minimum or Maximum of any one of the 27 date range functions Crystal provides. However, if the date is a database field, a formula or a parameter you need a different syntax. I use:

{Table.Date} - Day ( {Table.Date} ) + 1

The {Table.Date} field can be replaced with any Date value. If your field is a DateTime value you can add the DATE() function around the whole calculation to strip off the time:

DATE ( {Table.Date} - Day ( {Table.Date} ) + 1 )

Finding the last day of a month can be done several ways but I think the one below is the simplest. It can be used with any date value including the CurrentDate function:

DateAdd ('m' , 1 , {Orders.Order Date} - day({Orders.Order Date}) + 1) - 1

You will notice that the first formula is used within this one. I find the first of the target month, then use DateAdd to add one month and find the first of the following month. Finally I subtract 1 day to get the last day of the month. Note that DateAdd always returns a DateTime value. As above, you can use the DATE() function around this formula to strip off the time. These calculations are especially handy when you need rolling date columns that are all derived from a parameter date. By adding and subtracting more months you can calculate the first and last date of a whole range of months.

Optimização da função das Datas

Optimização feita pelo meu BOSS (Afonso Netsigma) --- ;-) Fixola

-- Retorna data como string
RETURNS nvarchar(25)ASBEGIN DECLARE @Datafmt nvarchar(25)
-- Verifica se a data é válida
IF @data Is Null SET @Datafmt = '' -- dia-mes-ano
ELSE IF @formato = 2
BEGIN set @Datafmt=convert(nvarchar(10),@data,105)
END -- somente dia
ELSE IF @formato = 3
BEGIN set @Datafmt=convert(nvarchar(2),@data,105)
END -- somente mês
ELSE IF @formato = 4
BEGIN set @Datafmt=convert(nvarchar(2),@data,101)
END -- somente Ano
ELSE IF @formato = 5 BEGIN set @Datafmt=convert(nvarchar(4),@data,112) END
-- mes/dia/ano
ELSE IF @formato = 6 BEGIN set @Datafmt=convert(nvarchar(10),@data,101) END -- mes-dia-ano ELSE IF @formato = 7 BEGIN set @Datafmt=convert(nvarchar(10),@data,110) END -- Formato Longo ELSE IF @formato = 8 BEGIN SET @Datafmt =Convert(varchar(2), @data,105) SET @Datafmt = @Datafmt + ' de ' -- concatena o mês SET @Datafmt = @Datafmt + CASE Month(@data) WHEN 1 THEN 'Janeiro' WHEN 2 THEN 'Fevereiro' WHEN 3 THEN 'Março' WHEN 4 THEN 'Abril' WHEN 5 THEN 'Maio' WHEN 6 THEN 'Junho' WHEN 7 THEN 'Julho' WHEN 8 THEN 'Agosto' WHEN 9 THEN 'Setembro' WHEN 10 THEN 'Outubro' WHEN 11 THEN 'Novembro' ELSE 'Dezembro' END SET @Datafmt = @Datafmt + ' de ' -- concatena o ano SET @Datafmt = @Datafmt + convert(nvarchar(4), @data, 112) END -- Formato Curto ELSE IF @formato = 9 BEGIN set @Datafmt=convert(nvarchar(11),@data,106) END -- mês/ano ELSE IF @formato = 10 BEGIN set @Datafmt=convert(nvarchar(11),@data,106) set @Datafmt=substring(@Datafmt,4,3)+'/'+substring(@Datafmt,8,4) END -- mm/yyyy ELSE IF @formato = 11 BEGIN set @Datafmt=convert(nvarchar(10),@data,103) set @Datafmt=substring(@Datafmt,4,7) END -- dd/mm/yyyy hh:mm (24h) ELSE IF @formato = 12 BEGIN set @Datafmt=convert(nvarchar(10),@data,103) + ' ' + convert(nvarchar(5),@data,108) END
-- dd/mm/yyyy (Default) = 1
ELSE
BEGIN set @Datafmt=convert(nvarchar(10),@data,103)
END
RETURN(@Datafmt)END

Cálculo de diferenças de Datas

SELECT DATEDIFF(day, '2007-04-01','2007-06-04' ) AS no_of_Dias

SELECT DATEDIFF(month, '2007-04-01','2007-06-04' ) AS no_of_Meses

SELECT DATEDIFF(year, '2007-04-01','2007-06-04' ) AS no_of_Anos

Função (Script) para trabalhar com Datas

/* Este script permite criar a função dbo.fn_dateformat a qual possibilita a formatação de uma
data em até 12 formatos diferentes.
Coloque o script sobre o Query Analyzer e execute o script na base de dados onde deseja que
a função seja criada.
OBS: Se desejar, use este script como um template para desenvolver sua própria função.
*/
USE PUBS
GO
IF EXISTS (SELECT [name] FROM sysobjects WHERE id = object_id('dbo.fn_dateformat') and xtype='FN')
DROP FUNCTION dbo.fn_dateformat
GO
CREATE FUNCTION dbo.fn_dateformat (@data smalldateTime, @formato int)
/***************************************************************************
Esta função permite formatar uma data em um dos formatos abaixo.
Parâmetros:
@data - data a ser formatada
@formato - determina o formato de saída para a data
Opções de formato:
1 - dia/mes/ano ->> 31/08/2005 (Default)
2 - dia-mes-ano ->> 31-08-2005
3 - Somente dia ->> 31
4 - Somente Mês ->> 08
5 - Somente Ano ->> 2005
6 - mes/dia/ano ->> 08/31/2005
7 - mes-dia-ano ->> 08-31-2005
8 - Formato Longo ->> 31 de Agosto de 2005
9 - Formato Curto ->> 31-Agosto-2005
10 - Mês/Ano ->> Agosto/2005
11 - Mês/Ano ->> 08/2005
12 - Dia/Hora ->> 31-08-2005 13:14
Exemplo:SELECT dbo.fn_dateformat(getdate(),1) as [dia/mes/ano]
SELECT dbo.fn_dateformat(getdate(),10) as [Mes/Ano]
SELECT dbo.fn_dateformat(getdate(),12) as [Dia/Hora]
Autor: Nilton Pinheiro
Website: http://www.mcdbabrasil.com.br
Baseado no Original: http://www.sqlservercentral.com/scripts/contributions/1568.asp
*******************************************************************************/
-- Retorna data como string
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @Datafmt nvarchar(20)
-- Verifica se a data é válida
IF @data Is Null SET @Datafmt = ''
-- dia-mes-ano
ELSE IF @formato = 2
BEGIN
IF Day(@data) < datafmt =" '0'" datafmt =" Convert(varchar(2),Day(@data))" datafmt =" @Datafmt" datafmt =" (@Datafmt" datafmt =" (@Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 3" datafmt =" ('0'" datafmt =" (Convert(varchar(2)," formato =" 4" datafmt =" '0'" datafmt =" Convert(varchar(2)," formato =" 5" datafmt =" (SELECT" formato =" 6" datafmt =" '0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" (@Datafmt" datafmt =" (@Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 7" datafmt =" '0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" (@Datafmt" datafmt =" (@Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 8" datafmt =" ('0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 9" datafmt =" ('0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 10" datafmt =" CASE" datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 11" datafmt =" '0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" @Datafmt" formato =" 12" datafmt =" ('0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" (@Datafmt" datafmt =" (@Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt" datafmt =" ('0'" datafmt =" Convert(varchar(2)," datafmt =" @Datafmt" datafmt =" (@Datafmt" datafmt =" (@Datafmt" datafmt =" @Datafmt" datafmt =" @Datafmt">

Datas 1º dia e último do Mês

--1. Achar o 1º dia do Mês:
select dateadd(dd,-(day('2007-04-11')-1),'2007-04-11')
--2. O último dia do Mês:
select dateadd(dd,-(day(dateadd(mm,1,'2007-04-11'))),dateadd(mm,1,'2007-04-11'))

Invocar Objectos através de Strings

Criar uma instância de um objecto dinamicamente:
frm = DirectCast(Activator.CreateInstance(Type.GetType("Nome_do_Assembly." & Trim(Form))), Form)


Aceder às propriedades (ou qualquer outro elemento) de um Objecto:
frm.GetType.GetProperty("Nome_daPropriedade").GetValue(frm, Nothing)

Função para aceder ao FRM:

Public Shared Function GetObjectByName(ByVal Nome As String) As Object
Dim arOfTypes As Type()
'Array de Tipos de cada assembly
Dim objAssembly As System.Reflection.Assembly
objAssembly = System.Reflection.Assembly.Load("Netsigma.GRLReports")
arOfTypes = objAssembly.GetTypes
'For Each type in arOfTypes get Properties,Methods and Events
For Each T As Type In arOfTypes
Try
Dim s As Object = Activator.CreateInstance(T)
If T.Name.Equals(Nome) Then
Return s
End If
Catch ex As Exception
End Try
Next
Return Nothing
End Function

Gerar Random

Public Function RandomNumber(ByVal MaxNumber As Integer, Optional ByVal MinNumber As Integer = 0) As Integer
'initialize random number generator
Dim r As New Random(System.DateTime.Now.Millisecond)
'if passed incorrect arguments, swap them
'can also throw exception or return 0
If MinNumber > MaxNumber Then
Dim t As Integer = MinNumber
MinNumber = MaxNumber
MaxNumber = t
End If
Return r.Next(MinNumber, MaxNumber)
End Function

Executar Processo e Esperar

Public Sub ShellandWait(ByVal ProcessPath As String)
Dim objProcess As System.Diagnostics.Process
Try
objProcess = New System.Diagnostics.Process()
objProcess.StartInfo.FileName = ProcessPath
objProcess.StartInfo.WindowStyle = ProcessWindowStyle.Normal
objProcess.Start()

'Wait until the process passes back an exit code
objProcess.WaitForExit()
'Free resources associated with this process
objProcess.Close()
Catch
MessageBox.Show("Could not start process " & ProcessPath, "Error")
End Try
End Sub

Diferença entre DATAS

Static t_inicio As DateTime
Static t_fim As DateTime
Dim t_diferenca As TimeSpan
t_inicio = Now
' t_fim = Now
't_diferenca = t_fim.Subtract(t_inicio)
' MsgBox("Tempo de carregar dados (dataset)" & t_diferenca.TotalSeconds.ToString("0.000000") & " segundos ->Registos:" & dsBD.Movimentos.Rows.Count.ToString)

Converter BMP to ANY

Public Function ConvertBMP(ByVal BMPFullPath As String, ByVal imgFormat As ImageFormat) As Boolean
Dim bAns As Boolean
Dim sNewFile As String
Try 'bitmap class in system.drawing.imaging
Dim objBmp As New Bitmap(BMPFullPath)
'below 2 functions in system.io.path
sNewFile = GetDirectoryName(BMPFullPath)
sNewFile &= GetFileNameWithoutExtension(BMPFullPath)
sNewFile &= "." & imgFormat.ToString
objBmp.Save(sNewFile, imgFormat)
bAns = True 'return true on success
Catch bAns = False 'return false on error
End Try
Return bAns
End Function
'USAGE 'ConvertBMP("C:\test.bmp", ImageFormat.Jpeg)
'ConvertBMP("C:\test.bmp", ImageFormat.Emf)
'ConvertBMP("C:\test.bmp", ImageFormat.Exif)
'ConvertBMP("C:\test.bmp", ImageFormat.Gif)
'ConvertBMP("C:\test.bmp", ImageFormat.Icon)
'ConvertBMP("C:\test.bmp", ImageFormat.MemoryBmp)
'ConvertBMP("C:\test.bmp", ImageFormat.Png)
'ConvertBMP("C:\test.bmp", ImageFormat.Tiff)
'ConvertBMP("C:\test.bmp", ImageFormat.Wmf)

Calcula Idade

Public Function Calcula_Idade(ByVal Birthdate As System.DateTime, Optional ByVal AsOf As System.DateTime = #1/1/1700#) As String
Dim iMonths As Integer
Dim iYears As Integer
Dim dYears As Decimal
Dim lDayOfBirth As Long
Dim lAsOf As Long
Dim iBirthMonth As Integer
Dim iAsOFMonth As Integer
If AsOf = "#1/1/1700#" Then AsOf = DateTime.Now
End If
lDayOfBirth = DatePart(DateInterval.Day, Birthdate)
lAsOf = DatePart(DateInterval.Day, AsOf)
iBirthMonth = DatePart(DateInterval.Month, Birthdate)
iAsOFMonth = DatePart(DateInterval.Month, AsOf)
iMonths = DateDiff(DateInterval.Month, Birthdate, AsOf)
dYears = iMonths / 12
iYears = Math.Floor(dYears)
If iBirthMonth = iAsOFMonth Then
If lAsOf <>
iYears = iYears - 1
End If
End If
Return iYears
End Function

Gravar um Byte Array para Ficheiro

Dim byteData() As Byte
...
' Create a file and write the byte data to a file.
Dim oFileStream As System.IO.FileStream
oFileStream = New System.IO.FileStream("bytes.dat", System.IO.FileMode.Create)
oFileStream.Write(byteData, 0, byteData.Length - 1)
oFileStream.Close()

Algoritmo BubbleSort

Public Function BubbleSortArray(ByVal NumericArray As Variant) _ As Variant
'RETURNS Array, or vbEmpty if there's an error'e.g., passed array contains elements that'can't be compared to each other, such as'objects
'will work when elements are all numbers or single characters'of the same case.
Dim vAns As VariantDim vTemp As VariantDim bSorted As BooleanDim lCtr As LongDim lCount As LongDim lStart As LongDim lPass as long
vAns = NumericArray If Not IsArray(vAns) Then
BubbleSortArray = vbEmpty
Exit Function
End If
On Error GoTo ErrorHandler
lStart = LBound(vAns)l
Count = UBound(vAns)lPass=0
bSorted = False
Do While Not bSorted
bSorted = True
For lCtr = lCount - 1 To lStart + lPass Step -1
If vAns(lCtr + 1) <>
DoEvents
bSorted = False
vTemp = vAns(lCtr)
vAns(lCtr) = vAns(lCtr + 1)
vAns(lCtr + 1) = vTemp
End If
Next lCtr
lPass = lPass + 1
Loop BubbleSortArray = vAns
Exit Function
ErrorHandler:BubbleSortArray = vbEmpty
Exit Function
End Function

Conversão Decimal para Hexadecimal

string DecimalToBase(int iDec, int numbase) { string strBin = ""; int[] result = new int[32]; int MaxBit = 32; for(; iDec > 0; iDec/=numbase) { int rem = iDec % numbase; result[--MaxBit] = rem; } for (int i=0;i= base10) strBin += cHexa[(int)result.GetValue(i)%base10]; else strBin += result.GetValue(i); strBin = strBin.TrimStart(new char[] {'0'}); return strBin; }

int BaseToDecimal(string sBase, int numbase) { int dec = 0; int b; int iProduct=1; string sHexa = ""; if (numbase > base10) for (int i=0;i=0; i--,iProduct *= numbase) { string sValue = sBase[i].ToString(); if (sValue.IndexOfAny(cHexa) >=0) b=iHexaNumeric[sHexa.IndexOf(sBase[i])]; else b= (int) sBase[i] - asciiDiff; dec += (b * iProduct); } return dec; }

Connections - PARADOX

• ODBC
  5.X:
  Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;CollatingSequence=ASCII"
  7.X:
  "Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\myDb;DefaultDir=C:\myDb;DriverId=538;FIL=Paradox 7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"

• OleDbConnection (.NET)
  Standard
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDb;Extended Properties=Paradox 5.x;"

Connections - POSTGRESQL

• Core Labs PostgreSQLDirect (.NET)
  Standard:
  "User ID=root; Password=pwd; Host=localhost; Port=5432; Database=testdb;Pooling=true; Min Pool Size=0; Max Pool Size=100; Connection Lifetime=0"

Connections - MIMER SQL E LIGHTBASE

• MIMER SQL
  ODBC
  Standard Security:
  "Driver={MIMER};Database=mydb;Uid=myuser;Pwd=mypw;"
  Prompt for username and password:"Driver={MIMER};Database=mydb;"

• LIGHTBASE

Standard
Standard:"user=USERLOGIN;password=PASSWORD;UDB=USERBASE;server=SERVERNAME"

Connections - INGRES

• ODBC
  DSN-less
  "Provider=MSDASQL.1;DRIVER=Ingres;SRVR=xxxxx;DB=xxxxx;Persist Security Info=False;uid=xxxx;pwd=xxxxx;SELECTLOOPS=N;Extended Properties="""SERVER=xxxxx;DATABASE=xxxxx;SERVERTYPE=INGRES""

Connections - INFORMIX

• ODBC
  Informix 3.30:
  "Dsn='';Driver={INFORMIX 3.30 32 BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd
  Informix-CLI 2.5:
  "Driver={Informix-CLI 2.5 (32 Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"

• OLE DB
  IBM Informix OLE DB Provider:
  "Provider=Ifxoledbc.2;password=myPw;User ID=myUser;Data Source=dbName@serverName;Persist Security Info=true"

Connections - SYBASE

• ODBC
  Standard Sybase System 12 (or 12.5) Enterprise Open Client:
  "Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"
  Standard Sybase System 11:
  "Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;"

• Intersolv 3.10:
  "Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"
  Sybase SQL Anywhere (former Watcom SQL ODBC driver):
  "ODBC; Driver=Sybase SQL Anywhere 5.0; DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=username;Pwd=password;Dsn="""""
  Note! The two double quota following the DSN parameter at the end are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax. The empty DSN parameter is indeed critical as not including it will result in error 7778.

• OLE DB
  Adaptive Server Anywhere (ASA):
  "Provider=ASAProv;Data source=myASA"
  
  Adaptive Server Enterprise (ASE) with Data Source .IDS file:
  "Provider=Sybase ASE OLE DB Provider; Data source=myASE"
  Note that you must create a Data Source .IDS file using the Sybase Data Administrator. These .IDS files resemble ODBC DSNs.
  Adaptive Server Enterprise (ASE):
  "Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000;Catalog=myDBname;User Id=username;Password=password" - some reports on problem using the above one, try the following as an alternative -"Provider=Sybase.ASEOLEDBProvider;Server Name=myASEserver,5000;Initial Catalog=myDBname;User Id=username;Password=password"
  This one works only from Open Client 12.5 where the server port number feature works, allowing fully qualified connection strings to be used without defining any .IDS Data Source files.

• AseConnection (.NET)
  Standard:
  "Data Source='myASEserver';Port=5000;Database='myDBname';UID='username';PWD='password';"
  Declare the AseConnection:
  C#:using Sybase.Data.AseClient;AseConnection oCon = new AseConnection();oCon.ConnectionString="my connection string";oCon.Open();
  VB.NET:Imports System.Data.AseClientDim oCon As AseConnection = New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()

Connections - IBM DB2

• OLE DB, OleDbConnection (.NET) from ms TCP/IP:
  "Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"
  APPC:
  "Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"

• IBM's OLE DB Provider (shipped with IBM DB2 UDB v7 or above)
  TCP/IP:
  Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50000;uid=myUserName;pwd=myPwd;

• ODBC
  Standard:
  "driver={IBM DB2 ODBC DRIVER};Database=myDbName;hostname=myServerName;port=myPortNum;protocol=TCPIP; uid=myUserName; pwd=myPwd"

Connections - INTERBASE

• ODBC, Easysoft
  Local computer:
  "Driver={Easysoft IB6 ODBC};Server=localhost;Database=localhost:C:\mydatabase.gdb;Uid=username;Pwd=password"
  Remote Computer:
  "Driver={Easysoft IB6 ODBC};Server=ComputerName;Database=ComputerName:C:\mydatabase.gdb;Uid=username;Pwd=password"

• ODBC, Intersolv
  Local computer:
  "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=localhost;Database=localhost:C:\mydatabase.gdb;Uid=username;Pwd=password"
  Remote Computer:
  "Driver={INTERSOLV InterBase ODBC Driver (*.gdb)};Server=ComputerName;Database=ComputerName:C:\mydatabase.gdb;Uid=username;Pwd=password"

• OLE DB, SIBPROvider
  Standard:
  "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey"
  Specifying character set:
  "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey;character set=ISO8859_1"
  Specifying role:
  "provider=sibprovider;location=localhost:;data source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password=masterkey;role=DIGITADORES"
  Network article http://community.borland.com/article/0,1410,27152,00.html

Connections - MySQL

• ODBC
  ODBC 2.50 Local database:
  "Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"
  ODBC 2.50 Remote database:
  "Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
  ODBC 3.51 Local database:
  "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"

• ODBC 3.51 Remote database:
  "DRIVER={MySQL ODBC 3.51 Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase; USER=myUsername;PASSWORD=myPassword;OPTION=3;"

• OLE DB, OleDbConnection (.NET)
  Standard:
  "Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"

• MySqlConnection (.NET)
  eInfoDesigns.dbProvider:
  "Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"
  This one is used with eInfoDesigns dbProvider, an add-on to .NET
  Declare the MySqlConnection:
  C#:using eInfoDesigns.dbProvider.MySqlClient;MySqlConnection oMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString = "my connectionstring";oMySqlConn.Open();
  VB.NET:Imports eInfoDesigns.dbProvider.MySqlClientDim oMySqlConn As MySqlConnection = New MySqlConnection()oMySqlConn.ConnectionString = "my connectionstring"oMySqlConn.Open()

• SevenObjects MySqlClient (.NET)
  Standard:
  "Host=server; UserName=myusername; Password=mypassword;Database=mydb;"
  This is a freeware ADO.Net data provider from SevenObjects

• Core Labs MySQLDirect (.NET)
  Standard:
  "User ID=root; Password=pwd; Host=localhost; Port=3306; Database=test;Direct=true; Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0"
  Read more at Core Lab and the product page.

Connections - ORACLE

• ODBC
  New version:
  "Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
  Old version:
  "Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

• OLE DB, OleDbConnection (.NET)
  Standard security:
  "Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
  This one's from Microsoft, the following are from Oracle
  Standard Security:
  "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"
  Trusted Connection:
  "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"

• OracleConnection (.NET)
  Standard:
  "Data Source=MyOracleDB;Integrated Security=yes;"
  This one works only with Oracle 8i release 3 or later
  Specifying username and password:
  "Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"
  This one works only with Oracle 8i release 3 or later
  Declare the OracleConnection:
  C#:using System.Data.OracleClient;OracleConnection oOracleConn = new OracleConnection();oOracleConn.ConnectionString = "my connectionstring";oOracleConn.Open();
  VB.NET:Imports System.Data.OracleClientDim oOracleConn As OracleConnection = New OracleConnection()oOracleConn.ConnectionString = "my connectionstring"oOracleConn.Open()
  Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>
  Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner

• Core Labs OraDirect (.NET)
  Standard:"User ID=scott; Password=tiger; Host=ora; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0"
  

• Data Shape
  MS Data Shape:"Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;user id=username;password=mypw"
  Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping >>

Connections - ACCESS

• ODBC
  Standard Security:
  "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
  Workgroup:
  "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;"
  Exclusive:
  "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

• OLE DB, OleDbConnection (.NET)
  Standard security:
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"
  Workgroup (system database):
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"
  With password:
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"

Connections - SQL SERVER

• ODBC
  
  Standard Security:
  "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
  Trusted connection:
  "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
  Prompt for username and password:
  oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

• OLE DB, OleDbConnection (.NET)
  Standard Security:
  "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
  Trusted Connection:
  "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
  (use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
  Prompt for username and password:
  oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
  Connect via an IP address:
  "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
  (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))

• SqlConnection (.NET)
  
  Standard Security:
  "Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" - or -"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False" (booth connection strings produces the same result)
  Trusted Connection:
  "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" - or -"Server=Aron1;Database=pubs;Trusted_Connection=True;" (booth connection strings produces the same result)
  (use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
  Connect via an IP address:
  "Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
  (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
  Declare the SqlConnection:
  C#:using System.Data.SqlClient;SqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString="my connectionstring";oSQLConn.Open();
  VB.NET:Imports System.Data.SqlClientDim oSQLConn As SqlConnection = New SqlConnection()oSQLConn.ConnectionString="my connectionstring"oSQLConn.Open()
  Data Shape
  MS Data Shape"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
  Want to learn data shaping? Check out 4GuyfFromRolla's great article about Data Shaping >>
  How to define which network protocol to use
  Example:"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
  Name : Network library
  dbnmpntw : Win32 Named Pipes
  dbmssocn : Win32 Winsock TCP/IP
  dbmsspxn : Win32 SPX/IPX
  dbmsvinn : Win32 Banyan Vines
  dbmsrpcn : Win32 Multi-Protocol (Windows RPC)
• Important Note! When connecting through the SQLOLEDB provider use the syntax Network Library=dbmssocnand when connecting through MSDASQL provider use the syntax Network=dbmssocn

(Fonte: www. Connectionstrings.html)

Formatar Datas em SQL

SET DATEFORMAT mdyGODECLARE @datevar datetimeSET @datevar = '12/31/98'SELECT @datevarGO
SET DATEFORMAT ydmGODECLARE @datevar datetimeSET @datevar = '98/31/12'SELECT @datevarGO
SET DATEFORMAT ymdGODECLARE @datevar datetimeSET @datevar = '98/12/31'SELECT @datevarGO

Style ID Style Type 0 or 100 mon dd yyyy hh:miAM (or PM) 101 mm/dd/yy 102 yy.mm.dd 103 dd/mm/yy 104 dd.mm.yy 105 dd-mm-yy 106 dd mon yy 107 Mon dd, yy 108 hh:mm:ss 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) 110 mm-dd-yy 111 yy/mm/dd 112 yymmdd 13 or 113 dd mon yyyy hh:mm:ss:mmm(24h) 114 hh:mi:ss:mmm(24h) 20 or 120 yyyy-mm-dd hh:mi:ss(24h) 21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h) 126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces) 130 dd mon yyyy hh:mi:ss:mmmAM 131 dd/mm/yy hh:mi:ss:mmmAM

declare @datevar datetime select @datevar = getdate()
/*Example for getdate() : getting current datetime*/ select getdate() [Current Datetime]
/*Example for dateadd : getting date 7 days from current datetime*/select dateadd(dd, 7, @datevar) [Date 7 days from now]
/*Example for datediff : getting no of days passed since 01-01-2004*/select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]
/*Example for datename : getting month name*/select datename(mm, @datevar) [Month Name]
/*Example for datepart : getting week from date*/select datepart(wk, @datevar ) [Week No]
/*Example for day : getting day part of date*/select day (@datevar) [Day]
/*Example for month : getting month part of date*/select month(@datevar) [Month]
/*Example for year : getting year part of date*/select year(@datevar) [Year]

A seguir apresento alguns códigos de exemplo:

1. Achar o 1º dia do Mês:
select dateadd(dd,-(day(DateColumn)-1),DateColumn)
2. O último dia do Mês:
select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn))