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"))
    {
        cnn.Open();
        // 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)
                    cmd.Parameters.Add(param);
            }
            // Define the data adapter and fill the dataset
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        } 
    }
    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")
        cnn.Open()
        ' 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
                    cmd.Parameters.Add(param)
                Next
            End If
            ' Define the data adapter and fill the dataset
            Using da As New SqlDataAdapter(cmd)
                dt = New DataTable
                da.Fill(dt)
            End Using
        End Using
    Return dt
  End Function
End Class

No comments:

Post a Comment