Csharp/CSharp Tutorial/ADO.Net/SqlDataReader
Содержание
- 1 Accessing Data Values in a DataReader
- 2 DataReader has records using HasRows property
- 3 Data Reading with SqlDataReader
- 4 Determining the Number of Records Returned in a DataReader
- 5 Get data by data type using SqlDataReader
- 6 Get the column ordinal for the Phone attribute and use it to output the column
- 7 Handle Multiple Results
- 8 how to read a table schema
- 9 Mapping .NET Data Provider Data Types to .NET Framework Data Types by calling the GetXXX method
- 10 Mapping .NET Data Provider Data Types to .NET Framework Data Types by casting
- 11 Mapping .NET Data Provider Data Types to .NET Framework Data Types by converting
- 12 Ordinal Indexer
- 13 Ordinal Indexer demo
- 14 Output fields from DataReader row
- 15 Reference result set by column index in SqlDataReader
- 16 Reference result set by column name in SqlDataReader
- 17 ResultSet Info
- 18 Retrieves data as native SQL Server types.
- 19 Retrieving a Result Set Stream Using a DataReader
- 20 Schema Table from SqlDataReader
- 21 SqlDataReader
- 22 SqlDataReader.GetString, GetDateTime
- 23 SqlDataReader: RecordsAffected
- 24 Typed Accessors: int, varchar and decimal
- 25 Updating Data Using CommondBuilder
- 26 Use indexer to read data
- 27 Use loop to read all data in SqlDataReader
- 28 Use SqlDataReader to retrieve data in the resultset
- 29 Use SQL Server Data Provider
- 30 Using a DataReader to read row by row
Accessing Data Values in a DataReader
<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 ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); dr.Read( ); Console.WriteLine("ContactID = {0}", dr[0]); Console.WriteLine("Title = {0}", dr["Title"]); Console.WriteLine("FirstName = {0}",dr.IsDBNull(3) ? "NULL" : dr.GetString(3)); Console.WriteLine("MiddleName = {0}",dr.IsDBNull(4) ? "NULL" : dr.GetSqlString(4)); Console.WriteLine("LastName = {0}",dr.IsDBNull(5) ? "NULL" : dr.GetSqlString(5).Value); Console.WriteLine("EmailAddress = {0}", dr.GetValue(7)); Console.WriteLine("EmailPromotion = {0}",int.Parse(dr["EmailPromotion"].ToString( ))); } }</source>
DataReader has records using HasRows property
<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 * FROM Person.Contact"; string sqlSelectEmpty = "SELECT * FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); Console.WriteLine("DataTable has records = {0}", dt.Rows.Count > 0); using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); Console.WriteLine(dr.HasRows); Console.WriteLine(dr.Read( )); dr.Close( ); } da = new SqlDataAdapter(sqlSelectEmpty, sqlConnectString); dt = new DataTable( ); da.Fill(dt); } }</source>
Data Reading with SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); thisConnection.Open(); SqlCommand thisCommand = thisConnection.CreateCommand(); thisCommand.rumandText = "SELECT CustomerID, CompanyName from Customers"; SqlDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]); } thisReader.Close(); thisConnection.Close(); } }</source>
Determining the Number of Records Returned in a DataReader
<source lang="csharp">/* CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON SELECT * FROM Person.Contact set @RowCount = @@ROWCOUNT RETURN @RowCount
- /
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 COUNT(*) FROM Person.Contact; SELECT * FROM Person.Contact;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); dr.Read( ); Console.WriteLine(dr.GetInt32(0)); dr.NextResult( ); int count = 0; while (dr.Read( )){ count++; } Console.WriteLine(count); dr.Close( ); command = new SqlCommand("Person.GetContacts", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output; dr = command.ExecuteReader( ); dr.Close( ); Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value); } } }</source>
Get data by data type using SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
[STAThread] static void Main(string[] args) { string SQL = "SELECT * FROM Employee"; string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(SQL, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); try { while (reader.Read()) { Console.Write("ID:"+reader.GetInt32(0).ToString() ); Console.Write(" ,"); Console.WriteLine("Name:" + reader.GetString(1).ToString() ); } } finally { reader.Close(); conn.Close(); } }
}</source>
ID:2 ,Name:G
Get the column ordinal for the Phone attribute and use it to output the column
<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 ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); SqlDataReader dr = command.ExecuteReader( ); dr.Read( ); int coPhone = dr.GetOrdinal("Phone"); Console.WriteLine("Phone = {0}", dr[coPhone]); } }</source>
Handle Multiple Results
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class MultipleResults { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql1 = @"select companyname,contactname from customers where companyname like "A%""; string sql2 = @"select firstname,lastname from employees"; string sql = sql1 + sql2; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); do{ while (rdr.Read()){ Console.WriteLine("{0} : {1}", rdr[0], rdr[1]); } Console.WriteLine("".PadLeft(60, "=")); } while (rdr.NextResult()); rdr.Close(); } catch (Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }</source>
how to read a table schema
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class SchemaOnlyCommandBehavior {
public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID = 1"; mySqlConnection.Open(); SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly); DataTable myDataTable = productsSqlDataReader.GetSchemaTable(); foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); if (myDataColumn.ToString() == "ProviderType") { Console.WriteLine(myDataColumn + "= " + ((System.Data.SqlDbType)myDataRow[myDataColumn])); } } } productsSqlDataReader.Close(); mySqlConnection.Close(); }
}</source>
Mapping .NET Data Provider Data Types to .NET Framework Data Types by calling the GetXXX method
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open(); using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { int contactID = dr.GetInt32(0); String firstName = dr.GetString(1); String middleName = dr.IsDBNull(2) ? null : dr.GetString(2); String lastName = dr.GetString(3); Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName); } } } }</source>
Mapping .NET Data Provider Data Types to .NET Framework Data Types by casting
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open(); using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { int contactID = (int)dr.GetSqlInt32(0); String firstName = (string)dr.GetSqlString(1); String middleName = dr.IsDBNull(2) ? null :(string)dr.GetSqlString(2); String lastName = (string)dr.GetSqlString(3); Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName); } } } }</source>
Mapping .NET Data Provider Data Types to .NET Framework Data Types by converting
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, LastName FROM Person.Contact"; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open(); using (SqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { int contactID = Convert.ToInt32(dr[0]); String firstName = Convert.ToString(dr[1]); String middleName = Convert.ToString(dr[2]); String lastName = Convert.ToString(dr[3]); Console.WriteLine("{0}\t{1}, {2} {3}",contactID, lastName, firstName, middleName); } } } }</source>
Ordinal Indexer
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class OrdinalIndexer { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select companyname,contactname from customers where contactname like "M%""; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(" {0} | {1}", rdr[0].ToString().PadLeft(25), rdr[1].ToString().PadLeft(20)); } rdr.Close(); } catch (Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }</source>
Ordinal Indexer demo
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string sql = @"select firstname,lastname from employee where firstname like "M%""; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(" {0} | {1}",rdr[0].ToString().PadLeft(25),rdr[1].ToString().PadLeft(20)); } rdr.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } }
}</source>
Output fields from DataReader row
<source lang="csharp">using System; 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 ContactID, FirstName, LastName FROM Person.Contact "; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( );
using (SqlDataReader dr = command.ExecuteReader( )) { while (dr.Read( )) { Console.WriteLine(dr["ContactID"]); Console.WriteLine(dr["LastName"]); Console.WriteLine(dr["FirstName"]); } } connection.Close( ); } }</source>
Reference result set by column index in SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string sql = @"select * from employee"; SqlConnection conn = null; SqlDataReader reader = null; try { conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); reader = cmd.ExecuteReader(); Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText); while(reader.Read()) { Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) ); } }catch (Exception e) { Console.WriteLine("Error: " + e); } finally { reader.Close(); conn.Close(); } }
}</source>
Reference result set by column name in SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string sql = @"select * from employee"; SqlConnection conn = null; SqlDataReader reader = null; try { conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); reader = cmd.ExecuteReader(); Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText); while(reader.Read()) { Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) ); } }catch (Exception e) { Console.WriteLine("Error: " + e); } finally { reader.Close(); conn.Close(); } }
}</source>
ResultSet Info
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class ResultSetInfo { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select contactname,contacttitle from customers where contactname like "M%""; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); Console.WriteLine("Column Name:\t{0} {1}",rdr.GetName(0),rdr.GetName(1)); Console.WriteLine("Data Type:\t{0} {1}",rdr.GetDataTypeName(0),rdr.GetDataTypeName(1)); while (rdr.Read()) { Console.WriteLine("{0} {1}",rdr.GetString(0),rdr.GetString(1)); } Console.WriteLine("Number of columns in a row: {0}",rdr.FieldCount); Console.WriteLine(rdr.GetName(0)); Console.WriteLine(rdr.GetOrdinal("contactname")); Console.WriteLine(rdr.GetFieldType(0)); Console.WriteLine(rdr.GetName(1)); Console.WriteLine(rdr.GetOrdinal("contacttitle")); Console.WriteLine(rdr.GetFieldType(1)); rdr.Close(); } catch (Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }</source>
Retrieves data as native SQL Server types.
<source lang="csharp">using System; using System.Data.SqlClient; using System.Data.SqlTypes; public class NativeSqlServer {
public static void Main() { string SQL = "SELECT OrderID, CustomerID, OrderDate, Freight FROM Orders"; SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand(SQL, con); SqlDataReader r; con.Open(); r = cmd.ExecuteReader(); while (r.Read()) { SqlInt32 orderID = r.GetSqlInt32(0); SqlString customerID = r.GetSqlString(1); SqlDateTime orderDate = r.GetSqlDateTime(2); SqlMoney freight = r.GetSqlMoney(3); Console.Write(orderID.ToString() + ", "); Console.Write(customerID + ", "); Console.Write(orderDate.ToString() + ", "); Console.Write(freight.ToString() + ", "); Console.WriteLine(); } }
}</source>
Retrieving a Result Set Stream Using a DataReader
<source lang="csharp">using System; 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 ContactID, FirstName, LastName FROM Person.Contact "; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); using (SqlDataReader dr = command.ExecuteReader( )){ while (dr.Read( )) { Console.WriteLine(dr["ContactID"]); Console.WriteLine(dr["LastName"]); Console.WriteLine(dr["FirstName"]); } } connection.Close( ); } }</source>
Schema Table from SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class SchemaTable { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select * from employees"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); DataTable schema = rdr.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn col in schema.Columns) Console.WriteLine(col.ColumnName + " = " + row[col]); } rdr.Close(); } catch (Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }</source>
SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
[STAThread] static void Main(string[] args) { string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string SQL = "SELECT * FROM Employee"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(SQL, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); Console.WriteLine("ID, FirstName"); while (reader.Read()) { Console.Write(reader["ID"].ToString() + ", "); Console.Write(reader["FirstName"].ToString() + ", "); } reader.Close(); conn.Close(); }
}</source>
SqlDataReader.GetString, GetDateTime
<source lang="csharp">using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient;
class Report {
static void Main(string[] args) { SqlConnection dataConnection = new SqlConnection(); try { dataConnection.ConnectionString = "Integrated Security=true;Initial Catalog=Northwind;Data Source=.\\SQLExpress"; dataConnection.Open(); SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.rumandText = "SELECT OrderID, OrderDate, " + "ShippedDate, ShipName, ShipAddress, ShipCity, " + "ShipCountry FROM Orders WHERE CustomerID="0001""; Console.WriteLine("About to execute: {0}\n\n", dataCommand.rumandText); SqlDataReader dataReader = dataCommand.ExecuteReader(); while (dataReader.Read()) { int orderId = dataReader.GetInt32(0); if (dataReader.IsDBNull(2)) { Console.WriteLine("Order {0} not yet shipped\n\n", orderId); } else { DateTime orderDate = dataReader.GetDateTime(1); DateTime shipDate = dataReader.GetDateTime(2); string shipName = dataReader.GetString(3); string shipAddress = dataReader.GetString(4); string shipCity = dataReader.GetString(5); string shipCountry = dataReader.GetString(6); Console.WriteLine( "Order: {0}\nPlaced: {1}\nShipped: {2}\n" + "To Address: {3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate, shipDate, shipName, shipAddress, shipCity, shipCountry); } } dataReader.Close(); } catch (SqlException e) { Console.WriteLine("Error accessing the database: {0}", e.Message); } finally { dataConnection.Close(); } }
}</source>
SqlDataReader: RecordsAffected
<source lang="csharp">using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string SQL = "SELECT * FROM Employee"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(SQL, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); Console.WriteLine("ID, FirstName"); while (reader.Read()) { Console.Write(reader["ID"].ToString() + ", "); Console.Write(reader["FirstName"].ToString() + ", "); } Console.WriteLine("Total Number of records affected: "+ reader.RecordsAffected.ToString() ); reader.Close(); conn.Close(); }
}</source>
Typed Accessors: int, varchar and decimal
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select productname,unitprice,unitsinstock,discontinued from products"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine( "{0}\t {1}\t\t {2}\t {3}", // nvarchar rdr.GetString(0).PadRight(30), // money rdr.GetDecimal(1), // smallint rdr.GetInt16(2), // bit rdr.GetBoolean(3)); } rdr.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } }
}</source>
Updating Data Using CommondBuilder
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", MyConnection); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); MyDataSet.Tables[0].Rows[0][0] = 55; SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); MyDataAdapter.Update(MyDataSet); }
}</source>
Use indexer to read data
<source lang="csharp">using System; using System.Data.SqlClient; class FirstExample {
public static void Main() { try { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "SELECT CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "WHERE CustomerID = "ALFKI""; mySqlConnection.Open(); SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); mySqlDataReader.Read(); Console.WriteLine("mySqlDataReader[\" CustomerID\"] = " + mySqlDataReader["CustomerID"]); Console.WriteLine("mySqlDataReader[\" CompanyName\"] = " + mySqlDataReader["CompanyName"]); Console.WriteLine("mySqlDataReader[\" ContactName\"] = " + mySqlDataReader["ContactName"]); Console.WriteLine("mySqlDataReader[\" Address\"] = " + mySqlDataReader["Address"]); mySqlDataReader.Close(); mySqlConnection.Close(); } catch (SqlException e) { Console.WriteLine("A SqlException was thrown"); Console.WriteLine("Number = " + e.Number); Console.WriteLine("Message = " + e.Message); Console.WriteLine("StackTrace:\n" + e.StackTrace); } }
}</source>
Use loop to read all data in SqlDataReader
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string sql = @"select firstName from Employee"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("{0}", rdr[0]); } rdr.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } }
}</source>
Use SqlDataReader to retrieve data in the resultset
<source lang="csharp">using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string SQL = "SELECT * FROM Employee"; string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(SQL, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); try { while (reader.Read()) { Console.Write("ID:"+reader.GetInt32(0).ToString() ); Console.Write(" ,"); Console.WriteLine("Name:" + reader.GetString(1).ToString() ); } } finally { reader.Close(); conn.Close(); } }
}</source>
ID:2 ,Name:G
Use SQL Server Data Provider
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; string sql = @"select * from employee"; SqlConnection conn = null; SqlDataReader reader = null; try { conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); reader = cmd.ExecuteReader(); Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText); while(reader.Read()) { Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) ); } }catch (Exception e) { Console.WriteLine("Error: " + e); } finally { reader.Close(); conn.Close(); } }
}</source>
Using a DataReader to read row by row
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); MyConnection.Open(); SqlCommand MyCommand = new SqlCommand("SELECT * FROM Employee", MyConnection); SqlDataReader MyDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection); while (MyDataReader.Read()) { Console.WriteLine(MyDataReader[0] + " " + MyDataReader[1]); } MyConnection.Close(); }
}</source>