Step 3: Proof of concept connecting to SQL using ADO.NET
- Previous article: Step 2: Create a SQL database for ADO.NET development
- Next article: Step 4: Connect resiliently to SQL with ADO.NET
This C# code example should be considered a proof of concept only. The sample code is simplified for clarity, and does not necessarily represent best practices recommended by Microsoft.
1: Connect
The method SqlConnection.Open is used to connect to your SQL database.
using System;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
}
}
/**** Actual output:
Connected successfully.
Press any key to finish...
****/
2: Execute a query
The method SqlCommand.ExecuteReader:
- Issues the SQL SELECT statement to the SQL system.
- Returns an instance of SqlDataReader to provide access to the result rows.
using System;
using DT = System.Data;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Program.SelectRows(connection);
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
static public void SelectRows(QC.SqlConnection connection)
{
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = @"
SELECT
TOP 5
COUNT(soh.SalesOrderID) AS [OrderCount],
c.CustomerID,
c.CompanyName
FROM
SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY
c.CustomerID,
c.CompanyName
ORDER BY
[OrderCount] DESC,
c.CompanyName; ";
QC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}\t{1}\t{2}",
reader.GetInt32(0),
reader.GetInt32(1),
reader.GetString(2));
}
}
}
}
}
/**** Actual output:
Connected successfully.
1 29736 Action Bicycle Specialists
1 29638 Aerobic Exercise Company
1 29546 Bulk Discount Store
1 29741 Central Bicycle Specialists
1 29612 Channel Outlet
Press any key to finish...
****/
3: Insert a row
This example demonstrates how to:
- Execute an SQL INSERT statement safely by passing parameters.
- Use of parameters protects against SQL injection attacks.
- Retrieve the auto-generated value.
using System;
using DT = System.Data;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Program.InsertRows(connection);
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
static public void InsertRows(QC.SqlConnection connection)
{
QC.SqlParameter parameter;
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = @"
INSERT INTO SalesLT.Product
(Name,
ProductNumber,
StandardCost,
ListPrice,
SellStartDate
)
OUTPUT
INSERTED.ProductID
VALUES
(@Name,
@ProductNumber,
@StandardCost,
@ListPrice,
CURRENT_TIMESTAMP
); ";
parameter = new QC.SqlParameter("@Name", DT.SqlDbType.NVarChar, 50);
parameter.Value = "SQL Server Express 2014";
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@ProductNumber", DT.SqlDbType.NVarChar, 25);
parameter.Value = "SQLEXPRESS2014";
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@StandardCost", DT.SqlDbType.Int);
parameter.Value = 11;
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@ListPrice", DT.SqlDbType.Int);
parameter.Value = 12;
command.Parameters.Add(parameter);
int productId = (int)command.ExecuteScalar();
Console.WriteLine("The generated ProductID = {0}.", productId);
}
}
}
}
/**** Actual output:
Connected successfully.
The generated ProductID = 1000.
Press any key to finish...
****/
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για