Tag: database

.net core mvc 5 soru cevap

On By semt20In .NET CORE, visual studioLeave a comment

add-migration yapınca The specified deps.json doesnt exists hatasının package managerdan çözemedim projenin bulunduğu klasorde cmdyi açıp

dotnet ef migrations add MyFirstMigration

komutunu çalıştırınca oldu ve sonrasında unutma

dotnet ef database update

dbcontexte modelde migrate işleminde ilgili propun eklenmesini isdemessen

[NotMapped]
public string URUNADI { get; set; }

DROPDOWN LIST MODELDE ENUM OLUSTURULUR ORNEK

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace KPET_MODULU2.Models
{
    public class  T_ODEMEISLEMI
    { 
        public ODEME_TIPI ODEME_TIPI { get; set; } = new ODEME_TIPI();  /*KK,MAILORDER,CEK*/ 
    }
    public enum ODEME_TIPI
    {
        KREDIKARTI,
        MAILORDER,
        CEK
    }
}

CONTROLLERDE SAĞ TIKLAYIP EKLE DENIR CREATE SECILIR VE ILGILI MODEL VE CONTEXT SECILIR VE YARATILAN SAYFAYA EKLENİR;asp-items=”Html.GetEnumSelectList()”

            <label asp-for="ODEME_TIPI" class="control-label"></label>
            <select asp-for="ODEME_TIPI" class="form-control" asp-items="Html.GetEnumSelectList<ODEME_TIPI>()"></select>
            <span asp-validation-for="ODEME_TIPI" class="text-danger"></span>

BAT DOSYASINDAN VERITABANI VE ONEMLI KLASOR YEDEKLEME

On By semt20In BACKUP, SqlLeave a comment
@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)

set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%

set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%

xcopy C:\Users\ek3g\Desktop\*.* D:\veritabanyedekler\yedekleme     /s/d/y/i/h/c/r
sqlcmd -S MYPC -i “D:\veritabanyedekler\yedekscript.sql”
“c:\program files\winrar\rar.exe” a -r D:\veritabanyedekler\yedegi_al\%SUBFILENAME% D:\veritabanyedekler\yedekleme\*.*
rd D:\veritabanyedekler\yedekleme\ /s /q

C# ile SQLE Resim Kaydetmek

On By semt20In C SHARPLeave a comment
resim kaydetmek için
SQL TURU VARBINARY(MAX)

c#
BM_PERSON.IMAGEDATA = DATA TURU BYTE[]
pe_GB1_IMAGE = PICTUREEDIT DEVEXPRESS COMPONENT
set = PI.BM_PERSON.IMAGEDATA = (byte[])pe_GB1_IMAGE.EditValue;
get = pe_GB1_IMAGE.EditValue = PI.BM_PERSON.IMAGEDATA;

SQL KAYDEDERKEN DIKKAT
                if (B.IMAGEDATA != null)
                    com.Parameters.AddWithValue(“@IMAGEDATA”, B.IMAGEDATA);
                else
                    com.Parameters.AddWithValue(“@IMAGEDATA”, System.Data.SqlTypes.SqlBinary.Null);

SQL SERVER DATABASEDE ISTENILEN DEĞERİ ARAMA PROSEDURE

On By semt20In SqlLeave a comment 
--EXEC SearchAllTables 'a' 

