C# 连接数据库

C# 连接数据　

　　一、SQL SERVER

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

string connectStr="Server=IP;Data Source=IP;Initial Catalog=数据库;User Id=sa;Password=密码";

View Code

　　　创建连接 SqlConnection(string source)

SqlConnection conn=new SqlConnection(connection);

View Code

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

string sql="select * from 表";

　　　　　　SqlCommand cmd=new SqlCommand(sql,conn);

View Code

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

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

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

　　　　　　　程序分别如下：

 cmd.ExecuteNonQuery();

　　　　　　　　　　　　　　 cmd.ExecuteReader();

　　　　　　　　　　　　　　 cmd.ExecuteScalar();

View Code

　　　遍历IDataReader

SqlDataReader reader=cmd.ExecuteReader();

　　　　　　　　while(reader.Read())

　　　　　　　　{

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

　　　　　　　　}

View Code

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

conn.close();

View Code

　　　SQLDataAdapter类可以一次取出数据

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

View Code

　　　　完整代码

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 直接上代码

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 直接上代码

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