Introduction to the C # Database classic notes __ Database

Getting started with the classic notes of database
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"; Connecting to a database
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 ("Name/t last name/n");
while (Sqlreader.read ())
{
Console.WriteLine ("{0}|{ 1} ", sqlreader[" FirstName "]. ToString (). PadLeft (a), sqlreader["LastName"]. ToString (). PadLeft (10));
}
Sqlreader.close ();

}
catch (Exception ex)
{
Console.WriteLine ("Error:" + ex.) message);
}
Finally
{
Sqlconn.close ();
}
}
}
6. 2 Execution command
ExecuteNonQuery does not return any results, the statement is not a query
ExecuteScalar a single value
ExecuteReader One or more
ExecuteXmlReader XML

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 ";
  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 ();
 &NBSP}
 
}
ExecuteScalar () returns an object type,

into string
String sql = "Select FirstName from Employees";
SqlCommand Sqlcomm = new SqlCommand (sql,sqlconn);
String str = (string) sqlcomm.executescalar ();
Console.WriteLine ("Number of Employees is:{0}", str);

To be transformed into 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's returning a SqlDataReader 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";
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-query command

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";
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;
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 ();
Noquerycommand.commandtext = "CREATE TABLE MyTable (myname varchar (), mynumber Integer)";
Console.WriteLine (SelectCommand.CommandText);
Noquerycommand.executenonquery ();
Noquerycommand.commandtext = "INSERT into MyTable values (@myName, @myNumber)";
NOQUERYCOMMAND.PARAMETERS.ADD ("@myName", sqldbtype.varchar,30);
NOQUERYCOMMAND.PARAMETERS.ADD ("@myNumber", SqlDbType.Int);
Noquerycommand.prepare ();
String[] names = {"Enrico", "Franco", "Gloria", "Horace"};
int i;
for (i=1;i<=4;i++)
{
noquerycommand.parameters["@myName"]. Value = Names[i-1];
noquerycommand.parameters["@myNumber"]. Value = i;
Console.WriteLine (Noquerycommand.commandtext);
Console.WriteLine ("Number of rows affected is {0}", Noquerycommand.executenonquery ());
}
}
catch (Exception ex)
{
Console.WriteLine ("Error:" + ex.) message);
}
Finally
{
Sqlconn.close ();
}
}
}