カテゴリー: SQL

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