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();
      }
    }
  }
}