C# 连接数据库

                                                                     C# 连接数据 

  一、SQL SERVER

   连接字符串为:Server=IP;Data Source=IP;Initial Catalog=数据库;User Id=sa;Password=密码

      

C# 连接数据库
string connectStr="Server=IP;Data Source=IP;Initial Catalog=数据库;User Id=sa;Password=密码";
View Code

 

   创建连接 SqlConnection(string source)

      

C# 连接数据库
SqlConnection conn=new SqlConnection(connection);
View Code

 

   包装Sql语句 SqlCommand(string sql,SqlConnection conn)

      

C# 连接数据库
string sql="select * from 表";

      SqlCommand cmd=new SqlCommand(sql,conn);
View Code

 

 

    执行查询 ①ExecuteNonQuery()-------返回受影响的行数

        ②ExecuteReader()---------返回IDataReader

        ③ExecuteScalar()---------返回结果集第一行第一列的值

        程序分别如下:

              

C# 连接数据库
 cmd.ExecuteNonQuery();

               cmd.ExecuteReader();

               cmd.ExecuteScalar();
View Code

 

 

   遍历IDataReader

        

C# 连接数据库
SqlDataReader reader=cmd.ExecuteReader();

        while(reader.Read())

        {

          reader[0]    //这里相当于一个多维数组

        }
View Code

    关闭数据库Close()  使用数据库应马上关闭

        

C# 连接数据库
conn.close();
View Code

 

   SQLDataAdapter类可以一次取出数据

C# 连接数据库
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);//不用SqlCommand 和 三个查询函数
DataSet ds = new DataSet();
da.Fill(ds, "table");
conn.Close();
return ds.Tables["table"];
View Code

    完整代码

      

C# 连接数据库
using System.Data;
using System.Data.SqlClient;


public class ConSql 
{
    private static string source=null;
    private static SqlConnection conn = null;
    public static  long ECR(string SQL)
    {
        try
        {
            SqlConnection conn = getConnection();
            conn.Open();
            SqlCommand select = new SqlCommand(SQL, conn);
            object Row =select.ExecuteScalar();
            Close();
            if (Row == null)
            {
                return -1;
            }
            else
            {
                return (int)Row;
            }
        }
        catch
        {
            return -2;
        }
    }

    public static DataTable ERD(string SQL)
    {
        try
        {
            SqlConnection conn = getConnection();
            conn.Open();
            SqlDbDataAdapter da = new SqlDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                conn.Close();
                return ds.Tables["table"];
       
        }
        catch
        {

            return ;
        }
    }

    public static bool ENQ(string SQL)
    {

        try
        {
            SqlConnection conn = getConnection();
            conn.Open();
            SqlCommand select = new SqlCommand(SQL, conn);
            select.ExecuteNonQuery();
            Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    protected static SqlConnection getConnection()
    {
        try
        {
            SqlConnection conn = new SqlConnection(source);
            return conn;
        }
        catch
        {
            SqlConnection conn = null;
            return conn;
        }
    }

    public static bool SetConnectionStr(string str)
    {
        try
        {
            source = str;
            return true;
        }
        catch
        {
            return false;
        }
    }
    public static bool Close()
    {
        try
        {
            conn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }
}
View Code

    二、MySql

    同sql server 直接上代码

      

C# 连接数据库
using MySQLDriverCS;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace hh
{
    class MySQL
    {
        private MySQLConnection conn = null;
        private MySQLCommand comn = null;
        public  MySQL(string ip, string database, string username, string password)
        {
            conn = new MySQLConnection(new MySQLConnectionString(ip, database, username, password).AsString);
        }
        public DataTable SQL_DataTable(string SQL)
        {
            try
            {
                conn.Open();
                setMySQLCommand("set names gd2312");
                comn.ExecuteNonQuery();
                MySQLDataAdapter mda = new MySQLDataAdapter(SQL, conn);
                DataSet ds = new DataSet();
                mda.Fill(ds, "table");
                DataTable dt = ds.Tables["table"];
                conn.Close();
                return dt;
            }
            catch 
            {
                return null;
            }
        }
        public long SQL_Number(string SQL)
        {
            try
            {
                conn.Open();
                setMySQLCommand("set names gd2312");
                long num = Convert.ToInt64(comn.ExecuteScalar());
                return num;
            }
            catch
            {
                return -1;
            }
        }
        public bool SQL_Cmd(string SQL)
        {
            try
            {
                conn.Open();
                setMySQLCommand("set names gd2312");
                comn.ExecuteReader();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool setMySQLCommand(string comand)
        {
            comn = new MySQLCommand(comand, conn);
            return true;
        }

    }
}
View Code

    三、Access

      同SQL 直接上代码

      

C# 连接数据库
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Configuration;

namespace Data.Access
{
    class AccessHelper
    {
        #region  private AccessbConnection DataConection()+Access数据库连接
        /// <summary>
        /// Access数据库连接
        /// </summary>
        /// <returns></returns>
        private OleDbConnection AccessConection()
        {
            return new OleDbConnection(ConfigurationManager.ConnectionStrings["strConn"].ToString());
        }
        #endregion

        #region public DataTable AccessReader(string sql)+Access数据库查询
        /// <summary>
        /// Access数据库查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable AccessReader(string sql)
        {
            using (OleDbConnection conn = this.AccessConection())
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                conn.Close();
                return ds.Tables["table"];
            }
        }
        #endregion

        #region public int AccessQuery(string sql)+Access数据库的增、删、改.返回受影响行数
        /// <summary>
        /// Access数据库的增、删、改.返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int AccessQuery(string sql)
        {
            using (OleDbConnection conn = this.AccessConection())
            {
                conn.Open();
                OleDbCommand oc = new OleDbCommand(sql, conn);
                int result = oc.ExecuteNonQuery();
                conn.Close();
                return result;
            }
        }
        #endregion

        #region public object AccessScaler(string sql)+ Access数据库的增、删、改.返回结果集第一行第一列的值
        /// <summary>
        ///  Access数据库的增、删、改.返回结果集第一行第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object AccessScaler(string sql)
        {
            using (OleDbConnection conn = this.AccessConection())
            {
                conn.Open();
                OleDbCommand oc = new OleDbCommand(sql, conn);
                object result = oc.ExecuteScalar();
                conn.Close();
                return result;
            }
        }
        #endregion
    }
}
View Code

 

      

    

C# 连接数据库,布布扣,bubuko.com

C# 连接数据库

上一篇:深入Mysql 导入导出


下一篇:尝试加载 Oracle 客户端库时引发 BadImageFormatException。问题记录