Csharp/CSharp Tutorial/ADO.Net/SqlCommand Select
Содержание
- 1 CommandReader
- 2 Execute Scalar Example
- 3 Execute the command to get Scalar value
- 4 Executing a Query That Does Not Return a Result Set
- 5 Executing a Query That Returns Multiple Result Sets with DataSet
- 6 Executing a Query That Returns Multiple Result Sets with SqlDataReader
- 7 Get table list
- 8 How to perform a SELECT statement using ADO.NET
- 9 Simple Query
CommandReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class CommandReader
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
string sql = @"select firstname,lastname from employees";
SqlCommand cmd = new SqlCommand(sql, conn);
Console.WriteLine("Command created and connected.");
try
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("Employee name: {0} {1}",
rdr.GetValue(0),
rdr.GetValue(1)
);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
Console.WriteLine("Connection Closed.");
}
}
}</source>
Execute Scalar Example
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; class Program {
static void Main(string[] args) {
SqlConnection thisConnection = new
SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
"Database=northwind");
thisConnection.Open();
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.rumandText = "SELECT COUNT(*) FROM Customers";
Object countResult = thisCommand.ExecuteScalar();
Console.WriteLine("Count of Customers = {0}", countResult);
thisConnection.Close();
}
}</source>
Execute the command to get Scalar value
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";
SqlConnection connection = new SqlConnection(sqlConnectString);
string selectText = "SELECT COUNT(*) FROM Person.Contact";
SqlCommand command = new SqlCommand(selectText, connection);
connection.Open( );
int recordCount = (int)command.ExecuteScalar( );
connection.Close( );
Console.WriteLine(recordCount);
}
}</source>
Executing a Query That Does Not Return a Result Set
<source lang="csharp">/* CREATE TABLE MyTable(
Id int NOT NULL PRIMARY KEY, Field1 nvarchar(50) NOT NULL, Field2 nvarchar(50) NOT NULL,
) INSERT INTO MyTable VALUES (1, "Field 1.1", "Field 2.1"); INSERT INTO MyTable VALUES (2, "Field 1.2", "Field 2.2"); INSERT INTO MyTable VALUES (3, "Field 1.3", "Field 2.3");
- /
using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=MyDatabase;";
string sqlDelete = "DELETE FROM MyTable WHERE Id = 2";
SqlConnection connection = new SqlConnection(sqlConnectString);
SqlCommand command = new SqlCommand(sqlDelete, connection);
connection.Open( );
int rowsAffected = command.ExecuteNonQuery( );
Console.WriteLine("{0} row(s) affected.", rowsAffected);
Console.WriteLine("Record with Id = 2 deleted.");
connection.Close( );
}
}</source>
Executing a Query That Returns Multiple Result Sets with DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";
string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
"SELECT TOP 3 * FROM Sales.SalesOrderDetail";
int rsNumber;
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataSet ds = new DataSet( );
da.Fill(ds);
rsNumber = 0;
foreach (DataTable dt in ds.Tables){
Console.WriteLine("Result set: {0}", ++rsNumber);
foreach (DataRow row in dt.Rows){
Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
}
}
}
}</source>
Executing a Query That Returns Multiple Result Sets with SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
string sqlConnectString = "Data Source=(local);" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";
string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
"SELECT TOP 3 * FROM Sales.SalesOrderDetail";
int rsNumber;
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataSet ds = new DataSet( );
da.Fill(ds);
rsNumber = 0;
using (SqlConnection connection = new SqlConnection(sqlConnectString)){
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open( );
SqlDataReader dr = command.ExecuteReader( );
rsNumber = 0;
do{
Console.WriteLine("Result set: {0}", ++rsNumber);
while (dr.Read( )){
Console.WriteLine("{0}, {1}, {2}", dr[0], dr[1], dr[2]);
}
Console.WriteLine(Environment.NewLine);
} while (dr.NextResult( ));
}
}
}</source>
Get table list
<source lang="csharp">using System; using System.Data.SqlClient; public class GetTableList {
public static void Main()
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "SELECT TABLE_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;
con.Open();
r = cmd.ExecuteReader();
while (r.Read()){
Console.WriteLine(r[0] + ": " + r[1]);
}
con.Close();
}
}</source>
How to perform a SELECT statement using ADO.NET
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main()
{
string connectionString ="server=localhost;database=Northwind;uid=sa;pwd=sa";
SqlConnection mySqlConnection = new SqlConnection(connectionString);
string selectString = "SELECT ID, FirstName FROM Employee";
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = selectString;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
string dataTableName = "Employee";
mySqlDataAdapter.Fill(myDataSet, dataTableName);
DataTable myDataTable = myDataSet.Tables[dataTableName];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ID = " + myDataRow["ID"]);
Console.WriteLine("FirstName = " + myDataRow["FirstName"]);
}
mySqlConnection.Close();
}
}</source>
Simple Query
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; class MainClass {
[STAThread]
static void Main(string[] args)
{
string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
using ( SqlConnection conn = new SqlConnection( cstr ) )
{
conn.Open();
SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
SqlDataReader rdr = cmd.ExecuteReader();
while ( rdr.Read() )
{
System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
}
rdr.Close();
}
}
}</source>
