ado.net

数据库连接语句
static string sqlCon = @"server =.; database=strike;Integrated
Security=True";
/// <summary>
/// 连接测试
/// </summary>
private static void TestConnection()
{
 //string conStr = @"server=.;database=strike;Integrated
Security=True;";
 SqlConnection conn = new SqlConnection(sqlCon);
 conn.Open();
 conn.Close();
 Console.WriteLine("连接对象新建成功。");
 Console.ReadKey();
}
1 增删改
增删改都是类似的操作，使用 SqlCommand 进行，都是执行非查询语句
ExecuteNonQuery()进行数据库操作，只是 sql 语句有点不同。
新增记录
/// <summary>
/// 新增记录
/// </summary>
public static void AddRecord()
{
 int res = -1;
 //string sqlCon = "server =.; database=strike;Integrated
Security=True";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sqlCon;
string b_title = "标";
string b_content = "容";
string b_author = "作";
string addtime = "2011-11-11";
int b_cid = 102;
string sqlStr2 = string.Format ("insert
book(b_cid,b_title,b_content,b_author,b_isdel,b_addtile,b_money)
values({0},'{1}','{2}','{3}',0,'{4}',10)", b_cid, b_title, b_content, b_author,
addtime);
string sqlStr = "insert
book(b_cid,b_title,b_content,b_author,b_isdel,b_addtile,b_money)
values(103,'dsf','dfdf','dfd',0,'2011-11-11',10)";
 SqlCommand cmd = new SqlCommand();
 cmd.Connection = conn;
 cmd.CommandText = sqlStr2;
 conn.Open();
 res = cmd.ExecuteNonQuery();
 conn.Close();
 if (res > 0)
 {
 Console.WriteLine("受影响" + res);
 }
}
删除数据
[csharp] view plaincopy
/// <summary>
/// 删除数据
/// </summary>
public static void DelRecord()
{
 int res = -1;
 //string sqlCon = "server =.;database=strike;Integrated
Security=True";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sqlCon;
string sqlStr = "delete book where b_id=18";
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
res = cmd.ExecuteNonQuery();
conn.Close();
if (res > 0)
{
Console.WriteLine(res + "行受影响");
}

更新操作
[csharp] view plaincopy
/// <summary>
/// 软删除
/// </summary>
public static void SoftDelRecord()
{
int res = -1;
// string sqlCon = @"server =.;database=strike;Integrated
Security=True";
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = sqlCon;
 string sqlStr = "update book set b_isdel=1 where b_cid=103";
 SqlCommand cmd = new SqlCommand(sqlStr, conn);
 conn.Open();
 res = cmd.ExecuteNonQuery();
 conn.Close();
 if (res > 0)
 {
 Console.WriteLine(res + "行受影响");
 }
}
2 查询操作
1）SqlCommand 查询单个值
用 ExecuteScalar（）方法 ，返回单个值(Object)（查询结果第一行第一列的
值）
public static void QuerySingle()
{
 SqlConnection conn = new SqlConnection(sqlCon);
 string sqlStr = "select * from book";
 SqlCommand cmd = new SqlCommand(sqlStr, conn);
 conn.Open();

 //返回的是查询结果集第一单元格的值，返回的是 object 类型
 object obj = cmd.ExecuteScalar();
 conn.Close();
 Console.WriteLine(obj.ToString());
}
2）SqlCommand 查询多行
用 ExecuteReader()方法
/// <summary>
/// 查询多行数据---DataReader 逐行读取,每次读一行
/// </summary>
public static void QueryList()

SqlConnection con = new SqlConnection(sqlCon);
string sqlStr = "select * from book";
SqlCommand cmd = new SqlCommand(sqlStr, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())//dr 每 read（）一次就读取一行
{

//read()后，dr 就是一行数据
object o = dr["b_title"];
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i].ToString() + "\t");
int a = dr.GetInt32(0);

}
Console.Write("\n");


}
// Console.WriteLine("有数据");
}
else
{
Console.WriteLine("无数据");
 }
}
3）使用适配器读取数据
public static void QueryListByAdapter()
{
SqlConnection conn = new SqlConnection(sqlCon);

string sqlStr = "select * from book";
//创建适配器对象，高数它要做些什么事情，走哪条路去
SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn);
// SqlDataAdapter da = new SqlDataAdapter();

//创建数据集对象（程序的临时数据库）
DataSet ds = new DataSet();

DataTable dt1 = new DataTable();
//调用 fill 方法，填充数据集。
da.Fill(dt1);
da.Fill(ds);

//获得数据集的第一张表
DataTable dt = ds.Tables[0];

//循环数据表的每一行
 for (int i = 0; i < dt.Rows.Count; i++)
 {
 DataRow dr = dt.Rows[i];//每一行付给 dr
 Console.WriteLine(dr[0].ToString() + "___" +
dr["b_title"].ToString());
}
}
4）调用存储过程
public static void QueryListByProc2()
{
 string sqlCon = "server=.;database=strike;Integrated
Security=True;";
SqlConnection conn = new SqlConnection(sqlCon);
SqlCommand cmd = new SqlCommand("uspo_GetPageBooks",
conn);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlParameter[] paras ={
 new
SqlParameter("@pageIndex",SqlDbType.Int,4),
 new
SqlParameter("@pageSize",SqlDbType.Int,4)
};
paras[0].Value = 1;
paras[1].Value = 3;
cmd.Parameters.AddRange(paras);

//有适配器读取数据
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

foreach (DataRow dr in dt.Rows)
{
 Console.WriteLine(dr[0].ToString() + "__" + dr[1].ToString() +
"__" + dr[2].ToString());
} 

parameter:

<pre name="code" class="csharp">string connStr="...";
string sql="select * from table where name=@name";
SqlParameter para=new SqlParameter("@name","john");
SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
da.SelectCommand.Parameters.Add(para);