unity连接SqlServer

前段时间项目需要用到数据库,就提前自学了一部分数据库的内容,比着MOMO大神的教程http://www.xuanyusong.com/archives/2326封装了一个可以操作SqlServer数据库的基类,自己加了些注释,都测试过了。可以使用,使用前记得把数据库的ip和数据库名改成自己的。

在unity里调用的话只需要new一下再通过这个对象访问就可以了。以后我还会继续维护这个基类

using System.Collections;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
using System;
using UnityEngine;
using System.Data;

public class SQLServer
{
    static string host = "server=192.168.1.126,1433;";  //实例的地址
    public static string dataBase = "database=Water;";   //数据库名
    static string uid = "uid=sa;";  //登录的用户名
    static string pwd = "pwd=1234;";  //登录密码

    private static SqlConnection conn;
    private SqlCommand cmd;
    private static SqlDataReader sdr;
    private SqlDataAdapter sda;

    public SQLServer()
    {
        LinkSQL();
    }
    ~SQLServer()
    {
        conn.Dispose();
    }
    void LinkSQL()
    {
        try
        {
            conn = new SqlConnection(host + dataBase + uid + pwd);
            conn.Open();
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString());
        }
    }
    /// <summary>
    /// 检查数据库是否连接成功,返回一个bool值
    /// </summary>
    /// <returns></returns>
    public bool isOpen()
    {
        return conn.State == ConnectionState.Open ? true : false;
    }

    /// <summary>
    /// 重新打开数据库
    /// </summary>
    public void ReOpen()
    {
        try
        {
            conn.Open();
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString());
        }
    }

    /// <summary>
    /// 暂时关闭数据库
    /// </summary>
    public void Close()
    {
        if (conn.State == ConnectionState.Closed) return;
        try
        {
            conn.Close();
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString());
        }
    }

    /// <summary>
    /// 切断数据库的连接
    /// </summary>
    public void ShutDown()
    {
        try
        {
            conn.Dispose();
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString());
        }
    }

    /// <summary>
    /// 创建一个表,参数(模式名,表名,表头名数组,表头数据类型数组),主键是表头第一个
    /// </summary>
    /// <param name="name"></param>
    /// <param name="col"></param>
    /// <param name="colType"></param>
    public void CreateTable(string SchemaName, string TableName, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            throw new Exception("数据和数据类型不匹配!");
        }
        string query = "CREATE TABLE " + "\"" + SchemaName + "\"" + '.' + TableName + "(" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; ++i)         // CREATE TABLE "Schema".tableName()
        {
            query += "," + col[i] + " " + colType[i];
        }
        query += ",PRIMARY KEY(" + col[0] + "));";
        cmd.CommandText = query;
        cmd.ExecuteScalar();
    }

    /// <summary>
    /// 插入一行数据,参数(模式名,表名,要插入的一行数据)
    /// </summary>
    /// <param name="SchemaName"></param>
    /// <param name="tableName"></param>
    /// <param name="values"></param>
    public void InsertInto(string SchemaName, string tableName, string[] values)
    {
        string query = "INSERT INTO \"" +SchemaName+"\"."+ tableName + " VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        cmd.CommandText = query;
        cmd.ExecuteScalar();
    }

    /// <summary>
    /// 插入部分或全部数据,参数(模式名,表名,要插入的表头名,要插入的数据)
    /// </summary>
    /// <param name="SchemaName"></param>
    /// <param name="tableName"></param>
    /// <param name="col"></param>
    /// <param name="values"></param>
    public void InsertInto(string schemaName, string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            throw new Exception("数据和数据类型不匹配!");
        }
        string query = "INSERT INTO \"" +schemaName+"\"."+ tableName + " (" + col[0];
        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i];
        }
        query += ") VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        cmd.CommandText = query;
        cmd.ExecuteScalar();
    }

    /// <summary>
    /// 查找数据,参数(模式名,表名,要查找的表头,where条件参数1,where条件运算符,where条件参数2),返回一个DataTable对象
    /// </summary>
    /// <param name="schemaName"></param>
    /// <param name="tableName"></param>
    /// <param name="items"></param>
    /// <param name="col"></param>
    /// <param name="operation"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public DataTable SelectWhere(string schemaName, string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length)
        {
            throw new Exception("col.Length != operation.Length != values.Length");
        }
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i)
        {
            query += ", " + items[i];
        }
        query += " FROM \"" + schemaName + "\"." + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i)
        {
            query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
        }
        cmd.CommandText = query;
        sda = new SqlDataAdapter();
        sda.SelectCommand = cmd;
        DataSet ds = new DataSet();
        sda.Fill(ds, "\"" + schemaName + "\"." + tableName);
        DataTable table = ds.Tables[0];
        if (table != null)
        {
            return table;
        }
        else
        {
            throw new Exception("要查询的表为空!");
        }
    }

    /// <summary>
    /// 修改数据,参数(模式名,表名,set后参数1,set后参数2,where参数1,where参数2,),参数运算符全为=
    /// </summary>
    /// <param name="schemaName"></param>
    /// <param name="tableName"></param>
    /// <param name="cols"></param>
    /// <param name="colsvalues"></param>
    /// <param name="selectkey"></param>
    /// <param name="selectvalue"></param>
    public void UpdateInto(string schemaName, string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
    {
        string query = "UPDATE " + "\""+schemaName+"\"."+tableName + " SET " + cols[0] + " = " + "'"+colsvalues[0]+"'";
        for (int i = 1; i < colsvalues.Length; ++i)
        {

            query += ", " + cols[i] + " =" + "'"+colsvalues[i]+"'";
        }

        query += " WHERE " + selectkey + " = " +"'"+ selectvalue + "' ";
        cmd.CommandText = query;
        cmd.ExecuteScalar();
    }

    /// <summary>
    /// 删除一整行数据,参数(模式名,表名,where参数1=,where参数2,AND或者OR)
    /// </summary>
    /// <param name="schemaName"></param>
    /// <param name="tableName"></param>
    /// <param name="cols"></param>
    /// <param name="colsvalues"></param>
    /// <param name="operation"></param>
    public void Delete(string schemaName, string tableName, string[] cols, string[] colsvalues,string operation)
    {
        string query = "DELETE FROM " + "\"" +schemaName+ "\"."+tableName +" WHERE " + cols[0] + " = " + "'"+colsvalues[0]+"'";
        for (int i = 1; i < colsvalues.Length; ++i)
        {
            query += " "+operation+" " + cols[i] + " = " +"'"+ colsvalues[i]+"'";
        }
        cmd.CommandText = query;
        cmd.ExecuteScalar();
    }
}


©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页