How can I Create a column in SQL SERVER where, depending on the value, debt or credit appears?

Asked

Viewed 90 times

1

Good friends, I made a program where I import data from an excel file in the sql database, this database is about values, and etc... And wanted to know if you have how to create a column where after being imported sql, automatically write in the next column if it is debit or credit Exmp:

Data Mov    Valor em EUR   Débito/Crédito
23-10-2019    -5000.00     Débito
3-1-2016      1234.50      Crédito

Do I have to program this in Asp.net or can I do it in sql? Every time I care in that table gives me the debit and credit

Code Asp.net: 

 protected void Upload_Click(object sender, EventArgs e)
 {
            string excelPath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            string filepath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.FileName);
            string filename = Path.GetFileName(filepath);
            FileUpload1.SaveAs(excelPath);
            string ext = Path.GetExtension(filename);
            String strConnection = @"Data Source=PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Persist Security Info=True;User ID=sa;Password=1234";
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HRD=YES;IMEX=1;\"";
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            OleDbCommand cmd = new OleDbCommand("Select * from [rptListaMovs_4$]", excelConnection);
            excelConnection.Open();
            cmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("Select * from [rptListaMovs_4$]", strConnection);
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
            {
                sqlBulk.DestinationTableName = "Dados";
                sqlBulk.ColumnMappings.Add("Data Mov", "Data Mov.");
                sqlBulk.ColumnMappings.Add("Data Valor", "Data Valor");
                sqlBulk.ColumnMappings.Add("Descrição do Movimento" , "Descrição do Movimento");
                sqlBulk.ColumnMappings.Add("Valor em EUR", "Valor em EUR");
                sqlBulk.WriteToServer(dReader);
             }
             excelConnection.Close();
}
sql database asp.net
  • 1

    To be possible is more there are n ways to address this subject, seems to me a little broad this question.

  • @Amadeuantunes As for example?

  • https://social.msdn.microsoft.com/Forums/Lync/en-US/cac57b8b-4175-4e59-ab19-efaf4dddaf18/c-serialize-deserialize-tofrom-excel-sheet?forum=vsto @Pedro Pereira

  • @Amadeuantunes So I have to program on Asp.net right? The problem is I’m new at this I don’t know where to start

  • From what I understand, you need to import from excel data using Asp.net and then you want to store that data in the database, so you’re going to have to get that data out of the file and put it in a database table. @Pedro Pereira

  • https://docs.microsoft.com/pt-br/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017 virtual column as I understand it, use a CASE

  • @Amadeuantunes I’ve done it... I now wanted to make that after importing the data into the database, in the database appear a table to say if the value that was taken out in the column "Value in EUR" is debit or credit

  • so you already have the data imported in the c# side and you want to write in the database? @Pedro Pereira

  • @Amadeuantunes I want after this, already in the database, SQL alone make a list of the values that are debited(negative) and credits (positive)

  • Sorry @Pedropereira still seems a bit vast, but you can do that on the c# side and project those values on the table. If the table is not created you create first. But if the table was to create dynamically you have to do it on the side of where you ask for the c# to create the table to sql and then put the data in this table

  • @Amadeuantunes This is for a customer, it simply matters to the database and then a column is added to the database to tell if that value is a credit or debit... Can’t explain it better than that... I made an example and everything up there

  • @String value = value > 0? "Credit" "Debit"; string st = "update Data set Data_value = " + value Where costumized.table_name; https://stackoverflow.com/questions/15246182/sql-update-statement-in-c-sharp

Show 7 more comments

2 answers

1


I’ve got it, here’s the personal solution:

ALTER TABLE dbo.Dados ADD [Debito/Credito] AS CASE WHEN [Valor em EUR] < 0 THEN 'Debito' ELSE 'Credito' END;

0

This can be solved in c#:

valor > 0? "Crédito" : "Débito"

and that "Credit/Debit" column in the database is not necessary because it is redundant, unless you have positive debit and negative credit.

  • Where should I put this in my code @Hudsonph

  • The code is above

  • @Pedropereira as I said you do not need to save credit or debit in the database pq' is redundant, the best even 'and when you do the reading by c# vc insert this mark

  • even a simple sql to separate credit and debit, select * from startName Where value > 0

  • But where do I have to enter this tag? in what part of the code? I have to open some If or so?

  • I know with a query sql, but I want it to be the program to be easy for the user

  • put your read code in the database, I’ll help you

  • The whole code is up there, I just edited it out

  • I hope you can help me :D I can’t find anything on the internet

  • @Pedropereira Sorry but I still think the question is a little broad would be possible to provide a little more details at least I’m still having some difficulty in understanding the question

  • @Amadeuantunes This is for a customer, and the type of table he has is of invoices and movements and values, and as it is for a customer I have to do the most optimized possible, and he has no columns where he says debit or credit... Nor will it because it is not his work... I will simply make you a program that 1. Import excel files into the database. Done 2. Create a column at the time of upload so that a column is created in the database to tell if the value refers to a debit or credit.. Better than this is impossible

  • https://stackoverflow.com/questions/15246182/sql-update-statement-in-c-sharp

  • @Pedropereira You can use this to update the field

  • @Pedropereira after the field exists inserts using this class I left the link above, there may be other solutions but it was my idea

Show 9 more comments

Browser other questions tagged sql database asp.net

You are not signed in. Login or sign up in order to post.