C#, VB.netでSQL Serverに接続する簡単な例。
C#
//SQL Serverへの接続
private int SQLExec()
{
try
{
var res = 0;
var connectionString = GetConnectionString();
var queryString = “SELECT count(*) FROM ～”;
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(queryString, cn))
{
cn.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 120;
res = (int)cmd.ExecuteScalar();
}
return res;
}
catch (Exception ex)
{
var ErrCaption = Application.ProductName + “(” + System.Reflection.MethodBase.GetCurrentMethod().Name + “)”;
Debug.WriteLine(ErrCaption + ” ” + ex.Message);
return -1;
}
}
//接続文字列作成 Windows認証
private string GetConnectionString()
{
var builder = new SqlConnectionStringBuilder()
{
IntegratedSecurity = true, // Windows認証
InitialCatalog = “DB名”,
DataSource = “(local)”
};
return builder.ToString();
}
//接続文字列作成 SQL Server認証
private string GetConnectionString2()
{
var builder = new SqlConnectionStringBuilder()
{
IntegratedSecurity = false, // SQL Server認証
InitialCatalog = “DB名”,
DataSource = “(local)”,
UserID = “ユーザー名”,
Password = “パスワード”
};
return builder.ToString();
}
VB.net
‘SQL Serverへの接続
Private Function SQLExec() As Integer
Try
Dim Res As Integer = 0
Dim connectionString As String = GetConnectionString()
Dim queryString As String = “SELECT count(*) FROM ～”
Using cn As New SqlConnection(connectionString)
Using cmd As New SqlCommand(queryString, cn)
cn.Open()
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 120
Res = cmd.ExecuteScalar
End Using
End Using
Return Res
Catch ex As Exception
Dim ErrCaption As String = My.Application.Info.ProductName & “(” & Reflection.MethodBase.GetCurrentMethod.Name & “)”
Debug.WriteLine(ErrCaption + ” ” + ex.Message)
Return -1
End Try
End Function
‘接続文字列作成 Windows認証
Private Function GetConnectionString() As String
Dim builder = New SqlConnectionStringBuilder With {
.IntegratedSecurity = True, ‘Windows認証
.InitialCatalog = “DB名”,
.DataSource = “(local)”
}
Return builder.ToString
End Function
‘接続文字列作成 SQL Server認証
Private Function GetConnectionString2() As String
Dim builder = New SqlConnectionStringBuilder With {
.IntegratedSecurity = False, ‘SQL Server認証
.InitialCatalog = “DB名”,
.DataSource = “(local)”,
.UserID = “ユーザー名”,
.Password = “パスワード”
}
Return builder.ToString
End Function