C# and VB.net Connectivity with Query String
C# Example
//Execute Non Query for query string
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
No comments:
Post a Comment
Comments Welcome