程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> c#銜接sqlserver數據庫、拔出數據、從數據庫獲得時光示例

c#銜接sqlserver數據庫、拔出數據、從數據庫獲得時光示例

編輯:C#入門知識

c#銜接sqlserver數據庫、拔出數據、從數據庫獲得時光示例。本站提示廣大學習愛好者:(c#銜接sqlserver數據庫、拔出數據、從數據庫獲得時光示例)文章只能為提供參考,不一定能成為您想要的結果。以下是c#銜接sqlserver數據庫、拔出數據、從數據庫獲得時光示例正文


c#銜接sqlserver、拔出數據、從數據庫獲得時光


using System;
using System.Data.SqlClient;

namespace Test
{
    //銜接數據庫
    public class Connection
    {
        private static string connectionString =
            "Server = 192.168.1.222;" +
            "Database = Test;" +
            "User ID = Test;" +
            "Password = abc123;";

        /// <summary>
        /// 銜接數據庫
        /// </summary>
        /// <returns></returns>
        private SqlConnection ConnectionOpen()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            return conn;
        }

        /// <summary>
        /// 向表(Table)中拔出一條數據
        /// </summary>
        public void Insert(string value1, string value2, string value3, DateTime dateTime)
        {
            SqlConnection conn = ConnectionOpen();
            string sql =
                "insert into Table(row1, row2, row3, DateTime) values ('" +
                value1 + "', '" + value2 + "', '" + value3 + "', '" + dateTime + "')";
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.ExecuteReader();
  
            conn.Close();
        }

        /// <summary>
        /// 從數據庫中獲得以後時光
        /// </summary>
        /// <returns></returns>
        public DateTime GetDateTimeFromSQL()
        {
            SqlConnection conn = ConnectionOpen();
            string sql = "select getdate()";
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader reader = comm.ExecuteReader();
            DateTime dt;
            if (reader.Read())
            {
                dt = (DateTime)reader[0];
                conn.Close();
                return dt;
            }
            conn.Close();
            return DateTime.MinValue;
        }
    }
}

c#銜接SQL Server 2008示例

/*
* 解釋
* 功效解釋:數據拜訪封裝。一切數據都要經由過程這個類界說的dbConnection拜訪數據庫。
* 同時,界說了通用的cmd,和cmd經常使用的拜訪存儲進程的辦法RunPro  
*   
* 作者: RogerWang
*   
* 創立日期:2006-02-15
*   
*/

using System;
using System.Data;
using System.Data.SqlClient;


namespace insurer
{
  /// <summary>
  /// DataAccess 的摘要解釋。
  /// </summary>
  public class DataAccess
  {
    private readonly string SQLCONNECTSTR = "server=(local);uid=sa;pwd=lwrong;database=insurer";
    private SqlConnection dbConnection;
    private readonly string RETUENVALUE = "RETURNVALUE";   


    //斷定要不要啟動事務
                private bool startrans = false;

    //為處理多筆數據導入的成績,特添加的事務處置屬性
    private SqlTransaction trans = null;

               
    //界說能否啟動事務屬性
    public bool StartTrans
    {
      get
      {
        return startrans;
      }
      set
      {
        startrans = value;
      }
    }


    //界說事務
    public SqlTransaction Trans
    {
      get
      {
        return trans;
      }
      set
      {
        if (value != null)
        {
          trans = value;
        }
      }
    }

    //創立翻開dbConnection對象
    public void OpenConnection()
    {
      if ( dbConnection == null )
      {
        dbConnection = new SqlConnection(SQLCONNECTSTR);
      }

      if ( dbConnection.State == ConnectionState.Closed )
      {
        try
        {
          dbConnection.Open();
        }
        catch(Exception ex)
        {
          SystemError.SystemLog(ex.Message);
        }
        finally
        {
        }
      }
    }

    //釋放dbConnection對象
    public void CloseConnection()
    {
      if (dbConnection != null)
      {
        if (dbConnection.State == ConnectionState.Open)
        {
          dbConnection.Dispose();
          dbConnection = null;
        }
      }
    }


    //

    //創立cmd,留意dbconnection在該函數中創立,但沒有在這函數中釋放。
    //在准確的面向對象設計辦法中,對象應當是誰創立,誰就應當擔任釋放。按這個不雅點,這個進程有些不平安!!!!
        private SqlCommand CreateCommand(string ProName,SqlParameter[] prams)
    {
      OpenConnection();
      SqlCommand cmd = new SqlCommand(ProName,dbConnection);
      cmd.CommandType = CommandType.StoredProcedure;


      //假如停止事務處置,那末對cmd的Transaction的事務賦值
      if (StartTrans)
      {
        cmd.Transaction = Trans;
      }


      if ( prams != null)
      {
        foreach(SqlParameter parameter in prams)
        {
          cmd.Parameters.Add(parameter);
        }
      }

      //cmd.Parameters.Add(

      return cmd;

    }


