C#数据库操作的三种经典用法
由于最近和数据库打交道，需要用C#和SQL Server 2005进行操作,就把近段时间内的最常用的操作做个总结。本人也是第一次用C#操作数据库，所以这三种典型用法对初学者还是挺有帮助的。以下是我在visual studio 2005上写的一个类(连的是SQL Server 2005)，已经过测试通过。里面有3个方法比较典型，源码如下: [color=#0000ff]using[/color][color=#000000] System;[/color][color=#0000ff]using[/color][color=#000000] System.Collections.Generic;[/color][color=#0000ff]using[/color][color=#000000] System.Text;[/color][color=#0000ff]using[/color][color=#000000] System.Data;[/color][color=#0000ff]using[/color][color=#000000] System.Data.SqlClient;[/color][color=#0000ff]namespace[/color][color=#000000] DatabaseOperate{ [/color][color=#0000ff]class[/color][color=#000000] SqlOperateInfo { [/color][color=#008000]//[/color][color=#008000]Suppose your ServerName is "aa",DatabaseName is "bb",UserName is "cc", Password is "dd" [/color]
[color=#000000] [/color][color=#0000ff]private[/color]
[color=#0000ff]string[/color][color=#000000] sqlConnectionCommand [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]Data Source=aa;Initial Catalog=bb;User ID=cc;Pwd=dd[/color][color=#000000]"[/color][color=#000000]; [/color][color=#008000]//[/color][color=#008000]This table contains two columns:KeywordID int not null,KeywordName varchar(100) not null [/color]
[color=#000000] [/color][color=#0000ff]private[/color]
[color=#0000ff]string[/color][color=#000000] dataTableName [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]Basic_Keyword_Test[/color][color=#000000]"[/color][color=#000000]; [/color][color=#0000ff]private[/color]
[color=#0000ff]string[/color][color=#000000] storedProcedureName [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]Sp_InertToBasic_Keyword_Test[/color][color=#000000]"[/color][color=#000000]; [/color][color=#0000ff]private[/color]
[color=#0000ff]string[/color][color=#000000] sqlSelectCommand [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]Select KeywordID, KeywordName From Basic_Keyword_Test[/color][color=#000000]"[/color][color=#000000]; [/color][color=#008000]//[/color][color=#008000]sqlUpdateCommand could contain "insert" , "delete" , "update" operate [/color]
[color=#000000] [/color][color=#0000ff]private[/color]
[color=#0000ff]string[/color][color=#000000] sqlUpdateCommand [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]Delete From Basic_Keyword_Test Where KeywordID = 1[/color][color=#000000]"[/color][color=#000000]; [/color][color=#0000ff]public[/color]
[color=#0000ff]void[/color][color=#000000] UseSqlReader() { SqlConnection sqlConnection [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlConnection(sqlConnectionCommand); SqlCommand sqlCommand [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlCommand(); sqlCommand.CommandType [/color][color=#000000]=[/color][color=#000000] System.Data.CommandType.Text; sqlCommand.Connection [/color][color=#000000]=[/color][color=#000000] sqlConnection; sqlCommand.CommandText [/color][color=#000000]=[/color][color=#000000] sqlSelectCommand; sqlConnection.Open(); SqlDataReader sqlDataReader [/color][color=#000000]=[/color][color=#000000] sqlCommand.ExecuteReader(); [/color][color=#0000ff]while[/color][color=#000000](sqlDataReader.Read()) { [/color][color=#008000]//[/color][color=#008000]Get KeywordID and KeywordName , You can do anything you like. Here I just output them. [/color]
[color=#000000] [/color][color=#0000ff]int[/color][color=#000000] keywordid [/color][color=#000000]=[/color][color=#000000] ([/color][color=#0000ff]int[/color][color=#000000])sqlDataReader[[/color][color=#000000]0[/color][color=#000000]]; [/color][color=#008000]//[/color][color=#008000]the same as: int keywordid = (int)sqlDataReader["KeywordID"] [/color]
[color=#000000] [/color][color=#0000ff]string[/color][color=#000000] keywordName [/color][color=#000000]=[/color][color=#000000] ([/color][color=#0000ff]string[/color][color=#000000])sqlDataReader[[/color][color=#000000]1[/color][color=#000000]]; [/color][color=#008000]//[/color][color=#008000]the same as: string keywordName = (int)sqlDataReader["KeywordName"] [/color]
[color=#000000] Console.WriteLine([/color][color=#000000]"[/color][color=#000000]KeywordID = [/color][color=#000000]"[/color]
[color=#000000]+[/color][color=#000000] keywordid [/color][color=#000000]+[/color]
[color=#000000]"[/color][color=#000000] , KeywordName = [/color][color=#000000]"[/color]
[color=#000000]+[/color][color=#000000] keywordName); } sqlDataReader.Close(); sqlCommand.Dispose(); sqlConnection.Close(); } [/color][color=#0000ff]public[/color]
[color=#0000ff]void[/color][color=#000000] UseSqlStoredProcedure() { SqlConnection sqlConnection [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlConnection(sqlConnectionCommand); SqlCommand sqlCommand [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlCommand(); sqlCommand.CommandType [/color][color=#000000]=[/color][color=#000000] CommandType.StoredProcedure; sqlCommand.Connection [/color][color=#000000]=[/color][color=#000000] sqlConnection; sqlCommand.CommandText [/color][color=#000000]=[/color][color=#000000] storedProcedureName; sqlConnection.Open(); sqlCommand.ExecuteNonQuery(); [/color][color=#008000]//[/color][color=#008000]you can use reader here,too.as long as you modify the sp and let it like select * from .... [/color]
[color=#000000] sqlCommand.Dispose(); sqlConnection.Close(); } [/color][color=#0000ff]public[/color]
[color=#0000ff]void[/color][color=#000000] UseSqlDataSet() { SqlConnection sqlConnection [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlConnection(sqlConnectionCommand); SqlCommand sqlCommand [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlCommand(); sqlCommand.CommandType [/color][color=#000000]=[/color][color=#000000] System.Data.CommandType.Text; sqlCommand.Connection [/color][color=#000000]=[/color][color=#000000] sqlConnection; sqlCommand.CommandText [/color][color=#000000]=[/color][color=#000000] sqlSelectCommand; sqlConnection.Open(); SqlDataAdapter sqlDataAdapter [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlDataAdapter(); sqlDataAdapter.SelectCommand [/color][color=#000000]=[/color][color=#000000] sqlCommand; DataSet dataSet [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] DataSet(); [/color][color=#008000]//[/color][color=#008000]sqlCommandBuilder is for update the dataset to database [/color]
[color=#000000] SqlCommandBuilder sqlCommandBuilder [/color][color=#000000]=[/color]
[color=#0000ff]new[/color][color=#000000] SqlCommandBuilder(sqlDataAdapter); sqlDataAdapter.Fill(dataSet, dataTableName); [/color][color=#008000]//[/color][color=#008000]Do something to dataset then you can update it to Database.Here I just add a row [/color]
[color=#000000] DataRow row [/color][color=#000000]=[/color][color=#000000] dataSet.Tables[[/color][color=#000000]0[/color][color=#000000]].NewRow(); row[[/color][color=#000000]0[/color][color=#000000]] [/color][color=#000000]=[/color]
[color=#000000]10000[/color][color=#000000]; row[[/color][color=#000000]1[/color][color=#000000]] [/color][color=#000000]=[/color]
[color=#000000]"[/color][color=#000000]new row[/color][color=#000000]"[/color][color=#000000]; dataSet.Tables[[/color][color=#000000]0[/color][color=#000000]].Rows.Add(row); sqlDataAdapter.Update(dataSet, dataTableName); sqlCommand.Dispose(); sqlDataAdapter.Dispose(); sqlConnection.Close(); } }} [/color]
页:
[1]