using System;
using System.Data.SqlClient;
using System.Data;
using System.Xml;//ExecuteXmlReader--XmlReader
namespace ConsoleApplication3
{
class class1
{
public static void Main()
{
///DataAdapter与Dataset关联
///
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
string select="SELECT ID,NAME,AGE FROM TableName";
SqlConnection conn=new SqlConnection(source);
SqlDataAdapter da=new SqlDataAdapter(select,conn);
DataSet ds=new DataSet();
da.Fill(ds,"TableName");
foreach(DataRow row in ds.Tables["TableName"].Rows)
{
Console.WriteLine("{0} and {1} and {2}",row[0],row[1],row[2]);
}*/
///ExecuteNonQuery-执行命令，只返回记录个数，（但存储过程就有返回值）
///
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
string select="UPDATE TableName "+
"SET NAME='BOB' "+
"WHERE NAME='HOME'";//UPDATE TableName SET NAME='BOB'WHERE NAME='HOME'
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand(select,conn);
cmd.ExecuteNonQuery();
conn.Close();*/
///ExecuteReader-返回一个类型化的IDataReader提供一种方法来读取一个
///或多个通过在数据源执行命令所获得的只进结果集流，这是由访问关系数据库的
///.NET Framework 数据提供程序实现的。
///
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
string select="SELECT ID,NAME,AGE FROM TableName";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand(select,conn);
SqlDataReader reader=cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine("{0} and {1} and {2}",reader[0],reader[1],reader[2]);
Console.WriteLine("{0} and {1} and {2}",reader.GetInt32(0),
reader.GetString(1),reader.GetInt32(2));
}
conn.Close();*/
///ExecuteScalar-从SQL语句中返回一个结果,例如表中个数，服务器时间等
///
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
string select="SELECT COUNT(*) FROM TableName";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand(select,conn);
Object o=cmd.ExecuteScalar();
Console.WriteLine("{0}",o);
conn.Close();*/
///ExecuteResultSet(只用于SQL提供者),
///如果不小心调用了它，就会抛出一个System.NotSupportedException异常．
///
///ExecuteXmlReader(只用于SQL提供者),
///返回一个XmlReader对象．SQL允许用FOR XML子句来扩展SQL子句，
///这个句子可以带有下述三个选项中的一个：
///FOR XML AUTO
///FOR XML RAW
///FOR XML EXPLICIT
///
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
string select="SELECT ID,NAME,AGE FROM TableName "+
"FOR XML AUTO";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand(select,conn);
XmlReader reader=cmd.ExecuteXmlReader();
while(reader.Read())
{
Console.WriteLine(reader.ReadOuterXml());
}
conn.Close();*/
///
///调用存储过程
///
///调用没有返回值的存储过程
///记录的更新
/*ALTER PROCEDURE dbo.TABLENAMEUPDATE
(
@ID int,
@NAME char(10)
)
AS
SET NOCOUNT OFF;
UPDATE TABLENAME
SET NAME=@NAME
WHERE ID=@ID;
RETURN*/
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand("TABLENAMEUPDATE",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID",SqlDbType.Int,0,"ID"));
cmd.Parameters.Add(new SqlParameter("@NAME",SqlDbType.Char,10,"NAME"));
cmd.UpdatedRowSource=UpdateRowSource.None;//存储过程的结果枚举
cmd.Parameters[0].Value=1;
cmd.Parameters[1].Value="ZhanGSKen";
cmd.ExecuteNonQuery();
conn.Close();*/
///记录的删除
/*ALTER PROCEDURE dbo.TABLENAMEDELETE
(
@ID int
)
AS
SET NOCOUNT OFF
DELETE FROM TABLENAME
WHERE ID=@ID;
RETURN */
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand("TABLENAMEDELETE",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID",SqlDbType.Int,0,"ID"));
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters["@ID"].Value=5;
cmd.ExecuteNonQuery();
conn.Close();*/
///调用有返回值的存储过程
///记录的插入
/*ALTER PROCEDURE dbo.TABLENAMEINSERT
(
@NAME char(10),
@ID int OUTPUT
)
AS
SET NOCOUNT OFF;
SELECT @ID=MAX(ID)+1
FROM TABLENAME;
INSERT INTO TABLENAME(ID,NAME)
VALUES(@ID,@NAME);
RETURN */
/*string source=@"server=ICE/SQLSERVER;"+
"uid=guest;pwd=guest;"+
"database=DBDemo";
SqlConnection conn=new SqlConnection(source);
conn.Open();
SqlCommand cmd=new SqlCommand("TABLENAMEINSERT",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@NAME",SqlDbType.Char,10,"NAME"));
cmd.Parameters.Add(new SqlParameter("@ID",SqlDbType.Int,0,ParameterDirection.Output,
false,0,0,"ID",DataRowVersion.Default,null));
cmd.UpdatedRowSource=UpdateRowSource.OutputParameters;
cmd.Parameters["@NAME"].Value="CHINA";
cmd.ExecuteNonQuery();
int newID=(int)cmd.Parameters["@ID"].Value;
Console.WriteLine("{0}",newID);
conn.Close();*/
}
}
}
//一般较容易出错的地方是两个string成员，因为要注意单词之间的空格。