    /// <summary>
    /// 創立cmd,並履行響應的操作。 然後釋放cmd!
    ///   
    /// 該函數是履行cmd沒有前往值,且沒有參數的辦法。
    /// </summary>
    /// <param name="ProName"></param>
    public bool RunProc(string ProName)
    {
      SqlCommand cmd = CreateCommand(ProName,null);

      bool k = false;
      try
      {
        k = (bool)cmd.ExecuteScalar();
      }
      catch(Exception ex)
      {
        SystemError.SystemLog(ex.Message);
      }
      finally
      {
        cmd.Dispose();
      }
      return k;

    }

    /// <summary>
    /// 創立cmd,並履行響應的操作。 然後釋放cmd!
    ///   
    /// 該函數是履行cmd沒有前往值,但有參數的辦法。
    /// </summary>
    /// <param name="ProName"></param>
    /// <param name="prams"></param>
    public bool RunProc(string ProName,SqlParameter[] prams)
    {
      SqlCommand cmd = CreateCommand(ProName,prams);

      bool k = false;
      try
      {
        k = (bool) cmd.ExecuteScalar();
      }
      catch(Exception ex)
      {
        SystemError.SystemLog(ex.Message);
      }
      finally
      {   
        cmd.Dispose();
        //Close();
      }
      return k;
    }

    /// <summary>
    /// 創立cmd,並履行響應的操作。 然後釋放cmd!
    ///   
    /// 該函數是履行cmd帶有前往值,但沒有參數的辦法。   
    /// </summary>
    /// <param name="ProName"></param>
    /// <param name="dataReader"></param>
    public void RunProc(string ProName,out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(ProName,null);
      dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      try
      {

      }
      catch(Exception ex)
      {
        SystemError.SystemLog(ex.Message);
      }
      finally
      {
        cmd.Dispose();
      }
    }

    /// <summary>
                /// 創立cmd,並履行響應的操作。 然後釋放cmd!
    ///   
    /// 該函數是履行cmd帶有前往值,且有參數的辦法。
    /// </summary>
    /// <param name="ProName"></param>
    /// <param name="prams"></param>
    /// <param name="dataReader"></param>
    public void RunProc(string ProName,SqlParameter[] prams,out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(ProName,prams);
      dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
      try
      {

      }
      catch(Exception ex)
      {
        SystemError.SystemLog(ex.Message);
      }
      finally
      {
        cmd.Dispose();
      }
    }


    /// <summary>
    /// 創立cmd的參數
    /// 該辦法的思緒就是按前提生成一個SqlParameter對象。
    /// 生成對象後,再給對象賦響應的前往值類型
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="DbType"></param>
    /// <param name="size"></param>
    /// <param name="direction"></param>
    /// <param name="Value"></param>
    /// <returns></returns>
    public SqlParameter CreateParam(string ParamName, SqlDbType DbType, int size,ParameterDirection direction,object Value)
    {
      SqlParameter param;

      if (size > 0)
      {
        param = new SqlParameter(ParamName,DbType,size);
      }
      else
      {
        param = new SqlParameter(ParamName,DbType);
      }

      param.Direction = direction;

      param.Value = Value;

      return param;
    }

    /// <summary>
    /// 創立cmd的輸出參數
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="DbType"></param>
    /// <param name="size"></param>
    /// <param name="Value"></param>
    /// <returns></returns>
    public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int size, object Value)
    {
      return CreateParam(ParamName,DbType,size,ParameterDirection.Input,Value);
    }

    /// <summary>
    /// 創立cmd的輸入參數
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="DbType"></param>
    /// <param name="size"></param>
    /// <returns></returns>
    public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int size)
    {
      return CreateParam(ParamName,DbType,size,ParameterDirection.Output,null);
    }

    /// <summary>
    /// 創立cmd帶有前往值的參數
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="DbType"></param>
    /// <param name="size"></param>
    /// <returns></returns>
    public SqlParameter CreateReturnParam(string ParamName,SqlDbType DbType, int size)
    {
      return CreateParam(ParamName,DbType,size,ParameterDirection.ReturnValue,null);
    }


    //開端一個事務
    public void BeginTrans()
    {
      OpenConnection();

      Trans = dbConnection.BeginTransaction(IsolationLevel.Serializable);
    }


    public void Commit()
    {
      if (Trans != null)
      {
        Trans.Commit();
      }
    }

    public void Rollback()
    {
      if (Trans != null)
      {
        Trans.Rollback();
      }
    }
  }
}

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved