《C#資料庫入門經典筆記》__資料庫

《資料庫入門經典筆記》
1。擷取資料
   6。1建立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);
  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 執行命令
ExecuteNonQuery  不返回任何結果，語句不是查詢
ExecuteScalar  單個值
ExecuteReader  一個或者多個
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();
  }
 }
}
ExecuteScalar()返回的是OBJECT類型，

轉化成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);

轉化成整形
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 執行具有多個結果的命令

ExecuteReader()
他返回的是SqlDataReader對象

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 執行非查詢命令

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 命令參數

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(30),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();
  }
 }
}