-1

I have a column with encrypted name(all other columns are not encrypted) in SQL Database table. And I have to decrypt the column with encrypted name to show in DataGrid to users of my application but the actual table of SQL database should not be changed.(has to remain as encrypted name).

I think UpdateCommand works to update the actual table and I have to find an alternative than below UpdateCommand.

Or is there alternative way to decrypt only 1 column on DataTable which is not influencing the actual table of database?

My simple code is,

SqlCommand gridcomm = new SqlCommand();
gridcomm.Connection = Conn;

gridcomm.CommandText = "SELECT Id, customername, phonenumber FROM customers";

SqlDataAdapter gridda = new SqlDataAdapter(gridcomm);

SqlDataReader gridreader = gridcomm.ExecuteReader();
while (gridreader.Read())
{
}
gridreader.Close();

DataTable griddt = new DataTable("customers");
gridda.Fill(griddt);

foreach (DataRow row in griddt.Rows)
{
    string strcustomername = (string) row["customername"].ToString();
    bytecustomername = Convert.FromBase64String(strcustomername);
    string decryptedcustomername = DecryptStringFromBytes_Aes(bytecustomername, byteAESKey, byteAESIV);

    row["customername"] = decryptedcustomername;
}

gridda.UpdateCommand = new SqlCommandBuilder(gridda).GetUpdateCommand();

dataGrid_Totalcustomerlist.ItemsSource = griddt.DefaultView;
gridda.Update(griddt);
M.kazem Akhgary
  • 18,645
  • 8
  • 57
  • 118
Kay Lee
  • 922
  • 1
  • 12
  • 40

2 Answers2

1

Hello Kay Lee: I think that if you look at implementing a Coverter in your View you will get exactly what you are looking for. In your IValueConverter implementation you can Implement the Decrypt routine. A Converter is the extended syntax in a WPF Binding Statement. If this is not clear then I will flesh out some more. Here is a great reference for Converters: http://www.wpf-tutorial.com/data-binding/value-conversion-with-ivalueconverter/

Kind Regards, Mark Wardell

Mark Wardell
  • 493
  • 7
  • 25
  • I really appreciate your excellence. However, I understand the content of suggested link but it's much difficult for me to write code with Converter because my major is just biochemistry far from software industry..Would you kindly guide me with simply modifying my above code or another more simple way than Converter? Once again, Thank you so much. – Kay Lee Mar 07 '16 at 04:08
  • I wil research tomorrow and may have some questions for you. – Mark Wardell Mar 07 '16 at 04:23
  • please go into your update command and get me the sql statement from that please? Also the Select SQL. I think I have an easy idea. – Mark Wardell Mar 07 '16 at 04:32
  • I've just came to outside for lunch(Far East Asia). select statement is just normal as "SELECT Id, customername, phonenumber from customers" Here isn't the Update statement but UpdateCommand with SqlCommandBuilder. Usually, we SELECT columns and fill datatable through dataadapter and it's all enough. – Kay Lee Mar 07 '16 at 04:51
  • However, in this case, I have to decrypt column of datatable after the datatable is filled. If we need to change values after datatable is filled, people say we need Update command with SqlCommandBuilder to avoid Exception. – Kay Lee Mar 07 '16 at 04:52
  • so there is no updte cmd in 'gridda?' look in debugger at gridda.UpdateCommand = new SqlCommandBuilder(gridda).GetUpdateCommand(); – Mark Wardell Mar 07 '16 at 05:00
  • takke it out and show me which exception you are getting if you do not mind please? – Mark Wardell Mar 07 '16 at 05:05
  • Hmm..Do you mean I'm needed to analyze the messages from running debugger? There's only very simple SELECT command as I told you. Now, I'm returning to City Library and will take 10 minutes. – Kay Lee Mar 07 '16 at 05:06
  • i think i am understanding now. What front end is it associated with? WPF? Winforms? – Mark Wardell Mar 07 '16 at 05:08
  • No problem at all. Please kindly wait. Thank you. It's WPF. – Kay Lee Mar 07 '16 at 05:08
  • Because I changed datatable, it's needed to be updated but unintensionally, the actual table is updated together. – Kay Lee Mar 07 '16 at 05:12
  • Also post the XAML at binding statement. Kay Lee I gotta sleep just now. I will look at it at lunch tomorrow – Mark Wardell Mar 07 '16 at 05:25
  • Ok, I really appreciate your ideas. Please refer to the link for the Exception when without UpdateCommand. http://stackoverflow.com/questions/588361/update-requires-a-valid-updatecommand-when-passed-datarow-collection-with-modifi I'm needed to study contents of this link more deeply. – Kay Lee Mar 07 '16 at 05:38
  • Dear Mark Wardell, kind gentleman. I finally solved by myself thinking just logically. Please refer to the self-answer I posted. Have delightful day ! – Kay Lee Mar 07 '16 at 12:16
1

I've read many posts but there were no solution for me as this case is unusual. However, I just thought logically and finally found solution by myself.

We just need to delete 2 line of Update related code because we don't need to update. 

gridda.UpdateCommand = new SqlCommandBuilder(gridda).GetUpdateCommand();

gridda.Update(griddt);

Hope this helps someone..

Kay Lee
  • 922
  • 1
  • 12
  • 40