C#实现ADO连接sql server数据库

小王不头秃

发布于 2024-06-19 15:03:37

2180

发布于 2024-06-19 15:03:37

我对ADO的理解不是多么的透彻，到目前为止我感觉ADO可能就是和JDBC一样的作用，都可以连接数据库。目前我实现的只有使用c#通过ADO来连接sqlserver（mysql）连接sqlServer数据库首先下载一个sqlserver数据库操作程序（如果可以不下就当我没说），然后如果没有sqlSclient程序包的话还要再VS中下载这个程序包，然后就可以进行写代码了连接mysql数据库的时候要下载一个驱动包 mysql-for-visualstudio-1.2.9.msi，版本要与电脑版本匹配，怎么下载合适的可以百度搜索，然后进行配置一波（同百度）就可通过存储数据的User类

using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApp2
{
    class User
    {
        int id;
        string name;
        public User(int id,string name)
        {
            this.id = id;
            this.name = name;
        }
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        public void show()
        {
            Console.WriteLine(id+"  "+name);
        }
    }
}

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200528192441913.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjI3MDc2,size_16,color_FFFFFF,t_70

//下述的程序包最好自己手动导入吧
using System;
using System.Runtime.CompilerServices;
using System.Data.SqlClient;
using System.Data;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.ComponentModel.Design;

namespace ConsoleApp2
{
    class Program
    {
        private string ConString = "Data Source=(local);Initial Catalog=cus;Integrated Security=TRUE";  //连接sqlserver的字符串，Data Source 对应上述的服务器名称
                 //Initial Catalog对应的数据库名称，最后一个参数按照原文中即可
        SqlConnection con;        
        //与sqlserver数据库建立的连接（mysql数据库对应的是MysqlConnection）
        public Program()
        {
            
        }
        //增
        public int Add(User user)
        {
            try
            {
                con = new SqlConnection(ConString);
                con.Open();
                string sql = "insert into Table_1 values('"+user.Name+"')";
                SqlCommand command = con.CreateCommand();       
                //执行的命令对象（mysql对应的MysqlCommand）
                command.CommandText = sql;
                int a = command.ExecuteNonQuery();
                con.Close();
                return a;
            }
            catch
            {
                Console.WriteLine("error");
                return -99;
            }
        }
        //查
        public User select(int id)
        {
            con = new SqlConnection(ConString);
            string sql = "select * from Table_1" ;
            con.Open();
            User user=null;
            SqlCommand command = new SqlCommand(sql, con);
            SqlDataReader reader = command.ExecuteReader();
            string imfor;
            if(reader.Read())
            {
                user=new User((int)reader[0],(string)reader[1]);
            }
            con.Close();
            return user;
        }
        //删
        public int delete(int id)
        {
            con = new SqlConnection(ConString);
            string sql = "delete from Table_1 where id='" + id + "'";
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            int a = command.ExecuteNonQuery();
            con.Close();
            return a;
        }
        //更新
        public int update(User user)
        {
            con = new SqlConnection(ConString);
            string sql = "update Table_1 set name='" + user.Name+"' where id="+user.Id;
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            int a = command.ExecuteNonQuery();
            con.Close();
            return a;
        }
        //查询所有数据
        public LinkedList<User> selectAll()
        {
            con = new SqlConnection(ConString);
            LinkedList<User> list = new LinkedList<User>();
            con.Open();
            string sql = "select * from Table_1 ";
            SqlCommand command = new SqlCommand(sql, con);
            SqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
            {
                User user = new User((int)reader[0], (string)reader[1]);
                list.AddLast(user);
            }
            con.Close();
            return list;
        }
        static void Main(string[] args)
        {
            Program p= new Program();
            User user1 = new User(3, "222");
            User user2 = new User(2, "33333");
            p.Add(user1);
            p.delete(1);
            p.select(2).show();
            p.update(user2);
            p.select(2);
            LinkedList<User> list= p.selectAll();


           foreach(User user in list)
            {
                user.show();
            }
            
        }
    }
}

实现连接mysql数据库的方式与上述代码相似 eg：

using System;
using System.ComponentModel.Design;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConsoleApp3
{
    class Program
    {
        private MySqlConnection con;
        public Program()
        {
            con = new MySqlConnection("server = 127.0.0.1; port = 3306; user = root; password = 123; database = cus");
        }
        public void select()
        {
            string sql = "select * from user";
            con.Open();
            MySqlCommand command = new MySqlCommand(sql, con);
            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
            {
                Console.WriteLine(reader[0]+" "+reader[1]);
            }
            con.Close();
        }
        static void Main(string[] args)
        {
            Program p = new Program();
            p.select();
        }
    }
}

只是实现了连接数据库还有查询功能，其他的功能代码类似于连接sqlserver数据库。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-06-19，如有侵权请联系 cloudcommunity@tencent.com 删除

sql