熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

C#如何使用數據庫

2013-11-13 09:51:20  來源: .NET編程 

  數據庫在我們開發項目中那是最常見的了無論復雜的項目還是簡單的項目只要涉及數據的操作(增刪改查)都會用到數據庫當然使用的數據庫的種類根據項目的需求不同也是不同的在我所做的項目中就是用過ACCESS SQL Server Oracle g因此簡單總結下c#如何使用數據庫的程序執行sql語句(包括單條sql多條sql存儲過程函數等等)大體有三個步驟連接數據庫執行sql語句返回結果那麼我們來看看c#是如何完成這三個步驟的無論哪種數據庫都是這三個步驟那麼就不按數據庫分了按照返回結果的類型來分類吧

    執行sql語句返回受影響的行數

  (一條sql語句

  view plainprint?

  public static int ExecuteNonQuery(string connStr string sql)

  { OleDbConnection conn = nulltry { conn = new OleDbConnection(connStr)connOpen()OleDbCommand command = new OleDbCommand(sql conn)return commandExecuteNonQuery()} catch { return } finally { if (conn != null)

  connClose()}(多條sql語句返回受影響行數由於多條sql為了保證數據庫的正確性最好使用事務方式運行

  view plainprint?

  public static int ExecuteNonQuery(string connStr List<string> sqlList)

  { int executeCount =

  OleDbTransaction transaction = null

  OleDbConnection conn = nulltry { conn = new OleDbConnection(connStr)connOpen()

  transaction = connBeginTransaction()

  OleDbCommand command = new OleDbCommand()

  commandConnection = conn

  commandTransaction = transaction

  for (int i = i < sqlListCount i++)

  { commandCommandText = sqlList[i]

  executeCount += commandExecuteNonQuery()} transactionCommit()} catch { if (transaction != null)

  transactionRollback()return } finally { if (conn != null)

  connClose()} return executeCount} 返回DataSet

  view plainprint?

  public static DataSet ExecuteQueryReturnDataSet(string connStr string sql)

  { OleDbConnection conn = nullDataSet ds = nulltry { conn = new OleDbConnection(connStr)ds = new DataSet()connOpen()

  OleDbDataAdapter command = new OleDbDataAdapter(sql conn)commandFill(ds datasetName} catch { } finally { if (conn != null)

  connClose()} return ds} 返回OleDbDataReader

  view plainprint?

  public static OleDbDataReader ExecuteQueryReturnReader(string sql string connStr)

  { OleDbConnection conn = new OleDbConnection()OleDbDataReader reader = null

  try { conn = new OleDbConnection(connStr)connOpen()

  OleDbCommand command = connCreateCommand()commandCommandText = sqlreader = commandExecuteReader()} catch { } finally { if (conn != null)

  connClose()} return reader} 執行存儲過程並返回受影響行數(以Oracle為例)

  view plainprint?

  public static int RunProcedure(string storedProcName IDataParameter[] parameters)

  { int result = try { using (OracleConnection connection = new OracleConnection(GetConnectionString()))

  { connectionOpen()

  OracleCommand command = BuildQueryCommand(connection storedProcName parameters)

  result = commandExecuteNonQuery()} catch (Exception e)

  {

  }

  return result} private static OracleCommand BuildQueryCommand(OracleConnection connection string storedProcName

  IDataParameter[] parameters)

  { OracleCommand command = new OracleCommand(storedProcName connection)

  commandCommandType = CommandTypeStoredProcedure

  foreach (OracleParameter parameter in parameters)

  { commandParametersAdd(parameter)}

  return command} 帶參數的sql語句

  view plainprint?

  public static int ExecuteNonQuery(string sqlstring connStr OleDbParameter[] commandParameters)

  { int count = OleDbCommand cmd = new OleDbCommand()

  OleDbConnection conn = nulltry { conn = new OleDbConnection(connStr)PrepareCommand(cmd connsql commandParameters)count = cmdExecuteNonQuery()cmdParametersClear()} catch { } finally {

  if (conn != null)

  { connClose()} return count}

  private static void PrepareCommand(OleDbCommand cmd OleDbConnection conn string sql OleDbParameter[] cmdParms)

  { if (connState != ConnectionStateOpen)

  connOpen()

  cmdConnection = conncmdCommandText = sqlcmdCommandType = CommandTypeText

  if (cmdParms != null)

  { foreach (OleDbParameter parm in cmdParms)

  cmdParametersAdd(parm)}一般常用的就這麼幾種數據庫的幾個重要對象分別是OleDbCommandOleDbConnectionOleDbDataReaderOleDbDataAdapterDataSet對於不同數據庫有幾個對象需要對應相應數據庫的對象OleDbCommandOleDbConnectionOleDbDataReaderOleDbDataAdapterOracle下OleDb換成OracleSql Server下 則換成SQL


From:http://tw.wingwit.com/Article/program/net/201311/11763.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.