ERP

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace WebApplication1
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    // [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        SqlConnection conn = new SqlConnection("Data Source=DESKTOP-T3ODSHL\\SQLEXPRESS;Initial Catalog=CRONUS;Integrated Security=True");
        SqlCommand cmd = new SqlCommand();


        [WebMethod]
        public void DeleteEmp(string no_)
        {
            try
            {
                conn.Open();
                cmd = new SqlCommand("DELETE FROM [Cronus].[dbo].[CRONUS Sverige AB$Employee] WHERE No_ = @no_", conn);

                cmd.Prepare();
                SqlParameter No_ = new SqlParameter("@no_", no_);
                cmd.Parameters.Add(No_);

                cmd.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }

        }

        [WebMethod]
        public List<List<string>> GetEmp() // kanske kan få ut detta på listan, för result är från list
        {
            conn.Open();
            List<List<string>> result = new List<List<string>>();
            cmd = new SqlCommand("SELECT No_, [First Name], [Last Name], [Job Title], Address, City FROM [CRONUS Sverige AB$Employee]", conn);

            result = Converter(cmd.ExecuteReader());
            conn.Close();
            return result;
        }





        [WebMethod]
        public void AddEmp(string employeeId, string firstName, string familyName, string address)
        { try
            {
                conn.Open();
                cmd = new SqlCommand("INSERT INTO [CRONUS Sverige AB$Employee] (No_, [First Name], [Last Name], [Address])"
                    + " VALUES (@employeeId, @firstName, @familyName, @address)", conn);


                cmd.Prepare();
                SqlParameter EmployeeId = new SqlParameter("@employeeId", employeeId);
                SqlParameter FirstName = new SqlParameter("@firstName", firstName);
                SqlParameter FamilyName = new SqlParameter("@familyName", familyName);
                SqlParameter Address = new SqlParameter("@no_", address);

                cmd.Parameters.Add(EmployeeId);
                cmd.Parameters.Add(FirstName);
                cmd.Parameters.Add(FamilyName);
                cmd.Parameters.Add(Address);
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                Debug.WriteLine(e.Message); // visar sqlError i visual konsolen
            }
        }

        [WebMethod]
        public void AddContract(string code, string description)
        {
            try
            {
                conn.Open();
                cmd = new SqlCommand("INSERT INTO [CRONUS Sverige AB$Employment Contract] VALUES (@code, @description)", conn);


                cmd.Prepare();
                SqlParameter Description = new SqlParameter("@description", description);
                SqlParameter Code = new SqlParameter("@code", code);


                cmd.Parameters.Add(Description);
                cmd.Parameters.Add(Code);

                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                Debug.WriteLine(e.Message); // visar sqlError i visual konsolen
            }
        }

        [WebMethod]
        public void UpdateEmp(string employeeId, string firstName, string familyName, string address)
        {
            conn.Open();


            cmd = new SqlCommand("UPDATE[CRONUS Sverige AB$Employee] SET[First Name] = @firstName, [Last Name] = @familyName, [Address] = @address WHERE [No_] = @employeeId", conn);
            cmd.Prepare();
            SqlParameter Emp = new SqlParameter("@employeeId", employeeId);
            SqlParameter Firstname = new SqlParameter("@firstName", firstName);
            SqlParameter FamilyName = new SqlParameter("@familyName", familyName);
            SqlParameter Address = new SqlParameter("@address", address);

            cmd.Parameters.Add(Emp);
            cmd.Parameters.Add(Firstname);
            cmd.Parameters.Add(FamilyName);
            cmd.Parameters.Add(Address);
            cmd.ExecuteNonQuery();
            conn.Close();
        }


        [WebMethod]
        public List<List<string>> Getsick()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT [Employee No_], [From Date], [Description], [Entry No_] "
            + "FROM[CRONUS Sverige AB$Employee Absence]"
            + " WHERE[From Date] BETWEEN '2004-01-01 00:00:00.000' AND '2004-12-31 23:59:59.999'"
            + " AND Description = 'Sjuk'", conn);

            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }


        [WebMethod]
        public List<List<string>> GetEmpInfo()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT[Employee No_], [Relative Code], [First Name], [Last Name]"
                    + " FROM[CRONUS Sverige AB$Employee Relative]", conn);
            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }

        /// 3B


        [WebMethod]
        public List<List<string>> GetAllKeys()
        {
            conn.Open();
            string sql = "SELECT TOP 5 CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
            SqlCommand cmd = new SqlCommand(sql, conn);

            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }

        [WebMethod]
        public List<List<string>> GetAllIndex()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT TOP 5 object_id, index_id, type_desc, name, is_primary_key FROM sys.indexes ", conn);


            var tmp = Converter(cmd.ExecuteReader()); // Converter Metoden kan inte köra null..
            conn.Close();
            return tmp;
        }

        [WebMethod]
        public List<List<string>> GetAllTableConstraint()
        {
            try
            {
                conn.Open();
                cmd = new SqlCommand("SELECT TOP 5 CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA"
                + " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS", conn);
                // det blir många tabeller och kolumner, Kanske köra select top 5 ??
                var tmp = Converter(cmd.ExecuteReader());
                return tmp;
            }
            catch(SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

        [WebMethod]
       public List<List<string>> GetAllTable1()
        {
            conn.Open();
            cmd = new SqlCommand(" SELECT top 5 name FROM sys.tables",conn);
            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }


        [WebMethod]
        public List<List<string>> GetAllTable2()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT TOP 5 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
                + " WHERE TABLE_TYPE = 'BASE TABLE'", conn);

            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }

        [WebMethod]
        public List<List<string>> GetAllColumnOnEmp1()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT TOP 6 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
                + " WHERE TABLE_NAME = 'CRONUS Sverige AB$Employee'", conn);

            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }

        [WebMethod]
        public List<List<string>> GetAllColumnOnEmp2()
        {
            conn.Open();
            cmd = new SqlCommand("SELECT top 5 c.name FROM sys.objects o"
                + " INNER JOIN sys.columns c ON c.object_id = o.object_id"
                + " AND o.name = 'CRONUS Sverige AB$Employee'", conn);

            var tmp = Converter(cmd.ExecuteReader());
            conn.Close();
            return tmp;
        }






        // Converter

        public List<List<string>> Converter(SqlDataReader sdr)
        {
            if (sdr != null)
            {
                List<List<string>> list = new List<List<string>>();
                while (sdr.Read())
                {
                    List<string> tmp = new List<string>();

                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        string parameter = "";
                        try
                        {
                            if (sdr.GetFieldType(i) == typeof(string))
                            {
                                parameter = sdr.GetString(i);
                            }
                            if (sdr.GetFieldType(i) == typeof(int))
                            {

                                parameter = sdr.GetInt32(i).ToString();
                            }
                            if (sdr.GetFieldType(i) == typeof(DateTime))
                            {
                                parameter = sdr.GetDateTime(i).ToString();
                            }
                        }
                        catch (SqlException e)
                        {
                            Debug.WriteLine(e.Message);
                        }

                        tmp.Add(parameter);

                    }

                    list.Add(tmp);
                }

                return list;
            }

            return null;
        }

       /* public DataTable getTable(ArrayOfString[] list)
        {
            ArrayOfString[] outerlist = list;
            DataTable table = new DataTable();
            ArrayOfString header = outerlist[0];
            for (int i = 0; i < header.Count; i++)
                table.Columns.Add(header[i]);
            for (int j = 1; j < outerlist.Length; j++)
            {
                ArrayOfString innerlist = outerlist[j];
                List<string> rowlist = new List<string>();
                for (int k = 0; k < innerlist.Count; k++)
                {
                    rowlist.Add(innerlist[k]);
                }
                string[] row = rowlist.ToArray();
                table.Rows.Add(row);
            }
            return table;
        }
        */

    }
}