Monday, March 19, 2012

sql sever connectivity with c# code

In C#:
public static class Dac
 public static DataTable ExecuteDataTable(string sqlStatement, 
                                     params SqlParameter[] arrParam)
    DataTable dt = new DataTable();
    // Open the connection
    using (SqlConnection cnn = new SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True"))
        // Define the command
        using (SqlCommand cmd = new SqlCommand())
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlStatement;
            // Handle the parameters
            if (arrParam != null)
                foreach (SqlParameter param in arrParam)
            // Define the data adapter and fill the dataset
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    return dt;
In VB:
Public Class Dac
  Public Shared Function ExecuteDataTable(ByVal sqlStatement _
        As String, _
        ByVal ParamArray arrParam() As SqlParameter) As DataTable
    Dim dt As DataTable
    ' Open the connection
    Using cnn As New SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True")
        ' Define the command
        Using cmd As New SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sqlStatement
            ' Handle the parameters
            If arrParam IsNot Nothing Then
                For Each param As SqlParameter In arrParam
            End If
            ' Define the data adapter and fill the dataset
            Using da As New SqlDataAdapter(cmd)
                dt = New DataTable
            End Using
        End Using
    Return dt
  End Function
End Class

No comments:

Post a Comment