数据库操作基础增删改查

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