[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

How to show encrypted value with Datagrid and Dataset

Hi,
I'm migrating from asp to asp.net(vb). I've got a problem that I want to show the encryed value with Datagrid and Dataset. I use  SQL server store procedures to decrypt it. So how could I show the encypted value? Any ideas are welcome!

Thanks!
0
lijunguo
Asked:
lijunguo
  • 10
  • 7
1 Solution
 
mmarinovCommented:
Hi lijunguo,

you want to show the encrypted value istead of the original one an decrypt it after a postback - is this correct ?


Regards!
B..M
mmarinov
0
 
lijunguoAuthor Commented:
Hi mmarinov,
Thanks for your quick post and your correction! It should be a decrypt one, not encrypt one. I'll edit the title.
0
 
surajgupthaCommented:
Since u are migrating. May i ask you how the decrytion was done in asp. Was it done using stored procs before?

If you are in need of minimal work in migrating, reuse the stored proc

If you are looking for performace issues concerning the use of stored proc and doing it in asp. net, it depends on the load of the processing required

Hope this helps

Suraj
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
lijunguoAuthor Commented:
I could not find the "edit" button to edit my question and title.
So the question is :

I'm migrating from asp to asp.net(vb). I've got a problem that I want to show the decryed value with Datagrid and Dataset. I use  SQL server store procedures to decrypt it. So how could I show the decypted value? Any ideas are welcome!


The original value in DB is encrypted by SQL SP. I need to show the decrypted value. Sorry for that
0
 
mmarinovCommented:
lijunguo,

so if you decrypt the values in sql, is this code work for you

SqlConnection connection = new SqlConnection(your connection string);
SqlCommand command = new SqlCommand ("your stored procedure name", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;

DataSet ds = new DataSet();
adapter.Fill ( ds );
DataGrid1.DataSource = ds.Tables[0];
DataGrid1.DataBind();

Regards!
B..M
mmarinov
0
 
lijunguoAuthor Commented:
mmarinov,

That's not working for me. My decripted SP only accept 2 CHAR parameters , one is for input, the other is for output.
It's not powerful enough to get all values in the table.
I think I need to provide more details about this question. I just make a simple sample.

My table is like this:
userid    int
username char
creditCardNo char

I want to show all three fields in the Datagrid and Dataset. When I run a query, it usually return many rows, if there is only one row returned, I could do a trick to show in Datagrid and Dataset. I want to show many rows which caused me problems. I am new to ASP.NET, for ASP, I use RECORDSET to loop and decript it's OK. But for Datagrid and Dataset, I don't know how to do it.

We may forget about the query, I just want to show all three fields for all users in the Datagrid and Dataset, is there a way to do it?

Thanks.
0
 
lijunguoAuthor Commented:
The creditCardNo is encripted in the table, we have to decript it first. But the SP only accecpt 2 parameters as I posted before. Do you think I have to rewrite a SP to return all the fields? Or is there a similar way to do like ASP Recordset?
0
 
mmarinovCommented:
it will be easy if you return the complete list of record and then work with them in the code ( something like asp recordset )

B..M
mmarinov
0
 
lijunguoAuthor Commented:
How could I do that?
0
 
lijunguoAuthor Commented:
I want to do it with Datagrid and Dataset, not asp recordset. I've already done it with ASP recordset one year ago. I want to do it with ASP.NET Datagrid and Dataset. I got stuck here.
0
 
mmarinovCommented:
why? in your stored procedure after working with the output parameter create a select param and then use dataadapter to fill the dataset
after that in DataSet.Tables(0) you will have the result from the select statement and you can bind the grid

B..M
mmarinov
0
 
lijunguoAuthor Commented:
Hi mmarinov,

I think you'll have a solution for me. The problem is I do not explain my question very well.

The problem is my SP is only accepted 2 parameters, one is the encrypted value from table(input), the other is decrypted value (output)

for example,

first, I get the query result from tbl_user, now cardnumber is encrypted, I need to pass the encrypted value to
store procedure.

sql_select = "select userid,username, cardnumber from tbl_user"

The above query return many rows, I want to bind that query to DataGrid or Dataset. But the cardnumber is not decrypted. I need to call SP to decrypt the value.  but my SP can only deal with one row each time. I am stuck here.


Is that clear for you? If not just let me know, I just start my work.
0
 
mmarinovCommented:
there is a solution, BUT i think it is will be a little slow :(

to get data you can use

SqlConnection myConn = new SqlConnection(myConnectionString);
   SqlDataAdapter myDataAdapter = new SqlDataAdapter();
   myDataAdapter.SelectCommand = new SqlCommand("select userid,username, cardnumber from tbl_user", myConn);

   myConn.Open();

   DataSet ds = new DataSet();
   myDataAdapter.Fill(ds, myTableName);

  datagrid1.DataSource = ds.Tables[0];
  datagrid1.DataBind();

then create an ItemDataBound event for the datagrid

protected void Item_Bound(Object sender, DataListItemEventArgs e)
      {

         if (e.Item.ItemType == ListItemType.Item ||
             e.Item.ItemType == ListItemType.AlternatingItem)
         {
            //let say the encrypted cardnumber is holded in column 2( the 3d column )
SqlConnection myConn = new SqlConnection(myConnectionString);
SqlCommand com = new SqlCommand("your stored procedure name", myConn);
   com.CommandType = CommandType.StoredProcedure;
   com.Parameters.Add("@EncryptedCardNumber", SqlDbType.VarChar, 80).Value = e.Item.Cells[2].Text;
   com.Parameters.Add("@DecryptedCardNumber", SqlDbType.VarChar);
   com.Parameters[1].Direction = ParameterDirection.InputOutput;

  com.ExecuteScalar();
  e.Item.Cells[2].Text = com.Parameters[1].Value;
  myConn.Close();
   

         }

      }

HTH
B..M
mmarinov
0
 
lijunguoAuthor Commented:
Hi mmarinov,
Thank you for your quick post, I use this for Intranet not for internet. a little bit slow is no problem. I'll change your code to test and let you know.

Thanks!
0
 
lijunguoAuthor Commented:
Hi mmarinov,

I changed to VB and it's working.  I don't know C#. You're so great!
You said it's a little bit slow, if you could give me a better ideas I'll really appreciate it.

Regrads,

Lijunguo
0
 
mmarinovCommented:
the better  idea is to return direct the decrypted card number
then it won't need to call every time to db when you bind the grid

glad that all works as you wish

B..M
mmarinov
0
 
lijunguoAuthor Commented:
Yes, you're right. It'll reduce the round trip. It takes time to do redesign. Anyway, we'll have a working solution. That's very important. Later I'll consider the performance.
Thanks for your help!
0
 
mmarinovCommented:
You are welcome

B..M
mmarinov
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now