using System; using System.Data.SqlClient; class MainClass { public static void Main(string[] args) { string conString = @"Data Source=.sqlexpress;" + "Database=Northwind;Integrated Security=SSPI;" + "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" + "Connection Lifetime=600;"; SqlConnectionStringBuilder sb1 = new SqlConnectionStringBuilder(conString); Console.WriteLine(" Min Pool Size = " + sb1.MinPoolSize); SqlConnectionStringBuilder sb2 = new SqlConnectionStringBuilder(conString); sb2.MinPoolSize = 5; Console.WriteLine(" " + sb2.ConnectionString); } }
Author: coder
IntegratedSecurity
using System; using System.Data.SqlClient; class MainClass { public static void Main(string[] args) { string conString = @"Data Source=.sqlexpress;" + "Database=Northwind;Integrated Security=SSPI;" + "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" + "Connection Lifetime=600;"; SqlConnectionStringBuilder sb1 = new SqlConnectionStringBuilder(conString); Console.WriteLine(" Use Integrated Security = " + sb1.IntegratedSecurity); SqlConnectionStringBuilder sb2 = new SqlConnectionStringBuilder(conString); sb2.IntegratedSecurity = true; Console.WriteLine(" " + sb2.ConnectionString); } }
InitialCatalog
using System; using System.Data.SqlClient; class MainClass { public static void Main(string[] args) { string conString = @"Data Source=.sqlexpress;" + "Database=Northwind;Integrated Security=SSPI;" + "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" + "Connection Lifetime=600;"; SqlConnectionStringBuilder sb1 = new SqlConnectionStringBuilder(conString); Console.WriteLine(" Database = " + sb1.InitialCatalog); SqlConnectionStringBuilder sb2 = new SqlConnectionStringBuilder(conString); sb2.InitialCatalog = "Northwind"; Console.WriteLine(" " + sb2.ConnectionString); } }
new SqlConnectionStringBuilder(conString)
using System; using System.Data.SqlClient; class MainClass { public static void Main(string[] args) { string conString = @"Data Source=.sqlexpress;" + "Database=Northwind;Integrated Security=SSPI;" + "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" + "Connection Lifetime=600;"; SqlConnectionStringBuilder sb1 = new SqlConnectionStringBuilder(conString); Console.WriteLine(" " + sb1.ConnectionString); } }
control SqlCommand to return a single row
using System; using System.Data; using System.Data.SqlClient; class SingleRowCommandBehavior { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products"; mySqlConnection.Open(); SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[" ProductID"] = " + mySqlDataReader["ProductID"]); Console.WriteLine("mySqlDataReader[" ProductName"] = " + mySqlDataReader["ProductName"]); Console.WriteLine("mySqlDataReader[" QuantityPerUnit"] = " + mySqlDataReader["QuantityPerUnit"]); Console.WriteLine("mySqlDataReader[" UnitPrice"] = " + mySqlDataReader["UnitPrice"]); } mySqlDataReader.Close(); mySqlConnection.Close(); } }
Use ExecuteXmlReader() to run a SELECT statement that returns XML
using System; using System.Data; using System.Data.SqlClient; using System.Xml; class ExecuteXmlReader { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID " + "FOR XML AUTO"; mySqlConnection.Open(); XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader(); myXmlReader.Read(); while (!myXmlReader.EOF) { Console.WriteLine(myXmlReader.ReadOuterXml()); } myXmlReader.Close(); mySqlConnection.Close(); } }
Populate a DataSet object using a SELECT statement
using System; using System.Data; using System.Data.SqlClient; class PopulateDataSetUsingSelect { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); Console.WriteLine("Retrieving rows from the Products table"); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); DataTable myDataTable = myDataSet.Tables["Products"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductID = " + myDataRow["ProductID"]); Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]); } } }