wb_InteractiveClassroomLocalDataScan/AppCode/SaveDataOperater.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Net.Http;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using System.Web;
using System.Windows.Forms;
using Dapper;
using Newtonsoft.Json;

    public static class SaveDataOperater
    {
        public static void AddPenData()
        {

        }
        private static string connectionString = "Server=.;Database=Tstudy;User Id=sa;Password=Woshiren123;Integrated Security=true;";


    public static void InsertIntoTable(string sql)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    /// <summary>
    /// 删除末班
    /// </summary>
    public static void DeleteTempListiInfo()
    {
        string sql = "DELETE TempListiInfo";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            connection.Execute(sql);
        }
    }

    public static void AddTempListiInfo(List<TempListiInfoServer> temp)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            // 使用事务确保数据一致性
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    string sql = @"INSERT INTO TempListiInfo (QuestionType, PageNo, pageWidth,pageHeight,LayOutData,BackGroundImg) 
                                VALUES (@QuestionType, @PageNo, @pageWidth,@pageHeight,@LayOutData,@BackGroundImg)";

                    using (SqlCommand command = new SqlCommand(sql, connection, transaction))
                    {
                        command.Parameters.Add("@QuestionType", System.Data.SqlDbType.NVarChar,50);
                        command.Parameters.Add("@PageNo", System.Data.SqlDbType.NVarChar, 50);
                        command.Parameters.Add("@pageWidth", System.Data.SqlDbType.Int);
                        command.Parameters.Add("@pageHeight", System.Data.SqlDbType.Int);
                        command.Parameters.Add("@LayOutData", System.Data.SqlDbType.NVarChar, -1);
                        command.Parameters.Add("@BackGroundImg", System.Data.SqlDbType.NVarChar, 200);

                        foreach (var t in temp)
                        {
                            command.Parameters["@QuestionType"].Value =t.type;
                            command.Parameters["@PageNo"].Value = t.pageNo;
                            command.Parameters["@pageWidth"].Value = t.widthDotMatrix;
                            command.Parameters["@pageHeight"].Value = t.hightDotMatrix;
                            command.Parameters["@LayOutData"].Value = t.areaPoints ?? "";
                            command.Parameters["@BackGroundImg"].Value = t.previewUrl ?? "";
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }

    public static void AddStudentInfo(List<StudentPen> studentPens)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // 使用事务确保数据一致性
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    string sql = @"INSERT INTO StudentInfo (StudentID, StudentName, PenNo) 
                                VALUES (@StudentID, @StudentName, @PenNo)";

                    using (SqlCommand command = new SqlCommand(sql, connection, transaction))
                    {
                        command.Parameters.Add("@StudentID", System.Data.SqlDbType.BigInt);
                        command.Parameters.Add("@StudentName", System.Data.SqlDbType.NVarChar, 100);
                        command.Parameters.Add("@PenNo", System.Data.SqlDbType.NVarChar, 50);

                        foreach (var pen in studentPens)
                        {
                            command.Parameters["@StudentID"].Value = pen.studentId;
                            command.Parameters["@StudentName"].Value = pen.studentName ?? (object)DBNull.Value;
                            command.Parameters["@PenNo"].Value = pen.penSerialNo ?? "";
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }

    public static void DeleteStudentInfo()
    {
        string sql = "Delete StudentInfo";
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            connection.Execute(sql);
        }
    }

    /// <summary>
    /// 删除所有笔的表
    /// </summary>
    public static void DropAllPenTable()
    {
        string  sql = @"SELECT 'DROP TABLE ' + TABLE_SCHEMA + '.""' + TABLE_NAME + '"";' AS DropCommands
                        FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_TYPE = 'BASE TABLE'
                          AND TABLE_NAME NOT IN ('PenStatus', 'QuestionTask','StudentInfo','TempMain','TempListiInfo','TaskGoServer');";
        using (var connection = new SqlConnection(connectionString))
        {
            StringBuilder sb = new StringBuilder();
            connection.Open();
            bool isgo = false;
            foreach (string list in connection.Query<string>(sql).AsList())
            {
                isgo = true;
                sb.Append(list);
            }
            if (isgo)
            {
                connection.Execute(sb.ToString());
            }
           
        }
    }

    public static List<PenStatus> GetAllPenStatus()
    {
        string sql = "SELECT PenNo as serialNo, Status as onlineStatus ,charge as power　FRom PenStatus";
        List<PenStatus> penStatusList = new List<PenStatus>();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(sql, connection))
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    PenStatus status = new PenStatus
                    {
                        serialNo = reader.GetString(0),
                        onlineStatus = reader.GetBoolean(1),
                        power = reader.GetInt32(2)
                    };
                    penStatusList.Add(status);
                }
            }
        }
        return penStatusList;
    }


    public static void DeletePenStatus()
    {
        //DELETE PenStatus;
        string sql = "DELETE QuestionTask;UPdate PenStatus SET Status=0";
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            connection.Execute(sql);
        }
    }

    //获取电量
    public static void UpdatePenCharge(string penNo, int charge)
    {
        if (string.IsNullOrEmpty(penNo))
        {
            return;
        }
        string sql = "SELECT COUNT(1) FROM PenStatus WHERE PenNo='" + penNo + "'";
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "";
            query = @"SELECT COUNT(1) FROM PenStatus WHERE PenNo=@PenNo";
            if (connection.ExecuteScalar<int>(query, new { PenNo = penNo }) > 0)
            {
                query = "UPDATE PenStatus SET charge=@charge WHERE  PenNo=@PenNo";
                connection.Execute(query, new { charge = charge, penNo = penNo });
            }
            else
            {
                query = "INSERT INTO  PenStatus (PenNo,Status,charge) VALUES(@PenNo,@Status,@charge)";
                connection.Execute(query, new { charge = charge, penNo = penNo, Status =1  });
            }
        }
    }

    /// <summary>
    /// 更改笔状态
    /// </summary>
    public async static void UpdatePenStatus(string penNo,int Status)
    {
        if (string.IsNullOrEmpty(penNo))
        {
            return;
        }
        string sql = "SELECT COUNT(1) FROM PenStatus WHERE PenNo='" + penNo + "'";
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "";
            query = @"SELECT COUNT(1) FROM PenStatus WHERE PenNo=@PenNo";
            if (connection.ExecuteScalar<int>(query, new { PenNo = penNo }) > 0)
            {
                query = "UPDATE PenStatus SET Status=@Status WHERE  PenNo=@PenNo";
                connection.Execute(query, new { Status = Status, penNo = penNo });
            }
            else
            {
                

                query = "INSERT INTO  PenStatus (PenNo,Status) VALUES(@PenNo,@Status)";
                connection.Execute(query, new { Status = Status, penNo = penNo });
            }
        }
    }

    /// <summary>
    /// 新增笔的表
    /// </summary>
    /// <param name="tableName"></param>
    public async static void AddSqlTable(string tableName,long ClassID)
    {
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            if (!CheckTableExists(tableName, connectionString))
            {
                CreateEmployeeTable(tableName, connectionString);


                using (HttpClient client = new HttpClient())
                {
                    List<ClassPenList> lt = new List<ClassPenList>();

                    ClassPenList cpl = new ClassPenList();
                    cpl.ClassId = ClassID;
                    Pens p = new Pens();
                    p.serialNo = tableName;
                    p.power = 0;
                    cpl.Pens = new List<Pens>();
                    cpl.Pens.Add(p);
                    lt.Add(cpl);
                    var jsonContent = System.Text.Json.JsonSerializer.Serialize(lt);
                    try
                    {
                        var content = new StringContent(jsonContent, Encoding.UTF8, "application/json");
                        HttpResponseMessage response = await client.PostAsync(WindowsFormsApp.InteractiveClassroomLocalDataScan.apiUrl + "/sdk/v1/dotmatrix/pen/add", content);
                        response.EnsureSuccessStatusCode();
                        ServerResult sr = JsonConvert.DeserializeObject<ServerResult>(await response.Content.ReadAsStringAsync());
                        if (sr.success)
                        {
                            UpdatePenStatus(tableName);
                        }
                    }
                    catch 
                    { 
                    
                    }
                  
                }

            }
        }
    }

    static void UpdatePenStatus(string penNo)
    {
        string SQL = "UPDATE PenStatus SET isToServer=1 WHERE PenNo='"+ penNo + "'";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(SQL, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        static bool CheckTableExists(string tableName, string connectionString)
        {
            string checkSql = @"
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME = @TableName";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(checkSql, connection))
                {
                    command.Parameters.AddWithValue("@TableName", tableName);
                    int count = (int)command.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        /// <summary>
        /// 创建笔的表
        /// </summary>
        static void CreateEmployeeTable(string tableName, string connectionString)
        {
        //public string penSerial { get; set; }
        //public string pageSerial { get; set; }
        //public ulong penType { get; set; }
        //public int cx { get; set; }
        //public int cy { get; set; }
        //public int force { get; set; }
        //public int flag { get; set; }
        //public long time { get; set; }

        string createSql = string.Format(@"
                CREATE TABLE {0} (
                    ID BIGINT PRIMARY KEY IDENTITY(1,1),
                    penSerial NVARCHAR(50) NOT NULL,
                    pageSerial NVARCHAR(50) NOT NULL,
                    penType INT NOT NULL,
                    cx INT NOT NULL,
                    cy INT NOT NULL,
                    force INT NOT NULL,
                    flag INT NOT NULL,
                    time BIGINT NOT NULL,
                    CreatedDate DATETIME DEFAULT GETDATE()
                );

                CREATE TABLE {1} (
                    ID BIGINT PRIMARY KEY IDENTITY(1,1),
                    penSerial NVARCHAR(50) NOT NULL,
                    pageSerial NVARCHAR(50) NOT NULL,
                    penType INT NOT NULL,
                    cx INT NOT NULL,
                    cy INT NOT NULL,
                    force INT NOT NULL,
                    flag INT NOT NULL,
                    time BIGINT NOT NULL,
                    CreatedDate DATETIME DEFAULT GETDATE()
                );


", "\""+tableName + "\"", "\"" + tableName + "_bak\"");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(createSql, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
    }