C# DataLayer.Insert方法代码示例

        public Users InsertUsers(Users user, AuthTable authUser, string connString)
        {
            string errorMessage = string.Empty;
            if (IsValidUserData(user, authUser, out errorMessage))
            {

                SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
                SqlParameter pUserFullName = new SqlParameter("@UserFullName", SqlDbType.NVarChar, 100);
                SqlParameter pUserEmailId = new SqlParameter("@UserEmailId", SqlDbType.NVarChar, 100);
                SqlParameter pUserGender = new SqlParameter("@UserGender", SqlDbType.Bit);
                SqlParameter pUserDOB = new SqlParameter("@UserDOB", SqlDbType.SmallDateTime);
                SqlParameter pUserAreaOfInterest = new SqlParameter("@UserAreaOfInterest", SqlDbType.NVarChar, 100);

                SqlParameter pUsernameAuth = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
                SqlParameter pPassword = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
                SqlParameter pRole = new SqlParameter("@Role", SqlDbType.Int);

                pUsername.Value = user.Username;
                pUserFullName.Value = user.UserFullName;
                pUserEmailId.Value = user.UserEmailId;
                pUserGender.Value = user.UserGender;
                pUserDOB.Value = user.UserDOB;
                pUserAreaOfInterest.Value = user.UserAreaOfInterest;

                pUsernameAuth.Value = user.Username;
                pPassword.Value = authUser.Password;
                pRole.Value = authUser.Role;

                SqlCommand cmdSQL = new SqlCommand();
                cmdSQL.CommandType = CommandType.StoredProcedure;
                cmdSQL.CommandText = "usp_InsertAuthTable";
                cmdSQL.Parameters.Add(pUsernameAuth);
                cmdSQL.Parameters.Add(pPassword);
                cmdSQL.Parameters.Add(pRole);

                bool isUsernamePresent = IsUsernamePresent(authUser, connString);
                DataLayer dataLayerObj = new DataLayer();
                if (!isUsernamePresent)
                {
                    cmdSQL = dataLayerObj.Insert(cmdSQL, connString);
                }
                else
                {
                    errorMessage = "Username already Present." + "<br/>" + errorMessage;
                }

                cmdSQL = new SqlCommand();
                cmdSQL.CommandType = CommandType.StoredProcedure;
                cmdSQL.CommandText = "usp_InsertUser";
                cmdSQL.Parameters.Add(pUsername);
                cmdSQL.Parameters.Add(pUserFullName);
                cmdSQL.Parameters.Add(pUserEmailId);
                cmdSQL.Parameters.Add(pUserGender);
                cmdSQL.Parameters.Add(pUserDOB);
                cmdSQL.Parameters.Add(pUserAreaOfInterest);

                if (!isUsernamePresent)
                {
                    cmdSQL = dataLayerObj.Insert(cmdSQL, connString);
                }
                else
                {
                    throw new Exception(errorMessage); ;

                }
            }
            else
            {
                throw new Exception(errorMessage);
            }
            return user;
        }

        public void InsertVote(Vote voteObj, string connString)
        {
            //declaration of parameters
            SqlParameter pAdviceId = new SqlParameter("@AdviceId", SqlDbType.Int);
            SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
            SqlParameter pIsLiked = new SqlParameter("@IsLiked", SqlDbType.Bit, 2);

            //initialization of parameters

            pAdviceId.Value = voteObj.AdviceId;
            pUsername.Value = voteObj.Username;
            pIsLiked.Value = voteObj.Isliked;

            //adding params to query

            SqlCommand cmdSql = new SqlCommand();
            cmdSql.CommandType = CommandType.StoredProcedure;
            cmdSql.CommandText = "usp_InsertVote";

            cmdSql.Parameters.Add(pAdviceId);
            cmdSql.Parameters.Add(pUsername);
            cmdSql.Parameters.Add(pIsLiked);

            //storage in data layer

            DataLayer datalayerObj = new DataLayer();
            cmdSql = datalayerObj.Insert(cmdSql, connString);
            return;
        }

        //insert Query
        public Query InsertQuery(Query queryObj, string connString)
        {
            string errorMessage;
            //declaration of parameters
            if (isValidQuery(queryObj, out errorMessage))
            {
                SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int);
                SqlParameter pQueryAreaOfInterest = new SqlParameter("@QueryAreaOfInterest", SqlDbType.NVarChar, 100);
                SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
                SqlParameter pSymptoms = new SqlParameter("@Symptoms", SqlDbType.NVarChar, 50);
                SqlParameter pMedicalHistory = new SqlParameter("@MedicalHistory", SqlDbType.NVarChar, 500);
                SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.NVarChar, 20);
                SqlParameter pIsAttendedTo = new SqlParameter("@IsAttendedTo", SqlDbType.Bit);
                SqlParameter pPostedDateTime = new SqlParameter("@PostedDateTime", SqlDbType.DateTime);

                //initialization

                //strings
                pQueryId.Direction = ParameterDirection.Output;
                pQueryAreaOfInterest.Value = queryObj.QueryAreaOfInterest;
                pUsername.Value = queryObj.Username;
                pSymptoms.Value = queryObj.Symptoms;
                pMedicalHistory.Value = queryObj.MedicalHistory;
                pStatus.Value = queryObj.Status;

                //the boolean value
                pIsAttendedTo.Value = queryObj.IsAttendedTo;

                //the date time value
                pPostedDateTime.Value = queryObj.PostedDatetime;

                //adding params to query
                SqlCommand cmdSql = new SqlCommand();
                cmdSql.CommandType = CommandType.StoredProcedure;
                cmdSql.CommandText = "usp_InsertQuery";

                cmdSql.Parameters.Add(pQueryAreaOfInterest);
                cmdSql.Parameters.Add(pUsername);
                cmdSql.Parameters.Add(pSymptoms);
                cmdSql.Parameters.Add(pMedicalHistory);
                cmdSql.Parameters.Add(pStatus);
                cmdSql.Parameters.Add(pIsAttendedTo);
                cmdSql.Parameters.Add(pPostedDateTime);
                cmdSql.Parameters.Add(pQueryId);

                //storage in data layer
                DataLayer datalayerObj = new DataLayer();
                datalayerObj.Insert(cmdSql, connString);
                queryObj.QueryId = int.Parse(pQueryId.Value.ToString());
            }
            else
            {
                throw new Exception(errorMessage);
            }

            //queryObj.QueryId = int.Parse(cmdSql.Parameters["@QueryId"].Value.ToString());
            return queryObj;
        }

        public Prescriptions InsertPrescription(Prescriptions prescriptionsObj, string connString)
        {
            SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
            SqlParameter pPrescriptionDateTime = new SqlParameter("@PrescriptionDateTime", SqlDbType.DateTime);
            SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.NVarChar, 7);
            SqlParameter pPrescriptionId = new SqlParameter("@PrescriptionId", SqlDbType.Int);
            //parmeter value intialization
            pUsername.Value = prescriptionsObj.Username;
            pPrescriptionDateTime.Value = prescriptionsObj.PrescriptionDateTime;
            pQueryId.Value = prescriptionsObj.QueryId;
            pPrescriptionId.Direction = ParameterDirection.Output;

            SqlCommand cmdSql = new SqlCommand();
            cmdSql.CommandType = CommandType.StoredProcedure;
            cmdSql.CommandText = "usp_InsertPrescription_test";
            cmdSql.Parameters.Add(pUsername);
            cmdSql.Parameters.Add(pPrescriptionDateTime);
            cmdSql.Parameters.Add(pQueryId);
            cmdSql.Parameters.Add(pPrescriptionId);
            DataLayer datalayerObj = new DataLayer();
            cmdSql = datalayerObj.Insert(cmdSql, connString);
            prescriptionsObj.PrescriptionId = Convert.ToInt32(pPrescriptionId.Value);
            return prescriptionsObj;
        }

        public PrescribedMedicine InsertPrescribedMedicine(PrescribedMedicine prescribedMedicineObj, string connString)
        {
            SqlParameter pMedicineName = new SqlParameter("@MedicineName", SqlDbType.NVarChar, 50);
            SqlParameter pPrescriptionId = new SqlParameter("@PrescriptionId", SqlDbType.Int, 50);
            SqlParameter pDosageBreakfast = new SqlParameter("@DosageBreakfast", SqlDbType.NVarChar, 7);
            SqlParameter pDosageLunch = new SqlParameter("@DosageLunch", SqlDbType.NVarChar, 7);
            SqlParameter pDosageDinner = new SqlParameter("@DosageDinner", SqlDbType.NVarChar, 7);
            SqlParameter pDays = new SqlParameter("@Days", SqlDbType.Int, 10);
            SqlParameter pNumberOfTablets = new SqlParameter("@NumberOfTablets", SqlDbType.Int, 10);

            //parmeter value intialization
            pMedicineName.Value = prescribedMedicineObj.MedicineName;
            pPrescriptionId.Value = prescribedMedicineObj.PrescriptionId;
            pDosageBreakfast.Value = prescribedMedicineObj.DosageBreakfast;
            pDosageLunch.Value = prescribedMedicineObj.DosageLunch;
            pDosageDinner.Value = prescribedMedicineObj.DosageDinner;
            pDays.Value = prescribedMedicineObj.Days;
            pNumberOfTablets.Value = prescribedMedicineObj.NumberOfTablets;

            SqlCommand cmdSql = new SqlCommand();
            cmdSql.CommandType = CommandType.StoredProcedure;
            cmdSql.CommandText = "usp_InsertPrescribedMedicines";
            cmdSql.Parameters.Add(pMedicineName);
            cmdSql.Parameters.Add(pPrescriptionId);
            cmdSql.Parameters.Add(pDosageBreakfast);
            cmdSql.Parameters.Add(pDosageLunch);
            cmdSql.Parameters.Add(pDosageDinner);
            cmdSql.Parameters.Add(pDays);
            cmdSql.Parameters.Add(pNumberOfTablets);
            DataLayer datalayerObj = new DataLayer();
            cmdSql = datalayerObj.Insert(cmdSql, connString);
            return prescribedMedicineObj;
        }

        public void InsertComment(Comments commentObj, string connString)
        {
            string errormsg;
            try
            {
                if (IsValidComment(commentObj, out errormsg))
                {
                    SqlParameter pAdviceId = new SqlParameter("@AdviceId", SqlDbType.Int, 0);
                    SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
                    SqlParameter pCommentsField = new SqlParameter("@CommentsField", SqlDbType.NVarChar, 400);
                    SqlParameter pCommentDateTime = new SqlParameter("CommentDateTime", SqlDbType.DateTime, 0);

                    pAdviceId.Value = commentObj.AdviceId;
                    pUsername.Value = commentObj.Username;
                    pCommentsField.Value = commentObj.CommentsField;
                    pCommentDateTime.Value = commentObj.CommentDateTime;

                    SqlCommand cmdSql = new SqlCommand();
                    cmdSql.CommandType = CommandType.StoredProcedure;
                    cmdSql.CommandText = "usp_InsertComment";
                    cmdSql.Parameters.Add(pAdviceId);
                    cmdSql.Parameters.Add(pUsername);
                    cmdSql.Parameters.Add(pCommentsField);
                    cmdSql.Parameters.Add(pCommentDateTime);

                    DataLayer datalayerObj = new DataLayer();
                    cmdSql = datalayerObj.Insert(cmdSql, connString);
                }
                else
                {
                    throw new Exception(errormsg);
                }
            }
            catch
            {
                throw;
            }
        }

        public Advice InsertAdvice(Advice adviceObj, string connString)
        {
            //declaration of parameters
            string errorMessage;

            if (isValidAdvice(adviceObj, out errorMessage))
            {
                SqlParameter pQueryId = new SqlParameter("@QueryId", SqlDbType.Int);
                SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
                SqlParameter pLikes = new SqlParameter("@Likes", SqlDbType.Int);
                SqlParameter pDislikes = new SqlParameter("@Dislikes", SqlDbType.Int);
                SqlParameter pAdviceDescription = new SqlParameter("@AdviceDescription", SqlDbType.NVarChar, 400);
                SqlParameter pAdviceTitle = new SqlParameter("@AdviceTitle", SqlDbType.NVarChar, 50);
                SqlParameter pAdviceDateTime = new SqlParameter("@AdviceDateTime", SqlDbType.DateTime);

                //initialization
                pQueryId.Value = adviceObj.QueryId;
                pUsername.Value = adviceObj.Username;
                pLikes.Value = adviceObj.Likes;
                pDislikes.Value = adviceObj.Dislikes;
                pAdviceDescription.Value = adviceObj.AdviceDescription;
                pAdviceTitle.Value = adviceObj.AdviceTitle;
                pAdviceDateTime.Value = adviceObj.AdviceDateTime;

                //adding params to query
                SqlCommand cmdSql = new SqlCommand();
                cmdSql.CommandType = CommandType.StoredProcedure;
                cmdSql.CommandText = "usp_InsertAdvice";

                cmdSql.Parameters.Add(pQueryId);
                cmdSql.Parameters.Add(pUsername);
                cmdSql.Parameters.Add(pLikes);
                cmdSql.Parameters.Add(pDislikes);
                cmdSql.Parameters.Add(pAdviceDescription);
                cmdSql.Parameters.Add(pAdviceTitle);
                cmdSql.Parameters.Add(pAdviceDateTime);

                //storage in data layer
                DataLayer datalayerObj = new DataLayer();
                cmdSql = datalayerObj.Insert(cmdSql, connString);

                //updating query status 'IsAttendedTo'
                SqlParameter pIsAttendedTo = new SqlParameter("@IsAttendedTo", SqlDbType.Bit);
                SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.VarChar, 30);
                SqlParameter pQuery1 = new SqlParameter("@QueryId", SqlDbType.Int);
                pQuery1.Value = pQueryId.Value;
                pIsAttendedTo.Value = true;
                pStatus.Value = "Answered";

                //first command
                SqlCommand cmdSql1 = new SqlCommand();

                cmdSql1.CommandType = CommandType.StoredProcedure;
                cmdSql1.CommandText = "usp_UpdateQueryAttended";

                cmdSql1.Parameters.Add(pQuery1);
                cmdSql1.Parameters.Add(pIsAttendedTo);

                datalayerObj = new DataLayer();
                cmdSql1 = datalayerObj.Update(cmdSql1, connString);

                //second command
                SqlCommand cmdSql2 = new SqlCommand();
                cmdSql2.CommandType = CommandType.StoredProcedure;
                cmdSql2.CommandText = "usp_UpdateQueryStatus";

                cmdSql2.Parameters.Add(pStatus);

                datalayerObj = new DataLayer();
                cmdSql2 = datalayerObj.Update(cmdSql2, connString);
            }

            else
            {
                throw new Exception(errorMessage);
            }
            return adviceObj;
        }