using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace _120224
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection())
{
conn.Open();
//添加
string sql = "insert into tablename values('a1','a2')";
//修改
string sql2 = "update tablename set a1='aaa',b1='bbb' where key = 123";
//删除
string sql3 = "delete from tablename where key = 123";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
SqlCommand cmd3 = new SqlCommand(sql3, conn);
if (cmd.ExecuteNonQuery() > 0)
{
//成功！
}
//查询
string sql4 = "select * from tablename";
SqlCommand cmd4 = new SqlCommand(sql4, conn);
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
//获得字段值
string a1 = dr["a1"].ToString();
string a2 = dr["a2"].ToString();
}
dr.Close();
//存储过程
SqlCommand cmd5 = new SqlCommand("过程名称", conn);
cmd5.CommandType = CommandType.StoredProcedure;
//输入参数
cmd5.Parameters.AddWithValue("@参数1", "值");
cmd5.Parameters.AddWithValue("@参数2", "值");
//输出参数  可选
SqlParameter outPar = new SqlParameter("@出参数1", SqlDbType.NVarChar, 30);
outPar.Direction = ParameterDirection.Output;
cmd5.Parameters.Add(outPar);
//返回值
SqlParameter rePar = new SqlParameter("ReturnValue", SqlDbType.Int);
rePar.Direction = ParameterDirection.ReturnValue;
cmd5.Parameters.Add(rePar);
//执行,insert ，delete ，update
cmd5.ExecuteNonQuery();
/
/或者执行  select
SqlDataReader dr2 = cmd5.ExecuteReader();
//遍历 dr2 ... 同上
//获得输出参数和返回值
string outValue = outPar.Value.ToString();
string reValue = rePar.Value.ToString();
//获得 select count()查询
string sql6 = "select count(id) from tablename";
SqlCommand cmd6 = new SqlCommand(sql6, conn);
int count = (int)cmd6.ExecuteScalar();
//DataSet  填充
string sql7 ="select * from tablename";
DataSet ds =
new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql7,conn);
da.Fill(ds,"数据库别名");
//DataTable 遍历
DataTable dt = ds.Tables[0];//或ds.Tables["数据库别名"];
foreach (DataRow item in dt.Rows)
{
//获得字段值
string a1 = item["a1"].ToString();
string a2 = item["a2"].ToString();
}
//DataTable 排序
DataView dv = dt.DefaultView;
dv.Sort = "排序字段名 desc";
//遍历排序结果
foreach (DataRowView item in dv)
{
sqltransaction什么意思
string a1 = item["a1"].ToString();
string a2 = item["a2"].ToString();
}
/
/DataSet 关系
string sql8 = "select * from tablename1; select * from tablename2";
SqlDataAdapter da2 = new SqlDataAdapter(sql8, conn);
da2.Fill(ds);
DataTable dt1 = ds.Tables[0];
DataTable dt2 = ds.Tables[1];
//建立dt1，dt2关系
ds.Relations.Add("a", dt1.Columns["key"], dt2.Columns["key"]);
//主从表关系遍历
foreach (DataRow item in dt1.Rows)
{
/
/获得主表字段
string a1 = item["a1"].ToString();
//获得从表字段
foreach (DataRow item2 in item.GetChildRows("a"))
{
string a2 = item2["a2"].ToString();
string a3 = item2["a3"].ToString();                       
}
}
//事物
SqlCommand cmd7 = new SqlCommand();
cmd7.Connection = conn;
SqlTransaction tr = conn.BeginTransaction();
cmd7.Transaction = tr;
try
{
cmd7.CommandText = "SQL语句1";
cmd7.ExecuteNonQuery();
cmd7.CommandText = "SQL语句2";
cmd7.ExecuteNonQuery();
tr.Commit();
}
catch (SqlException ex)
{
tr.Rollback();
}
//DataSet生成xml
ds.WriteXml("F:\\a.xml");
//xml载入DataSet
ds.ReadXml("F:\\a.xml");
}
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。