CREATE PROC SearchAllTables( @SearchStr nvarchar(100))
    AS
 BEGIN
     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
        SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
        SET @TableName = ''
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE @TableName IS NOT NULL
      BEGIN
        SET @ColumnName = ''
        SET @TableName = (
                            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                              FROM INFORMATION_SCHEMA.TABLES
                             WHERE TABLE_TYPE = 'BASE TABLE'
                               AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                               AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA)
                                                      + '.' + QUOTENAME(TABLE_NAME) ),
                                                  'IsMSShipped' ) = 0
                         )
      WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
              BEGIN
                        SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME))
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                        AND TABLE_NAME = PARSENAME(@TableName, 1)
                        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                        AND QUOTENAME(COLUMN_NAME) > @ColumnName )
                         IF @ColumnName IS NOT NULL
                              BEGIN
                                        INSERT
                                          INTO #Results
                                          EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',
                                                 LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + '
                                                 (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
                                END
                END
       END
SELECT ColumnName, ColumnValue
  FROM #Results
   END

Sentez Live Dev – Sql Queryden Hareketlerin Silinmesi

On By semt20In sentez, SqlLeave a comment
Aşağıdaki sorgu liveda (peşin satışsız,ncrsız) kartlar dışında hareketleri siler:(malzeme fişleri ,irsaliyeler,faturalar,teklifler,sipariş,muhasebe fişi,banka çek fişleri).
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.

 

 

 

delete Erp_InventoryReceiptItem
update Erp_Invoice set InventoryReceiptId = null
update Erp_InventoryReceiptAttachment set  InventoryReceiptId = null
delete Erp_InventoryReceiptAttachment
delete Erp_InvoiceAttachment
delete Erp_WorkOrderProduction
delete Erp_InventoryReceipt
delete Erp_Invoice
delete Erp_BankAccountTotal
delete Erp_BankCredit
delete Erp_BankReceiptItem
update Erp_ChequeReceipt set BankReceiptId=null
delete Erp_ChequeReceiptItem
delete Erp_ChequeReceiptAttachment
delete Erp_ChequeReceipt
delete Erp_BankReceiptAttachment
delete Erp_BankReceipt
delete Erp_CashTotalItem
delete Erp_CashTotal
delete Erp_Cheque
delete Erp_OrderReceiptItem
delete Erp_OrderReceiptAttachment
delete Erp_OrderReceipt
delete Erp_ContractItem
delete Erp_ContractAttachment
delete Erp_Contract
delete Erp_CurrentAccountReceiptItem
delete Erp_CurrentAccountReceiptAttachment
delete Erp_CurrentAccountReceipt
delete Erp_CurrentAccountTotal
delete Erp_QuotationReceiptItem
delete Erp_QuotationReceiptAttachment
delete Erp_QuotationReceipt
delete Erp_DemandReceiptItem
delete Erp_DemandReceiptAttachment
delete Erp_DemandReceipt
delete Erp_WorkOrderItem
delete Erp_WorkOrderAttachment
delete Erp_WorkOrderExplanation
delete Erp_WorkOrder
delete Erp_GLReceiptItem
delete Erp_GLReceipt
Delete Erp_InventoryTotal
delete Erp_ReceiptPaymentItem
delete Meta_ForexRate
delete Erp_ServiceTotal
Delete Erp_GLAccountTotal
delete Erp_BankAccountTotal
truncate table Log_Transaction

shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO

--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok

ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO

Sql Server Shrink Database and Log

On By semt20In SqlLeave a comment 


--log dosyasını bul     
 SELECT name FROM sys.master_files WHERE type_desc = 'LOG' and physical_name like '%Jam%'




        ALTER DATABASE DevJamieS
        SET RECOVERY SIMPLE
        GO
        DBCC SHRINKFILE (DevJamieS_log, 1)
        GO
        ALTER DATABASE DevJamieS
        SET RECOVERY FULL

------------

USE LKSDB;
GO
ALTER DATABASE LKSDB
SET RECOVERY SIMPLE;
GO

--datadakı log dosyasını shrınk yap

ALTER DATABASE LKSDB
SET RECOVERY FULL;
GO

EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
	ALTER DATABASE ?
	SET RECOVERY SIMPLE; 
		DBCC SHRINKFILE (['' + @logname + ''] ,1)
		 
		ALTER DATABASE ?
		SET RECOVERY FULL
		'')  
end 
'

Visual c# ms sql database çifte kontrol

On By semt20In C SHARP, Sql, visual studioLeave a comment 
           SqlConnection connectionString = new SqlConnection("Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA);
            connectionString.Open();
            {




                SqlCommand sqlcom2 = new SqlCommand("select count(malzeme_kodu) from tbl_malzeme  where tbl_malzeme.malzeme_kodu = '" + Text_malzeme_kodu.Text + "'", connectionString);

                if (sqlcom2.ExecuteScalar() != null)
                {
                    int UserExist = (int)sqlcom2.ExecuteScalar();
                    if (UserExist > 0)
                    {
                        MessageBox.Show("Bu İsimde Malzeme Kodu Daha Önceden Yaratılmıştır!");
                    }
                }

visual c# update ms sql table from query inside txt file

On By semt20In C SHARP, Sql, visual studioLeave a comment 
 // MessageBox.Show(Text_malzeme_grubu.EditValue.ToString());
            //  string connectionString = null;
            SqlConnection connectionString = new SqlConnection("Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA);
            connectionString.Open();
            FileInfo file = new FileInfo(Application.StartupPath + "\\tbl_cari_kartlari_duzenle_kaydet.txt");
            string duzenle = file.OpenText().ReadToEnd();
            //MessageBox.Show(silinecekkod.ToString());
            SqlCommand sqlcom = new SqlCommand(duzenle, connectionString);
            sqlcom.Parameters.AddWithValue("@cari_turu", Text_cari_turu.Text.Trim());
            sqlcom.Parameters.AddWithValue("@cari_adi", Text_cari_adi.Text.Trim());
            sqlcom.Parameters.AddWithValue("@irtibat", Text_irtibat.Text.Trim());
            sqlcom.Parameters.AddWithValue("@adres", Text_adres.Text.Trim());
            sqlcom.Parameters.AddWithValue("@paremetre", text_paremetre.Text.Trim());
            sqlcom.Parameters.AddWithValue("@cari_kodu", Text_cari_kodu.Text.Trim());




            sqlcom.ExecuteNonQuery();
            connectionString.Close();
            MessageBox.Show("Cari Başarıyla Düzenlendi!", "CARİ KARTI DÜZENLEME", MessageBoxButtons.OK, MessageBoxIcon.Information);

/*txt file =*/
update tbl_cariler
set cari_turu=@cari_turu , cari_adi = @cari_adi ,irtibat=@irtibat , adres = @adres, paremetre = @paremetre
where cari_kodu = @cari_kodu

gridview connect to ms sql with txt file query and get data to gridview with summary

On By semt20In C SHARP, Devexpress, Sql, visual studioLeave a comment 
  string connectionString = null;
            SqlConnection cnn;
            connectionString = "Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA;
            FileInfo file = new FileInfo(Application.StartupPath + "\\tbl_cariler.txt");
            string script = file.OpenText().ReadToEnd();
            cnn = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter(script, cnn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            gridControl1.DataSource = dt;
            gridView1.Columns[1].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
            gridView1.Columns[1].SummaryItem.DisplayFormat = "Adet = {0}";

            gridView1.Columns["Bakiye"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["Bakiye"].SummaryItem.DisplayFormat = "Toplam = {0}";
            gridView1.BestFitColumns();