Imports System.Data.SqlClient
Imports System.Web.Configuration
'System.Web.HttpContext.Current.Response.End()
'string str = "../Erro.aspx?msg='" + ex.Message + "'"
'System.Web.HttpContext.Current.Response.Redirect(str,true)
'return null
Module mdlMSSQLSRV
'notas sobre os strings de conexão com o banco de dados
'eles podem ser definidos em diversos locais por motivos de segurança
'
'Se for inserir no web config seve ser colocado logo após </appSettings>
'name é o nome da conexao
'connectionString é o string de conexão
'<connectionStrings>
'<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
'<add name="SQLPROD" connectionString="Server=Servidor; Database=BaseDados; UID=Usuario;PWD=Senha" providerName="System.Data.SqlClient" />
'<add name="SQLTESTES" connectionString="Server=Servidor; Database=BaseDados; UID=Usuario;PWD=Senha" providerName="System.Data.SqlClient" />
'<add name="SQLCON3" connectionString="Server=Servidor; Database=BaseDados; UID=Usuario;PWD=Senha" providerName="System.Data.SqlClient" />
'<add name="SQLCON4" connectionString="Server=Servidor; Database=BaseDados; UID=Usuario;PWD=Senha" providerName="System.Data.SqlClient" />
'<add name="ExcelCon" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|teste.xls;Extended Properties=Excel 12.0" />
'</connectionStrings>
'para ler o string de conexão da session
'Public cnstr As String = System.Web.HttpContext.Current.Session("CONEXAO").ToString()
Public cnstr As String = "" 'string de conexão com o banco de dados
Public cncn As SqlConnection 'conexão com o banco de dados
Public Erro As String = "" 'troca de mensagens de erro
''' <summary>
''' Definindo o string de conexão com o banco de dados que dependente da url de acesso do site
''' </summary>
Public Function ObtemStrConexao() As String
Dim cnstr As String = ""
'p=produção
Dim srvp As String = ""
Dim bdp As String = ""
Dim usup As String = ""
Dim senhap As String = ""
't=teste
Dim srvt As String = ""
Dim bdt As String = ""
Dim usut As String = ""
Dim senhat As String = ""
'definindo o acesso ao bd pela url do site - salvando na session
Dim strurl As String = My.Request.Url.AbsoluteUri
strurl = strurl.ToUpper()
If ((strurl.IndexOf("LOCALHOST") <> 0) Or (strurl.IndexOf("TEST") <> 0)) Then
'Servidores Local ou de teste
System.Web.HttpContext.Current.Session.Add("SERVIDOR", "TESTES")
'lendo do web config
'cnstr = WebConfigurationManager.ConnectionStrings("SQLTESTES").ConnectionString
'definindo o str conexão aqui mesmo
cnstr = "Server=" + srvp + "; Database=" + bdp + "; UID=" + usup + ";PWD=" + senhap + " providerName=System.Data.SqlClient/>"
System.Web.HttpContext.Current.Session.Add("CONEXAO", cnstr)
Else
'Servidor de produção
System.Web.HttpContext.Current.Session.Add("SERVIDOR", "PRODUCAO")
'lendo do web config
'cnstr = WebConfigurationManager.ConnectionStrings("SQLPROD").ConnectionString
'definindo o str conexão aqui mesmo
cnstr = "Server=" + srvt + "; Database=" + bdt + "; UID=" + usut + ";PWD=" + senhat + " providerName=System.Data.SqlClient/>"
System.Web.HttpContext.Current.Session.Add("CONEXAO", cnstr)
End If
Return cnstr
End Function
''' <summary>
''' conecta com o banco de dados - define str conexão
''' </summary>
Public Sub MS_SQL_SRV_CON()
Erro = ""
Try
If (cncn.State = ConnectionState.Closed) Then
ObtemStrConexao()
'cnstr = Session("CONEXAO").ToString()
cncn.ConnectionString() = cnstr
cncn.Open()
Return
End If
Catch ex As SqlException
Erro = ex.Message
End Try
End Sub
''' <summary>
'''faz a pesquisa e retorna um DataSet
''' </summary>
Public Function MS_SQL_SRV_DS(query As String) As DataSet
Erro = ""
Try
MS_SQL_SRV_CON()
Dim da As New SqlDataAdapter(query, cncn)
Dim ds As New DataSet
da.Fill(ds)
cncn.Close()
Return ds
Catch ex As SqlException
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
''' <summary>
'''executa um sql que não retorna nada
''' </summary>
Public Sub MS_SQL_SRV_EX(query As String)
Erro = ""
Try
MS_SQL_SRV_CON()
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandTimeout = 20000
cmd.ExecuteNonQuery()
cncn.Close()
Catch ex As Exception
Erro = ex.Message
'Throw ex
End Try
End Sub
''' <summary>
'''executa um sql que retorna um inteiro
''' </summary>
Public Function MS_SQL_SRV_INT(query As String) As Integer
Erro = ""
Try
MS_SQL_SRV_CON()
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Return Nothing 'erro
Erro = ex.Message
'Throw ex
End Try
End Function
''' <summary>
''' executa um sql que retorna um String
''' </summary>
''' <param name="query"></param>
''' <returns></returns>
Public Function MS_SQL_SRV_STR(query As String) As String
Erro = ""
Try
MS_SQL_SRV_CON()
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
''' <summary>
''' executa um sql que retorna um single(sql)
''' </summary>
''' <param name="query"></param>
''' <returns></returns>
Public Function MS_SQL_SRV_Single(query As String)
Erro = ""
Try
MS_SQL_SRV_CON()
Dim cmd = New SqlCommand(query, cncn)
cmd.CommandText = query
Return cmd.ExecuteScalar()
Catch ex As Exception
Erro = ex.Message
Return Nothing 'erro
'Throw ex
End Try
End Function
''' <summary>
''' Executa uma procedure e retorna um dataset
''' Parametros = "P1,P2,P3,P4..."
''' </summary>
Public Function MS_SQL_SQL_Proc(Procedure As String, Parametros As String) As DataSet
Erro = ""
Try
Dim SQL As String = Procedure + " " + Parametros
Dim cmd As New SqlCommand(SQL, cncn)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
Return ds
Catch ex As Exception
Erro = ex.Message
Return Nothing
'Throw ex
End Try
End Function
End Module