ADO.NET - C#查询/修改SQL Server数据库

发表于 更新于 本文字数： 202 阅读时长 ≈ 1 分钟

查询（输出查询结果）， 修改SQL Server数据库数据

ADO.NET - C#查询/修改SQL Server数据库

1 References

Retrieving and Modifying Data in ADO.NET - Microsoft Docs

2 Select

2.1 Query

1
2
3
4
5
6
7
8
SqlConnection Conn_Timesheet = new SqlConnection(connStr_Timesheet);

Conn_Timesheet.Open();
SqlCommand cmd_readUser = new SqlCommand("select * from [dbo].[Users]", Conn_Timesheet);
SqlDataAdapter da = new SqlDataAdapter(cmd_readUser);
DataSet ds = new DataSet();
da.Fill(ds, "Users");
Conn_Timesheet.Close();
  • Query Database and Fill the query result into a Dataset instance ds

2.2 output query result

2.2.1 Console

1
2
3
4
5
6
7
8
9
10
11
int count = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
    string UserName = row["UserName"].ToString();
    string PersonnelID = row["EmployeeID"].ToString();
    string NTaccount = row["DomainAccount"].ToString();
    string IsEnabled = row["IsLockedOut"].ToString();

    count++;
    Console.WriteLine("{0,-10}{1,-35}{2,-10}{3,-10}{4,10}", count, UserName, PersonnelID, NTaccount, IsEnabled);
}
  • Output query result stored in Dataset instance ds

2.2.2 XML

1
ds.WriteXml("Users.xml");
  • Output Dataset instance ds to a XML-format file

3 Modify

3.1 Insert

1
2
3
4
5
6
7
8
9
10
11
12
SqlConnection Conn_Timesheet = new SqlConnection(connStr_BIS);
Conn_BIS.Open();

SqlCommand cmd_insertUser = new SqlCommand("INSERT INTO [BIS].[dbo].[SUser]"
                                           + "([UserName], [PersonnelID], [NTaccount], [FullName], [IsEnabled], [Password], [CreateDate])"
                                           + "VALUES('" + PersonnelID + "', '" + PersonnelID + "', '" + NTaccount + "', '" + UserName + "', " + IsEnabled + ", 'BA3253876AED6BC22D4A6FF53D8406C6AD864195ED144AB5C87621B6C233B548BAEAE6956DF346EC8C17F5EA10F35EE3CBC514797ED7DDD3145464E2A0BAB413', '" + DateTime.Now.ToString() + "'"
                                           + ");",
                                           Conn_BIS);

cmd_insertUser.ExecuteNonQuery();

Conn_BIS.Close();
  • Insert a record into table