joao costa joao costa - 1 year ago 52
SQL Question

Saving a textfile into sql and sending it to FTP server

I have created a textfile programmatically and I saved it into a folder and now I need to save it into a table inside a database(I have already created the table)and after that fill a checkbox with those textfiles and send them to an FTP server.
Is that possible? If so how can I start doing it/what should I do?
Here is the code for creating the textfile and the code to the create the table. If you have any question about the code feel free to ask.

var numfatura = _transaction.TransDocument + _transaction.TransSerial + _transaction.TransDocNumber;

using (StreamWriter writer = new StreamWriter("C:\\Users\\HP8200\\Desktop\\Faturas Teste\\" +numfatura + ".txt"))
{
string numcont = _transaction.PartyFederalTaxID;
double numenc = _transaction.BillToPartyID;
DateTime data = _transaction.CreateDate;
double valor = _transaction.TotalAmount;
int zona = transaction.UnloadPlaceAddress.AddressID;
string zona2 = transaction.UnloadPlaceAddress.AddressLine2;
double quantidade = transaction.Details.Count;
string bonus = "0";
string valorStr = valor.ToString(CultureInfo.InvariantCulture);

writer.WriteLine($"{numcont};{numenc};{numfatura};{data:dd/MM/yyyy};{valorStr};{zona};");
foreach (ItemTransactionDetail detail in transaction.Details)
{
var item = MyApp.DSOCache.ItemProvider.GetItem(detail.ItemID, MyApp.SystemSettings.BaseCurrency);
double taxRate = MyApp.DSOCache.TaxesProvider.GetTaxRateFromTaxableGroupID(detail.TaxableGroupID, "PRT", "CON");
string barcode = item.BarCode;
var preconet = detail.TaxIncludedPrice;
var precoantesdisc = detail.UnitPrice;
string preconetStr = preconet.ToString(CultureInfo.InvariantCulture);
string precoantesdiscStr = precoantesdisc.ToString(CultureInfo.InvariantCulture);
writer.WriteLine($"{barcode};{taxRate};{precoantesdiscStr};{preconetStr};{quantidade};{bonus}");
}

} // create the text file
SqlConnection conn = new SqlConnection(@"Data source = 2c4138928627\Sage ; Database=ARMINDOData ; User Id=sa ; Password=sage2008+");
SqlCommand command = new SqlCommand("IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(Faturas char(250));", conn);
conn.Open();
SqlCommand insertCommand = new SqlCommand("INSERT INTO UXFaturas(Faturas) VALUES (*.txt)", conn);
command.ExecuteNonQuery();

MessageBox.Show("saved"); // create the table and insert the textfile

Answer Source

Create a stored procedure that takes in two parameters fileName and fileContent and then store it inside your UXFaturas table.

CREATE PROC USP_InsertFile(@fileName nvarchar(200),@fileContent nvarchar(max))
AS
BEGIN
     INSERT INTO UXFaturas VALUES(@fileName,@fileContent)
END

Here i am assuming that you UXFaturas table only has two columns.

Now you can simple call this stored procedure from your C# ADO code and pass in the right parameters.

using (SqlConnection conn = new SqlConnection(@"Data source = 2c4138928627\Sage ; Database=ARMINDOData ; User Id=sa ; Password=sage2008+"))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;

                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "USP_InsertFile";

                command.Parameters.AddWithValue("@fileName",fileName);
                command.Parameters.AddWithValue("@fileContent",fileContent);

                command.ExecuteNonQuery();
            }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download