Exemplo n.º 1
1
        public static void Main()
        {
            var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
            connection.Open();
            var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);

            var categoriesAndProducts = new Dictionary<string, List<string>>();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var category = (string)reader["CategoryName"];
                var product = (string)reader["ProductName"];
                if(categoriesAndProducts.ContainsKey(category))
                {
                    categoriesAndProducts[category].Add(product);
                }
                else
                {
                    categoriesAndProducts.Add(category, new List<string>());
                }
            }

            connection.Close();

            foreach (var kvp in categoriesAndProducts)
            {
                Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));

                foreach (var product in kvp.Value)
                {
                    Console.WriteLine("-- " + product);
                }
            }
        }
Exemplo n.º 2
1
        public void GetScheduledAppointments()
        {
            var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");



            string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
                             FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
                                  JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
                                  JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
                                  JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
                            WHERE e.eventOwner = 'mgeary'
                            ORDER BY e.eventID";
            using (var command = new SqlCommand(sql, con))
            {
                con.Open();
                using (var reader = command.ExecuteReader())
                {
                    var list = new List<ScheduledAppointment>();
                    while (reader.Read())
                        list.Add(new ScheduledAppointment
                        {
                            eventID = reader.GetInt32(0),
                            eventName = reader.GetString(1),
                            eventLocation = reader.GetString(2),
                            eventDate = reader.GetDateTime(3),
                            eventDuration = (float)reader.GetDouble(4),
                            eventUserName = reader.GetString(5)
                        });
                    allAppointments = list.ToArray();
                }
            }
        }
Exemplo n.º 3
1
        static void Main(string[] args)
        {
            Console.Write("Enter some text to search for it in the Products: ");
            var searchedProduct = Console.ReadLine();

            SqlConnection dbCon = new SqlConnection("Server=localhost; " +
                "Database=NORTHWND; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand(
                    "SELECT ProductName FROM Products " +
                    "WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);

                cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);

                SqlDataReader reader = cmd.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];
                        Console.WriteLine(productName);
                    }
                }
            }
        }
        private void ReadOrderData(string filterPrimkey, string connectionString, int rowIndex)
        {
            var queryString =
              "SELECT numcode, charcode, nominal, name, value FROM dbo.values_history inner join dbo.description on  dbo.description.id = dbo.values_history.id WHERE dbo.values_history.primkey ='" + filterPrimkey + "' ;";

            try
            {
                using (var connection =
                      new SqlConnection(connectionString))
                {
                    var command =
                        new SqlCommand(queryString, connection);
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        WriteCurrency(reader, rowIndex);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Text = ex.Message;
            }
        }
Exemplo n.º 5
1
        private static ICollection<string> FindProductBy(string partOfName)
        {
            var names = new List<string>();
            string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";

            var dbCon = new SqlConnection(connectionString);

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);

                command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");

                SqlDataReader reader = command.ExecuteReader();

                Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
                using (reader)
                {
                    string categoryName = string.Empty;
                    string currentCategoryName = string.Empty;
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];

                        names.Add(productName);
                    }
                }
            }

            return names;
        }
Exemplo n.º 6
1
        public RootObjectOut GetMessageByUser(UserIn jm)
        {
            RootObjectOut output = new RootObjectOut();
            String jsonString = "";
            try
            {
                String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                SqlConnection Connection = new SqlConnection(strConnection);
                String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
                SqlCommand Command = new SqlCommand(strSQL, Connection);
                Connection.Open();
                SqlDataReader Dr;
                Dr = Command.ExecuteReader();
                if (Dr.HasRows)
                {
                    if (Dr.Read())
                    {
                        jsonString = Dr.GetValue(0).ToString();
                    }
                }
                Dr.Close();
                Connection.Close();
            }
            catch (Exception ex)
            {
                output.errorMessage = ex.Message;
            }
            finally
            {
            }
            JavaScriptSerializer ser = new JavaScriptSerializer();
            output = ser.Deserialize<RootObjectOut>(jsonString);

            return output;
        }
        private void DataPortal_Fetch(CategoryCriteria criteria)
        {
            bool cancel = false;
            OnFetching(criteria, ref cancel);
            if (cancel) return;

            string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
                    using(var reader = new SafeDataReader(command.ExecuteReader()))
                    {
                        if (reader.Read())
                            Map(reader);
                        else
                            throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
                    }
                }
            }

            OnFetched();
        }
Exemplo n.º 8
1
 public List<Model.Beneficios> Select()
 {
     List<Model.Beneficios> lstBeneficios = new List<Model.Beneficios>();
     SqlConnection conexao = new SqlConnection(strCon);
     string sql = "Select * from Beneficios";
     SqlCommand cmd = new SqlCommand(sql, conexao);
     conexao.Open();
     try
     {
         SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         while (reader.Read())
         {
             Model.Beneficios beneficios = new Model.Beneficios();
             beneficios.id = Convert.ToInt32(reader["id"].ToString());
             beneficios.descr = Convert.ToString(reader["descr"].ToString());
             lstBeneficios.Add(beneficios);
         }
     }
     catch
     {
         Console.WriteLine("Deu erro na Seleção de Beneficios...");
     }
     finally
     {
         conexao.Close();
     }
     return lstBeneficios;
 }
Exemplo n.º 9
0
        public void mandarMensajeDeExito()
        {
            SqlCommand cmd = new SqlCommand("select * from THE_CVENGERS.COMPRA where COMPRA_ID = "+ idComp, Conexion.getConexion());
            SqlDataReader rd = cmd.ExecuteReader();
            rd.Read();
               bool sw = bool.Parse(rd["COMPRA_FORMA_DE_PAGO"].ToString());
            rd.Close();
            float monto = 0f;
             foreach (int it in checkedListBox1.CheckedIndices) { monto += float.Parse(((ItemsDevolucion)checkedListBox1.Items[it]).getPrecio()); }

            String mens = "La devolucion se ha realizado con exito. El monto de $"+ monto.ToString();
            if (!sw)
                mens += " sera retribuido en efectivo.";
            else
            {
                cmd.CommandText = "select THE_CVENGERS.tipoTarjetaCompra(" + idComp + ") 't'";
                rd = cmd.ExecuteReader();
                rd.Read();
                String tipoT = rd["t"].ToString();
                rd.Close();
                cmd.CommandText = "select THE_CVENGERS.numeroTarjetaCompra(" + idComp + ") 'n'";
                rd = cmd.ExecuteReader();
                rd.Read();
                String numT = rd["n"].ToString();
                rd.Close();
                mens += " sera retribuido a la tarjeta "+tipoT+" de numero "+numT;
            }
            MessageBox.Show(mens,"Información",MessageBoxButtons.OK);
        }
Exemplo n.º 10
0
		private void Page_Load(object sender, System.EventArgs e)
		{
			string strSQLQuery;
			SqlConnection adoSqlConn =new SqlConnection("data source=blr-ec-112844;initial catalog=master;password=sa;persist security info=True;user id=sa;");
			SqlCommand sqlcmdCommand = new SqlCommand("SELECT count(*) FROM testlang WHERE userfname=N'" + Request.Form["txtFName"] + "' AND userlname=N'" + Request.Form["txtLName"] + "'", adoSqlConn);
			SqlDataReader adosqlDataReader;
	        adoSqlConn.Open();
			sqlcmdCommand.CommandText = "SELECT count(*) FROM testlang WHERE userfname=N'" + Request.Form["txtFName"] + "' AND userlname=N'" + Request.Form["txtLName"] + "'";
			adosqlDataReader = sqlcmdCommand.ExecuteReader();
			adosqlDataReader.Read();

			if (adosqlDataReader.Read()==false)
			{
				adosqlDataReader.Close();
				Response.Write("<B>Thank You for Registering</B>");
				//Format the SQL Query to Insert Data.
				strSQLQuery = "INSERT INTO testlang (userfname, userlname, userlangid,useraddress) VALUES(N'" + Request.Form["txtFName"] + "', N'" + Request.Form["txtLName"] + "','" + Request.QueryString["lang"] + "', N'" + Request.Form["txtAddress"] + "')";
					//Insert the New Record.
					sqlcmdCommand.CommandText = strSQLQuery;
				sqlcmdCommand.ExecuteNonQuery();
			}
			else
				Response.Write("<B>You have already registered</B>");

        adosqlDataReader.Close();

        //Format the SQL Query to Get Data.
        strSQLQuery = "SELECT UID, userfname, userlname, userlangid,useraddress FROM testlang WHERE userlangid='" + Request.QueryString["lang"] + "'";
        //Response.Write(strSQLQuery)

        //Get All the records satisfying the LangID.
        sqlcmdCommand.CommandText = strSQLQuery;
        adosqlDataReader = sqlcmdCommand.ExecuteReader();
		}
