首页 > 代码库 > 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=密码";
创建连接 SqlConnection(string source)
SqlConnection conn=new SqlConnection(connection);
包装Sql语句 SqlCommand(string sql,SqlConnection conn)
string sql="select * from 表"; SqlCommand cmd=new SqlCommand(sql,conn);
执行查询 ①ExecuteNonQuery()-------返回受影响的行数
②ExecuteReader()---------返回IDataReader
③ExecuteScalar()---------返回结果集第一行第一列的值
程序分别如下:
cmd.ExecuteNonQuery(); cmd.ExecuteReader(); cmd.ExecuteScalar();
遍历IDataReader
SqlDataReader reader=cmd.ExecuteReader(); while(reader.Read()) { reader[0] //这里相当于一个多维数组 }
关闭数据库Close() 使用数据库应马上关闭
conn.close();
SQLDataAdapter类可以一次取出数据
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);//不用SqlCommand 和 三个查询函数DataSet ds = new DataSet();da.Fill(ds, "table");conn.Close();return ds.Tables["table"];
完整代码
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; } }}
二、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; } }}
三、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 }}
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。