"Database Getting Started Classic Notes" 1. Get Data 6.1 Creating a Command Object Using System; Using System.Data; Using System.Data.SqlClient;
class SQLServerProvider {static void Main (string [] args) {string Constr = @ "server = localhost; integrated security = true; database = Northwind"; // database connection SqlConnection sqlConn = new SqlConnection (Constr); try {sqlConn.Open (); Console.WriteLine (sqlConn.DataSource); string sql = "select * from Employees"; SqlCommand sqlComm = new SqlCommand (sql, sqlConn); SqlDataReader sqlReader = sqlComm.ExecuteReader (); Console.WriteLine ( "This program demonstrates The use of sql server .Net Data Provider "); console.writeline (" Querying Database '{0}' with query '{1}' ", sqlConn.Database, SQLComm.commandtext); console.writeline (" First Name / T last name / n "); while (SQLReader.Read ()) {console.writeline (" {0} | {1} ", SQLReader [" firstname "]. Tostring (). Padleft (10), SQLReader [" Lastname "]. TOSTRING (). Padleft (10));} sqlreader.close ();
} Catch (exception ex) {console.writeline ("Error:" ex.Message);} finally {sqlconn.close ();}}} 6.2 Execute Command EXECUTENONQUERY does not return any results, the statement is not a query executescalar single value ExecuteReader one or more ExecutexmlReader XML
Using system.data; using system.data.sqlclient;
class SQLServerProvider {static void Main (string [] args) {string Constr = @ "server = localhost; integrated security = true; database = Northwind"; SqlConnection sqlConn = new SqlConnection (Constr); try {sqlConn.Open (); string sql = "select FirstName from Employees"; SqlCommand sqlComm = new SqlCommand (sql, sqlConn); Console.WriteLine ( "Number of Employees is: {0}", sqlComm.ExecuteScalar ());} catch (Exception ex) {Console .Writeline ("Error:" EX.MESSAGE);} finally {sqlconn.close ();}}} EXECUTESCAL () returned to the object type, converted into stringstring sql = "select firstname from employees"; sqlcommand sqlcomm = new SQLCOMMAND (SQL, SQLCONN); String Str = (String) SQLCOMM.EXECUTESCALARAR (); console.writeline ("Number of Employees IS: {0}", STR);
Conversion to plastic string sql = "select count (*) from Employees"; sqlcommand sqlcomm = new sqlcommand (SQL, SQLCONN); int str = (int) sqlcomm.executescalar (); console.writeline ("Number of Employees is: { 0} ", STR);
6.3 Executing commands with multiple results
ExecuteReader () He returned the SqlDataReader object
Using system.data; using system.data.sqlclient;
class SQLServerProvider {static void Main (string [] args) {string Constr = @ "server = localhost; integrated security = true; database = Northwind"; SqlConnection sqlConn = new SqlConnection (Constr); try {sqlConn.Open (); string sql = "select FirstName, LastName from Employees"; SqlCommand sqlComm = new SqlCommand (sql, sqlConn); SqlDataReader sqlReader = sqlComm.ExecuteReader (); while (sqlReader.Read ()) {Console.WriteLine ( "Employees name: {0 } {1} ", SQLReader.getValue (0), SQLReader.getValue (1));} SQLReader.close ();} catch (exception ex) {console.writeline (" Error: " EX.MESSAGE);} Finally {sqlconn.close ();}}} 6.4 Execute Non Inquiry Commands
Using system.data; using system.data.sqlclient;
class SQLServerProvider {static void Main (string [] args) {string Constr = @ "server = localhost; integrated security = true; database = Northwind"; SqlConnection sqlConn = new SqlConnection (Constr); string sql = "select count (*) from Employees "; SqlCommand selectCommand = new SqlCommand (sql, sqlConn); SqlCommand noQueryCommand = sqlConn.CreateCommand (); try {sqlConn.Open (); Console.WriteLine (" Before insert: Number of Employees {0} ", selectCommand. ExecuteScalar ()); noQueryCommand.CommandText = "insert into Employees (FirstName, Lastname) values ( 'Chen', 'LianJia')"; Console.WriteLine (selectCommand.CommandText); Console.WriteLine ( "Number of rows affected is: (0) ", NOQUERYCOMMAND.EXECUTENONQUERY ()); console.writeline (" after INSERT: NUMBER OF EMPLOYEES {0} ", selectcommand.executescalar ());} catch (exception ex) {console.writeline (" Error: " EX.MESSAGE);} finally {sqlconn.close ();}}} 6.5 command parameters
Using system.data; using system.data.sqlclient;