Exemplo n.º 11
0
        private void button2_Click(object sender, EventArgs e)
        {
            SqlCommand sqlCmd = new SqlCommand("select * from THE_CVENGERS.ROL where ROL_NOMBRE ='" + rol + "'", Conexion.getConexion());
            SqlDataReader sqlReader = sqlCmd.ExecuteReader();
            sqlReader.Read();
            String idRol = sqlReader["ROL_ID"].ToString();
            sqlReader.Close();
            sqlCmd.CommandText = "select * from THE_CVENGERS.FUNCIONALIDAD where FUNC_NOMBRE ='" + listBox2.SelectedItem.ToString() + "'";
            sqlReader = sqlCmd.ExecuteReader();
            sqlReader.Read();
            String idFuncion = sqlReader["FUNC_ID"].ToString();
            sqlReader.Close();
            //hacer el insert
            sqlCmd.CommandText = "insert into THE_CVENGERS.FUNCIONXROL(FXR_ROL_ID,FXR_FUNC_ID) values (" + idRol + "," + idFuncion + ")";
            try
            {
                sqlCmd.ExecuteNonQuery();
                listBox1.Items.Add(listBox2.SelectedItem.ToString());
                int x = listBox2.SelectedIndex;
                listBox2.Items.RemoveAt(x);

                button2.Enabled = false;
                listBox2.Refresh();
                listBox1.Refresh();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK); }
        }
Exemplo n.º 12
0
        /// <summary>
        /// Return the song with a given id
        /// </summary>
        /// <param name="songId">The id of the song to get</param>
        /// <returns>The song with the given id, or null if no such movie exists</returns>
        public Song GetSong(int songId) {
            SqlCommand command = new SqlCommand("SELECT * FROM Song WHERE id =" + songId, connection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.Read()) {
                string album = reader["album"].ToString();
                reader.Close();
                command.CommandText = "SELECT * FROM Files WHERE id =" + songId;
                reader = command.ExecuteReader();
                reader.Read();
                Song song = new Song() {
                    Id = songId,
                    Album = album,
                    RentPrice = int.Parse(reader["rentPrice"].ToString()),
                    BuyPrice = int.Parse(reader["buyPrice"].ToString()),
                    Uri = reader["URI"].ToString(),
                    Title = reader["title"].ToString(),
                    Description = reader["description"].ToString(),
                    Year = short.Parse(reader["year"].ToString()),
                    CoverUri = reader["coverURI"].ToString(),
                    ViewCount = int.Parse(reader["viewCount"].ToString())
                };
                reader.Close();
                return song;

            }
            reader.Close();
            return null;
        }
Exemplo n.º 13
0
        private void button1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            comboBox1.Items.Clear();

            String strcon = "Data Source=VINOTH;Integrated Security=SSPI;Initial Catalog=Dafesty";
            SqlConnection cn = new SqlConnection(strcon);
            SqlCommand cm = new SqlCommand();

            cm.CommandText = "select movietitle from movies order by movietitle";
            cm.Connection = cn;
            cn.Open();
            SqlDataReader rd = cm.ExecuteReader();
            int count = 0;
            while (rd.Read())
            {
                listBox1.Items.Add(rd[0].ToString());
                count++;
            }
            label3.Text = count.ToString() + " Row(s) Selected";
            cm.CommandText = "select distinct Rating from Movies ";
            rd.Close();
            rd = cm.ExecuteReader();
            while(rd.Read())
                comboBox1.Items.Add(rd[0]);
            rd.Close();
            cn.Close();
        }
Exemplo n.º 14
0
    public static void getBook(int OrgID, ref string[] fields, ref int[] fieldNums, ref string[,] books)
    {
        int ObjID = 0;
        int counter = 0;

        SqlDataReader read;
        SqlCommand cmd = new SqlCommand();
        string e4Conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        SqlConnection conn = new SqlConnection(e4Conn);

        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Objects WHERE OrgID = '" + OrgID + "' AND ObjName = 'Book'";
        read = cmd.ExecuteReader();
        if (read.HasRows)
        {
            read.Read();
            try
            {
                ObjID = System.Convert.ToInt32(read["ObjID"]);
                read.Close();
                cmd.CommandText = "Select * FROM Fields WHERE ObjID = '" + ObjID + "'";
                read = cmd.ExecuteReader();
                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        fields[counter] = System.Convert.ToString(read["FieldName"]);
                        fieldNums[counter] = System.Convert.ToInt32(read["FieldNumber"]);
                        counter++;
                    }
                    read.Close();
                    cmd.CommandText = "SELECT * FROM Data WHERE ObjID = '" + ObjID + "'";
                    read = cmd.ExecuteReader();
                    if (read.HasRows)
                    {
                        counter = 0;
                        while (read.Read())
                        {
                            books[counter, 0] = System.Convert.ToString(read["Value00"]);
                            books[counter, 1] = System.Convert.ToString(read["Value01"]);
                            books[counter, 2] = System.Convert.ToString(read["Value02"]);
                            books[counter, 3] = System.Convert.ToString(read["Value03"]);
                            books[counter, 4] = System.Convert.ToString(read["Value04"]);
                            books[counter, 5] = System.Convert.ToString(read["Value05"]);
                            books[counter, 6] = System.Convert.ToString(read["Value06"]);
                            books[counter, 7] = System.Convert.ToString(read["Value07"]);
                            books[counter, 8] = System.Convert.ToString(read["Value08"]);
                            books[counter, 9] = System.Convert.ToString(read["Value09"]);
                            books[counter, 10] = System.Convert.ToString(read["Value10"]);
                            counter++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }
    }
Exemplo n.º 15
0
        /// <summary>
        /// Return the movie with a given id
        /// </summary>
        /// <param name="movieId">The id of the movie to get</param>
        /// <returns>The movie with the given id, or null if no such movie exists</returns>
        public Movie GetMovie(int movieId) {
            SqlCommand command = new SqlCommand("SELECT * FROM Movie WHERE id =" + movieId, connection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.Read()) {
                reader.Close();
                command.CommandText = "SELECT * FROM Files WHERE id =" + movieId;
                reader = command.ExecuteReader();
                reader.Read();
                Movie mov = new Movie() {
                    Id = movieId,
                    RentPrice = int.Parse(reader["rentPrice"].ToString()),
                    BuyPrice = int.Parse(reader["buyPrice"].ToString()),
                    Uri = reader["URI"].ToString(),
                    Title = reader["title"].ToString(),
                    Description = reader["description"].ToString(),
                    Year = short.Parse(reader["year"].ToString()),
                    CoverUri = reader["coverURI"].ToString(),
                    ViewCount = int.Parse(reader["viewCount"].ToString())
                };
                reader.Close();
                return mov;

            }
            reader.Close();
            return null;
        }
Exemplo n.º 16
0
        /// <summary>
        /// весь процесс заполнения информации
        /// </summary>
        void BaseCreation()
        {
            using (SqlConnection con = new SqlConnection(App.GetConnectString()))
            {
                con.Open();
                //Имя пользователя
                SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Password WHERE ID={0}", App.curPnID), con);
                TBlName.Text = cmd.ExecuteScalar().ToString();

                //Всего очков
                cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0}", App.curPnID), con);
                TBlPAll.Text = "Всего: "+cmd.ExecuteScalar().ToString();

                //Очки месяц
                cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'))", App.curPnID), con);
                TBlPMnt.Text = App.MonthName[DateTime.Now.Month]+": " + cmd.ExecuteScalar().ToString();

                //Лучший/худший результат всего
                cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} GROUP BY p.Name", App.curPnID), con);
                TBlBSAll.Text = "Всего: "+FindMax(cmd.ExecuteReader());
                TBlWSAll.Text = "Всего: "+FindMin(cmd.ExecuteReader());

                //Лучший/худший результат - месяц
                cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01')) GROUP BY p.Name", App.curPnID), con);
                TBlBSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMax(cmd.ExecuteReader());
                TBlWSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMin(cmd.ExecuteReader());
            }
        }
        public static string[,] GetListChevaux(SqlConnection conn)
        {
            SqlCommand sql = new SqlCommand("select id, nom, description, emplacement, race, discipline, idusager from cheval ");
            sql.Connection = conn;
            conn.Open();

            SqlDataReader sqlDR = sql.ExecuteReader();
            int nombre = 0;
            while (sqlDR.Read())
            {
                nombre++;
            }
            string[,] Tab = new string[nombre, 7];
            sqlDR.Close();
            if (nombre > 0)
            {
                SqlDataReader sqlDR2 = sql.ExecuteReader();
                int cpt = 0;
                while (sqlDR2.Read())
                {
                    Tab[cpt, 0] = sqlDR2.GetInt32(0).ToString();
                    Tab[cpt, 1] = sqlDR2.GetString(1);
                    Tab[cpt, 2] = sqlDR2.GetString(2);
                    Tab[cpt, 3] = sqlDR2.GetString(3);
                    Tab[cpt, 4] = sqlDR2.GetString(4);
                    Tab[cpt, 5] = sqlDR2.GetString(5);
                    Tab[cpt, 6] = sqlDR2.GetInt32(6).ToString();
                    cpt++;
                }
                sqlDR2.Close();
            }
            conn.Close();

            return Tab;
        }
