C# DBConnection.connect方法代码示例

    public String recoverPassword2(String SendtoAddress)
    {
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = String.Format("Your login credentials for the project by DILIGENTIA are as follows, <br /><br /><b>Username:</b> {0}<br /><b>Password:</b> {1}", stringArray[0], stringArray[1]);
        //String body = stringArray[0] + stringArray[1];

        MailMessage msg = new MailMessage();
        System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
        try
        {
            msg.Subject = "Recover Password";
            msg.Body = body;
            msg.From = new MailAddress("[email protected]");
            msg.To.Add(SendtoAddress);
            msg.IsBodyHtml = true;
            client.Host = "smtp.gmail.com";
            System.Net.NetworkCredential basicauthenticationinfo = new System.Net.NetworkCredential("[email protected]", "diligentia");
            client.Port = int.Parse("587");
            client.EnableSsl = true;
            client.UseDefaultCredentials = false;
            client.Credentials = basicauthenticationinfo;
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.Send(msg);
            return "Sucessful Completion";
        }
        catch (Exception ex)
        {
            return (ex.Message);
        }
    }

    public String recoverPassword(String SendtoAddress)
    {
        String fromAddress = "[email protected]";
        String toAddress = SendtoAddress;
        const String password = "diligentia";
        const String subject = "Retrieving Password";
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = stringArray[0] + stringArray[1];

        try
        {
            SmtpClient smtp = new SmtpClient
            {
                Host = "smtp.gmail.com",
                Port = 587,
                EnableSsl = true,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                Credentials = new NetworkCredential(fromAddress, password),
                Timeout = 3000
            };
            MailMessage message = new MailMessage(fromAddress, toAddress, subject, body);
            smtp.Send(message);
        }
        catch (Exception ex)
        {
        }
        return stringArray[0];
    }

 public List<String> getListOfForums()
 {
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = "SELECT f_title FROM [projectdb1].[dbo].[Forum]";
     return myConnection.getResultsList(query, "f_title");
 }

 public DataTable getCV(String id)
 {
     DataTable myTable = new DataTable();
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("SELECT c.f_name AS 'First Name' ,c.m_name AS 'Middle Name', c.l_name AS 'Last Name', c.dob AS 'Date of Birth', c.age AS 'Age', c.gender AS 'Gender', c.maritual_status AS 'Marital Status', c.add_no AS 'Number',c.add_street AS 'Street', c.add_town AS 'Town',c.email AS 'Email', p.phone AS 'Phone', s.school AS 'School'  FROM [Candidate] AS c, PrsPhone AS p, CanSchool AS s WHERE c.u_id=p.u_id AND c.u_id=s.u_id AND c.u_id={0}", id);
     myTable = myConnection.executeReturningQuery(query);
     return myTable;
 }

 public String getForumID(String forumName)
 {
     List<String> myList = new List<string>();
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("SELECT f_id FROM [projectdb1].[dbo].[Forum] WHERE f_title = '{0}'", forumName);
     myList = myConnection.getResultsList(query, "f_id");
     return myList.First();
 }

 public DataTable getInfo(String id)
 {
     DataTable myTable = new DataTable();
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("SELECT c.f_name AS 'First Name' ,c.m_name AS 'Middle Name', c.l_name AS 'Last Name', c.prs_email AS 'Personal Email', c.company AS 'Company',c.cmp_add_no AS 'C.A.No',c.cmp_add_street AS 'C.A.St',c.cmp_add_town AS 'C.A.tw',c.cmp_email AS 'Cm.Email',p.phone AS 'Pers Phone',b.phone AS 'Bus Phone'  FROM Manager AS c, PrsPhone AS p, BusPhone AS b WHERE c.u_id=p.u_id AND c.u_id=b.u_id AND c.u_id={0}", id);
     myTable = myConnection.executeReturningQuery(query);
     return myTable;
 }

 public String createForum(String userID, String forumTitle)
 {
     DateTime time = new DateTime();
     time = DateTime.Now;
     String timeString = time.ToString("yyyy-MM-dd HH:MM:ss");
     String query = String.Format("INSERT INTO Forum( u_id, f_time, f_title) VALUES ( {0}, '{1}', '{2}')", userID, timeString, forumTitle);
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String msg = myConnection.executeNonReturningQuery(query);
     return msg;
 }

 public String addInfo()
 {
     String query1 = String.Format("INSERT INTO Manager (f_name, m_name, l_name, prs_email, company, cmp_add_no, cmp_add_street, cmp_add_town, cmp_email, u_id) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9})",
                                                         fName, mName, lName, personalEmail, company, number, street, town, companyEmail, userID);
     String query2 = String.Format("INSERT INTO BusPhone (u_id, phone) VALUES ({0}, '{1}')", userID, companyPhone);
     String query3 = String.Format("INSERT INTO PrsPhone (u_id, phone) VALUES ({0}, '{1}')", userID, personalPhone);
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String msg = myConnection.executeNonReturningQuery(query1);
     String msg2 = myConnection.executeNonReturningQuery(query2);
     String msg3 = myConnection.executeNonReturningQuery(query3);
     return msg + msg2 + msg3;
 }

    public String createCV()
    {
        String query1 = String.Format("INSERT INTO [Candidate] ([u_id],[f_name],[m_name],[l_name],[dob],[gender],[maritual_status],[add_no],[add_street],[add_town],[email])" +
                        "VALUES({0}, '{1}', '{2}', '{3}', '{4}', {5}, {6}, '{7}', '{8}', '{9}', '{10}')",
                        userID, fName, mName, lName, dob, gender, mStatus, number, street, town, email);

        String query2 = String.Format("INSERT INTO [PrsPhone]([u_id],[phone]) VALUES ({0}, '{1}')", userID, phone);
        String query3 = String.Format("INSERT INTO [CanSchool]([u_id],[school]) VALUES ({0}, '{1}')", userID, school);
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        String msg = myConnection.executeNonReturningQuery(query1);
        String msg2 = myConnection.executeNonReturningQuery(query2);
        String msg3 = myConnection.executeNonReturningQuery(query3);
        return msg + msg2 + msg3;
    }

    // function to check if the username already exists.
    // return 1 if username is available
    public int checkAvailability(String username, String email)
    {
        List<String> myList1 = new List<string>();
        List<String> myList2 = new List<string>();

        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        String query1 = String.Format("SELECT * FROM UserAcc WHERE u_name = '{0}'", username);
        String query2 = String.Format("SELECT email FROM Candidate WHERE email = '{0}'", email);

        myList1 = myConnection.getResultsList(query1, "u_name");
        myList2 = myConnection.getResultsList(query2, "email");

        if ((myList1.Count >= 1) || (myList2.Count >= 1)) { return 0; }
        else { return 1; }
    }

 // functionto check the password for a given username
 public int checkPassword(String username, String password)
 {
     List<String> myList1 = new List<string>();
     List<String> myList2 = new List<string>();
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("SELECT * FROM UserAcc WHERE u_name='{0}'", username);
     myList1 = myConnection.getResultsList(query, "u_name");
     myList2 = myConnection.getResultsList(query, "passd");
     String[] array1 = myList1.ToArray();
     String[] array2 = myList2.ToArray();
     for (int i = 0; i < array1.Length; i++ )
     {
         if (array1[i]==username && array2[i]==password) { return 1; }
     }
     return 0;
 }

 public String UpdateCV()
 {
     String query1 = String.Format( "UPDATE [Candidate] SET [f_name] = '{0}', [m_name] = '{1}', [l_name] = '{2}', [dob] = '{3}', [gender] = {4}, [maritual_status] = {5}, [add_no] = '{6}', [add_street] = '{7}', [add_town] = '{8}', [email] = '{9}' WHERE u_id = {10}",
                                                             fName, mName, lName, dob, gender, mStatus, number, street, town, email, userID);
     String query2 = String.Format("UPDATE [CanSchool] SET [school] = '{0}' WHERE u_id = {1}", school, userID);
     String query3 = String.Format("UPDATE [PrsPhone] SET [phone] = '{0}' WHERE u_id = {1}", phone, userID);
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String msg1 = myConnection.executeNonReturningQuery(query1);
     String msg2 = myConnection.executeNonReturningQuery(query2);
     String msg3 = myConnection.executeNonReturningQuery(query3);
     return msg1 + msg2 + msg3;
 }

 public String insertComputerLanguage(String language, String proficiency)
 {
     String languageLowerCase = (language.Replace(" ", String.Empty)).ToLower();
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("INSERT INTO ComputerLanguage(u_id, languageLowerCase, language, proficiency) VALUES({0}, '{1}', '{2}', {3})", userID, languageLowerCase, language, proficiency);
     return myConnection.executeNonReturningQuery(query);
 }

 public DataTable getPostsOFForum(String forumID)
 {
     DataTable myTable = new DataTable(); ;
     DBConnection myConnection = new DBConnection();
     myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
     String query = String.Format("SELECT p.post FROM Post AS p, Forum AS f WHERE f.f_id = {0} AND f.f_id = p.f_id ORDER BY p.p_time", forumID);
     myTable = myConnection.executeReturningQuery(query);
     return myTable;
 }

    // function to create a new user account
    public String createAccount(String username, String password, String email, int role)
    {
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        if (this.checkAvailability(username, email) >= 1)
        {
            String query1 = String.Format("INSERT INTO UserAcc(u_name, passd, role) VALUES ('{0}','{1}', {2})", username, password, role);
            String msg1 = myConnection.executeNonReturningQuery(query1);

            return msg1;
        }
        else
        {
            return "Username is already taken.";
        }
    }