Executing SQL Commands in C# and ASP.Net
Learn to issue SQL commands from C# using connection strings, execute commands with and without replies, and retrieve replies when interacting with databases in C# and ASP.Net development.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
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 MySQL SQL : Structured Query Language Select * From PlantData Where Collection = Ivy Select * From PlantData Where Location = House10 Lists all the database records that satisfy that criteria Select COUNT (DISTINCT ScientificName) FROM PlantData 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=MBUCKLEY16; Initial Catalog=BotanicalApp; Integrated Security=True"; string CONNECTION_STRING_LOCATIONS ="Data Source=MBUCKLEY16; Initial Catalog=Locations; Integrated Security=True"; string CONNECTION_STRING_COLLECTIONS ="Data Source=MBUCKLEY16; Initial Catalog=Collections; Integrated Security=True"; note: SERVER DATABASE
Issuing an SQL command with no reply string connectionString = GlobalVariables.CONNECTION_STRING; SqlConnection sqlConnection1 = new SqlConnection(connectionString); sqlConnection1.Open(); // SQL output SqlCommand cmd = new SqlCommand(); cmd.CommandText = "INSERT INTO PlantData( [ID], [CommonName]) VALUES ( 1258 , Daisy ) " ; cmd.Connection = sqlConnection1; cmd.ExecuteNonQuery(); // executes the Command, expects no reply
Question 2: How do you get a reply? string connectionString = GlobalVariables.CONNECTION_STRING; String replyString = " "; SqlConnection sqlConnection1 = new SqlConnection(connectionString); sqlConnection1.Open(); // SQL output (query) SqlCommand cmd = new SqlCommand(); // SQL input (reader) SqlDataReader reader; IDataRecord readRecord = null; cmd.CommandText = "SELECT COUNT(DISTINCT ID) FROM PlantData"; cmd.Connection = sqlConnection1; reader = cmd.ExecuteReader(); // executes the command reads the reply readRecord = (IDataRecord)(reader); // places reply into a Data Record reader.Read(); // reads the Data Record replyString = String.Format("{0}", readRecord[0]); // convert ints to strings, nulls to "" if (String.Compare(replyString, "") != 0) // if not null { sqlConnection1.Close(); } // end if not blank else { sqlConnection1.Close(); replyString = ("SQL Query Error"); } // end if
3. What to do with replyString? e.g. Stats.Text = replyString;
public const string CONNECTION_STRING="Data Source=MBUCKLEY16;Initial Connection String incl. Server Name SQL Command: SELECT COUNT(DISTINCT ID) FROM PlantData Catalog=BotanicalApp;Integrated Security=True"; SQL Connection Database public string SQLCommand(string Cmd) { string replyString = ""; // Connection to SQL Server string connectionString = GlobalVariables.CONNECTION_STRING; SqlConnection sqlConnection1 = new SqlConnection(connectionString); SQL Data Reader IData Record // SQL output (query) SqlCommand cmd = new SqlCommand(); // SQL input (reader) SqlDataReader reader; IDataRecord readRecord = null; cmd.CommandText = Cmd; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader(); // executes the Command, reads the reply readRecord = (IDataRecord)(reader); // places reply into a Data Record reader.Read(); // reads the Data Record replyString = String.Format("{0}", readRecord[0]); if (String.Compare(replyString, "") != 0) // if not null { sqlConnection1.Close(); return (replyString); } // end if not blank else { sqlConnection1.Close(); return ("SQL Query Error"); } // end if } // end SQLCommand
In Default.aspx.cs protected void Page_Load(object sender, EventArgs e) { UsefulFunctions SQLQuery = new UsefulFunctions(); Stats.Text = SQLQuery.SQLCommand("SELECT COUNT(DISTINCT ScientificName) FROM PlantData"); } }
