Chinaunix首页 | 论坛 | 博客
  • 博客访问: 457889
  • 博文数量: 62
  • 博客积分: 1312
  • 博客等级: 中尉
  • 技术积分: 1555
  • 用 户 组: 普通用户
  • 注册时间: 2012-02-24 18:10
文章分类

全部博文(62)

文章存档

2014年(1)

2013年(5)

2012年(56)

分类: WINDOWS

2012-02-25 18:05:57

     做上位机开发,离不开数据库。我曾经做过几个数据库相关的小项目,下面就总结三种常用的数据库操作的方法。
     以下是我在VS2008上写的一个类,里边有三种方法比较典型。
 
源代码如下:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. namespace DatabaseOperate
  7. {
  8.  class SqlOperateInfo
  9.  {
  10.   //Suppose your ServerName is "aa",DatabaseName is "bb",UserName is "cc", Password is "dd"

  11.   private string sqlConnectionCommand = "Data Source=aa;Initial Catalog=bb;User ID=cc;Pwd=dd";
  12.   //This table contains two columns:KeywordID int not null,KeywordName varchar(100) not null

  13.   private string dataTableName = "Basic_Keyword_Test";
  14.   private string storedProcedureName = "Sp_InertToBasic_Keyword_Test";
  15.   private string sqlSelectCommand = "Select KeywordID, KeywordName From Basic_Keyword_Test";
  16.   //sqlUpdateCommand could contain "insert" , "delete" , "update" operate

  17.   private string sqlUpdateCommand = "Delete From Basic_Keyword_Test Where KeywordID = 1";
  18.   public void UseSqlReader()
  19.   {
  20.    SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
  21.    SqlCommand sqlCommand = new SqlCommand();
  22.    sqlCommand.CommandType = System.Data.CommandType.Text;
  23.    sqlCommand.Connection = sqlConnection;
  24.    sqlCommand.CommandText = sqlSelectCommand;
  25.    sqlConnection.Open();
  26.    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
  27.    while(sqlDataReader.Read())
  28.    {
  29.     //Get KeywordID and KeywordName , You can do anything you like. Here I just output them.

  30.     int keywordid = (int)sqlDataReader[0];
  31.     //the same as: int keywordid = (int)sqlDataReader["KeywordID"]

  32.     string keywordName = (string)sqlDataReader[1];
  33.     //the same as: string keywordName = (int)sqlDataReader["KeywordName"]

  34.     Console.WriteLine("KeywordID = " + keywordid + " , KeywordName = " + keywordName);
  35.    }
  36.    sqlDataReader.Close();
  37.    sqlCommand.Dispose();
  38.    sqlConnection.Close();
  39.   }
  40.   public void UseSqlStoredProcedure()
  41.   {
  42.    SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
  43.    SqlCommand sqlCommand = new SqlCommand();
  44.    sqlCommand.CommandType = CommandType.StoredProcedure;
  45.    sqlCommand.Connection = sqlConnection;
  46.    sqlCommand.CommandText = storedProcedureName;
  47.    sqlConnection.Open();
  48.    sqlCommand.ExecuteNonQuery();
  49.    //you can use reader here,too.as long as you modify the sp and let it like select * from ....

  50.    sqlCommand.Dispose();
  51.    sqlConnection.Close();
  52.   }
  53.   public void UseSqlDataSet()
  54.   {
  55.    SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
  56.    SqlCommand sqlCommand = new SqlCommand();
  57.    sqlCommand.CommandType = System.Data.CommandType.Text;
  58.    sqlCommand.Connection = sqlConnection;
  59.    sqlCommand.CommandText = sqlSelectCommand;
  60.    sqlConnection.Open();
  61.    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
  62.    sqlDataAdapter.SelectCommand = sqlCommand;
  63.    DataSet dataSet = new DataSet();
  64.    //sqlCommandBuilder is for update the dataset to database

  65.    SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
  66.    sqlDataAdapter.Fill(dataSet, dataTableName);
  67.    //Do something to dataset then you can update it to  Database.Here I just add a row

  68.    DataRow row = dataSet.Tables[0].NewRow();
  69.    row[0] = 10000;
  70.    row[1] = "new row";
  71.    dataSet.Tables[0].Rows.Add(row);
  72.    sqlDataAdapter.Update(dataSet, dataTableName);
  73.    sqlCommand.Dispose();
  74.    sqlDataAdapter.Dispose();
  75.    sqlConnection.Close();
  76.   }
  77.  }
  78. }
阅读(2221) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~