Posted on 2007-05-03 19:05
dodo.loveme 阅读(173)
评论(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();
}
}
}