Moss/Sharepoint:自定义 Oracle Membership

　　本文记录编写一个简单的基于oracle的membership for sharepoint示例。当项目客户的员工信息在其他的数据库，或者有员工信息遗留表的情况下可以考虑用自定义membership的方法。当然，如果微软默认的membership能够帮到你就在好不过了，稳定，而且不用二次开发。但是很多时候，我们会选择自定义，特别是在还有其他的系统使用原有遗留表的等等情况下，自定义显然是一个很好的方法。

　　这里我用一个基于Oracle的自定义Membership作为一个例子。

第一步：创建数据库

　　模拟创建一个遗留用户表，为了简单起见，我这里创建表的信息会相当简单，毕竟只是模拟。

CREATE TABLE users
(
  Id_P int, //唯一标识用户，因为oracle没有IDENTITY，所以我们后面创建一个sequence来解决这个问题。
  userName varchar(255), //用户名
  password varchar(255), //密码
  apartmentID int        //部门引用，这个对于membership是无用信息，在你的遗留表里面可定有很多字段就 像它一样被membership抛弃。
)

 

　　创建一个SEQUENCE实现Id_P  的唯一标识

create sequence SEQ_D
  minvalue 1
  maxvalue 99999999
  start with 21
  increment by 1
  cache 20;

 

　　接着插入两条数据条数据

insert into users(Id_P,userName,password,apartmentID)values(SEQ_D.Nextval,'sousou','sousou',1);
insert into users(Id_P,userName,password,apartmentID)values(SEQ_D.Nextval,'gogo','gogo',1);

 

　　好了，第一步已经完成了。

第二步：编写代码

　　我们这里使用的方法 极限编程（XP）的打通（Spike）概念建立一个基本的例子，先spike通就可以了，至于具体逻辑是千变万化的。这个方法也是前几天公司培训的时候学习到的开发方法.

