SQL commands from C and ASP net SQL

SQL commands from C# and ASP. net

SQL commands Microsoft’s database system is called “SQL Server” The most popular open source database is called “My. SQL” SQL : Structured Query Language Select * From Plant. Data Where Collection = ‘Ivy‘ Select * From Plant. Data Where Location = ‘House 10’ Lists all the database records that satisfy that criteria Select COUNT (DISTINCT Scientific. Name) FROM Plant. Data Counts the number of unique plant names therefore = number of plant types Returns a useful statistic

Question 1: How do you issue an SQL command from C#? It’s all about the connection string: string CONNECTION_STRING = "Data Source=MBUCKLEY 16; Initial Catalog=Botanical. App; Integrated Security=True"; string CONNECTION_STRING_LOCATIONS ="Data Source=MBUCKLEY 16; Initial Catalog=Locations; Integrated Security=True"; string CONNECTION_STRING_COLLECTIONS ="Data Source=MBUCKLEY 16; Initial Catalog=Collections; Integrated Security=True"; note: SERVER DATABASE

Issuing an SQL command with no reply string connection. String = Global. Variables. CONNECTION_STRING; Sql. Connection sql. Connection 1 = new Sql. Connection(connection. String); sql. Connection 1. Open(); // SQL output Sql. Command cmd = new Sql. Command(); cmd. Command. Text = "INSERT INTO Plant. Data( [ID], [Common. Name]) VALUES (‘ 1258’, ‘Daisy’) " ; cmd. Connection = sql. Connection 1; cmd. Execute. Non. Query(); // executes the Command, expects no reply

Question 2: How do you get a reply? string connection. String = Global. Variables. CONNECTION_STRING; String reply. String = " "; Sql. Connection sql. Connection 1 = new Sql. Connection(connection. String); sql. Connection 1. Open(); // SQL output (query) Sql. Command cmd = new Sql. Command(); // SQL input (reader) Sql. Data. Reader reader; IData. Record read. Record = null; cmd. Command. Text = "SELECT COUNT(DISTINCT ID) FROM Plant. Data"; cmd. Connection = sql. Connection 1; reader = cmd. Execute. Reader(); // executes the command reads the reply read. Record = (IData. Record)(reader); // places reply into a Data Record reader. Read(); // reads the Data Record reply. String = String. Format("{0}", read. Record[0]); // convert ints to strings, nulls to "" if (String. Compare(reply. String, "") != 0) { sql. Connection 1. Close(); } // end if not blank else { sql. Connection 1. Close(); reply. String = ("SQL Query Error"); } // end if // if not null

3. What to do with reply. String? e. g. Stats. Text = reply. String;

In Default. aspx. cs protected void Page_Load(object sender, Event. Args e) { Useful. Functions SQLQuery = new Useful. Functions(); Stats. Text = SQLQuery. SQLCommand("SELECT COUNT(DISTINCT Scientific. Name) FROM Plant. Data"); } }