Posted on 2007-05-03 19:05
dodo.loveme 閱讀(176)
評論(0) 編輯 收藏
《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();
}
}
}