Exemplo n.º 18
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 0;
        MultiView2.ActiveViewIndex = 0;
        if (ViewState["getdata"] == null)
        {
            SqlConnection conn = new SqlConnection(constrUDB);
            string query = "select * from usertable  where uname='" + Session["name"].ToString() + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = query;
            cmd.Connection = conn;
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                lblname.Text = dr["fname"].ToString();
                lblabout.Text = dr["about"].ToString();
                lblun.Text = dr["uname"].ToString();
                lblabtname.Text = (dr["fname"].ToString()).Split(' ')[0];

               // Label1.Text = (dr["fname"].ToString()).Split(' ')[0];
                ImageButton1.ImageUrl = dr["picurl"].ToString();
               /*
                Image1.ImageUrl = dr["b1imgurl"].ToString();
                Image2.ImageUrl = dr["b2imgurl"].ToString();
                Image3.ImageUrl = dr["b3imgurl"].ToString();
                Image4.ImageUrl = dr["b4imgurl"].ToString();
                Image5.ImageUrl = dr["b5imgurl"].ToString();
                lblbookname.Text = dr["b1name"].ToString();
                lblbookname0.Text = dr["b2name"].ToString();
                lblbookname1.Text = dr["b3name"].ToString();
                lblbookname2.Text = dr["b4name"].ToString();
                lblbookname3.Text = dr["b5name"].ToString();
                lblbabt.Text = dr["b1abt"].ToString();
                lblbabt0.Text = dr["b2abt"].ToString();
                lblbabt1.Text = dr["b3abt"].ToString();
                lblbabt2.Text = dr["b4abt"].ToString();
                lblbabt3.Text = dr["b5abt"].ToString();*/
                dr.Close();
                //make connection only if required
                SqlConnection conn1 = new SqlConnection(constrBDB);
                cmd.Connection = conn1;
                cmd.CommandText = "select * from bookrecord  where uname='" + Session["name"].ToString() + "'";
                conn1.Open();
                SqlDataReader dr1 = cmd.ExecuteReader();
                GridView3.DataSource = dr1;
                GridView3.DataBind();
                conn1.Close();
               }
            else
            {
                lblname.Text = "NO RECORD FOUND!";
                lblabout.Text = Session["name"].ToString();
            }
            conn.Close();
            ViewState["getdata"] = "I Have Done It Already";
        }
    }
Exemplo n.º 19
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        string status = "";
        if (Button1.Text == "Submit Instant Request")
        {
            status = "Allocated";
        }

        if (Button1.Text == "Submit Request")
        {
            status = "Pending";
        }

        string insertstring = "INSERT INTO Request (ModuleID, RoundID, UserID, NoStudents, Status, NoRooms) VALUES('" + ListBoxModCodes.SelectedItem.Text + "'," + DropDownList3.SelectedValue + ",@UserID," + TextBoxStudents.Text + ",'" + status + "',1) ";
        InsertNewBooking.InsertCommand = insertstring;
        InsertNewBooking.Insert();
        System.Data.SqlClient.SqlConnection MyConnection;
        System.Data.SqlClient.SqlCommand MyCommand;
        System.Data.SqlClient.SqlDataReader MyDataReader;
        MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
        MyConnection.Open();
        MyCommand = new System.Data.SqlClient.SqlCommand();
        MyCommand.Connection = MyConnection;
        MyCommand.CommandText = "SELECT RequestID FROM REQUEST";
        MyDataReader = MyCommand.ExecuteReader();
        string reqID = "";
        while (MyDataReader.Read())
        {
            reqID = MyDataReader.GetSqlValue(0).ToString();
        }
        MyDataReader.Close();
        MyDataReader = null;

                MyCommand.CommandText = "INSERT INTO RequestRoom (RequestID, RoomID) VALUES('" + reqID + "', '" + Label2.Text + "')";
                MyDataReader = MyCommand.ExecuteReader();
                MyDataReader.Close();
                MyDataReader = null;

                for (int z = 0; z < CheckBoxList1.Items.Count; z++)
                {
                    if (CheckBoxList1.Items[z].Selected == true)
                    {
                        for (int b = 0; b < CheckBoxList2.Items.Count; b++)
                        {
                            if (CheckBoxList2.Items[b].Selected == true)
                            {
                                MyCommand.CommandText = "INSERT INTO RequestTime (RequestID, Week, TimeID) VALUES(" + reqID + ", " + (z + 1) + ", " + b + ") ";
                                MyDataReader = MyCommand.ExecuteReader();
                                MyDataReader.Close();
                                MyDataReader = null;
                            }
                        }
                    }
                }
        MyCommand = null;
        MyConnection.Close();
        MyConnection = null;
        Response.Redirect("~/Secure/booked.aspx?"+reqID);
    }
Exemplo n.º 20
0
 protected void Page_Load(object sender, EventArgs e)
 {
     if ((Session["SCSLogin"] != null) & (Session["SCSDate"] != null))
     {
         if ((DateTime)Session["SCSDate"] <= DateTime.Now)
             Response.Redirect("Default.aspx");
         login = (string)Session["SCSLogin"];
         expDate = ((DateTime)Session["SCSDate"]).ToString();
         Response.Write("<div align=center> В системе как: " + login + "<BR> В системе до: " + expDate + "<BR></div>");
         Session["SCSDate"] = DateTime.Now.AddMinutes(10);
         Title = Title + " : " + login;
         /*if (Session["SCSReportDate1Flag"] == null)
         {
             Session["SCSReportDate1Flag"] = "False";
         }
         if (Session["SCSReportDate2Flag"] == null)
         {
             Session["SCSReportDate2Flag"] = "False";
         }/**/
         string connectionstring = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
         SqlConnection con = new SqlConnection(connectionstring);
         try
         {
             con.Open();
             SqlCommand cmd = new SqlCommand("", con);
             cmd.CommandText = "exec getUserAccess @lg";
             cmd.Parameters.Add("@lg", login);
             int access = (int)cmd.ExecuteScalar();
             if (access <= 1)
             {
                 Response.Redirect("Cabinet.aspx");
             }
             cmd.CommandText = "exec ShowFormReport @dtst, @dten, @lg";
             cmd.Parameters.Add("@dtst", (DateTime)Session["SCSReportDate1"]);
             cmd.Parameters.Add("@dten", (DateTime)Session["SCSReportDate2"]);
             SqlDataReader dr = cmd.ExecuteReader();
             gvVals.DataSource = dr;
             gvVals.DataBind();
             dr.Close();
             cmd.CommandText = "exec ShowFormReportOut @dtst, @dten, @lg";
             dr = cmd.ExecuteReader();
             gvOut.DataSource = dr;
             gvOut.DataBind();
             dr.Close();
         }
         catch (Exception err)
         {
             Response.Write(err.Message);
         }
         finally
         {
             con.Close();
         }
     }
     else Response.Redirect("Default.aspx");
 }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True");
        con.Open();
        string sel = DropDownList1.SelectedValue.Trim();

        SqlDataReader read;
        string s = "select * from factdata where uname = '" + TextBox1.Text + "'";
        SqlCommand cmd = new SqlCommand(s, con);
        read = cmd.ExecuteReader();
        string s1, s2;
        Label1.Visible = false;
        Label2.Visible = false;
        if (read.Read())
        {
            //    Label1.Text = read["uname"].ToString();
            s1 = read["sq_que"].ToString();
            s2 = read["sq_ans"].ToString();
            //    TextBox6.Text = read["city"].ToString();
            //    TextBox8.Text = read["pcode"].ToString();
            //    TextBox10.Text = read["country"].ToString();

            if (s1 != DropDownList1.SelectedValue.Trim())
            {
                Label1.Text = "Incorrect Security Question!";
                Label1.Visible = true;
                con.Close();
                read.Close();
                return;
            }
            if (s2 != TextBox3.Text)
            {
                Label2.Text = "Incorrect Answer for selected security Question!";
                Label2.Visible = true;
                con.Close();
                read.Close();
                return;
            }
            read.Close();
            if (s1 == DropDownList1.SelectedValue.Trim() && s2 == TextBox3.Text)
            {

                string str = "select password from factdata where uname = '" + TextBox1.Text + "'";
                SqlCommand cmnd = new SqlCommand(s, con);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    Label3.Text = "Your Password is : ";
                    LabelP.Text = read["password"].ToString();

                }
             //   Response.Redirect("~/SetPass.aspx",true);
            }
        }
    }
 public static string ExibeDadosVenda()
 {
     List<ItenVenda> itensDaVenda = new List<ItenVenda>();
     using (SqlConnection sqlConn =
                        new SqlConnection(ConnString))
     {
         using (SqlCommand sqlCommand = new SqlCommand())
         {
             string dadosVenda;
             sqlConn.Open();
             sqlCommand.CommandText = "select * from vendas order by Id desc";
             sqlCommand.Connection = sqlConn;
             SqlDataReader dataReader;
             dataReader = sqlCommand.ExecuteReader();
             if (dataReader.Read())
             {
                 string idVenda = dataReader[0].ToString();
                 float valorDaVenda = float.Parse(dataReader[1].ToString());
                 int codOperador = int.Parse(dataReader[2].ToString());
                 dataReader.Close();
                 sqlCommand.Parameters.AddWithValue("idVenda", int.Parse(idVenda));
                 sqlCommand.CommandText = "Select * from itemVenda where IdVenda = @idVenda";
                 SqlDataReader dr;
                 dr = sqlCommand.ExecuteReader();
                 while(dr.HasRows)
                 {
                     while (dr.Read())
                     {
                         Produto p = ProdutoRepositoryIPML.BuscaProduto(int.Parse(dr[4].ToString()));
                         ItenVenda item = new ItenVenda(p, int.Parse(dr[2].ToString()));
                         itensDaVenda.Add(item);
                     }
                     dr.NextResult();
                 }
                 dr.Close();
                 dadosVenda = "Código da Venda: " + idVenda;
                 dadosVenda += "\nVendedor: " + codOperador;
                 dadosVenda += "\n\nProdutos vendidos: \n";
                 foreach (ItenVenda item in itensDaVenda)
                 {
                     dadosVenda += "\nProduto: " + item.Produto.NomeProduto;
                     dadosVenda += "\nPreço Unitário: " + item.Produto.PrecoVenda;
                     dadosVenda += "\nQuantidade Comprada: " + item.Qtd+ "\n";
                 }
                 dadosVenda += "\n\n Valor total da Venda: " + valorDaVenda;
             }
             else
             {
                 return "Nenhuma venda encontrada";
             }
             return dadosVenda;
         }
     }
 }
Exemplo n.º 23
0
        public Rendeles(int pAsztalID, int pRendeles_id)
        {
            _AsztalId = pAsztalID;
            setColumnModel();
            setColumnModelSum();
            _ScrollPos = 0;
            fRENDELES_ID = pRendeles_id;
            fDATUM = DateTime.Now;
            fFIZETVE = false;
            fASZTAL_ID = pAsztalID;
            fKEDVEZMENY = 0;

            SqlConnection sc = new SqlConnection(DEFS.ConSTR);
            sc.Open();

            if (pRendeles_id != -1)
            {
                // meglevő rendelés be kell tölteni az ID-ra a rendelést.
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = sc;

                cmd.CommandType = CommandType.Text;

                cmd.CommandText = "SELECT ASZTAL_ID, isnull(PARTNER_ID,-1) PARTNER_ID, DATUM, FIZETVE, isnull(KEDVEZMENY,0) as KEDVEZMENY FROM RENDELES_FEJ WHERE RENDELES_ID =" + pRendeles_id.ToString();

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    fASZTAL_ID = (int)rdr["ASZTAL_ID"];
                    fPARTNER_ID = (int)rdr["PARTNER_ID"];
                    if (fPARTNER_ID > 0) { usedPartner = new Partner(fPARTNER_ID); }
                    fDATUM = (DateTime)rdr["DATUM"];
                    fFIZETVE = (1 == (int)rdr["FIZETVE"]);
                    fKEDVEZMENY = (double)rdr["KEDVEZMENY"];
                }
                rdr.Close();

                cmd.CommandType = CommandType.Text;

                cmd.CommandText = "SELECT SOR_ID FROM RENDELES_SOR WHERE isnull(DELETED,0) = 0 AND isnull(FIZETVE,0) = 0 AND RENDELES_ID =" + pRendeles_id.ToString();

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    lRendelesSor.Add(new RendelesSor((int)rdr["SOR_ID"], new SqlConnection(DEFS.ConSTR), false));

                }
                rdr.Close();

            }
            sc.Close();
        }
Exemplo n.º 24
0
        //初始化信息
        protected void BindData()
        {
            try
            {
                int courseID = Int32.Parse(Request.QueryString["courseID"].ToString());
                List<String> allTeachers = new List<string>();

                string queryCourse = "select name, startdate, enddate from Course where id=" + courseID;
                string queryTeachers = "select name from Teacher";
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnStringSTEduSys"].ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(queryCourse, connection);
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        oldCourseName.Value = reader[0].ToString();
                        tbCourseName.Text = reader[0].ToString();
                        tbStartDate.Text = reader[1].ToString();
                        tbEndDate.Text = reader[2].ToString();
                    }
                    reader.Close();

                    command.CommandText = queryTeachers;
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        allTeachers.Add(reader[0].ToString());
                    }

                    // call close when done reading.
                    reader.Close();
                }

                String temp = Request.QueryString["teachers"].ToString();
                String[] teachers = temp.Trim("'".ToCharArray()).Split('/');
                if (teachers.Length > 1)
                {
                    tbTeacher2.Text = teachers[1];
                }
                tbTeacher1.Text = teachers[0];
                for (int i = 0; i < allTeachers.Count; i++)
                {
                    ddlTeacher1.Items.Add(allTeachers[i]);
                    ddlTeacher2.Items.Add(allTeachers[i]);
                }
            }
            catch (Exception e)
            {
                Response.Redirect("./Course_Manage.aspx");
            }
        }
Exemplo n.º 25
0
    public void getItems(string orderID, int OrgID, ref int[] items, ref int[] itemTypes, ref string[] inStock)
    {
        SqlDataReader read;
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(e4Conn);
        string itemList = "";
        string itemTypeList = "";
        string[] parseString;
        string[] parseTypes;

        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Data WHERE Value00 = '" + orderID + "' AND Name = 'Order' AND OrgID = '" + OrgID + "'";

        read = cmd.ExecuteReader();
        if (read.HasRows)
        {
            read.Read();
            try
            {
                itemList = System.Convert.ToString(read["Value05"]);
                itemTypeList = System.Convert.ToString(read["Value06"]);
                parseString = itemList.Split(',');
                parseTypes = itemTypeList.Split(',');
                for (int y = 0; y < parseString.Length; y++)
                {
                    items[y] = System.Convert.ToInt32(parseString[y]);
                    itemTypes[y] = System.Convert.ToInt32(parseTypes[y]);
                    read.Close();
                    cmd.CommandText = "SELECT * FROM Data WHERE OrgID = '" + OrgID + "' AND Value00 = '" + items[y] + "' AND ObjID = '" + itemTypes[y] + "'";
                    read = cmd.ExecuteReader();
                    if (read.HasRows)
                    {
                        read.Read();
                        if (System.Convert.ToInt32(read["Value06"]) > 0)
                        {
                            inStock[y] = "Yes";
                        }
                        else
                        {
                            inStock[y] = "No";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }
    }
    protected void MostrarSeleccao(Object obj, EventArgs e)
    {
        saida.Text = "";

        ddlMunicipios.Items.Clear();
        ddlMunicipios.Items.Insert(0, new ListItem("Seleccione...", "0"));

        //if(dDL.SelectedIndex!=0)
        //{
            //ddlMunicipios.DataSource = municipios[dDL.SelectedIndex-1];
            //ddlMunicipios.DataBind();

            SqlDB Bd = new SqlDB("ConStr_DivAdmin");
            string str = "SELECT NomeMunicipio from Municipios where [CodigoDistrito] = @distrito";

            SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);
            cmd.Parameters.AddWithValue("@distrito",dDL.SelectedItem.Value);
            cmd.Connection = Bd.SqlConDB;

            Bd.SqlConDB.Open();
            SqlDataReader dR = cmd.ExecuteReader();

            //efectuar o data binding
            ddlMunicipios.DataSource = dR;
            ddlMunicipios.DataTextField = "NomeMunicipio";
            ddlMunicipios.DataValueField = "IdMunicipio";
            ddlMunicipios.DataBind();
            dR.Close();
            Bd.SqlConDB.Close();
        //}
    }
Exemplo n.º 27
0
        public List<ChatMessageModel> GetMessagesLimit(int limit)
        {
            List<ChatMessageModel> msg = new List<ChatMessageModel>();

            if (limit < 0) limit = 0;
            using (SqlConnection conn = SqlConnectionService.GetConnection())
            {
                string query = String.Format("SELECT TOP {0} u.UserId, u.Sex, m.Message, m.Time, u.UserLogin FROM MessageLogs m JOIN Users u on u.UserId = m.UserId ORDER BY m.MessageId DESC ", limit);
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();

                    cmd.Parameters.AddWithValue("@Limit", limit);

                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        msg.Add(Helper.CreateChatMessage(reader["UserLogin"].ToString(), Convert.ToInt32(reader["UserId"]), reader["Message"].ToString(), Convert.ToInt32(reader["Sex"]), reader["Time"].ToString()));
                    }

                    conn.Close();

                    return msg;
                }
            }
        }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con);

        cmd.Parameters.AddWithValue("@username", Textname.Text);
        cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
        cmd.Parameters.AddWithValue("@comment", txtComment.Text);
        cmd.Parameters.AddWithValue("@postedate", DateTime.Now);
        con.Open();
        //SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con);
        cmd.ExecuteNonQuery();
        // con.Close();

        SqlCommand cmd1 = new SqlCommand("select no from [user] where username='******' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con);
        SqlDataReader dr = cmd1.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text = dr["no"].ToString();
        }

        Textname.Text = string.Empty;
        txtSubject.Text = string.Empty;
        txtComment.Text = string.Empty;
        BindRepeaterData();
    }
Exemplo n.º 29
0
    public building(int id)
    {
        try{
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mainconn"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * FROM building WHERE id = @id", conn);
            cmd.Parameters.AddWithValue("id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            ID = new dataObject(id);
            Name = new dataObject(dr["Name"].ToString());
            Address1 = new dataObject(dr["Address1"].ToString());
            Address2 = new dataObject(dr["Address2"].ToString());
            Address3 = new dataObject(dr["Address3"].ToString());
            City = new dataObject(dr["City"].ToString());
            State = new dataObject(dr["State"].ToString());
            ZipCode = new dataObject(dr["ZipCode"].ToString());
            PhoneNumber = new dataObject(dr["PhoneNumber"].ToString());
            PhoneNumberExtension = new dataObject(dr["PhoneNumberExtension"].ToString());
            FaxNumber = new dataObject(dr["FaxNumber"].ToString());
            MobilePhoneNumber = new dataObject(dr["MobilePhoneNumber"].ToString());
            EmailAddress = new dataObject(dr["EmailAddress"].ToString());
            Contact = new dataObject(dr["Contact"].ToString());
            Active = new dataObject(Convert.ToBoolean(dr["Active"]));
            Comment = new dataObject(dr["Comment"].ToString());
            InputDate = new dataObject(Convert.ToDateTime(dr["InputDate"]));
            InputEmploee = new dataObject(new employee(Convert.ToInt32(dr["InputEmployeeID"])));

        }catch(Exception e){
            throw e;
        }
    }
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);

        if (!this.IsPostBack)
        {
            //efectuar o data binding
            //dDL.DataSource = distritos;
            //dDL.DataBind();

            SqlDB Bd = new SqlDB("ConStr_DivAdmin");
            string str = "SELECT NomeDistrito from Distritos";

            SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);

            Bd.SqlConDB.Open();
            SqlDataReader dR = cmd.ExecuteReader();

            //efectuar o data binding
            dDL.DataSource = dR;
            dDL.DataTextField = "NomeDistrito";
            dDL.DataBind();
            dR.Close();
            Bd.SqlConDB.Close();
        }

        return;
    }
Exemplo n.º 31
0
        public List <BO.AssetInventoryTracking.workorder> GetAllworkorder()
        {
            List <BO.AssetInventoryTracking.workorder> xworkorderList = new List <BO.AssetInventoryTracking.workorder>();
            string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder]";

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
                    conn.Open();
                    using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                        while (reader.Read())
                        {
                            BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
                            if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
                            {
                                xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
                            {
                                xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
                            {
                                xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["status"], DBNull.Value))
                            {
                                xworkorder.status = reader["status"].ToString();
                            }
                            if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
                            {
                                xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
                            {
                                xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
                            }
                            xworkorderList.Add(xworkorder);
                        }
                    }
                }
            }
            return(xworkorderList);
        }
Exemplo n.º 32
0
        public void fillDDLStates()
        {
            using (System.Data.SqlClient.SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connProd"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand();
                Command.CommandText = "dbo.GetAllStates";
                Command.Connection  = Connection;

                Connection.Open();
                SqlDataReader ClientNameData = Command.ExecuteReader();
                if (ClientNameData.HasRows)
                {
                    ddlStates.DataSource     = ClientNameData;
                    ddlStates.DataTextField  = "descr";
                    ddlStates.DataValueField = "stateID";
                    ddlStates.DataBind();
                }
            }
        }
Exemplo n.º 33
0
 public SqlDataReader ExecuteQuery(string sql)
 {
     try
     {
         sqlConn.Close();
         sqlConn.Open();
         SqlCommand sqlCom = new System.Data.SqlClient.SqlCommand();
         sqlCom.Connection  = sqlConn;
         sqlCom.CommandType = CommandType.Text;
         sqlCom.CommandText = sql;
         SqlDataReader reader = sqlCom.ExecuteReader();
         sqlCom.Dispose();
         return(reader);
     }
     catch (Exception ex)
     {
     }
     return(null);
 }
Exemplo n.º 34
0
 public static System.Data.SqlClient.SqlDataReader ExecuteReader(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.SqlClient.SqlParameter[] commandParameters)
 {
     System.Data.SqlClient.SqlCommand    sqlCommand    = new System.Data.SqlClient.SqlCommand();
     System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
     System.Data.SqlClient.SqlDataReader result;
     try
     {
         SqlHelper.PrepareCommand(sqlCommand, sqlConnection, null, cmdType, cmdText, commandParameters);
         System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
         sqlCommand.Parameters.Clear();
         result = sqlDataReader;
     }
     catch
     {
         sqlConnection.Close();
         throw;
     }
     return(result);
 }
Exemplo n.º 35
0
    public bool VerifyEOBStatus(string strUsername, string strSourceId, string strCarrier)
    {
        bool ReturnVar = false;

        System.Data.SqlClient.SqlDataReader reader = null;
        try
        {
            System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("[usp_VerifyEOBStatus]", sql.SqlConnection);
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", strUsername));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SourceId", strSourceId));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Carrier", strCarrier));

            reader = sqlCommand.ExecuteReader();

            if (reader.Read())
            {
                if ((!object.ReferenceEquals(reader[0], System.DBNull.Value)))
                {
                    if (reader[0].ToString() == "1")
                    {
                        ReturnVar = true;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            if (!reader.IsClosed)
            {
                reader.Close();
            }

            dbError(ex.StackTrace, ex.Message);
        }

        if (!reader.IsClosed)
        {
            reader.Close();
        }

        return(ReturnVar);
    }
Exemplo n.º 36
0
        public Boolean yaExisteUser(String uName)
        {
            string        conec = @"Data Source=BOSTON-PC\SQL_SERVER_2008;Initial Catalog=Kanban;Integrated Security=True";
            SqlConnection cone  = new SqlConnection(conec);

            cone.Open();
            string        query  = "select * from USUARIOS where USUARIO= '" + uName + "'";
            SqlCommand    cmd    = new System.Data.SqlClient.SqlCommand(query, cone);
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                cone.Close();
                return(true);
            }

            cone.Close();
            return(false);
        }
Exemplo n.º 37
0
    //Populate the Subjects Gridview
    protected void Populate_SubjectDetails_Gridview()
    {
        Panel_SubjectDetails.Controls.Clear();
        PlaceHolder1.Visible         = false;
        Panel_SubjectDetails.Visible = true;

        try
        {
            SqlCommand sqlCmd_GetSubj = new System.Data.SqlClient.SqlCommand("exec spSEC_PIVOT__RowEQ_Subject__Dynamic_Where", oConn);
            DataTable  dt_GetSubj     = new DataTable();

            SqlDataReader SqlReader_GetSubj = sqlCmd_GetSubj.ExecuteReader();
            dt_GetSubj.Load(SqlReader_GetSubj);

            //SqlDataAdapter sqlAdapter_GetSubj = new SqlDataAdapter(sqlCmd_GetSubj);
            //sqlAdapter_GetSubj.Fill(dt_GetSubj);
            int num_subjects = dt_GetSubj.Rows.Count;


            //build the Gridview & columns
            GridView SubjectDetails_gridview = new GridView();
            SubjectDetails_gridview.ID = "SubjectDetails_gridview";
            SubjectDetails_gridview.AutoGenerateColumns = true;
            SubjectDetails_gridview.CssClass            = "tblinputsmall_blue";
            //SubjectDetails_gridview.RowDataBound += new GridViewRowEventHandler(SubjStatus_Totals_gridview_RowDataBound);


            //Bind the data
            SubjectDetails_gridview.DataSource = dt_GetSubj;
            SubjectDetails_gridview.DataBind();
            Panel_SubjectDetails.Controls.Add(SubjectDetails_gridview);
        }

        catch (SqlException oException)
        {
            //errorLink1.InnerHtml += oException.Message;  //new with full study

            foreach (SqlError oErr in oException.Errors)
            {
                tblmyinfo.Rows[0].Cells[0].InnerHtml += oErr.Message;
            }
        }
    }
Exemplo n.º 38
0
        } //========== end of the mtd

        public List <AccountCustomerModel> GetAccountCustomerInfo(string SearchValue)
        {
            SearchValue = SearchValue.Replace("FORWARDSLASHXTER", "/");
            SearchValue = SearchValue.Replace("DOTXTER", ".");

            List <AccountCustomerModel> accountcustomerInfo = new List <AccountCustomerModel>();

            using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                var cmd = new System.Data.SqlClient.SqlCommand("spp_acountnumbers_customernames", con);
                cmd.CommandType    = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                //cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;

                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = "searchvalue",
                    Value         = SearchValue,
                });

                con.Open();
                //cmd.ExecuteNonQuery();
                //cmd2.ExecuteNonQuery();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    AccountCustomerModel pObj = new AccountCustomerModel();

                    // progressInfo.TotalCount = reader["teambankid"] != DBNull.Value ? int.Parse(reader["teambankid"].ToString()) : 0;
                    pObj.AccountNumber = reader["AcccountNumber"] != DBNull.Value ? reader["AcccountNumber"].ToString() : "";
                    pObj.CustomerName  = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "";
                    //pObj.Status = reader["Status"] != DBNull.Value ? reader["Status"].ToString() : "";
                    //pObj.Status = pObj.Status.ToUpper().Trim();

                    accountcustomerInfo.Add(pObj);
                }
                con.Close();
            }
            return(accountcustomerInfo);
        } //========== end of the mtd
