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