public class pitMembership : MembershipProvider
    {
        public pitMembership()
        {
        }
        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if(string.IsNullOrEmpty(name))
                name = "TestMemberShipProvider";
            base.Initialize(name, config);
        }
        //Your SQL Server connection
       // private  string conStr = ConfigurationManager.ConnectionStrings["CustomOracleConnection"].ToString();
        private string conStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=XE)));User ID=system;Password=admin;";
        // I would like call the appilcation name...
        private string _AppName = "TestMemberShipProvider";

        // Dummy ValidateUserStart ******************************
        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotImplementedException();
        }

        public override int GetNumberOfUsersOnline()
        {
            throw new NotImplementedException();
        }

        public override string GetUserNameByEmail(string email)
        {
            throw new NotImplementedException();
        }

        public override string ResetPassword(string username, string answer)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override bool UnlockUser(string userName)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override void UpdateUser(MembershipUser user)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            throw new Exception("The method or operation not implemented.");
        }
        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            throw new Exception("The method or operation not implemented.");
        }
        public override string GetPassword(string username, string answer)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override int MinRequiredPasswordLength
        {
            get { return 1; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return 1; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return ""; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return MembershipPasswordFormat.Clear; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return false; }
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            throw new Exception("The method or operation not implemented.");
        }

        public override int PasswordAttemptWindow { get { return 1; } }
        public override int MaxInvalidPasswordAttempts { get { return 5; } }

        public override bool RequiresQuestionAndAnswer { get { return false; } }
        public override bool EnablePasswordReset { get { return true; } }
        public override bool EnablePasswordRetrieval { get { return true; } }

        public override string ApplicationName
        {
            get { return _AppName; }
            set { if (_AppName != value) { _AppName = value; } }
        }

        // Dummy End ******************************


        public override bool ValidateUser(string username, string password)
        {
            OracleConnection con = new OracleConnection(conStr);
            OracleCommand cmd = new OracleCommand("SELECT COUNT(1) FROM users WHERE username=:username AND password=:password");
            cmd.Parameters.Add(new OracleParameter(":username", username));
            cmd.Parameters.Add(new OracleParameter(":password", password));
            cmd.Connection = con;
            con.Open();
            int count = Convert.ToInt16(cmd.ExecuteScalar());
            con.Close();

            return count != 0;
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            MembershipUserCollection muc = new MembershipUserCollection();

            try
            {
                OracleDataAdapter da = new OracleDataAdapter("SELECT id_p,username, password FROM users", new OracleConnection(conStr));
                DataSet ds = new DataSet();
                da.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    muc.Add(new MembershipUser(
                                this.Name,
                                dr["username"].ToString(),
                                dr["id_p"],
                                "ninethsense@hotmail.com",
                                "",
                                "",
                                true,
                                false,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.MinValue
                                ));
                }
                totalRecords = ds.Tables[0].Rows.Count;

            }
            catch (Exception e)
            {
                throw new Exception("Exception in GetAllUsers():" + e.Message);
            }
            return muc;
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            MembershipUserCollection muc = new MembershipUserCollection();

            try
            {
                // Beware of code injection :-)
                OracleDataAdapter da = new OracleDataAdapter("SELECT  id_p,username, password FROM users WHERE username='" + usernameToMatch + "'", new OracleConnection(conStr));
                DataSet ds = new DataSet();
                da.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    muc.Add(new MembershipUser(
                               this.Name,
                                dr["username"].ToString(),
                                dr["id_p"],
                                "ninethsense@hotmail.com",
                                "",
                                "",
                                true,
                                false,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.Now,
                                DateTime.MinValue
                                ));
                }
                totalRecords = ds.Tables[0].Rows.Count;

            }
            catch (Exception e)
            {
                throw new Exception("Exception in GetAllUsers():" + e.Message);
            }
            return muc;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            MembershipUser mu = null;
            try
            {
                // Beware of code injection :-)
                OracleDataAdapter da = new OracleDataAdapter("SELECT id_p,username, password FROM users WHERE id=" + providerUserKey.ToString(), new OracleConnection(conStr));
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    mu = new MembershipUser(
                       this.Name,
                        dr["username"].ToString(),
                        dr["id_p"],
                        "ninethsense@hotmail.com",
                        "",
                        "",
                        true,
                        false,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.MinValue
                        );
                }
            }
            catch (Exception e)
            {
                throw new Exception("Exception in GetUser()1:" + e.Message);
            }
            return mu;
        }
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            MembershipUser mu = null;
            try
            {
                // Beware of code injection :-)
                OracleDataAdapter da = new OracleDataAdapter("SELECT id_p,username, password FROM users WHERE username='" + username + "'", new OracleConnection(conStr));
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    mu = new MembershipUser(
                        this.Name,
                        dr["username"].ToString(),
                        dr["id_p"],
                        "ninethsense@hotmail.com",
                        "",
                        "",
                        true,
                        false,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.Now,
                        DateTime.MinValue
                        );
                }
            }
            catch (Exception e)
            {
                throw new Exception("Exception in GetUser()2:" + e.Message);
            }
            return mu;
        }
    }

 　　关于.net访问oracle数据库你可以查看  .net 访问oracle的总结

　　重写上面几个方法就可以做到了。关于要重写哪几个方法你可以参见Forms Authentication in SharePoint 微软建议的方法有：

　　这里我们我为了更加了简洁，去掉了与email相关的方法的重写。

　　可能很多人会认为membership provider与role provider是紧耦合的，因为在查看数据库表的时候，你也会迷惑这个问题。我现在做的是让我的membership在oracle里面，而role在sql server里面，这样做经测试是没有问题的，具体的分析将会在近期的文章中写出来。

 

第三步：Build以及测试

　　这里不讲怎么做unit测试了，我这里讲一下怎么测试整体效果，新建一个网站，配置好role和membership之后，点击你新建的网站解决方案，在点击ASP.NET 配置，进入网站管理工具。在这里你可以测试你的代码是否可以得到你想要的结果。还可以附加到进程调试查看他怎么运行的。不过这阶段你有可能会遇到相关的问题，无论是unit测试还是网站管理工具测试，请阅读   使用VS开发基于Oracle程序的严重问题  一文。

 

第四部：附加到GAC

一.开始 -> Programss-> Microsoft Visual Studio 2008 -> Visual Studio 2008 Tools -> Visual Studio 2008 command prompt.

二.输入 gacutil /i  位置+你的组件.dll.

 

第五步：配置membership等

　　这一步请见  Moss/Sharepoint 2010 Form认证(FBA)  一文。

 

　　如果你有任何问题都可以留言。