Visual C++ .NET/Database ADO.net/SqlCommand
Содержание
Call a stored procedure with parameters
<source lang="csharp">
- include "stdafx.h"
using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main(){
String ^Name = "Doors"; SqlConnection ^connection = gcnew SqlConnection(); connection->ConnectionString = "SQLConnection"; try{ SqlCommand ^cmd = gcnew SqlCommand(); cmd->Connection = connection; cmd->CommandType = CommandType::Text; cmd->CommandText =String::Format("SELECT FirstName, LastName FROM Authors WHERE LastName = "{0}"",Name); connection->Open(); SqlDataReader ^reader = cmd->ExecuteReader(); while(reader->Read()){ Console::WriteLine("{0} {1}",reader["FirstName"], reader["LastName"]); } reader->Close(); // CREATE PROCEDURE dbo.StoriesWhereLastName(@LastName NVARCHAR(32) = NULL) AS // SELECT StoryID, Headline, Story FROM Stories WHERE LastName = @LastName // RETURN cmd->CommandType = CommandType::StoredProcedure; cmd->CommandText = "StoriesWhereLastName"; cmd->Parameters->Add(gcnew SqlParameter("@LastName",SqlDbType::VarChar)); cmd->Parameters["@LastName"]->Value = Name; reader = cmd->ExecuteReader(); while(reader->Read()) { Console::WriteLine(reader["StoryID"]); Console::WriteLine(reader["Headline"]); Console::WriteLine(reader["Story"]); Console::WriteLine(); } reader->Close(); } catch (SqlException ^e) { Console::WriteLine("No connection the following error occurred: {0}", e->Message); } finally { connection->Close(); }
}
</source>
Execute delete command
<source lang="csharp">
- include "stdafx.h"
using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {
String ^Name = "Doors"; SqlConnection ^connection = gcnew SqlConnection(); connection->ConnectionString = "SQLConnection"; try{ SqlCommand ^cmd = gcnew SqlCommand(); cmd->Connection = connection; connection->Open(); cmd->CommandType = CommandType::StoredProcedure; cmd->CommandText = "InsertAuthor"; cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar)); cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar)); cmd->Parameters["@LastName"]->Value = "A"; cmd->Parameters["@FirstName"]->Value = "B"; int affected = cmd->ExecuteNonQuery(); Console::WriteLine("Insert - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Update - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Delete - {0} rows are affected", affected); }catch (SqlException ^e){ Console::WriteLine("No connection the following error occurred: {0}", e->Message); }finally{ connection->Close(); }
}
</source>
Execute update command
<source lang="csharp">
- include "stdafx.h"
using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {
String ^Name = "Doors"; SqlConnection ^connection = gcnew SqlConnection(); connection->ConnectionString = "SQLConnection"; try{ SqlCommand ^cmd = gcnew SqlCommand(); cmd->Connection = connection; connection->Open(); cmd->CommandType = CommandType::StoredProcedure; cmd->CommandText = "InsertAuthor"; cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar)); cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar)); cmd->Parameters["@LastName"]->Value = "A"; cmd->Parameters["@FirstName"]->Value = "B"; int affected = cmd->ExecuteNonQuery(); Console::WriteLine("Insert - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Update - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Delete - {0} rows are affected", affected); }catch (SqlException ^e){ Console::WriteLine("No connection the following error occurred: {0}", e->Message); }finally{ connection->Close(); }
}
</source>
use SqlCommand to call stored procedure
<source lang="csharp">
- include "stdafx.h"
using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Configuration; void main() {
String ^Name = "Doors"; SqlConnection ^connection = gcnew SqlConnection(); connection->ConnectionString = "SQLConnection"; try{ SqlCommand ^cmd = gcnew SqlCommand(); cmd->Connection = connection; connection->Open(); cmd->CommandType = CommandType::StoredProcedure; cmd->CommandText = "InsertAuthor"; cmd->Parameters->Add(gcnew SqlParameter("@LastName", SqlDbType::VarChar)); cmd->Parameters->Add(gcnew SqlParameter("@FirstName",SqlDbType::VarChar)); cmd->Parameters["@LastName"]->Value = "A"; cmd->Parameters["@FirstName"]->Value = "B"; int affected = cmd->ExecuteNonQuery(); Console::WriteLine("Insert - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "UPDATE Authors SET LastName = "Doe" WHERE LastName = "A""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Update - {0} rows are affected", affected); cmd->CommandType = CommandType::Text; cmd->CommandText = "DELETE FROM Authors WHERE LastName = "Doe""; affected = cmd->ExecuteNonQuery(); Console::WriteLine("Delete - {0} rows are affected", affected); }catch (SqlException ^e){ Console::WriteLine("No connection the following error occurred: {0}", e->Message); }finally{ connection->Close(); }
}
</source>