由于最近和数据库打交道,需要用C#和SQL Server 2005进行操作,就把近段时间内的最常用的操作做个总结。本人也是第一次用C#操作数据库,所以这三种典型用法对初学者还是挺有帮助的。
以下是我在visual studio 2005上写的一个类(连的是SQL Server 2005),已经过测试通过。里面有3个方法比较典型,源码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseOperate
{ class SqlOperateInfo
{ //Suppose your ServerName is "aa",DatabaseName is "bb",UserName is "cc", Password is "dd" private string sqlConnectionCommand = "Data Source=aa;Initial Catalog=bb;User ID=cc;Pwd=dd" ;
//This table contains two columns:KeywordID int not null,KeywordName varchar(100) not null private string dataTableName = "Basic_Keyword_Test" ;
private string storedProcedureName = "Sp_InertToBasic_Keyword_Test" ;
private string sqlSelectCommand = "Select KeywordID, KeywordName From Basic_Keyword_Test" ;
//sqlUpdateCommand could contain "insert" , "delete" , "update" operate private string sqlUpdateCommand = "Delete From Basic_Keyword_Test Where KeywordID = 1" ;
public void UseSqlReader()
{ SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = sqlSelectCommand; sqlConnection.Open(); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); while (sqlDataReader.Read())
{ //Get KeywordID and KeywordName , You can do anything you like. Here I just output them. int keywordid = ( int )sqlDataReader[0];
//the same as: int keywordid = (int)sqlDataReader["KeywordID"] string keywordName = ( string )sqlDataReader[1];
//the same as: string keywordName = (int)sqlDataReader["KeywordName"] Console.WriteLine( "KeywordID = " + keywordid + " , KeywordName = " + keywordName);
} sqlDataReader.Close(); sqlCommand.Dispose(); sqlConnection.Close(); } public void UseSqlStoredProcedure()
{ SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = storedProcedureName; sqlConnection.Open(); sqlCommand.ExecuteNonQuery(); //you can use reader here,too.as long as you modify the sp and let it like select * from .... sqlCommand.Dispose(); sqlConnection.Close(); } public void UseSqlDataSet()
{ SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = sqlSelectCommand; sqlConnection.Open(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand; DataSet dataSet = new DataSet();
//sqlCommandBuilder is for update the dataset to database SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
sqlDataAdapter.Fill(dataSet, dataTableName); //Do something to dataset then you can update it to Database.Here I just add a row DataRow row = dataSet.Tables[0].NewRow(); row[0] = 10000; row[1] = "new row" ;
dataSet.Tables[0].Rows.Add(row); sqlDataAdapter.Update(dataSet, dataTableName); sqlCommand.Dispose(); sqlDataAdapter.Dispose(); sqlConnection.Close(); } } } |
以上的程序概括了最典型的用法,也是最基本的用法。
希望通过本文的介绍,能给你带来帮助,学会C#数据库操作的用法。