store and retrieve binaryfile(fpt) in database

Question

i have to store a finger print template in database and retrieve it. do i have to set the column name as image or var binary(max)? i tried the some code from some sites, but didn't work.

i retrieved a blank file from database. am doing the project in c#.net using sql server 2005. the ftp file is 1.59kb in size

thanks in advance

//to add new user
        public void AddUser(string name,byte[] pf,int length)
        {

//code to insert file in database
            cn = new SqlConnection(connstring);
            cmd = new SqlCommand("adduser", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@name", name);
            //to save converted image to variable
            SqlParameter UploadedImage = new SqlParameter("@fp", SqlDbType.Binary, length);
            UploadedImage.Value = pf;
            cmd.Parameters.Add(UploadedImage);
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //retrieve fingerprint from database
        public void FingerPrintRtvl(string uid)
        {

                cn = new SqlConnection(connstring);
                adp = new SqlDataAdapter("fingerprintrtvl", cn);
                adp.SelectCommand.CommandType = CommandType.StoredProcedure;
                adp.SelectCommand.Parameters.AddWithValue("@uid", Convert.ToInt32(uid));
                DataSet ds = new DataSet("MyImages");
                cn.Open();
                adp.Fill(ds, "MyImages");
                cn.Close();

                byte[] MyData = new byte[57];

                DataRow myRow;
                myRow = ds.Tables["MyImages"].Rows[0];

                MyData = (byte[])myRow["fp"];
                int ArraySize = new int();
                ArraySize = MyData.GetUpperBound(0);
                string temp = System.IO.Path.GetTempPath();
                string fpFile = "D:\\" + "fingerprint.fpt";
                FileStream fs = new FileStream(fpFile, FileMode.OpenOrCreate, FileAccess.Write);
                fs.Write(MyData, 0, ArraySize);
                fs.Close();

        }

score 1 · Answer 1 · answered Mar 08 '12 at 04:56

thanks all of you for your guidance and i got the solution.....thanks a lot.
please make sure that the column type of the binary file is varbinary(max) in the database

    using system.data.Sqlclient;
    using system.IO;
    using system.Data;

  //function to store the fingerprint in database
  public void AddUser(string name)
    {
        // Read the file and convert it to Byte Array//,byte[] pf,int length
        string filePath = @"D:\10.fpt";
        string filename = Path.GetFileName(filePath);
        FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        BinaryReader br = new BinaryReader(fs);
        Byte[] bytes = br.ReadBytes((Int32)fs.Length);
        br.Close();
        fs.Close();

        //insert the file into database
       SqlConnection cn = new SqlConnection(connstring);
       SqlCommand cmd = new SqlCommand("adduser", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
        cmd.Parameters.Add("@fp", SqlDbType.Binary).Value = bytes;

        cn.Open(); cmd.ExecuteNonQuery();
        cn.Close();


    }

    //retrieve fingerprint from database
    public void FingerPrintRtvl(string uid)
    {

        //retrieving the file from the database
      SqlConnection  cn = new SqlConnection(connstring);
       SqlDataAdapter adp = new SqlDataAdapter("fingerprintrtvl", cn);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        adp.SelectCommand.Parameters.AddWithValue("@uid", Convert.ToInt32(uid));
        DataSet ds = new DataSet("MyImages");
        cn.Open();
        adp.Fill(ds, "MyImages");
        cn.Close();

        //storing the file in byte array
        byte[] MyData = new byte[0];

        DataRow myRow;
        myRow = ds.Tables["MyImages"].Rows[0];

        MyData = (byte[])myRow["fp"];
        int ArraySize = new int();
        ArraySize = MyData.GetUpperBound(0);
        string temp = System.IO.Path.GetTempPath();
        string fpFile = "D:\\" + "Aadhaarfingerprint.fpt";
        //saving the byte array in the local drive
        FileStream fs = new FileStream(fpFile, FileMode.OpenOrCreate, FileAccess.Write);
        fs.Write(MyData, 0, ArraySize);
        fs.Close();

    }

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

I have done some research for you: Look here

Seems that you don't follow the correct way to retrieve binary data, also your parameter should be SqlDbType.VarBinary

edited May 23 '17 at 12:21

Community

1
1

answered Mar 04 '12 at 12:28

Steve

213,761
22
232
286

score 0 · Answer 3 · answered Mar 04 '12 at 13:01

0

read all the bytes of the file and store it to a varbinary(max) column.

answered Mar 04 '12 at 13:01

Bahamut

1,929
8
29
51

store and retrieve binaryfile(fpt) in database

3 Answers3