首页 > 代码库 > C# 连接数据库

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