using System; using System.Data; using System.Data.Sql; class MainClass { public static void Main(string[] args) { using (DataTable SqlSources = SqlDataSourceEnumerator.Instance.GetDataSources()) { Console.WriteLine("Discover SQL Server Instances:"); foreach (DataRow source in SqlSources.Rows) { Console.WriteLine(" Server Name:{0}", source["ServerName"]); Console.WriteLine(" Instance Name:{0}", source["InstanceName"]); Console.WriteLine(" Is Clustered:{0}", source["IsClustered"]); Console.WriteLine(" Version:{0}", source["Version"]); } } } }
Database ADO.net
SqlDataReader Ordinal Indexer
using System; using System.Data; using System.Data.SqlClient; class OrdinalIndexer { static void Main(string[] args) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql = @"select FirstName, LastName from Employee"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); Console.WriteLine(" {0} {1}", "First Name".PadRight(25),"Last Name".PadRight(20)); Console.WriteLine(" {0} {1}", "============".PadRight(25), "============".PadRight(20)); while(reader.Read()) { Console.WriteLine(" {0} | {1}", reader[0].ToString().PadLeft(25), reader[1].ToString().PadLeft(20)); } reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }
Deal with Multiple Results
using System; using System.Data; using System.Data.SqlClient; class MultipleResults { static void Main(string[] args) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql1 = @"select FirstName from Employee;"; string sql2 = @"select FirstName, LastName from Employee;"; string sql = sql1 + sql2; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); do{ while(reader.Read()) { Console.WriteLine("{0} : {1}", reader[0], reader[0]); } Console.WriteLine("".PadLeft(60, '=')); } while(reader.NextResult()); reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }
Use While loop to read query result data from SqlDataReader
using System; using System.Data; using System.Data.SqlClient; class DataLooper{ 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 reader = cmd.ExecuteReader(); while(reader.Read()) { Console.WriteLine("{0}", reader[0]); } reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }
Use A Data Reader
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UsingADataReader { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.Open(); SqlCommand MyCommand = new SqlCommand("SELECT * FROM CaseInfo", MyConnection); SqlDataReader MyDataReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection); while (MyDataReader.Read()) { Console.WriteLine(MyDataReader[0] + " " + MyDataReader[1]); } MyConnection.Close(); } } }
Reference data in SqlDataReader by column name
using System; using System.Data.SqlClient; class pubsdemo { static void Main(string[] args) { String sConn = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; SqlConnection oConn = new SqlConnection(sConn); oConn.Open(); SqlCommand oCmd = new SqlCommand(sSQL, oConn); SqlDataReader oReader = oCmd.ExecuteReader(); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader["id"], oReader["firstname"], oReader["lastname"]); } } }
Read data from SqlDataReader
using System; using System.Data.SqlClient; class ConnectToSqlConnection { static void Main(string[] args) { String sConn = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; SqlConnection oConn = new SqlConnection(sConn); oConn.Open(); SqlCommand oCmd = new SqlCommand(sSQL, oConn); SqlDataReader oReader = oCmd.ExecuteReader(); int idxID = oReader.GetOrdinal("id"); int idxFirstName = oReader.GetOrdinal("firstname"); int idxLastName = oReader.GetOrdinal("lastname"); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader.GetValue(idxID), oReader.GetValue(idxFirstName), oReader.GetValue(idxLastName)); } } }