Exemplo n.º 39
0
    public void RunProcedure(string Name)
    {
        string connnecstring;

        connnecstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
        System.Data.SqlClient.SqlConnection PubsConn = new System.Data.SqlClient.SqlConnection(connnecstring);
        System.Data.SqlClient.SqlCommand    CMDproc  = new System.Data.SqlClient.SqlCommand();
        System.Data.SqlClient.SqlDataReader reader;
        CMDproc.CommandType = System.Data.CommandType.StoredProcedure;
        CMDproc.CommandText = Name;
        CMDproc.Connection  = PubsConn;
        PubsConn.Open();
        reader = CMDproc.ExecuteReader();
        PubsConn.Close();
        CMDproc.Dispose();
        reader.Close();
        reader.Dispose();
        PubsConn.Dispose();
    }
Exemplo n.º 40
0
        /// <summary>
        ///
        /// </summary>
        public void Load(System.Data.SqlClient.SqlConnection connection)
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            {
                cmd.Connection  = connection;
                cmd.CommandText = "SELECT ParentGroupID, AccountType, AccountID FROM SecurityRelation WHERE ";

                System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

                if (true == reader.Read())
                {
                    _parentGroupID = reader.GetString(0);
                    _accountType   = reader.GetString(1);
                    _accountID     = reader.GetString(2);
                }

                reader.Close();
            }
        }
Exemplo n.º 41
0
        /// <summary>
        ///
        /// </summary>
        public void Load(System.Data.SqlClient.SqlConnection connection)
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            {
                cmd.Connection  = connection;
                cmd.CommandText = "SELECT AccountID, SysModID, SysFuncID FROM SecurityUserAccount_FunctionRole WHERE ";

                System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

                if (true == reader.Read())
                {
                    _accountID = reader.GetString(0);
                    _sysModID  = reader.GetString(1);
                    _sysFuncID = reader.GetString(2);
                }

                reader.Close();
            }
        }
Exemplo n.º 42
0
        public bool tareaRepetida(string codigo)
        {
            comando = new System.Data.SqlClient.SqlCommand("select codigo from TareasGenericas where codigo=@codigo", conexion);
            comando.Parameters.AddWithValue("@codigo", codigo);
            SqlDataReader reader = comando.ExecuteReader();
            bool          rep;

            if (reader.Read())
            {
                rep = reader["codigo"].ToString() == codigo;
            }
            else
            {
                rep = false;
            }

            reader.Close();
            return(rep);
        }
        /// <summary>
        /// Read all MAX dates including NULL, ordering by not required inspection operations, in pipe test result
        /// </summary>
        /// <param name="number"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns>
        /// List of KeyValuePair contains: date of last inspection result and id of not required inspection operations
        /// </returns>
        public List <KeyValuePair <DateTime, Guid> > GetAllNotRequiredOperationResult()
        {
            CreateConnection();
            List <KeyValuePair <DateTime, Guid> > inspectionOperationsResult = new List <KeyValuePair <DateTime, Guid> >();

            try
            {
                using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
                {
                    connection.Open();
                    command.Connection = connection;

                    command.CommandText = String.Format(@"Select r.Date, t.id From PipeTest t
full join 
(Select Max(PipeTestResult.Date) date,PipeTestResult.pipeTestId testId 
From PipeTestResult PipeTestResult where  PipeTestResult.status not in('{0}')
 group by PipeTestResult.pipeTestId) r on r.testId=t.id where t.frequencyType ='{1}'
", PipeTestResultStatus.Scheduled.ToString(), InspectionFrequencyType.U.ToString());

                    SqlDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        inspectionOperationsResult.Add(new KeyValuePair <DateTime, Guid>(
                                                           dr[0] == System.DBNull.Value ? (DateTime)(new DateTime(1950, 6, 10, 15, 24, 16)) : (DateTime)dr[0],
                                                           (Guid)dr[1]
                                                           ));
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new RepositoryException("Get all not required operation", ex);
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            return(inspectionOperationsResult);
        }
Exemplo n.º 44
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //index = lstRewardsView.SelectedIndex;
        //lstRewardsView.SelectedIndex = index;

        if (Session["employeeLoggedIn"] == null)
        {
            Response.Redirect("Login.aspx"); //check that the filepath is correct
        }
        if (Session["employeeLoggedIn"].ToString() != "True")
        {
            Response.Redirect("Login.aspx"); //check that the filepath works
        }

        Employee user = (Employee)Session["user"];


        ////loop for it to only load this on the first time the page loads
        //ispostback
        //for (int i = 0; i < 1; i++)
        //{
        try
        {
            SqlConnection conn = ProjectDB.connectToDB();
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = conn;

            insert.CommandText        = "select concat([RewardID],' ',[Name],' ',[Description], ' ',[Price],' ',[StartDate]) AS search_RewardItems from [dbo].[Reward]";
            lstRewardsView.DataSource = insert.ExecuteReader();

            lstRewardsView.DataTextField = "search_RewardItems";
            lstRewardsView.DataBind();
            conn.Close();
        }

        //Shows an error message if there is a problem connecting to the database
        catch (Exception)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('ERROR')", true);
        }
        //}
    }
Exemplo n.º 45
0
        protected void Page_Load(object sender, System.EventArgs e)
        {
            btnBorrar.Enabled = false;
            btnBorrar.Visible = false;

            if (Session["loggeado"] == null)
            {
                Response.Redirect(System.Configuration.ConfigurationManager.AppSettings["urlRoot"].ToString() + "/ingreso.aspx", true);
            }
            if (Session["administrador"].ToString() == "False" && Session["escritura"].ToString() == "False")
            {
                Response.Write("<h2>No tiene suficientes permisos para ver esta p&aacute;gina.</h2>");
                Response.End();
            }
            if ((Session["editar_formato_id"] != null) && !IsPostBack)
            {
                btnBorrar.Enabled = true;
                btnBorrar.Visible = true;

                string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString;
                using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    string strQuery = "SELECT html, nombre FROM matricula.mailxls_formato WHERE id = " + Session["editar_formato_id"].ToString() + ";";
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strQuery, connection);
                    connection.Open();
                    System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
                    dr.Read();
                    html = dr[0].ToString();
                    if (html.Contains("\\'"))
                    {
                        html = html.Replace("\\'", "'");
                    }
                    if (html.Contains("\\\""))
                    {
                        html = html.Replace("\\\"", "\"");
                    }
                    elm1.Text     = Server.HtmlDecode(html);
                    TextBox1.Text = dr[1].ToString();
                    connection.Close();
                }
            }
        }
Exemplo n.º 46
0
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        //if (System.Web.Security.FormsAuthentication.Authenticate(txtUsername.Text, txtPassword.Text))
        //{
        //    System.Web.Security.FormsAuthentication.RedirectFromLoginPage(txtUsername.Text, false);
        //}
        //else
        //{
        //    lblError.Text = "Your username or password was incorrect.";
        //}

        System.Data.SqlClient.SqlConnection conn =
            new System.Data.SqlClient.SqlConnection();
        var conString = System.Configuration.ConfigurationManager.ConnectionStrings["GoDaddySQL"];

        conn.ConnectionString = conString.ConnectionString;

        conn.Open();
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
        comm.Connection = conn;

        string sql = "";

        sql = "select customerlogon from huber_customers where customerlogon = @customerlogon and customerpass = @customerpass";

        System.Data.SqlClient.SqlDataReader dr;
        comm.CommandText = sql;
        comm.Parameters.AddWithValue("@customerlogon", txtUsername.Text);
        comm.Parameters.AddWithValue("@customerpass", txtPassword.Text);

        dr = comm.ExecuteReader();

        if (dr.HasRows)
        {
            System.Web.Security.FormsAuthentication.SetAuthCookie(txtUsername.Text, false);
            Response.Redirect("default.aspx");
        }
        else
        {
            lblError.Text = "Your username or password was invalid.";
        }
    }
        /// <summary>
        /// Returns the supported countries.
        /// </summary>
        /// <param name="language">Code of the language that should be used.</param>
        /// <remarks>Shows how to call a stored procedure.</remarks>
        public Countries GetCountries(string language)
        {
            var result = new Countries();


            //First: A connection is needed
            using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\MyProjects\\Wifi-Kurs\\WIFI-Sisharp-Training\\wifi.sisharp.training.web\\App_Data\\AndritzHydro2019.mdf"))
            {
                //Second: A command is needed
                using (var command = new System.Data.SqlClient.SqlCommand("GetCountries", connection))
                {
                    //Configure the command
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@language", language);

                    //The Sql Server should cache the procedure...
                    command.Prepare();

                    //Don't forget:
                    connection.Open();

                    //Third: (Not needed with INSERT, UPDATE or DELETE: command.ExecuteNonQuery() is used)
                    // - only with SELECT
                    using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        //Map the data to the data transfer objects
                        while (reader.Read())
                        {
                            result.Add(new Country
                            {
                                Code        = reader["ISO"].ToString(),
                                Name        = reader["Name"].ToString(),
                                MaxNumber   = (int)reader["MaxNumber"],
                                NumberCount = (int)reader["NumberCount"]
                            });
                        }
                    }
                }
            }

            return(result);
        }
