C# and VB.net Connectivity with Query String

C# and VB.net Connectivity with Query String


C# Example

//Execute Non Query for query string

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}


VB.NET Example

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub


//
C# Example SQLReader Example

private static void ReadOrderData(string connectionString)
{
    string queryString =
        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }

        // Call Close when done reading.
        reader.Close();
    }
}


VB.NET SQLReader Example


Private Sub ReadOrderData(ByVal connectionString As String)
    Dim queryString As String = _
        "SELECT OrderID, CustomerID FROM dbo.Orders;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
                reader(0), reader(1)))
        End While

        ' Call Close when done reading.
        reader.Close()
    End Using
End Sub



C# Connectivity for  ExecuteSchalar

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

VB.NET Connectivity for  ExecuteSchalar

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar)
        cmd.Parameters("@Name").Value = newName
        Try
            conn.Open()
            newProdID = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

    Return newProdID
End Function

C# Example


//Execute Non Query for query string

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}


VB.NET Example

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub


//C# Example SQLReader Example

private static void ReadOrderData(string connectionString)
{
    string queryString =
        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }

        // Call Close when done reading.
        reader.Close();
    }
}


VB.NET SQLReader Example


Private Sub ReadOrderData(ByVal connectionString As String)
    Dim queryString As String = _
        "SELECT OrderID, CustomerID FROM dbo.Orders;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
                reader(0), reader(1)))
        End While

        ' Call Close when done reading.
        reader.Close()
    End Using
End Sub



C# Connectivity for  ExecuteSchalar

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

VB.NET Connectivity for  ExecuteSchalar

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar)
        cmd.Parameters("@Name").Value = newName
        Try
            conn.Open()
            newProdID = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

    Return newProdID
End Function

To display the list of stored procedure containing text in Sql Server

To display the list of  stored procedure containing text in Sql Server


DECLARE @Search varchar(255) SET @Search='CNC_EmailResults_Other'
SELECT DISTINCT o.name AS Object_Name,o.type_desc  FROM sys.sql_modules  m    INNER JOIN sys.objects
o ON m.object_id=o.object_id     WHERE m.definition Like '%'+@Search+'%'     ORDER BY 2,1

To find the text in the stored procedure in Sql server

To find the text in the stored procedure in Sql server



declare @newString varchar(205)
Set @newString = '%' + 'CNC_EmailResults_Other' + '%'
-- Insert statements for procedure here
select name from sysobjects where id in
(select id from syscomments where text like @newString)
order by name

Debug the Windows service in Setup

Debug the Windows service in Setup

1. Window service setup should be in debug mode.

2. Insert the code in the appropriate place System.Diagnostics.Debugger.Launch()

3. Now start debugging the windows service.

C# and VB.Net Connectivity using DataReader with Stored Procedure

C# and VB.Net Connectivity using  DataReader with Stored Procedure

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

VB.Net Connectivity for  DataReader

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub

Generics in Dotnet

Generics  in Dotnet

·         In generic class, you can create a collection that is type-safe at compile-time.

   

      Generics are the most powerful feature of C# 2.0. Generics allow you to define type-safe data structures, without committing to actual data types. This results in a significant performance boost and higher quality code, because you get to reuse data processing algorithms without duplicating type-specific code. In concept, generics are similar to C++ templates, but are drastically different in implementation and capabilities. This article discusses the problem space generics address, how they are implemented, the benefits of the programming model, and unique innovations, such as constrains, generic methods and delegates, and generic inheritance. You will also see how generics are utilized in other areas of the .NET Framework such as reflection, arrays, collections, serialization, and remoting.

            If the items are value types, they must be boxed when they are added to the list, and unboxed when they are retrieved. 

·       

            Both the casting and the boxing and unboxing operations decrease performance; the effect of boxing and unboxing can be very significant in scenarios where you must iterate over large collections.

    

      The other limitation is lack of compile-time type checking; because an ArrayList casts everything to Object, there is no way at compile-time to prevent client code from doing something such as this:

    System.Collections.ArrayList list = new System.Collections.ArrayList();

    // Add an integer to the list.

    list.Add(3);

    // Add a string to the list. This will compile, but may cause an error later.

    list.Add("It is raining in Redmond.");

    int t = 0;

    // This causes an InvalidCastException to be returned.

    foreach (int x in list)

    {   t += x;

    }

Boxing and Unboxing in Dotnet

Boxing and Unboxing  in Dotnet


Boxing is the process of converting a value type to the type object
When the CLR boxes a value type, it wraps the value inside a System.Object and stores it on the managed heap.
Unboxing extracts the value type from the object. 

int i = 123;
object o = (object)i;  // boxing

The object o can then be unboxed and assigned to integer variable i: 

o = 123;
i = (int)o;  // unboxing

Disadvantages of Boxing

Performance

Boxing and unboxing are computationally expensive processes. When a value type is boxed, an entirely new      object must be created. This can take up to 20 times longer than an assignment. When unboxing, the casting     process can take four times as long as an assignment.