Showing posts with label SQLS. Show all posts
Showing posts with label SQLS. Show all posts

Tuesday, November 1, 2011

Step 8 : WCF RESTful GET JSON List with FIDDLER


http://pantestmb.blogspot.com/2011/10/step-3-from-soap-to-wcf-restful-get.html

In Step 3 there was a sample of transforming a SOAP Web Service into a WCF RESTful one
with "application/xml" Content-Type ;

In step 7 - there are all the details about the full source code

To be accessed from a Android Client the sample must switch from XML to JSON data-content ;

Let's consider that the webservice is installed on the IP : 192.168.61.3 :
http://192.168.61.3/RestServicePost/RestServiceImpl.svc/json/getallpersons

The code behind the above link : 

        [OperationContract]
        [WebInvoke(Method = "GET",
            ResponseFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Wrapped,
            UriTemplate = "json/getallpersons")]
        PersonData[] getJsonPersons();

        public PersonData[] getJsonPersons()
        {
            return getAllPersons();
        }


        public PersonData[] getAllPersons()
        {
            SqlConnection dbConn = new SqlConnection(connStr);
            dbConn.Open();
            string sqlSelect = "select * from users ";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, dbConn);
            DataTable dt = new DataTable();
            SqlCommand dbCommand = new SqlCommand(sqlSelect, dbConn);
            da.Fill(dt);
            dbConn.Close();
            List list = new List();
            foreach (DataRow row in dt.Rows)
            {
                // Person target = Activator.CreateInstance();
                PersonData target = new PersonData();
                target.Name = row["Name"].ToString();
                target.User = row["UserName"].ToString();
                target.Email = row["EMail"].ToString();
                target.Password = row["Password"].ToString();
                // DataColumnAttribute.Bind(row,target);
                list.Add(target);
            }
            return list.ToArray();
        }

Pictures about what the code does ;
FIDDLER gets from the webservice all the persons from the database in JSON format :







Wednesday, October 19, 2011

Step 2 : From SOAP to WCF-RESTful-POST

From SOAP to WCF-RESTful-POST

Simple step 2 :

http://pantestmb.blogspot.com/2011/02/personpassport4asmx.html

SOAP

[WebMethod(Description = "Method to Add User")]
public bool AddUser(string username, string password, string name, string email)
{
bool returnBool = false;
SqlConnection dbConn = new SqlConnection(connStr);
string sqlStr = "INSERT INTO users(username,password,name,email)
values('" + username + "', '" + password + "', '" + name + "', '" + email + "');";
SqlCommand dbCommand = new SqlCommand(sqlStr,dbConn);
try
{
dbConn.Open();
if (dbCommand.ExecuteNonQuery()!=0)
{
returnBool=true;
}
returnBool=true;
}
catch
{
returnBool=false;
}
dbConn.Close();
return returnBool;
}





WCF REST POST :





    [DataContract(Namespace = "")]
    public class RequestData
    {
        [DataMember]
        public string details { get; set; }
    }


    [DataContract]
    public class PersonData
    {
        [DataMember]
        public string Name { get; set; }


        [DataMember]
        public string User { get; set; }


        [DataMember]
        public string Email { get; set; }


        [DataMember]
        public string Password { get; set; }
    }







        public PersonData AddUser(RequestData rData)
        {
            bool returnBool = false;


            var data = rData.details.Split('|');
            var response = new PersonData
            {
                Name = data[0],
                User = data[1],
                Email = data[2],
                Password = data[3]
            };


            SqlConnection dbConn = new SqlConnection(connStr);
            string sqlStr = "INSERT INTO users(username,name,email,password)
                                values('" + data[0] + "', '" + data[1] + "', '" + data[2] + "', '" + data[3] + "');";

            SqlCommand dbCommand = new SqlCommand(sqlStr, dbConn);
            try
            {
                dbConn.Open();
                if (dbCommand.ExecuteNonQuery() != 0)
                {
                    returnBool = true;
                }
                returnBool = true;
            }
            catch
            {
                returnBool = false;
            }
            dbConn.Close();
            return response;
        }



the result on fiddler





the result on SQL Server DataBase



Full Code on Google.Docs :
https://docs.google.com/document/d/1H0d2gq22-SsTElxI_mBEdAiagEOQL4HPXLInDHLDorg/edit?hl=en_GB

~ a 7z file ~ Visual Web Developer Express 2010 ~ full project :
https://docs.google.com/leaf?id=0BzKVfKe--t_cYTc4MWZkZjMtM2VmYi00OWIyLWJkMDgtN2FmOWE0ZGM4YTY5&hl=en_GB

Dumped Database can be downloaded at :
https://docs.google.com/leaf?id=0BzKVfKe--t_cYjAzOTk5YmEtNjk1ZS00ZTcxLWIzMDgtYTMwMTVlMTQwZDVi&hl=en_GB


Thursday, September 22, 2011

Sample Databases for SQL Server 2008


Northwind and pubs Sample Databases for SQL Server 2000

File Name - SQL2000SampleDb.msi = instnwnd.sql + instpubs.sql 



Running the Scripts Using osql
To run the scripts using osql:
1.              Open a command prompt window.
2.              From the command prompt, use the cd command to navigate to the folder containing the SQL Server 2000 Sample Database Scripts:
3.              cd c:\SQL Server 2000 Sample Databases
4.              Run osql specifying either Instpubs.sql or Instnwnd.sql as the input file. Here are some examples:
·                            To install the Northwind database on a default instance on the same computer:
osql -E -i instnwnd.sql
·                            To install the pubs database on a default instance on a different computer:
osql -E -S ComputerName -i instpubs.sql
·                            To install the Northwind database on a named instance:
osql -E -S ComputerName/InstanceName -i instnwnd.sql

Microsoft SQL Server Community Projects & Samples

SQL Server 2008R2 product code samples are available here. Download the AdventureWorks 2008R2 family of sample databases and AdventureWorks 2008R2 sample databases

Sunday, August 7, 2011

Change SQL Server Password by Windows Authentication

Change SQL Server Password

Change SQL Server Password by Windows Authentication

If Builtin/Administrator is present in SQL Server, you can login with an ID which is member of Administrators group and reset sa password in SQL Server. Just do as follows:

  • 1. Login into SQL server using Windows Authentication.
  • 2. In Object Explorer, open Security folder, open Logins folder. Right Click on sa account and go to Properties.
  • 3. Type a new SQL sa password, and confirm it. Click OK to finish.
After restarting SQL Server and all its services, you can log into SQL Server by sa login with new SQL sa password.