C# Utility.SqlDataAccess类代码示例

        public SendSms()
        {
            InitializeComponent();

            try
            {
                LOG_PATH = Globals.FileLogPath;
                ERROR_LOG_PATH = Globals.FileLogPath;

                _tokenSource = new CancellationTokenSource();
                _token = _tokenSource.Token;

                _timer = new System.Timers.Timer();
                _timer.Interval = Convert.ToDouble(ConfigurationManager.AppSettings["serviceInterval"]);
                _timer.AutoReset = true;
                _timer.Enabled = true;

                _timer.Elapsed += timer_Elapsed;

                _sda = new SqlDataAccess();
                _sda.openConnection(Globals.ConnectionString);

                _service = MSCRM.GetOrgService(true);

                _serviceProcess = new ServiceProcess(_sda, _service);
            }
            catch (Exception ex)
            {
                FileLogHelper.LogFunction(this.GetType().Name, "SendSms_SendSms_EXCEPTION:" + ex.Message, ERROR_LOG_PATH);
            }
        }

        public static MsCrmResult CheckOldPasswordCorrect(Guid portalUserId, string oldPassword, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string sqlQuery = @"SELECT
                                        COUNT(0) AS RecCount
                                    FROM
                                            new_user AS u (NOLOCK)
                                    WHERE
                                        u.new_userId='{0}'
                                    AND
                                        u.new_password='{1}'";
                #endregion

                int recCount = (int)sda.ExecuteScalar(string.Format(sqlQuery, portalUserId, oldPassword.Trim()));

                if (recCount > 0)
                {
                    returnValue.Success = true;
                    returnValue.Result = "Eski şifre ile bilgiler eşleşti.";
                }
                else
                {
                    returnValue.Result = "Eski şifre bilginiz yanlıştır.<br />Lütfen kontrol ediniz.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static Annotation GetAnnotationDetail(Guid annotationId, SqlDataAccess sda)
        {
            Annotation returnValue = new Annotation();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    A.AnnotationId
                                    ,A.MimeType
                                    ,A.FileName
                                    ,A.DocumentBody
                                FROM
                                    Annotation A
                                WHERE
                                    A.AnnotationId = '{0}'";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, annotationId));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.AnnotationId = (Guid)dt.Rows[0]["AnnotationId"];
                    returnValue.MimeType = dt.Rows[0]["MimeType"] != DBNull.Value ? dt.Rows[0]["MimeType"].ToString() : string.Empty;
                    returnValue.FileName = dt.Rows[0]["FileName"] != DBNull.Value ? dt.Rows[0]["FileName"].ToString() : string.Empty;
                    returnValue.File = dt.Rows[0]["DocumentBody"] != DBNull.Value ? dt.Rows[0]["DocumentBody"].ToString() : string.Empty;
                }
            }
            catch (Exception)
            {

            }
            return returnValue;
        }

        public static MsCrmResult CheckPhoneNumberMatch(string userName, string phoneNumber, string portalId, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        u.new_userId AS PortalUserId
                                        ,c.ContactId
                                        ,c.MobilePhone
                                    FROM
                                    new_user AS u (NOLOCK)
                                        JOIN
                                            new_new_user_new_role AS ur (NOLOCK)
                                                ON
                                                u.new_userId=ur.new_userid
                                        JOIN
                                            new_role AS r (NOLOCK)
                                                ON
                                                r.new_roleId=ur.new_roleid
                                                AND
                                                r.new_portalId='{2}'
                                        JOIN
                                            Contact AS c (NOLOCK)
                                                ON
                                                c.ContactId=u.new_contactId
                                    WHERE
                                    u.new_name='{0}'
                                    AND
                                    u.statecode=0
                                    AND
                                    u.statuscode=1 --Active
                                    AND
                                    c.MobilePhone='{1}'";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery, userName, phoneNumber, portalId));

                if (dt.Rows.Count > 0)
                {
                    returnValue.Success = true;
                    returnValue.Result = dt.Rows[0]["ContactId"].ToString();
                    returnValue.CrmId = (Guid)dt.Rows[0]["PortalUserId"];
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }

        public MsCrmResult GetToken(string portalId, string userName, string password)
        {
            MsCrmResult returnValue = new MsCrmResult();

            try
            {
                if (!string.IsNullOrEmpty(userName) && !string.IsNullOrEmpty(password) && !string.IsNullOrEmpty(portalId))
                {
                    _sda = new SqlDataAccess();
                    _sda.openConnection(Globals.ConnectionString);

                    returnValue = LoginHelper.LoginControl(new Guid(portalId), userName, password, _sda);

                    //returnValue.Success = true;

                    if (returnValue.Success)
                    {
                        Guid systemUserId = returnValue.CrmId;

                        IOrganizationService service = MSCRM.GetOrgService(true);
                        string ipAddress = HttpContext.Current.Request.UserHostAddress;

                        MsCrmResult logResult = LoginHelper.LogLogIn(returnValue.CrmId, new Guid(portalId), DateTime.Now, ipAddress, service);

                        returnValue.Result = Guid.NewGuid().ToString().Replace("-", "");

                        MsCrmResult sessionResult = SetUserSession(returnValue.Result, new Guid(portalId), systemUserId);

                        if (!sessionResult.Success)
                        {
                            return sessionResult;
                        }
                    }
                }
                else
                {
                    returnValue.Result = "Eksik Parametre.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }
            finally
            {
                if (_sda != null)
                {
                    _sda.closeConnection();
                }
            }

            return returnValue;
        }

        public ServiceProcess(SqlDataAccess sda, IOrganizationService service)
        {
            _sda = sda;
            _service = service;

            LOG_PATH = Globals.FileLogPath;
            ERROR_LOG_PATH = Globals.FileLogPath;

            SMTP_USERNAME = ConfigurationManager.AppSettings["username"].ToString();
            SMTP_PASSWORD = ConfigurationManager.AppSettings["password"].ToString();
            FROM_MAILADDRESS = ConfigurationManager.AppSettings["mailaddress"].ToString();
            MAIL_HOST = ConfigurationManager.AppSettings["hostaddress"].ToString();
            PORT = ConfigurationManager.AppSettings["port"].ToString();
        }

        public static MsCrmResultObject GetArticleCategoryInfo(Guid categoryId, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT DISTINCT
                                    E.new_articlecategoryId AS Id
                                    ,E.new_name Name
                                    ,E.new_portalId PortalId
                                    ,E.new_portalIdName PortalIdName
                                    ,E.new_imageurl AS ImageUrl
                                FROM
                                new_articlecategory AS E (NOLOCK)
                                WHERE
                                    E.new_articlecategoryId = '{0}'
                                    AND
                                    E.statuscode=1 --Active";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, categoryId));
                if (dt != null && dt.Rows.Count > 0)
                {
                    EntityReference er = new EntityReference()
                    {
                        Id = (Guid)dt.Rows[0]["Id"],
                        Name = dt.Rows[0]["Name"].ToString(),
                        LogicalName = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png"
                    };

                    returnValue.Success = true;
                    returnValue.ReturnObject = er;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M024";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public MsCrmResult ConfirmForm(string token, int formCode)
        {
            MsCrmResult returnValue = new MsCrmResult();

            LoginSession ls = new LoginSession();

            try
            {
                _sda = new SqlDataAccess();
                _sda.openConnection(Globals.ConnectionString);

                #region | CHECK SESSION |
                MsCrmResultObject sessionResult = GetUserSession(token);

                if (!sessionResult.Success)
                {
                    returnValue.Result = sessionResult.Result;
                    return returnValue;
                }
                else
                {
                    ls = (LoginSession)sessionResult.ReturnObject;
                }

                #endregion

                MsCrmResultObject resultFormInfo = DiscoveryFormHelper.GetDiscoveryFormInfo(formCode, _sda);

                if (resultFormInfo.Success)
                {
                    DiscoveryForm formInfo = (DiscoveryForm)resultFormInfo.ReturnObject;

                    formInfo.Status = new OptionSetValueWrapper() { AttributeValue = (int)DiscoveryFormStatus.LotusConfirmed };
                }
                else
                {
                    returnValue.Result = resultFormInfo.Result;
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }

        public static MsCrmResultObj<List<Town>> GetAllTowns(SqlDataAccess sda)
        {
            MsCrmResultObj<List<Town>> returnValue = new MsCrmResultObj<List<Town>>();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        t.new_townId AS Id
                                        ,t.new_name AS Name
                                        ,t.new_towncode AS Code
                                        ,t.new_cityId AS CityId
                                        ,t.new_cityIdName AS CityIdName
                                        ,'new_city' AS CityIdTypeName
                                    FROM
                                    new_town AS t (NOLOCK)
                                        JOIN
                                            new_city AS c (NOLOCK)
                                                ON
                                                t.new_cityId=c.new_cityId";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery));

                if (dt.Rows.Count > 0)
                {
                    List<Town> townList = dt.ToList<Town>();

                    returnValue.Success = true;
                    returnValue.ReturnObject = townList;
                }
                else
                {
                    returnValue.Result = "Kayıt bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
                returnValue.HasException = true;
            }

            return returnValue;
        }

        public static MsCrmResultObject GetPointCodeInfo(string code, SqlDataAccess sda)
        {
            MsCrmResultObject returnValue = new MsCrmResultObject();

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        pc.new_pointcodeId AS Id
                                        ,pc.new_name AS Name
                                        ,pc.new_portalid AS PortalId
                                        ,pc.new_portalidName AS PortalIdName
                                        ,'new_portal' AS PortalIdTypeName
                                        ,pc.new_group AS GroupCode
                                        ,pc.new_code AS Code
                                        ,pc.new_point AS Point
                                        ,pc.statuscode AS Status
                                    FROM
                                    new_pointcode AS pc (NOLOCK)
                                    WHERE
                                    pc.new_code='{0}'";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery, code));

                if (dt.Rows.Count > 0)
                {
                    List<PointCode> pCode = dt.ToList<PointCode>();

                    returnValue.Success = true;
                    returnValue.ReturnObject = pCode[0];
                }
                else
                {
                    returnValue.Result = "Kod bulunamadı.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }

            return returnValue;
        }

        public static MsCrmResult CheckIsUserYourFriend(Guid portalId, Guid portalUserId, Guid selectedUserId, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    f.new_friendshipId AS Id
                                FROM
                                    new_friendship AS f (NOLOCK)
                                WHERE
                                    f.new_portalId='{0}'
                                AND
                                    f.statecode=0
                                AND
                                (
                                    (f.new_partyoneId='{1}' AND f.new_partytwoId='{2}')
                                OR
                                    (f.new_partyoneId='{2}' AND f.new_partytwoId='{1}')
                                )";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, portalId, portalUserId, selectedUserId));

                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.Success = true;
                    returnValue.CrmId = (Guid)dt.Rows[0]["Id"];
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M039"; //"Kullanıcı arkadaşınız değildir!";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult GetPortalId(string url, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    u.new_portalId AS BrandId
                                FROM
                                    new_portalurl AS u (NOLOCK)
                                        JOIN
                                            new_portal AS b (NOLOCK)
                                                ON
                                                b.new_portalId=u.new_portalId
                                                AND
                                                b.statecode=0
                                                AND
                                                b.statuscode=1 --Active
                                WHERE
                                    u.new_name='{0}'
                                AND
                                    u.statecode=0";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, url));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.CrmId = (Guid)dt.Rows[0]["BrandId"];
                    returnValue.Success = true;
                }
                else
                {
                    returnValue.Result = "M004"; //"Girmiş olduğunuz adres herhangi bir portala ait değil.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Success = false;
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult GetPageContent(Guid portalId, PageNames pageName, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                        pc.new_content AS Content
                                    FROM
                                        new_pagecontent AS pc (NOLOCK)
                                    WHERE
                                        pc.new_portalId='{0}'
                                    AND
                                        pc.new_page={1}
                                    AND
                                        pc.statecode=0";

                #endregion

                DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, ((int)pageName).ToString()));

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Content"] != DBNull.Value)
                    {
                        returnValue.Success = true;
                        returnValue.Result = dt.Rows[0]["Content"].ToString();
                    }
                }
                else
                {
                    returnValue.Result = "M051"; //"Sayfa içeriği hazırlanmamıştır.";
                }
            }
            catch (Exception ex)
            {
                returnValue.Result = ex.Message;
            }
            return returnValue;
        }

        public static MsCrmResult LoginControl(Guid portalId, string userName, string password, SqlDataAccess sda)
        {
            MsCrmResult returnValue = new MsCrmResult();
            try
            {
                #region | SQL QUERY |
                string query = @"SELECT
                                    U.new_userId UserId
                                FROM
                                    new_user U (NoLock)
                                WHERE
                                    U.new_name = '{0}'
                                    AND
                                    U.new_password = '{1}'
                                    AND
                                    U.statecode = 0
                                    AND
                                    U.statuscode = {2}";
                #endregion

                DataTable dt = sda.getDataTable(string.Format(query, userName, password, (int)PortalUserStatus.Active));
                if (dt != null && dt.Rows.Count > 0)
                {
                    returnValue.CrmId = (Guid)dt.Rows[0]["UserId"];

                    MsCrmResultObject roleResult = PortalUserHelper.GetPortalUserRoles(portalId, returnValue.CrmId, sda);
                    returnValue.Success = roleResult.Success;
                    returnValue.Result = roleResult.Result;
                }
                else
                {
                    returnValue.Success = false;
                    returnValue.Result = "M035"; //"Hatalı kullanıcı adı veya şifre!";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returnValue;
        }

        public static int GetUserLoginCount(Guid portalId, Guid portalUserId, DateTime start, DateTime end, SqlDataAccess sda)
        {
            int returnValue = 0;

            try
            {
                #region | SQL QUERY |

                string sqlQuery = @"SELECT
                                    COUNT(0)
                                FROM
                                    new_loginlog AS ll (NOLOCK)
                                WHERE
                                    [email protected]
                                AND
                                    [email protected]
                                AND
                                    ll.new_logindate BETWEEN @start AND @end
                                AND
                                    ll.StateCode=0";

                #endregion

                SqlParameter[] parameters = {
                                            new SqlParameter("@PortalId",portalId)
                                            ,new SqlParameter("@UserId",portalUserId)
                                            ,new SqlParameter("@start",start)
                                            ,new SqlParameter("@end",end)
                                        };

                returnValue = (int)sda.ExecuteScalar(sqlQuery, parameters);

            }
            catch (Exception ex)
            {

            }

            return returnValue;
        }