iT邦幫忙

0

想請問LINQ有這種查詢語法嗎

想請問LINQ可以查詢DB欄位資料型態的上限值嗎?

就是假如我DB裡有個欄位叫Input,型態為varchar(1000)

我有辦法用LINQ或是其他方法來查詢這個欄位的型態值嗎?

因為最近蠻常遇到這個溢位的問題

我想說最基本的就前端判斷外

後端能不能也做個處理

字串長度?
tenno081 iT邦新手 2 級 ‧ 2021-05-12 10:17:15 檢舉
對,應該說查字串長度有沒有超過DB欄位設定長度

2 個回答

0
japhenchen
iT邦大師 1 級 ‧ 2021-05-12 12:26:02
最佳解答

MSSQL 的方法

public int GetColumnMaxLength(string tableName, string columnName)
{
    int result = -1;
    using(var conn = new SqlConnection("ConnectionString"))
    {
        conn.Open();
        var cmd = new SqlCommand("SELECT * FROM "+tableName+" WHERE 1 = 0", conn);
        var reader = cmd.ExecuteReader();
        var schema = reader.GetSchemaTable();
        result = schema.AsEnumerable()
                         .Single(s => s.Field<string>("ColumnName") == "Column")
                         .Field<int>("ColumnSize");
        conn.Close();
    }
    return result;
}

加個database上去比較好

public int GetColumnMaxLength(string dbname ,string tableName, string columnName)
{
    int result = -1;
    using(var conn = new SqlConnection("ConnectionString"))
    {
        conn.Open();
        var cmd = new SqlCommand("SELECT * FROM "+dbname+".dbo."tableName+" WHERE 1 = 0", conn);
        var reader = cmd.ExecuteReader();
        var schema = reader.GetSchemaTable();
        result = schema.AsEnumerable()
                         .Single(s => s.Field<string>("ColumnName") == "Column")
                         .Field<int>("ColumnSize");
        conn.Close();
    }
    return result;
}
0
a9864277
iT邦新手 5 級 ‧ 2021-05-12 10:31:05

你是EF Core的話,你可以在Entity設定欄位狀態

https://docs.microsoft.com/zh-tw/ef/core/modeling/entity-properties?tabs=data-annotations%2Cwithout-nrt

或是自己寫簡單的驗證

public class Program
    {  
        static async Task Main(string[] args)
        {
            List<Student> student = new List<Student>
            {
                new Student { Name = "一" },
                new Student { Name = "一二" },
                new Student { Name = "一二三" },
                new Student { Name = "一二三四" },
                new Student { Name = "一二三四五" }
            };

            Console.WriteLine(string.Join(",", student.Select(x=>x.Name)));

            Console.ReadKey();
        }
        public class Student
        {
            private string _name;

            public string Name
            {
                get
                {
                    if (_name.Length <= 3)
                        return _name;
                    return "溢位";
                }
                set { _name = value; }
            }

        }

    }

https://ithelp.ithome.com.tw/upload/images/20210512/20135969fEK2Hf5uHM.jpg

tenno081 iT邦新手 2 級 ‧ 2021-05-12 10:37:17 檢舉

我還是MVC XD
在entity裡設定MVC也可以

[StringLength(5, ErrorMessage = "輸入超過5個字")]
public string CONTENT { get; set; }

但看來目前好像這樣比較簡單些@@

我要發表回答

立即登入回答