.Net Core DataReader CRUD Using Procedure

.Net Core DataReader CRUD Using Procedure
使用DataReader 进行CRUD操作
while (reader.Read())中返回值需根据自己的procedure返回值调整

Retrive All

public IEnumerable<Models.Event> ListEvents()
        {
            var events = new List<Models.Event>();

            using (var connection = new SqlConnection(connectionString))
            {
            	//这里的ListEvents是Procedure名字，请自行更改，下同
                using (var command = new SqlCommand("ListEvents", connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            events.Add(new Models.Event { Id = (string)reader["Id"], EventName = (string)reader["EventName"], UserId = (int)reader["CreatorId"] });
                        }
                    }
                }
            }
            return events;
        }

Retrive With Condition

public IEnumerable<Models.Event> ListEventsById(int id)
        {
            var events = new List<Models.Event>();
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("ShowEventByUser", connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    connection.Open();
                    command.Parameters.Add(new SqlParameter("@id", id));
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            events.Add(new Models.Event { Id = (string)reader["Id"], EventName = (string)reader["EventName"] });
                        }
                    }
                }
            }
            return events;
        }

Delete

public int DeleteEvent(string id)
        {
            var events = new List<Models.Event>();
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("DeleteEvent", connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    connection.Open();
                    command.Parameters.Add(new SqlParameter("@id", id));
                    using (var reader = command.ExecuteReader())
                    {
                        int count = 0;
                        while (reader.Read()) 
                        {
                        //此处返回的是受影响的行数，update和Add理论上都应该添加（在这篇例子中没有，请自行修改）
                            count= (int)reader["rowNum"];
                        }
                        if (count == 0)
                        {
                            return -1;
                        }
                        else
                        {
                            return 1;
                        }
                    }
                }
            }
        }

Update

public Models.Event UpdateEvent(string id, string eventName)
        {
            var events = new List<Models.Event>();
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("UpdateEvent", connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    connection.Open();
                    command.Parameters.Add(new SqlParameter("@eventName", eventName));
                    command.Parameters.Add(new SqlParameter("@id", id));
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader == null)
                        {
                            return null;
                        }
                        else
                        {
                            return new Models.Event { Id = id, EventName = eventName };
                        }
                    }
                }
            }
        }

Create

public Models.Event AddEvent(int creatorId, string eventName)
        {
            var events = new List<Models.Event>();
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("AddEvents", connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    connection.Open();
                    string id = System.Guid.NewGuid().ToString("N");
                    command.Parameters.Add(new SqlParameter("@creatorId", creatorId));
                    command.Parameters.Add(new SqlParameter("@eventName", eventName));
                    command.Parameters.Add(new SqlParameter("@id", id));
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader == null)
                        {
                            return null;
                        }
                        else
                        {
                            return new Models.Event {Id = id,EventName = eventName };
                        }
                    }
                }
            }
        }