Exemplo n.º 48
0
        public BO.AssetInventoryTracking.workorder GetByIDworkorder(int workorderID)
        {
            BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
            string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder] WHERE workorderID=@workorderID";

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
                    cmd.Parameters.AddWithValue("@workorderID", workorderID);
                    conn.Open();
                    using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
                        if (reader.Read())
                        {
                            if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
                            {
                                xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
                            {
                                xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
                            {
                                xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["status"], DBNull.Value))
                            {
                                xworkorder.status = reader["status"].ToString();
                            }
                            if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
                            {
                                xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
                            }
                            if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
                            {
                                xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
                            }
                        }
                    }
                }
            }
            return(xworkorder);
        }
Exemplo n.º 49
0
    protected void BtnLogin_Click(object sender, EventArgs e)
    {
        bool   validate  = true;
        String adminUser = HttpUtility.HtmlEncode(txtAdminUser.Value);
        String adminPass = HttpUtility.HtmlEncode(txtAdminPass.Value);

        System.Data.SqlClient.SqlCommand adminLogin = new System.Data.SqlClient.SqlCommand();
        adminLogin.Connection = sc1;
        sc1.Open();
        adminLogin.CommandText = "SELECT * FROM ADMINACC WHERE Username = upper(@AdminUser)";
        adminLogin.Parameters.Add(new SqlParameter("@AdminUser", adminUser));
        System.Data.SqlClient.SqlDataReader adminReader = adminLogin.ExecuteReader();

        if (adminReader.HasRows)       // if the username exists, it will continue
        {
            while (adminReader.Read()) // this will read the single record that matches the entered username
            {
                String firstname = adminReader["firstname"].ToString();
                CurrentSession.Current.userEmail = adminUser;
                String adminPasstxt = adminReader["password"].ToString();

                if (PasswordHash.ValidatePassword(txtAdminPass.Value, adminPasstxt)) // if the entered password matches what is stored, it will show success
                {
                    CurrentSession.Current.AdminUser = adminUser;

                    Response.Write("<script>alert('Success!')</script>");

                    Response.Redirect("admin.aspx");
                }
                else
                {
                    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('Sign in failed! Wrong password.');", true);
                    validate = false;
                }
            }
            sc1.Close();
        }
        else
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", " alert('Admin user doesn't exist.');", true);
        }
    }
Exemplo n.º 50
0
 private void cbInitialCatalog_DropDown(object sender, System.EventArgs e)
 {
     using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()))
     {
         this.toolStripStatusLabel1.Text = "Установка подключения...";
         System.Windows.Forms.Application.DoEvents();
         try
         {
             using (sqlConnection)
             {
                 sqlConnection.Open();
                 string cmdText = "SELECT name FROM sys.databases; ";
                 using (System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(cmdText, sqlConnection))
                 {
                     System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                     this.cbInitialCatalog.Items.Clear();
                     using (sqlDataReader)
                     {
                         while (sqlDataReader.Read())
                         {
                             this.cbInitialCatalog.Items.Add(sqlDataReader.GetString(0));
                         }
                     }
                 }
             }
             this.toolStripStatusLabel1.Text = "Подключение установлено";
             System.Windows.Forms.Application.DoEvents();
         }
         catch (System.Exception ex)
         {
             this.toolStripStatusLabel1.Text = "";
             new ExceptionMessageBox(ex)
             {
                 Caption        = "Ошибка",
                 InnerException = ex.InnerException,
                 Buttons        = ExceptionMessageBoxButtons.OK,
                 Symbol         = ExceptionMessageBoxSymbol.Error
             }.Show(this);
             base.DialogResult = System.Windows.Forms.DialogResult.None;
         }
     }
 }
Exemplo n.º 51
0
    protected void Populate_GridView_DataDict()
    {
        if (debugprint)
        {
            tblmyinfo.Rows[0].Cells[0].InnerHtml += "<br/>---> Populate_GridView_DataDict <br/>";
        }

        try
        {
            string mycmd = "exec spDataDict_by_measureID " + DDL_SelectMeasureID.SelectedValue.ToString();

            if (debugprint)
            {
                tblmyinfo.Rows[0].Cells[0].InnerHtml += "=Pop GridView=" + mycmd + ".<br/>";
            }

            SqlCommand    sqlCmd3    = new System.Data.SqlClient.SqlCommand(mycmd, oConn);
            SqlDataReader sqlReader3 = sqlCmd3.ExecuteReader();
            DataTable     dt3        = new DataTable();
            dt3.Load(sqlReader3);

            GridView_DataDict.DataSource = dt3;
            GridView_DataDict.CssClass   = "DataDictCell";
            GridView_DataDict.DataBind();

            lblMeasName_ContentPage.Text = measurename;

            GridView_DataDict.Visible = true;

            lblINTRO_measname.Visible       = true;
            lblMeasName_ContentPage.Visible = true;
        }


        catch (SqlException oException)
        {
            foreach (SqlError oErr in oException.Errors)
            {
                tblmyinfo.Rows[0].Cells[0].InnerHtml += oErr.Message;
            }
        }
    }
        public ActionResult StudentList()
        {
            TempData["Class"] = TempData["Class"];
            Session["Class"]  = TempData["Class"];
            String selectedclass    = TempData["Class"].ToString();
            string connectionString = @"Data Source=msi;Initial Catalog=SEFASSIGNMENT;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

            System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);

            sqlConnection.Open();

            System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT Stu_ID FROM [SEFASSIGNMENT].[dbo].[Student] WHERE CLASS_ID='" + selectedclass + "'");
            sqlCommand.Connection = sqlConnection;

            SqlDataReader myreader      = sqlCommand.ExecuteReader();
            List <String> StudentIDList = new List <String>();

            while (myreader.Read())
            {
                StudentIDList.Add(myreader[0].ToString());
            }

            myreader.Close();

            sqlCommand            = new System.Data.SqlClient.SqlCommand("SELECT Stu_Name FROM [SEFASSIGNMENT].[dbo].[Student] WHERE CLASS_ID='" + selectedclass + "'");
            sqlCommand.Connection = sqlConnection;
            SqlDataReader newmyreader     = sqlCommand.ExecuteReader();
            List <String> StudentNameList = new List <String>();

            while (newmyreader.Read())
            {
                StudentNameList.Add(newmyreader[0].ToString());
            }
            newmyreader.Close();
            sqlConnection.Close();

            var model = new StudentList
            {
                StudentNameList = StudentNameList,
                StudentIDList   = StudentIDList
            }; return(View(model));
        }
Exemplo n.º 53
0
        public Part lookupAssociatedPart(int partID)
        {
            Console.WriteLine(productID);
            SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True");

            con.Open();
            var query = "SELECT productID, partID, name, price, inStock FROM dbo.associatedParts where partID = @partID and productID = @productID";
            var cmd   = new System.Data.SqlClient.SqlCommand();

            cmd.CommandText = query;
            cmd.Connection  = con;
            cmd.Parameters.AddWithValue("@partID", partID);
            Console.WriteLine("prodcutID" + getProductID());
            cmd.Parameters.AddWithValue("@productID", getProductID());

            String        partsID;
            String        name;
            String        price;
            String        inStock;
            String        min;
            String        max;
            Part          lookedUpAssocPart = new Part();
            SqlDataReader rdr = cmd.ExecuteReader();

            // Fill the strings with the values retrieved, convert to types as needed
            while (rdr.Read())
            {
                partsID = rdr["partID"].ToString();
                lookedUpAssocPart.setPartID(Convert.ToInt32(partsID));

                name = rdr["name"].ToString();
                lookedUpAssocPart.setName(name);

                price = rdr["price"].ToString();
                lookedUpAssocPart.setPrice(Convert.ToDouble(price));

                inStock = rdr["inStock"].ToString();
                lookedUpAssocPart.setinStock(Convert.ToInt32(inStock));
            }
            con.Close();
            return(lookedUpAssocPart);
        }
        /// <summary>
        /// إرسال أمر SQl
        /// وإرجعه على شكل مصفوفتين كائنات
        /// </summary>
        /// <param name="SqlCommand1">أمر SQL</param>
        /// <returns>ليست بداخل ليست Object</returns>
        public static async Task <List <List <object> > > GetSql(System.Data.SqlClient.SqlCommand SqlCommand1)
        {
            List <object>         object1 = new List <object>();
            List <List <object> > object2 = new List <List <object> >();

            if (await openConction())
            {
                SqlCommand1.Connection = Sqldatabasethrding.SqlConnection1;

                SqlDataReader SqlDataReader1 = null;

                try
                {
                    SqlDataReader1 = SqlCommand1.ExecuteReader();


                    while (await SqlDataReader1.ReadAsync())
                    {
                        for (int i = 0; i < SqlDataReader1.FieldCount; i++)
                        {
                            object1.Add(SqlDataReader1.GetValue(i));
                        }
                        object2.Add(object1);
                        object1 = new List <object>();
                    }
                }
                catch (Exception e)
                {
                    ErrorClass.SaveErrorFile(e);
                }
                finally
                {
                    SqlDataReader1.Close();
                    SqlDataReader1.Dispose();
                }
                return(object2);
            }
            else
            {
                return(object2);
            }
        }
