public List <LinkViewModel> Links() { List <LinkViewModel> list = new List <LinkViewModel>(); if (_ModuleID != Guid.Empty) { using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { using (SqlCommand cmd = new SqlCommand("SELECT d.*, dc.DisplayName AS CategoryName, dc.LinkCategorySerial FROM cms_Link d JOIN cms_LinkCategory dc ON d.LinkCategorySerial = dc.LinkCategorySerial WHERE d.ModuleID = @ModuleID AND Locale = @Locale ORDER BY dc.SortOrder, dc.DisplayName, d.LinkLabel", cn)) { cmd.Parameters.Add("ModuleID", SqlDbType.UniqueIdentifier).Value = _ModuleID; cmd.Parameters.Add("Locale", SqlDbType.VarChar, 10).Value = _Locale; cmd.CommandType = CommandType.Text; cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { LinkViewModel linkViewModel = new LinkViewModel(); linkViewModel.LinkSerial = Convert.ToInt32(dr["LinkSerial"]); linkViewModel.LinkCategorySerial = Convert.ToInt32(dr["LinkCategorySerial"]); linkViewModel.Locale = dr["Locale"].ToString(); linkViewModel.LinkLabel = dr["LinkLabel"].ToString(); linkViewModel.LinkURL = dr["LinkURL"].ToString(); linkViewModel.LinkDescription = dr["LinkDescription"].ToString(); linkViewModel.CategoryName = dr["CategoryName"].ToString(); list.Add(linkViewModel); } cmd.Connection.Close(); } } } return(list); }
public void Add(LinkViewModel linkViewModel) { using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO [cms_Link] ([ModuleID], [Locale], [LinkLabel], [LinkDescription], [LinkURL], [LinkCategorySerial]) VALUES (@ModuleID, @Locale, @LinkLabel, @LinkDescription, @LinkURL, @LinkCategorySerial)", cn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@ModuleID", SqlDbType.UniqueIdentifier).Value = _ModuleID; cmd.Parameters.Add("@Locale", SqlDbType.VarChar, 10).Value = _Locale; cmd.Parameters.Add("@LinkLabel", SqlDbType.VarChar, 250).Value = linkViewModel.LinkLabel; cmd.Parameters.Add("@LinkDescription", SqlDbType.VarChar).Value = linkViewModel.LinkDescription; cmd.Parameters.Add("@LinkURL", SqlDbType.VarChar, 250).Value = linkViewModel.LinkURL; cmd.Parameters.Add("@LinkCategorySerial", SqlDbType.Int).Value = linkViewModel.LinkCategorySerial; cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } }