Beispiel #1
0
        static void Main(string[] args)
        {
            string connectionString = "Integrated Security=true;" +
                                "database=userFiles;" +
                                "Data Source=.;"; // local machine

            SqlConnectionStringBuilder sqlStrBldr = new SqlConnectionStringBuilder(connectionString);
            sqlStrBldr.UserID = "sa";
            sqlStrBldr.Password = "nhti";

            SqlConnection connection = new SqlConnection(sqlStrBldr.ConnectionString); //  new SqlConnection(connectionString);

            SqlCommand testCommand = new SqlCommand("INSERT INTO musicUsers VALUES('Test5', 'Test5PW')");

            testCommand.Connection = connection;
            testCommand.Connection.Open();

            testCommand.ExecuteNonQuery();

            testCommand.Connection.Close();

            Console.WriteLine(testCommand.ToString());

            SqlCommand command = new SqlCommand("checkMusicUser", connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter userName = new SqlParameter("@uname", SqlDbType.VarChar);
            userName.Direction = ParameterDirection.Input;
            userName.Value = "Test1";
            userName.Size = 50;
            command.Parameters.Add(userName);

            SqlParameter password = new SqlParameter("@passw", SqlDbType.VarChar);
            password.Direction = ParameterDirection.Input;
            password.Value = "Test1PW";
            password.Size = 50;
            command.Parameters.Add(password);

            SqlParameter returnValue = new SqlParameter("@retval", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.Output;
            command.Parameters.Add(returnValue);

            command.Connection.Open();

            command.ExecuteNonQuery();

            Console.WriteLine("TEST");
            Console.WriteLine("Return result: " + command.Parameters["@retval"].Value.ToString());
            Console.ReadKey();
        }
Beispiel #2
0
        /// <summary>
        /// //////////////////////////////connexion to server ////////////////////////////////

        public static void save_points_in_db(double x, double y, double z, int frame, string color, SqlConnection myConnection)
        {


            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }


            SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Points_saved (colx, coly, colz,frame,color) Values ('" + x + "','" + y + "','" + z + "','" + frame + "','" + color + "')", myConnection);

            //    SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Points_saved (colx, coly, colz,frame,color) Values (0.1,0.3,0.5,1,'red')", myConnection);

            Console.WriteLine(myCommand.ToString());
            myCommand.ExecuteNonQuery();
 

            try
            {

                myConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
Beispiel #3
0
        private void btnRegist_Click(object sender, EventArgs e)
        {
            bool finished = true;

            string adminID = this.textBoxID.Text.Trim();
            string adminName = this.textBoxName.Text.Trim();
            string adminSex = this.textBoxSex.Text.Trim();
            string adminBirth = this.textBoxBirth.Text.Substring(0, 10).Trim();

            if (adminID == "" || adminName == "" || adminSex == "" || adminBirth == "")
            {
                MessageBox.Show("请输入完整信息","提示信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
                return;
            }

            if (this.textBoxPassword1.Text.Trim() != this.textBoxPassword2.Text.Trim())
            {
                MessageBox.Show("两次输入密码不一致","错误信息",MessageBoxButtons.OK,MessageBoxIcon.Error);
                this.textBoxPassword1.Text = "";
                this.textBoxPassword2.Text = "";
                return;
            }
            string adminPassword = this.textBoxPassword1.Text.Trim();
            string comStr = string.Format("select * from dboSys where Sys_IID = \'{0}\'", adminID);
            SqlConnection sqlconnection=new SqlConnection(conStr);
            DataSet dataSet = new DataSet();
            try
            {
                dataSet = SqlCommondClass.GetDataSet(comStr, "result", sqlconnection);
            }
            catch (System.Exception)
            {
                finished = false;
                MessageBox.Show("打开数据库失败", "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            finally
            {
                sqlconnection.Close();
            }

            if (dataSet.Tables[0].Rows.Count >= 1)
            {
                MessageBox.Show("该管理员编号已经存在,可以直接登录", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                comStr = string.Format("insert into dboSys(Sys_IID,Sys_NAME,Sys_SEX,Sys_BIRTHDAY,Sys_PASSWORD) values(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\')",
                                                           adminID,adminName,adminSex,adminBirth, coding(adminPassword));
                MessageBox.Show(comStr);
                try
                {
                    sqlconnection.Open();
                    SqlCommand command = new SqlCommand(comStr, sqlconnection);
                    MessageBox.Show(command.ToString());
                    command.ExecuteNonQuery();
                }
                catch (System.Exception)
                {
                    finished = false;
                    MessageBox.Show("创建管理员失败", "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                finally
                {
                    sqlconnection.Close();
                }
                if (finished)
                {
                    MessageBox.Show("创建管理员成功");
                }

            }
        }
Beispiel #4
0
        private void ApplySystemDefaultToAll_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox
                .Show(
                    "This action will overwrite all Pedigree GUI Preferences in the riskApps database.  Are you sure this is what you want to do?",
                    "Irreversible Action",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

            if (result == DialogResult.OK)
            {
                TypeConverter converter = TypeDescriptor.GetConverter(typeof(Font));

                SqlCommand cmdProcedure = null;
                try
                {

                    using (SqlConnection connection = new SqlConnection(BCDB2.Instance.getConnectionString()))
                    {
                        connection.Open();
                        cmdProcedure = new SqlCommand("sp_3_UpdateLkpGuiPrefsEnMasse", connection);
                        cmdProcedure.CommandType = CommandType.StoredProcedure;
                        cmdProcedure.CommandTimeout = 300; //change command timeout from default to 5 minutes

                        //SessionManager.Instance.MetaData.SystemWideDefaultPedigreePrefs.ConsumeSettings(this.preferences);

                        //GUIPreference p = SessionManager.Instance.MetaData.SystemWideDefaultPedigreePrefs;

                        cmdProcedure.Parameters.Add("@modifiedDate", SqlDbType.DateTime);
                        cmdProcedure.Parameters["@modifiedDate"].Value = DateTime.Now;
                        cmdProcedure.Parameters.Add("@pedigreeZoomValue", SqlDbType.Int);
                        cmdProcedure.Parameters["@pedigreeZoomValue"].Value = preferences.GUIPreference_zoomValue;
                        cmdProcedure.Parameters.Add("@pedigreeVerticalSpacing", SqlDbType.Int);
                        cmdProcedure.Parameters["@pedigreeVerticalSpacing"].Value = preferences.GUIPreference_verticalSpacing;
                        cmdProcedure.Parameters.Add("@modifiedBy", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@modifiedBy"].Value = SessionManager.Instance.ActiveUser.userLogin;
                        cmdProcedure.Parameters.Add("@ShowRelIds", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowRelIds"].Value = preferences.GUIPreference_ShowRelIds;
                        cmdProcedure.Parameters.Add("@PedigreeBackground", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@PedigreeBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_PedigreeBackground);
                        cmdProcedure.Parameters.Add("@nameWidth", SqlDbType.Int);
                        cmdProcedure.Parameters["@nameWidth"].Value = preferences.GUIPreference_nameWidth;
                        cmdProcedure.Parameters.Add("@limitedEthnicity", SqlDbType.Bit);
                        cmdProcedure.Parameters["@limitedEthnicity"].Value = preferences.GUIPreference_limitedEthnicity;
                        cmdProcedure.Parameters.Add("@limitedNationality", SqlDbType.Bit);
                        cmdProcedure.Parameters["@limitedNationality"].Value = preferences.GUIPreference_limitedNationality;
                        cmdProcedure.Parameters.Add("@ShowTitle", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowTitle"].Value = preferences.GUIPreference_ShowTitle;
                        cmdProcedure.Parameters.Add("@ShowName", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowName"].Value = preferences.GUIPreference_ShowName;
                        cmdProcedure.Parameters.Add("@NameFont", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@NameFont"].Value = converter.ConvertToString(preferences.GUIPreference_NameFont);
                        cmdProcedure.Parameters.Add("@ShowUnitnum", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowUnitnum"].Value = preferences.GUIPreference_ShowUnitnum;
                        cmdProcedure.Parameters.Add("@UnitnumFont", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@UnitnumFont"].Value = converter.ConvertToString(preferences.GUIPreference_UnitnumFont);
                        cmdProcedure.Parameters.Add("@ShowDob", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowDob"].Value = preferences.GUIPreference_ShowDob;
                        cmdProcedure.Parameters.Add("@DobFont", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@DobFont"].Value = converter.ConvertToString(preferences.GUIPreference_DobFont);
                        cmdProcedure.Parameters.Add("@TitleSpacing", SqlDbType.Int);
                        cmdProcedure.Parameters["@TitleSpacing"].Value = preferences.GUIPreference_TitleSpacing;
                        cmdProcedure.Parameters.Add("@TitleBackground", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@TitleBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_TitleBackground);
                        cmdProcedure.Parameters.Add("@TitleBorder", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@TitleBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_TitleBorder);
                        cmdProcedure.Parameters.Add("@ShowLegend", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowLegend"].Value = preferences.GUIPreference_ShowLegend;
                        cmdProcedure.Parameters.Add("@LegendBackground", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@LegendBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_LegendBackground);
                        cmdProcedure.Parameters.Add("@LegendBorder", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@LegendBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_LegendBorder);
                        cmdProcedure.Parameters.Add("@LegendRadius", SqlDbType.Int);
                        cmdProcedure.Parameters["@LegendRadius"].Value = preferences.GUIPreference_LegendRadius;
                        cmdProcedure.Parameters.Add("@LegendFont", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@LegendFont"].Value = converter.ConvertToString(preferences.GUIPreference_LegendFont);
                        cmdProcedure.Parameters.Add("@ShowComment", SqlDbType.Bit);
                        cmdProcedure.Parameters["@ShowComment"].Value = preferences.GUIPreference_ShowComment;
                        cmdProcedure.Parameters.Add("@CommentBackground", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@CommentBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_CommentBackground);
                        cmdProcedure.Parameters.Add("@CommentBorder", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@CommentBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_CommentBorder);
                        cmdProcedure.Parameters.Add("@CommentFont", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@CommentFont"].Value = converter.ConvertToString(preferences.GUIPreference_CommentFont);
                        cmdProcedure.Parameters.Add("@LegendX", SqlDbType.Int);
                        cmdProcedure.Parameters["@LegendX"].Value = preferences.GUIPreference_LegendX;
                        cmdProcedure.Parameters.Add("@LegendY", SqlDbType.Int);
                        cmdProcedure.Parameters["@LegendY"].Value = preferences.GUIPreference_LegendY;
                        cmdProcedure.Parameters.Add("@LegendHeight", SqlDbType.Int);
                        cmdProcedure.Parameters["@LegendHeight"].Value = preferences.GUIPreference_LegendHeight;
                        cmdProcedure.Parameters.Add("@LegendWidth", SqlDbType.Int);
                        cmdProcedure.Parameters["@LegendWidth"].Value = preferences.GUIPreference_LegendWidth;
                        cmdProcedure.Parameters.Add("@TitleX", SqlDbType.Int);
                        cmdProcedure.Parameters["@TitleX"].Value = preferences.GUIPreference_TitleX;
                        cmdProcedure.Parameters.Add("@TitleY", SqlDbType.Int);
                        cmdProcedure.Parameters["@TitleY"].Value = preferences.GUIPreference_TitleY;
                        cmdProcedure.Parameters.Add("@TitleHeight", SqlDbType.Int);
                        cmdProcedure.Parameters["@TitleHeight"].Value = preferences.GUIPreference_TitleHeight;
                        cmdProcedure.Parameters.Add("@TitleWidth", SqlDbType.Int);
                        cmdProcedure.Parameters["@TitleWidth"].Value = preferences.GUIPreference_TitleWidth;
                        cmdProcedure.Parameters.Add("@CommentX", SqlDbType.Int);
                        cmdProcedure.Parameters["@CommentX"].Value = preferences.GUIPreference_CommentX;
                        cmdProcedure.Parameters.Add("@CommentY", SqlDbType.Int);
                        cmdProcedure.Parameters["@CommentY"].Value = preferences.GUIPreference_CommentY;
                        cmdProcedure.Parameters.Add("@CommentHeight", SqlDbType.Int);
                        cmdProcedure.Parameters["@CommentHeight"].Value = preferences.GUIPreference_CommentHeight;
                        cmdProcedure.Parameters.Add("@CommentWidth", SqlDbType.Int);
                        cmdProcedure.Parameters["@CommentWidth"].Value = preferences.GUIPreference_CommentWidth;
                        cmdProcedure.Parameters.Add("@VariantFoundText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantFoundText"].Value = preferences.GUIPreference_VariantFoundText;
                        cmdProcedure.Parameters.Add("@VariantFoundVusText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantFoundVusText"].Value = preferences.GUIPreference_VariantFoundVusText;
                        cmdProcedure.Parameters.Add("@VariantNotFoundText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantNotFoundText"].Value = preferences.GUIPreference_VariantNotFoundText;
                        cmdProcedure.Parameters.Add("@VariantUnknownText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantUnknownText"].Value = preferences.GUIPreference_VariantUnknownText;
                        cmdProcedure.Parameters.Add("@VariantNotTestedText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantNotTestedText"].Value = preferences.GUIPreference_VariantNotTestedText;
                        cmdProcedure.Parameters.Add("@VariantHeteroText", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@VariantHeteroText"].Value = preferences.GUIPreference_VariantHeteroText;
                        cmdProcedure.Parameters.Add("@hideNonBloodRelatives", SqlDbType.NVarChar);
                        cmdProcedure.Parameters["@hideNonBloodRelatives"].Value = preferences.GUIPreference_hideNonBloodRelatives;

                        cmdProcedure.ExecuteNonQuery();
                    }

                    //bw 6/2/2014 - added annotation placements
                    foreach (PedigreeAnnotation pa in preferences.annotations)
                    {
                        ParameterCollection pc = new ParameterCollection();
                        pc.Add("unitnum", "-2");
                        pc.Add("area", pa.area);
                        pc.Add("slot", pa.slot);
                        pc.Add("annotation", pa.annotation);
                        pc.Add("user", SessionManager.Instance.ActiveUser.userLogin);
                        BCDB2.Instance.RunSPWithParams("sp_3_Save_PedigreeAnnotations", pc);
                    }

                    MessageBox.Show(
                        "Current GUI Preferences applied to all patients in database.",
                        "Complete",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    Logger.Instance.WriteToLog("[sp_3_UpdateLkpGuiPrefsEnMasse] Executing Stored Procedure - "
                            + cmdProcedure.ToString() + "; " + ex.ToString());
                }
            }
            else
            {
                return;
            }
        }
Beispiel #5
0
        /// <summary>
        /// 将其它SQL命令参与到当前事务中来
        /// </summary>
        /// <param name="sqlCmd"></param>
        public void JohnTrans(SqlCommand sqlCmd)
        {
            this.ClearErrInfo();

            try
            {
                sqlCmd.Transaction = this.trans;
            }
            catch (Exception exp)
            {
                this.errMsg = sqlCmd.ToString() + "参与sql事务失败," + exp.Message;
                this.WriteErr();
            }
        }
Beispiel #6
0
        protected void ButtonAddDvd_Click(object sender, EventArgs e)
        {
            string res = ""; //hidden sql connection result variable
            SqlConnection conn;
            SqlCommand comm, commdet;
            string connectionString = ConfigurationManager.ConnectionStrings["DVDconnstring"].ConnectionString;
            conn = new SqlConnection(connectionString);
            comm = new SqlCommand("INSERT INTO DVDtable (DVDtitle, DVDartist, DVDrating, DVDprice) "
                + " VALUES (@DVDtitle, @DVDartist, @DVDrating, @DVDprice); SELECT SCOPE_IDENTITY();", conn);
            commdet = new SqlCommand("INSERT INTO Details(DVDID, Description, PicURL) "
                + "VALUES ( @DVDID, @Description, @PicURL)", conn);
            try //declare parameters and enter data
            {
                comm.Parameters.Add("@DVDtitle", System.Data.SqlDbType.NVarChar);
                comm.Parameters["@DVDtitle"].Value = textboxDVDTitle.Text; //  "meow"; //
                comm.Parameters.Add("@DVDartist", System.Data.SqlDbType.NVarChar);
                comm.Parameters["@DVDartist"].Value = textboxDVDArtist.Text; //"meow"; //
                comm.Parameters.Add("@DVDrating", System.Data.SqlDbType.Int);
                comm.Parameters["@DVDrating"].Value = Convert.ToInt32(textboxDVDRating.Text); // 1; //
                comm.Parameters.Add("@DVDprice", System.Data.SqlDbType.Money);
                comm.Parameters["@DVDprice"].Value  = Convert.ToDouble(textboxDVDPrice.Text); // 1; //

            }
            catch //catch errors in comm declarations such as text entered into numeric fields
            {
                dbErrorLabel.Text = "Something was entered wrong.";
                res += comm.ToString() + "\n"
                    + comm.Parameters.ToString() + "\n";
            }

            try //connect to SQL
            {
                dbErrorLabel.Text = "Connecting...";
                conn.Open();

                if (textboxDVDDescription.Text != "" && textboxDVDDescription.Text != null)
                {
                    commdet.Parameters.Add("@DVDID", System.Data.SqlDbType.Int);
                    commdet.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 500);
                    commdet.Parameters.Add("@PicURL", System.Data.SqlDbType.NVarChar, 100);

                    commdet.Parameters["@DVDID"].Value = comm.ExecuteScalar();
                    dbErrorLabel.Text = " DVD named " + comm.Parameters["@DVDtitle"].Value + " was added! ";
                    commdet.Parameters["@Description"].Value = textboxDVDDescription.Text;
                    commdet.Parameters["@PicURL"].Value = textboxDVDPicURL.Text;
                    dbErrorLabel.Text += " " + commdet.ExecuteNonQuery() + " detail added.";
                }else
                {
                    comm.ExecuteScalar();
                    dbErrorLabel.Text = " DVD named " + comm.Parameters["@DVDtitle"].Value + " was added! ";
                }

                InterfaceClearFields();
            }
            catch (SqlException se) //catches sql failures and rejections
            {
                dbErrorLabel.Text += "There was an error adding this DVD entry to the system. "+ se.ToString();
                res += se.ToString() + "\n" + e.ToString() + "\n";
            }
            finally
            {
                res += "results of execution. \n"
                    + comm.Parameters[0].Value + "\n"
                    + conn.ToString() + " " + conn.State + "\n closing connection... ";
                conn.Close();
                res += conn.State +"\n";
            }
        }
        public SqlDataReader CheckUpdate(string table1, string table2, string pkey1, string pkey2, ArrayList compareFields1, ArrayList compareFields2, ArrayList additionalFields, int adField, SqlConnection sqlConn, LogFile log)
        {
            // adField holds the value of which table holds values from ad to check the AD field fomat distinguished name vs samaccount / id if ad field is 0 neither should be checked
            // managerADtype if true manager field holds an AD object cn=name,ou=blah,dc=blah else it is a samaccount field
            // NULL not handeled as blanks
            // additionalFields takes the field names " table.field,"
            // Assumes table1 holds the correct data and returns a data reader with the update fields columns from table1
            // compare fields 1 & 2 should have the same number of items or it is likely that all rows will be found needing updating
            // returns fields from comparefields
            // returns the rows which table2 differs from table1

            // Assumes table1 holds the correct data and returns a data reader with the update fields columns from table1
            // returns the rows which table2's concatenated update fields differ from table1's concatenated update fields
            // eliminates rows which do not have a matching key in both tables
            //
            // additional fields table2.data2, table1.data5
            //*************************************************************************************************
            //| Table1                                  | Table2                                | Returned result
            //*************************************************************************************************
            //| ID            Data          Data5       | ID             Data       Data2       |                   | Table1.ID     Table1.DATA table1.data5        table2.data2
            //| 1             a             ty          | 1              a          e           | NOT RETURNED      |
            //| 2             b             e           | null           null       r           | NOT RETURNED      |
            //| 3             c             uyt         | 3              null       f           | RETURNED          | 3             c           uyt                 f
            //| 4             d             tr          | 4              e          w           | RETURNED          | 4             e           tr                  w
            //| 4             f             sr          | 4              f          w           | NOT RETURNED      |

            string compare1 = "";
            string compare2 = "";
            string fields = "";
            string notnull = "";
            string additionalfields = "";
            bool managerADtype = false;
            int i = 0;

            //int i;
            //string debugRecordCount = "";
            //string debug = "";
            //debug = " total users from AD \n";
            //SqlCommand sqlDebugComm = new SqlCommand("select top 20 * FROM " + table2, sqlConn);
            //SqlDataReader debugReader = sqlDebugComm.ExecuteReader();
            //int debugFieldCount = debugReader.FieldCount;
            //for (i = 0; i < debugFieldCount; i++)
            //{
            //    debug += debugReader.GetName(i) + ", ";
            //}
            //debug += "\n";
            //while (debugReader.Read())
            //{
            //    for (i = 0; i < debugFieldCount; i++)
            //    {
            //        debug += (string)debugReader[i] + ", ";
            //    }
            //    debug += "\n";
            //}
            //sqlDebugComm = new SqlCommand("select count(" + pkey2 + ") FROM " + table1, sqlConn);
            //debugReader.Close();
            //debugRecordCount = sqlDebugComm.ExecuteScalar().ToString();
            //MessageBox.Show("table " + table2 + " has " + debugRecordCount + " records \n " + debugFieldCount + " fields \n sample data" + debug);

            //check to see what format the manager is in and what table it is in
            if (adField == 1)
            {
                foreach (string key in compareFields1)
                {
                    if (key == "CN")
                    {
                    }
                    if (key == "manager")
                    {

                        SqlCommand sqlCheck = new SqlCommand("select top 1 manager FROM " + table1, sqlConn);
                        // create the command object
                        SqlDataReader checkReader;
                        try
                        {
                            sqlCheck.CommandTimeout = 360;
                            checkReader = sqlCheck.ExecuteReader();
                            log.addTrn(sqlCheck.CommandText.ToString(), "Query");
                            checkReader.Read();
                            if ((string)checkReader[0].ToString().Substring(0, 2) == "CN=")
                            {
                                managerADtype = true;
                            }
                            checkReader.Close();
                        }
                        catch (Exception ex)
                        {
                            log.addTrn("Failed SQL command " + sqlCheck.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                        }

                    }
                }
            }
            else if (adField == 2)
            {
                foreach (string key in compareFields2)
                {
                    if (key == "manager")
                    {

                        SqlCommand sqlCheck = new SqlCommand("select top 1 manager FROM " + table2, sqlConn);
                        // create the command object
                        SqlDataReader checkReader;
                        try
                        {
                            sqlCheck.CommandTimeout = 360;
                            checkReader = sqlCheck.ExecuteReader();
                            log.addTrn(sqlCheck.ToString(), "Query");
                            checkReader.Read();
                            if ((string)checkReader[0].ToString().Substring(0, 2) == "CN=")
                            {
                                managerADtype = true;
                            }
                            checkReader.Close();
                        }
                        catch (Exception ex)
                        {
                            log.addTrn("Failed SQL command " + sqlCheck.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                        }
                    }
                }
            }

            // need a comand builder and research on the best way to compare all fields in a row
            // this basically will just issue a concatenation sql query to the DB for each field to compare
            //foreach (string key in compareFields1)
            //{
            //    if (managerADtype == false && key == "manager" && adField == 2)
            //    {
            //        //add code for substring of manager field
            //        compare1 = compare1 + "case when " + table2 + "." + compareFields2[i] + " <> '' then (substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + ";
            //        fields += "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5), ";
            //    }
            //    else
            //    {
            //         compare1 = compare1 + "case when " + table2 + "." + compareFields2[i] + " <> '' then (" + table1 + "." + key + " COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + ";
            //         fields += table1 + "." + key + ", ";
            //    }
            //    i++;
            //}
            //i = 0;

            foreach (string key in compareFields1)
            {
                if (managerADtype == false && key == "manager" && adField == 2)
                {
                    //add code for substring of manager field
                    compare1 = compare1 + "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS + ";
                    fields += "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5), ";
                }
                else
                {
                    compare1 = compare1 + "ltrim(rtrim(" + table1 + "." + key + ")) COLLATE SQL_Latin1_General_CP1_CS_AS + ";
                    fields += table1 + "." + key + ", ";
                }
                i++;
            }
            i = 0;
            //compare1 = compare1 + table1 + "." + pkey1;
            foreach (string key in compareFields2)
            {
                if (managerADtype == false && key == "manager" && adField == 1)
                {
                    //add code for substring of manager field
                    compare2 = compare2 + "case when ltrim(rtrim(" + table2 + "." + compareFields2[i] + ")) <> '' then (substring(" + table2 + "." + key + ",4, charindex('ou=', " + table2 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + ";
                    notnull += "case when len(ltrim(rtrim(" + table2 + "." + key + "))) > 3 then substring(" + table2 + "." + key + ",4, charindex('ou=', " + table2 + "." + key + ")-5) else '' end <> '' OR ";
                }
                else
                {
                    compare2 = compare2 + "case when ltrim(rtrim(" + table2 + "." + compareFields2[i] + ")) <> '' then (ltrim(rtrim(" + table2 + "." + key + ")) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + ";
                    //fields += table2 + "." + key + ", ";
                    notnull += "ltrim(rtrim(" + table2 + "." + key + ")) <> '' OR ";
                }
                i++;
            }
            //compare2 = compare2 + table2 + "." + pkey2;
            foreach (string key in additionalFields)
            {
                additionalfields += key;
            }
            // remove trailing comma and +
            compare2 = compare2.Remove(compare2.Length - 2);
            compare1 = compare1.Remove(compare1.Length - 2);
            fields = fields.Remove(fields.Length - 2);
            notnull = notnull.Remove(notnull.Length - 3);
            additionalfields = additionalfields.Remove(additionalfields.Length - 2);
            SqlCommand sqlComm;
            if (additionalFields.Count > 0)
            {
                sqlComm = new SqlCommand("SELECT DISTINCT " /*+ compare2 + "," + compare1 + "," + table1 + "." + pkey1 + "," + table2 + "." + pkey2 + ","*/ + fields + ", " + additionalfields + " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2 + " AND (" + compare2 + ") <> (" + compare1 + ") INNER JOIN " + table2 + " as [" + table2 + "temp] ON " + table1 + "." + pkey1 + " = [" + table2 + "temp]." + pkey2 + " AND " + table2 + "." + pkey2 + " IS NOT NULL WHERE " + notnull, sqlConn);
            }
            else
            {
                sqlComm = new SqlCommand("SELECT DISTINCT " + fields + " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2 + " AND (" + compare2 + ") <> (" + compare1 + ") INNER JOIN " + table2 + " as [" + table2 + "temp] ON " + table1 + "." + pkey1 + " = [" + table2 + "temp]." + pkey2 + " AND " + table2 + "." + pkey2 + " IS NOT NULL WHERE " + notnull, sqlConn);
            }
            //AND " + table2 + "." + pkey2 + " != NULL
            try
            {
                sqlComm.CommandTimeout = 360;
                SqlDataReader r = sqlComm.ExecuteReader();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");
                return r;
            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
            }
            return null;
        }
Beispiel #8
0
        protected void ButtonSignin_Click(object sender, EventArgs e)
        {
            if (txtPassword.Text == "" || txtUserID.Text == "")
            {
                if (txtPassword.Text == "")
                {
                    LabelPasswordWarning.Text = "*Required";
                    LabelPasswordWarning.Visible = true;
                }

                if (txtUserID.Text == "")
                {
                    LabelIDWarning.Text = "*Required";
                    LabelIDWarning.Visible = true;
                }

            }
            else
            {

                string userName = txtUserID.Text,
                        userPassword = txtPassword.Text;

                SqlConnection db = new SqlConnection(SqlDataSource1.ConnectionString);
                SqlCommand cmd = new SqlCommand();

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "SELECT COUNT(*) FROM [User] WHERE ([User_Email] = '" + userName + "')";
                cmd.Connection = db;
                db.Open();

                LabelIDWarning.Text = cmd.ToString();

                int cat = (int)cmd.ExecuteScalar();

                /************************************************************************************************
                 *
                 * function: Looks for the Username entered by the client.
                 *
                 ************************************************************************************************/

                try
                {
                    if (cat == 1)
                    {
                        LabelIDWarning.Text = "Success!";
                        LabelIDWarning.Visible = true;
                        LabelPasswordWarning.Visible = false;
                    }
                    else
                    {
                        LabelIDWarning.Text = "User Name not found";
                        LabelIDWarning.Visible = true;
                        cat = 0;
                    }

                }
                catch
                {

                    LabelIDWarning.Text = "Error!";
                    LabelIDWarning.Visible = true;

                }
                finally
                {

                    db.Close();
                }

                /************************************************************************************************
                 *
                 * function: Looks for the password of the matching username above.
                 *
                 ************************************************************************************************/

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "SELECT COUNT(*) FROM [User] WHERE ([User_Password] = '" + userPassword + "')";
                cmd.Connection = db;
                db.Open();
                cat += (int)cmd.ExecuteScalar();

                try
                {
                    if (cat == 2)
                    {
                        LabelIDWarning.Text = "Success!";
                        LabelIDWarning.Visible = true;
                        LabelPasswordWarning.Visible = false;

                        Response.Redirect("/home.aspx");

                    }
                    else
                    {
                        LabelPasswordWarning.Text = "Incorrect Password";
                        LabelPasswordWarning.Visible = true;
                        cat = 0;
                    }

                }
                catch
                {

                    LabelIDWarning.Text = "Error!";
                    LabelIDWarning.Visible = true;
                }
                finally
                {

                    db.Close();
                }
            }
        }