Exemplo n.º 55
0
        public int checkproductqty(int batch_id, int productID)
        {
            int dt = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                {
                    cmd.CommandText = "sp_selectbatchwisequantity";
                    cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        cmd.Parameters.AddWithValue("@batch_id", batch_id);
                        cmd.Parameters.AddWithValue("@product_id", productID);
                        cmd.Connection     = con;
                        cmd.CommandTimeout = 600000;
                        con.Open();

                        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if (reader.Read())
                            {
                                dt = Convert.ToInt32(reader["StockAvl"].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ErrorLog.saveerror(ex);
                    }
                    finally
                    {
                        if (con.State == ConnectionState.Open)
                        {
                            con.Close();
                        }
                    }

                    return(dt);
                }
            }
        }
Exemplo n.º 56
0
        //4)Once a case is chosen, this will load the data from the Persons table and data from the IndividualAtRisk table and loads it into the Care Giver tab on the
        //Case View form.
        public CareGiver RetrieveCareGiverData(int indivId)
        {
            var giver = new CareGiver();

            sqlConnection1 = InitializeConnectionString();

            using (sqlConnection1)
            {
                string data = "Select * from Persons, CareGiver, IndividualAtRisk where IndividualAtRisk.IndivId = '" + indivId + "' AND IndividualAtRisk.IndivId = CareGiver.IndivId AND Caregiver.PersonId = Persons.PersonId";
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(data, sqlConnection1);
                sqlConnection1.Open();
                using (SqlDataReader read = cmd.ExecuteReader())
                {
                    while (read.Read())
                    {

                        giver.personId = read["PersonId"].ToString();
                        giver.lastName = read["Last_Name"].ToString();
                        giver.firstName = read["First_Name"].ToString();
                        giver.gender = read["Gender"].ToString();
                        giver.race = read["Race"].ToString();
                        giver.dob = read["DOB"].ToString();
                        giver.ssn = read["SSN"].ToString();
                        giver.milDependent = read["Military_Dependent"].ToString();
                        giver.churchConnection = read["Church_Connections"].ToString();
                        giver.streetAddress = read["Street_Address"].ToString();
                        giver.apartment = read["Apartment_Number"].ToString();
                        giver.city = read["City"].ToString();
                        giver.state = read["State"].ToString();
                        giver.zip = read["Zip_Code"].ToString();
                        giver.email = read["Email"].ToString();
                        giver.homePhone = read["Telephone_Home"].ToString();
                        giver.mobilePhone = read["Telephone_Mobile"].ToString();
                        giver.workPhone = read["Telephone_Work"].ToString();
                        giver.type = read["Person_Type"].ToString();
                        giver.relationship = read["Relationship"].ToString();
                    }
                }
            }
            sqlConnection1.Close();
            return giver;
        }
Exemplo n.º 57
0
        } //========== end of the mtd

        public IEnumerable <Models.IncomeAccountsListingModel> GetIncomeAccountsListingUsingParams(string search)
        {
            List <IncomeAccountsListingModel> obList = new List <IncomeAccountsListingModel>();

            using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                var cmd = new System.Data.SqlClient.SqlCommand("", con);

                con.Open();
                //cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "SELECT top 1000 * FROM Income_AccountsListing "
                                  + " where ACCOUNTNUMBER like @searchval";
                cmd.Parameters.AddWithValue("@searchval", "%" + search + "%");
                //cmd.CommandTimeout = 0;

                //cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;


                //cmd.ExecuteNonQuery();
                //cmd2.ExecuteNonQuery();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var ob = new IncomeAccountsListingModel();

                    //iob.ID = reader["ID"] != DBNull.Value ? int.Parse(reader["ID"].ToString()) : 0;
                    ob.ACCOUNTNUMBER       = reader["ACCOUNTNUMBER"] != DBNull.Value ? reader["ACCOUNTNUMBER"].ToString() : "default";
                    ob.CustomerName        = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "default";
                    ob.MIS_CODE            = reader["MIS_CODE"] != DBNull.Value ? reader["MIS_CODE"].ToString() : "default";
                    ob.BranchCode          = reader["BranchCode"] != DBNull.Value ? reader["BranchCode"].ToString() : "default";
                    ob.accountofficer_code = reader["accountofficer_code"] != DBNull.Value ? reader["accountofficer_code"].ToString() : "default";
                    ob.Team_branch         = reader["Team_branch"] != DBNull.Value ? reader["Team_branch"].ToString() : "default";
                    ob.Date_Open           = reader["Date_Open"] != DBNull.Value ? DateTime.Parse(reader["Date_Open"].ToString()) : DateTime.Parse("1000-01-01");

                    obList.Add(ob);
                }
                con.Close();
            }
            return(obList);
        } //========== end of the mtd
Exemplo n.º 58
0
        public HttpResponseMessage GetAvailableIncomeAccountsUnit(HttpRequestMessage request)
        {
            return(GetHttpResponse(request, () =>
            {
                HttpResponseMessage res = null;

                string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["FintrakDBConnection"].ConnectionString;

                List <IncomeAccountsUnitModel> IncomeAccountsUnitList = new List <IncomeAccountsUnitModel>();

                using (var con = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    var cmd = new System.Data.SqlClient.SqlCommand("proc_Income_Accounts_Units_Get", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandTimeout = 0;

                    //cmd.Parameters.Add("@result", System.Data.SqlDbType.TinyInt).Direction = System.Data.ParameterDirection.Output;

                    con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        var tb = new IncomeAccountsUnitModel();

                        tb.ID = reader["ID"] != DBNull.Value ? int.Parse(reader["ID"].ToString()) : 0;
                        tb.AccountNumber = reader["AccountNumber"] != DBNull.Value ? reader["AccountNumber"].ToString() : "default";
                        tb.CustomerName = reader["CustomerName"] != DBNull.Value ? reader["CustomerName"].ToString() : "default";
                        tb.MIS_Code = reader["MIS_Code"] != DBNull.Value ? reader["MIS_Code"].ToString() : "default";

                        IncomeAccountsUnitList.Add(tb);
                    }
                    con.Close();
                }

                //comm = "Operation Successful.";
                res = request.CreateResponse(HttpStatusCode.OK, IncomeAccountsUnitList);

                return res;
            }));
        }
Exemplo n.º 59
0
    public void sendToButton_Click(object sender, EventArgs e)
    {
        List <int> studentIDList = new List <int>();

        for (int i = 0; i < gridviewRefer.Rows.Count; i++)
        {
            CheckBox check = (CheckBox)gridviewRefer.Rows[i].FindControl("studentCheck");


            if (check.Checked)
            {
                //find student ID for student who is checked
                int studentID = Convert.ToInt32(gridviewRefer.DataKeys[i]["StudentEntityID"]);
                studentIDList.Add(studentID);
            }
        }
        String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(connectionString);
        sc.Open();

        System.Data.SqlClient.SqlConnection EmailQuery = new System.Data.SqlClient.SqlConnection(connectionString);
        List <String> emailList = new List <String>();

        // Mail Button Query
        EmailQuery.Open();
        System.Data.SqlClient.SqlCommand query = new System.Data.SqlClient.SqlCommand();
        query.Connection = EmailQuery;

        foreach (var studentID in studentIDList)
        {
            query.CommandText = "SELECT UserEntity.EmailAddress FROM UserEntity INNER JOIN Student ON UserEntity.UserEntityID = Student.StudentEntityID WHERE Student.StudentEntityID=" + studentID;
            System.Data.SqlClient.SqlDataReader Result = query.ExecuteReader();

            while (Result.Read())
            {
                String email = Result.GetString(0);
                emailList.Add(email);
            }
        }
        EmailQuery.Close();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["AccountId"] != null && Convert.ToInt16(Session["type"]) == 1)
        {
            System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["roommagnetdbConnectionString"].ToString());

            System.Data.SqlClient.SqlCommand selectEmail = new System.Data.SqlClient.SqlCommand();
            selectEmail.Connection = sc;

            sc.Open();

            intellicorpEmail.Text = String.Empty;

            //Populate Emails
            selectEmail.CommandText = "SELECT Account.FirstName, Account.LastName, Account.Email " +
                                      "FROM Account FULL OUTER JOIN Host ON Account.AccountID = Host.HostID FULL OUTER JOIN Tenant ON Account.AccountID = Tenant.TenantID " +
                                      "WHERE Host.BackgroundCheckStatus = 0 OR Tenant.BackgroundCheckStatus = 0";


            StringBuilder nameCard = new StringBuilder();
            System.Data.SqlClient.SqlDataReader reader = selectEmail.ExecuteReader();

            while (reader.Read())
            {
                String fName = reader["FirstName"].ToString();
                String lName = reader["LastName"].ToString();
                String email = reader["Email"].ToString();

                //StringBuilder
                StringBuilder myCard = new StringBuilder();
                myCard.Append("<tr><td><a href =\"#\" class=\"tenantdashlist\">" + "Name: " + fName + " " + lName + "</a></td>" +
                              "<td><a href =\"#\" class=\"tenantdashlist\">" + "Email: " + email + "</a></td></tr>");
                intellicorpEmail.Text += myCard.ToString();
            }
            reader.Close();
            sc.Close();
        }
        else
        {
            Response.Redirect("Home.aspx");
        }
    }