C# Data.StoredProcedure类代码示例

        public virtual int Add(NewsItem item)
        {
            int newID = 0;
            using (StoredProcedure sp = new StoredProcedure("News_AddItem"))
            {
                sp.Params.Add("@GroupID", System.Data.SqlDbType.Int).Value = item.GroupID;
                sp.Params.Add("@LangCode", System.Data.SqlDbType.NChar).Value = item.LangCode;
                sp.Params.Add("@Title", System.Data.SqlDbType.NVarChar,255).Value = item.Title;
                sp.Params.Add("@ImageFile", System.Data.SqlDbType.VarBinary).Value = item.ImageFile;
                sp.Params.Add("@Notice", System.Data.SqlDbType.NVarChar, 1024).Value = item.Notice;
                sp.Params.Add("@Text", System.Data.SqlDbType.NVarChar).Value = item.Text;

                sp.Params.Add("@LinkUrl", System.Data.SqlDbType.NVarChar, 255).Value = Tools.FixUrl(item.LinkUrl);
                sp.Params.Add("@LinkText", System.Data.SqlDbType.NVarChar, 255).Value = item.LinkText;
                sp.Params.Add("@Link2Url", System.Data.SqlDbType.NVarChar, 255).Value = Tools.FixUrl(item.Link2Url);
                sp.Params.Add("@Link2Text", System.Data.SqlDbType.NVarChar, 255).Value = item.Link2Text;
                sp.Params.Add("@ImagePosition", System.Data.SqlDbType.Int).Value = item.ImagePosition;

                sp.Params.Add("@IsActive", System.Data.SqlDbType.Bit).Value = item.IsActive;
                sp.Params.Add("@IsMain", System.Data.SqlDbType.Bit).Value = item.IsMain;
                sp.Params.Add("@Date", System.Data.SqlDbType.DateTime).Value = item.Date;

                sp.Params.Add("@OrderNumber", System.Data.SqlDbType.Int).Value = item.OrderNumber;

                newID = Convert.ToInt32(sp.ExecuteScalar());
            }
            return newID;
        }

		public Service GetByLangGroup(string language, int groupId)
		{
			Service item = null;
			using (StoredProcedure sp = new StoredProcedure("Services_GetByLangGroup"))
			{
				sp.Params.Add("@GroupID", System.Data.SqlDbType.Int).Value = groupId;
				sp.Params.Add("@LangCode", System.Data.SqlDbType.NChar).Value = language;
				using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
				{
					if (r != null && r.Read())
					{
						item = new Service();
						item.ID = Convert.ToInt32(r["ID"]);
						item.LangCode = Convert.ToString(r["LangCode"]);
						item.Name = Convert.ToString(r["Name"]);
						item.Telephone = Convert.ToString(r["Telephone"]);
						item.ServiceStatusID = Convert.ToInt32(r["ServiceStatusID"]);
						item.GroupID = Convert.ToInt32(r["GroupID"]);
						item.CityID = Convert.ToInt32(r["CityID"]);
						item.RegionID = Convert.ToInt32(r["RegionID"]);
						item.Address = Convert.ToString(r["Address"]);
						item.Url = Convert.ToString(r["Url"]);
                        item.Publish = Convert.ToBoolean(r["Publish"]);
					}
				}
			}
			return item;
		}

        public List<DownloadUserForm> GetByFileID(int id)
        {
            List<DownloadUserForm> res = new List<DownloadUserForm>();
            using (StoredProcedure sp = new StoredProcedure(string.Format("SELECT * FROM DownloadUserForms WHERE FileID = @FileID")))
            {
                sp.command.CommandType = CommandType.Text;
                sp.Params.Add("FileID", SqlDbType.NVarChar).Value = id;
                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {

                        while (r.Read())
                        {
                            DownloadUserForm d = new DownloadUserForm();
                            d.Url = r.GetString(r.GetOrdinal("Url"));
                            d.FileID = r.GetInt32(r.GetOrdinal("FileID"));
                            d.ID = r.GetInt32(r.GetOrdinal("ID"));
                            d.Email = r.GetString(r.GetOrdinal("Email"));
                            d.FirstName = r.GetString(r.GetOrdinal("FirstName"));
                            d.LastName = r.GetString(r.GetOrdinal("LastName"));
                            res.Add(d);
                        }

                    }
                }
            }
            return res;
        }

 public List<Product> GetAllByLanguage(string language)
 {
     List<Product> all = new List<Product>();
     using (StoredProcedure sp = new StoredProcedure("Products_GetAllByLanguage"))
     {
         sp.Params.Add("@LangCode", SqlDbType.NChar).Value = language;
         using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
         {
             if (r != null)
             {
                 while (r.Read())
                 {
                     Product item = new Product();
                     item.ID = Convert.ToInt32(r["ID"]);
                     item.LangCode = Convert.ToString(r["LangCode"]);
                     item.Name = Convert.ToString(r["Name"]);
                     item.GroupID = Convert.ToInt32(r["GroupID"]);
                     item.OrderNumber = Convert.ToInt32(r["OrderNumber"]);
                     item.CategoryID = Convert.ToInt32(r["CategoryID"]);
                     item.Url = Convert.ToString(r["Url"]);
                     item.ImageLenght = Convert.ToInt32(r["ImageLenght"]);
                     all.Add(item);
                 }
             }
         }
     }
     return all;
 }

        public virtual List<HomePageBlock> GetAll(string langCode)
        {
            List<HomePageBlock> all = new List<HomePageBlock>();
            using (StoredProcedure sp = new StoredProcedure("HomePageBlocks_GetAll"))
            {
                sp.Params.Add("@LangCode", System.Data.SqlDbType.NChar).Value = langCode;
                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            HomePageBlock item = new HomePageBlock();

                            item.ID = Convert.ToInt32(r["ID"]);
                            item.GroupID = Convert.ToInt32(r["GroupID"]);
                            item.LangCode = Convert.ToString(r["LangCode"]);
                            item.LinkUrl = Convert.ToString(r["LinkUrl"]);
                            item.LinkText = Convert.ToString(r["LinkText"]);
                            item.OrderNumber = Convert.ToInt32(r["OrderNumber"]);
                            item.IsActive = Convert.ToBoolean(r["IsActive"]);
                            item.DateCreated = Convert.ToDateTime(r["DateCreated"]);

                            all.Add(item);
                        }
                    }
                }
            }
            return all;
        }

        public virtual List<SuccessStory> GetAll(string language)
        {
            List<SuccessStory> all = new List<SuccessStory>();
            using (StoredProcedure sp = new StoredProcedure("SuccessStories_GetAll"))
            {
                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            SuccessStory item = new SuccessStory();

                            item.ID = Convert.ToInt32(r["ID"]);
                            item.GroupID = Convert.ToInt32(r["GroupID"]);
                            item.LangCode = Convert.ToString(r["LangCode"]);
                            item.Title = Convert.ToString(r["Title"]);
                            item.Text = Convert.ToString(r["Text"]);
                            item.Date = NullableConverter.ToDateTime(r["Date"]);
                            item.PdfFileName = Convert.ToString(r["PdfFileName"]);

                            all.Add(item);
                        }
                    }
                }
            }
            return all;
        }

        public virtual List<SuccessStory> GetByLang(string langCode)
        {
            List<SuccessStory> all = new List<SuccessStory>();
            using (StoredProcedure sp = new StoredProcedure("SuccessStory_GetByLang"))
            {
                sp.Params.Add("@LangCode", System.Data.SqlDbType.NChar).Value = langCode;

                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            SuccessStory item = new SuccessStory();

                            item.ID = Convert.ToInt32(r["ID"]);
                            item.GroupID = Convert.ToInt32(r["GroupID"]);
                            item.LangCode = Convert.ToString(r["LangCode"]);
                            item.Title = Convert.ToString(r["Title"]);
                            item.Text = Convert.ToString(r["Text"]);
                            item.Date = NullableConverter.ToDateTime(r["Date"]);
                            item.HasImage = Convert.ToBoolean(r["HasImage"]);
                            item.HasPdfFile = Convert.ToBoolean(r["HasPdfFile"]);
                            item.PdfFileName = Convert.ToString(r["PdfFileName"]);
                            all.Add(item);
                        }
                    }
                }
            }
            return all;
        }

        public List<SerialNumber> GetAll()
        {
            List<SerialNumber> all = new List<SerialNumber>();
            using (StoredProcedure sp = new StoredProcedure("SerialNumbers_GetAll"))
            {
                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            SerialNumber item = new SerialNumber();

                            item.ID = Convert.ToInt32(r["ID"]);
                            item.Number = Convert.ToString(r["SerialNumber"]);
                            item.ServiceID = NullableConverter.ToInt32(r["ServiceID"]);
                            item.AdminID = Convert.ToInt32(r["AdminID"]);
                            item.CreatedDate = Convert.ToDateTime(r["CreatedDate"]);
                            item.ModifiedDate = NullableConverter.ToDateTime(r["ModifiedDate"]);

                            all.Add(item);
                        }
                    }
                }
            }
            return all;
        }

        public List<iPadActionSerial> GetAll()
        {
            List<iPadActionSerial> items = new List<iPadActionSerial>();
            using (StoredProcedure sp = new StoredProcedure("iPadActionSerial_GetAll"))
            {
                var r = sp.ExecuteReader();

                if (r != null)
                {
                    while (r.Read())
                    {
                        iPadActionSerial item = new iPadActionSerial {
                                BuyDate = Convert.ToDateTime(r["BuyDate"]),
                                Email = Convert.ToString(r["Email"]),
                                Seller = Convert.ToInt32(r["Seller"]),
                                ID = Convert.ToInt32(r["ID"]),
                                Serial = Convert.ToString(r["Serial"]),
                                SellerName = Convert.ToString(r["SellerName"]),
                                Status = Convert.ToInt32(r["Status"])
                        };

                        items.Add(item);
                    }
                }
            }

            return items;
        }

 //
 public List<City> GetAllByLanguage(string language)
 {
     List<City> all = new List<City>();
     using (StoredProcedure sp = new StoredProcedure("Cities_GetAllByLanguage"))
     {
         sp.Params.Add("@LangCode", SqlDbType.NChar).Value = language;
         using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
         {
             if (r != null)
             {
                 while (r.Read())
                 {
                     City item = new City();
                     item.ID = Convert.ToInt32(r["ID"]);
                     item.LangCode = Convert.ToString(r["LangCode"]);
                     item.Name = Convert.ToString(r["Name"]);
                     item.RegionID = Convert.ToInt32(r["RegionID"]);
                     item.GroupID = Convert.ToInt32(r["GroupID"]);
                     all.Add(item);
                 }
             }
         }
     }
     return all;
 }

		public List<Service> GetAllByLanguage(string language)
		{
			List<Service> all = new List<Service>();
			using (StoredProcedure sp = new StoredProcedure("Services_GetAllByLanguage"))
			{
				sp.Params.Add("@LangCode", SqlDbType.NChar).Value = language;
				using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
				{
					if (r != null)
					{
						while (r.Read())
						{
							Service item = new Service();
							item.ID = Convert.ToInt32(r["ID"]);
							item.LangCode = Convert.ToString(r["LangCode"]);
							item.Name = Convert.ToString(r["Name"]);
							item.Telephone = Convert.ToString(r["Telephone"]);
							item.ServiceStatusID = Convert.ToInt32(r["ServiceStatusID"]);
							item.GroupID = Convert.ToInt32(r["GroupID"]);
							item.CityID = Convert.ToInt32(r["CityID"]);
							item.Url = Convert.ToString(r["Url"]);
                            item.Publish = Convert.ToBoolean(r["Publish"]);
							all.Add(item);
						}
					}
				}
			}
			return all;
		}

        public virtual List<TranslatableText> GetAll()
        {
            List<TranslatableText> all = new List<TranslatableText>();
            using (StoredProcedure sp = new StoredProcedure("TranslatableTexts_GetAll"))
            {
                using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            TranslatableText item = new TranslatableText();

                            item.ID = Convert.ToString(r["ID"]);
                            item.GroupID = Convert.ToInt32(r["GroupID"]);
                            item.LangCode = Convert.ToString(r["LangCode"]);
                            item.Text = Convert.ToString(r["Value"]);

                            all.Add(item);
                        }
                    }
                }
            }
            return all;
        }

 public List<Specialization> GetAllByLanguage(string language)
 {
     List<Specialization> all = new List<Specialization>();
     using (StoredProcedure sp = new StoredProcedure("Specializations_GetAllByLanguage"))
     {
         sp.Params.Add("@LangCode", SqlDbType.NChar).Value = language;
         using (SqlDataReader r = (SqlDataReader)sp.ExecuteReader())
         {
             if (r != null)
             {
                 while (r.Read())
                 {
                     Specialization item = new Specialization();
                     item.ID = Convert.ToInt32(r["ID"]);
                     item.LangCode = Convert.ToString(r["LangCode"]);
                     item.Name = Convert.ToString(r["Name"]);
                     item.Description = Convert.ToString(r["Description"]);
                     item.GroupID = Convert.ToInt32(r["GroupID"]);
                     all.Add(item);
                 }
             }
         }
     }
     return all;
 }

 public static bool Clear()
 {
     bool result = false;
     using (StoredProcedure sp = new StoredProcedure("Log_Clear"))
     {
         result = sp.ExecuteNonQuery() > 0;
     }
     return result;
 }

 public bool DeleteImageByGroupID(int groupID)
 {
     bool result = false;
     using (StoredProcedure sp = new StoredProcedure("Products_DeleteImage"))
     {
         sp.Params.Add("@GroupID", System.Data.SqlDbType.Int).Value = groupID;
         result = sp.ExecuteNonQuery() > 0;
     }
     return result;
 }